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.
- Include the ssql.def file. (See Including ssql.def below.)
- Allocate a data area. (See Allocating a data area below.)
- Initialize SQL Connection. (See Initializing SQL Connection below.)
- Connect to a database. (See Making database connections and Using your program with different databases and drivers below.)
- Open one or more cursors. (See Using cursors below.)
- Define variables and bind data areas. (See Defining variables and binding data areas below.)
- Process the required SQL statements for each cursor. (See Processing SQL statements below.)
- Close the cursors. (See Using cursors with SQL Connection below.)
- Disconnect from the database. (See Disconnecting from a database below.)
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:
- For Windows and UNIX systems, use the %OPTION function to set system option #48. For example:
sts = %option(48,1) ;Sets option 48 and assigns the return value to a variable (sts).
- For OpenVMS systems, call the %INIT_SSQL function. For example:
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.
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.
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 |
Loads desired data to the program data area. |
|
Non-SELECT statement |
Executes an SQL statement. |
|
Stored procedure |
Executes a stored procedure and passes parameters, but doesn’t accept a result set. |
|
Executes a stored procedure without parameters or a result set. |
||
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:
- Connect string syntax and some connection and programming details differ for the different database drivers. See Building connect strings, which includes Oracle notes and examples, ODBC-compliant (VTX11) notes and examples, MySQL notes and examples, SQL Server notes and examples, and Using the SQL Server shared memory protocol.
- Some databases have their own SQL constructs that won’t work with other databases. Avoid database-specific SQL if you plan to use your program with different databases.
- Stored procedure syntax differs for each database. For example, use the ODBC CALL escape sequence if you are using an ODBC driver:
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.
- Different databases and database drivers produce different errors. If your program was written to handle errors from one database and driver, don’t expect to get the same errors when you move to another database or use another database driver. For example, if you use an ODBC database driver, you will get ODBC errors.
- Different drivers use different %SSC_CMD options.
- Cursors work differently for different databases. See Using cursors with SQL Connection.
- Autocommit works differently on different databases, so transactions that make sense for one database/driver combination may not work for another. For example, autocommit is turned off by default for Oracle databases, databases accessed with VTX11, and SQL Server databases accessed with VTX12_SQLNATIVE. So with these databases/drivers, you need to use %SSC_COMMIT before calling %SSC_RELEASE because %SSC_RELEASE rolls back pending transactions. For more information, see Understanding transactions and autocommit.
- Updates and locking work differently for different databases. See Understanding updates and locking.