Writing an SQL Connection program

To ensure your program works properly with the SQL Connection API, write your program to do the following. See SQL Connection sample programs for information on programs distributed with Connectivity Series that illustrate these steps.

Note that the sequence of the above and the details vary. For example, SELECT statements are typically processed as follows:

1. Assign values to bind area.
2. Open a cursor and bind data variables.
3. Define data for the result row.
4. Fetch and load data into defined data fields.
5. Process the fetched data.
6. Repeat the fetch, load, and process steps until complete.
7. Close the cursor.

Non-SELECT statements, on the other hand, are typically processed as follows:

1. Open a cursor and bind data variables.
2. Assign values to the bind area.
3. Execute the statement. (This does the actual bind.)
4. Repeat the assign and execute statements until complete.
5. Close the cursor.

See Function call flows for details on the function call sequences used for different types of queries and updates.

Including ssql.def

The ssql.def file contains the definitions needed for SQL Connection. You must use the .INCLUDE compiler directive in your SQL Connection program to include ssql.def, which is located in the synergyde\connect directory. Because the CONNECTDIR environment variable is generally set to this directory, you can use it in the .INCLUDE statement to locate ssql.def. For example:

.include "CONNECTDIR:ssql.def"

Allocating a data area

To receive data from or send data to a database, your program must allocate a data area for the incoming and outgoing data. To enable your program to do this, create a record with variables that correspond to the data you’ll be reading from or writing to the database. For example:

.align
literal samples    ;Data area for retrieved data (Variables for defining)
        deptnum         ,i4
        deptname        ,a6
        division        ,a15
.align
record data        ;Data area for data to insert (Variables for binding)
       s_deptnum       ,[MX_VARS] i2    ,1,2,3,
       s_deptname      ,[MX_VARS] a6    ,"SDM","SUPP","ACCT"
       s_division      ,[MX_VARS] a15   ,"ACCOUNT", "INTERNAL S","OFFICE MIS"

For more information on data area allocation, see Using data.

Initializing SQL Connection

For an SQL Connection program to run, you must initialize SQL Connection. This instructs the Synergy runtime to allocate the necessary memory.

You can initialize SQL Connection with the DBLOPT environment variable (see Installing, configuring, and initializing), or you can initialize SQL Connection from your program. To initialize SQL Connection from your program, do one of the following:

sts = %option(48,1)   ;Sets option 48 and assigns the return value to a variable (sts).

xcall init_ssql       ;Initializes SQL Connection.

Making database connections

Connecting to a database essentially means logging on to a database. Once logged on, a connection is established and maintained by SQL Connection as an open database channel. Once a connection is established, each SQL statement is processed by the native SQL processor within the database engine. SQL Connection simply passes the SQL statement through to the database.

On Unix and OpenVMS, SQL Connection enables you to maintain up to seven concurrent connections to one or more heterogeneous databases in a client/server configuration. On Windows, to accommodate multi-threading, you can have up to 100 connections. Be careful not to exceed the maximum number of concurrent connections. If your application deals with multiple database servers, you may want to connect to the same database more than once, but remember that each connection requires a separate log-on to the database. You can disconnect and reconnect to the same database without increasing the number of connections.

Note

With Synergy .NET in a multi-threaded environment, you must ensure that each thread uses a separate connection (i.e., a separate channel). Channels are not automatically shut down when threads terminate.

We don’t recommend recycling (pooling) connections, particularly for Oracle databases. You may want to consider recycling connections if there is generally a large period of elapsed time (e.g., over 15 minutes) between connection attempts, but you should never recycle Oracle connections because the OCI library that SQL Connection uses doesn’t handle connection recycling well.

Connecting to a database

To connect to a database, use the %SSC_INIT function to initialize a database channel, and then use the %SSC_CONNECT function with a connect string to connect to a database channel (and specify the database, username, password, and so forth). The syntax of the connect string is dependent on the database and configuration. (See Building connect strings.)

if (%ssc_init(dbchn))             ;Initialize a database channel.
  goto err_exit
Writes(1, "Connecting to database. Please wait...")
if (%ssc_connect(dbchn, user))    ;Connect to database using the connect
  goto err_exit                   ; string represented by the user variable and the 
                                  ; database channel represented by dbchn.

Disconnecting from a database

To disconnect from a database and release the database channel, use the %SSC_RELEASE function. For example:

if (%ssc_release(dbchn)) ;Release the database channel
  goto err_exit          ; represented by the dbchn variable.

Using cursors

SQL Connection uses three types of cursor: logical cursors, database cursors, and statement cursors. When you initialize a database channel with %SSC_INIT, you’ll allocate logical cursors. But otherwise, you’ll create, use, and close database and statement cursors. %SSC_OPEN opens a new cursor, %SSC_SCLOSE and %SSC_CLOSE close cursors (soft close or hard close, respectively), and various SQL Connection routines accept a cursor ID. You can use %SSC_CMD to set cursor options. See Using cursors with SQL Connection for more information.

Defining variables and binding data areas

You can use variables to store data sent to and received from the database. Defined variables store data received from a database; bind variables store data that’s sent to a database. To create defined variables, use %SSC_DEFINE or %SSC_STRDEF. For a bind variable, you pass a placeholder in the SQL statement and then pass the bind variable in %SSC_OPEN, %SSC_BIND, or %SSC_SQLLINK. See Using variables to map data for more information.

Processing SQL statements

Once you open a cursor and, if necessary, map the data, you can process an SQL statement in one of three ways, depending on the type of SQL statement as shown in the table below.

Type

Function

Description

SELECT statement

%SSC_MOVE

Loads desired data to the program data area.

Non-SELECT statement

%SSC_EXECUTE

Executes an SQL statement.

Stored procedure

%SSC_EXECIO

Executes a stored procedure and passes parameters, but doesn’t accept a result set.

%SSC_EXECUTE

Executes a stored procedure without parameters or a result set.

%SSC_MOVE

Retrieves data from a SQL Server stored procedure result set.

Supported SQL statement syntax differs from one database to another. However, most databases support the ANSI SQL syntax. If you want to write a truly database-independent application, you must avoid database-specific syntax in your SQL statements.

Stored procedures are highly recommended to boost database performance. Stored procedures can enhance program modularity and reduce development costs. In a client/server configuration, well-written stored procedures can also greatly reduce network traffic. Note, however, that stored procedures are database dependent and therefore cannot be used in a truly database-independent application. See Running stored procedures from SQL Connection for more information.

Using your program with different databases and drivers

Different databases and drivers handle things differently. If you plan to use your program with different database, keep the following in mind:

sqlp = "{call read_salary (:1, :2, :3)}"
if (%ssc_open(dbchn, cur1, sqlp, SSQL_NONSEL))

For more information, see Running stored procedures from SQL Connection and the stp_*.dbl example files included in the Connectivity Series distribution.