Bug #38544 parameterized SELECT fails with ODBC 05.01.0004
Submitted: 4 Aug 2008 16:22 Modified: 4 Aug 2008 17:29
Reporter: Carlos Alloatti Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.4 OS:Windows (XP SP3)
Assigned to: CPU Architecture:Any
Tags: ODBC 5.1.4 PARAMETERIZED FOXPRO

[4 Aug 2008 16:22] Carlos Alloatti
Description:
Remote views in Visual Foxpro use a SELECT statement to create a local cursor. Remote views can be parameterized. 

Parameterized views crashes Visual Foxpro 9.0 SP2 using ODBC connector version 5.1.4, works with ODBC 3.51.26

This bug is duplicated here:

http://bugs.mysql.com/bug.php?id=35067

I am reposting since it seems the original bug report is abandoned.

How to repeat:
Create a parameterized remote view in Visual Foxpro.

The following Foxpro code corresponds to a parameterized remote view:

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 is the myodbc.sql for ODBC 5.1 for the above:

-- 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;

Notice the "_latin1" string near the parameterized parameter.

This is the myodbc.sql for ODBC 3.51 for the same Foxpro code:

-- 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;

Notice no "_latin1" string near the parameterized parameter.

Now if you replace ?m.lcIdCaja in the Foxpro remote view with an actual value, using ODBC 5.1.4:

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 crashing Foxpro.

Suggested fix:
Maybe check why a "_latin1" string is added to the SELECT command in ODBC 5.1 Driver.
[4 Aug 2008 17:29] Jess Balint
Carlos,

The bug is not "abandoned". Please do not create duplicate bug reports.

Duplicate of bug#35067.