API Connection to mySQL in Windows

[Fuzzmaster]

Hopefully this helps someone as it certainly caused me fits...

Problem:

Webspeed application needed to connect and retrieve from both Progress and mySQL databases.

Problem Compounded:

ODBC DataServer is out as Progress does not support mySQL connections. (Many thanks to this forum saving me that aggravation).

Solution:

Matthew Lang (A Friggin' Genius & Hero) wrote API mapping for mySQL functions dubbed "pro2mysql". Available (for now) here:

http://www.oehive.org/amduus/pro2mysql/

Additional Problem:

"pro2mysql" was written for use on solaris/unix/linux systems based on the mySQL shared library "libmysqlclient.so" (not so cool for Windows servers running IIS - No shared libraries).

How I somehow managed to get it to work:

1. Download "pro2mysql" and copy "mysql-functions.i" to the webspeed working directory.
2. Install mysql-client (I used version 5.0) on Webserver, including additional library option.
3. Copy "libmysql.dll" and "libmysql.lib" files from the mySQL installation directory to SYSTEM32 folder (Not sure you need to do this).
4. Add these Environment Variables to Windows ("Environment Variables..." - Advanced Tab - after right-clicking on "My Computer"):
   • MYSQLHOST - Hostname of mysql server
   • MYSQLDB - Name of mysql database
   • MYSQLPORT - Port used by mysql server (Optional).
   • MYSQLUSER - Username
   • MYSQLPASS - Password
5. Reboot (I couldn't find another way).
6. Find and Replace every instance of "libmysqlclient.so" with "libmysql.dll" in the file "mysql-functions.i".
7. Change your drawers as you may have made a mess watching it work.

How to use it (Now that it Works):

<!--WSS

{./inc/sys_fnc.i} /* ### Include Pro2MySQL Functions - Thanks Matthew Lang! */

DEF VAR conn AS memptr NO-UNDO.
DEF VAR rs AS memptr NO-UNDO.
DEF VAR dbERR AS INT NO-UNDO.
DEF VAR dbMSG AS CHAR NO-UNDO.
DEF VAR dbSQL AS CHAR NO-UNDO.
DEF VAR dbROW AS INT NO-UNDO.

ASSIGN dbERR = 0
dbROW = 0.
dbSQL = "SELECT * FROM table;". /* ### QUERY */

db_connect(conn).

ASSIGN dbERR = db_query(conn,dbSQL).
IF dbERR <> 0 THEN
ASSIGN dbMSG = "Query Error: " + db_last_error(conn).
ELSE
ASSIGN rs = db_use_result(conn)
dbROW = db_fetch_into_array(rs).

IF dbERR <> 0 THEN DO: -->

<H1>`dbMSG`</H1>
<!--WSS END. IF dbROW <> 1 THEN DO: /* ### No Records */ -->
<H3 CLASS="noprint">No Records Found Matching Search Criteria.</H3>
<!--WSS END. /* ### No Records */ ELSE DO: /* ### Records Exist */ -->
<TABLE>
<TR><TH CLASS="left">Field</TH></TR>
<!--WSS DO WHILE dbROW EQ 1: /* ### While Records Exist */ -->
<TR><TD CLASS="left">`mysql-row[1]`</TD></TR>
<!--WSS ASSIGN dbROW = db_fetch_into_array(rs). END. /* ### While Records Exist */ -->
</TABLE>

<!--WSS END. /* ### Records Exist */ -->

 

[mattl]

Hi,
I saw this a little late I have a new version now that's a lot more flexible, I'll try to keep you posted after I clean it up and release it.

Matt

Hopefully this helps someone as it certainly caused me fits...

Problem:

Webspeed application needed to connect and retrieve from both Progress and mySQL databases.

Problem Compounded:

ODBC DataServer is out as Progress does not support mySQL connections. (Many thanks to this forum saving me that aggravation).

Solution:

Matthew Lang (A Friggin' Genius & Hero) wrote API mapping for mySQL functions dubbed "pro2mysql". Available (for now) here:

http://www.oehive.org/amduus/pro2mysql/

Additional Problem:

"pro2mysql" was written for use on solaris/unix/linux systems based on the mySQL shared library "libmysqlclient.so" (not so cool for Windows servers running IIS - No shared libraries).

How I somehow managed to get it to work:

1. Download "pro2mysql" and copy "mysql-functions.i" to the webspeed working directory.
2. Install mysql-client (I used version 5.0) on Webserver, including additional library option.
3. Copy "libmysql.dll" and "libmysql.lib" files from the mySQL installation directory to SYSTEM32 folder (Not sure you need to do this).
4. Add these Environment Variables to Windows ("Environment Variables..." - Advanced Tab - after right-clicking on "My Computer"):
   • MYSQLHOST - Hostname of mysql server
   • MYSQLDB - Name of mysql database
   • MYSQLPORT - Port used by mysql server (Optional).
   • MYSQLUSER - Username
   • MYSQLPASS - Password
5. Reboot (I couldn't find another way).
6. Find and Replace every instance of "libmysqlclient.so" with "libmysql.dll" in the file "mysql-functions.i".
7. Change your drawers as you may have made a mess watching it work.

How to use it (Now that it Works):

<!--WSS

{./inc/sys_fnc.i} /* ### Include Pro2MySQL Functions - Thanks Matthew Lang! */

DEF VAR conn AS memptr NO-UNDO.
DEF VAR rs AS memptr NO-UNDO.
DEF VAR dbERR AS INT NO-UNDO.
DEF VAR dbMSG AS CHAR NO-UNDO.
DEF VAR dbSQL AS CHAR NO-UNDO.
DEF VAR dbROW AS INT NO-UNDO.

ASSIGN dbERR = 0
dbROW = 0.
dbSQL = "SELECT * FROM table;". /* ### QUERY */

db_connect(conn).

ASSIGN dbERR = db_query(conn,dbSQL).
IF dbERR <> 0 THEN
ASSIGN dbMSG = "Query Error: " + db_last_error(conn).
ELSE
ASSIGN rs = db_use_result(conn)
dbROW = db_fetch_into_array(rs).

IF dbERR <> 0 THEN DO: -->

<H1>`dbMSG`</H1>
<!--WSS END. IF dbROW <> 1 THEN DO: /* ### No Records */ -->
<H3 CLASS="noprint">No Records Found Matching Search Criteria.</H3>
<!--WSS END. /* ### No Records */ ELSE DO: /* ### Records Exist */ -->
<TABLE>
<TR><TH CLASS="left">Field</TH></TR>
<!--WSS DO WHILE dbROW EQ 1: /* ### While Records Exist */ -->
<TR><TD CLASS="left">`mysql-row[1]`</TD></TR>
<!--WSS ASSIGN dbROW = db_fetch_into_array(rs). END. /* ### While Records Exist */ -->
</TABLE>

<!--WSS END. /* ### Records Exist */ -->

 

[Fuzzmaster]

Let the salivation commence...

Given the huge response to my initial post I just assumed myself a moron and figured there must be some ridiculously easy method that I don't know about (& no one felt like sharing).

 

[rguidi]

Hi,
I saw this a little late I have a new version now that's a lot more flexible, I'll try to keep you posted after I clean it up and release it.

Matt

first of all, thank to Matt for his solution, i'm using it and it works great (we use progress 9 on Win xp)

now the problem: i'm testing our application on OpenEdge 10 on Windows 7 , and I'm getting an error
during the connection to mysql db (function db_connect -> mysql_real_connect) , is there any fix
needed to make it work on windows 7 ? (I tested also OpenEdge 10 on WinXP and that is working ok
so I desume it's a windows 7 behaviour)

EDIT: i have just tested the latest version of libmysql.dll (from http://dev.mysql.com/downloads/ )
and I get the same results (works in winxp, but error on win7)

tnx much for any hint

PS: if u have released a new version of your code I'm really interested

 

[mattl]

I have a new set of "drivers." I completely rewrote them from scratch, and should be compatable with OE 10 (all versions), both 32 and 64 bit. I haven't been able to test it on different platforms, however. There are two sets: One is a progress -> mysql drivers (ie run queries from progress against mysql, get results back into progress). The other is php -> progress, mainly used for basic sql to insert/update/delete into the progress database. This is a good for replication.

If there's any interest, I'll post somewhere. Sorry so late.

Matt

first of all, thank to Matt for his solution, i'm using it and it works great (we use progress 9 on Win xp)

now the problem: i'm testing our application on OpenEdge 10 on Windows 7 , and I'm getting an error
during the connection to mysql db (function db_connect -> mysql_real_connect) , is there any fix
needed to make it work on windows 7 ? (I tested also OpenEdge 10 on WinXP and that is working ok
so I desume it's a windows 7 behaviour)

EDIT: i have just tested the latest version of libmysql.dll (from http://dev.mysql.com/downloads/ )
and I get the same results (works in winxp, but error on win7)

tnx much for any hint

PS: if u have released a new version of your code I'm really interested