Solved

VFP 9 MySQL Select Data Type Problem

Posted on 2010-09-08
4
569 Views
Last Modified: 2012-06-27
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!!

0
Comment
Question by:formadmirer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:apresence
ID: 33633471
In MySQL, try:
lcQuery="SELECT CAST(nh1 AS DECIMAL(6,2)) FROM " + lcTableName
0
 

Author Comment

by:formadmirer
ID: 33633521
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.
0
 
LVL 6

Accepted Solution

by:
apresence earned 500 total points
ID: 33633530
@formadmirer: Yeah, I know you said you had tried it, but I figured it was worth a shot.

I'm not sure what's going on.  If I had to guess, ODBC is being confused about the data types.  Have you tried installing the latest MySQL ODBC drivers?  If the versions are different from the PC that is working and the one that isn't, that's a pretty good culprit to look at.
0
 

Author Closing Comment

by:formadmirer
ID: 33633604
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.
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Microsoft is moving in-place eDiscovery & hold from ECP to EOP console under Content Search in Search and Investigation Options.  In this post, I will be showing you how to export emails to a PST file using the Content Search Options.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question