Connecting to MySQL

[PointOfLight]

Since this came up in another thread, I decided to toy with it a bit. To use MySQL in a PPL application (PC only), you must first download the ODBC drivers from MySQL: Home Page. Once you get the drivers installed, do the following in your program:


//put this in the #init function of a form
#include "adodb"

func MySQL_OpenDB(db$, dsn$)
local(con$, strConn$);

con$ = CreateCOMObject("ADODB.Connection");

Invoke(con$, "Open", dsn$);
if (not IsNull(ComError%))
return(false);
end;
db.ConnectString$ = dsn$;
#endif

return(true);
end;


Somewhere else in the program, you might have code like this:


local(connect$, cnt$);
global(db$, rec$);

connect$ = "Driver={mySQL ODBC 3.51 Driver};Server=fileserver;Port=3306;Option=4;Datab ase=rustysab_sabredb;Uid=root;Pwd=MyPassword;";
struct(db$, adodb);
struct(rec$, adorec);
if(MySQL_OpenDB(&db$, connect$))
if(ADO_SelectTable(db$, &rec$, "tblreviews"))
ShowMessage(ADO_FieldCount(rec$));
else
ShowMessage("Unable to open table");
end;
else
ShowMessage("Unable to connect to database");
end;


The key here is the connect string. It is a series of properties separated by ; The properties are as follows:

//should be static except for the driver version (ex: 3.51, 5.0)
Driver={mySQL ODBC 3.51 Driver};
//if on the same box as the app, put localhost here
Server=fileserver;
//only need if server is remote
Port=3306;
//not sure what this does, but I think it needs to
// be different if you want to edit records
Option=4;
//database to connect to
Database=rustysab_sabredb;
//user with sufficent rights to database
Uid=root;
//password for user specified in Uid
Pwd=MyPassword;

I haven't validated that all of the ADO functions work with MySQL, but most of them should. I know that the RecordCount property does not work. Enjoy, and if I get more spare time I might even write up a little something about this.

[tigme]

Good to know it will work on the PC. Another way one could establish a connection from the PPC through IP would be to create a PHP gateway script on a server. The PHP code handle the login and password details and your PHP app can then just send some commands through TCP/IP to the PHP server code. People using Flash are using a similar topolgy to connect to a MySQL server.

See:
http://www.webmonkey.com/webmonkey/programming/php/tutorials/tutorial2.html

or do a search Google for "flash mysql"

Hope it helps someone.

[bmanske]

Recently, Microsoft released the SQL Server Compact Edition. <a href=&quot;http://www.microsoft.com/sql/editions/compact/default.mspx&quot;>http://www.microsoft.com/sql/editions/compact/default.mspx</a> </p>

When I get the time, I plan on trying it on the PPC. I'll post my results... unless someone else beats me to it.</p>