ODBC Data Exchange

From Planimate Knowledge Base
Revision as of 15:24, 17 April 2009 by Tony.Griffith (talk | contribs)
Jump to navigation Jump to search

ODBC Data Exchange

Planimate® supports using ODBC to connect with and transfer data to and from external Data Sources.

SQL queries can be prepared, using replaceable parameters to make them dynamic within a Planimate® model. Reading and writing to DSN’s is supported.

Setting Up An ODBC Data Source

Any Database you want to use needs to be 'Registered' with ODBC as a Data Source.

Open Control Panel, then open Administrative Tools, and look for it.

ODBC Admin Tools.jpg


Navigate to the User DSN tab, and select Add.

ODBC DSN Admin.jpg

Select the type of Database Driver you will be needing.

ODBC Create New Data Source.jpg

Enter a Name for the database that will readily identify it.

Description is optional.

ODBC Access Setup.jpg

Then hit the Select Button and Browse to the actual database to be used.

Click OK, and it should now be accessible to the Planimate® model.


Setting Up An ODBC Connection From Planimate®

Place a Change Object on the Panel.


Click on it in object view and choose the ODBC option from the Pop up Menu.

ODBC Option Select.jpg


ODBC Import Dialog

In the ODBC Object field, enter the Name of the ODBC Data Source

ODBC Import Dialog.jpg

In the Query to execute Field, enter the Query (an SQL Statement).

Choose the local target (a Planimate® Table)

Select Options for this ODBC operation.


Also recommended is to choose the Option to Automatically Add labels.


ODBC Options

Automatically Add Labels
Set this option to cause labels to automatically be added to label lists rather than prompting the user for each new label.
Don't Stop Run on Errors
ODBC operations have an option to not stop the run on error. A system attribute returns whether an error in the ODBC operation occurred.

A Non zero value indicates an error.

The system attribute is called ODBC Error Status and must be read in the same thread that performs the ODBC operation.

Keep Database Open
Set this option in ODBC Read to leave the database open after the operation. This retains the connection to the DSN. If the same DSN is required again, the cached connection will be used and will be much quicker than opening a new one.

To keep a particular connection open over multiple change objects, each change object using that DSN must have the "Keep Open" option on.

Only one DSN can be kept open, a new one will override an older one. However if a second DSN is accessed while an existing DSN has been cached AND the KeepOpen option is not on for the second DSN, the original DSN will remain cached/open. Future Planimate® versions may support caching multiple DSNs.

A modeller should close a DSN (by making the last access to it from a change object without the KeepOpen() option. Not doing so is not good practice and a warning will be logged to the Planimate® debug file when the model is closed.

Remove Trailing Spaces
Set this option to strip any trailing spaces from data as it is imported.

Dynamic DSN Lookups

DSNs can be dynamically looked up via an attribute/label list

If the DSN starts with a ":" the following text is interpreted using the same mechanism as the ODBC attribute parser.

Hence ":pMyAttribute" will use the formatted value of portal attribute "MyAttribute" as the DSN name.

Notes regarding Planimate® and ODBC

Launching Planimate® with the command line option /DEBUGODBC enables ODBC debugging

ODBC command buffer space is 16k.

The time format YYYY-MM-DD may be used in SQL/ODBC dates

ODBC Data Source Username and Passwords

These can optionally be read from an external file. This file is scrambled and generated by a small separate executable.

To use the external file, in the username and password fields precede the filename with an "@" and append an "|1" for the username field and "|2" for the password field.

eg: for username you might have @mypassfile.dat|1

The filename can be remapped in the model's INI file as is possible with other files referenced in Planimate. This enables the end user to keep the exact path of the file out of the model/standalone EXE.

Running Planimate® in a Different User Account

Planimate® can run in a different user account than the user that launches it. This means Planimate® can have access to data which the user cannot access from explorer.

This works as follows:

The shortcut which launches Planimate® needs to include the /LOGIN=filespec command line option, where filespec points to a password file. This file is generated using the ODBC Password Generator EXE.

The generator EXE is used to create an encrypted file for Planimate® containing the username and password of an account on the local machine to use.

If a domain controller is used, the username entered in the password generator can be of the form "user@domain".

If /LOGIN is user and there is a problem opening the file or verifying the password, an error will be given and Planimate® will close.

Under Win2000, the user account which launches Planimate® must have SE_TCB_NAME privilege and in some cases SE_CHANGE_NOTIFY_NAME as well.

The target account must have SE_LOGON_INTERACTIVE privilege.