pro2mysql


Interface for Progress 4GL to query mysql databases.


Intro
-------------------------------
I had some data in a mysql database that I wanted to retreive into my
Progress 4GL application.  I tried to "shell out" from UNIX and run the
mysql command line client, but it got messy.  Using the C api provided
with mysql, and Progress' ability to run C functions in a shared library,
I have attempted to map the C functions into the Progress 4GL.  The result
seems to be stable, and about as fast as if connecting to mysql from php.

I tried to make the functions work like they do PHP.

Requirements
-------------------------------
This was tested on progress 9.1D on Solaris, 32 bit, and mysql server 
running on linux and solaris.  I have no idea how it will perform on
other platforms.

* mysql client, with the libmysqlclient.so in LD_LIBRARY_PATH 
  (if using solaris, you will have to build mysql from source as the
  library is not included with the binary distribution like with linux).
  You probably want to compile with the same bits as progress (32 or 64)
* mysql server.  tested on linux and solaris, I don't know why it wouldn't 
  work on another platform.  Tested with mysql 4.0, 4.1, and 5.0
* enviroment variables below set to some values
* probably more that I forgot

Contents
--------------------------------
mysql-functions.i  -- include this into your programs for the mysql functions

createdb.p         -- this program will create the schema and data for a 
                      progress database into mysql.  If you run this against
                      sports, you will end up with a sports database on
                      your mysql server.  You can run this aginst other
                      progress databaes too, just watch out for mysql
                      reserved words used as fields

sports-query-.p    -- sample program that queries the sports database and
                      displays some fields

REAME.txt          -- this file

Functions
------------------------------------

Integer db_connect(input-output resource as memptr)
This connects to the database.  You must define the resource memptr in progress.
always returns true (I'm assuming we can always connect to our database).
  
Integer db_query(input resource as memptr,input sql as character)
This executes a query.  If this query will update the database, it will be logged into a table called 
update_log in the utility database (if the variable db_log_query is set)
The resource comes from the db_connect(), sql is the sql to be executed.
return values:  0 if query were successful, anything else means unsuccessful.
 
Resource db_use_result(input resource as memptr)
You must call this after calling db_query if you issued a "select."  You supply the resource (from 
db_connect()), and the functions returns a result resource, which you must define in your program 
as memptr.
 
Integer db_fetch_into_array(input res_resource as memptr)
This function gets the next row of select query.  You supply the res_resource from 
db_use_result(), and the mysql-row[] is populated with your results. An integer is returned.  If 
there was a row available, 1 is returned, any other return value means no row.
 
Const Integer db_free_result(input res_resource as memptr)
You must call this after db_use_result() when you're done with your results.  supply the 
res_resource from db_use_result().  This always returns 1.
 
Const integer db_close(input res_resource as memptr)
You MUST call this to close the db when you're done.  This always returns 1.
 
Character db_last_error(input resource as memptr)
provides the last error.  If you get an error from db_query(), you can run this to get a text 
description of the error.

Environment variables
-----------------------------------
MYSQLHOST 	hostname of mysql server
MYSQLDB   	name of default mysql database you'd like to use
MYSQLPORT	optional port of mysql server if you'd like to connect via TCP
MYSQLUSER	username
MYSQLPASS	password

Bugs
------------------
send them to me!

Usage
-------------------
use this at your own risk!  if you do use it, please let me know!


Matthew Lang
matt@moredirect.com