2 Replies Latest reply on Sep 11, 2018 3:03 PM by Chris-DataEngines

    OCIDateSetDate is not portable across platforms with different endianness

    Chris-DataEngines

      Hi,

       

      Here is an extract of what we are doing to set a date in the bound memory:

       

      sb2 year = 0;

      ub1 month = 0;

      ub1 day = 0;

       

      Date date(dateVal); //  dateVal numeric representation of a date in our software. The details are unimportant, it is simply a number of days.

      year = date.year();

      month = date.month();

       

      yr = year % 100 + 100;

      cent = year / 100 + 100;

      year = (static_cast<sb2>(cent) << 8) + yr; // Note that this calculation is portable

      OCIDateSetDate((OCIDate *) boundData, year, month, day);

       

      In AIX, if "year" is 2018, boundData will be set with 120 118 in the first 2 bytes and the result will be 2018 in the database which is correct. (Check https://docs.oracle.com/database/121/LNOCI/oci03typ.htm#LNOCI16290 for more information about oracle date internal representation).

      In Windows/Linux, 2018 will be set as 118 120 which is the reverse and will appear as 1820 when loaded in the database. I believe this is because OCIDateSetDate implements the reading of the "year" value with non-portable calculation or casting, maybe using "union" types.

       

      The problem is due to endianness but the OCIDateSetDate should be endianness-agnostic and provide a portable solution.

       

      Kind Regards,

      Chris

        • 1. Re: OCIDateSetDate is not portable across platforms with different endianness
          cj

          Can you give full operating system, Oracle Database and Oracle client details?

          Have you got a runnable testcase - what does 'when loaded' mean?

           

          Have you contacted Oracle Support?

          • 2. Re: OCIDateSetDate is not portable across platforms with different endianness
            Chris-DataEngines

            I have not contacted Oracle support. We have a work-around. Only trying to help you guys fixing the oracle software.

             

            Oracle client 11G R1 (oci.dll: 11.1.0.1) - Server: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

            Client machine: Microsoft Windows 10 Enterprise: Version 10.0.17134 Build 17134

             

            Here is the program you asked. You will need an existing table with one date column containing one record, say 2010-09-10 12:00:00 for example. Modify the code with your user name, password, server, table name etc. The program will update the record in the table with what should be 2019-01-01 12:00:00.

             

            Now run the program on Windows, the record is updated to 1920-01-01 12:00:00.

            Run the same program on AIX and it correctly updates to 2019-01-01 12:00:00.

             

            The year bytes are inverted on Windows, this is because of endianness. The struct defines the year as an sb2 (signed short) in OCIDate but the bytes must be read independently to extract century and year as described on Oracle website page:https://docs.oracle.com/database/121/LNOCI/oci03typ.htm#LNOCI16264 . Unfortunately endianness is not accounted for when doing so and the bytes end up reversed on little endian platforms.

             

            #include "stdafx.h"
            #include <oci.h>
            #include <string>
            #include <vector>

            #define ALENP ub2
            #define INDP sb2
            #define RCODEP ub2

            #define OCI_NOT_NULL 0

            int main()
            {
            //////////////////////////////////////////////////////////////////////////////////
            // Starting Session begin
            //////////////////////////////////////////////////////////////////////////////////

            OCIEnv *tmpEnv;
            sword result = OCIEnvCreate(&tmpEnv, OCI_OBJECT | OCI_THREADED, NULL, NULL, NULL, NULL, 0, NULL);

            ub2 code = OCINlsCharSetNameToId(tmpEnv, (const oratext  *)"WE8MSWIN1252"); OCIEnv *envHandle(0);
            if (code) {
              result = OCIEnvNlsCreate(&envHandle, OCI_OBJECT | OCI_THREADED, NULL, NULL, NULL, NULL, 0, NULL, code,code);
              if (result != OCI_SUCCESS) printf("OCIEnvNlsCreate problem");
            }
            else {
              printf("problem with OCIEnvNlsCreate!\n");
            }

            OCIError *errorHandle;
            result = OCIHandleAlloc((dvoid *)envHandle, (dvoid **)&errorHandle, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
            if (result != OCI_SUCCESS) printf("problem with error handle creation!\n");

            OCIServer *serverHandle_;
            result = OCIHandleAlloc((dvoid *)envHandle, (dvoid **)&serverHandle_, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0);
            if (result != OCI_SUCCESS) printf("problem with server handle creation!\n");

            OCISvcCtx *servCtx_h_;
            result = OCIHandleAlloc((dvoid *)envHandle, (dvoid **)&servCtx_h_, OCI_HTYPE_SVCCTX, (size_t)0, (dvoid **)0);
            if (result != OCI_SUCCESS) printf("problem with server context handle creation!\n");

            OCISession *sessionHandle_;
            result = OCIHandleAlloc((dvoid *)envHandle, (dvoid **)&sessionHandle_, OCI_HTYPE_SESSION, (size_t)0, (dvoid **)0);
            if (result != OCI_SUCCESS) printf("problem with session handle!\n");

            result = OCIAttrSet((void *)servCtx_h_, OCI_HTYPE_SVCCTX, (dvoid *)serverHandle_, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errorHandle);
            if (result != OCI_SUCCESS) printf("problem with setting server attribute!\n");

            std::string userName("youruser");
            result = OCIAttrSet((dvoid *)sessionHandle_, (ub4)OCI_HTYPE_SESSION, (dvoid *)userName.c_str(), (ub4)userName.length(), OCI_ATTR_USERNAME, errorHandle);
            if (result != OCI_SUCCESS) printf("problem with setting user name!\n");

            std::string password("yourpassword");
            result = OCIAttrSet((dvoid *)sessionHandle_, (ub4)OCI_HTYPE_SESSION, (dvoid *)password.c_str(), (ub4)password.length(), OCI_ATTR_PASSWORD, errorHandle);
            if (result != OCI_SUCCESS) printf("problem with setting password!\n");

            std::string path("yourserver");
            result = OCIServerAttach(serverHandle_, errorHandle, (text *)path.c_str(), (sb4)path.length(), 0);
            if (result != OCI_SUCCESS) printf("problem with OCIServerAttach!\n");

            result = OCISessionBegin(servCtx_h_, errorHandle, sessionHandle_, OCI_CRED_RDBMS, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem with OCISessionBegin!\n");

            result = OCIAttrSet((void  *)servCtx_h_, (ub4)OCI_HTYPE_SVCCTX, (void  *)sessionHandle_, (ub4)0, OCI_ATTR_SESSION, errorHandle);
            if (result != OCI_SUCCESS) printf("problem with session attribute!\n");

            //////////////////////////////////////////////////////////////////////////////////
            // Starting Session End
            //////////////////////////////////////////////////////////////////////////////////

            //////////////////////////////////////////////////////////////////////////////////
            //
            // In this test we want to update a date in the database
            //
            //////////////////////////////////////////////////////////////////////////////////

            // Prepare and execute select for update statement
            OCIStmt *stmt1;
            result = OCIHandleAlloc(envHandle, (dvoid **)&stmt1, (ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
            if (result != OCI_SUCCESS) printf("problem with statement handle allocation!\n");
            std::string statement1("SELECT \"YOURDATE\" FROM yourdatetable FOR UPDATE");
            result = OCIStmtPrepare(stmt1, errorHandle, (text *)statement1.c_str(), (ub4)statement1.length(), OCI_NTV_SYNTAX, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem with statement preparation!\n");
            int prefetch = 0;
            result = OCIAttrSet((dvoid *)stmt1, (ub4)OCI_HTYPE_STMT, (dvoid *)&prefetch, (ub4) sizeof(prefetch), OCI_ATTR_PREFETCH_ROWS, errorHandle);
            if (result != OCI_SUCCESS) printf("problem with prefetch attribute!\n");
            result = OCIAttrSet((dvoid *)stmt1, (ub4)OCI_HTYPE_STMT, (dvoid *)&prefetch, (ub4) sizeof(prefetch), OCI_ATTR_PREFETCH_MEMORY, errorHandle);
            if (result != OCI_SUCCESS) printf("problem with prefetch attribute!\n");
            result = OCIStmtExecute(servCtx_h_, stmt1, errorHandle, (ub4)1, (ub4)0, (const OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DESCRIBE_ONLY);
            if (result != OCI_SUCCESS) printf("problem with execute describe!\n");
            int iters = 0;
            result = OCIStmtExecute(servCtx_h_, stmt1, errorHandle, (ub4)iters, (ub4)0, (const OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem with executing statement!\n");

            // Prepare update statement
            OCIStmt *stmt2;
            result = OCIHandleAlloc(envHandle, (dvoid **)&stmt2, (ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
            if (result != OCI_SUCCESS) printf("problem with statement handle allocation!\n");
            std::string statement2("UPDATE yourdatetable SET \"YOURDATE\" = :1 WHERE ROWID = :2");
            result = OCIStmtPrepare(stmt2, errorHandle, (text *)statement2.c_str(), (ub4)statement2.length(), OCI_NTV_SYNTAX, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem with statement preparation!\n");

            // Bind select statement
            OCIDateTime** dateTimeArray = new OCIDateTime*[1];
            result = OCIDescriptorAlloc(envHandle, (dvoid **)&dateTimeArray[0], OCI_DTYPE_TIMESTAMP, 0, 0);
            if (result != OCI_SUCCESS) printf("problem with timestamp descriptor allocation!\n");
            ALENP* alenp = new ALENP[1]; // array of lengths of individual values - only varies for strings
            INDP* indArray = new INDP[1];
            RCODEP* rcodep = new RCODEP[1];   // array of return codes
            alenp[0] = sizeof(OCIDateTime*);
            std::vector<OCIDefine *> defines;
            defines.resize(1, NULL);
            result = OCIDefineByPos(stmt1, &defines[0], errorHandle, 1, &dateTimeArray[0], sizeof(OCIDateTime*), SQLT_TIMESTAMP, &indArray[0], &alenp[0], &rcodep[0], OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem binding timestamp!\n");
            result = OCIDefineArrayOfStruct(defines[0], errorHandle, sizeof(OCIDateTime*), sizeof(INDP), sizeof(ALENP), sizeof(RCODEP));
            if (result != OCI_SUCCESS) printf("problem with OCIDefineArrayOfStruct!\n");

            // Fetch from the select statement
            sb4 rowsFetched = 0;
            result = OCIStmtFetch2(stmt1, errorHandle, 1, OCI_FETCH_NEXT, rowsFetched, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem with fetching row!\n");
            // Get the ROWID
            OCIRowid *rowID;
            result = OCIDescriptorAlloc(envHandle, (dvoid **)&rowID, OCI_DTYPE_ROWID, 0, 0);
            if (result != OCI_SUCCESS) printf("problem allocating ROWID descriptor!\n");
            result = OCIAttrGet(stmt1, OCI_HTYPE_STMT, rowID, 0, OCI_ATTR_ROWID, errorHandle);
            if (result != OCI_SUCCESS) printf("problem getting ROWID descriptor!\n");

            // Bind the update
            void* data = new OCIDate;
            OCIBind** bindHandles = new OCIBind*[2];
            sb2 indp = 0;
            result = OCIBindByPos(stmt2, &bindHandles[0], errorHandle, 1, data, 7, SQLT_DAT, &indp, 0, 0, 0, 0, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem binding timestamp!\n");
            result = OCIBindByPos(stmt2, &bindHandles[1], errorHandle, 2, &rowID, sizeof(OCIRowid **), SQLT_RDD, 0, 0, 0, 0, 0, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem binding ROWID!\n");

            // Set the bound data
            sb2 year = 2019;
            ub1 month = 1;
            ub1 day = 1;
            ub1 cent = 0;
            ub1 yr = 0;
            yr = year % 100 + 100;
            cent = year / 100 + 100;
            year = (static_cast<sb2>(cent) << 8) + yr; // Note that this calculation is portable
            OCIDateSetDate((OCIDate *)data, year, month, day);
            ub1 hour = 12;
            ub1 min = 0;
            ub1 sec = 0;
            OCIDateSetTime((OCIDate *)data, hour + 1, min + 1, sec + 1);

            // Execute update statement
            result = OCIStmtExecute(servCtx_h_, stmt2, errorHandle, (ub4)1, (ub4)0, (const OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem executing update!\n");
            result = OCITransCommit(servCtx_h_, errorHandle, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("problem with commit!\n");

            // Freeing resources
            result = OCIDescriptorFree(rowID, OCI_DTYPE_ROWID);
            if (result != OCI_SUCCESS) printf("problem with freeing descriptor!\n");
            rowID = 0;
            result = OCIDescriptorFree(*(dvoid**)dateTimeArray, OCI_DTYPE_TIMESTAMP);
            if (result != OCI_SUCCESS) printf("problem with freeing descriptor!\n");
            delete data;
            delete[] bindHandles;
            delete[] alenp;
            delete[] indArray;
            delete[] rcodep;
            result = OCIHandleFree(stmt1, OCI_HTYPE_STMT);
            if (result != OCI_SUCCESS) printf("problem with freeing statement!\n");
            result = OCIHandleFree(stmt2, OCI_HTYPE_STMT);
            if (result != OCI_SUCCESS) printf("problem with freeing statement!\n");

            //////////////////////////////////////////////////////////////////////////////////

            // END update test

            //////////////////////////////////////////////////////////////////////////////////

            //////////////////////////////////////////////////////////////////////////////////
            // Ending Session begin
            //////////////////////////////////////////////////////////////////////////////////

            result = OCISessionEnd(servCtx_h_, errorHandle, sessionHandle_, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("Problem OCISessionEnd OCI_DEFAULT!\n");

            result = OCIHandleFree(sessionHandle_, OCI_HTYPE_SESSION);
            if (result != OCI_SUCCESS) printf("Problem OCIHandleFree OCI_HTYPE_SESSION!\n");

            result = OCIServerDetach(serverHandle_, errorHandle, OCI_DEFAULT);
            if (result != OCI_SUCCESS) printf("Problem OCIServerDetach OCI_DEFAULT!\n");

            result = OCIHandleFree(serverHandle_, OCI_HTYPE_SERVER);
            if (result != OCI_SUCCESS) printf("Problem OCIHandleFree OCI_HTYPE_SERVER!\n");

            result = OCIHandleFree(servCtx_h_, OCI_HTYPE_SVCCTX);
            if (result != OCI_SUCCESS) printf("Problem OCIHandleFree OCI_HTYPE_SVCCTX!\n");

            result = OCIHandleFree(errorHandle, OCI_HTYPE_ERROR);
            if (result != OCI_SUCCESS) printf("Problem OCIHandleFree OCI_HTYPE_ERROR!\n");

            //////////////////////////////////////////////////////////////////////////////////
            // Ending Session End
            //////////////////////////////////////////////////////////////////////////////////

                return 0;
            }