VFP - MySQL integration 3

[AndrewMozley]

I have a VFP application (Pegasus OPERATIONS) in which I would like to read information from a table (if that is the correct terminology) created in MySQL. I am not familiar with MySQL - but am prepared to learn.

The MySQL application will maintain a table with record for each job which it completes. I plan to read it from my VFP application, update several of my tables and then update the record in the MySQL table with a flag to say that I have done my updates.

Is this possible? The acronym "ODBC connectivity" comes to mind. although I have no great experience of this. Any guidance much appreciated. Thanks, Andrew

 

[Mike Lewis]

Hello Andrew,

Good to see you hear again.

Basically, MySQL is just like any other so-called "remote database" (that's the term that's used in the VFP Help). You're right that ODBC is relevant. But that's only the first step.

I suggest you start by reading the following section of the Help file:

Using Visual FoxPro / Developing Visual FoxPro Applications / Enhancing Applications Using SQL Pass-Through Technology

Then focus on three key functions: SQLCONNECT(), SQLEXEC() and SQLDISCONNECT(). These will give you what you need to read and update the MySQL table.

These are not the only way to approach it. You might also look at remote views and cursor adapters. But, in my opinion, the above functions will be the simplest and most direct.

Come back if you have any follow-up questions (as I'm sure you will).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

I am not familiar with MySQL

While, if you are the one who is going to maintain this application, you should get yourself familiar with MySQL, knowing it 'in depth' is not the place to start.

As Mike said above, understanding how to Read/Write data to/from an 'alien' database (or as the VFP Help says - 'remote database'), you need to understand the basics of the VFP commands that he mentioned.

Additionally you will need the SQL Command strings which will be executed from, but not within your VFP application to be written in the language/syntax of the 'alien database', not the VFP SQL language. A number of the command syntax will be the same, but there may likely be differences that you need to be aware of - that's where you need to know MySql (for now).

It might be worth your while to get the book:
MySQL Client-Server Applications with Visual FoxPro

http://www.hentzenwerke.com/catalog/mysqlvfp.htm

Another good reference, despite it being for SQL Server, might be:
Client-Server Applications with Visual FoxPro and SQL Server 7.0

http://www.hentzenwerke.com/catalog/csvfp.htm

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

As you already can see from the answers of the others: Yes. And as you only need to read one table and set a flag shallow knowledge of MySQL is sufficent.

You need the MySQL ODBC driver:

http://dev.mysql.com/downloads/connector/odbc

Latest version should be able to connect to any mysql datbase.

One of the things that might turn out as a show stopper is, if the mysql database is not available remotely in the real sense of the word, that is your desktop app may not connect to an online database, if the port of the mysql service is open for remote access, if the database server isn't configured for remote access, if the concrete single mysql database is not allowing remote acces and/or you have no user/password enabled for remote access.

So the first step is to make sure your hoster is supporting that feature or explicitly looking for remote database hosting.

The other route to handle that would be to move the querying and updating to local scripts on the server, most probably php. You can call these from VFP like you can navigate to any URL and you can make http requests with GET or POST in that case as an indirect interface to mysql via php scripts.

Bye, Olaf.

 

[AndrewMozley]

Thank you Mike, JRB and Olaf.

I realize that I had better learn to walk first! I would like to read a record from the Northwind Database which is supplied in the SAMPLES folder of Microsoft Office\Office11.

I believe that I have set up an ODBC data source (pointing to that database), called NorthSrc, by going into Control Panel | Administrative Tools | Data Sources(ODBC), then clicking on ADD, selecting the Microsoft Access Driver, then specifying a Data Source name of NorthSrc and then navigating to the NorthWind.mdb database in order to specify the database path.

Back in VFP9, I then run this program

[blue]SET STEP ON
LOCAL x1
x1 = SQLCONNECT("NorthSrc")
SQLEXEC(x1,"SELECT * FROM Customers INTO Cursor ABC")
SET STEP ON
RETURN .T.[/blue]

I believe that the SQLCONNECT() function call executes successfully, returning a value of 1.

The SQLEXEC() function, however, appears not to be successful - that is to say, I have no open Aliases when I key in SET in the command window.

Sorry this is such trivial stuff, but I would again be grateful for guidance if you have been able to read a record from an external database (in this case the Access database, Northwind.mdb)

 

[Mike Lewis]

Andrew,

Your immediate problem is "INTO Cursor ABC". Remove that clause from your SELECT and it should work OK. The results (that is, the contents of the Customers table) will be in a cursor named Sqlresults.

The reason it didn't work is that INTO CURSOR is VFP syntax, but it is Access that is executing the query.

I'm not clear why you need the SET STEP in there. If you just do a BROWSE at the end of your code, you should see the customers.

I assume you understand that what you are doing here getting data from Access, not from MySQL. The general principle is identical, but the method of setting up the ODBC connection might be different. Also, you will need to ensure that the SELECT that you send is compatible with MySQL syntax (it probably will be, if the query is a simple one).

Apart from that, it looks like you are on your way.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[AndrewMozley]

Thank you Mike. That certainly does the business. My SET STEPs were included because I am an error-prone programmer.

Yes, I appreciate that this is just working with Access. I don't have any MySQL data yet (will get it on Tuesday) and no doubt some command will be different, but am delighted to find that the techniques work.

And thanks Olaf. I will ask my SQL developer to check that his database is open for business.

Andrew

 

[JRB-Bldr]

Here are a couple more small pieces of advice...

When you do the following...

x1 = SQLCONNECT("NorthSrc")
SQLEXEC(x1,"SELECT * FROM Customers INTO Cursor ABC")

you should capture the results of the SQLxxxxxx command execution and interrogate it to see that it completed OK.

nHandle = SQLCONNECT("NorthSrc")
IF nHandle > 0
  * --- GOOD Connection Established ---
  nRet = SQLEXEC(nHandle,"SELECT * FROM Customers", "ABC")
  IF nRet = 1
    SELECT ABC
    * --- Command Execution Successful ---
    <do whatever for Successful>
  ELSE
    * --- Command Execution NOT Successful ---
    <do whatever for NOT Successful>
  ENDIF
ELSE
   * --- Connection Not Good ---
   <do whatever>
ENDIF

On a more basic level, in your simple code above, I do not see you executing the full ODBC Connection String
(see examples at:

http://www.connectionstrings.com/mysql)

to establish the initial Connection to the MySQL database (nHandle).

Now if you have a Connection defined within a VFP Database using an existing Windows workstation DSN (ODBC 'connection') and have that VFP Database open, then you can do it as you show above.

But if you want to do the whole thing without using a pre-defined VFP Database Connection, you will have to define the whole ODBC Connection string and use it in your code.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

open for business."

Well, make that "ready and configured for remote access". Or at least capable to change towards enabling remote access.

If you have an SQL developer you can delegate that task to, I'd be surprised he wouldn't know about remote access, unless he just familar with vfp or access. Most any RDBMS database system you use for sql access is a database only and remote access is normal to RDBMs, VFP and Access are the special cases, really.

You're already helped about how to use SQLEXEC(), in general remember you're leavin foxpro with that command, the command is sent and executed by the remote database system, in that systems sql dialect.

So for MySQL your reference would be

http://dev.mysql.com/doc/refman/5.6/en/sql-syntax.html

There is no "INTO" clause there, as that is sql dialect specific to VFP. SQLEXEC() does create cursor by default, and nothing else, so eg creating a table needs an addition COPY TO commmand, if you would want that one day, but cursors are fine of course.

See SQLEXECs parameters, the third parameter "cCursorName" is for determining, what cursor the sql should generate, but that part is VFPs job, it takes the output of the ODBC driver and generates a foxpro cursor of it, that is not done by the remote database, so there is no need and no sense in telling the remote system what you want in VFP.

Bye, Olaf.

 

[AndrewMozley]

Thanks JrbBldr.

You mention that I have not executed the full ODBC connection string. I appreciate that I may have been lucky in being able to define a data source (from the Windows Control Panel) and then connect to that data source using the SQLCONNECT() command in Visual Foxpro.

On the website to which you provide a link, one example is :

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Is this an additional parameter to SQLCONNECT() or to CREATE CONNECTION? And if I do it this way, is this an alternative to defining a data source through the Windows Control Panel?

A worked example would be very helpful.

Thanks. Andrew

 

[Olaf Doschke]

You're usage of SQLCONNECT() with a DSN is fully sfficient. To use a connection string you would need to change to usiong SQLSTRINGCONNECT(), see help for it's parameterisation. No need to change, but if you change, you have a DSNless connection.

It's a matter of taste what is easier to confiugure. I've had administrators that prefer control via DSNs, you can also centrally store connectionstrings and in my oppinion have it easier to maintain them than DSNs.

No matter what you use - SQLCONNECT() or SQLSTRINGCONNECT(), the resulting handle is good for usage of SQLEXEC() and of course SQLDISCONNECT().

Bye, Olaf.

 

[Olaf Doschke]

In regard to CREATE CONNECTION: That is adding a connection to DBCs and the basis for creating remote view, but not part of the so calle d SQL Passthrough functionality of all the SQL...() functions of foxpro. You will not need this at all, unless you want to change to remote views.

Bye, Olaf.