Binding Timestamps: Time Zones seem to get deleted

[KarlKeiser]

I want to store a timestamp with time zone in a database and retrieve it again. The retrieving part is working, however submitting the timestamp causes a problem. It only works correctly when I give the timestamp as a string in the SQL query. When I try to bind it instead, the time zones get deleted.

So this is working:

const char * insertSQL = "INSERT INTO timezones VALUES(1, TIMESTAMP '2003-01-02 3:44:55.66 +7:08')";
dpiConn_prepareStmt(conn, 0, insertSQL, strlen(insertSQL), NULL, 0, &insertStmt);
dpiStmt_execute(insertStmt, DPI_MODE_EXEC_DEFAULT, NULL);

And this isn't:

const char * bindSQL = "INSERT INTO timezones VALUES(2, :A)";
dpiConn_prepareStmt(conn, 0, bindSQL, strlen(bindSQL), NULL, 0, &bindStmt);
dpiData bindData;
dpiData_setTimestamp(&bindData, 2003, 1, 2, 3, 44, 55, /*fsec*/660000000, /*tzHour*/7, /*tzMin*/8);

Click here for a gist that (hopefully) reproduces the problem.

In short, it does the following:

• Establishes context and connection
• Sets the timezone using ALTER SESSION to something like 10:55
• Makes a table, insert a timestamp directly
• Makes a dpiTimestamp, inserts it using binds
• Prints the dpiTimestamp that was inserted
• Fetches the dpiTimestamp for both queries and prints them

This is the result:

Binding timestamp:     2003-01-02 03:44:55.660000000 +07:08
Result timestamp [1]:  2003-01-02 03:44:55.660000000 +07:08  -  This is the timestamp that was inserted directly
Result timestamp [2]:  2003-01-02 03:44:55.660000000 +10:55  -  This is the timestamp that was bound

The directly inserted timestamp has the timezone +07:08, as specified in the SQL query. The bound timestamp, however, lost that timezone and instead got the +10:55 timezone we set for the session earlier.

What I believe to be the problem

The bind is done like this:

dpiStmt_bindValueByPos(bindStmt, 1, DPI_NATIVE_TYPE_TIMESTAMP, &bindData);

Here, DPI_NATIVE_TYPE_TIMESTAMP has to be given, there seems to be no other option for timestamps. Internally, dpiStmt_bindValueByPos then passes that to dpiStmt__createBindVar (in dpiStmt.c). Here, this code is on line 370:

case DPI_NATIVE_TYPE_TIMESTAMP:
            oracleTypeNum = DPI_ORACLE_TYPE_TIMESTAMP;
            break;

If we change that DPI_ORACLE_TYPE_TIMESTAMP to DPI_ORACLE_TYPE_TIMESTAMP_TZ, then the program generates the correct output:

Binding timestamp:     2003-01-02 03:44:55.660000000 +07:08
Result timestamp [1]:  2003-01-02 03:44:55.660000000 +07:08  -  This is the timestamp that was inserted directly
Result timestamp [2]:  2003-01-02 03:44:55.660000000 +07:08  -  This is the timestamp that was bound

It appears that DPI_ORACLE_TYPE_TIMESTAMP discards the time zone info. Perhaps there was supposed to be a functionality where it recognizes that the column is defined as TIMESTAMP(9) WITH TIME ZONE and gives the according internal datatype. Of course I'd also be very happy about a DPI_NATIVE_TYPE_TIMESTAMP_TZ type specifier. :)

(or perhaps I'm completely misunderstanding how this functionality works)

Answer the following questions:

1. What version of ODPI-C are you using (see dpi.h)?

Version 3.0.0

2. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?

dpiStmt_bindValueByPos seems to be causing the issue

3. What error(s) you are seeing?

The wrong time zone is stored, this can be seen both by fetching it with ODPI and by viewing the table in sqlplus

4. What OS (and version) is your application executing on?

Microsoft Windows 10 Home 10.0.17134 Build 17134

5. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where it is installed?

instantclient_18_3, installed in C:\Oracle, downloaded from Oracle's site

6. What is your Oracle Database version?

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

7. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to? On macOS, what is in ~/lib?

C:\Oracle\instantclient_18_3

8. What environment variables did you set? How exactly did you set them?

INSTANT_CLIENT_LIB_PATH = C:\Oracle\instantclient_18_3

9. What compiler version did you use?

gcc.exe (x86_64-posix-seh-rev0, Built by MinGW-W64 project) 8.1.0

Also getting the problem with

Microsoft (R) C/C++ Optimizing Compiler Version 19.15.26726 for x64

[anthony-tuininga]

The reason you are seeing this is that dpiStmt_bindValueByPos() and dpiStmt_bindValueByName() only accept the native type (by design). They do not accept the Oracle type and simply use the default Oracle type (which as you noted doesn't include the time zone for DPI_NATIVE_TYPE_TIMESTAMP). If you want to bind a timestamp with time zone you'll need to use the somewhat more complex process of creating a variable and binding it. Like this:

dpiData *bindData;
dpiVar *bindVar;

if (dpiConn_newVar(conn, DPI_ORACLE_TYPE_TIMESTAMP_TZ, DPI_NATIVE_TYPE_TIMESTAMP,
        1, 0, 0, 0, NULL, &bindVar, &bindData) < 0) {
    printf("Failed to create variable!\n");
    return -1;
}
dpiData_setTimestamp(&bindData, 2003, 1, 2, 3, 44, 55, /*fsec*/660000000, /*tzHour*/7, /*tzMin*/8);
if (dpiStmt_bindByPos(stmt, 1, bindVar) < 0) {
    printf("Failed to bind data!\n");
    return -1;
}

[KarlKeiser]

That solved my problem. Thank you very much, Anthony!