Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#24 problems running sql under oorexx

closed
nobody
Classes (8)
5
2014-08-26
2007-06-01
Anonymous
No

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.

Discussion

  • Mark Miesfeld
    Mark Miesfeld
    2007-06-28

    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
    Mark Miesfeld
    2007-06-28

    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

     


Anonymous


Cancel   Add attachments