Do you happen to know what values are stored in database for those fields? all different?

The best thing would be to provide ODBC trace of the report generation. Trace of successful report with MySQL's connector could also help in some cases.

Is that correct that you tried with both 3.0.3 and 3.0.5 versions?

I have attached the ODBC traces from an example report, one with the MariaDB ODBC connector and the other with the MyODBC connector. The report using MyODBC connector shows the correct date/times, the report using the MariaDB ODBC connector has NULL date/times.

I originally noticed this issue with 3.0.3 and then upgraded to 3.0.5 - both have this same behavior. In this particular report all of the records have the date/time value in the TransactionDateTime columnof '2018-06-12 15:11:37'. I have tested other reports with different values in the records and the result is the same.

Please let me know if there is any additional information I can provide.

I altered my report to remove everything except for the DATETIME field in question to produce more minified traces.

odbc-trace-mariadb-min.log odbc-trace-myodbc-min.log

I have done some more digging and I have some additional details.

To test the exact same query that the report is using outside of Crystal Reports I added the query using the ODBC data source to an Excel document. The Excel document printed out the date/time values correctly. With further digging it turns out that Excel was using SQLGetData after SQLFetch to retrieve the individual values. Crystal Reports used only SQLFetch. I have attached the ODBC trace from Excel in case that is somehow useful here. odbc-trace-mariadb-excel.log

The ODBC traces I posted yesterday show essentially the same output for the MariaDB ODBC connector as for the MyODBC connector, after initializing and binding everything they call SQLFetch once for each row. Other applications, such as Excel in this case, call SQLGetData following SQLFetch.

I did some more digging and built a debugging version of the MariaDB ODBC connector to trace further. I added statements to output when the functions in ma_statement.c are entered. I also printed out the string representation of (SQL_TIMESTAMP_STRUCT *)DataPtr at the end of the MADB_CopyMadbTimestamp function in ma_helper.c. The output (after the initial initialization and binding) looks like this for each row:

MADB_StmtFetchScroll
MADB_StmtFetch
MADB_FixFetchedValues
MADB_CopyMadbTimestamp Timestamp: 2018-06-12 15:11:37.0000

This is the correct value so the query and SQLFetch are retrieving the correct date/time values from the database, and MADB_CopyMadbTimestamp is correctly taking them from the internal buffer bound to the query result and populating DataPtr. It seems to me that when called from MADB_StmtFetch (by way of MADB_FixFetchedValues) the buffer that the SQL_TIMESTAMP_STRUCT structure is being populated into is somehow not getting linked up with what the client application is reading.

This same debugging output during the query from Excel looks like this for each row:

MADB_StmtFetchScroll
MADB_StmtFetch
MADB_FixFetchedValues
ResetDescIntBuffers
MADB_StmtGetData
MADB_StmtGetData Timestamp: 2018-06-12 15:11:37.0000

I added the timestamp output in MADB_StmtGetData, that is populating the result correctly as it is fetching the values from the result set. This doesn't go through MADB_CopyMadbTimestamp.

I get a little lost from here in the maze of buffers and bindings so I'm not really sure how to trace it further, but I hope this helps narrow it down. Everything I'm seeing seems to point to MADB_CopyMadbTimestamp not putting the result where the client application is expecting it, resulting in this case in NULL values on the report.

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.

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.

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