ODBC conversions
The ODBC applications will use CHAR, DATE, TIME, or TIMESTAMP as the SQL type to bind and retrieve DATE type2 values.
The possible conversion from C datatype to SQL datatype and vice-versa are listed in the following table:
C Datatype | SQL Datatype |
SQL_C_TYPE_DATE or SQL_C_TYPE_TIME or SQL_C_TYPE_TIMESTAMP |
SQL_CHAR |
SQL_VARCHAR |
|
SQL_LONG_VARCHAR |
|
SQL_TYPE_DATE |
|
SQL_TYPE_TIME |
|
SQL_TYPE_TIMESTAMP |
C Datatype | SQL Datatype |
SQL_C_TYPE_DATE or SQL_C_TYPE_TIME or SQL_C_TYPE_TIMESTAMP |
SQL_WCHAR |
SQL_WVARCHAR |
|
SQL_WLONG_VARCHAR |
|
SQL_TYPE_DATE |
|
SQL_TYPE_TIME |
|
SQL_TYPE_TIMESTAMP |
-
If the SQLBindParameter ODBC API C type is SQL_C_TYPE_DATE and SQL type is SQL_DATE, the date value is inserted into DATE type2 column. Because time is not part of date, the default time will be inserted into SQL/MX. For example, if the application binds 2016-11-04 from DATE column, the value inserted into SQL/MX is '2016-11-04 00:00:00 A.M.'.
-
If the SQLBindParameter ODBC API C type is SQL_C_TYPE_TIME and SQL type is SQL_DATE, time value is inserted into DATE type2 column. Because date is not part of time, the default date will be inserted into SQL/MX. For example, if the application binds 10:58:59 for TIME column, the value inserted into SQL/MX is '2016-11-01 10:58:59 A.M.' , where 2016-11-01 represents the first day of the current month.
-
If the SQLBindParameter ODBC API C type is SQL_C_TYPE_TIMESTAMP and SQL type is SQL_DATE, timestamp (0) value is inserted into DATE type2 column. For example, if the application binds 2016-08-23 11:38:49.23145 from TIMESTAMP column, the value inserted into SQL/MX is '2016-08-23 11:38:49 A.M.'.
-
While retrieving results with SQLBindCol or SQLGetData ODBC API, if applications set the target type of DATE type2 column with SQL_C_TYPE_DATE to copy the DATE value into the application buffer. For example, if the inserted value is 2016-11-04 11:12:30 A.M, since the application binds with DATE, the value retrieved is '2016-11-04'.
-
While retrieving results with SQLBindCol or SQLGetData ODBC API, if applications set the target type of DATE type2 column with SQL_C_TYPE_TIME to copy the TIME value into the application buffer. For example, if the inserted value is 2016-11-10 10:58:59 A.M, since the application binds with TIME, the value retrieved is '10:58:59'.
-
While retrieving results with SQLBindCol or SQLGetData ODBC API, if application set the target type of DATE type2 column with SQL_C_TYPE_TIMESTAMP to copy TIMESTAMP value into the application buffer. For example, if the inserted value is 2016-08-23 10:58:59 A.M, since the application binds with TIMESTAMP, the value retrieved is '2016-08-23 10:58:59'.
JDBC conversions
The JDBC applications can use setObject(), setDate(), setString(), setTime() and setTimeStamp() methods to bind the values and getObject(),getDate(), getTime(), getTimeStamp(), and getString() methods to retrieve the values. DATE type2 is mapped to java.sql.Timestamp object in Java. If the setTime() method is called to bind the DATE type2 column, the default date value is 1970-01-01. If the setDate() method is called to bind the DATE type2 column, the default time value is 00:00:00 AM.
The ResultSetMetadata method will display the new DATE type2 column type when DC_MODE is set to 1.
Object Type-Example Value |
Value stored in SQL/MX |
java.sql.Date - 2016-09-23 |
2016-09-23 00:00:00 |
java.sql.Time - 00:21:56 |
1970-01-01 00:21:56 |
java.sql.Timestamp - 2016-09-23 17:03:50 |
2016-09-23 17:03:50 |
Example Value from SQL/MX |
Get method - Value Displayed |
2016-09-23 17:03:50 |
getObject() - 2016-09-23 17:03:50.0 |
2016-10-25 00:00:00 |
getDate() - 2016-10-25 |
2016-11-23 00:21:56 |
getTime() - 00:21:56 |
2016-09-23 17:03:50 |
getTimeStamp() - 2016-09-23 17:03:50.0 |
2016-10-25 17:10:56 |
getString() - 2016-10-25 17:10:56 |