VFP & MySql 2

[David Higgs]

I have a Third party application that uses a database in MySQL. I have managed (with the help of my son!) to connect to the MySQL database with VFP. I currently copy the contents of the MySQL Database to a VFP.dbf and use the data accordingly. I would however like to be able to run the third party software 'as is', at the same time using my VFP as a front end to it and 'append' data to the MySQL database. Is this feasible? I understand it maybe a steep learning curve!!

Regards,
David

 

[GriffMG]

That should be doable, you would need a way to connect to the MySQL database through something like ODBC or OLEDB - there will be drivers for that out there.

I haven't tried that yet, so I would be interested to see how you get on.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[GriffMG]

You might look here:

It's not new...

http://www.dbforums.com/showthread.php?1701571-Visual-Foxpro-and-MySQL-connection

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[JRB-Bldr]

While I have done a good bit of VFP + MS SQL Server backend applications, I too have never done it with MySQL

Regardless....
A Hentzenwerke book you might want to look at might be:

MySQL Client-Server Applications with Visual FoxPro​

Link​

Additionally you might look over: Link

Good Luck,
JRB-Bldr

 

[Dan Freeman]

I would proceed with caution.

If you update a database "owned" by a third-party application, you risk violating unknown business rules within the application and can cause quite a mess.

I'm not saying you shouldn't do it. But please do it with appropriate consideration and caution.

The others have provided references for information. The mechanics are pretty much the same as for any remote data source.

 

[Olaf Doschke]

What Dan says.

Aside of the precautions you should take, you already got to a stage of extracting data as DBF. Well, that's one step too far, you can query with SQLEXEC and turn the result cursor into an updatable cursor with several CURSORSETPROPS setting remote table, keyfields, updatablefieldlist, etc and then can do the core VFP data persisting command TABLEUPDATE. It acts on a DBF or remote backend by applying the buffered changes, all new, all changed and all delted rows are processed, so you can make any change to the remote backend.

If you're that far an easy way to APPEND data as usual is:
1. Query into a cursor with a statement like SELECT * FROM table WHERE 1=0, meaning you pull no data at all, but get the table schema/structure in a VFP cursor.
2. Make it updatable
3. APPEND to it as you'd do to any normal cursor or DBF
4. TABLEUPDATE()

The cursoradapter could also be helpful, as you just need the same connection string as with SQLSTRINGCONNECT and the cursoradapter builder can help you to make the cursor updatable in an interactive way, easier than via several CURSORSETPROP() calls. Then creating the cursoradapter can replace step 1 and 2, if you configure it to create its cursor at initialisation automatically.

Bye, Olaf.

 

[David Higgs]

Thank you all for your help, there is a lot to digest so I will proceed with caution! I will report back on my progress.

Regards,
David

 

[JRB-Bldr]

As Dan has rightly pointed out above, BE CAREFUL

At times when I have had to do something similar, I found out that when the 3rd party application did something that seemed simple, it could (and many times DID) do something unexpected.

For example: When it seemed as though a specific operation should have needed to update one and only one record in one table, I often found that it was indeed updating 2 or 3 tables - thereby maintaining previously unknown referential integrity within the multiple data tables.

I had to meticulously investigate the operations of the 3rd party application for each and every activity that I was going to possibly affect BEFORE I was confident that I had 'mapped' everything that needed changing for those operations.

Obviously the more complex and/or comprehensive the 3rd part application, the more likely this is to occur.

Again, have fun but be careful.

Good Luck,
JRB-Bldr

 

[David Higgs]

Making some slow progress with VFP / MySQL project.

I have set up an ODBC connection and have access to the MySQL database.

I can SELECT and BROWSE Data ok. However when I try to SELECT data BETWEEN two dates with the following code:-

msql= "SELECT * FROM &HRD_LOG WHERE BETWEEN (Col_time_on,DATETIME(2015,01,01,00,00,00),DATETIME(2015,12,31,23,59,59))"

I get a "Can't call database" error.

I have tried replacing the commas with "/" and "-" with the same result.

NOTE (Col_time_on is in "datetime" format.)

Regards,
David

 

[Mike Lewis]

David,

You need to keep in mind that, when accessing any "remote" database, including MySQL, you need to use SQL syntax that conforms to the database in question, not to VFP.

In this case, you are using the BETWEEN() function. That's a VFP function, and naturally MySQL does not recognise it. In MySQL, the equivalent syntax is like this:

[tt]SELECT <whatever> FROM <my table> WHERE x BETWEEN y AND Z[/tt]

Also, MySQL does not recognise your VFP DATETIME() function. In MySQL, you can use the datetimes directly, without any conversion function.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

In MySQL, you can use the datetimes directly, without any conversion function

Just to clarify that, in MySQL dates are held as strings, in the format YYYY-MM-DD. So, in this case, your SELECT would look like this:

[tt]SELECT * FROM MyTable WHERE Col_time_on BETWEEN '2015-01-01' AND '2015-12-31'[/tt]

You'll see I've changed &HRD_LOG as well, as MySQL does not recognise VFP's macro expansion operator.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Hello Mike,

Thank you for your reply.

I tried your suggestion:-
SELECT * FROM MyTable WHERE Col_time_on BETWEEN '2015-01-01' AND '2015-12-31'
and all worked ok.

Ok on VFP's macro expansion operator I should have mentioned that in my test program I have the following:-

HRD_LOG = "TABLE_HRD_CONTACTS_V01" 

msql = "SELECT * FROM &HRD_LOG WHERE Col_time_on BETWEEN '2015-01-01' AND '2015-12-31'"
														
IF SQLEXEC(xCon,msql,"cHRD") <= 0					
   	MESSAGEBOX("Can't Call Database",0+16,"Error")
  	SQLDISCONNECT(xCon)								
	RETURN 
ENDIF 

BROWSE FONT 'verdana',12

Which seems to work ok


Regards,
David

 

[Mike Lewis]

Glad it's working, David. Of course, your macro expansion should work OK the way you've done it.

When SQLEXEC() returns a negative number, that indicates an error from the back end. You obviously know that. But to handle that properly, you should also determine exactly what the error is. The way to do that is to call AERROR(), which will give you the actual error code or message returned by MySQL.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Gagnon]

SELECT * FROM &HRD_LOG

I don't think this would work In SQL.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[David Higgs]

Thanks again for your help Mike, much appreciated.

I'll take a look at the Error Reporting at some stage.

SELECT * FROM &HRD_LOG

I don't think this would work In SQL.

It's working fine! Take another look at my code and see how I've applied HRD_LOG.

Regards,
David

 

[Olaf Doschke]

Indeed Mike, you can do macro expansion even within strings, it's expanded before the command is passed to MySQL ODBC. It's faster to cut the strings into two parts and add "SELECT * FROM "+HRD_LOG+" WHERE..." or use Textmerge(), but you can do macro expansion.

Besides that last point in the follow up discussion it's nice to see you get it going.

One more hint in regard of your own "Can't Call Database" message. Even if you won't want to display details to end users, you can get more info about the error MySQL reports, when SQLEXEC returns a negative value, if you create the error array with AERROR(), see the SQLEXEC help topic. You would have seen yourself you mixed VFP code into your queries, which MySQL does not understand. That's always one of the concerns with converting a VFP application to a server database. You not only have the different client/server paradigm, you also need to change your queries to not use VFP functions, you use the database server SQL dialect and that also has some pros, eg MySQL offers quite extensive functions for encryption and clauses like LIMIT you don't have at hand native in VFP.

Bye, Olaf.

 

[David Higgs]

Hello Olaf,

Thank you for your input. I will indeed be looking at the AERROR() command to improve error reporting.

All noted reference VFP / SQL; I'm sure I will be back with more questions as I progress.

Today I will be inserting my new code into my application to see how it performs. The new code is part of a front end to a third party application in 'readonly' at the moment. I have carried out some tests on inserting data into the SQL which looks encouraging.

Thanks again to all for the assistance.

Regards,
David