ODBC Data Exchange

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.

Navigate to the User DSN tab, and select Add.

Select the type of Database Driver you will be needing.

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

Description is optional.

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 Import Dialog

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

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.

Dynamic Statements

ODBC statements can reference portal and item attributes.These may be used to construct an SQL request with dynamic parameters.

Quoted text is processed without modification. It may contain any character except the quote
itself.

Outside of double quotes, single quotes enable a single character including the " to be inserted.

Portal attributes can be included using p.attributename and Item attributes can be included using i.attributename. attributename itself can be quoted, eg: p."attribute name with spaces"

An ampersand (&) should be used to separate fields in the statement.

Statements may have line breaks etc. within them.

An example statement:

"Select * from " & i.TableName & " where
Author='" & i.WantedAuthor & "'"

In this example,
Item attribute "TableName" will provide the name of the table
Item attribute "WantedAuthor" provides an author name

ODBC Debugging

Planimate reports ODBC errors using a generic error code. These are defined as follows:

define IF_ODBC_CONNECT_HANDLE 2 // Connection handle allocation

IF_ODBC_CONNECTING   3 // Connecting to data
IF_ODBC_STATE_HANDLE 4 // Statement handle allocation
IF_ODBC_SQL          5 // Executing SQL statement
IF_ODBC_ADD          6 // Adding a record
IF_ODBC_FIND         7 // Matching a record
IF_ODBC_DELETE       8 // Deleting a record
IF_ODBC_UPDATE       9 // Updating a record
IF_ODBC_COLUMN      10 // Obtaining column information
IF_ODBC_TABLE       11 // Obtaining table information
IF_ODBC_FETCH       12 // Reading a record
IF_ODBC_BIND        13 // Binding to column

ODBC operations have an option to not stop the run on error. The system attribute s.ODBCErrorStatus will be set instead. It must be read in the same thread that performs the ODBC operation.

To have Planimate report more ODBC diagnostics, run Planimate with the /DEBUGODBC command line option.

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.

Data Exchange Articles

• File Options
• SQLite Database Interface
• Paste Data
• System String Related System Attributes
• Planimate command line options
• File access error handling
• Model INI File
• Options for Clipboard Export
• Using Time Zones and UTC time
• Table Paste/Replace behaviour
• Determining the last file read or written at a change object
• Executing a command in a minimised window
• Making a HTTP request from a URL
• Broadcast Receivers - determining how many listeners received a broadcast
• Import from Clipboard or File to Table
• Label List Import Format
• Serving web pages and responding to HTTP requests
• Speeding up ODBC operations by keeping the connection open
• Socket configuration dialog for end users
• Dynamically looking up ODBC Data source
• TCP/IP broadcast handler limitations

Data Exchange Frequently Asked Questions

• ODBC Issues with SQL Server