_odbc (Database Access)
This object enables simple ODBC (Open Database Connectivity) access.
Introduction
(Linux supported as of 3.1.001.14)
In general, users have their own applications that generate data files that are then merged with an applicable form design. Almost always, those data files will be generated via access to various corporate databases. The apps use speedy computer languages that can maintain database connectivity efficiently across millions of operations. That continues. In no way is this simple JavaScript interface for database access meant to supplant that. What it does allow a user to do is to augment the provided data with some additional elements from an applicable database. That is the extent of the intended usage. It is not intended to maintain databases or "discover" all of the data for a given document. That is still the purview of the customer application.
ODBC requires the use of "Connector/ODBC" drivers that are specific to the database technology in use at the customer site. These connectors are not provided by DocOrigin -- they are a customer's responsibility. The underlying DocOrigin C++ code is expecting ODBC version 3 drivers to be used. DocOrigin does not do any ODBC administration. Setting up DSN (Data Source Name) definitions that enable access is again the responsibility of the customer.
Background
The ODBC functionality involves the use of several JavaScript objects:
The | The _odbc object serves as the anchor. It is available to you 'out of the box'. It allows you to connect to a given database 'data source' via a DSN name that you provide. The _odbc object also lets you terminate your ODBC usage so that all database connections are closed. |
The ODBC connection object | The connection object, often named oConn, is used to execute SQL statements. Typically those would be select statements, though other SQL statements such as insert or describe can be executed as well. In theory, though unlikely, you could have multiple connections open while processing a single document. |
The ODBC statement object | The statement object, often named oStmnt, is used to cycle through all of the rows that were selected by the executed query. In theory, though probably unusual, you could have multiple statements active simultaneously. |
The ODBC row object | The row object, often named oRow, provides all of the columns and their values for a single row. This is often the most convenient means of processing the query results. |
The ODBC column object | The column object, often named oCol, can be used to look at individual columns either by name or column number. |
Usage / Contexts / Performance
Remember that the intended usage is not to write your business application in JavaScript in a document presentation tool. This is meant to add a few data items to what your main data-producing application provided. Establishing a connection to a database is quite slow - e.g. hundreds of milliseconds. That's a lifetime compared to normal document generation processing. It is surely best if the data provided by the program, specifically meant for that purpose, is complete. This JavaScript API for database access offers a port in a storm, especially performance-wise, to augment that data.
Often a "Merge run" processes data for only a single document. In such a case, it's simple, you, of necessity, make an ODBC connection, do your thing, and then terminate your ODBC access. There are no magic options. That one document will pay the entire overhead of establishing a database connection. If your Merge run processes data for multiple documents then you run afoul of the fact that at the end of each document the JavaScript context is destroyed, all your JavaScript objects are gone and you start anew. However, for the special (and 99%) case, of connecting to a single data source, that data source can be kept open for the entire run -- across all switches of JavaScript context. In such a case you would make the _odbc.connect(DSN) call in a Start-of-Job event script. In the Start-of-Document (or Data Merged) event you would use the special reconnect call: oConn = _odbc.connect(). Notice that no DSN argument is supplied and hence can only mean to reconnect to a ('the') prior connection. This is done without any disconnect/reconnect overhead. Hence the overhead of establishing the database connection is amortized over all documents, over the entire Merge run.
