Automating Xojo Database Development with ARGen

Database applications are relatively easy with Xojo. The native Database, DatabaseRecord, and Recordset classes make it easy to connect, add, edit, and delete data in your console, desktop, and web applications. What we’ve found over the years isn’t that it’s hard to create these applications but that it’s tedious and also the very nature of Xojo tends to make life more complicated. Let me explain.

The Xojo database classes are very generic. To insert data into the recordset you either use an SQL Insert statement or use the DatabaseRecord class. The SQL statement, by itself if fraught with peril as it’s very easy to mistype the statement and miss it in testing. Even using PreparedStatements (which you should be using whenever possible) you can still screw it up. Using the DatabaseRecord class is more object oriented and without a doubt easier to use but it has one big problem: It doesn’t check datatypes. At design time it will happily let you try to put a date value into an integer field (depends on how you try, of course). It is not until runtime that you may get any sort error which leads to the next issue.

Database errors, at least in the global framework, do not generate exceptions. This means that unless you, the developer, check for the error, you may not get the results you expect. On an Insert statement, either using SQL or the DatabaseRecord, your data may simply never be inserted and you’ll never know it unless you’re checking the Error property on the database connection. The Error must be checked after EVERY SQL action whether that be SQL Select, Insert, or Delete. Even checking after a Prepare statement is wise to do so.

The good news (maybe) is that Xojo for iOS and the iOSSQLiteDatabase class will throw an exception if it experiences a database error. It’s good that you don’t need to check for the error manually, but now you need to put in exception handling around your database code and deal with exceptions accordingly. It’s not hard, but it’s also not trivial code either.

The IDE knows next to nothing about the database and certainly when you code it you have to query the database for Table and Field schema’s. To make it worse, the Xojo database framework has no idea about the concept of Views requiring you to query the database specifically to find and learn more about them. The IDE has no idea about the field datatypes either and the compiler can’t warn you about mismatched datatypes. These types of errors are only caught at runtime which is usually way too late in the process (hopefully not by the client either).

The database frameworks are not very object oriented friendly. If you have a simple Add/Edit dialog you need to know if the record is new, or not, since this changes how you code saving the data. New requires the DatabaseRecord object and an existing record can be updated via the Recordset object (both can use SQL statements but the statements themselves require different statements).

Xojo is easy to use but the ability to stick code in control events tends to make big database applications unwieldy and full of spaghetti code. Changing a field name in a commonly used table, or even worse changing the field datatype, is often at your own peril because you might have SQL statements referencing it in hundreds of places in the code. Forget it one place and you have an error in the waiting.

Finally, there’s the hooking your user interface up to your database code. Many people have Load/Save methods in every window/dialog/container that needs it. Use a SQL statement to get the data, then use the Recordset to load the user interface, and then use a Save method (that knows the difference between new and existing records) to put it back into the database. Again, the IDE compiler can’t help you if you make either a spelling mistake in the SQL statement or Field names so you won’t find these errors out until runtime (which is often too late).

There are options, to all of these problems. Creating your own data classes is a step above the standard Xojo code, in my opinion. You encapsulate a record into a Xojo class and implement your own Load and Save methods. This is a better object and forces some compiler warnings into your code. It also tends to put your database code all in one folder and/or NameSpace. You can use field name constants for field names, and force all db code into one class or module. Unfortunately, if you have hundreds of tables it’s also tedious to do all this.

There are other solutions out there, but ARGen takes some of the tediousness out of the work of creating a Xojo database application and does some other nifty things too like creating a basic User Interface for you.. ARGen is short for ActiveRecord Generator. ActiveRecord is a set of classes that map the database into equivalent Xojo namespaced classes.

Say you have a table named Employee. ARGen would create a class named ‘Employee’ in the ‘Data’ namespace and you would reference it in code as Data.Employee. Each field in the table is then mapped to a property in the class. The FirstName text field would be mapped to a string property named “FirstName”. This property is referenced as Data.Employee.FirstName and since the compiler knows this property is a string it will complain if you try and put an integer into it. Since there’s only one place you would ever define FirstName in your project it becomes really easy to change the name or change the datatype and then have the compiler catch any errors. It also means that AutoComplete in the code editor works meaning you’ll never mistype a table or field name again without the compiler catching it.

When an ActiveRecord project first starts up and connects to the database we call a Register method that calls out which tables/views are added into ActiveRecord. ActiveRecord scans the schema of the table and then attempts to match up every field with a corresponding property on the class. If a field is missing from your class it will generate an error that’s caught only while debugging (a “table x is missing field y” type of message).

Each ActiveRecord instance is a complete record. It has built-in Save and Delete methods. It also has events for Before and After Create, Before and After Update, to let you do things globally for each record. One example we use all the time are CreatedBy, CreatedDate fields in the Before Create event and ModifiedBy and ModifiedDate fields in the Before Update event. One place, ever, to put that data into the database rather than the potential thousands of places it might. There is also a convenient IsModified method to figure out if data has actually changed or not.

The Save method knows if it’s a new record or an existing record and does the right thing behind the scenes for you. Using a transaction, using a PreparedStatement. I think this one feature alone saves us hundreds of hours of coding because it’s a simple Save call. Nothing more be done. It really takes the worry out of it.

There are other coding features that we could get into but it all revolves around making creating database applications in Xojo fast and easy. ARGen creates a bunch of helper functions like IsDuplicate, List, and so on to help make your life easier. ARGen has a free mode which allows you to get the ActiveRecord classes and up to two tables at a time done.

For many years this is all ARGen did and we used it with great success. However, we found ourselves spending a lot of time on the next step – creating windows, dialogs, and containers and hooking up the database classes to the User Interface. Practically every table you run across has a List and Edit form. In version 2 we added the ability create a rudimentary User Interface which saves even more time. You only get this ability with the fully paid version.

When generating a Xojo project ARGen puts in a number of #pragma error statements where the developer needs to look at code and uncomment code and/or fix code. It’s hard to guess and frankly we don’t want your newly generated project to compile without you having to fix some code. Trust me – it’s better this way. There are simply too many variables and ways of doing things. Because of the #pragma error statements I like to bring over classes and UI over from the generated project as I need them rather than all at once. No need to fix hundreds of pieces of code until you’re ready to look at them.

ARGen and ActiveRecord isn’t perfect and it’s not the only solution around. ARGen lets the compiler do some work for you and eliminates some of the very common, but trivial, mistakes. It also lets you save a ton of time when it comes to standard database code and building user interfaces. It isn’t a panacea for development but ARGen can save you a lot of time and effort.

If you’d like to learn more about ARGen and what it can do for you, please download the free/demo version from the product page at http://www.bkeeney.com/allproducts/argen/. There are several videos on the ARGen menu that show more details.

Update:  I did a Xojo webinar a while back that talks about some of this in detail.  It’s a little dated but worth watching:  http://developer.xojo.com/webinar-simplying-db-access-with-bkeeney-activerecord

Xojo Cloud Database Support

Last week Xojo announced new features for Xojo Cloud.  They now support MySQL and PostgreSQL database servers in addition to SQLite that they have supported since day one.  One of the interesting features with the database support is that db admin tools that support SSL tunnels can connect to the database as if it was running locally.  In my testing it was surprisingly easy to setup and use.

The first thing to do is log into your Xojo Cloud account control panel.  Then simply enable either the MySQL or PostgreSQL database and enable the SSL Tunnel.  In each case you will receive a username and password that you’ll need to copy before moving on to the next step.

Screen Shot 2015-02-26 at 10.28.25 AM

Our MySQL admin tool of choice is NaviCat.  Setting it up was pretty easy to do.  Create a new connection and then navigate to the SSH tab.  Enter your server IP address, the username and password.

Screen Shot 2015-02-26 at 10.32.30 AM

Then navigate to the General tab and enter a Name for this connection (I used Xojo Cloud).  Because you’re using the SSL Tunnel you need to enter ‘localhost’ into the Host field.  Enter your Xojo supplied username and password and then test your connection.

Screen Shot 2015-02-26 at 10.38.00 AM

After that, everything acts just as if the server were local to you.  In this example I created a sample database named ‘bkeeney’ and a table called ’t_temp’.

Screen Shot 2015-02-26 at 10.34.09 AM

Your Xojo web application, then, will connect to it via the localhost parameter along with username and password suppled to you from Xojo.  Because it’s inside the firewall your web app needs to do nothing more.

Setting up a database server in Xojo Cloud really is that simple.  It just works.  From start to finish it only takes a few minutes to get up and running.  It’s a great addition to Xojo Cloud.

Recordset’s are Funny Things

We have a new developer working at BKeeney Software and he’s in the learning phase of Xojo. Not only is he new to Xojo but he doesn’t have a ton of experience doing software development in general. As someone who’s been using Xojo for a long time it is interesting to see how people struggle with the language and the IDE.

Today we had one of those “teaching moments” as he was beating his head up against the wall with this bit of code in a save method. This is a simple Invoice application saving the invoice line item to an SQLite database that’s on the local machine. See if you can spot the mistake.

dim rs as recordset =db.sqlselect( "Select * From tinvoicelineitem where invoicelineitem_ID = " + str(iInvoiceLineItem_ID) )
if db.error then
   msgbox CurrentMethodName + " db error: " + db.ErrorMessage
   break
   return false
end
rs.Field("InvoiceID").IntegerValue = iInvoiceID
rs.Field("salesTax_Flag").BooleanValue = bSalesTax_Flag
rs.Update

if db.error then
   msgbox CurrentMethodName + " db error: " + db.ErrorMessage
   break
   return false
end


Have you spotted the error yet? I don’t blame you. It’s easy to miss and it took me a few minutes of staring at it to find it. The code runs with zero code errors and zero database errors. The only thing that’s wrong is that it doesn’t save any line item data.

Okay, I’ll tell you. I forgot to add the rs.edit command at the top of the method. Without telling the recordset that you’re editing it, it will never generate the SQL to update the RecordSet.


dim rs as recordset =db.sqlselect( "Select * From tinvoicelineitem where invoicelineitem_ID = " + str(iInvoiceLineItem_ID) )
if db.error then
   msgbox CurrentMethodName + " db error: " + db.ErrorMessage
   break
   return false
end
rs.Edit // IMPORTANT!
rs.Field("InvoiceID").IntegerValue = iInvoiceID
rs.Field("salesTax_Flag").BooleanValue = bSalesTax_Flag
rs.Update

if db.error then
   msgbox CurrentMethodName + " db error: " + db.ErrorMessage
   break
   return false
end


I can argue that the lack of an error is bad. Sure, I didn’t code it right, but you’d think there would be some sort of error if an update was called without a starting edit. I’ve added <feedback://showreport?report_id=35134> to the Feedback system.

To be honest, we don’t do straight Xojo database calls much any more simply because we’re using ActiveRecord for most projects. AR eliminates most of the tedious database coding and let’s you deal with the data and the UI. When you call the Save in ActiveRecord you don’t need to care if it’s a new record or not. AR handles all the gory (and boring) details for you.

What say you my Xojo friends? Do you get bit by this type of thing when doing database coding?

Debugging Your Xojo Applications

Your customers and clients expect your Xojo applications to be as bug free as possible.  What mechanisms do you have in place to handle an error and report it?  Bugs occur – that’s a fact of life – and even the best error handling in the world can’t prevent bugs from occurring.

Thoroughly testing your application is your first and best line of defense.  However, it’s very time consuming and without good testing procedures it may even be a waste of time.  I would also add the it’s very hard for the developer to be a good tester of their own code.  You programmed it to do a certain task in a certain way.  Someone else will have a different set of expectations.

Regression testing is the only way to really make sure that changes in one part of your code doesn’t change other parts of your application (or a new version of Xojo doesn’t affect you either!).  An excellent way to do regression testing on your software is to use the open source unit testing module called XojoUnit (it’s now part of Xojo).  It allows you to test your code with known inputs and test them against the actual output.

A common question from the forums is that people get an error message saying, “The application has encountered an error and must now shutdown,” and they have no idea what the error is or where the error happened.  They need to learn as much as they can about the Exception class and in particular the Stack property.  The stack was introduced way back in 2006 and is a string array that contains the methods that have run from the entry point into your code until where the exception occurred.  Be aware that the Include Function Names property has to be true in your application for the stack to be human readable.

Use the UnhandledException event in the application class to capture any errors that weren’t handled elsewhere.  The exception stack allows you to determine where the error occurred and from there it’s a simple matter to send an email, post to a web form or write the error out to a log file that includes important details such as platform, operating system version and the version of your application.

Some applications will require files be in a specific location and when debugging your application those files might not be in the proper (final) location.  Use the DebugBuild constant along with conditional compilation, #If,  to handle things differently at debug time and runtime.  For debugging purposes you can have the required files in the local project directory for convenience sake.  A feature added in 2007 allows you to place your debug build in a particular location which eliminates the need to have non-project files in your project directory.

Cross-platform applications require additional handling but now that Xojo with (or without) a desktop license can do remote debugging and it’s very easy to do.  I run the Xojo IDE on Mac OS X on an iMac and use VMWare running various versions of Windows and Linux so I can debug my applications in those environments.  The remote debugger works exactly like the regular debugger except that the debug application is running in another environment.  It’s a little slower to initiate since the app has to be transferred to the other environment but otherwise it’s the same process.

I highly recommend testing early and often on the other platforms you’re developing for.  Don’t wait until the end to do extensive testing.  While Xojo does a great job on cross-platform applications there ARE platform differences you need to be aware of.

New developers coming from Visual Basic 6 are often irritated by the perceived lack of database error in Xojo.  An incorrect SQL statement when opening a recordset results in nil recordset objects instead of a throwing a runtime error.  The unexpected nil recordset then causes NilObjectException errors.  You must get in the habit of checking your database object for errors after every database operation.  Once you catch the error you can at least be more graceful on how to recover from it.

That’s a lot of information so do your research.  Debugging your application isn’t as hard as you think.

What things do you do to make your life easier hunting down or preventing bugs?

ActiveRecord and ARGen Updates

ARGen256We’ve been consistently updating ActiveRecord for our consulting and internal projects.  We find ActiveRecord to be a wonderfully way to get the Xojo IDE to do more work for you.  It reduces table/field name errors and gives you some nice autocomplete features in the Xojo Code Editor.  In addition to all that you never have an issue with silent data type conversion errors.  It has saved us hundreds of hours of development time in the past couple of years.  Another huge bonus for us in team development is having it flag the developer (runtime exception) when a new field is added but it’s not in the class yet (at runtime it’s silent).

Today, we released an update to the general ActiveRecord release and is available through the ActiveRecord page at http://www.bkeeney.com/rbinto/activerecord/

New in this release:

  • Support for CubeSQL Server
  • Provisional support (meaning don’t call us if it doesn’t work) for Oracle DB servers
  • Fixed a number of issues with MySQL, and PostgreSQL
  • Added SQLiteDatabase for Xojo users migrating away from REALSQLDatabase
  • Added more comments for errors when there are missing plugins (now very likely with CubeSQL support).

We also updated ARGen, our utility to generate the classes required to use ActiveRecord in your Xojo applications.  This $19.95 utility is a tool that makes creating the ActiveRecord classes a breeze.  Point it to the database you want, select the tables, hit the create button and it generates the Xojo classes required to use the database with your ActiveRecord application.

While it’s not very hard to create your own classes but this utility is a huge time saver.  For a test I took one of our big databases (about 350 tables with varying number of fields in each) and ran it through ARGen.  In 20 seconds it generated all of the data classes for the database and all I had to do was open the resulting XML in Xojo and drag the data classes into a project.

More information on ARGen at http://www.bkeeney.com/allproducts/argen/

New in this release:

  • Support for CubeSQL Server
  • Provisional support (meaning don’t call us if it doesn’t work) for Oracle db servers
  • Added SQLiteDatabase for Xojo users migrating away from REALSQLDatabase
  • Added the ability to add suffixes to your field names (in addition to the prefix)

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

Recordset.idxField vs Recordset.Field

Yesterday I was complaining about my OPC project and I’ve added a few more bullet points in the comments section.  This morning I added a comment about how the idiot developer used Recordset.idxField(index) rather than Recordset.Field(“FieldName”).  I hate it when developers use idxField because it makes it tough to read and I constantly have to go back and look at the query and count fields.  In the short term I modify the query string in the IDE and put index comments in the in string.  Something like this:

s = “Select field1, ” _ //1

+ “field2, ” _ //2

and so on but it’s still a pain.

Anyway, Christian S. posted a comment that got me thinking.  He said:

idxField is good. It can speedup applications a lot.

Is that really true?  I have never tested it so I created a little test application using an actual query from the project I’m working on.  I run the same query twice.  The first time I loop through the recordset using nothing but idxField and the second time I use nothing but field which uses the field name.  The results are interesting and confirmed my suspicions.

If I run my two functions just once and bring back only 1 record and do one read in the function the idx field is indeed faster by about 50%.  However my testing differences range from 6 to 300 microseconds faster and while I didn’t average out all the results, I’d say most times it was around 60 microseconds faster.  I ran this test a lot to see if I would see a wider variation but did not.  My 300 microseconds difference happened just once out of about 25 runs.

I changed my query so that instead of bringing back 1 record it brings back over 700 and I loop through the recordset and then do the same same test.  IdxField comes back about 15% faster.  When I loop through the same row 10,000 times there is no statistical difference which means that while Field is slower it’s a one time hit since my tests show that even doing the same pull 10,000 times the total difference between the two functions is still only about a 10-15% difference in overall speed.

My conclusion is that yes, idxField IS faster than using Field but to say it speeds up an application “a lot” is an exaggeration.  In 10 years of Real Studio consulting work I have rarely done an application that requires that much speed from the queries.  If you have any sort of graphical user interface I think it safe to say that updates to the controls on screen are significantly slower than pulling data out of the recordset.  That’s NOT to say that the query itself is fast because often times that is the most limiting factor.

I won’t discourage you from using idxField.  If you do, though, I really encourage you to use constants named after your fields.  So perhaps you create a constant named kField1 and the value is 1 for the first field in your recordset.  The only drawback to this approach is that you (or the developer after you) will change the SQL query and all the code using idxfield no longer works because your field order is different.

I much prefer the explicitness of using Field because I never have to worry if I change my field order.  It’s very readable.  It’s not perfect and there are plenty of reasons why it’s not the best (there’s a reason why we use ActiveRecord in a lot of projects).  Use what’s best for you and what’s best for the five years from now.

What say you, RB developers?

See You in Frankfurt

Join me at the Real Studio Database Days training in Frankfurt, Germany on November 3rd and 4th.  I am looking forward to talking to the group.  Speakers include:

  • Stéphane Pinel from Real Software
  • Geoff Perlman from Real Software (via video chat)
  • Jens Boschulte from DynaForms GmbH
  • Simon Larkin from QiSQL
  • Thomas Tempelmann
  • Christian Schmitz from Monkeybread Software
I love going to developer conferences.  I get to meet people that are passionate about Real Studio and those that are just learning about it.  What’s best, though, is the cool stuff I learn from those that haven’t spent ten years working with it like I have.
In my recent trip to Nigeria, someone totally new to Real Studio taught me a new technique with web apps.  I can’t wait to try it out on a project.  So you never know what you’ll learn!  See you there!

Real Studio Database Days

If you’d like to learn more about Real Studio and how it integrates with your favorite database there is an event coming up that might be of interest to you.  MonkeyBread Software is hosting the Real Studio Database Days, November 3, and 4, 2011 in Frankfurt, Germany.

Currently there are over 30 people registered from eight countries including Germany, Austria, Belgium, France, United Kingdom, Netherlands, and the USA.  This is a good time of the year to get away.

More info can be found at http://www.monkeybreadsoftware.de/realbasic/events/#database days

After checking flight costs there’s a possibility I’ll be there.  If I do, I’ll do a short presentation on our REALbasic implementation of ActiveRecord.  If you’ve not used it, ActiveRecord lets you use auto-complete of your tables/fields in the Real Studio IDE, takes advantage of the strong type-casting in Real Studio and, takes advantage of REALbasic events.

 

 

 

 

 

 

 

 

 

Why Is My Recordset Nil?

This one comes up on the RB Forums a lot.  The post generally goes something like this:  “I have a database and everything works fine until here <insert code snippet> and I get a Nil Object Exception because rs is nil.”

If everything else is working fine then it must be the recordset.  If you’re not checking the database for an error you’ll never know!  The db.error property tells you that you had an error and the db.errormessage property does a pretty good job of telling you what the error is.

dim rs as recordset
rs = db.SQLSelect(“SELECT * FROM sometable WHERE somefield=avalue”)
if db.error then
msgbox “Error!” + db.ErrorMessage
return
end

//do something with the recordset here.

I find this happens a lot with developers that came from Visual Basic 6 where database errors throw an exception.  REALbasic doesn’t work the same way.  Get over it and start checking for database errors.

If you want to capture database errors you can do something like what we’ve done:

if gDB.Error then Raise new BK_Debug.BK_DatabaseException(gDB.ErrorMessage, s)

Where BK_DatabaseException is a RuntimeException subclass defined like this:

Sub Constructor(sMessage as string, sSQL as string)
If sMessage <> “” Then
Me.Message = sMessage
End If

If sSQL <> “” Then
Me.Message = Me.Message + EndOfLine +  EndOfLine + sSQL
End
End Sub

This can be useful if you have automated error handling and can receive this information.  Of course, if you’re creating a runtime exception you had better be handling them in the app.unhandledexception event otherwise your app will shutdown and you’ll have pissed off users.