Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OO Rexx and DB2 LUW

Status
Not open for further replies.
mkmc

mkmc

Programmer
Oct 16, 2023
4
0
0
US
Has anyone written a rexx that uses data stored in DB2 LUW?
 
Sort by date Sort by votes
I use REXX in two scenarios:
#1 The REXX program runs on iSeries and uses data stored on DB2 on IBM iSeries
#2 The REXX program runs on a Windows PC and uses data stored in DB2 on IBM iSeries

In scenario #2 I use the Open Object REXX interpreter. To connect from Windows PC to DB2 database which is running on the other computer, the ADODB.Connection is used.
I don't have DB2 LUW but IMO to work with it a similar method can be used as I'm using now.

What's your problem and how can we help you?
 
Upvote 0 Downvote
mikrom, thanks for the quick reply. I am currently an app programmer on IBM Z/OS. I use rexx extensively to process data on DB2 for z/os. I want to prepare to do similar things with my own personal data. I want to leverage my current skills to do this. I am trying to determine what is the best and most economical software to obtain to do this on my small windows machine.

I am thinking of downloading OOREXX and free DB2 for LUW. Does this make sense? I kinda wanted to see an example of how this would be done. My programming experience on anything but the mainframe is minimal if at all - a little MS Access years ago.

Your thoughts/suggestions are welcome. Examples are great.
 
Upvote 0 Downvote
Hi mkmc,

Here is an example how to read data stored in DB2 on IBM iSeries from Open Object REXX program running on Windows PC.

In DB2 database running on IBM iSeries (aka AS/400) I have a Database schema in which I have a table. I want to connect from my Windows PC using userid and password to the database, selecting from the table some records and save them into a CSV file on my PC.

Here is the OO REXX script:

db_example_db2_as400.rex

Code: 
[COLOR=#0000ff]/********************************************/[/color]
[COLOR=#0000ff]/* Reading records and fields from database */[/color]
[COLOR=#0000ff]/********************************************/[/color]

[COLOR=#0000ff]/*## Enter Login-Data */[/color]
csebk  [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]raw_input([/color][COLOR=#ff00ff]"AS/400 name : "[/color][COLOR=#008080])[/color]
schema [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]raw_input([/color][COLOR=#ff00ff]"DB schema   : "[/color][COLOR=#008080])[/color]
userid [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]raw_input([/color][COLOR=#ff00ff]"User Id     : "[/color][COLOR=#008080])[/color]
[COLOR=#0000ff]/* Using getpass for getting password */[/color]
pwd    [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]getpass([/color][COLOR=#ff00ff]"Password    : "[/color][COLOR=#008080])[/color]

[COLOR=#0000ff]/*## using ADO */[/color]
[COLOR=#0000ff]/* Connection String */[/color]
connection_string[COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]"PROVIDER=IBMDA400; DATA SOURCE="[/color]csebk[COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
                  [COLOR=#ff00ff]"; USER ID="[/color]userid[COLOR=#ff00ff]"; PASSWORD="[/color]pwd[COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
                  [COLOR=#ff00ff]"; DEFAULT COLLECTION = "[/color]schema

[COLOR=#0000ff]/* Open ADO connection */[/color] 
connection [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff].OLEObject[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]New[/color][COLOR=#008080]([/color][COLOR=#ff00ff]"ADODB.Connection"[/color][COLOR=#008080])[/color]
connection[COLOR=#804040][b]~[/b][/color][COLOR=#008080]open[/color][COLOR=#008080]([/color]connection_string[COLOR=#008080])[/color]

[COLOR=#0000ff]/* Create an instance of an ADO Recordset */[/color]
recordset   [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff].OLEObject[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]New[/color][COLOR=#008080]([/color][COLOR=#ff00ff]"ADODB.Recordset"[/color][COLOR=#008080])[/color]
sql_stmt [COLOR=#804040][b]=[/b][/color]  [COLOR=#ff00ff]"select * "[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
            [COLOR=#ff00ff]"from P001800V "[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
            [COLOR=#ff00ff]"where OBEC like '%Mesto%'"[/color]
[COLOR=#0000ff]/* Open the recordset, using an SQL statement and the existing ADO connection */[/color]
recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]open[/color][COLOR=#008080]([/color]sql_stmt[COLOR=#804040][b],[/b][/color] connection[COLOR=#804040][b],[/b][/color] 1[COLOR=#804040][b],[/b][/color] 3[COLOR=#008080])[/color]

[COLOR=#0000ff]/* Create and populate an array of field names */[/color]
fields_count [COLOR=#804040][b]=[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Count[/color]
fields [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
[COLOR=#804040][b]do[/b][/color] j[COLOR=#804040][b]=[/b][/color]1 [COLOR=#804040][b]to[/b][/color] fields_count
  [COLOR=#804040][b]if[/b][/color] j [COLOR=#804040][b]<[/b][/color] fields_count [COLOR=#804040][b]then[/b][/color] 
    fields [COLOR=#804040][b]=[/b][/color] fields [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#008080]([/color]j[COLOR=#804040][b]-[/b][/color]1[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Name[/color] [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]" "[/color]
  [COLOR=#804040][b]else[/b][/color]
    fields [COLOR=#804040][b]=[/b][/color] fields [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#008080]([/color]j[COLOR=#804040][b]-[/b][/color]1[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Name[/color]
[COLOR=#804040][b]end[/b][/color]

csv_file [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"mesta.csv"[/color]
[COLOR=#0000ff]/* Open CSV file */[/color]
[COLOR=#804040][b]say[/b][/color] [COLOR=#ff00ff]"Now writing data to CSV file '"[/color] [COLOR=#804040][b]||[/b][/color] csv_file [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]"'.."[/color] 

[COLOR=#0000ff]/* Header line */[/color]
line [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]translate([/color]fields[COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]";"[/color][COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]" "[/color][COLOR=#008080])[/color]
[COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file[COLOR=#804040][b],[/b][/color] line[COLOR=#804040][b],[/b][/color] 1

[COLOR=#0000ff]/* Data lines */[/color]
[COLOR=#804040][b]do [/b][/color][COLOR=#804040][b]while[/b][/color][COLOR=#804040][b] [/b][/color][COLOR=#008080]([/color][COLOR=#804040][b]\[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]EOF[/color][COLOR=#008080])[/color]
  line [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
  [COLOR=#804040][b]do[/b][/color] j[COLOR=#804040][b]=[/b][/color]1 [COLOR=#804040][b]to[/b][/color] [COLOR=#008080]words([/color]fields[COLOR=#008080])[/color]
    fieldname [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]word([/color]fields[COLOR=#804040][b],[/b][/color] j[COLOR=#008080])[/color]
    [COLOR=#0000ff]/* join field values into string */[/color]
    [COLOR=#804040][b]if[/b][/color] j [COLOR=#804040][b]<[/b][/color] fields_count [COLOR=#804040][b]then[/b][/color]
      line [COLOR=#804040][b]=[/b][/color] line [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Item[/color][COLOR=#008080]([/color]fieldname[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Value[/color] [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]";"[/color]
    [COLOR=#804040][b]else[/b][/color]
      line [COLOR=#804040][b]=[/b][/color] line [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Item[/color][COLOR=#008080]([/color]fieldname[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Value[/color]
  [COLOR=#804040][b]end[/b][/color]
  [COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file[COLOR=#804040][b],[/b][/color] line
  recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]MoveNext[/color] 
[COLOR=#804040][b]end[/b][/color]
[COLOR=#804040][b]say[/b][/color] [COLOR=#ff00ff]"..done."[/color]

[COLOR=#0000ff]/* Close CSV file */[/color]
[COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file
[COLOR=#0000ff]/* Close RecordSet */[/color]
recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]close[/color][COLOR=#008080]()[/color]
[COLOR=#0000ff]/* Close Connection */[/color]
connection[COLOR=#804040][b]~[/b][/color][COLOR=#008080]close[/color][COLOR=#008080]()[/color]
[COLOR=#804040][b]exit[/b][/color]


[COLOR=#0000ff]/**************** Functions ****************/[/color]
[COLOR=#008080]raw_input[/color][COLOR=#804040][b]:[/b][/color] [COLOR=#804040][b]procedure[/b][/color]
  [COLOR=#0000ff]/* function raw_input() */[/color]
  [COLOR=#804040][b]use arg[/b][/color] prompt
  [COLOR=#804040][b]call [/b][/color][COLOR=#008080]charout[/color] [COLOR=#804040][b],[/b][/color] prompt
  [COLOR=#804040][b]parse pull[/b][/color] inp
  [COLOR=#804040][b]return[/b][/color] inp

[COLOR=#008080]getpass[/color][COLOR=#804040][b]:[/b][/color] [COLOR=#804040][b]procedure[/b][/color]
  [COLOR=#0000ff]/* print the prompt */[/color]
  [COLOR=#804040][b]call [/b][/color][COLOR=#008080]charout[/color] [COLOR=#804040][b],[/b][/color] [COLOR=#008080]arg([/color]1[COLOR=#008080])[/color]
  [COLOR=#0000ff]/* define some ASCII constants */[/color]
  EnterKey     [COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]'0D'X[/color] [COLOR=#0000ff]/* ASCII Enter i.e. Carriage Return */[/color]
  BackSpaceKey [COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]'08'X[/color] [COLOR=#0000ff]/* ASCII BackSpace */[/color]
  BeepSound    [COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]'07'X[/color] [COLOR=#0000ff]/* ASCII Beep sound */[/color] 
  pwd [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]''[/color] 
  [COLOR=#804040][b]do forever [/b][/color]
    char [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]SysGetKey([/color][COLOR=#ff00ff]'NOECHO'[/color][COLOR=#008080])[/color]
    [COLOR=#804040][b]if[/b][/color] char[COLOR=#804040][b]=[/b][/color]EnterKey [COLOR=#804040][b]then[/b][/color] [COLOR=#804040][b]leave[/b][/color]
    [COLOR=#804040][b]if[/b][/color] char[COLOR=#804040][b]=[/b][/color]BackSpaceKey [COLOR=#804040][b]then[/b][/color]
      [COLOR=#804040][b]if[/b][/color] [COLOR=#008080]length([/color]pwd[COLOR=#008080])[/color][COLOR=#804040][b]>[/b][/color]0 [COLOR=#804040][b]then[/b][/color]
        [COLOR=#804040][b]do[/b][/color]
          pwd[COLOR=#804040][b]=[/b][/color][COLOR=#008080]substr([/color]pwd[COLOR=#804040][b],[/b][/color]1[COLOR=#804040][b],[/b][/color][COLOR=#008080]length([/color]pwd[COLOR=#008080])[/color][COLOR=#804040][b]-[/b][/color]1[COLOR=#008080])[/color]
          [COLOR=#0000ff]/* Move Cursor Back */[/color]
          [COLOR=#804040][b]call [/b][/color][COLOR=#008080]charout[/color] [COLOR=#804040][b],[/b][/color] BackSpaceKey
          [COLOR=#804040][b]call [/b][/color][COLOR=#008080]charout[/color] [COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]' '[/color]
          [COLOR=#804040][b]call [/b][/color][COLOR=#008080]charout[/color] [COLOR=#804040][b],[/b][/color] BackSpaceKey
        [COLOR=#804040][b]end[/b][/color]
      [COLOR=#804040][b]else[/b][/color]
        [COLOR=#804040][b]call [/b][/color][COLOR=#008080]charout[/color] [COLOR=#804040][b],[/b][/color] BeepSound 
    [COLOR=#804040][b]else[/b][/color] 
      [COLOR=#804040][b]do  [/b][/color]
        [COLOR=#804040][b]call [/b][/color][COLOR=#008080]charout[/color] [COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]'*'[/color] 
        pwd [COLOR=#804040][b]=[/b][/color] pwd[COLOR=#ff00ff]''[/color]char
      [COLOR=#804040][b]end[/b][/color] 
  [COLOR=#804040][b]end[/b][/color] 
  [COLOR=#804040][b]say[/b][/color] 
  [COLOR=#804040][b]return[/b][/color] pwd
 
Upvote 0 Downvote
Hi mkmc,

You mentioned, that you have MS Access on your PC. So you can use it easily too.
I created in MS Access on my PC a simple database with one table named same and with the same data structure, as I have on DB2 on AS/400. I saved the database in a file named Exercise.accdb, in the same directory where my REXX scripts reside.
My access database is not password protected, I do not need schema name, userid and password in my connection string. So I can simplify the REXX script given above, omitting the subroutines for reading schema name, userid and password and changing connection string for AS/400 to the appropriate connection string for MS Access. Everything else stay the same.

Here is the OO REXX script for MS Access:

db_example_ms_access.rex

Code: 
[COLOR=#0000ff]/********************************************/[/color]
[COLOR=#0000ff]/* Reading records and fields from database */[/color]
[COLOR=#0000ff]/********************************************/[/color]

[COLOR=#0000ff]/*## using ADO */[/color]
[COLOR=#0000ff]/* Connection String */[/color]
connection_string[COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]"PROVIDER=Microsoft.ACE.OLEDB.12.0"[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
                  [COLOR=#ff00ff]"; DATA SOURCE=Exercise.accdb"[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
                  [COLOR=#ff00ff]"; Persist Security Info=False;"[/color]

[COLOR=#0000ff]/* Open ADO connection */[/color] 
connection [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff].OLEObject[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]New[/color][COLOR=#008080]([/color][COLOR=#ff00ff]"ADODB.Connection"[/color][COLOR=#008080])[/color]
connection[COLOR=#804040][b]~[/b][/color][COLOR=#008080]open[/color][COLOR=#008080]([/color]connection_string[COLOR=#008080])[/color]

[COLOR=#0000ff]/* Create an instance of an ADO Recordset */[/color]
recordset   [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff].OLEObject[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]New[/color][COLOR=#008080]([/color][COLOR=#ff00ff]"ADODB.Recordset"[/color][COLOR=#008080])[/color]
sql_stmt [COLOR=#804040][b]=[/b][/color]  [COLOR=#ff00ff]"select * "[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
            [COLOR=#ff00ff]"from P001800V "[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
            [COLOR=#ff00ff]"where OBEC like '%Mesto%'"[/color]
[COLOR=#0000ff]/* Open the recordset, using an SQL statement and the existing ADO connection */[/color]
recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]open[/color][COLOR=#008080]([/color]sql_stmt[COLOR=#804040][b],[/b][/color] connection[COLOR=#804040][b],[/b][/color] 1[COLOR=#804040][b],[/b][/color] 3[COLOR=#008080])[/color]

[COLOR=#0000ff]/* Create and populate an array of field names */[/color]
fields_count [COLOR=#804040][b]=[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Count[/color]
fields [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
[COLOR=#804040][b]do[/b][/color] j[COLOR=#804040][b]=[/b][/color]1 [COLOR=#804040][b]to[/b][/color] fields_count
  [COLOR=#804040][b]if[/b][/color] j [COLOR=#804040][b]<[/b][/color] fields_count [COLOR=#804040][b]then[/b][/color] 
    fields [COLOR=#804040][b]=[/b][/color] fields [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#008080]([/color]j[COLOR=#804040][b]-[/b][/color]1[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Name[/color] [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]" "[/color]
  [COLOR=#804040][b]else[/b][/color]
    fields [COLOR=#804040][b]=[/b][/color] fields [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#008080]([/color]j[COLOR=#804040][b]-[/b][/color]1[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Name[/color]
[COLOR=#804040][b]end[/b][/color]

csv_file [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"mesta.csv"[/color]
[COLOR=#0000ff]/* Open CSV file */[/color]
[COLOR=#804040][b]say[/b][/color] [COLOR=#ff00ff]"Now writing data to CSV file '"[/color] [COLOR=#804040][b]||[/b][/color] csv_file [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]"'.."[/color] 

[COLOR=#0000ff]/* Header line */[/color]
line [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]translate([/color]fields[COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]";"[/color][COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]" "[/color][COLOR=#008080])[/color]
[COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file[COLOR=#804040][b],[/b][/color] line[COLOR=#804040][b],[/b][/color] 1

[COLOR=#0000ff]/* Data lines */[/color]
[COLOR=#804040][b]do [/b][/color][COLOR=#804040][b]while[/b][/color][COLOR=#804040][b] [/b][/color][COLOR=#008080]([/color][COLOR=#804040][b]\[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]EOF[/color][COLOR=#008080])[/color]
  line [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
  [COLOR=#804040][b]do[/b][/color] j[COLOR=#804040][b]=[/b][/color]1 [COLOR=#804040][b]to[/b][/color] [COLOR=#008080]words([/color]fields[COLOR=#008080])[/color]
    fieldname [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]word([/color]fields[COLOR=#804040][b],[/b][/color] j[COLOR=#008080])[/color]
    [COLOR=#0000ff]/* join field values into string */[/color]
    [COLOR=#804040][b]if[/b][/color] j [COLOR=#804040][b]<[/b][/color] fields_count [COLOR=#804040][b]then[/b][/color]
      line [COLOR=#804040][b]=[/b][/color] line [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Item[/color][COLOR=#008080]([/color]fieldname[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Value[/color] [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]";"[/color]
    [COLOR=#804040][b]else[/b][/color]
      line [COLOR=#804040][b]=[/b][/color] line [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Item[/color][COLOR=#008080]([/color]fieldname[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Value[/color]
  [COLOR=#804040][b]end[/b][/color]
  [COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file[COLOR=#804040][b],[/b][/color] line
  recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]MoveNext[/color] 
[COLOR=#804040][b]end[/b][/color]
[COLOR=#804040][b]say[/b][/color] [COLOR=#ff00ff]"..done."[/color]

[COLOR=#0000ff]/* Close CSV file */[/color]
[COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file
[COLOR=#0000ff]/* Close RecordSet */[/color]
recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]close[/color][COLOR=#008080]()[/color]
[COLOR=#0000ff]/* Close Connection */[/color]
connection[COLOR=#804040][b]~[/b][/color][COLOR=#008080]close[/color][COLOR=#008080]()[/color]
[COLOR=#804040][b]exit[/b][/color]
 
Upvote 0 Downvote
Hi mkmc,

For a simple home/hobbyist/educational usage I would prefer a simple database without need to start and stop a database server.

If you don't want to buy MS Access, you can also use SQLite. It's free and very good database. You can download an install an ODBC driver sqliteodbc_w64.exe here SQLite ODBC Driver
To connect and work with with SQLite at the same way as I posted above (ADO) you have to use Microsoft OLE DB Provider for ODBC. I could post you an example for using SQLite in OO REXX.

 
Upvote 0 Downvote
Thanks, mikrom. The SQLite example would be great.
 
Upvote 0 Downvote
Hi mkmc,

With SQLite it's almost the same as with Access. I have an example Guinea Pigs Database. It's saved in a file PIGS_DB in my directory and contains table GUINEA_PIGS from which I want to select some records and save them into a CSV-file. So after I installed the SQLite driver mentioned above I only need to change the connection string, the select statement and the name of the CSV file, everything else stay the same.

db_example_sqlite.rex

Code: 
[COLOR=#0000ff]/********************************************/[/color]
[COLOR=#0000ff]/* Reading records and fields from database */[/color]
[COLOR=#0000ff]/********************************************/[/color]

[COLOR=#0000ff]/*## using ADO */[/color]
[COLOR=#0000ff]/* Connection String */[/color]
connection_string[COLOR=#804040][b]=[/b][/color][COLOR=#ff00ff]"PROVIDER=MSDASQL; DRIVER={SQLite3 ODBC Driver}"[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
                  [COLOR=#ff00ff]"; DATABASE=c:\00_mikrom\Work\PIGS_DB"[/color]
[COLOR=#0000ff]/* Open ADO connection */[/color] 
connection [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff].OLEObject[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]New[/color][COLOR=#008080]([/color][COLOR=#ff00ff]"ADODB.Connection"[/color][COLOR=#008080])[/color]
connection[COLOR=#804040][b]~[/b][/color][COLOR=#008080]open[/color][COLOR=#008080]([/color]connection_string[COLOR=#008080])[/color]

[COLOR=#0000ff]/* Create an instance of an ADO Recordset */[/color]
recordset   [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff].OLEObject[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]New[/color][COLOR=#008080]([/color][COLOR=#ff00ff]"ADODB.Recordset"[/color][COLOR=#008080])[/color]
sql_stmt [COLOR=#804040][b]=[/b][/color]  [COLOR=#ff00ff]"select * "[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
            [COLOR=#ff00ff]"from GUINEA_PIGS "[/color][COLOR=#804040][b]||[/b][/color][highlight #ffff00][COLOR=#000000],[/color][/highlight]
            [COLOR=#ff00ff]"where NAME like '%Sisi%'"[/color]
[COLOR=#0000ff]/* Open the recordset, using an SQL statement and the existing ADO connection */[/color]
recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]open[/color][COLOR=#008080]([/color]sql_stmt[COLOR=#804040][b],[/b][/color] connection[COLOR=#804040][b],[/b][/color] 1[COLOR=#804040][b],[/b][/color] 3[COLOR=#008080])[/color]

[COLOR=#0000ff]/* Create and populate an array of field names */[/color]
fields_count [COLOR=#804040][b]=[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Count[/color]
fields [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
[COLOR=#804040][b]do[/b][/color] j[COLOR=#804040][b]=[/b][/color]1 [COLOR=#804040][b]to[/b][/color] fields_count
  [COLOR=#804040][b]if[/b][/color] j [COLOR=#804040][b]<[/b][/color] fields_count [COLOR=#804040][b]then[/b][/color] 
    fields [COLOR=#804040][b]=[/b][/color] fields [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#008080]([/color]j[COLOR=#804040][b]-[/b][/color]1[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Name[/color] [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]" "[/color]
  [COLOR=#804040][b]else[/b][/color]
    fields [COLOR=#804040][b]=[/b][/color] fields [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#008080]([/color]j[COLOR=#804040][b]-[/b][/color]1[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Name[/color]
[COLOR=#804040][b]end[/b][/color]

csv_file [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"pigs.csv"[/color]
[COLOR=#0000ff]/* Open CSV file */[/color]
[COLOR=#804040][b]say[/b][/color] [COLOR=#ff00ff]"Now writing data to CSV file '"[/color] [COLOR=#804040][b]||[/b][/color] csv_file [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]"'.."[/color] 

[COLOR=#0000ff]/* Header line */[/color]
line [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]translate([/color]fields[COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]";"[/color][COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]" "[/color][COLOR=#008080])[/color]
[COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file[COLOR=#804040][b],[/b][/color] line[COLOR=#804040][b],[/b][/color] 1

[COLOR=#0000ff]/* Data lines */[/color]
[COLOR=#804040][b]do [/b][/color][COLOR=#804040][b]while[/b][/color][COLOR=#804040][b] [/b][/color][COLOR=#008080]([/color][COLOR=#804040][b]\[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]EOF[/color][COLOR=#008080])[/color]
  line [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color]
  [COLOR=#804040][b]do[/b][/color] j[COLOR=#804040][b]=[/b][/color]1 [COLOR=#804040][b]to[/b][/color] [COLOR=#008080]words([/color]fields[COLOR=#008080])[/color]
    fieldname [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]word([/color]fields[COLOR=#804040][b],[/b][/color] j[COLOR=#008080])[/color]
    [COLOR=#0000ff]/* join field values into string */[/color]
    [COLOR=#804040][b]if[/b][/color] j [COLOR=#804040][b]<[/b][/color] fields_count [COLOR=#804040][b]then[/b][/color]
      line [COLOR=#804040][b]=[/b][/color] line [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Item[/color][COLOR=#008080]([/color]fieldname[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Value[/color] [COLOR=#804040][b]||[/b][/color] [COLOR=#ff00ff]";"[/color]
    [COLOR=#804040][b]else[/b][/color]
      line [COLOR=#804040][b]=[/b][/color] line [COLOR=#804040][b]||[/b][/color] recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]Fields[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Item[/color][COLOR=#008080]([/color]fieldname[COLOR=#008080])[/color][COLOR=#804040][b]~[/b][/color][COLOR=#008080]Value[/color]
  [COLOR=#804040][b]end[/b][/color]
  [COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file[COLOR=#804040][b],[/b][/color] line
  recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]MoveNext[/color] 
[COLOR=#804040][b]end[/b][/color]
[COLOR=#804040][b]say[/b][/color] [COLOR=#ff00ff]"..done."[/color]

[COLOR=#0000ff]/* Close CSV file */[/color]
[COLOR=#804040][b]call [/b][/color][COLOR=#008080]lineout[/color] csv_file
[COLOR=#0000ff]/* Close RecordSet */[/color]
recordset[COLOR=#804040][b]~[/b][/color][COLOR=#008080]close[/color][COLOR=#008080]()[/color]
[COLOR=#0000ff]/* Close Connection */[/color]
connection[COLOR=#804040][b]~[/b][/color][COLOR=#008080]close[/color][COLOR=#008080]()[/color]
[COLOR=#804040][b]exit[/b][/color]

Btw for working with databases, I'm using this free database tool:
It has build in SQLite driver and contains sample database, which you can experiment with.
Without need to install any other tools, you can create with it your own SQLite database, and maintain it using SQL - i.e. create and drop tables, insert data, update data, select data.
 
Upvote 0 Downvote
Hi mkmc,

Then tell, which is more attractive to you, MS Access or SQLite, or do you still strictly insist that it need to be only DB2?
:)
 
Upvote 0 Downvote
Thanks mikrom. When I do this I will probably go with SQLite. It does appear a bit different as compared to rexx and db2 in z/os:
- The connection
- lineout?
- In zos, you use the address command to code for different command environments.
- No DSNREXX
- No cursor
- No fetching the cursor
etc.

Its all good. I can figure this stuff out.

I really appreciate it.
 
Upvote 0 Downvote
Hi mkmc,

Yes on PC it's very different. On IBM i we use ADDRESS, EXECSQL, CURSOR, FETCH too - for example you can look what I posted on 4 Nov 09 09:47 here
LINEIN and LINEOUT are standard ANSI REXX functions for reading and writing text files, available in OO REXX and Regina REXX interpreters
see:
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kavya55
  • Locked
  • Question
/* REXX */ ADDRESS TSO &quot;D IPLINFO&quot;
Replies
0
Views
371
Kavya55
Kavya55
Jongskie M.
  • Locked
  • Question
REXX Loop conditional statement not working 1
Replies
5
Views
410
Jongskie M.
Jongskie M.
Jongskie M.
  • Locked
  • Question
REXX Get last two digits of the year 1
Replies
4
Views
374
mikrom
mikrom
firedotwater
  • Locked
  • Question
REXX data manipulation
Replies
5
Views
127
mikrom
mikrom
lopes1211
  • Locked
  • Question
ZOC REXX how to send server output to file 2
Replies
6
Views
460
bignose21
bignose21

Part and Inventory Search

Sponsor

Back
Top