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>
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.