Nested SQL Loops in Windows

Databases
#1

I have a Web app that works fine from my Mac connecting to a MS-SQL DB using the ActualTech ODBC driver.

In the app is a SQL loop to select some records and in the middle of that loop I have a function call that does a look up of something in another table in the same DB. The DB is opened in the SESSION.OPEN event and I use the same connection for both SQL reads with different record sets.

Originally the nested SQL statement was in a function but to isolate the problem I just put a simple SQL statement in the loop without a function call. The SQL is as simple as possible and I know it works because it captured the exact SQL in a message box and pasted it into a SQL client to test. This is NOT the SQL in the actual program but I wanted it to be as simple as possible.

When I do a build and put the app on the Windows Server 2008r2 box (Abyss Web Server and SQLServer 2008) where the DB exists and connect with the Microsoft ODBC connection I get a NIL OBJECT EXCEPTION when the SQL Select for the inner loop is executed.

I dissected things and finally created a second record set in the chunk of code where the loop existed and did a very simple SELECT ‘ABC’ statement and I get a NILL OBJECT EXCEPTION but only in Windows.

My Mac connects to the exact same DB on the Windows server. Everything else works just fine but this is the only place (so far) that has a nested SQL loop.

Here is some “pseudo” code representing the problem:

DIM rs1 as recordset
DIM rs2 as recordset

SQL = "SELECT col1, col2 FROM TABLE WHERE col3 = 'x'"

rs1 = Session.MYDB.SQLselect(SQL)

WHILE NOT EOF(rs1)

  wk = rs1.Field("col1").GetString

  SQL = "SELECT 'ABC'"
  'This statement below generates a NIL OBJECT EXCEPTION but only in Windows
  rs2 = Session.MYDB.SQLselect(SQL)
  
  <blah blah blah more code here>

  rs2.Close

  rs1.MoveNext

WEND
rs1.Close

Is it a bug in XOJO or a limitation of the Windows ODBC driver? Do I need multiple connections if I have any nested SQL loops?

I suppose I could do a JOIN and avoid the whole nested loop problem in this simple case but sometimes it there will be logic involved to decide the SQL for the inner loop and doing it in code and not SQL is much less complex.

Thanks.

#2

It’s an issue with MS SQL Server. You should be getting an error message about already having a result set open (I don’t remember the wording). You have to use a separate DB connection for the nested SQL. Make 2 connections to the database and you’ll be fine.

#3

If I had gotten a “real” error message it probably would have made sense sooner. Since it only happened on the server I had to add some code to diagnose the problem, build, and deploy. I have not tried remote debugging yet … but maybe it is time.

All I got was a NIL OBJECT EXCEPTION with the standard nasty and impossible to decipher popup asking you to send it as feedback.

Apparently the OSX ODBC driver has a way to make it work and Windows does not. ???

Anyway thanks for the reply. Back to work.

#4

You don’t show any error checking in your code, so it’s hard to say whether you should have seen a “real” error or not.

#5

I also saw SQL Driver being limited to one recordset.

Please check for errors after SQLSelect, prepare or SQLExecute.

#6

My pseudo code did not have error checking but my real app did, but the execution of the SQLselect caused the NIL OBJECT EXCEPTION and the error was never caught.

rs1 = Session.MYDB.SQLselect(SQL)
if Session.MYDB.Error then
  <some error handling>
end if

Will a TRY / CATCH handle a NIL OBJECT EXCEPTION?

#7

yes

#8

Sounds like a bug in the odbc plugin or driver. It should handle the error more gracefully.

#9