  |
GoFiler Legato Script Reference
Legato v 1.4j Application v 5.22b
|
| Table of Contents | < < Previous | Next >> |
Chapter Eight — Data Functions (continued)
8.5 Open Database Connectivity (ODBC) Tools
ODBC (Open Database Connectivity) is a standard programming language middleware API for accessing database management systems (DBMS). ODBC allows for access to many database types both as local hosts and as remote servers. The hosts can be basic applications such as Microsoft Excel or Access or remote services such as MySQL or SquealServer. Legato provides a series of functionality and objects to allow the use of the Microsoft Windows standard ODBC drivers. However, this also means the ODBC driver must be setup in order for scripts to access an external database.
In order to effectively use ODBC, programmers need at least a basic knowledge of database structure, terms and of Sequential Query Language (SQL). It is beyond the scope of this documentation to instruct on databases and SQL.
ODBC is designed to allow independent access of database systems (including different operating systems). ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. An ODBC driver can be thought of as analogous to a printer or other driver, providing a standard set of functions for the application to use while implementing DBMS-specific functionality.
The application connects via an ODBC driver which in turn manages the connection to the server or servers. Drivers are normally provided by the server vendor and must be 32-bit ANSI compatible. In many cases, the driver can be set up as generic connection or as a named connection. In the generic mode, the script must provide the authentication parameters for connecting and logging into the server while within a named connection, this information is built into the driver. The latter is a more secure method since the script is not required to contain a user name and password within the code.
Once a connection has been established, the SQL query is the primary communication.
ODBC support is purposed for access of existing databases, not design of database systems. That is the domain of other applications.
Finally, note that the connection is a 32-bit connection. For Windows 7 and earlier, accessing the correct dialog can be a bit trick.
An example of the Windows 8.1 interface:
As part of the setup, check MSDN or other sources to insure the correct interface is being used.
To access an external database, a script first establishes a connection to a remote host. The connection parameters will vary from host application to host application and may require various levels of user authentication. Once a connection has been established, a connection object is created and the script can perform a series of queries to read, write and alter the database. When a successful query is performed, a result object is normally created. The result object can then be referenced.
When a connection is established, a connection handle is returned by the ODBCConnect function. This handle is then used for most of the ODBC functions.
Connections can be open for extended periods of time. Avoid repeatedly opening and closing connections if it is not necessary since it can be time consuming.
To close a connection, use the CloseHandle function.
When a query is performed, the result of the query is placed into a Result Object and a Result Object handle is returned. Result handles are special in that they consume a fair amount of resources and are limited to 32 concurrently open result queries per application session. Therefore, it is important to close Result Object after they have been depleted of data or are no longer needed.
All data is returned as encoded strings. Each database host and table supports differing types of data fields. The driver type is specified as ANSI and it is up to the caller to process any data types to the required binary fields such as numbers or dates.
Unlike many functions, most ODBC functions will load the last error message in addition to the last error code. The message, when present, contains data from the host serving the database interface. This can be exceptionally useful when diagnosing issues with ODBC and driver connection issues or determining the nature of a routine failure.
Note that some platforms do not support a full suite of ODBC functions and may or may not return meaningful errors. For example, Excel only supports a fraction of the ODBC functionality.
Connection Management:
CloseHandle — Closes an object handle and releases any associated resources.
ODBCConnect — Opens connection to a database server via ODBC connection.
Query Management:
ODBCFetchAll — Returns all rows and columns from the request as represented by the handle.
ODBCFetchArray — Returns all columns for the current row from the request as represented by the handle.
ODBCFetchBinary — Returns a handle to a Data Object for a column that has 'blob' type data.
ODBCFetchColumnName — Gets the name of a specified column by position.
ODBCFetchColumnNames — Gets the names of all columns in the Result Object.
ODBCFetchData — Returns the data for a specific column of the current row.
ODBCGetColumnCount — Returns the number of columns returned for the specified request.
ODBCGetRowCount — Returns the number of rows returned for the specified request.
ODBCHasData — Returns TRUE if the Request Object has any rows (data).
ODBCNextRow — Moves internally to the next row within the Result Object.
ODBCQuery — Performs a structured query based on the connection handle and query string.
| Table of Contents | < < Previous | Next >> |
© 2012-2024 Novaworks, LLC. All rights reserved worldwide. Unauthorized use, duplication or transmission prohibited by law. Portions of the software are protected by US Patents 10,095,672, 10,706,221 and 11,210,456. GoFiler™ and Legato™ are trademarks of Novaworks, LLC. EDGAR® is a federally registered trademark of the U.S. Securities and Exchange Commission. Novaworks is not affiliated with or approved by the U.S. Securities and Exchange Commission. All other trademarks are property of their respective owners. Use of the features specified in this language are subject to terms, conditions and limitations of the Software License Agreement.