Nested Recordsets Problem

Databases
1

OK, this on has been bothering me for a few days, and I cannot find anything related to it in the forums.

I have a need to query a database table for distinct values, then using each value, build another query to get some specific information from the same table. My first recordset looks great, returning about 30 rows. I query the table a second time, and I get the data expected. However…when I step to the next record of the first recordset I encounter EOF. If I don’t open the second recordset, I don’t get EOF until 30 records later in my loop. Here is the actual test code (apologies for the rather cryptic SQL SELECT statements, but I wanted to show valid code):

-------------- START OF CODE -------------------
’ Test nested recordsets

Dim strQuery As String
Dim sLine As String

Dim rsQC As RecordSet
Dim rsLines As RecordSet

'Connect to the ODBC database
app.dbcQC = New ODBCDatabase
app.dbcQC.DataSource = “ColorQC”
If not app.dbcQC.Connect Then
Exit
end

’ Get the list of line numbers
strQuery = “SELECT DISTINCT lineno FROM color_qc ORDER BY lineno”
rsLines = app.dbcQC.SQLSelect(strQuery)
If (not app.dbcQC.Error) Then
If rsLines <> Nil Then

  While Not rsLines.EOF
    sLine = app.trimNulls(rsLines.IdxField(1).StringValue.Trim)
    
    ' Get the info for the specified line
    strQuery = "SELECT etos(color_qc.dtstart),  etos(color_qc.dtstop), color_qc.spinlot, color_qc.color, color_qc.colornum, etos(color_qc.pounds), etos(color_qc.letdown), etos(color_qc.islabelchange), etos(color_qc.ismakeup), etos(color_qc.isapproved), etos(color_qc.iscos), color_qc.stage FROM color_qc WHERE color_qc.lineno = '" + sLine + "' ORDER BY dtstart"
    rsQC = app.dbcQC.SQLSelect(strQuery)
    rsQC.Close
    
    rsLines.MoveNext
  wend
  
end

end
rsLines.Close

’ Close the connection
app.dbcQC.close()

----------------------- END OF CODE ------------------

The database in question is a DB2 variant called Recital running on Solaris 10. Recital Software supplies the ODBC driver. I thought it might be the ODBC driver, so I ginned up a quick test using VB6 and the nested loops worked flawlessly.

I may be missing something in the documentation, but I haven’t seen anything that even hints at this. I tried using two different connections, and while messy, it worked fine on Windows 8 and Windows XP; it crashes when opening the second connection on Windows 7. (Pull hair here.)

Can someone please enlighten me on this finer point of recordset access? I have used this approach many times over the years in other languages, and would hate to give up my bad habits this late in life.

2

Usually, when I see nested Recordsets it means I need to look at my SQL in the first query. There is usually a way to combine the two into just one query using a Join.

The other thing that I don’t see is any database error checking. Always, always, always, check app.dbcQC.Error after every database operation. Xojo does NOT automatically throw an exception if you have an SQL error. Recordsets will NEVER be nil unless there was an SQL error. Usually the DB.ErrorMessage will tell you what’s going on.

So I didn’t answer the question, but I think there are some things you could do differently.

3

I’ve never had a problem with this sort of thing, so I don’t think it’s Xojo. It may be a limitation of the odbc driver not allowing 2 result sets on the same table. Or a limitation of the database itself. I know, for example, that MS SQL Server will not allow you to have 2 active result sets at the same time.

That said, there is no need for the first query. Sort the second query by both lineno and dtstart. That gives you the same results in a single query.

4

The fact that it crashes with Windows 7, but with XP or 8 when opening two connections make me think there may be something strange going on in the OBDC driver.

5

I agree with Bob. Change your WHERE clause to:

WHERE color_qc.lineno IN (SELECT DISTINCT lineno FROM color_qc ORDER BY lineno)

And get rid of the outer record set.

It’ll likely be faster that way too.

6

Greg, won’t that just select all the records anyway? What the 2 queries appear to do is get all the lineno in order and then within each lineno, get the details of each record by dtstart. The same thing can be accomplished by the second query by changing the order by to

ORDER BY lineno, dtstart
7

As Bob already said, always check for errors after each querry. And try to avoid nested requests. They are WAY slower than JOIN or nested querries.

8

My initial feeling is I agree with others, I do not see the need for nested record sets and a simple order by might do the trick here.

However, the code appears to be incomplete as it is not clear how rsQC is being used. From what I see below, you are assigning rsQC and then closing it right away.

[code]While Not rsLines.EOF
sLine = app.trimNulls(rsLines.IdxField(1).StringValue.Trim)

    ' Get the info for the specified line
    strQuery = "SELECT etos(color_qc.dtstart), etos(color_qc.dtstop), color_qc.spinlot, color_qc.color, color_qc.colornum, etos(color_qc.pounds), etos(color_qc.letdown), etos(color_qc.islabelchange), etos(color_qc.ismakeup), etos(color_qc.isapproved), etos(color_qc.iscos), color_qc.stage FROM color_qc WHERE color_qc.lineno = '" + sLine + "' ORDER BY dtstart"
    
    rsQC = app.dbcQC.SQLSelect(strQuery)
    
    // ******** Comment: What do you intend to do with rsQC? *********** //
    
    rsQC.Close
    rsLines.MoveNext
  wend[/code]
9

[quote=134721:@Tim Hare]Greg, won’t that just select all the records anyway? What the 2 queries appear to do is get all the lineno in order and then within each lineno, get the details of each record by dtstart. The same thing can be accomplished by the second query by changing the order by to

ORDER BY lineno, dtstart [/quote]
Yes, of course. That’s what I get for answering at 1am.

10

Thank you all for such quick and comprehensive responses. Let me address a few of the comments.

Bob - Excellent advice. I was unaware of this issue with Xojo, so I will be doing that very thing.

Tim - Also an excellent point, and normally what I would do. However, the complete code (not the framework here showing the error) goes to the last record of each rsQC recordset and processes only the fields found there. I can certainly step through the recordset looking for the change of lineno, but that seems inefficient compared with a nested query. The nested query is also easier for someone to debug or rewrite ten years from now (and that could be me).

Dean - Very possible. This ODBC driver package is only in its second iteration, so you might have something there. See my closing comment.

Sascha - True, anything I can do in SQL is faster. However, I have that whole last record thing and I’m not sure I can get that with just a JOIN. I’ll do some digging though to see if I can make it work.

Rich - I just put in the code that was needed to make the error occur. In the production code I play around with the rsQC data to produce pretty tables and meters. Putting that code here would have cluttered things up and made Greg read more than he had to at 0100.

Thanks again all of you. I will implement or test all of these suggestions and even try the same code against an MSSQL database to see if I get the same behavior. If I find anything I’ll report back here for your reference.

11

Matthew,

A bit of an idea here of how you can do this with one recordset. Looks like you are trying to do 2 things:

  1. Obtain a distinct list of lineno from table color_qc
  2. use distinct values from lineno to query the same table color_qc looking to group these records on this filter.

since both record sets are pulling from the same table, maybe we can keep track of each lineno in a current recordset row and use that to make a decision on.

Also… what might help here (I didn’t do it below) would be to take your database connection process and put it in the method which could perform a SQL Execute or perform a query that returns a recordset.

(Pseudo Code):

[code] dim strQuery, linenoCurrentStr, linenoPreviousStr as String, rsQC as RecordSet

app.dbcQC = New ODBCDatabase
app.dbcQC.DataSource = “ColorQC”

If not app.dbcQC.Connect Then
strQuery = “SELECT etos(color_qc.dtstart), etos(color_qc.dtstop), color_qc.spinlot, color_qc.color, color_qc.colornum, etos(color_qc.pounds), etos(color_qc.letdown), etos(color_qc.islabelchange), etos(color_qc.ismakeup), etos(color_qc.isapproved), etos(color_qc.iscos), color_qc.stage FROM color_qc ORDER BY lineno ASC, dtstart ASC”
rsQC = app.dbcQC.SQLSelect(strQuery)

if app.dbcQC.error then ' simple error checking.
  MsgBox "error: " + app.dbcQC.errormessage
  
else
  
  rsQC.MoveFirst
  
  while not rsQC.EOF
    
    linenoCurrentStr = rsQC.Field("lineno").StringValue
    
    if linenoCurrentStr = linenoPreviousStr then 
      // lineno record is the same as previous record, perform action.
      
    else
      // lineno record is not the same as previous record, perform action.
      
    end if
    
    
    linenoPreviousStr = linenoCurrentStr // assign lineno Previous with current value
    rsQC.MoveNext
    
  wend
  
end if

end

app.dbcQC.Close[/code]

12

Rich,

 Thanks.  That is roughly what I am leaning toward right now.  I checked for DB errors at every SQLSelect and all was clean.  And the JOIN idea is out since I would be joining my table to itself, which even in Southwest Virginia is illegal last I checked.

 I think I will also try creating a method that returns a recordset for further processing, or pass the lineno field to another method for processing there.  Gut feeling is that it won't make any difference, but it will only take a few minutes to test.
13

there is no need to perform a join onto itself as the data is coming from the same table. A simple order by will do here.

The idea of creating a database connection class is more of a means to help minimize how much work is required to connect to the database.

for example, if you had a db class, you could do something like

[code]dim db as new dbClass
dim rs as RecordSet

rs = db.dbSelect(“select * from some_table”)
if rs = nil then exit

rs.MoveFirst
while not rs.EOF
// do something

rs.MoveNext
wend
[/code]

14

Try using 2 separate database connections, one for each query. (You would have to do that for mssql anyway.)

15

confused…

Refactoring the SQL to make it easier to read … I get this

SELECT DISTINCT lineno 
  FROM color_qc 
 ORDER BY lineno"


SELECT etos(color_qc.dtstart), 
       etos(color_qc.dtstop), 
       color_qc.spinlot, 
       color_qc.color, 
       color_qc.colornum, 
       etos(color_qc.pounds), 
       etos(color_qc.letdown), 
       etos(color_qc.islabelchange), 
       etos(color_qc.ismakeup), 
       etos(color_qc.isapproved), 
       etos(color_qc.iscos), 
       color_qc.stage 
  FROM color_qc 
 WHERE color_qc.lineno = '" + sLine + "' 
 ORDER BY dtstart"

The first query returns a distinct list of line #s, which infers there may be duplicates… if so, are the rest of the fields on records with the same line number also the same? If SO, then why deal with two queries?

SELECT DISTINCT
      etos(color_qc.dtstart), 
       etos(color_qc.dtstop), 
       color_qc.spinlot, 
       color_qc.color, 
       color_qc.colornum, 
       etos(color_qc.pounds), 
       etos(color_qc.letdown), 
       etos(color_qc.islabelchange), 
       etos(color_qc.ismakeup), 
       etos(color_qc.isapproved), 
       etos(color_qc.iscos), 
       color_qc.stage 
  FROM color_qc 
 ORDER BY dtstart"

If the data on records with the same line# are NOT the same, you have no way to determine WHICH you would choose, if they are the same, then the line# itself is not important.

16

Perhaps I need to expand my description some. Ya’ll have been very helpful, and some excellent points were made. I agree with all of the proposed SELECT statements presented, including the ORDER BY clauses suggested.

Here is the ultimate goal. The first recordset contains the distinct line numbers (as in production line). There about 30 of these, and the number is more or less constant over time.

I want to get the last record related to each of these lines, which will contain the current data about said line (ie: what is running right now or just ran). Thus, the second query gets all of the records for each line, then goes to the last (most current) one and grabs the necessary data. At this point I go to the next line number and do it all over again.

As you can see, the first recordset is small and always will be, but the nested one will grow continuously as new orders hit each production line. It has hit 4000 rows in just a couple of months. This is why I did not want to do a SELECT statement and ORDER BY lineno and dtstart; later on my recordsets will be quite large and stepping through them very inefficient.

Thus I had essentially two options: 1. Use two connections (per Tim) or 2. Read in the limited line information and close that recordset before moving on. I was using the first option, but it crashes on Windows 7. So, Option 2 is. I read the line number data into an array, then close the recordset, and move onto getting the details. The ODBC connection handles jumping to the last row of data, so it should be pretty speedy, even as the table balloons. I’m even considering reading the line number data once per month or when the program starts, and just keeping the array in a static or shared property to eliminate that step each time I check the line status (every 5 seconds right now).

I’m not super keen on using the array, as I just like to let the database do the lifting anytime I can. that’s what it’s really good at. But since the data set is so small, I’ll live with it. But I will be checking to see if I get the same problem in Microsoft SQL, as I expect to use Xojo in a large project next year that will use MSSQL server.

Thanks again to everyone who had feedback on this question.

17

You should use a subquery. I can post an example when I get back to my office.

18

And you will have the same problem in MS SQL as it explicitly does not allow multiple open recordsets.

19

I know that ideally you should only need one recordset with all needed columns included, but it is permissible to have multiple connections to the server, so each recordset has it’s own connection to the db server.

Be aware that the MSSQL plugin uses the Native client which has a bug (over 10 years) and will fail when retrieving a recordsest that contains a Text or VarChar(Max) column. Fail as in crash the app.

20

Assuming that dtstart is unique within a given lineno, you could use a subquery like

strQuery = "SELECT etos(color_qc.dtstart), etos(color_qc.dtstop), color_qc.spinlot, color_qc.color, color_qc.colornum, etos(color_qc.pounds), etos(color_qc.letdown), etos(color_qc.islabelchange), etos(color_qc.ismakeup), etos(color_qc.isapproved), etos(color_qc.iscos), color_qc.stage FROM color_qc 
WHERE color_qc.dtstart= (select max(dtstart) FROM color_qc cqc WHERE cqc.lineno = color_qc.lineno)
ORDER BY lineno"

That would require the minimum number of rows be returned.