Link to home
Start Free TrialLog in
Avatar of formadmirer
formadmirerFlag for United States of America

asked on

VFP 9 MySQL Select Data Type Problem

Hi All. I have an FP ap that I've been using for a couple of years now that selects data from around 50 mysql tables. All has been working fine until today when I installed the ap on a second PC.

On this PC and this PC ONLY I'm having a problem with some of the selects.  Everything still works fine on the original PC. Both are WinXP SP3 utilizing ODBC 3.51.

The problems seem to stem from data type issues. Most I have fixed with simple IF vartype statements.

But one I simply can't get a handle on. The data is being selected from a MySQL 5.1.33 server running on WinXP SP3. The FieldType is Decimal 6,2.

After I connect to the db, I simply run:

lcQuery="SELECT nh1 FROM " + lcTableName

and use SQLEXEC to place the value into a Cursor.

The value of nh1 in the table is 0.190

However, any attempt to use the value returned as Numeric, for example a simple if statement:

IF (nh1 < 2)
      WAIT WINDOW "T"
ELSE
      WAIT WINDOW "F"
ENDIF

fails with a mismatched data type error. It seems the date type of the value returned is Text.

I've tried using VARTYPE to change the data type, I've tried CAST, nothing seems to work.

Can someone PLEASE show me how to Convert this data to numeric, either in FP (preferrable) or in MySQL as it's being selected from the table?? Thx!!

Avatar of apresence
apresence

In MySQL, try:
lcQuery="SELECT CAST(nh1 AS DECIMAL(6,2)) FROM " + lcTableName
Avatar of formadmirer

ASKER

apresence, thanks, but I actually did already try that. The value returned, when tested with the simple vartype IF statement, still evaluates to being text and can't be used in any numeric comparison statements without generating the mismatch data type error.

I know it SHOULD work, it's just not.
ASKER CERTIFIED SOLUTION
Avatar of apresence
apresence

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Unbelievable - you hit the nail on the head. After nearly 3 hours trying to figure this out, I had just thought to check the odbc drivers myself.

My PC (where the ap was designed) is using 3.51.17, the new PC, 3.51.11.

Updated the new PC to the same driver as mine and VOILA! - it now works!

Thanks for the help - you get the points.