este es el texto de la pagina
MySQL Database Access from RPG
by Scott Klement
System iNetwork Programming Tips Editor
April 17, 2006 —
For years, I've been asked, "How do I access a third-party database from
an RPG program?" I've been asked this question about Sybase, Oracle,
Clipper, MySQL, PostgreSQL, dBase, and SQL Server. My answer has always been
"RPG's support for Java makes it possible to call JDBC drivers."
Recently, I needed to do this myself, because my company has two FreeBSD
machines running MySQL, and I needed access to them from an RPG program.
In this article, I show you the steps that I took to make it possible for
me, a lowly RPG programmer, to access a MySQL database.
*Install the JDBC Driver*
Before you can use the MySQL JDBC driver from your RPG program, you need
to install the driver on your system. MySQL calls its JDBC driver
Connector/J. You can get it from MySQL.com <http://www.mysql.com/> by
clicking Developer Zone|Downloads|MySQL Connector/J.
The current stable version of Connector/J is version 3.1. Here's a direct
link to the download page for that version:
http://dev.mysql.com/downloads/connector/j/3.1.html
If you have a Windows desktop, you're probably better off downloading the
ZIP archive of the source and binaries. If you're a Linux or Unix user, you
can use a TAR.GZ version. In either case, download the file to your PC and
extract it into a temporary folder.
Inside the ZIP or TAR archive that you download from MySQL.com is the JDBC
driver in a JAR file. Because I downloaded version 3.1.12, the JAR file is
named as follows:
mysql-connector-java-3.1.12-bin.jar
You need to install that JAR file on your iSeries. The way you do that is
by copying it to the /QIBM/UserData/Java400/ext directory in your system's
IFS. I chose to do that using FTP in binary mode, but you could also use
Windows Networking or iSeries Navigator.
If you have no access to the /QIBM/UserData/Java400/ext directory, another
alternative is to upload the file to a different spot in your IFS, and
include it in your CLASSPATH. For example, if you place it in the /test
directory of the IFS, you could use the following CL command to add it to
your CLASSPATH:
ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/test/mysql-connector-java-3.1.12-bin.jar:.')
You need to set this before loading the Java Virtual Machine (JVM) in each
job. If you place the file in the /QIBM/UserData/Java400/ext directory, you
do not need to change your CLASSPATH variable at all.
*Test the Driver from Java*
Now that the driver is installed, it makes sense to run a quick Java
program to verify that that the driver is working. I wrote a simple one that
connects to the server and lists the user IDs and hostnames from the MySQL
user database that I use for testing the driver. I named the program
testMySql.java and included it with the code download for this article.
Before you try out the testMySql.java program, you should edit the source
code and look for the following three lines (near the top):
String mysqlurl = "jdbc:mysql://test.example.com/mysql";
String userid = "klemscot";
String password = "bigboy";
You need to change that to point to the server that you want to test. In
this example, I'm connecting to a server named test.example.com and using
the user ID klemscot and the password bigboy.
After that Java code has been updated with the correct values for your
server, you can compile it from QShell on the iSeries by typing the
following command:
javac testMySql.java
After it has compiled, you can run it from QShell as follows:
java testMySql
If you receive errors about classes not being defined, it means that Java
can't find the MySQL JAR file. Either you installed it in the wrong
location, or your CLASSPATH is set up improperly.
If you receive errors about not being able to connect, make sure that your
iSeries is properly configured for your network and that no firewall is
blocking you from accessing the MySQL server.
If you get really stumped and can't get the test program to work, consider
asking for help in *iSeriesNetwork.com's* C/C++ and Java
forum<http://www.iseriesnetwork.com/isnetforums/forums.php>.
*Connecting from RPG*
Now that JDBC is installed, it's time to use RPG to access it. To simplify
this process, I created a service program called JDBCR4. In that service
program, I created a subprocedure called MySQL_Connect(), which connects to
a MySQL database server.
Here's what my MySQL_Connect() subprocedure looks like:
P MySQL_Connect B export
D MySQL_Connect PI like(Connection)
D server 256A varying const options(*varsize)
D database 256A varying const options(*varsize)
D userid 50A varying const options(*varsize)
D password 50A varying const options(*varsize)
D prop s like(Properties)
D drv s like(Driver)
D temp s like(Connection)
D url s 1024A varying
D conn s like(Connection)
/free
monitor;
jdbc_begin_object_group(1000);
// Create connection properties object
prop = new_Properties();
setProperty(prop: s('user') : s(userid));
setProperty(prop: s('password'): s(password));
// Register a MYSQL driver
drv = new_MySQL();
registerDriver(drv);
// Connect to database server
url = 'jdbc:mysql://' + %trim(server) + '/'
+ %trim(database);
temp = Driver_Connect( drv : s(url): prop );
jdbc_end_object_group(temp: conn);
on-error;
return *NULL;
endmon;
return conn;
/end-free
P E
The subprocedure starts by creating a Java Properties object. This is an
object that stores some properties about a connection. In this case, I add
user and password properties to the Properties object. I use this later to
log in to the MySQL server.
Next, it loads the JDBC driver for MySQL. The new_MySQL() prototype is
actually a call to the com.mysql.jdbc.Driver() routine in that JAR file
that I uploaded earlier. The registerDriver() prototype calls Java's
DriverManager to register the MySQL driver with the system.
Finally, the subprocedure calls the Driver_Connect() prototype to create a
connection to the MySQL database. It passes the Properties object so that a
user and password can be used to log in to the MySQL server.
The MySQL_Connect() subprocedure shown here is the only routine in my
JDBCR4 service program that's MySQL specific. If you want to connect to a
different database platform, you need to register that platform's JDBC
driver instead of the MySQL one, and you have to pass the appropriate
properties for that driver.
When I want to connect to a MySQL database, I call the MySQL_Connect()
subprocedure as follows:
D conn s like(Connection)
.
.
conn = MySql_Connect( 'test.example.com'
: 'myDatabaseName'
: %trim(userid)
: %trim(passwrd) );
The JDBCR4 service program also contains a subprocedure called
JDBC_close() for closing the connection when I'm done working with the
database. I won't show you the code for the JDBC_close() subprocedure here,
but it's included in the code download for this article.
To disconnect from the server, I call JDBC_close() at the end of my
program:
JDBC_close(conn);
*Running Non-Query Statements*
JDBC has two types of SQL statements that you can run: those that return a
set of records, which I like to call "queries," and those that do not return
anything. JDBC refers to the latter as "update" statements, because most of
the time you use them to perform an Insert or Update SQL statement.
To execute these SQL statements that don't return anything, I coded a
subprocedure called JDBC_ExecUpd(). For example, if I want to create a new
table named RpgTest, I call the JDBC_ExecUpd() subprocedure as follows:
D rc s 10I 0
/free
rc = JDBC_ExecUpd( conn : 'Create Table RpgTest'
+ '('
+ ' ItemNo Dec(5,0) Not Null, '
+ ' Count Int Not Null, '
+ ' LastChg Timestamp '
+ ' Default CURRENT_TIMESTAMP, '
+ ' LastSold Date Default Null, '
+ ' TimeTest Time Default Null, '
+ ' Price Dec(7,2) Not Null, '
+ ' Description VarChar(25) not Null, '
+ ' Primary Key( ItemNo )'
+ ')' );
if (rc < 0);
ErrMsg = 'Unable to CREATE table';
endif;
The first parameter to the JDBC_ExecUpd() subprocedure is the connection
object returned by the MySQL_Connect() API. The second parameter is the SQL
statement to execute, in this case the Create Table statement. The return
value is the number of records that the SQL statement affected. If the SQL
statement doesn't operate on records, it returns 0. If an error occurs, it
returns -1.
*Running Queries*
When an SQL statement does return information, it's stored in a result
set. A result set is similar to the cursors that we use in embedded SQL in
RPG — it's an object containing the results of the query, and I can read
record and field information from it.
To work with queries, I created three subprocedures in the JDBCR4 service
program. JDBC_ExecQry() executes a query and returns a Java ResultSet
object. JDBC_NextRow() positions the ResultSet to the next row (or "record"
to us old-fashioned RPG guys), and JDBC_GetCol() gets a column (or "field")
from the current row.
When I'm done reading the result set, I call the JDBC_FreeResult()
subprocedure in JDBCR4 so that the memory can be returned to the system.
Here's an example of running a query and reading the results:
D rs s like(ResultSet)
.
.
rs = jdbc_ExecQry( conn : 'Select ItemNo,Description'
+ ' from RpgTest'
+ ' where LastSold < DATE("2003-01-01")'
);
dow (jdbc_nextRow(rs));
ItemNo = %int(jdbc_getCol(rs: 1));
Desc = jdbc_getCol(rs: 2);
except; // print the itemNo and Desc fields
enddo;
jdbc_freeResult(rs);
The JDBC_GetCol() procedure takes a column number for its second
parameter. This is the number of the column in the result set. For example,
in my Select statement, I requested two fields, ItemNo and Description, to
be returned for each record in my result set. When I call JDBC_getCol() with
a column number of 1, I get the ItemNo field for the current record. When I
call it with a column number of 2, I get the Description field.
*Prepared Statements*
My JDBCR4 service program also has routines that let you prepare SQL
statements with parameter markers. This is great for inserts and updates
because you can use the parameter markers to insert data from your RPG
fields.
Here's an example of using a prepared statement to insert a record into
the RpgTest table:
D rc s 10I 0
D stmt s like(PreparedStatement)
/free
stmt = JDBC_PrepStmt(conn : 'Insert Into RpgTest '
+ '(ItemNo, Count, LastChg, LastSold, '
+ 'TimeTest, Price, Description)'
+ ' values (?,?,?,?,?,?,?)' );
if (stmt = *NULL);
ErrMsg = 'Prepare Statement failed!';
return *OFF;
endif;
JDBC_setDecimal (stmt: 1: ItemNo);
JDBC_setInt (stmt: 2: Count);
JDBC_setTimeStamp(stmt: 3: LastChg );
JDBC_setDate (stmt: 4: LastSold );
JDBC_setTime (stmt: 5: TimeTest );
JDBC_setDecimal (stmt: 6: Price );
JDBC_setString (stmt: 7: Description );
rc = JDBC_ExecPrepUpd( stmt );
if (rc < 0);
ErrMsg = 'Execute Prepared Failed!';
return *OFF;
endif;
JDBC_FreePrepStmt( stmt );
The JDBC_PrepStmt() prepares an SQL statement. In this case, it's an
Insert statement in which I set the values of the fields to ?,?,?,?,?,?,?.
These question marks are parameter markers and are replaced with data from
my variables when the statement is executed. To supply the values, I call
the JDBC_setDecimal(), JDBC_setString(), and other JDBC_setXXXX() routines.
When I pass a 1 for the second parameter to the JDBC_setXXXX() routines, it
replaces the first question mark with my variable. When I pass a 2, it
replaces the second question mark, and so on.
Like the other SQL statements that I demonstrated, prepared statements
come in two varieties: those that return a result set, and those that do
not. In the preceding example, the statement does not return a result set,
so I call the JDBC_ExecPrepUpd() subprocedure to run it. If I used a
prepared statement for a query, I'd call JDBC_ExecPrepQry() to run it, and
it'd return a result set that I could read in my program.
*Get Your Own Copy*
If you'd like to download and try out my JDBCR4 service program, you can
obtain it in the code download for this article. At the top of the
JDBCR4.rpgle source member, there are instructions for how to compile the
service program. I also include a MYSQLTEST.rpgle source member (with its
own compile instructions) that demonstrates how to call the routines in
JDBCR4.
__________________________________________________
Forum.HELP400 es un servicio m&#225;s de NEWS/400.
&#169; Publicaciones Help400, S.L. - Todos los derechos reservados
http://www.help400.es
_____________________________________________________
Para darte de baja visita la siguente URL:
http://listas.combios.es/mailman/listinfo/forum.help400
