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.