Date: 06/04/2002
Version: 9.40
This document describes feature and performance topics not covered in the IBM Informix ESQL/C Programmer's Manual or modified since publication.
This manual might not reflect the latest names of Informix database servers. The following table lists the database server versions and their corresponding names.
Informix Dynamic Server and Informix Internet Foundation 2000, version 9.21, support XA transactions in a tightly-coupled mode, which allows you to use Microsoft Transaction Server as a transaction manager in the TP/XA environment.
You can use Microsoft Transaction Server only with a version of the Informix ODBC Driver that is current with or later than version 9.21 of the Informx database server.
Prior to this release, both the database server and ESQL/C required all datetime strings to be formatted using ANSI format 'YYYY-MM-DD HH:MM:SS.FFFFF' when converting a date-time string to the datetime data type, even if other formats were specified using the DBTIME, GL_DATETIME, or locale environment variables. When an ESQL/C program fetched a datetime value from the database server into a string host variable, it converted it using ANSI format. When the client converted a string to datetime type, which occurs only for insert cursors when the host variable type is a string and the target column is datetime, it requires the string be in ANSI format. This limitation makes it impossible to upgrade from Informix ALS (Asian Language Support) client/server products, which recognize non-ANSI datetime formats, to GLS (Global Language Support) client/server products.
This release of Informix ESQL/C implements support for non-ANSI datetime formats, which enables both ESQL/C programs and the database server to convert them correctly.
To prevent the problem of backward compatibility this release of ESQL/C provides a new environment variable, USE_DTENV, to activate this feature. When USE_DTENV enables support of Non-ANSI datetime formats, both the ESQL/C program and the database server will expects and produce datetime strings in the format specified by the following environment variables, in order of precedence:
You can find these precedence rules in the Informix Guide to GLS Functionality, under the heading "Customizing Date and Time End-User Formats".
The environment variable is passed from the ESQL/C program to the database server. Setting this environment variable for the database server alone will have no affect. You must set it for the ESQL/C client program, which passes it to the database server.
To enable support for non-ANSI datetime formats, set USE_DTENV to 1 as shown in the following example:
If the database server does not support non-ANSI datetime formats you should not set USE_DTENV for the ESQL/C client program. Doing so makes it possible for the ESQL/C program to fetch a datetime value into a string host variable and convert it to a non-ANSI formatted string. The program would not be able to bind this string to a datetime column, however, if the database server subsequently tries to convert the string to datetime because the database server expects ANSI format. The client would be able to convert strings to datetime (insert cursors) but because users are not normally aware when the client or the server performs the conversion, you should not set USE_DTENV when the database server does not support non-ANSI datetime formats.
Both the client and server convert a datetime value to a string and convert date-time strings to datetime type values using the format specified by the GLS environment variable when the following conditions are true:
This applies to DB-Access load and unload commands and to the dbimport, dbexport and dbload utilities.
Note: The USE_DTENV feature does not affect the interval data type.
An insert into this datetime column will fail with this DBTIME settings. It's missing the hour, minute, second.
The fix for this would require additional datetime formats for EACH qualifier possibility, so the correct format could be found for each qualifier. Example in no special order:
3. For datetime to string conversions, if the datetime qualifier doesn't match the datetime format then we follow these rules:
Example (datetime value has missing greater datetime fields):
The current requirement is that all datetime strings must be in ANSI format. This makes it impossible for users who wish to upgrade from Informix ALS client/server to GLS client/server products. ALS client/server products recognized non-ANSI datetime formats.
Both client and server currently support non-ANSI date formats, but not datetime. With this feature Informix will no longer discriminate against non-ANSI datetime formats.
There's no affect on existing code, but there is an affect on old data files generated by DB-Access or the database administrator tools. These files will have datetime values as ANSI formatted strings. You should not set USE_DTENV when loading this data with a client and server that both support non-ANSI datetime formats.
When USE_DTENV is set and one of the GLS datetime environment variables specifies a different datetime string format, DB-Access displays can be affected.
The ANSI format is 25 characters. If the format specified through one of the GLS datetime environment variables is less than or greater than the maximum length of the 25 character ANSI standard datetime literal, the DB-Access display will be affected.
Example:
The deccvflt() function has inadvertently gone undocumented in previous versions of the ESQL/C manual.
The deccvflt() function converts a C float type number into an ESQL/C decimal type number.
int deccvflt(flt_val, dec_val)
float flt_val;
dec_t *dec_val;
flt_val is the float value that deccvflt() converts to a decimal type value.
dec_val is a pointer to a decimal structure where deccvflt() places the result of the conversion.
0 The conversion was successful.
<0 The conversion failed.
ExampleOutput
The ifx_var_freevar() function frees the memory that has been allocated for the var binary and lvarchar pointer host variables.
int ifx_var_freevar(var_bin)
var binary *var_bin;
var_bin is the address of the "var binary" or "lvarchar pointer" host variable.
Whenever you have a "var binary" or "lvarchar pointer" host variable, as shown in the following example, you must explicitly free the memory that is allocated for it by using the ifx_var_freevar() function.
The following example illustrates the use of ifx_var_freevar() you must explicitly free memory that has been allocated for these "var binary" & "lvarchar pointer" host variables with the ifx_var_freevar() function:
If you do not call ifx_var_dealloc() to deallocate memory that has been allocated for the data buffer of the "var binary" host variable, "ifx_var_freevar()" would do it for you and then it frees the memory of the "var binary" & "lvarchar pointer" host variables. After "ifx_var_freevar()" was called, "poly" and "c" would be set to null.
0 The function was successful.
<0 The function was not successful and the return value indicates the cause of the error.
ifx_var_flag(), ifx_var_alloc(), ifx_var_dealloc()
Decimal precision for FLOAT and SMALLFLOAT conversions to DECIMAL data type has been increased from 8 (SMALLFLOAT) and 16 (FLOAT) to 9 and 17, respectively. You might experience differences in decimal precision after FLOAT/SMALLFLOAT to DECIMAL conversions. The functions deccvflt() (SMALLFLOAT to DECIMAL conversion) and deccvdbl() (FLOAT to DECIMAL conversion), which have converted float numbers to decimal numbers with 8 digit and 16 digit precision, now have 9 digit and 17 digit precision.
For more information on the impact and scope of this feature, refer to the Release Notes file for this release of ESQL/C. The following section describes the IFX_USE_PREC_16 environment variable, which provides backward compatibility.
A new environment variable IFX_USE_PREC_16 can be used to revert the precision to 8/16 for all SMALLFLOAT/FLOAT to DECIMAL conversions by a client ESQL/C program. Note that this environment variable affects the client only and is not sent to the server.
When IFX_USE_PREC_16 is set to 1, a 9/17 client product will only use 8/16 precision to perform all SMALLFLOAT/FLOAT to DECIMAL conversions.
The environment variable IFX_NOZEROMDY has been added to change the default behavior of the DATE data type and several ESQL/C date related library functions. If IFX_NOZEROMDY is not set (the default), the date value 00/00/0000 (4 digit year) is accepted as a null date in a DATE data type or as input to the ESQL/C library functions rdefmtdate(),and rstrdate(). The function rmdyjul() also treats the input date as a null date if all 3 fields in the mdy input array are all zeros. If IFX_NOZEROMDY is set to 1, the date value 00/00/0000 and mdy input array fields of all zeros will be treated as an invalid date.
For more information, refer to the Release Notes for this release of ESQL/C.
Chapter 13 of the INFORMIX-Informix ESQL/C Programmer's Manual, Version 9.2, states the following:
"The DCE thread library, which the Open Software Foundation(OSF) developed creates a standard interface for thread-safe applications."
If DCE is not available on your platform, ESQL/C can use POSIX thread libraries or Sun Solaris thread libraries as well.
If your platform supports DCE, POSIX or Solaris thread packages, you must install them on the same client computer as ESQL/C.
SOL and POSIX are also valid options for the THREADLIB environment variable.
Informix ESQL/C allows developers to specify the runtime context that will be used for a set of statements. A runtime context will hold all the thread specific data that ESQL/C must maintain, ie. connections and their current states, cursors and their current states etc.
The feature allows ESQL/C programmers to improve the performance of their MESQL/C applications. By using the SQLCONTEXT definitions and directives, the number of thread-specific data block lookups can be reduced.
The following embedded SQL statements support the definition and usage of runtime contexts:
The SQLCONTEXT definition and statements are only recognized when the esql -thread option is used. If the -thread option is not specified, the statements are ignored.
The use of the SQLCONTEXT statements will cause the ESQL/C preprocessor to generate code in the .c file that differs from the generated code when no SQLCONTEXT statements are present.
SQLCONTEXT context_var;
PARAMETER SQLCONTEXT param_context_var;
BEGIN SQLCONTEXT OPTIMIZATION;
END SQLCONTEXT OPTIMIZATION;
The END SQLCONTEXT directive should appear before the end of the scope of the SQLTCONTEXT definition in use or compile time errors will occur for "undefined symbol sql_context_var".
The Informix ESQL/C Programmer's Manual currently says, "you must explicitly deallocate memory that has been allocated to the var binary host variable." This statement should be changed to say, "you must explicitly deallocate memory that has been allocated to the data buffer of a var binary host variable."
The Informix ESQL/C Programmer's Manual currently says, "you must explicitly allocate memory for the data of a var binary host variable with the ifx_var_alloc() function." This statement should be changed to say, "you must explicitly allocate memory for the data buffer of a var binary host variable with the ifx_var_alloc() function."
The Informix ESQL/C Programmer's Manual has two tables in Chapter 3 that currently say:
SQL Data Type | ESQL/C or C Data Type |
---|---|
CHAR (n) | fixchar array [ n] or |
LVARCHAR | lvarchar |
VARCHAR (m,x) | varchar [ m + 1] or |
The LVARCHAR entry should be changed to say,
SQL Data Type | ESQL/C or C Data Type |
---|---|
LVARCHAR |
lvarchar array [ n + 1]
where "n" is the length of the longest string that might be stored in the LVARCHAR field. |
In Chapter 3 of the TP/XA Programmer's Manual, Version 9.13, in the section, "Building Servers for an X/Open DTP Environment", step 3 says:
"3. Link the object file created in step 1 with the TP/XA library called $INFORMIXDIR/lib/esql/libinfxxa.a"
The name of this library was changed in Version 9.13. The name of the libraries Version 9.13 are libifxa.a (non-threaded) or libthxa.a (threaded).
You can use the SqlFreeMem() function to tell ESQL/C when memory can be free. The prototype is:
void FAR PASCAL SqlFreeMem( void far * MemAddr, int FreeType );
MemAddr is the address of the memory to be freed.
FreeType is the type of memory to be freed and can have the following values:
free memory allocated to a string, i.e. a string returned by dececvt() or decfcvt().
free a sqlda structure returned by a $describe (into sqlda) statement
(MemAddr is ignored.) Free information in the application information block that cannot be freed on sqlexit (or exit). This frees all dynamically allocated cursors, memory pointed to by those cursors, and the tables that keep track of the cursors. It frees only for the current connection. If the user has done one or more GetConnect calls, the user must do a SetConnect and SqlFreeMem for each connection (including the default connection). This call does not free allocated descriptors that the user could have, but did not, free with a $deallocate descriptor.
NOTE 1: Do a CONN-FREE before issuing sqlexit().
NOTE 2: Doing a CONN-FREE frees memory that the ESQL/C program may have saved the address of. See the next section for how the keep this from happening.
The DECLARE and PREPARE statements allow you to specify cursor ids and statement ids either statically (with hard-coded named strings) or dynamically (with a host variable that contains the cursor name or statement id). If your ESQL/C application reuses cursors that have been named statically, you may experience memory-leak problems. If you use SqlFreeMem (NULL, CONN), this frees all of the dynamically allocated cursors and trying to use the saved address will result in a GPF if the freed memory is re-used.
To avoid problems, use dynamic ids. That is, use host variables to hold the cursor names and statement is in DECLARE and PREPARE.