Tutorial : Using MySQL ODBC Connector with Foxpro 9 and MySQL

Yesterday, I got a project to handle with MySQL and Visual Foxpro. I’m used to MySQL, and so does with Visual Foxpro. The trouble is that I’ve never work on both of them in the same time. Usually I’m using Foxpro integrated DBMS when using Visual Foxpro, and MySQL used to be a DBMS when I’m using PHP, C#, or ASP.Net.
I got a little problem on connecting those software, but after multiple tries, it did works.
How that happen? here’s the simple tutorial:

Note : I’m using Microsoft Windows XP SP 3, Visual Foxpro 9, and WAMP server 1.7.3, I’m working on a single desktop pc. I’m sorry i didn’t include the images example on doing this, hope with just the texts you would understand how it works.

First, create a database on MySQL. The database is quite important, because when you install and configure ODBC, there’s an option of which databases you want to use. I’m also using PHPmyadmin for the GUI of the database application. here’s for example.

> CREATE DATABASE dbfarmasi
> CREATE TABLE `dbfarmasi`.`tb_brg` (`kdbrg` CHAR( 6 ) NOT NULL ,`nmbrg` VARCHAR( 50 ) NOT NULL , PRIMARY KEY ( `kdbrg` )) ENGINE = INNODB
> INSERT INTO `dbfarmasi`.`tb_brg` (`kdbrg` ,`nmbrg`) VALUES ( ’00001′, ‘First item’ );
> INSERT INTO `dbfarmasi`.`tb_brg` (`kdbrg` ,`nmbrg`) VALUES ( ’00002′, ‘Second item’ );

i’m named the database “dbfarmasi” and the table “tb_brg”. So here, we got a database and a table, rock!

Then you download a software called MySQL ODBC Connector. I’m using mysql-connector-odbc-3.51.25-win32.exe, which is free, and can be downloaded by visit mysql site. You can try to google it.

Install. After installation has done, you should go to “Control Panel” > “Administrative Tools” > “Data Sources (ODBC)”. And then there’s pop-up window of ODBC. In “User DSN” tab, right side, press “Add” button”, and select “MySQL ODBC 3.51 Driver” as the data source, and “Finish”.

Another windows will appear, it is a configuration window for ODBC. Try this :

Data source name : koneksi
Description : connect foxpro and mysql
Server : localhost
User : root
Database : dbfarmasi

The DSN name is up to you, so does the description.
The default Server is localhost, ’cause I’m not using remote database.
Default user name is root, as the first super user name in mysql.
I’m using no password on mysql (it is set to none by default when you’re install WAMP)
and the database I’m using is dbfarmasi, as i’ve created before.

Ta-da. The connection has been made.

Now, for the visual foxpro. here is a simple one :
Create a project, whatever the name is, save it. on “Project Manager” window, choose “Code” tab.
Press “New” on Program.
Here is the code:

PUBLIC P1
STORE SQLCONNECT(‘koneksi’, ‘root’) TO P1

close and save it to name :  ”setdatabase“

now, back to “Project Manager” window, choose “Document” tab, press “New” button to create a new form, and pick the “New Form”, not the “Form Wizard” thing.
After that, an empty form will appear. create a two Textboxs.
named the first textbox to : txtkdbrg and the second textbox to :txtnmbrg.
Add a grid to form. named it as gridContainer.

Now double-click the form,  select the object form1 and the procedure init, and add this code:

DO setdatabase
thisform.gridContainer.RecordSource=”"
SQLEXEC(P1,”select * from tb_brg”,”tb_brg”)
IF NOT USED(“tb_brg”)
USE tb_brg ALIAS tb_brg
ENDIF
SELECT tb_brg
thisform.gridContainer.RecordSource=”tb_brg”

Save the form and run it. TA-DA. It works, its connected. The content of table “tb_brg” is showed by the form by the gridContainer.

It means that the ODBC works. Here the connection thing :
do setdatabase
SQLEXEC(P1,”select * from tb_brg”,”tb_brg”)
means :
run program code “setdatabase” (which we’ve created before, for connection)
select P1 as ODBC connection DNS, (connection name variable in setdatabase (replace SQLCONNECT(bla bla bla))
“select * from tb_brg”, the sql query, select all from table tb_brg
“tb_brg”, is a cursor in visual foxpro, that used for alias.

So, with SQLEXEC([connection name],”[sql query]“,[cursor name]), you can do sql command: SELECT. Don’t forget to add cursorname/alias when you’re using SELECT.

You don’t have to add cursorname if you’re using INSERT or DELETE query.
example :

SQLEXEC(P1,”insert into tb_brg (kdbrg,nmbrg) values (‘”+thisform.txtkdbrg.value+”‘,’”+thisform.txtnmbrg.value+”‘)”)

means you insert value from textbox (txtkdbrg and txtnmbrg).
for delete command example :

SQLEXEC(P1,”delete from tb_brg where kdbrg =’”+thisform.txtkdbrg.Value+”‘”)

Once again, i’m sorry i’m not using images for this tutorial.
Hope you understand what i meant in this simple tutorial, and hope it help you.