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.