You can use any JDBC/ODBC-compliant database as a data source for a Generator template or object. In Windows, you must first register the data source to establish a mapping between the database and its driver to the system.
Note: JDBC/ODBC connectivity is not supported on the Macintosh. You can develop template files that use database connectivity on the Macintosh, but you will not be able to preview them until the templates are moved to a Windows or UNIX system with the required JDBC/ODBC drivers and databases installed.
Before attempting to use JDBC/ODBC connectivity, be sure to configure the data source on your local machine. Consult your operating system or database application documentation for specific instructions, as this process differs across platforms.
In Windows, be sure to configure the data source as a System DSN, instead of a User DSN. System DSNs are available to all users on the system (including the system itself and the account used by the Web server for anonymous HTTP requests). User DSNs are restricted to the user account that created them, and their use requires a login under such an account.
After registering your JDBC/ODBC data source, be sure to verify the connection to the database or data source through the driver. Some database applications provide utilities to test ODBC connections through the driver. Before you start attempting to connect to the database through Generator, it's a good idea to be sure that you can indeed connect to the database through the ODBC driver. Finding and resolving problems at the driver level will save time and frustration when you begin to work with Generator.
To assign the ODBC data source to the template:
1 | Display the template command or Generator object with which you want to associate the database. |
2 | Specify a URL similar to the following in the data source field: |
fgjdbc:///?driver=sun.jdbc.odbc.JdbcOdbcDriver&url=jdbc:odbc: |
|
![]() |
Note: When creating and testing your template, enter a variable in the data source field. In a text file associated with a Generator Environment Variable command, enter the special URL that contains your SQL query as the definition for the variable. This makes it easier to modify and test your queries as you develop the template. When you have completed and tested the template, replace the variable in the data source field with the final URL. Using the query directly in the data source field, rather than requiring a call to the file system for the text file, ensures the best performance.
This URL is a special Generator extension that directs Generator to load a special module able to interpret the following arguments of the URL and make a database query. Because the query is specified as part of a URL, any spaces or other special characters in the data source must be URL-encoded.
fgjdbc:/// directs Generator to interpret the rest of the URL as a connect string and SQL query using JDBC extensions to Java.
?driver=sun.jdbc.odbc.JdbcOdbcDriver identifies the JDBC driver to use for the SQL query. This example uses the JDBC/ODBC bridge driver supplied with the Java Runtime Environment included with Generator. If you are connecting to an ODBC data source, use the value exactly as specified. This string would change, however, when using a native JDBC driver. For example, a native JDBC driver for Oracle might look like ?driver=oracle.jdbc.driver
.OracleDriver
. See the documentation for your database application or JDBC driver for specific details.
&url=jdbc:odbc:DataSourceNam identifies the JDBC URL, which identifies the ODBC data source to open. For the JDBC/ODBC bridge, DataSourceName
must match the name of the data source registered with ODBC. When using a pure JDBC driver, remove odbc:
.
&userid=UserID identifies the user ID associated with the SQL query. You do not have to identify a user ID, but you must include at least &userid=
in the URL.
&password=Password identifies the password associated with the SQL query. You do not have to identify a password, but you must include at least &password=
in the URL.
&query=SELECT%20*%20FROM%20TableName; is the SQL query to run. The query used in this example corresponds to the following SQL statement, which retrieves all the records contained within the table:
SELECT * FROM TableName;
Generator can process any SQL statement that returns an SQL database table. Because the query is specified as part of a URL, any spaces or other special characters in the data source must be URL-encoded.
SQL INSERT, UPDATE, and DELETE statements are generally not useful for Generator objects because they do not return a database table or useful result set that Generator can interpret. If your project requires these types of database operations, consider pointing Generator objects to a URL for a Web application that handles these database operations and returns a useful result set to Generator. This strategy allows you to pass information to the Web application in the query string portion of the URL. The Web application (ASP, Cold Fusion, CGI, and so on) can accept these parameters, perform the database operations, provide error checking and exception handling, and return a meaningful data source to Generator as output for the HTTP request.