David:
once you installed BSF4Rexx as per the directions given in the section
'Installation in a "Ten Second" Nutshell' in
<http://wi.wu.ac.at/rgf/tmp/20090614_b4r4beta/readmeBSF4Rexx.txt>, you
will find a JDBC example in the subdirectory "samples/ReneJasen",
entitled "jdbc.jrexx".
This example demonstrates how to interact with a PostgreSQL, derby and
hsqldb (actually the default database for OpenOffice Base, which can
also interface to any JDBC or ODBC database). Watch out for the
userid/passwd for the PostgreSQL example.
Here's the Rexx code using JDBC:
/* original author: Rene Jansen ("rvj"), 2006-11 */
/* BSF4Rexx JDBC Example rvj 20061128, 20061201
Pre-requisites for the "derby" database: add "derby.jar" to your
"CLASSPATH" environment variable
Pre-requisites for the "hsql" database: add "hsqldb.jar" to your
"CLASSPATH" environment variable
Version: 0.4
*/
-- specify imports, hence we can use BSF4Rexx constructors ("new" and
"newStrict")
prop = .bsf~bsf.import("java.util.Properties")
driverMgr = .bsf~bsf.import("java.sql.DriverManager")
parse upper arg dbms +1 -- get first character in uppercase
select
when dbms = 'P' then -- 'PostgreSQL'
do
-- instantiate jdbc driver
bsf.import('org.postgresql.Driver')~newinstance
-- make the dbms connection and open a statement
statement =
driverMgr~getConnection('jdbc:postgresql:rvjansen','rvjansen','')~createStatement
end
when dbms = 'H' then -- 'hsql', used for OpenOffice.org since
version 2.0 (fall 2005)
do
-- instantiate jdbc driver
-- bsf.import('org.hsqldb.jdbcDriver', .nil)~newinstance
bsf.loadClass('org.hsqldb.jdbcDriver')~newinstance
-- make the dbms connection (memory only dbms in this case) and
open a statement
statement =
driverMgr~getConnection('jdbc:hsqldb:mem:aname','sa','')~createStatement
end
when dbms = 'D' then -- 'derby'
do
-- instantiate jdbc driver
bsf.import('org.apache.derby.jdbc.EmbeddedDriver', .nil)~newinstance
-- make the dbms connection and open a statement
props = prop~new
props~put("user", "user1")
props~put("password", "user1")
statement =
driverMgr~getConnection('jdbc:derby:derbyDB;create=true',props)~createStatement
end -- when do
otherwise
do
say "no dbms specified, exiting ..."
exit
end
end
call dropTable statement, "test" -- make sure table gets dropped, if it
exists
-- create the table
statement~executeUpdate("CREATE TABLE test( name char(42), place char(42))")
-- insert some rows
statement~executeUpdate("INSERT INTO test (name, place) VALUES('Rony G.
Flatscher', 'Vienna' )")
statement~executeUpdate("INSERT INTO test (name, place) VALUES('Lee
Peedin', 'Wallace' )")
statement~executeUpdate("INSERT INTO test (name, place) VALUES('Rene
Jansen', 'Amsterdam')")
-- select database content: specify query and execute to get result set
rs = statement~executeQuery('select name, place from test order by name')
do while rs~next -- iterate over rows in result set
say pp(rs~getString("name")~strip) "from" pp(rs~getString("place")~strip)
end
say
-- use an aggregate query, defining an alias name (for retrieval by
column name)
rs = statement~executeQuery('select count(*) as total_fans from test')
-- calculate total
do while rs~next
-- the following statement uses the alias name to address the column
say "BSF4Rexx has at least" rs~getString("Total_Fans") "fans! (Using
alias name 'Total_Fans' as argument.)"
/* the following statement forces a specific signature to be used,
making it clear
that the Rexx string is to be used as a Java int(eger);
using the message "bsf.invokeStrict" expects the name of the method
first and then
pairs of typeIndicator, value for each argument: */
say "BSF4Rexx has at least" rs~bsf.invokeStrict("getString", "int", "1")
"fans! (Using positional argument.)"
end
::requires bsf.cls -- get the Java support
::routine dropTable -- drop a table
use arg stmt, tableName
call BsfShowErrorMessage 0 -- switch off printing the stack trace
signal on syntax -- catch exception, if table does not exist
yet (very first run)
stmt~executeUpdate("DROP TABLE" tableName)
syntax:
return
Here's the output, if running this program against a derby and a hsql RDBMS:
E:\rony\dev\bsf\src\samples\ReneJansen>rexxj2 jdbc.jrexx D
[Lee Peedin] from [Wallace]
[Rene Jansen] from [Amsterdam]
[Rony G. Flatscher] from [Vienna]
BSF4Rexx has at least 3 fans! (Using alias name 'Total_Fans' as argument.)
BSF4Rexx has at least 3 fans! (Using positional argument.)
E:\rony\dev\bsf\src\samples\ReneJansen>rexxj2 jdbc.jrexx H
[Lee Peedin] from [Wallace]
[Rene Jansen] from [Amsterdam]
[Rony G. Flatscher] from [Vienna]
BSF4Rexx has at least 3 fans! (Using alias name 'Total_Fans' as argument.)
BSF4Rexx has at least 3 fans! (Using positional argument.)
E:\rony\dev\bsf\src\samples\ReneJansen>
As you can see it demonstrates how to create and drop a table, how to
insert rows into the table, how to use select-statements (how to
retrieve the column values by name or index position) and how to iterate
over a result set. Pretty much of that what you request.
It should be quite easy to add your MySQL database in addition to this
demo/nutshell program.
HTH,
---rony
Rony G. Flatscher wrote:
> David:
>
> David Ruggles wrote:
>
>> This is an open question to the list and anyone else that might be
>> interested.
>>
>> Is anyone available who would be interested in writing an ODBC or MySQL
>> interface for ooRexx 4.0?
>>
>> Safe Data, Inc. is heavily dependant on ooRexx and MySQL, however as a
>> company and individually we don't have the C expertise nor the time to
>> implement this ourselves. On the other hand we should be able to front some
>> bounty to see that it gets accomplished. What I would like to do is lay out
>> our goals and see what kind of bounty might be needed and find anyone else
>> in the community who would be willing to offer bounty as well. I'm going to
>> list two sets of requirements, ideal and minimum. Anyone willing to add
>> bounty please indicate which set of requirements (and/or add your own) and
>> how much, anyone willing and able to do the work please indicate how much
>> bounty would be needed for each set of requirements.
>>
>> Ideal Requirements:
>> * Cross Platform
>> * ODBC & unixODBC (any ODBC/unixODBC driver)
>> * Stored Functions
>> * Stored Procedures
>> * All Normal SQL Statements (COMMIT, ROLLBACK, SELECT, DELETE, etc.)
>>
>> Minimum Requirements:
>> * Windows (strongly desire Linux also)
>> * MySQL
>> * ALL Normal SQL Statements
>>
>>
> You have all of this already available to you. Use Java instead of C,
> C++ and you are in business using BSF4Rexx.
>
> To evaluate with ooRexx 4.0 you could use the official version of
> BSF4Rexx at <http://wi.wu-wien.ac.at/rgf/rexx/bsf4rexx/current/>.
>
> [There is an "experimental" version (at beta level, but not yet
> announced) at <http://wi.wu-wien.ac.at/rgf/tmp/20090614_b4r4beta/>,
> which is rewritten for ooRexx 4.0. This version runs the BSF4Rexx
> testUnits of lately that a student wrote for the older, "current"
> version without a failure, so I am pretty confident that it is fully
> compatible and stable.]
>
> Give it a try and start to enjoy what cross-platform Java functionality
> is directly available to your Rexx programs! In the Java world
> interfacing with RDBMS is realized with JDBC. If you research a little
> bit, you will be surprised an overwhelmed what is available to you.
>
> HTH,
>
> ---rony
>
>
>
>
------------------------------------------------------------------------------
_______________________________________________
Oorexx-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/oorexx-devel
