ARGen 3.0.3

BKeeney Software Inc. is proud to announce an update to ARGen, our ActiveRecord Generator utility for Xojo developers. This minor update includes dark mode support, speed improvements, and important updates for generated projects. Updating to 3.0.3 is recommended for all ARGen users.

ARGen is available for macOS and Windows. It can be used for free in limited mode, and is priced at $99.95 to unlock all features. Existing version 2.x users will automatically be provided an upgrade opportunity when launching version 3.

3.0.3 Release Notes:

Changes:

  • Added Dark Mode support
  • Simplified manual relationship management
  • Selecting a different SQLite database now clears the password field
  • kMaxReturn is now a protected constant for cleaner code
  • DBUpdates module code is now cleaner
  • Improved instructions in some locations
  • Base project templates optimized
  • Preferences module no longer writes to SpecialFolder.Preferences
  • iOS Create Data Sources defaults to true
  • Updated links to Xojo documentation
  • Generated localization module constants are now protected

Fixes:

  • DBUpdates.SetDBVersion no longer uses a BKS extension synonym for str()
  • Fixed return statement for iOS apps using 2018r2
  • Projects with empty name now have default save name
  • BKS Created/Modified overrides no longer generate properties that fail to Register
  • Corrected minor UI bug on Windows
  • Project listing loads faster
  • Speed improvements throughout the software
  • Projects created but never saved are no longer retained when closed
  • Checking for updates at launch now works
  • Preferences window will show the last update check time

Pricing, examples, and more details can be found at the project homepage at https://bkeeney.com/argen/ 

Evaluating A Xojo Consultant

We had an interesting conversation with a prospective client last week.  Well, I should back up and say this isn’t the first time we talked to them as we were a finalist for a project of theirs two years ago.  Turns out their Xojo consultant is having a hard time completing the work and not giving them deliverables in a timely manner.  This is for a project we estimated at less than six months worth of work.

The client is looking for someone that could come in and complete the project for them.  They were hoping that a large majority of the work could be reused.  My response was that this was unlikely for several reasons.  The first being that they used their own kit which means a set of classes, subclasses, and libraries that we are unfamiliar with and for me to use them without serious investigation (i.e. time and money) would be less than ideal.  Plus, if I’m the developer for the project I’m now responsible for it working and I’m taking a gamble on code that I don’t know and haven’t vetted.  

My second thought was wondering if their overall design had been the cause for some of the delay.  Perhaps they coded themselves into a corner.  It’s an awkward conversation to have with a client – especially after they’ve spent a boatload of money.

Regardless, I’m sure our conversation was not what the client was looking for.  They wanted a project savior because their consultants had deceived them in their capabilities and in their ability to deliver.  

We politely asked who their consultant was and they told us.  A brief internet search pulled up the company website and their *one* Xojo related post.  Everything else on their website is hardware and .NET related.  If I was evaluating a Xojo consultant that would give me some pause for concern.  You really want a consultant that does a lot of Xojo projects.  Xojo is not like .NET or Java or any other language.  It has its own set of strengths and weaknesses (just like every other language) and I’ve seen it all too often where a developer tries to make Xojo do what <insert language here> does.  It’s almost always a disaster.

We’ve been using Xojo for over 16 years.  If I’m not friends with most of the Xojo consultants out there I at least know of them and their reputation.  I have lost bids to my competitors and if the prospective client will tell us who they chose I can at least give a thumbs up to those that I know and trust since I know the client will be in good hands.  For those that I don’t know I don’t give any opinion.  But it’s also been my experience that those consultants that stick around are good and are active in the community.

If you’re hiring a Xojo consultant you should always ask for references.  If you’re going to spend tens of thousands of dollars (or more) with them you should do a little homework.  Don’t buy their assurances.  If they are really reliable then they will find references for you to check out.  This will let you know if they do good work, are on time, and on budget.  Are they reliable?  Are they hard to work with?

If you’re new to Xojo consulting that’s okay too – we were all new once.  Be honest with the client and do your best to prove to them that you’re up to the challenge.  

The Xojo community is very accepting and the forum is filled with developers that have provided answers to newbies, provided source code examples, and even established public repositories to share their code.  Have they spoke at any Xojo developers conferences?  Written for Xojo Developer magazine?  This public work can, and should be, part of the reference list.  This shows the prospective client that you really know and work with Xojo.

Hiring a Xojo consultant isn’t that difficult.  There are a number of qualified consultants that will create a great project for you.  The first step is to fill out the form at Find a Developer page on the Xojo website at https://www.xojo.com/resources/consultants.php.  This will get your project in front of everyone looking for work.  Then it’s up to you.

Ask about their Xojo work they’ve done in the public.  Ask for their references.  Heck, ask them what they love and hate about Xojo (that might be a long conversation).  If you don’t do your homework you might be wasting an opportunity in not getting your project to market, or waste tends of thousands of dollars for work that doesn’t get delivered.

XDC Video Sale

The Xojo Developer Conference (XDC) is a fantastic event.  I highly recommend going to one.  The 2019 XDC event this year is May 1st through the 3rd in Miami, Florida.  I look forward to XDC as you will not find a greater density of Xojo developers on the planet!  Everyone there is all-in on Xojo and you can easily talk Xojo non-stop for three full days (or longer).  More details on XDC at https://www.xojo.com/xdc/

At XDC 2015 they started recording the sessions and attendees get these recordings as part of their conference admission.  This was a great addition because it made choosing between sessions a lot easier because you know you could watch it later.  Plus you could rewatch sessions whenever you wanted.

The XDC videos are also for sale after the conference.  They were not inexpensive but certainly cheaper than going to the event.  Xojo recently announced that the XDC videos from 2018 are now available for $99.  The 2016 videos are available for $75.  These are great prices for 64 videos that cover everything from the Keynote by Geoff Perlman, to database topics, to Windows and Linux and iOS and everything in between.  I haven’t totaled up the exact hours but if my rough calculations are correct the two years of videos adds up to over 1900 minutes of video.  I guarantee there’s a topic you’re interested in that is in this list.

I’m so looking forward to XDC 2019.  Not only to escape a fairly brutal winter in the midwest but to reconnect with my friends and talk Xojo non-stop for days on end.  But also to go to sessions that haven’t been done before.  Many of the speakers had done their favorite topics several times and chose different topics (I did this intentionally to get outside my box).  You can find the season list at https://www.xojo.com/xdc/sessions/

As I usually do I’ll do blog posts on the more interesting topics.  I expect to hear more about Web 2.0, Interops, Android, Xojo Plugins and much more.

Hope to you see you at XDC.  If we’ve never met please stop me and introduce yourself and tell me how you ‘know’ me.  Some read the blog and some have bought our Xojo developer products.  I love to hear about your experiences and how I can serve you better.

Updating Records with Transactions

In my last blog post I talked about inserting records into a SQLite database and found that using transactions makes them incredibly fast.  Does the same hold true for updating an existing record?  We’ll find out.

In this set of tests I inserted 100,000 records and then randomly selected 10,000 records to update with our same random name, age, and date values.  In theory this means I have records distributed all over the range of the database.

In the first test we use a simple RecordSet which means we have to query for it first.  Then call its Edit method, change the values and call update.  It’s a two step process.

For i As Integer = 0 To ari.Ubound
   Dim rs As recordset
   rs = db.SQLSelect("Select * from t_table where table_id = " + Str(ari(i)))
   If db.error Then
      If db.Error Then
         MsgBox "DB Error: " + db.ErrorMessage
         Return
      End
   End
   rs.edit
   rs.Field("Name").stringvalue = GetName
   rs.Field("Age").integervalue = GetAge
   rs.Field("TheDate").DateValue = New date
   rs.Update
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage
      Return
   End
Next

Unsurprisingly, this method took about 49 seconds for 10,000 records.

In test two I switched to using a PreparedStatement to update the record.

Dim sql As String = "Update t_table Set Name = ?, Age=?, TheDate =? WHERE table_id = ?"
Dim ps As SQLitePreparedStatement = db.Prepare(sql)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_INT64)
For i As Integer = 0 To ari.Ubound
   ps.Bind(0, GetName)
   ps.bind(1, GetAge)
   ps.Bind(2, New Date)
   ps.bind(3, ari(i) )
   ps.SQLExecute
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage
      Return
   End
Next

When run for 10,000 records this takes about 47 seconds so there is a little bit of time savings by not querying the database for the data to begin with.  It’s also possible that the PreparedStatements aren’t nearly as efficient as one would hope.

I then repeated the same tests within a Transaction.  In both cases updating records effectively took no time at all!  If I increase the number of Updates to 10,000 both the RecordSet update and the PreparedStatement takes about 8 seconds.  

I’m surprised at these results because you would think that doing the query required for the RecordSet would take more time but it’s no different than using the PreparedStatement SQL Update.  Why is that?  I have a couple of theories.  First, the table isn’t very complex with only 4 columns and I’m only ever querying and updating based on the primary key.  I’m sure a more complex query would slow things down.  We also have no indexes on any the columns which can cause the database to do more work behind the scenes.

Regardless, we learned a few things today.  First, database transactions are critical to large scale database manipulation in a timely manner.  Second, it doesn’t matter if you use RecordSet Edit/Update or if you use PreparedStatements as either one is fast enough.

What sorts of questions do you have about databases that I can test for you?

Database Transactions in Xojo

Every now and then someone on the Xojo forum wonders why inserting data into an SQLite database is so slow.  Databases are designed to hold billions of records and do it fast, right?  So why is their application so slow?  The answer is they are relying upon the built-in transaction in Xojo.

By default SQLite databases do an automatic transaction for you.  This means that as soon as you attempt to insert, update, or delete data the work to write that change to disk happens as soon as possible.

For example, let’s take the following bit of code to insert data into a table:

For i As Integer = 1 To kMax
   Dim dbr As New DatabaseRecord
   dbr.Column("Name") = GetName
   dbr.IntegerColumn("Age") = GetAge
   dbr.DateColumn("TheDate") = New date
   db.InsertRecord "t_table", dbr
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage
      Return
   End
Next

GetName gets a random name from an array that has 26 names in it and adds a random integer between 1000 and 10000.  GetAge returns a random integer between 1 and 74. 

This is pretty simple insert and if you run this 10,000 times it takes roughly 49 seconds saving to a desktop SQLite file on my 5k iMac.  And in so doing the application is locked up for that entire time because I’m in the tight loop.  This is simply unacceptable.

I’m sure someone is screaming why are you using the DatabaseRecord!  It’s slow!  It’s inefficient!  You should be using PreparedStatement’s!  Okay, so using pretty much the same logic:

Dim sql As String = "Insert into t_table (Name, Age, TheDate) Values(?, ?, ?);"
Dim ps As SQLitePreparedStatement = db.Prepare(sql)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
For i As Integer = 1 To kMax
   ps.Bind(0, GetName)
   ps.bind(1, GetAge)
   ps.Bind(2, New Date)
   ps.SQLExecute
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage
      Return
   End
Next

This takes about 48 seconds.  No big time savings there.  Obviously that’s not the improvement we need.

What we need to do is put these inserts in a database transaction using the following bit of code before the loop starts:

db.SQLExecute("BEGIN TRANSACTION")
If db.Error Then
   MsgBox "DB Error: " + db.ErrorMessage
   Return
End

And then at the end use:

db.Commit

Using the DatabaseRecord method takes a whopping 1 second with 10,000 records.  Using the prepared statement is so fast that my measured elapsed time in seconds is effectively 0.  

If I up the number of records inserted to 1,000,000 I get an interesting result.  The DatabaseRecord method takes 27 seconds where the PreparedStatement method takes 47 seconds.  And if I declare the PreparedStatement inside the loop it now takes 55 seconds.

What have we learned in this blog post?  First, using a database transaction is considerably faster than using the default transaction behavior.  Second, using DatabaseRecord is pretty fast and depending upon the number of record inserted it might be considerably faster.  Honestly, I didn’t expect this.  

In the next blog post I’ll look at Record updates and what are the best methods.

Formatted Text 3.2

Lenexa, KS (February 12th 2019) — BKeeney Software Inc. releases a major update to Formatted Text Control. Developers can now implement tab leaders, change character spacing, implement text drop shadows, and use standard keyboard handling in their applications that require a robust word processing control. This version contains dozens of new, changed, and updated items.

Formatted Text Control (FTC) is a set of classes that offer developers word processing capabilities for their Mac and Windows desktop applications. FTC has several viewing modes that gives users a choice of Page View, Edit View, or Single Line view. FTC supports inline graphics, hyperlinks, and text formatting that the built-in Xojo TextArea control just can’t do. Users can import and save text, RTF, and XML format documents.

FTC version 3.2.0 is a free update to all 3.x users and is recommended. Existing users should be notified via FileShare but can contact BKeeney Software at support@bkeeney.com if they have any questions.

FTC is sold as unencrypted source code and costs $150 USD. More information and demo projects are available at https://www.bkeeney.com/formatted-text-control/ .

Complete change list:
New Items:

  • Contains code from the ImagePlay Effects Library – http://imageplay.sourceforge.net 
  • Tab Leaders implemented
  • Issue #3832: Implement Control + Up/Down Arrow Key Handling
  • Issue #3833: Implement paragraph moving via keyboard handling
  • Issue #3700: Drop Shadow available on Windows and Linux
  • Issue #3795: The FTDocument properties characterStyles and paragraphStyles need to be accessible to subclasses.

Added CharacterStyles and ParagraphStyles getter/setters so developers can create their own style editor.
* Character Spacing implemented (requires Xojo >= 2015.4)

Bug Fixes:

  • Issue #3650: CustomObjects Demo: Added FTFile.Clone method so it works with FTIterator propertly.
  • Issue #3653: macOS: “getDoubleClickTime” – Cocoa replacement for old CarbonLib call
  • Issue #3699: Add Win64 Declares
  • Issue #3654: Hi-DPI Mode: FTPicture – Handles drawn incorrect and matches wrong.
  • Issue #3702: DragRect is half height in HiDPI
  • Fixed an issue with FTParagraph clones that have a different ID after going through the FTIterator
  • Issue #2007/3605: Candidate Window Shows up in Wrong Location
  • Issue #3646: FTParagraph.getXML saves wrong TabStop Properties
  • Issue #3709: FTPicture ignores Nudge property
  • Issue #3748: FTDocument.selectionBold,Italic, shadow, strikethrough, subscript, superscript, underline now works properly
  • Issue #3752: FTRBScript: Fix for printing line numbers
  • Issue #3763: RTFReader/FtcRtfReader ignored the left/right margin and first indent of paragraphs.
  • Issue #3764: RTFReader ignored “\sb” SpaceBefore of paragraphs.
  • Win64 Issue: Modified FTCUndoPaste.constructor and FTCUndoManager.savePaste to allow pasting (Xojo Compiler Issue. Feedback 53967)
  • Issue #3750: Suggestion & comparison of the Office applications for the display optimization of the paragraph background color
  • Issue #3775: Implement Corner Color Property in Formatted Text (Switch between Light/DarkMode – Xojo Version >= 2018.3)
  • Issue #3777: Implement Page Shadow Property in Formatted Text (Switch between Light/DarkMode – Xojo Version >= 2018.3)
  • Issue #3755: Printing has the side-effect of scrolling the editing window to the top
  • Issue #3794: FTDocument.getSelectedParagraphs returns first paragraph of multi-paragraph doc regardless of insertion point
  • Issue #3796: FTStyleRun.copyStyleData does not copy background color
  • Issue #3820: RTFWriter missed “\par” after the last Paragraph
  • Issue #3774: FormattedText — changeParagraphMargins method name changeParagraphMarigns
  • Issue #3762: FTDocument.selectionFontColor returns sameColor false when it should be true
  • Issue #3842: FTStyleRun.updateWith Style sets textColor black when undefined
  • Issue #3806: FTDocument.addParagraphStyle and addCharacterStyle bypassed by insertXML and cannot be overridden in subclass
  • Issue #3818: Print RB Code Editor: Unwanted positive y-offset of the content compared to its line number
  • Issue #3637: FTCParagraphStyle.backgroundColor won’t set correctly
  • Issue #3910: RTF-Reader ignores local value for first line indent in paragraphs
  • Issue #3914: First Paragraph on Page with Background Color ignores Before Space
  • Issue #3917: Paragraphs Background Color ignores Hanging Indent

Changes:

  • Renamed FormattedText.xDisplayPosition to FormattedText.hScrollValue to better reflect what it is.
  • Renamed FormattedText.lastXDisplayPosition to FormattedText.lastHScrollValue
  • Renamed FormattedText.yDisplayPosition to FormattedText.vScrollValue to better reflect what it is.
  • Renamed FormattedText.lastYDisplayPosition to FormattedText.lastVScrollValue
  • Renamed FormattedText.GetResolution to FormattedText.GetMonitorPixelsPerInch to better reflect what it does.
  • Renamed FTDocument.SetResolution to FTDocument.setMonitorPixelsPerInch
  • Renamed FTDocument.GetResolution to FTDocument.getMonitorPixelsPerInch
  • Renamed FTDocument.Resolution to FTDocument.MonitorPixelsPerInch
  • FormattedText.DISPLAY_ALIGN_CENTER/LEFT/RIGHT replaced with Display_Alignment enum
  • FormattedText.MODE_PAGE/NORMAL/EDIT/Single replaced with Display_Mode enum
  • FTLine.FIRST_LINE/MIDDLE/LAST/BOTH replaced with Line_Type enum
  • FTPicture.Handle constants converted to FTPicture.Handle_Type enum
  • FTParagraph.Alignment constants converted to FTParagraph.Alignment_Type enum
  • Removed Alignment constants from RTFConstants module
  • Changed how hyperlinks are drawn in FTObject.drawHyperLink
  • Changed how the gradient shadow is drawn in FTPage.drawPageViewMode
  • Changed how strikethroughs are drawn in FTObject.drawStrikethrough
  • Changed how underline is drawing in FTObject.drawUnderline
  • Can now read/write shadow properties from/to RTF Documents
  • Added AcceptFileDrop to test window for testing custom objects. Added FTFile into project.
  • Changed how Styles are saved and read in XML format.
  • Issue #3802/1456: Command + (Shift Key) + Arrow Keys doesn’t move insertion point correctly
  • Changed RB Script classes to Xojo Script.
  • Now have TabLeaders (WORK IN PROGRESS)
  • Embedded FTC demo now does text formatting with keyboard shortcuts.
  • Removed unused FTDocument.DocOffset method.
  • Fixed TargetCocoa and TargetWin32 constants with more appropriate ones for 2018 R4.
  • Removed duplicated code line in RTFReader.SetupKnownCommands
  • Updated ParagraphWindow to easy format First Line and Hanging Indents

PARTIAL IMPLEMENATION:
* Issue #3753: PageBreaks won’t load from RTF

BKS Tab Control – Drag Rearrange Tabs!

Lenexa, KS (February 8th 2019) — BKS Tab Control update can now drag rearrange

BKeeney Software releases a minor update for BKS Tab Control enabling users to drag and drop to rearrange tabs. Developers can enable or disable this feature using a switch in the Xojo IDE. A new event, TabOrderChanged, is raised when the user performs a drag-rearrange.

The BKS Tab Control is a set of classes that offer developers a classic “tabs control” for Xojo Desktop applications. The Tab Control can attach to any RectControl or Window, and will maintain a relationship to this parent control if and when it changes size. Tabs can be displayed in one of four directions (North, South, East, West) and individually offer options like a close button, an icon, a background color, and can be disabled.

Other features:
  • Optional CloseBox can be positioned on the left of right
  • If the CloseBox is selected a CancelClose event is fired
  • Optional Icon can be positioned on the left or right
  • Each tab can be independently styled (colors, fonts, text decorations)
  • Each tab can be disabled
  • Tabs that overflow may be accessed with an overflow popup menu
  • Works in HiDPI, non-HiDPI, and dark modes

BKS Tab Control has been tested in macOS, Windows, and several varieties of Linux with Xojo 2018 R4 and back to Xojo 2017 R1. The control may work unchanged in older versions of Xojo.

BKS Tab Control is sold as unencrypted source code and costs $75 USD. Existing customers can download the update with the original link on their sales receipt. More information and a demo project are available at: https://www.bkeeney.com/allproducts/bks-tab-control/

modGtk3 for Xojo

Linux has always been kind of an odd duck when it comes to Xojo.  If you create Linux applications with the IDE running in Linux the default sizes for controls is 26 which is different than MacOS or Windows since their default control height is 22.  See the initial screenshot:  the controls in Linux just don’t look right at the default Mac/Win control height.

The standard answer for many years was to subclass your controls and modify the height of the controls in Linux.  This was okay but it hard to make your UI look good on all three target platforms.

Xojo 2017 R2 changed the drawing system it uses.  The switch to GTK3 made it possible to modify the CSS of your application theoretically making it possible to make all platforms look and behave the same.

Just because you can modify the CSS means it’s a trivial task.  Several long time Xojo community members Jim McKay and Jürg Otter stepped up to the plate and put in the time to figure it out.  The result is at https://bitbucket.org/pidog/modgtk3/src/master/.

In this screenshot you can see that everything looks as you’d expect.

Implementing this in your own project is simple.  Download the BitBucket repository, open the project in Xojo and copy the GTK3 folder into  your own project.  Then in the App.Open event put these three lines in:

modGTK3.initGtkEntryFix
modGTK3.initGtkWidgetHeightFix
modGTK3.InitGlobalGTK3Style

That’s it.  Voila!  Now your app looks better in Linux and there’s no need to subclass your controls.  I realize this isn’t magic but it sure seems like it.

BKS Shorts 2.0.9

Today we released BKeeney Shorts 2.0.9.  This is a free update to all version 2.0.x users.  

Shorts is the award winning reporting tool for Xojo applications.  Shorts allows a Xojo developer to embed a report designer inside in a desktop application, view reports in any resolution on desktop or web applications, save report files to file or to a database, and to export reports to HTML, CSV, and PDF (requires the DynaPDF plugin from MBS).  Shorts works with SQLite, CubeSQL, PostgreSQL, MySQL, MS SQL Server, ODBC, and Informix (requires the SQL plugin from MBS).     Shorts comes as 100% unencrypted Xojo source code.    

New:

  • Align: Justify text! (#3627)
  • Report Designer can now copy and paste! (#3691)
  • Method to tell the DynaPDF renderer where to look for custom fonts
  • Report Designer bands with a Band Script now offer an indication in the designer (#3701)
  • Schema Zapper will remove the DB schema from the JSON for manual template changes (#3632)

Changes:

  • Running in HiDPI in Windows using Xojo 2018 R3 now shows report preview in proper clarity
  • Report Designer no longer nags about deleting items that can be undone
  • Designer window positioning suggested by container event for better scoping
  • Standardized naming on winReportDesigner
  • DesignCanvas.GetUniqueName is now more smart
  • DBWrapper.DatabaseFile is now DBWrapper.SQLiteDatabaseFile for consistency
  • ReportBKS no longer redundantly loads styles to the global styles dictionary
  • PAF_PrintKit.DictStyles is now protected for clarity in code

Fixes:

  • PreviewCC Mojave / 2018R3 & R4 graphical glitch workaround
  • Reports where the page header is empty no longer have multiple page positioning issues
  • Added Numeric and Decimal datatypes for PostgreSQL, maps to Double
  • Views now show up in the Manual Relations editor (#3731)
  • Designer window will no longer reposition if the report template positioning doesn’t fit onscreen (#3651)
  • Fixed localization mismatch in the navigator (#3851)
  • DBWrapper no longer assumes SQLite databases have a database file (#3694)

Removed:

  • PAFConfirm function, please use the modGlobals.Alert function instead
  • winReportDesigner no longer has a Placard control
  • PAF_DatabaseKit.CreateDatabaseFile function, it served no purpose in the Shorts project

For more pricing, demo versions, and training videos please visit http://www.bkeeney.com/allproducts/bkeeney-shorts/

Nil Recordset

The question on how to deal with nil RecordSets is OFTEN asked in the Xojo forums.  Either the original poster or a well meaning responder will do something like this:

dim rs as recordset 
rs = db.sqlselect(sql)
If rs <> Nil Then
     If Not rs.EOF Then
       // No Records in Table or bad querry?
     End If
End If

It works, but it only masks the real issue.  Why is rs nil in the first place?  The answer is because the SQL statement to get the RecordSet had an error!  What is that error?  We have no idea because the code doesn’t ask for the error.

Most of the time the database will give you an error message that will point you in the right direction.  How do you tell that the database had an error?  You check the Error property.

dim rs as recordset 
rs = db.sqlselect(sql)
if db.error then
     //Do something
end


The database can give you an error code but this varies from database to database so I don’t find it exceptionally useful.  However, there is an ErrorMessage property that gives you a human readable message.  

dim rs as recordset 
rs = db.sqlselect(sql)
if db.error then
     MsgBox “DB Error: “ + db.ErrorMessage
     Return
end


In this example I’m showing this to the user but this is probably not a great idea for most uses because you don’t want the average user to know what happened.  However, it’s great while debugging.

The error you get back might be a little mysterious.  SQL is a language specific to databases and the error message returned expects you know a little bit about databases.

The second part of my message to you is that if you’re using SQLSelect or SQLExecute in your code you should look at PreparedStatements.  They’re safer in that PreparedStatement have some security features and does the heavy lifting of taking the input data and putting it into the right format.  What do I mean by this?

In a normal SQL statement you would have an SQL statement like this:

dim sql as string
sql = “Select * from users where last_name = ‘Keeney’;”


This selects all users whose last name is ‘Keeney’.  If you were going to allow the user to search for whatever name they want you would end up doing something like this:

dim sName as string
sName = txtSearchField.Text
sql = “Select * from users where last_name = '“ + sName + “’;”


Note that I had to manually put the single quotes in before and after the name since the database column is text.  This works great until you enter an Irish name like O’Neil.  The database will choke on this because it has an extra apostrophe in the statement.  You can escape this data yourself but what a pain.  Instead, use a PreparedStatement.

dim ps as SQLitePreparedStatement
dim sql as string
sql = “Select * from users where last_name = ?”
ps = db.prepare(sql)
ps.bindtype(0, SQLitePreparedStatement.SQLITE_TEXT
ps.bind(0, txtSearch.text)
dim rs as Recordset
rs = ps.SQLSelect
if db.error then
     MsgBox db.errormessage
     return
end


Nowhere in the code do I have to put in apostrophe’s or escape any data.  It’s just handled and this is true if you have database columns that are text, date, or numeric.

PreparedStatements have a number of advantages besides the automatic formatting.  They help with security and SQL Injection attacks.  If you don’t know what those are just do a web search to learn more.  You can reuse PreparedStatements in code but in my testing it doesn’t save much time as it appears as if the database plugin saves them internally.  Regardless, there are so many reasons to be using PreparedStatements over straight SQL.

To recap:  A nil recordset always mean a database error has happened.  You need to check for for the error after every operation whether that be a Select, Insert, Update, or Delete statement.  Basically after every SQLSelect and SQLExecute statement.  Ideally you want to start moving all of your code to use PreparedStatements because of all of its advantages.