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

17 thoughts on “Database Programmers: Do Yourself a Favor

  1. If your first code sample was inside a try-catch block, wouldn’t that achieve the same thing?
    I mean when you get a database error the whole operation generally has to be aborted anyway

  2. jjb :

    If your first code sample was inside a try-catch block, wouldn’t that achieve the same thing?
    I mean when you get a database error the whole operation generally has to be aborted anyway

    No because it reports absolutely NOTHING back to you except you had a Nil Object Exception. The whole goal is to find the DATABASE error and fix it.

    You are correct that it generally has to be aborted but without telling me what the DATABASE error is any sort of try-catch block is practically worthless.

  3. This is great stuff. Thanks Bob.

    As you mentioned in the post other platforms throw an exceptions when there is a DB error, RS does not. This is a painful lesson to learn the hard way.

    As a noob to RS (coming from VB6) take Bobs advice and extend the Database class and create a DB exception class. It will save you much pain and suffering.

    Heck he was nice enough to post the code so you might as well.

  4. Thanks for the explanation Bob, I’m sorry if this is a bit dense but could you just clarify something?
    In vb6 if, e.g my SQL s is faulty and if I have the next line inside an error handler :

    Set rs = db.OpenRecordset(s)

    I will be returned an Err.Number an Err.Description and (if I set things up that way) an Erl, the line number of the error.
    Does RB’s try catch returns none of those things?

  5. please replace

    raise new BKS_Database.DatabaseException( db.ErrorMessage, sql )

    with

    raise new BKS_Database.DatabaseException( db, sql )

    to make it working.

  6. @jjb
    No. In RB the only think you’ll get is that you have a Nil Object Exception. The error isn’t *in* RB, per se, the error is in the database. Subtle distinction but huge.

    The point is that RB doesn’t do any DB error handling for you. It’s up to you to implement it and frankly I see this as such a common problem I had to comment on it.

  7. Thanks very much Bob, it’s very generous of you and I’ll definitely be incorporating this from now on.

  8. jjb :

    Thanks very much Bob, it’s very generous of you and I’ll definitely be incorporating this from now on.

    Not a problem. I’m just trying to save the world one user at a time.

  9. Thanks, Bob. Every time I see your code it reminds of how merely average I am as a coder and that I am really getting by on knowing my industry inside and out. On to the next lesson…..

    @Brad Rhine – while I have your ear, just wanted to let you know your PNG Mask Extractor is great, saves me tons of time when I am trying to create masks for the double image pics REAL and some of the 3rd party tools use for icons. Thank you!

  10. Merv :

    Thanks, Bob. Every time I see your code it reminds of how merely average I am as a coder and that I am really getting by on knowing my industry inside and out. On to the next lesson…..

    Thanks, Merv, but don’t sell yourself short. I’ve gone back and looked at my early attempts at coding and I’m not too proud of it. This code is based on years and years (and more years) of doing it the hard way.

  11. I see one problem, huge IMHO especially with web edition:

    dim s as string = “Select * from MyTable where fldLastName = ‘” + SomeCritera + “‘ order by fldLastName”

    Using this type of query in an example to beginners or people who don’t know how to use the database methods properly is just wrong. One should arguably always use a prepared statement. The above should be written as:

    Dim q As REALSQLPreparedStatement = db.Prepare(“SELECT * FROM MyTable WHERE fldLastName=? ORDER BY fldLastName”)
    q.BindType(0, REALSQLPreparedStatement.SQLITE_TEXT)
    q.Bind(0, SomeCriteria)

    Dim rs = q.SQLSelect

    It is more code and makes for a longer tutorial but I have seen much, much code like your example in the wild and they are just waiting to be bit, especially if their app is web based, thus thousands of SQL injection bots just exploring forms for the exact error you are helping people make.

    You may address it in a later post, but is anyone who read this one going to read your next also and then change all their code?

  12. @Jeremy Cowgar
    That’s actually a very good point. This is a bad example for a web app because of SQL injection attacks.

    However, the point of the post was that Real Studio developers don’t check for errors when they should. If I could just get that *one* point across to people just starting out they’d save themselves some time later on.

  13. Yup, I agree. However, using Prepared Statements in desktop apps has just as much application. Having a disgruntled employee, they could simply enter into the search field:

    John Doe’; UPDATE user SET is_admin=1 WHERE code=’myself’;

    and now they have admin access to a “secure” application/database system.

    Further, even for the non-hacker, in your example imagine searching for business name “Smith’s Dairy”. Crash, most likely.

    You can employ an escaping method and not use prepared statements, but to ensure cross-database compatibility, one should simply use prepared statements.

    Jeremy

Comments are closed.