Writing an SQL Connection program

To ensure your program works properly with the SQL Connection API, write your program to do the following. (See Sample programs below 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.

Sample programs

The Connectivity Series distribution includes sample programs that illustrate the basic structure for an SQL Connection program. These programs are in the connect\synsqlx subdirectory of the main Synergy/DE installation directory.

The following examples work with MySQL, Oracle, SQL Server, Sybase, and Synergy databases. They are distributed on all platforms.

The following example is for MySQL only. This is distributed only on Windows and UNIX platforms.

The following example is for Oracle only. This is distributed on all platforms.

The following examples are for SQL Server only. These are distributed only on Windows platforms and will run only if the Microsoft pubs sample database is installed. (This legacy sample database is no longer installed with SQL Server by default. See the comments in the files for details.)

The Connectivity Series distribution (all platforms) also includes exam_saveviews.dbl, an example program for saving views for xfODBC.

On OpenVMS, when linking SQL Connection programs, be sure to use the ssqlrtl.opt link options file (instead of synrtl.opt).

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"

For more information on CONNECTDIR, see Appendix A: Environment Variables.

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 Defining 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.

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.

Be careful not to exceed the maximum number of concurrent connections. 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 multithreading, you can have up to 100 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. On the other hand, you can disconnect and reconnect to the same database without increasing the number of connections.

Note

With Synergy .NET in a multithreaded 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 can’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 two types of cursor: logical and database. When you initialize a database channel with %SSC_INIT, you’ll allocate logical cursors, but otherwise, you’ll create, use, and close database cursors. %SSC_OPEN opens a new cursor, %SSC_SCLOSE and %SSC_CLOSE close cursors (soft close or hard close), and various SQL Connection routines accept a cursor ID so they can access data and update the database via a cursor. You can also 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, you 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 type of SQL statement. See 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.

Notes on drivers and databases

Keep the following in mind when working with different databases and drivers:

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.

Note the following for SQL Server, and for more notes and tips on using SQL Server with SQL Connection, see SQL Server notes and examples.