Cannot return error from MSSQL PreparedStatements

Databases
1

Hi all,

Here is my simple test for prepared statements with MSSQL:

  dim LocDB as new MSSQLServerDatabase
  
  LocDB.Host = "DELL-WIN10"
  LocDB.DatabaseName = "Test_Web"
  LocDB.UserName = "sa"
  LocDB.Password = "12345678"
  
  If Not locDB.Connect Then
    MsgBox("DB Connect Failure!")
  else
    dim Loc_Sql as string = "SELECT * FROM SystemUser WHERE GUID=?"
    
    Dim rs as RecordSet
    Dim ps as MSSQLServerPreparedStatement
    
    ps = locdb.Prepare(Loc_Sql)
    ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
    ps.bind(0, 1)
    rs = ps.sqlselect
    If locdb.Error Then
      msgbox "Error: " + locdb.ErrorMessage
    else
      msgbox rs.Field("UserCode").StringValue
    End if
  end if

This runs successfully and returns the “UserCode”. However, if I change the SQL to an invalid one like “SELECT *, FROM SystemUser WHERE GUID=?”, it does not return error but gives me an NilObjectException at rs.Field(“UserCode”).StringValue.

I tried using ODBC to connect to a firebird DB with similar test, it does return error to me. Can I say this is a bug or so?

2

My experience indicates that the MSSQL Native client is full of holes. Xojo’s plugin uses this client. Yes you can say this is a bug, but who owns it?

Maybe try Christians plugin. I don’t know if this will help, I’ve just worked around the more obvious issues with prepared statements that use the MSSQL Native client.

3

i tried to use odbc to connect to firebird database doesn’t have this problem. Don’t know if I use the same to connect to MsSql server do have the same problem or not. Is that odbc will be slower than mssql natice client? Another thing is ms sql native client support MARS.

4

You should to refer to these threads. I battled this for quite some time and finally gave up trying to use prepared statement in MSSQL:

MSSQL 1

MSSQL 2

IF you find a solution please post for the benefit of future searches. Good luck!