Connect to MySQL from VFP

[AndrewMozley]

(A continuation of a Dec 2011 thread)

I would like to read some records from a MySQL database. I have MySQL 5.5 installed, and also the MySQL workbench.

If I go into the WorkBench I can see that there is a server (I believe) called Local MySQL. Within that there is a schema defined Trakker4_operations and within that, a table operations_parts

Perhaps optimistically, I have written this code :

lConnect = SQLCONNECT("trakker4_operations")
SQLEXEC(lConnect,"SELECT * FROM operations_parts")

However, the SQLCONNECT function call fails (returning -1). I feel that I need to provide different parameters to the function call. Do I e.g. have to tell SQLCONNECT that it needs to use a MySQK driver, and if so. how. Grateful for any guidance.

Andrew

 

[JRB-Bldr]

I don't see where you CONNECT to the SERVER - "Local MySQL"

You might want to look over the various Connect Strings at:

http://www.connectionstrings.com/mysql

Other references you might want to look at might be:

http://books.google.com/books?id=Nq...#v=onepage&q=connectstrings mysql vfp&f=false

or

http://saltydogllc.com/?p=193

or

http://www.cullytechnologies.com/presentations/vfpaccessingmysql/VFP Accessing MySQL.pdf

or

http://fox.wikis.com/wc.dll?Wiki~VFPCommandSQLStringConnect

Or just do a Google Search for: connectstrings mysql vfp

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Andrew,

As you correctly supposed, the first line of code is incorrect.

You have two options. Either:

lConnect = SQLCONNECT("XXX")

where XXX is the name of an existing DSN on your computer (not the name of the database). To create a DSN, use the ODBC applet in Control Panel. You will need to point it to your MySQL driver, and then fill in the boxes in the resulting dialogue. Once you've named the resulting DSN, you pass that name to SQLCONNECT (in quotes).

The other option is to use SQLSTRINGCONNECT() instead of SQLCONNECT(), passing a connection string (in quotes). I'm not sure exactly what the format is of the connection string for MySQL, but it will look something like this:

Driver={MySQL ODBC 5.5 Driver};Server=localhost;Database=Trakker4_operations;
User=Andrew;Password=MyPassword;

(Note that this should all be on one line. I split the above line to make it fit better in the message window.)

Perhaps you can give one or other of those methods a try, and come back if you need any more detailed information.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

SQLCONNECT("trakker4_operations")

see the help on SQLCONNECT.

Also any SQL Passthrough function returning -1 to denote an error also leaves more info about the error for the next call to AERROR(), that will then create an error array with error number 1526 (ODBC error), SQL Passthrough is ODBC, and only ODBC.

This means the SQL Passthrough functions don't raise the normal system error handler, you'lll never have to handle error 1526 as a raised exception, this error always happens silent, so SQL Passsthrough functions are an exception to the exception/error handling. Their direct result is a success/error indicating number and you get the concrete error only by usage of AERROR.

This is very important to know, when working with SQL Passthrough.
It's mentioned here:

http://msdn.microsoft.com/en-us/library/aa975616(v=vs.71).aspx

Bye, Olaf.