Re: Re: Inconsistency between JDBC and ODBC drivers when dealing with TIMESTAMP WITH TIME ZONE - Mailing list pgsql-odbc

Yes, I have to workaround this issue in my application since the existing issue as the current drivers have this bug. And to avoid overhead caching the server time zone is the best option to avoid repeated calls (thought their is a risk of you being wrong as you can changes the session time zone via a statement which will make the cached value wrong if it was run before that.

As apposed to showing my code to get a SQL_C_CHAR  I'll show you the ODBC code that is perplexing:

In convert.c's copy_and_convert_field() line 946: This case statement block states that for PG_TYPE_ABSTIME, PG_TYPE_DATETIME, PG_TYPE_TIMESTAMP_NO_TMZONE and PG_TYPE_TIMESTAMP that the string given by the server is:

1) -Infinity

2) -Infinity

3) invalid

4) %4d-%2d-%2d %2d:%2d:%2d%31s %15s (YYYY-MM-dd hh:mm:ss[.ffffff][-/+zh[:zm] [BC]) (max 35 characters for year < 10000)

What that code does for 1) - 3) is perfectly fine, you have to choose some timestamp value and those values are as good as anything else. 4) is dealt by the function timestamp2stime on line 224 of the convert_field.  The only bug I see in this code is how it handles the time zone parsing, it only handles +/-zh, but not the possible but rare +/-zh:zm format/ That bug is in the switch statement on line 245 of that file. The cases for '+', '-' and '.' all don't check for the existence of ":" in the string.  But doesn't matter currently as we throw away the time zone, so no fix is needed in this method at the current time, just a note that it is not handling a possible form of the string correctly.

Going back to copy_and_convert_field() on line 1147 this case happens if we are returning the text version of a value as indicated by the local variable text_handling. We then get to line 1170 where it handles PG_TYPE_ABSTIME, PG_TYPE_DATETIME, PG_TYPE_TIMESTAMP_NO_TMZONE and PG_TYPE_TIMESTAMP again and it calls a function, stime2timestamp, to rebuild the string in the format of 4) but with no zone information! (NOTE: stime2timestamp also has a bug with time zone it doesn`t handle offset that need the minute, i.e. -3:30 but not a concern as zone is never used currently)  If I was writing this part of the code I would have something like the logic shown below as I don`t understand what is wrong with the string the server gave us and why we would want to give it in an alternate form:

case PG_TYPE_ABSTIME:

case PG_TYPE_DATETIME:

case PG_TYPE_TIMESTAMP_NO_TMZONE:

case PG_TYPE_TIMESTAMP:

   len = 19;

   if (cbValueMax > len)

   {

      if ( (strnicmp(value, INFINITY_STRING, 8) == 0) ||

           (strnicmp(value, MINFINITY_STRING, 9) == 0) ||

           (strnicmp(value, "invalid", 7) == 0) // Keep old behaviour

      {      

         /* sprintf(rgbValueBindRow, "%.4d-%.2d-%.2d %.2d:%.2d:%.2d",

         std_time.y, std_time.m, std_time.d, std_time.hh, std_time.mm, std_time.ss); */

         stime2timestamp(&std_time, rgbValueBindRow, FALSE,

                         PG_VERSION_GE(conn, 7.2) ? (int) cbValueMax - len - 2 : 0);

         len = strlen(rgbValueBindRow);

      }

      else

      {

         len = strlen(value);

         if (cbValueMax > len)

         {

            // Just give back what the server gave us

            strcpy(rgbValueBindRow, value);

         }

         else

         {      

            /* fall back to generating the string */

            stime2timestamp(&std_time, rgbValueBindRow, FALSE,

                         PG_VERSION_GE(conn, 7.2) ? (int) cbValueMax - len - 2 : 0);

            len = strlen(rgbValueBindRow);

         } 

      }

   }

   break;

I hope that clears things up.

Walter