• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • Last Modified:

VFP Convert MySQL Queries to Work With VFP DB

Hi all. I have a portion of a program that I need to change from connecting to a MySQL db to connecting to a VFP database.

The table names and the data they contain are identical. Below is a sample select that I am currently using with MySQL.

Is there a way I can change just the connection in order to avoid having to rewrite all the select, update and add queries throughout the program?


lcTableName = "supplier"
lcCursor = "MySQLResult"
lcQuery="SELECT supplier_id FROM " + lcTableName + " WHERE supplier_id < ?lnSup_ID ORDER BY supplier_id DESC LIMIT 1"
lnResult=SQLEXEC(pnConnection,lcQuery,lcCursor)
IF lnResult < 1
	DO SQLQueryError IN error_messages WITH lcQuery
	RETURN
ENDIF

Open in new window

0
formadmirer
Asked:
formadmirer
  • 4
  • 3
  • 2
1 Solution
 
pcelbaCommented:
You could change just the connection string in theory BUT  MySQL and VFP are two different dialects of the SQL language so you must expect some differences, e.g. VFP does not recognize LIMIT clause in your query.

If you are using some language dependent functions in your queries (e.g. INSTR, DATE, etc.) then you cannot use such query in both languages without appropriate adjustments.

Other aspect is data access. Why would you use SQLEXEC() to access VFP data when you can access DBF data directly? SQLEXEC() can just slow the process down in this case. If you execute the query as a macro in VFP (by executing  &lcQuery  command) then the data access should be faster.

So the query review throughout the program is a must most probably but you may try to change connection string and test your app.

You should collect all SQLEXEC calls into a data tier in your app and solve all SQL engine dependent differences at one place. It could then result in a dialect independent data engine...
0
 
Olaf DoschkeSoftware DeveloperCommented:
Pavel has answered that well. The main question is, why go to DBF at all? If it works fine, then don't change it. Do you want to access that data in a VFP app, too? You could think of a seperate process synching DBF and MySQL table and then use MySQL as always and the DBF as the other app is used to.

Such a bridging process shouldn't be hard to do. If you already have migrated the data you've already done one step of the process.

Of course it would be more ideal to only have data in one table. You could also adapt the other app to mysql just for that table.

The main point about not converting the code is, that even just in this example it's not easy to see, if you can convert all your queries. As Pavel says LIMIT is not an option in VFP. Moving dat around is much easier than adapting code.

Bye, Olaf.
0
 
formadmirerAuthor Commented:
Thanks. I did find the problem with LIMIT already and have done a few tests substituting TOP with good results so far.

What I am thinking is that down the road they will want this converted back to using the MySQL db, and I was trying to find a way to simplify the transition from one db type to the other, i.e. make less work for me.

What I may end up doing, as much as I don't really want to, is to comment out the original sQL and just leave it in place, and add the new vfp queries below each. That way I can simply switch back in the future if I need to.

I'm going to close this as your reply was really what I was expecting, but I needed to hear it from someone who really knows their stuff.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
formadmirerAuthor Commented:
In the time I was posting my comment yours came in Olaf.

The reason, as was explained to me, is that they want to remotely connect to a mysql db, which contains about 90% of the data required for the program.

The rest of the data will be stored locally on the client PC and they don't want the client to have to install any additional software such as mysql - therefore the dbf tables come into play.
0
 
Olaf DoschkeSoftware DeveloperCommented:
In detail about this query: It searches a supplier_id lower than lnSup_ID, but it orders the result in descending order and limits it to 1 result.

So, if supplier_id is the primary key, which is quite probable for a table called suppliers, this query does no more and no less than a SKIP -1 in supplier_id order, if you'd do that in a DBF.

You have to put in your interpretation and brains to figure out the intention and redo in foxpro. It's not even necessarily a query you serach for.

Actually if a SKIP -1 fits this it looks like this code was created to "simulate" the SKIP -1 and find the previous supplier in ID order. If you did that migration, do you stil have your old foxpro code perhaps? There you'll perhaps find all your answers, simply resuse what you had for the DBF.

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
>they don't want the client to have to install any additional software such as mysql
OK, that's a reason. But the software was for a 100% mysql backend before, wasn't it?
For what reasons do they want to move out 10%? Security? Performance? Suppliers should not be stored online? Is this data per client and not to be shared online? Then why was it programmed that way before?

Ok, you don't have to answer all that. I don't really care, but it still seems like an unnecessary rewrite.

Bye, Olaf.
0
 
formadmirerAuthor Commented:
I love that "I don't really care" ;)

This code has been used to interconnect with several different shopping carts over the years. Some had more features than others, therefore some have more fields and tables.

Some of this additional data is useful within the program, even if the current shopping cart doesn't make use of it. That data is to be stored locally on the client's PC so that the remote MySQL db for the current cart does not have to be altered.

Thanks for the help.
0
 
Olaf DoschkeSoftware DeveloperCommented:
>This code has been used to interconnect with several different shopping carts over the years.
>Some had more features than others, therefore some have more fields and tables.

Very fine, that was the short explantion I hoped for. :)

OK, then it should be limited work needed to move some of the code to VFP for the one or the other shopping cart not offering the mysql tables as another shopping cart. Good Luck and you knw, we're here to support you.

Bye, Olaf.
0
 
pcelbaCommented:
If you decide to adapt the source to a different data storage then don't use comments but conditional compilig. You'll just need one #DEFINE command at the beginning of your code or in some header (.H) file and appropriate #IFDEF/#IFNDEF commands around SQLEXECs.

To switch between data platforms is then very easy.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now