EF's Blog

Tutorial : Using MySQL ODBC Connector with Foxpro 9 and MySQL

Posted on: March 15, 2009

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.

38 Responses to "Tutorial : Using MySQL ODBC Connector with Foxpro 9 and MySQL"

hi Erwin, it did helped me…kindly give more examples for grid…:)

Glad it helped you. What kind operation for grid do you want ?

Note that VFP 9 added a 4th [optional] parameter to SQLEXEC() to get the number of rows returned in the result set by Select or the number of rows affected by Update, Insert, Delete.

Handy if you want to know the success/failure of your query.

I’m not used to use the 4th optional parameter. Would you mind to give me an example? it will be nice. thanks🙂

i wonder why this update command didnt work…

SQLEXEC(CONN, “UPDATE TBLMESSAGE SET THEMESSAGE = VMESSAGE, FROMWHOM = VFROM, TOWHOM = VTO, RECEIVINGOFFICER = VRECEIVINGOFFICER,
TIMERECEIVED = TIMEREC1, DATERECEIVED = DATEREC WHERE MESSAGEID = CURRENTID”)

does the VMESSAGE, VFROM, VTO, TIMEREC1, DATEREC, and CURRENTID is a memory variables ?

So far as I know, and i hope it would help you, if I were you, i would do this :

SQLEXEC(CONN, “UPDATE TBLMESSAGE SET THEMESSAGE = ‘”+VMESSAGE+”‘, FROMWHOM = ‘”+VFROM+”‘, TOWHOM = ‘”+VTO+”‘, RECEIVINGOFFICER = ‘”+VRECEIVINGOFFICER+”‘,
TIMERECEIVED = ‘”+TIMEREC1+”‘, DATERECEIVED = ‘”+DATEREC+”‘ WHERE MESSAGEID = ‘”+CURRENTID+”‘”)

Good Luck🙂

thank you Erwin Fransiscus for visual foxpro sample “grid” connecting to mysql

thx dude…

It doesn’t work for me. Program Error: Connection handle is invalid.😦

Hi, i have a trouble!!

I made it all right….i guess xD but when i run it, say me “the file setdatabse.prg doesnt exists” why show me this???

Please help!!

Hi, its me again xD but i was watching something when i try to delete or config againg the DSN it show me this “Invalid atribute string” and i have win vista, and the version odbc driver is 5.1.7 maybe that is the problem, but i dont know. What do you say??

The Same with aila, The Update Command Didn’t Work

SQLEXEC(FAA_Con1,’update faa_component_in set descr=?brg&nn,type=?tipe&nn,;
pn=?ipn&nn,sn=?isn&nn,dte_in=?dte_in,mnfc=?iMnFc&nn,nm_cust=?pel,;
remark1=?rmk1&nn,remark2=?rmk2&nn where letter_no=?no and wo_no=?wo&nn’)

how come mine has an error message saying command contains unrecognized phrase/keyword

can you help me with my problem sir? please? I really need the solution. ..

hi Erwin, thanks for the tuts.what if you want to update all tables in a database?is there a way to do it.I have a complete vfp application and am trying to migrate to mysql database.I have already converted the dbf’s to mysql.please advise.thanks

how to create table sql from visual foxpro? thanks

We’re using VFP9 SP2 on an XP SP3 box to connect to a remote MySQL database hosted at GoDaddy.

On occasion, SQLEXEC with UPDATE returns a negative result (fails) on a table which otherwise presents no problem. It then continues to fail with ANY form of SQLEXEC (UPDATE, INSERT, DELETE, etc) until we open a new connection handle. SQLDISCONNECT does work, though. It’s as though the handle gets corrupted or runs out of resources or something. Has anyone run into this?

please give a helping hand here;i want to connect mysql 3.51database to foxpro-9 but it gives me a torrid time!

How come? Can u explain the detail?

Erwin Fransiscus i got a little problem in my project, i do everything what you said but VFP show me an error that says “file c:\xxxxx.techers.dbf doesn’t exists” does it guess VFP be already connected with MySql? help me please…:/

I don’t think your vfp has been connected to mysql..has the odbc connection test has been done to make sure mysql has connected with the odbc..?

ok thanks for your help… i opened the phpmyadmin and upload the database in sql format, then launch the odbc connector and i change configuration for database name and ready, it’s functionally xD

It’s me Again🙂 well i want to do a Loop to save the data of rows in an array… Is it possible? (With the same source code)

Thanks for your help

I have never tried it in foxpro myself but logically it could.
Of course you have to edit the code to match your needs🙂

mmmm but how i can extract variables from SQLEXEC() function a variables in FoxPro syntax?

sorry cannot help you for that, i have no foxpro installed on my laptop anymore. maybe google will help you

What about a combobox? to show some values

good post man!
It really hel me a lot!
Can you help me?
how it would be the code to fill all the textbox?

Example if you enter the id number of any record.
how the others textbox will fill the information that belongs to that id number.

Thanks for all!

My regards!ª

Hi Erwin, can you explain how to connect with ADO Connection with VFP and MySQL, which gives more performance than above said code.

Thanks
MMN

Thank you. It was very great without images also!

Thank you Erwin!

Hallo is there a way to connect a website application i have created with joomla such that it connects to a fox pro database which is external. That is for example a hotel booking module in a website that connects to a foxpro database that is external and not contained on that server?

As for I know, if I’m not mistaken, is you cannot do that. Foxpro database is designed to work offline, or should I say, you need another database system to work online like mySQL or SQL server.

Very Good Work

hi,
im having a problem with this command SQLEXEC(gnConnHandle,”INSERT INTO store (cus_code,brdnch_code) VALUES (‘011′,’012’)”)
can anyone help me.
thanks in advance

@sherwin
what’s the error message?
whether it is in accordance with the type of the variable in the insert?

hi to all of you guys, as the example given above it was almost successful when i performed it..but i got a problem with contents of Grid..the datas appear in every field are “Memo” but the datas stored in my wampserver are all accurate using the insert command of sql, only the Grid is my problem. i used wamperver and and navicat mysql.i also installed mysql-connector-odbc-3.51.25-win32.exe.can you help me guys..thank you so much.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

EF’s Blog

I'm just ordinary people who lived in an extra-ordinary world.

EF's Blog Stats

  • 126,564 hits
March 2009
M T W T F S S
« Feb   Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Archives

%d bloggers like this: