Database Programmers: Do Yourself a Favor

[NOTE: ] As Jeremy points out in the notes below, this example really is BAD for web apps because of SQL Injection attacks. You should start looking at PreparedStatements if you have not done so already.

I get to see a lot of code from other developers and it seems that in the past couple of months I’ve seen a LOT of code.  Real Studio is a good database programming environment (we can argue all day long about binding but that’s another post) and is arguably very easy to learn and use.

But I can say, without any doubt, that between the code I’ve seen and the questions I’ve answered on the Real Software forums regarding database issues, people just don’t get how to use them.  In practically all cases the Real Studio developer could have easily solved their issue if they had just checked the database error bit and the error message.

We see a lot of code that does something like this:

dim s as string = "Select * from MyTable where fldLastName = '" + SomeCritera + "' order by fldLastName"

dim rs as recordset = db.sqlselect(s)

while rs.eof = false
   
   //do something with the data
   
   rs.movenext
   
wend

Pretty straightforward stuff except sometimes the recordset is nil and causes a Nil Object Exception.  Nil Object Exceptions are ‘bad’, right?  So what do people do?  Well, they tend to do something like this:

dim s as string = "Select * from MyTable where fldLastName = '" + SomeCritera + "' order by fldLastName"

dim rs as recordset = db.sqlselect(s)

if rs <> nil and rs.recordcount > 0 then
   
   while rs.eof = false
      
      //do something with the data
      
      rs.movenext
      
   wend
   
end


Slightly better as it won’t throw an error now but the problem still persists because we don’t know WHY the recordset was nil.  A better way to do this is to check for the error because we know, from experience, that the ONLY time a nil recordset is returned is when there is a database error:

dim s as string = "Select * from MyTable where fldLastName = '" + SomeCritera + "' order by fldLastName"

dim rs as recordset = db.sqlselect(s)

if db.error then
   
   msgbox "DB Error in " + CurrentMethodName + endofline + db.errormessage
   
   return
   
end

while rs.eof = false
   
   //do something with the data
   
   rs.movenext
   
wend


So perhaps that’s not ideal because you don’t want to end user to see the error but you should at least have this sort of error checking and handling to see what you did wrong (if anything).

I think some of this practice has come from developers migrating from Visual Basic 6 where exceptions are raised on a database error.  They expect REALbasic to do it too so don’t even think about it.  The other reason is that Real Software’s examples and documentation don’t check for errors like they SHOULD be doing (they might be now that Paul L is doing the documentation but for years this has been a problem).

We do a dozen or so big projects a year so this means that over the past ten years we’ve done hundreds of database projects.  Eventually we found ourselves doing the EXACT same code over and over and over and over again and eventually we got tired of it.  So we came up with our own methods for checking for and dealing with database errors.  One of those is our own SQLSelectRaiseOnError:

Function SQLSelectRaiseOnError(extends db as Database, sql as String) As RecordSet
   
   dim rs as RecordSet = db.SQLSelect( sql )
   
   if db.Error then
      
      raise new BKS_Database.DatabaseException( db, sql )
      
   end if
   
   return rs
   
End Function


So rather than calling the built in SQLSelect statement we use our own that will raise our own subclassed Runtime Exception.  It’s Constructor is VERY simple:

Sub Constructor(db as Database, sql as string = "")
   
   if db.Error then
      
      ErrorCode = db.ErrorCode
      
      ErrorMessage = db.ErrorMessage
      
      Message = Str( ErrorCode ) + ": " + ErrorMessage
      
   else
      
      ErrorMessage = "Unknown error"
      
   end if
   
   if sql <> "" then
      
      Message = Message + EndOfLine + "   " + sql
      
   end if
   
End Sub


We capture the error code, message, and hopefully the sql the developer used.  Then we can either use Try/Catch or handle the exception in a manner of our choosing.

At this point you can guess that we have our versions of SQLSelect, SQLExecute, and DatabaseRecordInsert.   Our initial (error handling) method then turns into:

dim s as string = "Select * from MyTable where fldLastName = '" + SomeCritera + "' order by fldLastName"

dim rs as recordset = db.sqlselectRaiseOnError(s)

//If exception it returns immediately!

while rs.eof = false
   
   //do something with the data
   
   rs.movenext
   
wend


This saves potentially 4 to 5 lines of code and over the course of a big database project this could mean thousands of lines of code.  We are lazy programmers – we don’t like to do more work than necessary and this is one way to accomplish that goal.

So do yourself a favor, always, always, ALWAYS check your database for errors.  It will make your life easier – I guarantee it!

If you’ve gotten this far I will say it again:

ALWAYS CHECK YOUR DATABASE FOR ERRORS!

Happy programming!  What other tips do you think would help people new to Real Studio?

 

[Edit:  Changed the code calling the new BKS_Database.DatabaseException.]