ODBCResultSet returns false until final Loop

Discussion in 'Lotus Notes Programming' started by moxie, May 30, 2006.

  1. moxie

    moxie Guest

    I am trying to access 2 tables from an SQL database. The results from
    the second query are based on the results of the first query. I loop
    through the first result set, grab a variable and use it in the 2nd
    query. The 2nd query does not return a result set until the very last
    row.

    Dim con As New ODBCConnection
    Dim qry As ODBCQuery
    Dim qry2 As ODBCQuery
    Dim result As ODBCResultSet
    Dim result2 As ODBCResultSet

    Call con.ConnectTo("database","username","password")

    Set qry = New ODBCQuery
    Set result = New ODBCResultSet
    Set qry.Connection = con
    Set result.Query = qry
    qry.SQL = "SELECT salesno FROM table1 WHERE quote IN (0)"

    result.Execute

    Do
    result.NextRow
    salesnum = result.GetValue("salesno", salesnum)
    Set qry2 = New ODBCQuery
    Set result2 = New ODBCResultSet
    Set qry2.Connection = con
    Set result2.Query = qry2

    qry2.SQL = {SELECT col1, col2, col3 FROM table2 WHERE salesno = '} &
    salesnum & {'}

    result2.Execute

    If result2.IsResultSetAvailable Then
    '...code to create document
    End If
    Loop Until result.IsEndOfData

    result2.Close(DB_CLOSE)
    result.Close(DB_CLOSE)
    con.Disconnect


    The first query is successful and gives me a set of sales numbers. I
    can see in the debugger that the second query is set correctly. But as
    I loop through the sales numbers, result2.IsResultSetAvailable returns
    False, until the last row from the first result set. Then it returns
    True and I can create a document for the last sales number.

    If I enter a salesnum directly into the query like this:
    qry2.SQL = {SELECT col1, col2, col3 FROM table2 WHERE salesno =
    '33445'}

    result2.IsResultSetAvailable returns True

    Any ideas why I can't get a 2nd resultset until the last value of the
    1st set?
     
    moxie, May 30, 2006
    #1
    1. Advertisements

  2. This seems to me like a natural join on the table1 to table2?

    In this case the SQL-statement would read (depending on your SQL-dialect):

    select
    t1.salesno,
    t2.col1,
    t2.col2,
    t2.col3
    from
    table1 t1,
    table2 t2
    where
    t1.salesno=t2.salesno and
    t1.quote in (0)


    or in the JOIN notation:

    select
    t1.salesno,
    t2.col1,
    t2.col2,
    t2.col3
    from
    table1 t1
    join table2 t2 on (t1.salesno=t2.salesno)
    where
    t1.quote in (0)

    (all written in one line in Notes OFC)

    In this way you're able to avoid network overhead, query preparation time
    for qry2 and get a faster throughput as the SQL-server is able to resolve
    the relation between table1 and table2 right on the server.


    If you really have to use 2 separate queries e.g. because of the two tables
    sitting in different databases without any possibility to join them like
    above I'd declared an object in a scriptlibrary which opens a new connection
    for every instance of a query object.


    The constructor would read like this:

    declare myODBCObject
    private con as ODBCConnection
    private qry as ODBCQuery
    private res as ODBCResultSet
    sub new(db as String, usr as String, pwd as String)
    Set con = New ODBCConnection
    Call con.ConnectTo(db, usr, pwd)
    Set qry = New ODBCQuery
    Set res = New ODBCResultSet
    Set res.qury=qry
    end sub

    sub disconnect()
    con.close()
    end sub

    sub setQuery(q as String)
    res.Query=q
    res.Execute
    end sub

    function getRes() as ODBCResultSet
    getRes=this.res
    end function

    function isResultSetAvailable() as integer
    isResultSetAvailable=res.IsResultSetAvailable
    end function

    function isEndOfData() as Integer
    isEndOfData=res.IsEndOfData
    end function

    function getValue(col as String) as variant
    getValue=res.getValue(col)
    end function

    sub nextRow()
    Call res.NextRow
    end sub
    end


    In your code the usage of the object would read like this:

    Dim salesObj = New myODBCObject("database", "username", "password")
    Dim detailObj = New myODBCObject("database", "username", "password")
    Call salesObj.setQuery("select salesno from table1 where quote in (0)")
    Do
    SalesObj.NextRow
    detailObj.setQuery("select col1, col2, col3 from table2 where salesno=" & SalesObj.getValue("salesno"))
    if detailObj.IsResultSetAvailable Then
    ' Code to create document
    end if
    until salesRes.isEndOfData

    Call salesObj.Disconnect()
    Call detailObj.Disconnect()


    t++
     
    Thomas Antepoth, Jun 2, 2006
    #2
    1. Advertisements

  3. moxie

    moxie Guest

    Thank you so much for your response. I did the first option you
    mentioned and came up with this:

    qry.SQL = SELECT tbl1.lineitem, tbl1.itemno, tbl1.custno, tbl1.salesno
    FROM tbl1, tbl2 WHERE tbl2.quote = 0 and (tbl1.salesno = tbl2.salesno)


    It works!! Thanks
     
    moxie, Jun 2, 2006
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.