[ODBC-231] ODBC Error 0000 in SSIS with longtext Created: 2019-03-14  Updated: 2019-03-19  Resolved: 2019-03-17

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: General
Affects Version/s: 3.1.0
Fix Version/s: 2.0.19, 3.0.9, 3.1.1

Type: Bug Priority: Critical
Reporter: Peter Godwin Assignee: Lawrin Novitsky
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows Server 2012 R2 x64
Latest Visual Studio Data Tools

Attachments: Text File SQL.LOG     File maodbc.dll    

 Description   

When using an the MariaDB ODBC driver within an SSIS Package to push data to a table with a longtext column I receive the following error:

[Destination - ALLERGY_COMMENT [2]] Error: There was an error while trying to get schema information. state: '00000'. Native Error Code: 0. [ma-3.1.0][10.3.11-MariaDB-log]

The DDL for the destination table (in MariaDB) is included below.
Also attached is the ODBC Trace from the Windows ODBC trace. I wasn't able to generate a trace from the MariaDB file. The MySQL ODBC driver seems to work if I swap it out instead.

CREATE TABLE `allergy_comment` (
`ALLERGY_COMMENT_ID` double NOT NULL,
`ALLERGY_INSTANCE_ID` double NOT NULL,
`ALLERGY_ID` double NOT NULL,
`COMMENT_DT_TM` datetime DEFAULT NULL,
`COMMENT_PRSNL_ID` double NOT NULL,
`ALLERGY_COMMENT` longtext DEFAULT NULL,
`ACTIVE_IND` double DEFAULT NULL,
`ACTIVE_STATUS_CD` double NOT NULL,
`ACTIVE_STATUS_DT_TM` datetime DEFAULT NULL,
`ACTIVE_STATUS_PRSNL_ID` double NOT NULL,
`BEG_EFFECTIVE_DT_TM` datetime NOT NULL,
`END_EFFECTIVE_DT_TM` datetime NOT NULL,
`CONTRIBUTOR_SYSTEM_CD` double NOT NULL,
`DATA_STATUS_CD` double NOT NULL,
`DATA_STATUS_DT_TM` datetime DEFAULT NULL,
`DATA_STATUS_PRSNL_ID` double NOT NULL,
`UPDT_APPLCTX` double NOT NULL,
`UPDT_CNT` double NOT NULL,
`UPDT_DT_TM` datetime NOT NULL,
`UPDT_ID` double NOT NULL,
`UPDT_TASK` double NOT NULL,
`COMMENT_TZ` double DEFAULT NULL,
`BEG_EFFECTIVE_TZ` double DEFAULT NULL,
`CMB_COMMENT_ID` double NOT NULL,
`CMB_PRSNL_ID` double NOT NULL,
`CMB_PERSON_ID` double NOT NULL,
`CMB_DT_TM` datetime DEFAULT NULL,
`CMB_TZ` double DEFAULT NULL,
`CMB_FLAG` double NOT NULL,
`LAST_UTC_TS` timestamp(6) NULL DEFAULT NULL,
`Audit_CreateJobId` bigint(20) DEFAULT NULL,
`Audit_CreateDate` timestamp NOT NULL DEFAULT current_timestamp(),
`Audit_UpdateJobId` bigint(20) DEFAULT NULL,
`Audit_UpdateDate` timestamp NULL DEFAULT NULL,
`Audit_Deleted` bit(1) NOT NULL DEFAULT b'0',
`Audit_DeleteJobId` bigint(20) DEFAULT NULL,
`Audit_DeletedDate` timestamp NULL DEFAULT NULL,
`Audit_ValidFrom` timestamp NULL DEFAULT NULL,
`Audit_ValidUntil` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`ALLERGY_COMMENT_ID`),
KEY `ALLERGY_COMMENT_XIF2ALLERGY_COMMENT` (`ALLERGY_ID`),
KEY `ALLERGY_COMMENT_XIF6ALLERGY_COMMENT` (`ALLERGY_INSTANCE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 Comments   
Comment by Lawrin Novitsky [ 2019-03-14 ]

Thank you for your report. Noticed one strange thing in your log
DtsDebugHost 3928-3f00 EXIT SQLGetTypeInfo with return code -1 (SQL_ERROR)
HSTMT 0x000000C5C1BF84E0
SWORD 0 <SQL_ALL_TYPES>

DIAG [42S22] [ma-3.1.0][10.3.11-MariaDB-log]Unknown column 'BIT' in 'field list' (1054)
which I can't repeat. Tried with 3.0, 3.1 32b and 64b, tried with earlier and later server versions, even though I don't think server version can play any role here. And we have test for that very call. I am a bit out of ideas what can that be and what to do with it. Do you Microsoft's ODBC Test application(comes in mdac tools)? If you do, could you please try this function call there and see if it exhibit the same behaviour there?
And I move to the main issue for now...

Comment by Lawrin Novitsky [ 2019-03-14 ]

The error occurs on fetching result of SQLColumnsW for the table. And seemingly it chokes on the row for the longtext column(ALLERGY_COMMENT). So, longtext column is the reason indeed

Comment by Peter Godwin [ 2019-03-14 ]

Thanks Lawrin. I'll grab the ODBC Test application and run some tests. The bit type should have been boolean instead for our requirements.
I'll retry with boolean just in case, but the issue really seems to be limited to tables with longblob/longtext columns.
Is there any further logging / testing I could provide to assist?

Comment by Peter Godwin [ 2019-03-15 ]

I think I can re-produce the error in the ODBC Test tool.

I have almost 0 idea about ODBC, but it seems the 'Bind Col All' option on the result causes the error. Skipping that steps returns the types normally. The errors between UNICODE and ANSI test tools vary.

edit: I originally missed the Bind Col All step in the instructions and thought it was an ANSI/UNICODE/ODBC 3.0 issue. I have removed the other comments as a result.

  1. Conn --> Full Connect... --> MariaDB DSN, with ODBC Behaviour set to ODBC 3.0
  2. Catalog --> SQLCollumns
  • CatalogName: <null pointer>
  • SchemaName: iemr_test (our db name)
  • TableName: allergy_comment
  • ColumnName: ALLERGY_COMMENT

This prints the following:

SQLColumns:
 
				In:				StatementHandle = 0x005A8AC8, CatalogName = SQL_NULL_HANDLE, NameLength1 = 0, SchemaName = "iemr_test", NameLength2 = 9, 
										TableName = "allergy_comment", NameLength3 = 15, ColumnName = "ALLERGY_COMMENT", NameLength4 = 15
 
				Return:	SQL_SUCCESS=0

Next, Results --> Bind Col All - This part seems to be the cause of the issue, skipping this step works as expected.

Bind Col All

 
Bind Col All:
 
									icol, fCType, cbValueMax, pcbValue, rgbValue
 
											1, SQL_C_WCHAR=-8, 66, 0, ""
 
											2, SQL_C_WCHAR=-8, 2, 0, ""
 
											3, SQL_C_WCHAR=-8, 66, 0, ""
 
											4, SQL_C_WCHAR=-8, 66, 0, ""
 
											5, SQL_C_SSHORT=-15, 2, 0, 0
 
											6, SQL_C_WCHAR=-8, 508, 0, ""
 
											7, SQL_C_SLONG=-16, 4, 0, 0
 
											8, SQL_C_SLONG=-16, 4, 0, 0
 
											9, SQL_C_SSHORT=-15, 2, 0, 0
 
											10, SQL_C_SSHORT=-15, 2, 0, 0
 
											11, SQL_C_SSHORT=-15, 2, 0, 0
 
											12, SQL_C_WCHAR=-8, 508, 0, ""
 
											13, SQL_C_WCHAR=-8, 508, 0, ""
 
											14, SQL_C_SSHORT=-15, 2, 0, 0
 
											15, SQL_C_SSHORT=-15, 2, 0, 0
 
											16, SQL_C_SLONG=-16, 4, 0, 0
 
											17, SQL_C_SLONG=-16, 4, 0, 0
 
											18, SQL_C_WCHAR=-8, 5, 0, ""

Then Results --> Get Data All, which returns the following:

Get Data All

 
Get Data All:
 
TST1005: Highest bound column is 18.
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
SQLFetch returned: SQL_ERROR=-1
 
				:		szSqlState = "00000", *pfNativeError = 0, 						szErrorMsg = "[ma-3.1.0][10.3.11-MariaDB-log]"

If I try the same thing again, but this time another column, the result returns successfully:

SQLColumns:
 
				In:				StatementHandle = 0x005A8AC8, CatalogName = SQL_NULL_HANDLE, NameLength1 = 0, SchemaName = "iemr_test", NameLength2 = 9, 
										TableName = "allergy_comment", NameLength3 = 15, ColumnName = "ACTIVE_IND", NameLength4 = 10
 
				Return:	SQL_SUCCESS=0
 
 
 
Get Data All:
 
TST1005: Highest bound column is 18.
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
"iemr_test", <Null>, "allergy_comment", "ACTIVE_IND", 8, "DOUBLE", 15, 8, <Null>, 10, 1, "", "NULL", 8, <Null>, 4, 7, "YES"
 
1 row fetched from 18 columns.

If I try the same steps through the ANSI tool I get the following, which may be a more useful error.

ODC Test (ANSI)

 
 
 
	Full Connect(Default)
 
 
 
	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
 
 
 
	Successfully connected to DSN 'MariaDB-Test'.
 
SQLColumns:
 
				In:				StatementHandle = 0x001D6FD0, CatalogName = "iemr_test", 
										NameLength1 = 9, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, 
										TableName = "allergy_comment", NameLength3 = 15, ColumnName = "ALLERGY_COMMENT", NameLength4 = 15
 
				Return:	SQL_SUCCESS=0
 
 
 
Bind Col All:
 
									icol, fCType, cbValueMax, pcbValue, rgbValue
 
											1, SQL_C_CHAR=1, 65, 0, ""
 
											2, SQL_C_CHAR=1, 2, 0, ""
 
											3, SQL_C_CHAR=1, 65, 0, ""
 
											4, SQL_C_CHAR=1, 65, 0, ""
 
											5, SQL_C_SSHORT=-15, 2, 0, 0
 
											6, SQL_C_CHAR=1, 254, 0, ""
 
											7, SQL_C_SLONG=-16, 4, 0, 0
 
											8, SQL_C_SLONG=-16, 4, 0, 0
 
											9, SQL_C_SSHORT=-15, 2, 0, 0
 
											10, SQL_C_SSHORT=-15, 2, 0, 0
 
											11, SQL_C_SSHORT=-15, 2, 0, 0
 
											12, SQL_C_CHAR=1, 254, 0, ""
 
											13, SQL_C_CHAR=1, 254, 0, ""
 
											14, SQL_C_SSHORT=-15, 2, 0, 0
 
											15, SQL_C_SSHORT=-15, 2, 0, 0
 
											16, SQL_C_SLONG=-16, 4, 0, 0
 
											17, SQL_C_SLONG=-16, 4, 0, 0
 
											18, SQL_C_CHAR=1, 4, 0, ""
 
 
 
Get Data All:
 
TST1005: Highest bound column is 18.
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
SQLFetch returned: SQL_ERROR=-1
 
				:		szSqlState = "22003", *pfNativeError = 0, *pcbErrorMsg = 57
 
										szErrorMsg = "[ma-3.1.0][10.3.11-MariaDB-log]Numeric value out of range"

When Bind Col All is not called, this is the result:

 
 
	Full Connect(Default)
 
 
 
	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
 
 
 
	Successfully connected to DSN 'MariaDB-Test'.
 
SQLColumns:
 
				In:				StatementHandle = 0x00159338, CatalogName = SQL_NULL_HANDLE, NameLength1 = 0, SchemaName = "iemr_test", NameLength2 = 9, 
										TableName = "allergy_comment", NameLength3 = 15, ColumnName = "ALLERGY_COMMENT", NameLength4 = 15
 
				Return:	SQL_SUCCESS=0
 
 
 
Get Data All:
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
"iemr_test", <Null>, "allergy_comment", "ALLERGY_COMMENT", -10, "LONGTEXT", 4294967295, 17179869180, <Null>, <Null>, 1, "", "NULL", -10, <Null>, 17179869180, 6, "YES"
 
1 row fetched from 18 columns.

So looks like the BUFFER_LENGTH and CHAR_OCTET_LENGTH values might be overflowing (I think, I'm unsure of the types)

Comment by Peter Godwin [ 2019-03-15 ]

Quick check of the source, I think there needs to be a test case in
https://github.com/MariaDB/mariadb-connector-odbc/blob/3d0d5a537d0c7855a0172d27706c17a7a4b63f97/ma_statement.h#L124
for SQL_WLONGVARCHAR and SQL_LONGVARCHAR ? as CHARACTER_MAXIMUM_LENGTH = 4294967295 for LONGTEXT at least (the value is getting multiplied by 4 but i'm not sure what sets that).

Comment by Peter Godwin [ 2019-03-15 ]

Finally, just tried with a table with LONGBLOB with the same steps and it crashes the ODBC Test tool. Without the col bind, it works properly.

Unhandled exception at 0x522E374A (maodbc.dll) in odbcte32.exe: 0xC0000005: Access violation reading location 0x00000010.

Loading up Visual Studio and the source code showed @ https://github.com/MariaDB/mariadb-connector-c/blob/34f8887af03d022416dd6593de91d0706e57f46b/libmariadb/mariadb_lib.c#L3281

mariadb_lib.c

 
/* snip */
 
MYSQL_FIELD * STDCALL mysql_fetch_field_direct(MYSQL_RES *res,uint fieldnr)
 
{
 
  return &(res)->fields[fieldnr];
 
}

Exception:

Unhandled exception thrown: read access violation.
 
res was nullptr.

Call Stack

 
>	maodbc.dll!mysql_fetch_field_direct(st_mysql_res * res, unsigned int fieldnr) Line 3281	C
 
 	maodbc.dll!MADB_StmtGetData(void * StatementHandle, unsigned short Col_or_Param_Num, short TargetType, void * TargetValuePtr, long BufferLength, long * StrLen_or_IndPtr, int InternalUse) Line 2529	C
 
 	maodbc.dll!SQLGetData(void * StatementHandle, unsigned short Col_or_Param_Num, short TargetType, void * TargetValuePtr, long BufferLength, long * StrLen_or_IndPtr) Line 1813	C

EDIT:
Edit, this might just be me doing it wrong. On the same table if I follow these steps the driver doesn't crash.

  1. Conn-->Full Connect
  2. Catalog --> SQLColumns --> Fill in TableName
  3. Results --> Bind Col All
  4. Catalog --> SQLColumns --> Fill in TableName
  5. Results --> Get Data All

Log:

Full Connect(Default)
 
 
 
	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
 
 
 
	Successfully connected to DSN 'MariaDB-Test'.
 
SQLColumns:
 
				In:				StatementHandle = 0x001B9640, CatalogName = SQL_NULL_HANDLE, 
										NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, TableName = "CE_BLOB", 
										NameLength3 = 7, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
 
				Return:	SQL_SUCCESS=0
 
 
 
Bind Col All:
 
									icol, fCType, cbValueMax, pcbValue, rgbValue
 
											1, SQL_C_CHAR=1, 65, 0, ""
 
											2, SQL_C_CHAR=1, 2, 0, ""
 
											3, SQL_C_CHAR=1, 65, 0, ""
 
											4, SQL_C_CHAR=1, 65, 0, ""
 
											5, SQL_C_SSHORT=-15, 2, 0, 0
 
											6, SQL_C_CHAR=1, 254, 0, ""
 
											7, SQL_C_SLONG=-16, 4, 0, 0
 
											8, SQL_C_SLONG=-16, 4, 0, 0
 
											9, SQL_C_SSHORT=-15, 2, 0, 0
 
											10, SQL_C_SSHORT=-15, 2, 0, 0
 
											11, SQL_C_SSHORT=-15, 2, 0, 0
 
											12, SQL_C_CHAR=1, 254, 0, ""
 
											13, SQL_C_CHAR=1, 254, 0, ""
 
											14, SQL_C_SSHORT=-15, 2, 0, 0
 
											15, SQL_C_SSHORT=-15, 2, 0, 0
 
											16, SQL_C_SLONG=-16, 4, 0, 0
 
											17, SQL_C_SLONG=-16, 4, 0, 0
 
											18, SQL_C_CHAR=1, 4, 0, ""
 
SQLColumns:
 
				In:				StatementHandle = 0x001B9640, CatalogName = SQL_NULL_HANDLE, 
										NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, TableName = "CE_BLOB", 
										NameLength3 = 7, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
 
				Return:	SQL_SUCCESS=0
 
 
 
Get Data All:
 
TST1005: Highest bound column is 18.
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
"iemr_test", <Null>, "ce_blob", "EVENT_ID", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 1, "NO"
 
"iemr_test", <Null>, "ce_blob", "VALID_UNTIL_DT_TM", 93, "DATETIME", 19, 16, <Null>, 10, 0, "", <Null>, 9, 3, <Null>, 2, "NO"
 
"iemr_test", <Null>, "ce_blob", "BLOB_SEQ_NUM", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 3, "NO"
 
"iemr_test", <Null>, "ce_blob", "VALID_FROM_DT_TM", 93, "DATETIME", 19, 16, <Null>, 10, 0, "", <Null>, 9, 3, <Null>, 4, "NO"
 
"iemr_test", <Null>, "ce_blob", "BLOB_LENGTH", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 5, "NO"
 
"iemr_test", <Null>, "ce_blob", "COMPRESSION_CD", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 6, "NO"
 
SQLFetch returned: SQL_ERROR=-1
 
				:		szSqlState = "22003", *pfNativeError = 0, *pcbErrorMsg = 57
 
										szErrorMsg = "[ma-3.1.0][10.3.11-MariaDB-log]Numeric value out of range"

For the same table without the bind step:

Without bind

 
Full Connect(Default)
 
 
 
	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
 
 
 
	Successfully connected to DSN 'MariaDB-Test'.
 
SQLColumns:
 
				In:				StatementHandle = 0x001B6C88, CatalogName = SQL_NULL_HANDLE, 
										NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, TableName = "CE_BLOB", 
										NameLength3 = 7, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
 
				Return:	SQL_SUCCESS=0
 
 
 
Get Data All:
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
"iemr_test", <Null>, "ce_blob", "EVENT_ID", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 1, "NO"
 
"iemr_test", <Null>, "ce_blob", "VALID_UNTIL_DT_TM", 93, "DATETIME", 19, 16, <Null>, 10, 0, "", <Null>, 9, 3, <Null>, 2, "NO"
 
"iemr_test", <Null>, "ce_blob", "BLOB_SEQ_NUM", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 3, "NO"
 
"iemr_test", <Null>, "ce_blob", "VALID_FROM_DT_TM", 93, "DATETIME", 19, 16, <Null>, 10, 0, "", <Null>, 9, 3, <Null>, 4, "NO"
 
"iemr_test", <Null>, "ce_blob", "BLOB_LENGTH", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 5, "NO"
 
"iemr_test", <Null>, "ce_blob", "COMPRESSION_CD", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 6, "NO"
 
"iemr_test", <Null>, "ce_blob", "BLOB_CONTENTS", -4, "LONGBLOB", 4294967295, 4294967295, <Null>, <Null>, 1, "", "NULL", -4, <Null>, 4294967295, 7, "YES"
 
"iemr_test", <Null>, "ce_blob", "UPDT_DT_TM", 93, "DATETIME", 19, 16, <Null>, 10, 0, "", <Null>, 9, 3, <Null>, 8, "NO"
 
"iemr_test", <Null>, "ce_blob", "UPDT_ID", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 9, "NO"
 
"iemr_test", <Null>, "ce_blob", "UPDT_TASK", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 10, "NO"
 
"iemr_test", <Null>, "ce_blob", "UPDT_CNT", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 11, "NO"
 
"iemr_test", <Null>, "ce_blob", "UPDT_APPLCTX", 8, "DOUBLE", 15, 8, <Null>, 10, 0, "", <Null>, 8, <Null>, 4, 12, "NO"
 
"iemr_test", <Null>, "ce_blob", "LAST_UTC_TS", 93, "DATETIME", 19, 16, <Null>, 10, 1, "", "NULL", 9, 3, <Null>, 13, "YES"
 
"iemr_test", <Null>, "ce_blob", "Audit_CreateJobId", -5, "BIGINT", 19, 20, 0, 10, 1, "", "NULL", -5, <Null>, 3, 14, "YES"
 
"iemr_test", <Null>, "ce_blob", "Audit_CreateDate", 93, "DATETIME", 19, 16, <Null>, 10, 0, "", "current_timestamp(6)", 9, 3, <Null>, 15, "NO"
 
"iemr_test", <Null>, "ce_blob", "Audit_UpdateJobId", -5, "BIGINT", 19, 20, 0, 10, 1, "", "NULL", -5, <Null>, 3, 16, "YES"
 
"iemr_test", <Null>, "ce_blob", "Audit_UpdateDate", 93, "DATETIME", 19, 16, <Null>, 10, 1, "", "NULL", 9, 3, <Null>, 17, "YES"
 
"iemr_test", <Null>, "ce_blob", "Audit_Deleted", -6, "TINYINT", 3, 1, 0, 10, 0, "", "0", -6, <Null>, 1, 18, "NO"
 
"iemr_test", <Null>, "ce_blob", "Audit_DeleteJobId", -5, "BIGINT", 19, 20, 0, 10, 1, "", "NULL", -5, <Null>, 3, 19, "YES"
 
"iemr_test", <Null>, "ce_blob", "Audit_DeletedDate", 93, "DATETIME", 19, 16, <Null>, 10, 1, "", "NULL", 9, 3, <Null>, 20, "YES"
 
"iemr_test", <Null>, "ce_blob", "Audit_ValidFrom", 93, "DATETIME", 19, 16, <Null>, 10, 1, "", "NULL", 9, 3, <Null>, 21, "YES"
 
"iemr_test", <Null>, "ce_blob", "Audit_ValidUntil", 93, "DATETIME", 19, 16, <Null>, 10, 1, "", "NULL", 9, 3, <Null>, 22, "YES"
 
22 rows fetched from 18 columns.

Comment by Peter Godwin [ 2019-03-15 ]

OK last couple of comments for the day.

Quick summary:

  • LONGTEXT type (and likely the other 'big' text types) return the wrong sizes to SQLColumns:
  • Binding against the SQLColumns causes a 'Numeric value out of range' error (under ANSI client)
  • I went back and confirm that the 3.06 driver returns the correct length for LONGTEXT (4,294,967,295 rather than 17,179,869,180), but still fails with SSIS (Numeric value out of range)
  • Setting the bind types for SQLColumns to SQL_C_SBIGINT seemed to work for columns 7 (COLUMN_SIZE), 8 (BUFFER_LENGTH), 16 (CHAR_OCTET_LENGTH). I'm not sure if that's in spec or not though.
Comment by Lawrin Novitsky [ 2019-03-15 ]

Thank you for all the tests you've made! I appreciate that. I could repeat SQLColumns problem - sorry for not sharing that here. My findings is similar to yours. The only thing, I never got wrong column size. That value(17,179,869,180) is precisely 4 times greater than the correct one. And could be the correct value for octet or buffer length. Column size is measured in characters, while other 2 in bytes. And 4 - is maximum number of bytes for single character encoding in utf8.
I actually asked you to try in Odbc Test application to call SQLGetTypeInfo with SQL_ALL_TYPES parameter. That is the other error that seemingly did not significantly affect execution, but is present in your trace file. I could not repeat that error, and it looks very strange.

Comment by Peter Godwin [ 2019-03-16 ]

Sorry, I missed the original request for SQLGetTypeInfo.
I've determined the issue with the error was an init query of "SET SQL_MODE='ORACLE';" - see the test at the bottom of this post.

Tests have been completed with 3.06 and 3.1 RC 32-Bit with the ODBC Unicode tester. Unless otherwise stated, all ODBC options are at their default.

All tests have been created on a new database, DDL included below.

Lastly, the ODBC driver in it's current form will crash SSIS during execution (though I couldn't always reliably crash it). I've confirmed the crash is with the ODBC component in SSIS, but can't be certain what the cause is as there was nothing obvious in the logs.

Sadly, I can't spend any more work time on this. I'll try to assist out of work hours, but for work we'll need to find an option with more mature Windows connectivity.

DDL for tests

 
CREATE DATABASE ODBC_TEST;
 
USE ODBC_TEST;
 
create table combined_test ( 
 id int auto_increment primary key, 
 contents longtext null, 
 blobcontent longblob null, 
 bitfield bit(1) null
 
);

Maria ODBC 3.06 32bit

SQLGetTypeInfo

 
SQLGetTypeInfo:
 
 In:  StatementHandle = 0x001C7000, DataType = SQL_ALL_TYPES=0
 
 Return: SQL_SUCCESS=0
 
 
 
Get Data All:
 
"TYPE_NAME", "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREFIX", "LITERAL_SUFFIX", "CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERVAL_PRECISION"
 
"BIT", -7, 1, "", "", <Null>, 1, 1, 3, 0, 0, 0, "BIT", 0, 0, 0, 0, 10, <Null>
 
"BOOL", -7, 1, "", "", <Null>, 1, 1, 3, 0, 0, 0, "BOOL", 0, 0, 0, 0, 10, <Null>
 
"TINYINT", -6, 3, "", "", <Null>, 1, 0, 3, 0, 0, 1, "TINYINT", 0, 0, 0, 0, 10, <Null>
 
"TINYINT UNSIGNED", -6, 3, "", "", <Null>, 1, 0, 3, 1, 0, 1, "TINYINT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"BIGINT", -5, 19, "", "", <Null>, 1, 0, 3, 0, 0, 1, "BIGINT", 0, 0, 0, 0, 10, <Null>
 
"BIGINT UNSIGNED", -5, 20, "", "", <Null>, 1, 0, 3, 1, 0, 1, "BIGINT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"LONG VARBINARY", -4, 16777215, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "LONG VARBINARY", 0, 0, 0, 0, 10, <Null>
 
"MEDIUMBLOB", -4, 16777215, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "MEDIUMBLOB", 0, 0, 0, 0, 10, <Null>
 
"LONGBLOB", -4, 2147483647, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "LONGBLOB", 0, 0, 0, 0, 10, <Null>
 
"BLOB", -4, 65535, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "BLOB", 0, 0, 0, 0, 10, <Null>
 
"TINYBLOB", -4, 255, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "TINYBLOB", 0, 0, 0, 0, 10, <Null>
 
"VARBINARY", -3, 255, "'", "'", "length", 1, 1, 3, 0, 0, 0, "VARBINARY", 0, 0, 0, 0, 10, <Null>
 
"BINARY", -2, 255, "'", "'", "length", 1, 1, 3, 0, 0, 0, "BINARY", 0, 0, 0, 0, 10, <Null>
 
"LONG VARCHAR", -1, 16777215, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "LONG VARCHAR", 0, 0, 0, 0, 10, <Null>
 
"MEDIUMTEXT", -1, 16777215, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "MEDIUMTEXT", 0, 0, 0, 0, 10, <Null>
 
"LONGTEXT", -1, 2147483647, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "LONGTEXT", 0, 0, 0, 0, 10, <Null>
 
"TEXT", -1, 65535, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "TEXT", 0, 0, 0, 0, 10, <Null>
 
"TINYTEXT", -1, 255, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "TINYTEXT", 0, 0, 0, 0, 10, <Null>
 
"CHAR", 1, 255, "'", "'", "length", 1, 0, 3, 0, 0, 0, "CHAR", 0, 0, 0, 0, 10, <Null>
 
"NUMERIC", 2, 65, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "NUMERIC", -308, 308, 0, 0, 10, <Null>
 
"DECIMAL", 3, 65, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "DECIMAL", -308, 308, 0, 0, 10, <Null>
 
"INTEGER", 4, 10, "", "", <Null>, 1, 0, 3, 0, 0, 1, "INTEGER", 0, 0, 0, 0, 10, <Null>
 
"INTEGER UNSIGNED", 4, 10, "", "", <Null>, 1, 0, 3, 1, 0, 1, "INTEGER UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"INT", 4, 10, "", "", <Null>, 1, 0, 3, 0, 0, 1, "INT", 0, 0, 0, 0, 10, <Null>
 
"INT UNSIGNED", 4, 10, "", "", <Null>, 1, 0, 3, 1, 0, 1, "INT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"MEDIUMINT", 4, 7, "", "", <Null>, 1, 0, 3, 0, 0, 1, "MEDIUMINT", 0, 0, 0, 0, 10, <Null>
 
"MEDIUMINT UNSIGNED", 4, 8, "", "", <Null>, 1, 0, 3, 1, 0, 1, "MEDIUMINT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"SMALLINT", 5, 5, "", "", <Null>, 1, 0, 3, 0, 0, 1, "SMALLINT", 0, 0, 0, 0, 10, <Null>
 
"SMALLINT UNSIGNED", 5, 5, "", "", <Null>, 1, 0, 3, 1, 0, 1, "SMALLINT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"FLOAT", 6, 10, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "FLOAT", -38, 38, 0, 0, 10, <Null>
 
"DOUBLE", 8, 17, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "DOUBLE", -308, 308, 0, 0, 10, <Null>
 
"DOUBLE PRECISION", 8, 17, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "DOUBLE PRECISION", -308, 308, 0, 0, 10, <Null>
 
"REAL", 8, 17, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "REAL", -308, 308, 0, 0, 10, <Null>
 
"VARCHAR", 12, 255, "'", "'", "length", 1, 0, 3, 0, 0, 0, "VARCHAR", 0, 0, 0, 0, 10, <Null>
 
"ENUM", 12, 65535, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "ENUM", 0, 0, 0, 0, 10, <Null>
 
"SET", 12, 64, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "SET", 0, 0, 0, 0, 10, <Null>
 
"DATE", 91, 10, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "DATE", 0, 0, 0, 0, 10, <Null>
 
"TIME", 92, 8, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "TIME", 0, 0, 0, 0, 10, <Null>
 
"DATETIME", 93, 16, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "DATETIME", 0, 0, 0, 0, 10, <Null>
 
"TIMESTAMP", 93, 16, "'", "'", "scale", 1, 0, 3, 0, 0, 0, "TIMESTAMP", 0, 0, 0, 0, 10, <Null>
 
"CHAR", -8, 255, "'", "'", "length", 1, 0, 3, 0, 0, 0, "CHAR", 0, 0, 0, 0, 10, <Null>
 
"VARCHAR", -9, 255, "'", "'", "length", 1, 0, 3, 0, 0, 0, "VARCHAR", 0, 0, 0, 0, 10, <Null>
 
"LONG VARCHAR", -10, 16777215, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "LONG VARCHAR", 0, 0, 0, 0, 10, <Null>
 
43 rows fetched from 19 columns.

SQLColumns

 
SQLColumns:
 
    In:    StatementHandle = 0x005C74F0, CatalogName = SQL_NULL_HANDLE, NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, 
          TableName = "combined_test", NameLength3 = 13, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
 
    Return: SQL_SUCCESS=0
 
 
 
Get Data All:
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
"odbc_test", <Null>, "combined_test", "id", 4, "INT", 10, 4, 0, 10, 1, "", <Null>, 4, <Null>, 2, 1, "YES"
 
"odbc_test", <Null>, "combined_test", "contents", -1, "LONGTEXT", 4294967295, 4294967295, <Null>, <Null>, 1, "", "NULL", -1, <Null>, 4294967295, 2, "YES"
 
"odbc_test", <Null>, "combined_test", "blobcontent", -4, "LONGBLOB", 4294967295, 4294967295, <Null>, <Null>, 1, "", "NULL", -4, <Null>, 4294967295, 3, "YES"
 
"odbc_test", <Null>, "combined_test", "bitfield", -7, "BIT", 1, 1, <Null>, 10, 1, "", "NULL", -7, <Null>, <Null>, 4, "YES"
 
4 rows fetched from 18 columns.

ODBC Connector 3.1RC

SQLGetTypeInfo

 
SQLGetTypeInfo:
 
    In:    StatementHandle = 0x00568E60, DataType = SQL_ALL_TYPES=0
 
    Return: SQL_SUCCESS=0
 
 
 
Get Data All:
 
"TYPE_NAME", "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREFIX", "LITERAL_SUFFIX", "CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERVAL_PRECISION"
 
"BIT", -7, 1, "", "", <Null>, 1, 1, 3, 0, 0, 0, "BIT", 0, 0, 0, 0, 10, <Null>
 
"BOOL", -7, 1, "", "", <Null>, 1, 1, 3, 0, 0, 0, "BOOL", 0, 0, 0, 0, 10, <Null>
 
"TINYINT", -6, 3, "", "", <Null>, 1, 0, 3, 0, 0, 1, "TINYINT", 0, 0, 0, 0, 10, <Null>
 
"TINYINT UNSIGNED", -6, 3, "", "", <Null>, 1, 0, 3, 1, 0, 1, "TINYINT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"BIGINT", -5, 19, "", "", <Null>, 1, 0, 3, 0, 0, 1, "BIGINT", 0, 0, 0, 0, 10, <Null>
 
"BIGINT UNSIGNED", -5, 20, "", "", <Null>, 1, 0, 3, 1, 0, 1, "BIGINT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"LONG VARBINARY", -4, 16777215, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "LONG VARBINARY", 0, 0, 0, 0, 10, <Null>
 
"MEDIUMBLOB", -4, 16777215, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "MEDIUMBLOB", 0, 0, 0, 0, 10, <Null>
 
"LONGBLOB", -4, 2147483647, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "LONGBLOB", 0, 0, 0, 0, 10, <Null>
 
"BLOB", -4, 65535, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "BLOB", 0, 0, 0, 0, 10, <Null>
 
"TINYBLOB", -4, 255, "'", "'", <Null>, 1, 1, 3, 0, 0, 0, "TINYBLOB", 0, 0, 0, 0, 10, <Null>
 
"VARBINARY", -3, 255, "'", "'", "length", 1, 1, 3, 0, 0, 0, "VARBINARY", 0, 0, 0, 0, 10, <Null>
 
"BINARY", -2, 255, "'", "'", "length", 1, 1, 3, 0, 0, 0, "BINARY", 0, 0, 0, 0, 10, <Null>
 
"LONG VARCHAR", -1, 16777215, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "LONG VARCHAR", 0, 0, 0, 0, 10, <Null>
 
"MEDIUMTEXT", -1, 16777215, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "MEDIUMTEXT", 0, 0, 0, 0, 10, <Null>
 
"LONGTEXT", -1, 2147483647, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "LONGTEXT", 0, 0, 0, 0, 10, <Null>
 
"TEXT", -1, 65535, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "TEXT", 0, 0, 0, 0, 10, <Null>
 
"TINYTEXT", -1, 255, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "TINYTEXT", 0, 0, 0, 0, 10, <Null>
 
"CHAR", 1, 255, "'", "'", "length", 1, 0, 3, 0, 0, 0, "CHAR", 0, 0, 0, 0, 10, <Null>
 
"NUMERIC", 2, 65, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "NUMERIC", -308, 308, 0, 0, 10, <Null>
 
"DECIMAL", 3, 65, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "DECIMAL", -308, 308, 0, 0, 10, <Null>
 
"INTEGER", 4, 10, "", "", <Null>, 1, 0, 3, 0, 0, 1, "INTEGER", 0, 0, 0, 0, 10, <Null>
 
"INTEGER UNSIGNED", 4, 10, "", "", <Null>, 1, 0, 3, 1, 0, 1, "INTEGER UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"INT", 4, 10, "", "", <Null>, 1, 0, 3, 0, 0, 1, "INT", 0, 0, 0, 0, 10, <Null>
 
"INT UNSIGNED", 4, 10, "", "", <Null>, 1, 0, 3, 1, 0, 1, "INT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"MEDIUMINT", 4, 7, "", "", <Null>, 1, 0, 3, 0, 0, 1, "MEDIUMINT", 0, 0, 0, 0, 10, <Null>
 
"MEDIUMINT UNSIGNED", 4, 8, "", "", <Null>, 1, 0, 3, 1, 0, 1, "MEDIUMINT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"SMALLINT", 5, 5, "", "", <Null>, 1, 0, 3, 0, 0, 1, "SMALLINT", 0, 0, 0, 0, 10, <Null>
 
"SMALLINT UNSIGNED", 5, 5, "", "", <Null>, 1, 0, 3, 1, 0, 1, "SMALLINT UNSIGNED", 0, 0, 0, 0, 10, <Null>
 
"FLOAT", 6, 10, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "FLOAT", -38, 38, 0, 0, 10, <Null>
 
"DOUBLE", 8, 17, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "DOUBLE", -308, 308, 0, 0, 10, <Null>
 
"DOUBLE PRECISION", 8, 17, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "DOUBLE PRECISION", -308, 308, 0, 0, 10, <Null>
 
"REAL", 8, 17, "", "", "precision,scale", 1, 0, 3, 0, 0, 1, "REAL", -308, 308, 0, 0, 10, <Null>
 
"VARCHAR", 12, 255, "'", "'", "length", 1, 0, 3, 0, 0, 0, "VARCHAR", 0, 0, 0, 0, 10, <Null>
 
"ENUM", 12, 65535, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "ENUM", 0, 0, 0, 0, 10, <Null>
 
"SET", 12, 64, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "SET", 0, 0, 0, 0, 10, <Null>
 
"DATE", 91, 10, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "DATE", 0, 0, 0, 0, 10, <Null>
 
"TIME", 92, 8, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "TIME", 0, 0, 0, 0, 10, <Null>
 
"DATETIME", 93, 16, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "DATETIME", 0, 0, 0, 0, 10, <Null>
 
"TIMESTAMP", 93, 16, "'", "'", "scale", 1, 0, 3, 0, 0, 0, "TIMESTAMP", 0, 0, 0, 0, 10, <Null>
 
"CHAR", -8, 255, "'", "'", "length", 1, 0, 3, 0, 0, 0, "CHAR", 0, 0, 0, 0, 10, <Null>
 
"VARCHAR", -9, 255, "'", "'", "length", 1, 0, 3, 0, 0, 0, "VARCHAR", 0, 0, 0, 0, 10, <Null>
 
"LONG VARCHAR", -10, 16777215, "'", "'", <Null>, 1, 0, 3, 0, 0, 0, "LONG VARCHAR", 0, 0, 0, 0, 10, <Null>
 
43 rows fetched from 19 columns.

SQLColumns

 
SQLColumns:
 
    In:    StatementHandle = 0x00568E60, CatalogName = SQL_NULL_HANDLE, NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, 
          TableName = "combined_test", NameLength3 = 13, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
 
    Return: SQL_SUCCESS=0
 
 
 
Get Data All:
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
"odbc_test", <Null>, "combined_test", "id", 4, "INT", 10, 4, 0, 10, 1, "", <Null>, 4, <Null>, 2, 1, "YES"
 
"odbc_test", <Null>, "combined_test", "contents", -10, "LONGTEXT", 4294967295, 17179869180, <Null>, <Null>, 1, "", "NULL", -10, <Null>, 17179869180, 2, "YES"
 
"odbc_test", <Null>, "combined_test", "blobcontent", -4, "LONGBLOB", 4294967295, 4294967295, <Null>, <Null>, 1, "", "NULL", -4, <Null>, 4294967295, 3, "YES"
 
"odbc_test", <Null>, "combined_test", "bitfield", -7, "BIT", 1, 1, <Null>, 10, 1, "", "NULL", -7, <Null>, <Null>, 4, "YES"
 
4 rows fetched from 18 columns.

ODBC 3.1RC 32-Bit `SET SQL_MODE='ORACLE';`

SQLGetTypeInfo

 
SQLGetTypeInfo:
 
    In:    StatementHandle = 0x005674F0, DataType = SQL_ALL_TYPES=0
 
    Return: SQL_ERROR=-1
 
    stmt:  szSqlState = "42S22", *pfNativeError = 1054, *pcbErrorMsg = 67, *ColumnNumber = -2, *RowNumber = -2
 
          MessageText = "[ma-3.1.0][10.3.11-MariaDB-log]Unknown column 'BIT' in 'field list'"

SQLColumns

 
SQLColumns:
 
    In:    StatementHandle = 0x005674F0, CatalogName = SQL_NULL_HANDLE, NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, 
          TableName = "combined_test", NameLength3 = 13, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
 
    Return: SQL_SUCCESS=0
 
 
 
Get Data All:
 
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
 
"odbc_test", <Null>, "combined_test", "id", 4, "INT", 10, 4, 0, 10, 1, "", <Null>, 4, <Null>, 2, 1, "YES"
 
"odbc_test", <Null>, "combined_test", "contents", -10, "LONGTEXT", 4294967295, 17179869180, <Null>, <Null>, 1, "", "NULL", -10, <Null>, 17179869180, 2, "YES"
 
"odbc_test", <Null>, "combined_test", "blobcontent", -4, "LONGBLOB", 4294967295, 4294967295, <Null>, <Null>, 1, "", "NULL", -4, <Null>, 4294967295, 3, "YES"
 
"odbc_test", <Null>, "combined_test", "bitfield", -7, "BIT", 1, 1, <Null>, 10, 1, "", "NULL", -7, <Null>, <Null>, 4, "YES"
 
4 rows fetched from 18 columns.

Comment by Lawrin Novitsky [ 2019-03-17 ]

Thank you - that is very valuable information about oracle mode.
As about maturity - I'd actually say, that the bug in this case in is SSIS It binds COLUMN_SIZE field as SQL_C_SLONG, and 4294967295 is greater than max signed int, thus connector returns truncation error. Btw, another strange thing is why you don't get status and error text. I always do. And about should it be signed or unsigned. SQLColumns pagehttps://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolumns-function?view=sql-server-2017] only says it is integer. But it doesn't say anything about sign-ness of any field. The page about column size says it is "column size in characters ... as contained in the SQL_DESC_LENGTH descriptor field", and here we can read, that SQL_DESC_LENGTH is SQLULEN field. And that means unsigned, and it is larger on 64bit platform. Interesting, that if follow BUFFER_ and OCTET_LENGTH in the same way - they happen to be SQLLEN, i.e. signed.
But back to column size. I've fixed that error on fetch. But now SSIS will get 4294967295 in its signed SQL_C_SLONG buffer, that in that type means -1. I am not sure if it will correctly process that value But ODBC is MS's thing, SSIS is MS's thing - we must obey and try to both comply to specs, and make all (main) applications work.
Once again - thank you for your tests and infromation. The info about Oracle mode is especially valuable. I am gonna push the fix and close the issue. I'd appreciate though if you could test the connector with the fix applied. If you can, I will build and post binaries here, but please tell me what is better - just the dll, or msi

Comment by Lawrin Novitsky [ 2019-03-17 ]

The fix has been pushed to odbc-3.0 as 553c71f

Comment by Peter Godwin [ 2019-03-17 ]

Happy to test. Can test with just the dll

Comment by Peter Godwin [ 2019-03-17 ]

Thanks Lawrin, it wouldn't shock me in the slightest if SSIS is the real culprit
Re: error codes, I could only get the error codes and messages to show up on the ANSI test client; using the UNICODE test client I'd always get the 0000 error.

Did you by chance check https://jira.mariadb.org/browse/ODBC-231?focusedCommentId=124834&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-124834 ? Should I log this one as a separate issue?

Comment by Lawrin Novitsky [ 2019-03-18 ]

I've attached the dll. It contains also SQLGetTypeInfo fix.
This is 32b. I already don't remember why, but I've assumed that you need 32b.

Yes, separate report is better, and I've seen you had filed one. Thanks!

Comment by Peter Godwin [ 2019-03-18 ]

Thanks Lawrin. Could you also attach a 64bit version? I'm getting a crash on the 64bit version related to converting unicode to utf8 and suspect it might be related.

Comment by Peter Godwin [ 2019-03-18 ]

Thanks Lawrin

It seems to have fixed the issue with SSIS with that version.

Cheers

Comment by Lawrin Novitsky [ 2019-03-19 ]

Great! And thanks for your help. It's appreciated

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