Data conversion when binding and defining

SQL Connection automatically converts Synergy data types to database‑specific data types when data flow is from a Synergy application to the database. And database‑specific data types are converted into Synergy data types when data flow is from the database to a Synergy application. Note the following:

Data conversion when binding

When binding host variables to database columns, SQL Connection makes the following data conversions:

Binding Synergy DBL host variables…

…to database columns

Alpha

Binary, char, date, datetime, varchar

Decimal

Numeric

Implied decimal

Float, numeric

Integer

Integer, numeric

System.String

Binary, char, date, datetime, varchar

Note the following:

Data conversion when defining

When loading database columns to defined host variables, SQL Connection makes the following conversions:

Loading database columns…

…to defined Synergy DBL host variables

Binary

Alpha, System.String

Char

Alpha, System.String

Currency

Implied decimal

Date

Alpha, decimal, integer, System.String

Datetime (including datetime derivatives, such as DATETIMEOFFSET for SQL Server)

Alpha, System.String

Double

Implied decimal

Float

Implied decimal

Integer

Decimal, integer

Number

Decimal, implied decimal, integer

Numeric

Decimal, implied decimal

Time

Alpha, System.String

Timestamp

Alpha, System.String

Varchar

Alpha, System.String

Note the following:

Using %SSC_INDICATOR when updating a column with null

When binding a char, date, datetime, numeric or float column with null, you can use %SSC_INDICATOR to determine if the column is currently null. You can then use this information to determine if the update value should be stored as null.

  1. Initialize SQL Connection (%SSC_INIT), connect to the database (%SSC_CONNECT), and start the transaction (%SSC_COMMIT), as illustrated in the diagrams in Function call flows.
  2. Open a cursor with SSQL_SELECT and SSQL_POSITION, and set up the SELECT statement, binding variables as necessary.
  3. Define variables.
  4. Set up the update statement with %SSC_SQLLINK, binding variables as necessary.
  5. Use %SSC_INDICATOR to record null status for retrieved columns.
  6. Fetch data with %SSC_MOVE.
  7. Execute the update statement with %SSC_EXECUTE (SSQL_STANDARD).
    Test int_array elements (returned from the %SSC_INDICATOR call) to determine if database columns contain null values before the update. If a column is null and the bind variable for the column is blank and alpha or zero and decimal or implied decimal, set the first character in the bound field to %CHAR(0), which is binary zero. This instructs the database store the value as null.
  8. If there are more rows, fetch data (%SSC_MOVE) and then execute update (%SSC_EXECUTE) in a loop.
  9. Rebind and close the cursor and release the connection and commit the transaction as necessary. See Bulk update for information.

 

Converting dates and times

Date and time columns are defined differently for different databases. For example, in Oracle and Synergy databases, dates have the date data type. In SQL Server, dates and times have the datetime data type. When you’re using SQL Connection functions to write to the database, the SQL statements you pass must use the correct formats and commands for the database. Unfortunately, there are no standard commands for this. For example, for Oracle and Synergy databases, you use the TO_DATE() or CAST() functions, and for SQL Server you use the CONVERT() function (unless you’re using a d8 variable with the YYYYMMDD format, as discussed below).

On the other hand, when you’re using SQL Connection functions to read dates and times from the database, these functions pass a date and/or time to your application that’s been converted to either an alpha value (if it’s passed to an alpha variable) or a Julian date value (if it’s passed to a numeric variable).

What if your application uses d6 or d8 variables for dates? Because SQL Connection functions convert returned dates to Julian date values for numeric variables, you won’t be able to use d6 or d8 variables in SQL Connection functions to directly receive dates unless you want to use the Synergy DBL Julian routines. Otherwise, you’ll need to cast these numeric fields as alpha fields. For example, the following sets date_field to eight digits of the Julian date value if date_field is defined as a d8:

sts = %SSC_DEFINE(dbchn, cur1, 1, date_field)

The following, however, casts the date as an alpha field:

sqlp = "SELECT or_odate FROM orders WHERE or_number=3"
If (%ssc_open(dbchn, cur1, sqlp, SSQL_SELECT))
    goto err_exit
sts = %SSC_OPTION(dbchn, SSQL_GETOPT, date_base, date_format, null)
date_format = "DDMMYYYY"
sts = %SSC_OPTION(dbchn, SSQL_SETOPT, date_base, date_format, null)
sts = %SSC_DEFINE(dbchn, cur1, 1, ^A(date_field))

In this case, if the retrieved date is February 6, 1958, date_field will be set to 06021958. To then write this value back to a database, you could do something like the following for Oracle or SQL Server:

sts = %SSC_OPEN(dbchn, cur1, "insert into orders(or_date) where 
&               or_number=3 values(to_date(:1, "DDMMYYYY")", 
&               SSQL_NONSEL, SSQL_STANDARD, 1, ^A(date_field))

When writing to an Oracle or SQL Server database, if your program uses a d8 variable and the YYYYMMDD format for the date, you don’t need to use TO_DATE() or CONVERT() to write the date to the database. (The YYYYMMDD format is the default for these databases.) You will, however, need to convert the d8 into an alpha with the ^A() function.

For a date conversion example, see exam_create_table.dbl in the connect\synsqlx subdirectory of the main Synergy/DE installation directory.

Using numeric database columns

To maximize the portability of your code to various databases, we recommend using the numeric type for columns when writing CREATE TABLE statements. Creating a database column as numeric will ensure that the column will map to a database data type suitable for commercial data storage equivalent to at least a d28.10. Some databases also allow integer storage. SQL Connection will translate between the database numeric data types and the Synergy DBL variables, whether they are integer, decimal, or implied decimal.