MySQL Bugs: #35067: connector/odbc 5.1.2 with foxpro 7 through 9

Bug #35067	connector/odbc 5.1.2 with foxpro 7 through 9
Submitted:	5 Mar 2008 5:05	Modified:	7 Aug 2008 14:16
Reporter:	Fazil Aliev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.2	OS:	Windows
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
Hi, 
I use connector/odbc 5.1.2 (because older versions does not accept SSL) 
with visual foxpro 7 and 9 (windows XP).
remote sql views with ? mark does not work starting with 5..(they were working fine with older versions of connector) versions. 

How to repeat:
open any database "data1" with visual foxpro 7 or 9(I don't have foxpro 8).
and table "table1" with the field "id".
create connection 'Myconn'and remote view 'View1' usind ODBC connection like...

OPEN DATABASE c:\data1.DBC
CREATE CONNECTION Myconn DATASOURCE "Mysql1" USERID "user1" ;
  PASSWORD "pass1" DATABASE "data1"
SET MULTILOCKS ON
id=70
CREATE SQL VIEW View1 CONNECTION Myconn SHARE AS SELECT * FROM table1 WHERE id = ?id

use UserView alias AAA

<<<<<<<Foxpro gives an error message and shuts down.>>>>>>

if instead you use 
CREATE SQL VIEW View1 CONNECTION Myconn SHARE AS SELECT * FROM table1 WHERE id=70

works fine.
Please note: I tried using "&" and "@" instead of question mark also, and they did not help. In the error log file the command looks like:
SELECT * FROM table1 WHERE id = 7.000000000000000000000e+001 so everything is normal because this command also work under view.

Thanks in advance.  

Suggested fix:
I think it must be fixed with connector/odbc next version.

I stumbled upon this bug, here is myodbc.sql for 3.51 and 5.1:

-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0004
-- Timestamp: 080726  4:43:55

SELECT Stock.guid_stock, Stock.idcaja, Stock.serial, Stock.guid_articulo, Stock.tara, Stock.bruto, Stock.neto, Stock.barra, Stock.operador, Stock.fecha, Stock.codigo, Stock.codigo1, Stock.codigo2, Stock.codigo3, Stock.codigo4, Stock.titulo, Stock.tipo1, Stock.tipo2, Stock.clave, Stock.nombre, Stock.descripcion, Stock.lote, Stock.turno, Stock.serial2, Stock.tfecha, Stock.tturno, Stock.tipo3 FROM  stock Stock WHERE  Stock.idcaja = ( _latin1'02DG0PQ0LQ'  ) ORDER BY Stock.idcaja;

-- Query logging
--
--  Driver name: MySQL ODBC 3.51 Driver  Version: 03.51.26
-- Timestamp: 080726  4:42:52

SELECT Stock.guid_stock, Stock.idcaja, Stock.serial, Stock.guid_articulo, Stock.tara, Stock.bruto, Stock.neto, Stock.barra, Stock.operador, Stock.fecha, Stock.codigo, Stock.codigo1, Stock.codigo2, Stock.codigo3, Stock.codigo4, Stock.titulo, Stock.tipo1, Stock.tipo2, Stock.clave, Stock.nombre, Stock.descripcion, Stock.lote, Stock.turno, Stock.serial2, Stock.tfecha, Stock.tturno, Stock.tipo3 FROM  stock Stock WHERE  Stock.idcaja = ( '02DG0PQ0LQ'  ) ORDER BY Stock.idcaja;

5.1 makes VFP crash and burn, 3.51 works with no problem. Notice the "_latin1" thing near the WHERE clause.

ODBC TRACE 3.51

Attachment: ODBC_TRACE_3.51.TXT (text/plain), 102.72 KiB.

ODBC TRACE 5.1

Attachment: ODBC_TRACE_5.1.TXT (text/plain), 26.90 KiB.

This is how the SQL looks like in VFP9:

SELECT Stock.guid_stock, Stock.idcaja, Stock.serial, Stock.guid_articulo,;
  Stock.tara, Stock.bruto, Stock.neto, Stock.barra, Stock.operador,;
  Stock.fecha, Stock.codigo, Stock.codigo1, Stock.codigo2, Stock.codigo3,;
  Stock.codigo4, Stock.titulo, Stock.tipo1, Stock.tipo2, Stock.clave,;
  Stock.nombre, Stock.descripcion, Stock.lote, Stock.turno, Stock.serial2,;
  Stock.tfecha, Stock.tturno, Stock.tipo3;
 FROM ;
     stock Stock;
 WHERE  Stock.idcaja = ( ?m.lcIdCaja );
 ORDER BY Stock.idcaja

m.lcIdCaja is assigned a value before running the query, for example: '02DG0PQ0LQ'

This fails with 5.1, the myodbc.sql trace shows that strange _latin1 string.

Now if I replace ?m.lcIdCaja in the VFP9 SQL with the actual value, like this:

SELECT Stock.guid_stock, Stock.idcaja, Stock.serial, Stock.guid_articulo,;
  Stock.tara, Stock.bruto, Stock.neto, Stock.barra, Stock.operador,;
  Stock.fecha, Stock.codigo, Stock.codigo1, Stock.codigo2, Stock.codigo3,;
  Stock.codigo4, Stock.titulo, Stock.tipo1, Stock.tipo2, Stock.clave,;
  Stock.nombre, Stock.descripcion, Stock.lote, Stock.turno, Stock.serial2,;
  Stock.tfecha, Stock.tturno, Stock.tipo3;
 FROM ;
     stock Stock;
 WHERE  Stock.idcaja = ( '02DG0PQ0LQ' );
 ORDER BY Stock.idcaja

Then it works fine in 5.1 and the myodbc.sql file is this:

-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0004
-- Timestamp: 080726  5:05:31

SELECT Stock.guid_stock, Stock.idcaja, Stock.serial, Stock.guid_articulo, Stock.tara, Stock.bruto, Stock.neto, Stock.barra, Stock.operador, Stock.fecha, Stock.codigo, Stock.codigo1, Stock.codigo2, Stock.codigo3, Stock.codigo4, Stock.titulo, Stock.tipo1, Stock.tipo2, Stock.clave, Stock.nombre, Stock.descripcion, Stock.lote, Stock.turno, Stock.serial2, Stock.tfecha, Stock.tturno, Stock.tipo3 FROM  stock Stock WHERE  Stock.idcaja = ( '02DG0PQ0LQ' ) ORDER BY Stock.idcaja;

Notice no _latin1 string near the WHERE clause, so I guess that _latin1 string is what is causing havoc in this case.

Bug#38544 was marked as a duplicate of this.

This is likely fixed by the patch for bug#36069. Please try a snapshot of version 5.1.5 which includes this fix, or wait for the release.
Snapshots: http://downloads.mysql.com/snapshots.php

Jess:

No crashes with mysql-connector-odbc-noinstall-5.1.5r1134_20080804_0640-win32

Thank you.

Dear Jess and Carlos,
Thank you for your help and comments. 
I've checked mysql-connector-odbc-noinstall-5.1.5r1134_20080804_0640-win32
Everything works great. I appreciate your help.

With Best Regards,
Fazil aliev