Recently I needed to update an old Web project that used a Microsoft SQL Server Database as its data source. This application is running as a service on a Windows machine and is for internal use only. I decided when updating the project, I would also update to API 2.0 database commands using Xojo 2019 R3.2 and I would like to share some of those code changes with you.
In API 2.0, SQLSelect became SelectSQL and SQLExecute became ExecuteSQL. Additionally API 2.0 automatically uses prepared statements. Now, while they perform much the same actions, there are enhancements which make code much simpler to create and read. This is because they implement Prepared Statements under the hood.
For Example, this API 1.0 code:
Dim ps As PreparedSQLStatement
Dim rs As RecordSet
ps = Session.db.Prepare("SELECT * FROM users WHERE emailaddress = ?;")
ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.Bind(0, "wayne@axisdirect.co.nz")
rs = ps.SQLSelect()
Session.UserID = rs.Field("id").IntegerValue
Becomes:
Var rs As RowSet
rs = Session.db.SelectSQL("SELECT * FROM users WHERE emailaddress = ?;" _
,"wayne@axisdirect.co.nz")
Session.UserID = rs.Column("id").IntegerValue
Seven lines of code become just four and in my opinion the second is much easier to follow. A prepared statement is not required (as it is implemented under the hood), and the bind type is automatic.
That SELECT statement does not have many bindings, but here is an INSERT statement that does – API 1.0 code
Dim ps As PreparedSQLStatement
ps = Session.db.Prepare("INSERT INTO reports (title, scheduletype, dayofmonth, dayofweek, sunday, monday, tuesday, wednesday, thursday, friday, saturday, nextrun, reportfile, deliverymethod, suspended) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(1, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
ps.BindType(2, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
ps.BindType(3, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
ps.BindType(4, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TINYINT)
ps.BindType(5, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TINYINT)
ps.BindType(6, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TINYINT)
ps.BindType(7, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TINYINT)
ps.BindType(8, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TINYINT)
ps.BindType(9, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TINYINT)
ps.BindType(10, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TINYINT)
ps.BindType(11, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING) ' This is a datetime value, but we're going to bind a SQLDateTime String
ps.BindType(12, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(13, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
ps.BindType(14, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_TINYINT)
ps.Bind(0, TitleField.Text)
ps.Bind(1, ScheduleTypeMenu. ListIndex)
ps.Bind(2, If(ScheduleTypeMenu. ListIndex = 2, Val(DayOfMonthField.Text), 0)
ps.Bind(3, If(ScheduleTypeMenu. ListIndex = 1, Val(DayOfWeekField.Text), 0)
ps.Bind(4, If(WeekDay(0).Value, 1, 0)
ps.Bind(5, If(WeekDay(1).Value, 1, 0)
ps.Bind(6, If(WeekDay(2).Value, 1, 0)
ps.Bind(7, If(WeekDay(3).Value, 1, 0)
ps.Bind(8, If(WeekDay(4).Value, 1, 0)
ps.Bind(9, If(WeekDay(5).Value, 1, 0)
ps.Bind(10, If(WeekDay(6).Value, 1, 0)
ps.Bind(11, NextRunField.DateValue.SQLDateTime)
ps.Bind(12, ReportFileField.Text)
ps.Bind(13, DeliveryMethodMenu.SelectedIndex)
ps.Bind(14, 1)
ps.SQLExecute()
If Session.db.Error Then
Break ' The insert statement failed for some reason - this should only happen during debugging.
End If
Becomes:
Var sql As String sql = "INSERT INTO reports (title, scheduletype, dayofmonth, dayofweek, sunday, monday, tuesday, wednesday, thursday, friday, saturday, nextrun, reportfile, deliverymethod, suspended) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" Try Session.db.ExecuteSQL(sql, _ TitleField.Value, _ ScheduleTypeMenu.ListIndex, _ If(ScheduleTypeMenu.ListIndex = 2, Val(DayOfMonthField.Text), 0), _ If(ScheduleTypeMenu.ListIndex = 1, Val(DayOfWeekField.Text), 0), _ If(WeekDay(0).Value, 1, 0), _ If(WeekDay(1).Value, 1, 0), _ If(WeekDay(2).Value, 1, 0), _ If(WeekDay(3).Value, 1, 0), _ If(WeekDay(4).Value, 1, 0), _ If(WeekDay(5).Value, 1, 0), _ If(WeekDay(6).Value, 1, 0), _ NextRunField.DateValue.SQLDateTime, _ ReportFileField.Value, _ DeliveryMethodMenu.ListIndex, _ 1) Catch Err As DatabaseException Break ' The insert statement failed for some reason - this should only happen during debugging. End Try
Again, a huge reduction in coding and a more readable result. You will also notice the use of a try/catch block. API 2.0 methods raise exceptions and during debugging these are invaluable as they break on the exception rather than later when your logic bug bites 😊
The API 2.0 SelectSQL method also returns a RowSet rather than a RecordSet. The RowSet is iterable unlike the RecordSet and that makes it much more user friendly. I can’t tell you how many times I’ve run a project only to find it just sits there using all the CPU cycles it can because I forgot to call the MoveNext method.
I understand that many people using Xojo on Windows probably aren’t targeting MS SQL Server, but I believe this is changing. And as more developers target Windows and MS SQL Server, the fixes and improvements that come with API 2.0 will make this easier for them. I know they are making my life a lot easier.
Oh, and by the way, before this update this application would start with a memory usage of about 100MB and grow to >500MB over the week and then I would restart the service. Updating this project to API 2.0 resulted in ongoing memory usage of between 7.5 and 8.5MB for the running application. I suspect this is a combination of my improved coding and improvements under the hood by Xojo.
Wayne Golding has been a Xojo developer since 2005 and is a Xojo MVP. He operates the IT Company Axis Direct Ltd which primarily develops applications using Xojo that integrate with Xero www.xero.com. Wayne’s hobby is robotics where he uses Xojo to build applications for his Raspberry Pi, often implementing IoT for remote control.