Bug #37352 Unknown column results in SQLSTATE 42S21

• Submitted: 11 Jun 2008 18:33
• Modified: 12 Jun 2008 5:12
• Reporter: Heinrich Schuchardt
• Status: Can't repeat
• Category: Connector / ODBC
• Severity: S3 (Non-critical)
• Version: 3.51
• OS: Any
• CPU Architecture: Any

[11 Jun 2008 18:33] Heinrich Schuchardt

Description:
ODBC is used to issue a SELECT statement with a invalid column. 

The driver reported the following diagnostics whilst running SQLExecDirect
42S21:1:1054:[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-Debian_1ubuntu3.3-log]Unknown column 'FT10' in 'where clause'

Here the information returned from function SQLGetDiagRec() is added, the colons separate the following fields:
SQLState - five-character SQLSTATE code pertaining to the diagnostic record RecNumber. The first two characters indicate the class; the next three indicate the subclass. In the example 42S21 signifies 'Column already exists'.
RecNumber - Indicates the status record from which the application seeks information. Status records are numbered from 1.
NativeError - Native error code, specific to the data source. In the example MySQL error 1054 signifies 'Unknown column'.
MessageText - Error message text.

I would expect SQLState 42S22 - Column not found

When issuing the same select inside mysql I receive:
ERROR 1054 (42S22): Unknown column 'FT10' in 'where clause'

How to repeat:
Submit SELECT with illegal column via ODBC connector.

Suggested fix:
Check mapping of error codes in ODBC connector.

[12 Jun 2008 5:12] Tonci Grgin

Hi Heinrich and thanks for your report.

You haven't posted exact MyODBC version but I presume this is a server bug (if any) rather than c/ODBC one. I would suggest testing this from cl client first and then, if result is correct, upgrade c/ODBC as I found no problem (see below).

Test done in Microsoft generic ODBC client on WinXP Pro SP2 localhost:
Server version: 5.0.64-pb1103-log MySQL Pushbuild Edition, build 1103

mysql> show create table a;
| Table | Create Table
| a     | CREATE TABLE `a` (
  `Codigo` int(10) unsigned NOT NULL auto_increment,
  `Nombre` varchar(255) default NULL,
  `Telefono` varchar(255) default NULL,
  `Observaciones` longtext,
  `Direccion` varchar(255) default NULL,
  `Dni` varchar(255) default NULL,
  `CP` int(11) default NULL,
  `Provincia` varchar(255) default NULL,
  `Poblacion` varchar(255) default NULL,
  PRIMARY KEY  (`Codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.01 sec)

mysql> SELECT FLD10 FROM a;
ERROR 1054 (42S22): Unknown column 'FLD10' in 'field list'

c/ODBC 5.1.5 rev 1115: 	Successfully connected to DSN '5064-local'.
SQLExecDirect:
	In:	hstmt = 0x00851FE0, szSqlStr = "", cbSqlStr = -3
		Return:	SQL_ERROR=-1
		stmt:	szSqlState = "42S22", *pfNativeError = 1054, *pcbErrorMsg = 88, *ColumnNumber = -2, *RowNumber = -2
		MessageText = "[MySQL][ODBC 5.1 Driver][mysqld-5.0.64-pb1103-log]Unknown column 'FLD10' in 'field list'"

c/ODBC 3.51.25GA: 	
		Return:	SQL_ERROR=-1
		stmt:	szSqlState = "42S22", *pfNativeError = 1054, *pcbErrorMsg = 89, *ColumnNumber = -2, *RowNumber = -2
		MessageText = "[MySQL][ODBC 3.51 Driver][mysqld-5.0.64-pb1103-log]Unknown column 'FLD10' in 'field list'"