Creating an SQL database connection

The New Connection wizard creates an SQL database connection that you can use either at design time or at run time.

EGL offers two other ways to create an SQL connection:

  • The sqlLib.connect() system function, to activate a connection at run time; see connect().
  • The vgLib.connectionService() system function, for programs migrated from VisualAge® Generator or EGL version 5; see connectionService().

Even if you plan to use either of these functions, it is still recommended that you have a default connection in place.

Prerequisites

You must have a database set up and running. The database must be one of the products that EGL supports. See Supported SQL database managers for more information.

Opening the New Connection wizard

In EGL, you can access the New Connection wizard from many of the places where an SQL connection is required. The workbench provides several places to find the New Connection wizard:

  • From the workbench menu, click Window > Preferences > EGL > SQL Database Connections. To the right of the list labeled Connection, click the button labeled New.
  • Right-click an EGL project and then click Properties. In the Properties window, click EGL Runtime Data Source and then click New.
  • Open the Data perspective (Window > Open Perspective > Other > Data). The Database Explorer view is available in this perspective, by default in the lower left corner of the workbench. Right-click Connections and pick New Connection from the menu.
    Note:
    The Data perspective is not filtered for EGL, and includes information for other products. Thus if you run the New Connection wizard from the Data perspective, you might see databases listed (such as Generic JDBC or Sybase) that EGL does not support. Use the table in "Supported database managers" to determine the database managers that you can use with EGL.

The information the wizard needs varies with the type of database that you use. When you choose from the Select a database manager list, the wizard fills in as many default values as it can. For the list of supported databases and the information that each requires, see Supported SQL database managers.

When you have given the wizard enough information, the Test Connection button becomes available. Click this button to verify your connection information. If you get an error message, check your settings or work with your database administrator to resolve the problem.

If the test is successful, you have everything you need to use the EGL features that require a design-time connection, such as Creating a data access application or Retrieving SQL table data. You are not, however, automatically set up to have an SQL connection at run time. To specify that connection, see Using an SQL database connection at run time.

Creating the new connection

Once you have opened the New Connection wizard as explained above, you can start filling in the information:
  1. Under Select a database manager, select the type and version of database you are connecting to. You should do this step first because the remainder of the fields on the page depend on this choice.
  2. Once you have selected the database type, fill in the remainder of the fields on the page. Which fields you need to fill in depends on which database you are connecting to. See Fields in the New Connection wizardfor information on the individual fields.
  3. When you have filled out the fields in the wizard, you can click Test Connection to make sure that the connection is working.
  4. Click Next.
  5. On the Filter page, you can select schemas from the database to be included or ignored in the connection.

    By default, the New Connection wizard will retrieve information for each schema in the database and each table in each of those schemas. Retrieving this information can take time on large databases. The EGL Data Access Application wizard requires this connection information to produce parts from the database, but the other areas of EGL design-time access functionality, such as the SQL retrieve functionality described in Retrieving SQL table data, do not require this information. For this reason, you can save time by filtering out schemas or tables that you do not want to use with the Data Access Application wizard, or by filtering out all of the schemas and tables if you do not want to use this connection with the Data Access Application wizard at all.

  6. If you do not want to filter schemas out of the connection, click Finish.
  7. If you want to filter schemas out of the connection, follow these additional steps:
    1. Clear the Disable filter check box.
    2. Click the Selection radio button. The schemas in the database are listed below.
    3. In the list under Selection, select Include selected items or Exclude selected items, depending on whether you want to select the schemas to include or the schemas to exclude from the connection.
    4. Select or clear the check boxes next to the schemas in the list. You must select at least one schema.

      Schemas that you filter out of the connection will not be available if you use this connection with the Data Access Application wizard.

    5. Click Finish.
    Alternately, you can select the Expression radio button and enter a search string to indicate which tables should be included.

Fields in the New Connection wizard

The New Connection wizard fills in the following fields automatically:
JDBC driver
This is the EGL name for the driver that is used to talk to the database manager, such as "IBM® DB2® Universal."
JDBC driver class
This is the name of the Java™ class that contains the driver:
  • For IBM® DB2® Universal Driver, the driver class is com.ibm.db2.jcc.DB2Driver
  • For IBM® DB2® APP DRIVER for Windows™, the driver class is COM.ibm.db2.jdbc.app.DB2Driver
  • For the Oracle JDBC thin client-side driver, the driver class is oracle.jdbc.driver.OracleDriver
  • For the Informix® JDBC NET driver, the driver class is com.informix.jdbc.IfxDriver
  • For the DataDirect SequeLink JDBC Driver for SQL Server, the driver class is com.ddtek.jdbc.sqlserver.SQLServerDriver
  • For the Microsoft™ JDBC Driver for SQL Server 2005, the driver class is com.microsoft.jdbc.sqlserver.SQLServerDriver; for SQL Server 2000, the driver class is com.microsoft.sqlserver.jdbc.SQLServerDriver
  • For Derby, the driver class is org.apache.derby.jdbc.EmbeddedDriver
  • For Cloudscape®, the driver class is com.ibm.db2j.jdbc.DB2jDriver
  • For other driver classes, refer to the documentation for the driver.
Connection URL
This is the address that EGL uses to contact the database, such as "jdbc:db2://localhost:50000/SAMPLE:retrieveMessagesFromServerOnGetMessage=true;" This URL contains a hostname, port number, and attributes.

You can override the default values in these fields by selecting Other for your JDBC driver and specifying the appropriate JDBC driver class.

The wizard will complete as many of the other fields as it can, but might require you to fill in some or all of the following:
Connection Name
You will not need to complete this field if you check the Use default naming convention option, which typically uses the name of your database.
SID
The ID of the server where your database is located.
Host
The host name of your database server.
Port number
The port number that you connect to on the host.
Server
The address of your database server.
Database
The name of the specific database to which you want to connect.
Class location
The fully qualified location of the *.jar or *.zip file that contains the driver class:
  • For IBM® DB2® Universal Driver, type the fully qualified filenames to the db2jcc.jar and db2jcc_license_cu.jar files
  • For IBM® DB2® APP DRIVER for Windows™, type the fully qualified filename to the db2java.zip file; for example, d:\sqllib\java\db2java.zip
  • For the Oracle THIN JDBC DRIVER, type the fully qualified pathname to the ojdbc14.jar file; for example, d:\Ora81\jdbc\lib\ojdbc14.jar or, if you require Oracle trace, ojdbc14_g.jar
  • For the Informix® JDBC NET driver, type the fully qualified filename to the ifxjdbc.jar file
  • For the DataDirect SequeLink JDBC Driver for SQL Server, type the fully qualified filenames to the base.jar, util.jar, and sqlserver.jar files
  • For the Microsoft™ JDBC Driver for SQL Server, type the fully qualified filenames to the msbase.jar, msutil.jar, and mssqlserver.jar files
  • For Derby, type the fully qualified path to the derby.jar file
  • For Cloudscape®, type the fully qualified filename to the db2j.jar file
  • For other driver classes, refer to the documentation for the driver
User ID
If your database is password protected, you can store the user ID here. Note that the Tomcat server ignores the userID and password that you provide here and uses the values from its server configuration.
Password
If your database is password protected, you can store the password here. Note that the Tomcat server ignores the userID and password that you provide here and uses the values from its server configuration.