Odd behavior: SQL Result different between Mysql Browser and SQLEXEC 1

[Jay9988]

Hi All,

I have just experienced this odd behavior:

I have a SELECT statement with several subqueries and join

If I tried manually the statement on the Mysql Browser utility, it generates records exactly what I intended (For example: 5 records). But if I run the same SQL statement from the VFP with sqlexec, it generates slightly different (more records have generated, looks like the filtering from subquery I used, do not works)

I have tried on different environment (different Mysql Server, Different Client), the SQLEXEC is fine (generated 5 records as I intended)

Has anyone experienced this kind of situation ? I have tried to re-install MySQL ODBC connector, but doesn't help

Many thanks

 

[Jay9988]

Hi Chris,

But why in experiment 1: there's no difference between 'R' and 'R ' ?

 

[Chris Miller]

Well, what values are in the data in the kddivisi column?

Chriss

 

[Jay9988]

Hi Chriss

all data kddivisi column, in all tables are R actually. and datatype is Char(2)
Is MySQL store 'R' and 'R ' differently? because I cannot tell the difference in the Mysql Browser

thanks

 

[Chris Miller]

kddivisi='R' vs kddivisi='R ' could get different results if there are records with kddivisi='R1' or any value with something else than space after the R. And it depends how exactly MySQL compares strings. I don't actually know exactly, but it should be documented, and it seems not to be as you expect it.

Chriss

 

[Jay9988]

Hi Chriss,

Thanks a lot for your time and explanation. I appreciate it..
Many thanks

 

[Chris Miller]

No problem.

All in all

1. MySQL executes what comes over via SQLEXEC, there's no involvement of the VFP SQL engine
2. the only part where VFP is involved but also the MYSQL ODBC driver DLL is how ?parameters are coming over and are converted. But anything that you code into the query string comes over as is.

Differences of results can only be explained in differences in the query, not because of VFP involvement vs MySQL Browser. VFP Isn't even putting in the values at the ? mark, that's what you can see from the log of my sample query WHERE ID=?n. What arrives is ?, not ?n.

In the end, you'll only detect any real differences in the general log, therefore.

Chriss