Detect Error from Prepared Statement

Databases
1

Hi All,

Here is my code, self is a connected database , Loc_Sql is a sql statement passed into the module

  Dim rs as RecordSet
  Dim ps as MSSQLServerPreparedStatement
  
  ps = self.Prepare(Loc_Sql)
  If self.Error Then
    msgbox(loc_sql)
    MsgBox(self.ErrorMessage)
  End If

   ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
   ps.bind(0, "Test")

  rs = ps.sqlselect
  If self.Error Then
    msgbox(loc_sql)
    MsgBox(self.ErrorMessage)
  End If

I intentionally passed in an invalid sql statement, however no error is raised. Maybe something wrong with my syntax?

2

Self is a protected keyword and is the parent window. Try me instead?

3

Agreed. Don’t use Self for your own property names. It’s magic in all sorts of ways in a desktop app and even more so in a web app.

4

Thanks. But I tried to use the instance name to replace self, still no error raised. :frowning:

5

I would still check the ErrorMessage property. Just in case it’s some sort of warning that doesn’t warrant setting Error to True.

6

Thanks Greg. My testing statement is a totally invalid statement, I think the db should capture this error.

7

Have you stepped through this code in the debugger to see if it makes it all the way through? I ask because the prepare statement throws exceptions and does not set error codes.

8

In the above sample, could you show the sql you are passing?

9

The SQL statement is simple: SELECT GUID, Status FROM SystemUser WHERE UserCode=?

and then I pass this SQL together with an 2 dimensional array representing values and data types to the database class. In the above example, putting the “Test” string is just for simplicity, actually I am reading the passed array for binding. Then I return a recordset back

If the record exists, I can read the value in the calling window by rs.Field(“GUID”).integervalue. That means, the code works. Just if I try to change the statement to like: SELECT GUIDs, Stats, FROM SystemUser WHERE UserCode=?, no error is posted

10

I have never seen an error creating a prepared statement on any database engine in Xojo, it is not until you sqlexecute or sqlselect that you’ll get your error.

11

Yes I did:

rs = ps.sqlselect
  If self.Error Then
    msgbox(loc_sql)
    MsgBox(self.ErrorMessage)
  End If

Just because I didn’t get error from here, so I try in somewhere else

12

What instance name? Just try “me” there.

13

The very first answer told you not to use Self. As long as you insist on using self there is no helping you. Stop wasting everyones time.

14

Sorry guys, I was out of work today… I didn’t meant to insist to use self, just I don’t have time to test yet… Will try your suggestion to use me instead, and will post here again the result. Thanks all.

15

Okay, I have tested to replace self by me, still no error is posted, here is my complete code (it is under a class myDatabase which is a subclass of MSSQLServerDatabase):

DatabaseSelect(Loc_Sql as string, BindValues(,) as string) as RecordSet

  dim i as integer
  Dim rs as RecordSet
  Dim ps as MSSQLServerPreparedStatement
  
  ps = me.Prepare(Loc_Sql)
  If me.Error Then
    msgbox("t1: " + loc_sql)
    MsgBox(me.ErrorMessage)
  End If
  for i = 0 to UBound(BindValues)
    Select Case BindValues(i,1)
    Case "String"
      ps.BindType(i, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
    Case "Integer"
      ps.BindType(i, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
    End Select
    ps.bind(i, BindValues(i,0))
  next
  rs = ps.sqlselect
  If me.Error Then
    msgbox(loc_sql)
    MsgBox(me.ErrorMessage)
  End If
  
  return rs