Uploaded image for project: 'MariaDB Connector/ODBC'
  1. MariaDB Connector/ODBC
  2. ODBC-148

DATE and DATETIME values are NULL in Crystal Reports

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 2.0.16, 3.0.3, 3.0.5
    • 3.0.6, 2.0.18
    • General
    • None
    • Windows 10

    Description

      I am attempting to use the MariaDB ODBC connector as a replacement for the MyODBC connector and have run into an issue where all DATE and DATETIME fields are being read by the report as having NULL values.

      I found the following reference to this issue here: https://apps.support.sap.com/sap/support/knowledge/public/en/2604895

      That page describes a workaround (one that would not be practical in my situation) but for the cause says only "MariaDB is not listed in the supported data sources for Crystal Reports". In my case I am using the MariaDB ODBC connector to connect to a MySQL 5.0 database, so something must be different in how the results appear to the report.

      I would be happy to provide any additional information in tracking down the difference that is causing the conflict.

      Attachments

        Activity

          Thanks, you've already did more, than I could expect!

          That is actually strange, MADB_CopyMadbTimestamp is pretty simple function copying numeric data from one struct(MYSQL_TIME) to another(SQL_TIMESTAMP_STRUCT). And DataPtr you printed is supposed to be the value buffer bound by the application, and it gets value during MADB_CopyMadbTimestamp execution. Since you have debug version, you may want to compare DataPtr value - I mean buffer address it points to, with what is passed to SQLBindCol for the field, to verify those are the same addresses. But that is quite unlikely, that there is error there.
          But looking at the crystal report mini trace - it's hard to imagine what causes the error. Pretty basic use of ODBC API.

          Lawrin Lawrin Novitsky added a comment - Thanks, you've already did more, than I could expect! That is actually strange, MADB_CopyMadbTimestamp is pretty simple function copying numeric data from one struct(MYSQL_TIME) to another(SQL_TIMESTAMP_STRUCT). And DataPtr you printed is supposed to be the value buffer bound by the application, and it gets value during MADB_CopyMadbTimestamp execution. Since you have debug version, you may want to compare DataPtr value - I mean buffer address it points to, with what is passed to SQLBindCol for the field, to verify those are the same addresses. But that is quite unlikely, that there is error there. But looking at the crystal report mini trace - it's hard to imagine what causes the error. Pretty basic use of ODBC API.
          adrian_sru Adrian Matlack added a comment - - edited

          Got it!

          Turns out the location pointed to by DataPtr was correct. I looked further at MADB_FixFetchedValues and noticed that *IndicatorPtr is being set for most of the other types, but not for the date/time types. I made the changes below in MADB_CopyMadbTimestamp to set that to the size of the SQL_TIMESTAMP_STRUCT, SQL_TIME_STRUCT, or SQL_DATE_STRUCT structure being used and it solved the problem. My report now shows the correct date/times.

          This diff is on top of the 3.0.5 source, but I tested with v2 of the ODBC connector and it has the same problem so the fix should be applied to that branch as well if it is still being maintaned.

          My only other question is, assuming this fix can be verified, when might I be able to see it in a build of the ODBC connector? I have been able to play around with a debug build well enough, but I would prefer it to be a more official (and signed) build before I get it to others.

          Thank you so much for the assistance!

          @@ -802,13 +802,18 @@ SQLRETURN MADB_CopyMadbTimestamp(MADB_Stmt *Stmt, MYSQL_TIME *tm, MADB_Desc *Ard
                 }
                 ts->hour= tm->hour;
                 ts->minute= tm->minute;
                 ts->second= tm->second;
                 ts->fraction= tm->second_part * 1000;
          -      if (ts->year + ts->month + ts->day + ts->hour + ts->minute + ts->fraction + ts->second == 0)
          -        if (ArdRecord->IndicatorPtr)
          -          *ArdRecord->IndicatorPtr= SQL_NULL_DATA;
          +
          +	  if (ArdRecord->IndicatorPtr)
          +	  {
          +		  if (ts->year + ts->month + ts->day + ts->hour + ts->minute + ts->fraction + ts->second == 0)
          +			*ArdRecord->IndicatorPtr = SQL_NULL_DATA;
          +		  else
          +			*ArdRecord->IndicatorPtr = sizeof(SQL_TIMESTAMP_STRUCT);
          +	  }
               }
               break;
               case SQL_C_TIME:
               case SQL_C_TYPE_TIME:
               {
          @@ -819,22 +824,30 @@ SQLRETURN MADB_CopyMadbTimestamp(MADB_Stmt *Stmt, MYSQL_TIME *tm, MADB_Desc *Ard
                   return MADB_SetError(&Stmt->Error, MADB_ERR_22007, NULL, 0);
                 }
                 ts->hour= tm->hour;
                 ts->minute= tm->minute;
                 ts->second= tm->second;
          +
          +	  if (ArdRecord->IndicatorPtr)
          +		  *ArdRecord->IndicatorPtr = sizeof(SQL_TIME_STRUCT);
               }
               break;
               case SQL_C_DATE:
               case SQL_TYPE_DATE:
               {
                 SQL_DATE_STRUCT *ts= (SQL_DATE_STRUCT *)DataPtr;
                 ts->year= tm->year;
                 ts->month= tm->month;
                 ts->day= tm->day;
          -      if (ts->year + ts->month + ts->day == 0)
          -        if (ArdRecord->IndicatorPtr)
          -          *ArdRecord->IndicatorPtr= SQL_NULL_DATA;
          +
          +	  if (ArdRecord->IndicatorPtr)
          +	  {
          +		  if (ts->year + ts->month + ts->day == 0)
          +			  *ArdRecord->IndicatorPtr = SQL_NULL_DATA;
          +		  else
          +			  *ArdRecord->IndicatorPtr = sizeof(SQL_DATE_STRUCT);
          +	  }
               }
               break;
             }
           
             return SQL_SUCCESS;
          

          adrian_sru Adrian Matlack added a comment - - edited Got it! Turns out the location pointed to by DataPtr was correct. I looked further at MADB_FixFetchedValues and noticed that *IndicatorPtr is being set for most of the other types, but not for the date/time types. I made the changes below in MADB_CopyMadbTimestamp to set that to the size of the SQL_TIMESTAMP_STRUCT, SQL_TIME_STRUCT, or SQL_DATE_STRUCT structure being used and it solved the problem. My report now shows the correct date/times. This diff is on top of the 3.0.5 source, but I tested with v2 of the ODBC connector and it has the same problem so the fix should be applied to that branch as well if it is still being maintaned. My only other question is, assuming this fix can be verified, when might I be able to see it in a build of the ODBC connector? I have been able to play around with a debug build well enough, but I would prefer it to be a more official (and signed) build before I get it to others. Thank you so much for the assistance! @@ - 802 , 13 + 802 , 18 @@ SQLRETURN MADB_CopyMadbTimestamp(MADB_Stmt *Stmt, MYSQL_TIME *tm, MADB_Desc *Ard } ts->hour= tm->hour; ts->minute= tm->minute; ts->second= tm->second; ts->fraction= tm->second_part * 1000 ; - if (ts->year + ts->month + ts->day + ts->hour + ts->minute + ts->fraction + ts->second == 0 ) - if (ArdRecord->IndicatorPtr) - *ArdRecord->IndicatorPtr= SQL_NULL_DATA; + + if (ArdRecord->IndicatorPtr) + { + if (ts->year + ts->month + ts->day + ts->hour + ts->minute + ts->fraction + ts->second == 0 ) + *ArdRecord->IndicatorPtr = SQL_NULL_DATA; + else + *ArdRecord->IndicatorPtr = sizeof(SQL_TIMESTAMP_STRUCT); + } } break ; case SQL_C_TIME: case SQL_C_TYPE_TIME: { @@ - 819 , 22 + 824 , 30 @@ SQLRETURN MADB_CopyMadbTimestamp(MADB_Stmt *Stmt, MYSQL_TIME *tm, MADB_Desc *Ard return MADB_SetError(&Stmt->Error, MADB_ERR_22007, NULL, 0 ); } ts->hour= tm->hour; ts->minute= tm->minute; ts->second= tm->second; + + if (ArdRecord->IndicatorPtr) + *ArdRecord->IndicatorPtr = sizeof(SQL_TIME_STRUCT); } break ; case SQL_C_DATE: case SQL_TYPE_DATE: { SQL_DATE_STRUCT *ts= (SQL_DATE_STRUCT *)DataPtr; ts->year= tm->year; ts->month= tm->month; ts->day= tm->day; - if (ts->year + ts->month + ts->day == 0 ) - if (ArdRecord->IndicatorPtr) - *ArdRecord->IndicatorPtr= SQL_NULL_DATA; + + if (ArdRecord->IndicatorPtr) + { + if (ts->year + ts->month + ts->day == 0 ) + *ArdRecord->IndicatorPtr = SQL_NULL_DATA; + else + *ArdRecord->IndicatorPtr = sizeof(SQL_DATE_STRUCT); + } } break ; } return SQL_SUCCESS;

          Hmm, and that is for fixed-length type. I actually thought it is not even required for fixed length types, but I've just read through related specs pages, and looks like it is. Thus absolutely our bug.

          The best way would be if you made a pull request on GitHub, then I'd merge your commit, and you name will stay in the history. If you don't want to do that, I can take you diff from here - no problem.

          Now, not sure if that is the case for such a simple fixes, which probably is hard to do the other way, but in usual case the MariaDB Corporation needs to have shared ownership of all code that is included in the MariaDB C/ODBC distribution. The easiest way to achieve this is by submitting your code under the BSD-new license.

          The other alternative is to sign the code contribution agreement which can be found here: https://mariadb.com/kb/en/mariadb/mca/

          Please indicate in a comment below that you are contributing your new code of the whole pull request, including one or several files that are either new files or modified ones, under the BSD-new license or that you have filled out the contribution agreement and sent it. Or, if you are going to make a Pull Request - you can do that in the comment on github.

          And thanks - you did all my work. And it's not visible in the trace, that what is put in the buffers in case of SQLGetData.

          As for the release date - we have just released 3.0.5, so I can't promise next release to be very soon. But I can promise that your fix will be there.

          Lawrin Lawrin Novitsky added a comment - Hmm, and that is for fixed-length type. I actually thought it is not even required for fixed length types, but I've just read through related specs pages, and looks like it is. Thus absolutely our bug. The best way would be if you made a pull request on GitHub, then I'd merge your commit, and you name will stay in the history. If you don't want to do that, I can take you diff from here - no problem. Now, not sure if that is the case for such a simple fixes, which probably is hard to do the other way, but in usual case the MariaDB Corporation needs to have shared ownership of all code that is included in the MariaDB C/ODBC distribution. The easiest way to achieve this is by submitting your code under the BSD-new license. The other alternative is to sign the code contribution agreement which can be found here: https://mariadb.com/kb/en/mariadb/mca/ Please indicate in a comment below that you are contributing your new code of the whole pull request, including one or several files that are either new files or modified ones, under the BSD-new license or that you have filled out the contribution agreement and sent it. Or, if you are going to make a Pull Request - you can do that in the comment on github. And thanks - you did all my work. And it's not visible in the trace, that what is put in the buffers in case of SQLGetData. As for the release date - we have just released 3.0.5, so I can't promise next release to be very soon. But I can promise that your fix will be there.

          I have created a pull request on GitHub, and noted in the commit comment that it is submitted under the BSD-new license.

          Thanks again!

          adrian_sru Adrian Matlack added a comment - I have created a pull request on GitHub, and noted in the commit comment that it is submitted under the BSD-new license. Thanks again!

          Fix and testcase are in commits cd5c619 and 7a1a226. Changed original fix to use OctetLengthPtr, since IndicatorPtr may be different from length pointer.
          Yet to be merged into 2.0

          Lawrin Lawrin Novitsky added a comment - Fix and testcase are in commits cd5c619 and 7a1a226. Changed original fix to use OctetLengthPtr, since IndicatorPtr may be different from length pointer. Yet to be merged into 2.0

          People

            Lawrin Lawrin Novitsky
            adrian_sru Adrian Matlack
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.