Database Primary Keys

The primary key that you define for your tables is critical to a well performing and well designed database.  The primary key to your table is very important so it’s worth spending some time on it BEFORE you start coding.

Let’s start out by describing what the primary key does.  The primary key is the unique identifier of a record for each table.  Regardless of how many rows the table has in it, the primary key is the pointer to a particular row.  This key will never change during the entire lifespan of the row.  There will never, ever, be two records with the same ID.

The primary key should never change.  This is why using a text field as a primary key that contains human readable/changeable data is such a bad idea.  I’ve worked on databases where they used Name as a primary key.  This is such a bad idea especially in cultures where women change their last name when getting married.  Even exempting female name changes most places in the world allow you to change your name legally.  Since it can change it’s a bad idea for use as a primary key.

Another example that we’ve dealt with in the past is using a Social Security Number (national id).  Another bad idea for a number of reasons.  One, data entry mistakes happen.  Two, government agency mistakes occasionally happen where there are two (or more) people with the same SSN.  Three, it’s not uncommon in certain parts of the country where undocumented workers use the same SSN.  Four, identity fraud is common.  Because this means it’s possible for two people or more people to have the same SSN it’s not a unique identifier.  Avoid using it.  It’s  a bad idea.

We recommend using an auto increment integer primary key.  Let the database do what it does best and let it pick it out for you.  That way there will never be any collisions or duplicates.  It’s what databases do very well all on their own.

I’ve worked on projects where there was no auto-increment primary key and was defined as an integer.  The developer devised a scheme to get the largest record ID and increment it by one when needed.  This was fine when a single user used the app but when multiple people were inserting data at the same time collisions occurred generating errors because primary keys must be unique.  It was ugly to fix and it all would have been avoided if they had used an auto increment primary key to begin with.

Someone asked me the other day if ActiveRecord could use a text primary key.  No, and it won’t ever.  It expects an integer primary key.  If you ARE using a text primary key.  Stop.  Add an auto-increment primary key and then add a Unique Index on the text field.

Real Studio/Xojo has some interesting things happen if you try to open a recordset that’s not returning the primary key in the query.  It won’t be able to update it in some cases.  You’ll get a generic message about not know which record you’re trying to access.  I’ve had some issues, in the past, using many-to-many tables that couldn’t be edited even though they had a unique index on fields.  So I recommend adding an auto-increment primary key event to many-to-many tables.  Then, if you have constraints on your index (like the index needing to be unique) you can add them there.  So far it’s worked for me.

For primary keys we use a standard naming convention:  tablename_ID.  A lot of people use a generic ID field but we’ve found that to be less than ideal when doing table joins.  If you have table A, B, and C all with an ID field joining them together means I have to put an alias in my SQL statement so I can retrieve those primary keys later.  By using the A_ID, B_ID, C_ID I never have to alias the field names in a query.  Plus, it has the added benefit of making the SQL easier to read because the name is explicit and the _ID tells me its a primary key.

Each database has its own internal record id.  SQLite uses rowid and you can actually query against it.  If you haven’t specified a primary key in your table it will use that rowid as the primary key.  When you specify a primary key, the specified primary key and rowID are the same.  However, if you do not specify the primary key the rowID is not guaranteed to stay the same.  If you were relying upon rowid to identify records in other tables you might be putting yourself in through a world of hurt if the user ever does a vacuum on the database.    The moral of the story is don’t rely upon the internal id – always specify your own primary key (that auto increments if you haven’t heard it enough now).

If you get into the habit of using an auto increment integer primary key you’ll avoid many problems later.  You don’t have to use our naming standard for primary keys but doing so will improve the readability of your SQL statements.  Primary keys are too important for anyone/thing but the database to figure them out.

Happy Coding!

Xojo Licensing Changes

Xojo, Inc. confirmed last week that the licensing for Xojo is changing.  The IDE now costs nothing.  Free.  Zip.  Zilch.  Nada.  Now we all know that it’s awful hard to stay in business by not charging any money, so the catch is that to make a build you need a license.  If you’re building for desktops (Mac, Windows, Linux) you need a desktop license.  Building for Web you’ll need the web license.  Building a console app you’ll need a console license.  The only oddity in the mix is if you’re using database servers which requires an additional license SQLite is included in all licenses).

Now that the IDE is free all users can take advantage of all Xojo features.  Item encryption, server sockets, SSL support, database encryption, remote debugging, container controls, code profiling, IDE scripting and build automation are the big items that were not available to all users depending on what license they had for Real Studio.  This has some advantages for training and getting people to actually use those features.  Everyone has the same set of features and all are available on all supported platforms.

Announced Pricing is this:

Console Licenses $100 ($50 renewal)

Desktop $300 ($150 renewal)

DB Servers $300 ($150 renewal)

Web $400 ($200 renewal)

So what does this mean for those with existing Real Studio licenses?  Real Studio personal licenses automatically get a Xojo Desktop license.  Real Studio Professional get Desktop, Database, and Console licenses for Xojo.  Real Studio Web licenses get converted to Xojo Web and Database.

Real Studio Enterprise licenses now get what’s called Xojo Pro.  Xojo Pro gives you all licenses.  You get desktop, console, web, and database licenses.  It comes with Priority Support which means that Xojo Inc. will handle all Priority Support issues first and then everything else.  Pro licenses also work on three machines (in contrast to the two for other licenses).

Another feature for Xojo Pro is that it gives you guaranteed access to the beta’s.  The Real Studio beta list really had no restrictions.  You signed up and you got access.  They didn’t say this but I suspect this is in response to the many people on the beta list that never reported anything but generated a ton of useless chatter.  This will definitely cut down on that problem.

Some people have taken offense to this.  They are not going to be Xojo Pro users so they feel that they’ll be cut out of the beta list.  The wording in the keynote was very specific.  Xojo Pro gives you ‘guaranteed’ access to the beta list.  That does not mean that you can’t join the beta it just means that it’s up to their discretion.  Those that have contributed in the past will most likely be welcome.  Those that have not are probably out of luck.

Another advantage of getting a Xojo Pro license is access to a Xojo Pro Only forum.  They figure it will most likely be the full-time developers (such as BKeeney Software) that use it.  Honestly, I have a problem with this even though I will most likely have several Pro licenses.  I have been a top 10 poster in the Real Studio forums for many years and I just don’t see how this helps me, or the community.  It seems like it will introduce some stratification into the community where there is none now.  I can’t imagine having an issue that I wouldn’t ask the entire community about.  I could go on about this but it seems to me that it’s a marketing bullet-point to make Pro look better.  I think it’s a bad idea and probably won’t use it.

The final ‘advantage’ of the Pro license is that Feedback cases now get a 3x multiplier. Real Studio licenses are a little different.  I believe Personal licenses get no multiplier, Pro/Web gets 3x and I thought that Enterprise licenses now get a 5x multiplier.  So in this scenario everyone else gets no multiplier but Pro users are the only ones to get it.

The Feedback multiplier isn’t a huge change but it definitely favors the Pro license.  I like this but I have my doubts that it will change anything significant.  As a pro user I have needs that are not being met by Real Studio (and now Xojo) despite many years of blogging and generating feedback reports.  Instead they have consistently gone for marketing bullet-point solutions and solutions that make it easier to sell to beginner and hobbyist developers.

Don’t get me wrong.  The free licensing has the potential to introduce Xojo to a lot of people that would consider themselves hobbyist or part-time developers.  That’s a good thing.  But it’s the folks like me (the Pro’s) that spend serious cash.  I want and need features that a) make my development life easier or b) help make me money (usually in completing things faster).  Not a whole lot of those types of features have been introduced in the past five years.

What do you think about the new licensing and the Xojo Pro features?

ActiveRecord Generator


Real Studio makes doing database applications very easy.  The strength of the database classes in Real Studio is that they’re generic enough to do almost anything.  The drawback to the database classes in Real Studio is that they’re generic enough to do almost anything.  No contradiction there, eh?

Database programming in Real Studio can be very tedious.  It’s the exact same code over and over again.  I generally have Load, SaveExisting, SaveNew, and Validation methods where I am literally just using the same combination of SQLSelect, SQLExecute, InsertDatabaseRecord statements over and over again.  Since the IDE has no idea what tables and fields are in the database it can’t help me detect table and field misspellings (a common issue) or tell me when I’m trying to convert data improperly.  Depending upon the database data conversion errors will happen silently with no errors (I’m looking at you SQLite and MySQL).

For years we’ve been using ActiveRecord for Real Studio.  It’s been a great way to abstract all the boring and tedious database stuff out of the way and let Real Studio work with us and for us.  It’s very nice to have AutoComplete work (most of the time) and the compiler warn of datatype mismatches.  It’s been a great timesaver but sometimes the work involved creating the classes in Real Studio was a hindrance.

About a year ago we were working on an conversion project where the original app was created in Microsoft Access.  The database had a hundred or so tables and some of those tables had two hundred fields in each table (I didn’t say it was a well designed database) and the client wanted a Real Studio version of it for Mac and Windows.  Doing all that work of creating the Real Studio classes would have taken twenty or thirty hours (or more) so we came up with a generator to create the classes for us.

This is where ARGen, the ActiveRecord Generator, for Real Studio was born.  Since then we’ve used it in a couple of projects and found it very, very handy.  It is now available in our web store and lets you scan an SQLite, MySQL, PostgreSQL or Microsoft SQL Server 2008 databases.  It then simply generates the necessary classes in a Real Studio XML file that you can open in Real Studio and copy and paste the namespace data files into your own application.

ARGen conveniently adds a generic DBOpen, DBClose, and Register functions into the namespace module letting you start using ActiveRecord as quickly as possible.  Each ActiveRecord class then has FindByID and List shared methods for your convenience.  Naturally you’ll add your own shared methods over time but it’s a good starting place for most developers.

You can try out ARGen for free but if you want to unleash its full potential it’s a paltry $9.95.  I think this is a steal since it might be saving you many, many hours of coding.

Downloads for the Mac OS  X and Windows versions can be found at 

Purchase in the BKeeney web store here.


Real World Only Two Months Away!

I don’t know about you, but I’m really looking forward to Real World this year.  Not only do I get to overdose on one of my favorite topics I get to reconnect with the many, many Real Studio developer friends I’ve met over the years.  Usually by the end of the week my voice is totally shot because I talk so much.

I think this Real World promises to be one of the more memorable.  The new IDE will most likely be released by then and it will provide plenty of material to talk about.  It looks like the entire first day is devoted to the BIG topics such as the unreleased iOS version, Web Edition, and the new IDE, to name just a few.  I will even go so far as to say that we’ll get a surprise announcement or two.

If you’re on the fence about going I suggest you take a look at the Sessions list at  Lot’s of sessions there for everyone to learn something new.

Seth, my lead developer, is leading two sessions on Object Oriented Programming.  We’ve been talking about ways to make this more engaging and useful than some of the sessions we’ve seen in the past.  I think everyone will enjoy it a lot.

I’m also doing two sessions.  The first is intermediate database programming techniques.  I think this will be how to work around the most common issues related to using databases in Real Studio.  This one hit home the other day when I had to work in a project that uses traditional Real Studio database coding rather than the technique we use.  It was painful and a good reminder on topics I need to add to my session.

My second session is about reporting tools for Real Studio.  I’m not a huge fan of the built-in reporting tool with Real Studio mainly because it doesn’t do a good job creating reports more than just a few pages in length.  There are other tools that you might want to consider and I’m hoping to show you one or two options you might not know about yet.

Don’t forget on the Tuesday before the conference we’re holding a training session of our own.  The morning session is all about databases.  We’ll be reviewing the Real Studio database classes and my “THOU SHALT NOT” list when it comes to database coding in Real Studio.

The afternoon session is about all the little things you need to polish your Real Studio applications.  We’ll talk about 3rd party tools, utilities, controls, libraries, and plugins.  We’ll talk about some of the most common needs/problems that Real Studio developers face and how we’ve overcome them over the years.

Sign up for both Real World and our training day before March 17th to receive a discount!

More information on the Sessions at Real World can be found at

More information regarding our training day can be found at

See you in Orlando!

Defining Primary Keys For Real Studio Apps

An issue that seems to come up fairly regularly with people new to database applications in Real Studio is how to make unique record identifiers when they insert data.  The simple answer is that you don’t – the database itself already knows how to do this.

SQLite databases (REALSQLDatabase in Real Studio) automatically have a rowid regardless if you define your own primary key or not.  You can access this rowid via sql and, in fact, have to use if you want to edit a recordset (if you don’t have a primary key defined) or you’ll get a database error along the lines of it not knowing which record to edit.  However, as soon as you define the primary key field it, and the rowid, are one in the same and hold the same value.

I’ve seen some developers come up with elaborate schemes to come up with unique primary id’s for the database tables.  This is a bad idea!  That database can (and should) do that for you.  It’s exceptionally dangerous to come up with your own scheme.  Bugs happen, mistakes happen, and the problem only gets worse when you add in multiple user databases where multiple connections can insert data at any time and when you have billions, or trillions, of rows of data.  Let the database do that work for you!  It was designed to do that afterall.

I think part of the problem is the built-in Database Editor in Real Studio is awful.  It doesn’t give you the option of making an auto-increment field.  See screenshot below:




We ditched the built-in editor many years ago for other tools.  For SQLite we use a variety of tools and they ALL do a better job of managing your SQLite database than Real Studio.  For example, take a look at the options that Base gives us just for the primary key:


The same goes for all the other databases that Real Studio supports.  There are tools that are genuinely better in almost every aspect that the Real Studio Database Editor.  Do a little research and you’ll find tons of tools that range from free to hundreds of dollars.  If you want editors that are similar across all databases I’d recommend Navicat

A lot of our projects use SQLite.  We use straight SQL to create our tables, indexes, and relationships.  Here is an example of defining an integer primary key that auto increments:

Create Table IF NOT EXISTS  T_Account(Account_ID    INTEGER  PRIMARY KEY AUTOINCREMENT, ShortAcctNumber    TEXT NULL …..

We always use the tools to create the database during development and then have the tools generate the initial SQL for us.  It’s simple and fast and I don’t have to remember the details.  I just define the fields and then copy and paste the SQL into the project.  I’m lazy that way.

If you’re looking for example of this process you can take a look at our subscription training videos at  Our Journal Entry project goes from start to finish creating the database from scratch via code and the showing how to add, edit, and delete data from the database.

Depending upon the rowid can be detrimental to the future of your project.  They can be reused whereas a primary key cannot.  Take a look at this blog post from Marco over at SQLABS:

A quick scan of MySQL and PostgreSQL documentation shows that they don’t use rowid so if you have to migrate from SQLite to a database server your rowid code is now invalid and you’ll rewrite your code to make and use a primary key.  So it’s just easier to start defining your primary, integer, auto increment keys now and start using the primary key field instead of rowid.

Let the primary key work for you.  Don’t create your own scheme for numbering your primary key field and for your own sanity ditch the Real Studio Database Editor.  Happy coding!

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

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

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

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

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

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:


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?

Getting Started with SQL?

Many Real Studio projects require the use of a database.  In 10 years of Real Studio consulting and many, many projects both internally and for clients, I can tell you the projects that did NOT use a database simply because they are the oddities in my career.  If truth be told, even some that didn’t use a database could have been, and perhaps should have been using a database.

A lot of Real Studio developers avoid them because the term ‘database’ is scary and mysterious and brings up images of having to wrestle with huge problematic installations of MS SQL Server, PostgreSQL and MySQL.  While you might have to use them eventually, we do a lot of development on the lowly and surprisingly powerful SQLite.  It is a lightweight database that offers most of the features of the big database servers.  But for many, SQL, the language behind databases, is mysterious and arcane.  Not so!

At last weeks Real Studio Database Days training in Frankfurt, Simon Larkin of QiSQL gave a very interesting talk about SQL.  He also has a section on his website devoted to learning SQL.  His SQL School takes you through the basics of the terms and then starts introducing you to more and more complex situations.

Equally important is his Database Design section that walks you through the best ways of designing your database.  Learn about primary keys, entities, relationships and database normalization.  All in all the tutorials are very well done and if you’re just starting to use databases this is a good place to start.

Simon likes to use raw SQL, which is fine, but I’m lazy and SQL-challenged at times so I tend to use a number of SQLite utility applications.  Since each seems to have some compelling feature over the others I use the one that best fits the project.  I use Base, SQLiteManager, NaviCat, and the FireFox plugin SQLite Manager.  Of these, SQLiteManager and NaviCat can open encrypted databases and only Navicat being able to remember the encryption key.  Navicat also has a visual Query Builder that newbies to SQL might find attractive.

What SQLite tool are you using and why?

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