MSSqlServerPreparedStatement results in nilObjectException

Databases
1

HI Everyone.
I’m having an issue using a MSSQLServerPreparedStatement.
Following the XoJo documentation example I have created the following:

         '#-#-#-#-#-#-#
         DbConnect // Centralised connection call.
         '#-#-#-#-#-#-#
         Dim Ps as MSSQLServerPreparedStatement
         Ps = Db.Prepare("SELECT * FROM [dbo].[company] ORDER By [companyName] ASC")
         Dim Rs as RecordSet = Ps.SQLSelect
         If Rs<>Nil then
         Rs.MoveFirst
        //Populate the listbox etc

At the Rs<>Nil point a NilObjectException is raised
Can anyone identify the reason for the NilObjectException?
Rgds Bill

2
  • Db is not connected (even if you think so) - for debug purposes check immediately before above code.
  • Check for Db.Error after SQLSelect.
  • Then check for rs <> Nil.
  • Then check for rs.EOF.
  • You now are on the first record (no MoveFirst necessary).
3

Hi Eli
Connection to the Db is confirmed.
Replaced the MSSqlPreparedStatement call with standard Db select call and everything is Ok. The issue must be with prepared statement.
Rgds Bill

4

If [dbo].[company] has a TEXT, VARCHAR(MAX) or VARBINARY(MAX) you’ll get this. It’s a known bug with the SQL Native client. You can use the ODBC plugin which will work, but with limitations - I’ve only been able to get the first 254 bytes of a Text column.

5

HI Wayne
Thank you that that. I have come across the limitation you highlight but have managed on another project to increase to varChar(5000). Which while not the same as varChar(Max) got around the issue for me.
Bottom line is, I will have to forgo prepared statements until XoJo correct the fault.
Rgds

6

I had the same problem some time back but changed to the MBS SQL plug-in instead of the Xojo standard issue … I now use a boatload of prepared statements with my MS SQL Server database applications and don’t have any problems.