Strange problem with mySQL + Labview

[jed]

I am having a strange problem using mySQL via ADO.

I have written a medium sized LV app that uses mySQL as a back end. It works perfectly at the customer site, on many, many XP machines.

When I connect my laptop to the network, on all permutations of XP, VISTA and LV7.1.1 and LV 8.5, any column results that were created with a CONCAT or GROUP_CONCAT function appear something like this: "?????+????++??".

I have even installed the DB locally in case it was some sort of network problem, but I still see it. I thought it might be something to do with the default character set, but it occurs when I connect to the same server. I cut/pasted the query out of a labview probe into mySQL Query Browser and it had no problem.

I see the bad results as soon as the Variant is cast with the DB function...

[MikePorter]

QUOTE (jed @ Aug 20 2008, 12:08 PM)

I am having a strange problem using mySQL via ADO.

I have written a medium sized LV app that uses mySQL as a back end. It works perfectly at the customer site, on many, many XP machines.

When I connect my laptop to the network, on all permutations of XP, VISTA and LV7.1.1 and LV 8.5, any column results that were created with a CONCAT or GROUP_CONCAT function appear something like this: "?????+????++??".

I have even installed the DB locally in case it was some sort of network problem, but I still see it. I thought it might be something to do with the default character set, but it occurs when I connect to the same server. I cut/pasted the query out of a labview probe into mySQL Query Browser and it had no problem.

I see the bad results as soon as the Variant is cast with the DB function...

I have never used that property for reading data from the database because it has a lot of "interesting" features. Conceptually what you have to do is use properties to read each field of each row returned. Check here for examples and some good discussion.

Mike...

[Yair]

QUOTE (MikePorter @ Dec 22 2008, 05:10 PM)

I have never used that property for reading data from the database because it has a lot of "interesting" features.

Can you expand on that? I noticed that using it makes the query considerably faster for larger data sets (natually) and I'm pretty sure that NI uses this in their new version of the DB toolkit. I know you think the toolkit is unnecessary, but presumably both NI and Microsoft have at least some people who know what they're doing, right (ducks to avoid the shower of rotten tomatoes)?

P.S. Welcome to LAVA. Did Ben manage to convince you?

[alukindo]

Hi Jed:

I use LabVIEW with ADO along with MS SQL Server and MS Access databases. I have all along used the 'GET ROWS' method to cast the variant data in to a LabVIEW 2D array of strings with consistent performance and correctness of output.

. . . Based on your reported anomaly, it appears that you may be getting the bad result from a new SQL query that may have a syntax error that is intolerant when executed via ADO, while the MySQL query utility somehow corrects the syntax error automatically or is simply tolerant to the syntax error. I am assuming here that the MySQL utility does not use ADO drivers to access MySQL.

E.g: in SQL Server T-SQL syntax, concatenating a string with a value column requires you to do something like: 'Result = ' + STR(Value,6,1) in this case the 'Value' is a column that holds a numeric parameter. This numeric parameter column cannot be concatenanted with a string unless it is first converted to a string value itself. The STR() function does that. Otheriwise, if you were to do: 'Result = + STR(Value,6,1)' then the location of apostrophes will cause the result to appear with the plus signs just as you see it in your bug report.

Can you otherwise, paste the MySQL query for us to check-out?

Regards

Anthony L.