VFP and SQL Conversion

[asergeev]

I have a query tool that was written in FP/VFP. It works fine for the last 10 years in our application. Now we are moving from FoxPro backend to RDBMS, to SQL Server in particular. We are using RV and SPT and VFP DAL for the data connection. Unfortunately, ODBC can't convert such VFP keywords as EMPTY (for any field type), ALLTRIM or ==.
Any ideas how we can convert the stored VFP queries to run them as SPT without a lot of code changes?

Thanks,
Andrei

 

[Mike Lewis]

Andrei,

There's no automatic way of doing this. You will just have to look up each function in turn, and edit the SQL code accordingly.

For example, the is no ALLTRIM() in SQL Server, but you can use RTRIM(LTRIM()) instead. VFP functions like CTOD(), TTOD() and VAL() translate to CONVERT(). SUBSTR() becomes SUBSTRING(). DATE() becomes GETDATE(). And so on.

SQL Server doesn't follow VFP's odd system of comparing strings, where the right-hand string is considerd to be equal to the left-hand string even if it is shorter. For that reason, there is no need for a double-equals operator. If you want to do a partial string comparison, you use the LIKE operator with wildcards.

As you can see, you really need to familiarise yourself with T-SQL (the SQL dialect used by SQL Server) before you can go much further.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[asergeev]

Mike,
Thank you. This is what I thought.

Andrei.

 

[tatin]

Sorry Andrei. Mike is correct.

I am in your shoes as well, and I am taking it to a higher level. For my part, I am moving from VFP to MYSQL and I am redoing my queries and data access codes to be easily adaptable to changing backends.

Hence, I am a public variable pcRDBMS which informs my application of the Database Server in effect and I use a DO CASE each time I am going to write an SQL string. If I move to a new Backend I search for all the instances of pcRDBMS in my code and add another CASE to deal with their peculiarity.

 

[Mike Lewis]

Tatin and Andrei,

Of course, it would be very much easier if all your data access code was localised into a single class. But it's difficult to do that retrospectively.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TamarGranor]

I haven't worked with it much yet, but will the new Data Explorer in VFP 9 help with this kind of translation?

Tamar

 

[asergeev]

Friend of mine actually sent me an intersting link for such library that looks very promising.

http://www.dirfile.com/easysql4fox.htm

Thanks,
Andrei.

 

[Mike Lewis]

Andrei,

That looks interesting. But I wonder if it also converts the function names. I haven't read the entire description yet.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)