Data conversion when binding and defining

This topic includes the following sections:

Data conversion when binding
Data conversion when defining
Using %SSC_INDICATOR when updating a column with null
Converting dates and times
Using numeric database columns

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

For more information on binding and defining (data mapping), see Using variables to map data. For information on binding and defining large binary or character columns greater than 65,535 bytes, see the %SSC_LARGECOL Discussion. (Columns greater than 65,535 bytes are not supported in %SSC_EXECIO or %SSC_STRDEF. )
Date-to-numeric conversions result in Julian date values that are compatible with %NDATE.
If you store implied-decimal values as whole numbers (programmatically handling the decimal point), you must use ^D to convert variables that store the whole numbers into the correct implied-decimal format for sending data to and receiving data from the database. If you don’t, only the whole number part of a decimal value will be stored. For example, if you use a d10 variable (instead of a d10.2) for a currency field, the value 100.02 will be truncated to the whole number 100.

Data conversion when binding

When binding host variables to database columns, SQL Connection makes data conversions shown in the following table. For more detailed conversion information, see Appendix A: Data Type Mapping for SQL Connection.

Synergy DBL host variable type	Database column type
Alpha	Binary, char, date, datetime^a, time, timestamp, varchar
Decimal	Data, integer, number, numeric
Implied decimal	Currency, double, float, number, numeric
Integer	Date, integer, number, numeric
System.String^b	Binary, char, date, datetime^a, time, timestamp, varchar

a. This includes datetime derivatives, such as DATETIME OFFSET for SQL Server.

b. System.String bind variables are not supported for array-based operations. For non-array operations, SQL Connection can convert data in System.String bind variables to char, date, datetime, time, timestamp, varchar, or (when using %SSC_LARGECOL) binary. When a string variable is bound to a select statement, a string larger than 65,535 is supported without the use of %SSC_LARGECOL.

Note the following:

For alpha host variables, fields that start with a binary 0 become null. Depending on the database, data for alpha host variables may be converted in other cases as well. For example, a blank field or a field filled with spaces may be converted to null or to a single space. It may also remain as is. And a field with trailing blanks may be trimmed for varchar. For information on an option that controls the way Oracle treats data for alpha host variables, see SSQL_TRIMCHAR.
For decimal and implied decimal host variables, blanks become zeros, ASCII zeros remain zeros, and host variables that start with binary zero become null values.
For integer host variables, binary zeros remain binary zeros. There is no way to store a null value using a bound integer field.
For binary columns for SQL Server or Oracle, %SSC_EXECIO treats the data as a char field and trims trailing spaces, unless you can use the SSQL_EXBINARY option. (If you use the SSQL_EXBINARY option, %SSC_EXECIO uses the given data and length.) %SSC_MOVE always preserves binary column data.
To bind a null value to a char, date, datetime, numeric, or float database column, set the first character position of your alpha or decimal field to binary zero with %CHAR(0). If you are using a decimal field, you will need to use ^A to cast it as an alpha. (You cannot store a null value using a bound integer field.) See Using %SSC_INDICATOR when updating a column with null for a method for doing this, and remember to reset the column to its original value before using it with anything other than an %SSC_ function.

Data conversion when defining

When loading database columns to defined host variables, SQL Connection makes conversions shown in the following table. For more detailed conversion information, see Appendix A: Data Type Mapping for SQL Connection.

Database column types	Synergy DBL host variable types
Binary	Alpha, System.String^b
Char	Alpha, System.String^b
Currency	Implied decimal
Date	Alpha, decimal, integer, System.String^b
Datetime^a	Alpha, System.String^b
Double	Implied decimal
Float	Implied decimal
Integer	Decimal, integer
Number	Decimal, implied decimal, integer
Numeric	Decimal, implied decimal
Time	Alpha, System.String^b
Timestamp	Alpha, System.String^b
Varchar	Alpha, System.String^b

a. Datetime types include datetime derivatives, such as DATETIME OFFSET for SQL Server.

b. System.String define variables are not supported for array-based operations. For non-array operations, SQL Connection can move the data in char, date, datetime, time, timestamp, varchar, or (when using %SSC_LARGECOL) binary columns to System.String define variables.

If a Synergy DBL host variable is not large enough to hold the data value assigned to it, the data value will be truncated. The original size of the data value can be determined using %SSC_INDICATOR.

You must use %SSC_INDICATOR to determine if a fetched column was returned with a null value.

Note the following:

For alpha host variables, nulls become blanks.
When using an alpha variable to receive data from a datetime column with microsecond precision, make sure the format string for %SSC_OPTION includes the UUUUUU mask.
For binary columns, %SSC_EXECIO converts binary zeros to spaces and trims trailing spaces, unless you pass the SSQL_EXBINARY option. (If you use the SSQL_EXBINARY option, %SSC_EXECIO uses the data as is.) %SSC_MOVE, on the other hand, always preserves binary zeros when retrieving data from binary columns.
For decimal, implied decimal, and integer host variables, nulls become zeros.

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.

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.
Open a cursor with SSQL_SELECT and SSQL_POSITION, and set up the SELECT statement, binding variables as necessary.
Define variables.
Set up the update statement with %SSC_SQLLINK, binding variables as necessary.
Use %SSC_INDICATOR to record null status for retrieved columns.
Fetch data with %SSC_MOVE.
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.
If there are more rows, fetch data (%SSC_MOVE) and then execute update (%SSC_EXECUTE) in a loop.
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).

If a date or time value is converted to an alpha value, you can use the %SSC_OPTION function to set the date/time mask, which specifies the format for the date or time. (The default is DD-MON-YYYY.) Unfortunately, there is only one date/time mask in effect at a time, so if you have different values (e.g., a date and a time in the same statement), only one format of alpha field can be returned. To work around this, you have to use the database conversion and mask functions in the SQL statement to format the returned field.
If a date or time value is converted to a Julian date value, the value is based on the SQL Connection Julian base date. You can get and set the Julian base date with the %SSC_OPTION function (though we don’t recommend changing this value). For more information on the Julian base date and the date/time format mask, see %SSC_OPTION.

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.

The following casts the date as an alpha field and shows a way of retrieving a time from the same statement:

record
date_file,   d8        ;DDMMYYYY
my_time,     d4        ;hhmmss
.
.
.
sqlp = "SELECT or_odate , time_format(dbtime,"%H%i%s") FROM mytable 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), ^a(my_time))

In this case, if the retrieved date is February 6, 1958, date_field will be set to 06021958. The my_time field is correctly formatted from the database time field HHMMSS. 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.