Bug #35816 using vb6 + mysql 5.051a + Connector 5.1.3

• Submitted: 3 Apr 2008 20:43
• Modified: 4 Apr 2008 9:13
• Reporter: João Bosco
• Status: Not a Bug
• Category: Connector / ODBC
• Severity: S2 (Serious)
• Version: 5.1.3
• OS: Windows (XP-SP2)
• CPU Architecture: Any

[3 Apr 2008 20:43] João Bosco

Description:
In previous versions of ODBC connector for example, of the  3.51.17 to 3.51.21 could use a SQL instruction via ado  as to follow with 100% of hit:

select concat( 'test  ' , '|' ,  lpad( 2, 4 , '0' )) test 

Accomplishing tests got success using the command cast of the numeric content for caracter as to follow:

select concat( 'test  ' , '|' ,  lpad( cast( 2 as char ) , 4, '0' ) ) test 

How to repeat:
In previous versions of ODBC connector for example, of the  3.51.17 to 3.51.21 could use a SQL instruction via ado  as to follow with 100% of hit:

select concat( 'test  ' , '|' ,  lpad( 2, 4 , '0' )) test 

Accomplishing tests got success using the command cast of the numeric content for caracter as to follow:

select concat( 'test  ' , '|' ,  lpad( cast( 2 as char ) , 4, '0' ) ) test 

Suggested fix:
In previous versions of ODBC connector for example, of the  3.51.17 to 3.51.21 could use a SQL instruction via ado  as to follow with 100% of hit:

select concat( 'test  ' , '|' ,  lpad( 2, 4 , '0' )) test 

Accomplishing tests got success using the command cast of the numeric content for caracter as to follow:

select concat( 'test  ' , '|' ,  lpad( cast( 2 as char ) , 4, '0' ) ) test

[4 Apr 2008 9:13] Tonci Grgin

Hi João and thanks for your report.

What I believe is that *previous* versions did not follow standards in behavior and new ones do even when we know there's server bug with metadata, for example. Here, I believe it is LPAD that returns result marked as BINARY thus your resultset looks like "0x2..." confusing your code. Please do search manual for LPAD and do tests in command line client with -T option like this:
mysql -uuser -p -T test
select concat( 'test  ' , '|' ,  lpad( 2, 4 , '0' )) test;
and compare metadata to:
select concat( 'test  ' , '|' ,  lpad( cast( 2 as char ) , 4, '0' ) ) test;
You should see something like this:
Server version: 5.0.58-pb1083-log MySQL Pushbuild Edition, build 1083

mysql> select concat( 'test  ' , '|' ,  lpad( 2, 4 , '0' )) AS test;
Field   1:  `test`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)  <<
Length:     11
Max_length: 11
Decimals:   31
Flags:      NOT_NULL BINARY <<

+-------------+
| test        |
+-------------+
| test  |0002 |
+-------------+
1 row in set (0.00 sec)

mysql> select concat( 'test  ' , '|' ,  lpad( cast( 2 as char ) , 4, '0' ) ) tes
t;
Field   1:  `test`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8) <<
Length:     11
Max_length: 11
Decimals:   31
Flags:      NOT_NULL <<

+-------------+
| test        |
+-------------+
| test  |0002 |
+-------------+
1 row in set (0.00 sec)

Metadata changes result in:
select concat( 'test  ' , '|' ,  lpad( 2 , 4, '0' ) ) test
SQLExecDirect:
				In:				hstmt = 0x00851FE0, szSqlStr = "", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"test"
0xtest  |0002
1 row fetched from 1 column.
while select concat( 'test  ' , '|' ,  lpad( cast( 2 as char ) , 4, '0' ) ) test produces:
SQLExecDirect:
				In:				hstmt = 0x00851FE0, szSqlStr = "", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"test"
"test  |0002"
1 row fetched from 1 column.

There is no bug here that I can see as connector is obeying metadata sent by server...