ODBC Data Exchange: Difference between revisions
mNo edit summary |
No edit summary |
||
Line 1: | Line 1: | ||
== ODBC Data Exchange == | == ODBC Data Exchange == | ||
Planimate® supports using ODBC to connect with and transfer data to and from external Data Sources. | 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. | 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 === | === Setting Up An ODBC Data Source === | ||
Any Database you want to use needs to be 'Registered' with ODBC as a 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. | Open Control Panel, then open Administrative Tools, and look for it. | ||
[[Image:ODBC Admin Tools.jpg]]<br> | [[Image:ODBC Admin Tools.jpg]]<br> | ||
<br> | <br> | ||
Navigate to the User DSN tab, and select Add. | Navigate to the User DSN tab, and select Add. | ||
[[Image:ODBC DSN Admin.jpg]]<br> | [[Image:ODBC DSN Admin.jpg]]<br> | ||
Select the type of Database Driver you will be needing. | Select the type of Database Driver you will be needing. | ||
[[Image:ODBC Create New Data Source.jpg]] | [[Image:ODBC Create New Data Source.jpg]] | ||
Enter a Name for the database that will readily identify it. | Enter a Name for the database that will readily identify it. | ||
Description is optional. | Description is optional. | ||
[[Image:ODBC Access Setup.jpg]]<br> | [[Image:ODBC Access Setup.jpg]]<br> | ||
Then hit the Select Button and Browse to the actual database to be used. | 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. | Click OK, and it should now be accessible to the Planimate® model. | ||
<br> | <br> | ||
=== Setting Up An ODBC Connection From Planimate® === | === Setting Up An ODBC Connection From Planimate® === | ||
Place a Change Object on the Panel. | Place a Change Object on the Panel. | ||
<br> Click on it in object view and choose the ODBC option from the Pop up Menu. | <br> Click on it in object view and choose the ODBC option from the Pop up Menu. | ||
[[Image:ODBC Option Select.jpg]]<br> | [[Image:ODBC Option Select.jpg]]<br> | ||
<br> | <br> | ||
=== ODBC Import Dialog === | === ODBC Import Dialog === | ||
In the ODBC Object field, enter the Name of the ODBC Data Source | In the ODBC Object field, enter the Name of the ODBC Data Source | ||
[[Image:ODBC Import Dialog.jpg]]<br> | [[Image:ODBC Import Dialog.jpg]]<br> | ||
In the Query to execute Field, enter the Query (an SQL Statement). | In the Query to execute Field, enter the Query (an SQL Statement). | ||
Choose the local target (a Planimate® Table) | Choose the local target (a Planimate® Table) | ||
Select Options for this ODBC operation. | Select Options for this ODBC operation. | ||
Also recommended is to choose the Option to Automatically Add labels. | |||
=== 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 | |||
#define IF_ODBC_CONNECTING 3 // Connecting to data base | |||
#define IF_ODBC_STATE_HANDLE 4 // Statement handle allocation | |||
#define IF_ODBC_SQL 5 // Executing SQL statement | |||
#define IF_ODBC_ADD 6 // Adding a record | |||
#define IF_ODBC_FIND 7 // Matching a record | |||
#define IF_ODBC_DELETE 8 // Deleting a record | |||
#define IF_ODBC_UPDATE 9 // Updating a record | |||
#define IF_ODBC_COLUMN 10 // Obtaining column information | |||
#define IF_ODBC_TABLE 11 // Obtaining table information | |||
#define IF_ODBC_FETCH 12 // Reading a record | |||
#define IF_ODBC_BIND 13 // Binding to column | |||
=== ODBC Options === | === ODBC Options === | ||
Line 71: | Line 86: | ||
:ODBC operations have an option to not stop the run on error. A system attribute returns whether an error in the ODBC operation occurred. | :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. | 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. | The system attribute is called ODBC Error Status and must be read in the same thread that performs the ODBC operation. | ||
;Keep Database Open | ;Keep Database Open | ||
Line 79: | Line 94: | ||
: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. | :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. | 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. | 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. | 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 | ;Remove Trailing Spaces | ||
Line 91: | Line 106: | ||
=== Dynamic DSN Lookups === | === Dynamic DSN Lookups === | ||
DSNs can be dynamically looked up via an attribute/label list | 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. | 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. | Hence ":pMyAttribute" will use the formatted value of portal attribute "MyAttribute" as the DSN name. | ||
=== Notes regarding Planimate® and ODBC === | === Notes regarding Planimate® and ODBC === | ||
Launching Planimate® with the command line option /DEBUGODBC enables ODBC debugging | Launching Planimate® with the command line option /DEBUGODBC enables ODBC debugging | ||
ODBC command buffer space is 16k. | ODBC command buffer space is 16k. | ||
The time format YYYY-MM-DD may be used in SQL/ODBC dates | The time format YYYY-MM-DD may be used in SQL/ODBC dates | ||
=== ODBC Data Source Username and Passwords === | === 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. | 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. | 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 | 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. | 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 === | === 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. | 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: | 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 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. | 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 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. | 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. | 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. | The target account must have SE_LOGON_INTERACTIVE privilege. | ||
Line 142: | Line 157: | ||
ordermethod=pagetouched | ordermethod=pagetouched | ||
order=descending | order=descending | ||
</dpl> | </dpl> | ||
<br> <br> | <br> <br> | ||
== Data Exchange Frequently Asked Questions == | == Data Exchange Frequently Asked Questions == | ||
Line 153: | Line 168: | ||
ordermethod=pagetouched | ordermethod=pagetouched | ||
order=descending | order=descending | ||
</dpl> | </dpl> | ||
<br> | <br> | ||
[[Category:Data/Exchange]] [[Category:Data]] | [[Category:Data/Exchange]] | ||
[[Category:Data]] |
Revision as of 13:04, 10 November 2011
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 Debugging
Planimate reports ODBC errors using a generic error code. These are defined as follows:
- define IF_ODBC_CONNECT_HANDLE 2 // Connection handle allocation
- define IF_ODBC_CONNECTING 3 // Connecting to data base
- define IF_ODBC_STATE_HANDLE 4 // Statement handle allocation
- define IF_ODBC_SQL 5 // Executing SQL statement
- define IF_ODBC_ADD 6 // Adding a record
- define IF_ODBC_FIND 7 // Matching a record
- define IF_ODBC_DELETE 8 // Deleting a record
- define IF_ODBC_UPDATE 9 // Updating a record
- define IF_ODBC_COLUMN 10 // Obtaining column information
- define IF_ODBC_TABLE 11 // Obtaining table information
- define IF_ODBC_FETCH 12 // Reading a record
- define IF_ODBC_BIND 13 // Binding to column
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