Connecting Databases

Following is the syntax for sqlconn():

SYNTAX
int
sqlconn ( pparms )
  char  *pparms;    /* INPUT */ 

The character string you pass to sqlconn() must specify the physical filenames of databases you want to access in your program. You can also specify any Progress database connection parameters in the string you specify in the Progress 4GL CONNECT statement. You can also specify the Parameter File (-pf) startup parameter to indicate the name of a parameter file that contains your list of database connection parameters.

The sqlconn() function only accepts database connection parameters and ignores Progress parameters (such as the Date Format (-d) startup parameter) that do not affect connection and run-time behavior for a specific database.

When you connect to a database, it receives a default logical name. This is the physical name of the database file without the .db extension. You can also assign a logical name using the Logical Database Name (-ld) startup parameter. You can then reference this logical name in your ESQL statements and sqldiscon() function as an application standard. For more information on database logical names, see the Progress Programming Handbook.

See the Progress Startup Command and Parameter Reference for a list of parameters you can use when connecting databases by calling sqllogin() or sqlconn().

Accessing Multiple Databases in ESQL

You can access multiple Progress and non-Progress databases in an ESQL application. To access multiple databases, you only need to specify the appropriate startup parameters when you log in and when you connect to the databases using sqlconn().

By default, Progress can connect up to five databases at a time. If you want to connect to more than five databases, specify the number of databases using the Number of Databases (-h) startup parameter. The maximum number of databases to which you can connect is 240. For a large client, you can pass -h to sqllogin() when you start your ESQL session. For a small client, specify -h as a startup parameter when you start the Open Interface Broker (OIB) or manual Open Interface Driver (OID). For more information on the Number of Databases (-h) startup parameter, see the Progress Startup Command and Parameter Reference. For more information on the OIB and OID, see Building and Running ESQL Applications."

To actually connect your databases, you can make a separate call to sqlconn() for each database or a single call to connect them all at once. If you call sqlconn() once to connect multiple databases, include the -db option before each database name except the first. For example, the following sqlconn() call connects to databases customer and order:

sqlconn("customer -db order"); 

Using Database Name Qualifiers

When connecting to multiple databases in an ESQL program, you may need to qualify table names to avoid ambiguous references. Progress/SQL uses a two-part naming convention. The syntax to qualify a table name for a database is database-name.table-name. However, to qualify the columns in selection lists for ambiguous table references, specify a correlation name for the table to provide a single name to qualify each ambiguous column name. You can specify a correlation name following each qualified or unqualified table name in a table reference. You separate the table name and correlation name in the table reference with the AS keyword:

SYNTAX
SELECT ... FROM [database-name.]table-name AS correlation name
  [ , [database-name.]table-name AS correlation name ] ...; 

You can use two-part references of the form correlation-name.column-name to uniquely identify ambiguous column references in selection lists, as shown in this example:

EXEC SQL DECLARE x CURSOR FOR 
    SELECT firstcust.name, firstcust.cust\-num, 
           secondcust.name, secondcust.cust\-num
    FROM first.customer AS firstcust, second.customer AS secondcust; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095