#24 problems running sql under oorexx

[Anonymous]

Hello,

I'm a mainframe DB2 guy who uses a lot of rexx to do sql under DB2.

Now I installed oorexx under windows and want to do the same thing to tables under MS access.

---

code:
dbFileName=directory()"\rekening_historiek.mdb"
conn=.oleobject~new("ADODB.Connection")
conn~provider="Microsoft.Jet.OLEDB.4.0"
conn~open(dbFileName)
sql="insert into rek_historiek (rek_nr, datum_verrichting,datum_valuta,bedrag,categorie,categorie_detail,omschrijving) values ('"reknr"','"datum"','"valuta"','"bedrag"',' ',' ','"omschd1"' ) "
say sql
conn~execute(sql)

---

2 questions:

1. when inserting and I get duplicates, he gives an error

   35 - call insert
   Error 92 running C:\rekinfo\M.rex line 48: OLE error
   Error 92.906: OLE exception: Code: 80004005 Source: Microsoft JET Database Engi
   ne Description: De opgegeven wijzigingen aan de tabel zijn niet aangebracht omda
   t zij dubbele waarden zouden opleveren voor de index, primaire sleutel of relati
   e. Wijzig de gegevens in het veld of de velden die dubbele gegevens bevatten of
   definieer de index opnieuw.

What indeed shows that I want to insert a row, which is already in the table and must be unique.
But, I want the program to go on. At the end of the program, I would like to report how many inserts succeeded and how many duplicates. Does anyone know how to do so ?

1. when executing fe. "select count(*) from qsdfd" ,
   I want to get the value returned bij sql in a variable in rexx.

Please advice,
Jeroen m.

[Mark Miesfeld]

Logged In: YES
user_id=191588
Originator: NO

For your first question, you could use a signal on syntax to trap the error and continue. How you structure this for your needs is something you would have to come up with. But here is a general idea.

I took just the first couple of lines of your code and put it into a program:

/ Simple sql /

dbFileName=directory()"\rekening_historiek.mdb"
conn=.oleobject~new("ADODB.Connection")
conn~provider="Microsoft.Jet.OLEDB.4.0"
conn~open(dbFileName)

Now, since I don't have that database file on my system, when I ran the program I got this:

C:\work.ooRexx\other\help\sql>testJet.rex
5 - conn~open(dbFileName)
Error 92 running C:\work.ooRexx\other\help\sql\testJet.rex line 5: OLE error
Error 92.906: OLE exception: Code: 80004005 Source: Microsoft JET Database Engine Descripti
on: Could not find file 'C:\work.ooRexx\other\help\sql\rekening_historiek.mdb'.

Then I changed the program to this:

/ Simple signal on syntax example /

dbFileName=directory()"\rekening_historiek.mdb"
conn=.oleobject~new("ADODB.Connection")
conn~provider="Microsoft.Jet.OLEDB.4.0"

ret = openDB(conn)
if ret == 0 then say "Opened okay for" dbFileName
else say "Could not open" dbFileName

::routine openDB
use arg conn

signal on syntax name returnOne

conn~open(dbFileName)
signal on syntax -- Turn the signal off
return 0

returnOne:
signal on syntax -- Turn the signal off
return 1

And when it runs, I get this output:

C:\work.ooRexx\other\help\sql>testJet.rex
Could not open C:\work.ooRexx\other\help\sql\rekening_historiek.mdb

C:\work.ooRexx\other\help\sql>

For your problem you would probably want to use a counter and count each time you had the error. In the signal handler part, you would want to check the error code, or perhaps compare the description string. If it did not match the code (or description) for a duplicate insert, then take some other action. Look up in the docs the information that is available to you when the trap is invoked. There is enough information for you to determine if the syntax error is caused by your duplicate insert, or some other error.

For example change my openDB function to an executeSQL function. Then return 0 if the SQL executed correctly, in the signal handler return 1 if it is the duplicate error or return 2 if it is some other SQL error. Count all the times you get a 1 back and you have the number of duplicates you tried to insert.

For question 2:

I haven't worked with SQL and ooRexx and Access. I'd have to play around with it.

But off the top of my head: when you execute the SQL just assign the return to a variable. Then you might need the JET documentation to figure out how to interpret what you get back.

It might be as simple as:

sql = select count(*) from qsdfd"
say sql
count = conn~execute(sql)
say "Got a count of" count "from qsdfd"

but I don't know what form the data is returned in. And I don't know enough SQL off the top of my head to be sure what that SQL statement is. I think gsdfd is the table name and the statement returns the total number of rows in the table.

Post back and let me know if that is enough to get you going. If not I'll try to play around with Access a little see if I can come up with a little more information.

--
Mark Miesfeld

[Mark Miesfeld]

Logged In: YES
user_id=191588
Originator: NO

Okay I played with this a litte and have better information on how to get the results of a SQL query into ooRexx variables.

The first key is that when you do the conn~execute(sql) you get back a RecordSet object, an OLE/ActiveX object. Then you just need to manipulate that to get what you want out of it.

What I did was create a ContactManager database with a Contacts table in it. Then I added 7 contacts, using first name and last name fields

When I run the program listed below, I get the following output. The program itself has some comments to, hopefully, better explain how to work with the record set.

C:\work.ooRexx\other\help\sql>testJet.rex
Opened okay for C:\work.ooRexx\other\help\sql\ContactManagement.mdb

SQL: select count(*) from Contacts
record count? -1
Fields? 1
Field 1? an OLEObject
Field name: Expr1000
Field value: 7

Number of rows in the Contacts table: 7

SQL: select [FirstName] from Contacts
record count? 7
Fields? 1
Field 1? an OLEObject
Field name: FirstName
Field value: Mark

Contact first name: Mark
Contact first name: Tom
Contact first name: Frank
Contact first name: Sarah
Contact first name: Mary
Contact first name: Sally
Contact first name: Jane

SQL: select [LastName], [FirstName] from Contacts
Last name First name
=================================
Miesfeld Mark
Sawyer Tom
Simmons Frank
Smith Sarah
Thompson Mary
Fields Sally
Doe Jane

Last name First name

Miesfeld Mark
Sawyer Tom
Simmons Frank
Smith Sarah
Thompson Mary
Fields Sally
Doe Jane

C:\work.ooRexx\other\help\sql>

/ Simple OLEObject - ADODB example /

dbFileName=directory()"\ContactManagement.mdb"
conn=.oleobject~new("ADODB.Connection")
conn~provider="Microsoft.Jet.OLEDB.4.0"

-- Try to open the database, exit on error
ret = openDB(conn, dbFileName)
if ret == 0 then do
say "Opened okay for" dbFileName
say
end
else do
say "Could not open" dbFileName
return 99
end

-- Create a SQL statement. Count the number of rows in the Contacts
-- table
sql = "select count(*) from Contacts"
say "SQL:" sql

-- There are two ways, maybe more, to actually use the SQL. In any
-- case, what is returned is a record set objet.

-- This first method uses the connection execute.
rs = conn~execute(sql)

-- The rest is just guess work on my part. There may be easier ways to
-- do this, maybe not. It seems that it is necessary to move to the
-- first record. Without that I had no luck
rs~moveFirst

-- I happen to know that for this SQL query there will only be 1 record
-- with 1 field returned. So, I could just do:
--
-- say "Row count:" rs~fields~item(0)~value
--
-- But to show a little bit of how I figured this out, I'll print out a
-- little more

say "record count?" rs~RecordCount
say "Fields? " rs~fields~count
say "Field 1? " rs~fields~item(0)
say "Field name: " rs~fields~item(0)~name
say "Field value: " rs~fields~item(0)~value
say
say "Number of rows in the Contacts table:" rs~fields~item(0)~value
say
rs~close

-- This second method creates the record set object first.
sql = "select [FirstName] from Contacts"
say "SQL:" sql

rs = .oleObject~new("ADODB.recordset")
rs~open(sql, conn, 3, 3)

rs~moveFirst

say "record count?" rs~RecordCount
say "Fields? " rs~fields~count
say "Field 1? " rs~fields~item(0)
say "Field name: " rs~fields~item(0)~name
say "Field value: " rs~fields~item(0)~value
say

do while \ rs~EOF
say "Contact first name:" rs~fields~item(0)~value
rs~moveNext
end
rs~close
say

-- This shows how to get the field values using either an index or the
-- name of the field.
sql = "select [LastName], [FirstName] from Contacts"
say "SQL:" sql
rs = conn~execute(sql)
rs~moveFirst
say "Print contact names by field index"
say "Last name First name"
say "================================="
do while \ rs~EOF
say left( " " rs~fields~item(0)~value, 19 ) rs~fields~item(1)~value
rs~moveNext
end
say

-- Notice that you can reset the cursor in the record set.
rs~moveFirst
say "Print contact names by field name"
say "Last name First name"
say "================================="
do while \ rs~EOF
ln = left( " " rs~fields~item("LastName")~value, 19 )
fn = rs~fields~item("FirstName")~value
say ln fn
rs~moveNext
end

rs~close

conn~close

return 0
-- End of entry point

::routine openDB
use arg conn, dbFileName

signal on syntax name returnOne

conn~open(dbFileName)
signal on syntax
return 0

returnOne:
signal on syntax
return 1