[ODBC-152] Problem with SQLColumns / SQLFetchScroll Created: 2018-06-27  Updated: 2018-10-01  Resolved: 2018-10-01

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: General
Affects Version/s: 3.0.5
Fix Version/s: 3.0.7, 2.0.19

Type: Bug Priority: Major
Reporter: Alexander Roskamp Assignee: Lawrin Novitsky
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux/64bit

Attachments: File catalog3.c    

 Description   

With one of our tables at work we had problems with SQLColumns. If we executed a SQLColumns-Call of another before the call of this particular table, it was working.

I found out that the Result-Set-Column SQL_DATA_TYPE was reporting different data-types (mediumtext vs. smallint).

After some research, I managed to CAST this column explicitly to SIGNED (SMALLINT) so that our particular problem was getting solved. But I am sure that there is a better solution.

Here is out table definition:

CREATE TABLE IF NOT EXISTS `Putback_Source` (
`Revision` bigint(20) unsigned NOT NULL DEFAULT '0',
`Datum` date NOT NULL DEFAULT '0000-00-00',
`Uhrzeit` time NOT NULL DEFAULT '00:00:00',
`User_ID` int(10) unsigned NOT NULL DEFAULT '0',
`Projekt_ID` bigint(20) unsigned NOT NULL,
`Beschreibung` longtext CHARACTER SET latin1,
`Kunde_ID` int(10) unsigned DEFAULT NULL,
`Handbuch` smallint(5) unsigned NOT NULL,
`FehlerNr` bigint(20) unsigned DEFAULT NULL,
`TextChecked` tinyint(3) unsigned DEFAULT '0',
`Prio` smallint(1) unsigned DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `Putback_Source`
ADD PRIMARY KEY (`Revision`),
ADD UNIQUE KEY `Revision` (`Revision`);

—

And here my diff:

  1. diff -C 3 ma_statement.h ma_statement.h.mod
      • ma_statement.h 2018-06-07 20:06:47.000000000 +0200
      • ma_statement.h.mod 2018-06-27 17:23:50.507488424 +0200
        ***************
      • 212,224 ****
        #define MADB_DEFAULT_COLUMN_NEW "COLUMN_DEFAULT"
        #define MADB_DEFAULT_COLUMN(DbcHndl) (MADB_ServerSupports(DbcHndl,MADB_ENCLOSES_COLUMN_DEF_WITH_QUOTES) ? MADB_DEFAULT_COLUMN_NEW : MADB_DEFAULT_COLUMN_OLD)
        #define MADB_CATALOG_COLUMNSp4 " AS COLUMN_DEF,"\
        ! "CASE"\
        " WHEN DATA_TYPE = 'date' THEN " XSTR(SQL_DATETIME)\
        " WHEN DATA_TYPE = 'time' THEN " XSTR(SQL_DATETIME)\
        " WHEN DATA_TYPE = 'datetime' THEN " XSTR(SQL_DATETIME)\
        " WHEN DATA_TYPE = 'timestamp' THEN " XSTR(SQL_DATETIME)\
        " ELSE @dt "\
        ! "END AS SQL_DATA_TYPE,"\
        "CASE"\
        " WHEN DATA_TYPE = 'date' THEN " XSTR(SQL_TYPE_DATE)\
        " WHEN DATA_TYPE = 'time' THEN " XSTR(SQL_TYPE_TIME)\
      • 212,224 ----
        #define MADB_DEFAULT_COLUMN_NEW "COLUMN_DEFAULT"
        #define MADB_DEFAULT_COLUMN(DbcHndl) (MADB_ServerSupports(DbcHndl,MADB_ENCLOSES_COLUMN_DEF_WITH_QUOTES) ? MADB_DEFAULT_COLUMN_NEW : MADB_DEFAULT_COLUMN_OLD)
        #define MADB_CATALOG_COLUMNSp4 " AS COLUMN_DEF,"\
        ! "CAST(CASE"\
        " WHEN DATA_TYPE = 'date' THEN " XSTR(SQL_DATETIME)\
        " WHEN DATA_TYPE = 'time' THEN " XSTR(SQL_DATETIME)\
        " WHEN DATA_TYPE = 'datetime' THEN " XSTR(SQL_DATETIME)\
        " WHEN DATA_TYPE = 'timestamp' THEN " XSTR(SQL_DATETIME)\
        " ELSE @dt "\
        ! "END AS SIGNED) AS SQL_DATA_TYPE,"\
        "CASE"\
        " WHEN DATA_TYPE = 'date' THEN " XSTR(SQL_TYPE_DATE)\
        " WHEN DATA_TYPE = 'time' THEN " XSTR(SQL_TYPE_TIME)\

 Comments   
Comment by Lawrin Novitsky [ 2018-09-18 ]

Thank you for your report and proposed problem solution.

cast/convert solution would be good if not best solution, if it's result wasn't LONG, and not SMALLINT.

Also, for future, if you have a patch for us, you can do a pull request on github

Comment by Lawrin Novitsky [ 2018-09-18 ]

roskamp But what is the problem exactly? With which field and when? Maybe your solution actually makes sense, but I need to understand the problem to, at least. to have the test case for it.

Comment by Lawrin Novitsky [ 2018-09-18 ]

Eventually I have pushed the requested change, as it looks reasonable. But I still would like to understand the problem to be able to add the test case

Comment by Alexander Roskamp [ 2018-09-20 ]

I added a skeleton of a test-case and found out that the problem (Numeric value out of range) occurs when the column 'SQL_DATA_TYPE' is bound by SQLBindCol.

In this file you can even activate debugging output of SQLDescribeCol and the data of the result-set.

I hope that this test environment helps a bit.

Comment by Lawrin Novitsky [ 2018-10-01 ]

The fix and the testcase have been pushed into odbc-3.0 as d49df3d

Generated at Thu Feb 08 03:26:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.