[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 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 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
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
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:
dim rs as RecordSet = db.SQLSelect( sql )
if db.Error then
raise new BKS_Database.DatabaseException( db, sql )
end if
return rs
End Function
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
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 rs as recordset = db.sqlselectRaiseOnError(s)
//If exception it returns immediately!
while rs.eof = false
//do something with the data
rs.movenext
wend
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.]
So, let me get this straight. You’re saying we should check our databases for errors? 🙂
@Brad Rhine
No. You MUST check your database for errors. :O
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.
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.
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?
please replace
raise new BKS_Database.DatabaseException( db.ErrorMessage, sql )
with
raise new BKS_Database.DatabaseException( db, sql )
to make it working.
@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.
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.
What about escaping user input and binding queries?
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!
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.
Problem is I am looking at the code I wrote last week….. 😉
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?
@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.
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