Using variables to map data

This topic includes the following sections:

Defining variables
Binding data

As you add SQL statements to your program, you can use variables to store data sent to and received from the database. This is called data mapping. Defined variables store data received from a database. Bind variables store data that’s sent to a database.

Since SQL Connection always attempts to convert data for given types, make sure you use the proper data types for both defining and binding. In addition, remember that data conversion is a database-specific operation. See Data conversion when binding and defining for the recommended use of data types.

Defining variables

If your SQL Connection program includes an SQL statement that returns data, you must use defined variables to store the returned data. To do this, declare a variable for each column that will be returned from the query. Then, in a call to %SSC_DEFINE, specify the declared variables in the order that their corresponding columns are specified in the SQL statement. %SSC_DEFINE maps the variables to the returned columns of data. Finally, call %SSC_MOVE to fetch the data into the variables. If you are passing a lot of variables, you may want to use %SSC_STRDEF rather than %SSC_DEFINE, as it may improve performance.

The following example illustrates how you can handle an SQL statement that returns just one row of data:

sqlp="SELECT deptnum FROM org1 WHERE division=:1"
if (%ssc_open(dbchn, cur1, sqlp, SSQL_SELECT,, 1, a_bind_var))
  goto err_exit
if (%ssc_define(dbchn, cur1, 1, a_deptnum))    ;Map variable to column.
  goto err_exit
sts = %ssc_move(dbchn, cur1)          ;Fetch data into defined variable.

If the SQL statement returns more than one row of data, put the %SSC_MOVE call in a loop. For example:

sqlp="SELECT deptnum, deptname, hrdate, salary FROM org1 
;     WHERE division=:1"
if (%ssc_open(dbchn, cur1, sqlp, SSQL_SELECT,, 1, a_bind_var))
  goto err_exit
if (%ssc_define(dbchn, cur1, 4,a_deptnum, a_deptname, a_hrdate, 
;               a_salary))            ;Map variables to columns.
  goto err_exit
do forever
  begin
    sts = %ssc_move(dbchn, cur1, 1, rowcnt)    ;Fetch a row of data into
.                                              ; defined variables.
.
.

Binding data

For data that’s sent to the database, you can use bind variables to bind the data, rather than hard-coding the data in the SQL statement. You can bind

data that updates the database—for example, data in the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement.
data that’s part of the selection criteria—for example, data in a WHERE or LIKE clause.

Binding enables the database to prepare an SQL statement once and then reuse the prepared statement as many times as your program sends new data for the statement. (For information on resubmitting a statement after updating bind variables, see %SSC_REBIND.)

When you bind data, you map the data with a one-to-one mapping method (one Synergy DBL variable holds one column of data in the database row) on a column-by-column basis.

Binding takes place when the query is executed.

For SELECT statements happens with %SSC_OPEN or %SSC_REBIND.
For non-SELECT statements happens with %SSC_EXECUTE or %SSC_EXECIO.

For example, if you bind the host variable ordnum to a SELECT statement and ordnum equals 2 when %SSC_OPEN is called, the SELECT statement will use 2 for the query even if the ordnum value is changed to 3 before %SSC_MOVE is called. However, for a non-SELECT statement, the value 3 would be used because the variable ordnum is evaluated by %SSC_EXECUTE, which would follow %SSC_MOVE.

When submitting a non-select statement or resubmitting a select statement (%SSC_REBIND), if binding occurs in a different routine (after returning from the routine with the %SSC_OPEN or %SSC_BIND), do not use non-static records or local literals for bind variables, and do not use statement literals for %SSC_OPEN or %SSC_BIND variables (which would effectively be local literals). By the time the bind occurs, a non-static record or a local literal may no longer be in memory (due to segment reclamation) or may be overwritten, causing a segmentation fault or bad data.

Moreover, using the Synergy routine call block API to build a statement that calls %SSC_OPEN or %SSC_BIND may cause binding issues because literals are copied to memory for the call block and are lost once the handle for the statement is closed or goes out of scope (by leaving the routine). Statements that subsequently bind data will not have access to lost variables, which can result in a segmentation fault.

Specifying bind variables

To use a bind variable, put a placeholder (described below) in the SQL statement for each database parameter and pass a bind variable in an %SSC_OPEN, %SSC_BIND, or %SSC_SQLLINK call.

With %SSC_OPEN, you can bind variables for SELECT and non-SELECT statements.
With %SSC_BIND and %SSC_SQLLINK, you can bind variables only for non-SELECT statements.
If you have linked a statement to a SELECT cursor (using %SSC_SQLLINK), a subsequent call to %SSC_STRDEF binds variables for the original SELECT statement, and a subsequent call to %SSC_BIND binds variables for the linked statement.

For example, the following INSERT statement has six placeholders in the VALUES clause, and the %SSC_OPEN call has six bind variables that correspond to the placeholders: “:1” is a placeholder for deptnum, and “:2” is a placeholder for deptname, and so forth. If necessary, the statement could also have a WHERE clause with additional bind variables. Do not put placeholders in the column list.

    sqlp = "INSERT INTO ORG1 (DEPTNUM, DEPTNAME, MANAGER, DIVISION, "
&    "HRDATE, SALARY) VALUES (:1,:2,:3,:4,to_date(:5,'MM/DD/YYYY'),:6)"
    if (%ssc_open(dbchn, cur1, sqlp, SSQL_NONSEL, SSQL_STANDARD, MX_VARS,
&                 deptnum, deptname, manager, division, hrdate, salary))
      goto err_exit

For Oracle databases, the placeholder numbers determine the order in which the variables are used. For other databases, bind variables are used in the order they are specified in the %SSC_OPEN, %SSC_BIND, or %SSC_SQLLINK call; the placeholder number does not affect the order for these databases, but it is required. For placeholder numbers, use consecutive numbers starting with 1 and do not duplicate a number.

You can also use %SSC_STRDEF to bind (and define) variables for SELECT and non-SELECT statements. When possible use %SSC_OPEN, %SSC_BIND, or %SSC_SQLLINK, but for SELECT statements, you cannot bind more than 248 variables unless you use %SSC_STRDEF. And for non-SELECT statements, you cannot bind more than 252 variables unless you use %SSC_STRDEF or use multiple %SSC_BIND calls with 252 or less bind variables in each call.

If you use a bind variable to hold an operand for a LIKE clause, be sure to use a database trim function to trim trailing spaces. (For example, use RTRIM with SQL Server or TRIM with Oracle.) If you don’t, the trailing spaces will be evaluated as part of the LIKE clause. For example, if the bind variable is an a10, for example, and the LIKE clause operand is “a%”, the LIKE will specify a match for a% plus eight trailing spaces. In this case, for example, “anderson” would be overlooked.