ODBC Data Exchange
- 1 ODBC Data Exchange
- 1.1 Setting Up An ODBC Data Source
- 1.2 Setting Up An ODBC Connection From Planimate®
- 1.3 ODBC Import Dialog
- 1.4 Dynamic Statements
- 1.5 ODBC Debugging
- 1.6 ODBC Options
- 1.7 Dynamic DSN Lookups
- 1.8 Notes regarding Planimate® and ODBC
- 1.9 ODBC Data Source Username and Passwords
- 1.10 Running Planimate® in a Different User Account
- 2 Data Exchange Articles
- 3 Data Exchange Frequently Asked Questions
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.
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
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
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.
- 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
- SQLite Database Interface
- Paste Data
- System String Related System Attributes
- Planimate command line options
- Select File Transfer Mode
- Change Object File Options Choices
- File access error handling
- Model INI File
- Import from Clipboard or File to Table
- Determining the last file read or written at a change object
- Label List Import Format
- Executing a command in a minimised window
- ODBC Query Dialog
- Making a HTTP request from a URL
- Network Sockets Dialog
- Speeding up ODBC operations by keeping the connection open
- Broadcast Receivers - determining how many listeners received a broadcast
- ODBC Options Choice
- Serving web pages and responding to HTTP requests
- Dynamically looking up ODBC Data source
- Socket configuration dialog for end users
- Options for Clipboard Export
- TCP/IP Sockets Editor
- Table Paste/Replace behaviour
- TCP/IP broadcast handler limitations
- Using Time Zones and UTC time
Data Exchange Frequently Asked Questions