Here is a free open source wrapper for the Firebird client library, you're free to use it in any way you wish as long as you understand it's at your own risk :)
It's suitable for use with Firebird server >=2.5, older versions should work but may have issues with encodings. From version 2.5 onwards Firebird handles transliteration between the string encoding of client applications (stated on connection) and the character set of the database they are connected to and I've made use of that. The download includes a single window app which demonstates running a few tests against the employee example database that you can install with Firebird's installer, you just need to set an alias for it and/or edit the onclick handler of the connect button so it matches your set up.
I believe this should give Xojo desktop the ability to work with a client server database (something you usually only get with Pro+) and I also believe from Firebird 3 onwards the client library also contains the database engine so going from embedded to client server is only a matter of changing the connection string.
So far I've only tested it on Win32, I tried downloading the latest version of Xojo but it seems debugging is disabled for 64bit and I can't build 64bit with my license which is for an older version :(
I have implemented a significant subset of the Xojo standard database api so the standard stuff like db.SQLSelect, db.SQLExecute and rs.MoveNext should all work as expected. There are a few limitations, e.g. you can only move forwards through a Recordset, i.e. there's no MoveFirst, MoveLast or MovePrevious and RecordCount always returns -1.
Having pointed out some of the features missing compared to the Xojo standard api there are some additions too:
Working with parameters is as simple as passing in an array of variants. Firebird returns the types on Prepare so there's no need to specify them:
Dim v() As Variant v.Append("Must be literate, numerate and have a high threshold of boredom") v.Append("Admin") v.Append("England") Dim s As String = "UPDATE JOB SET JOB_REQUIREMENT = ? WHERE JOB_CODE = ? AND JOB_COUNTRY = ?;" If Not fb.SQLExecute2(s,v) Then // fb is an instance of FirebirdDatabase MsgBox fb.ErrorMessage fb.Rollback Else fb.Commit End If
As well as navigating through a recordset with Xojo's standard...
While Not rs.EOF rs.MoveNext Wend rs.Close
you can do this...
While rs.NextRecord Wend rs.Close
Database.InsertRecord works slightly differently, it takes a dictionary for the fields:
Dim d As New Dictionary d.Value("COUNTRY") = "China" d.Value("CURRENCY") = "Renminbi" If Not fb.InsertRecord("COUNTRY",d) MsgBox fb.ErrorMessage fb.Rollback Else fb.Commit End If
There's also similar functions for Updates and Deletions.
As an alternative to checking for an error after each update you can have the database raise an exception if an error occurs:
fb.RaiseExceptions = True Dim d As New Dictionary d.Value("COUNTRY") = "China" d.Value("CURRENCY") = "Renminbi" Call fb.InsertRecord("COUNTRY",d) fb.Commit MsgBox "Updated" Exception e As FirebirdException MsgBox e.Message If fb.DefaultTransaction.Depth > 0 Then fb.Rollback
The fields you get back from a FirebirdRecordset include additional members that you don't get in Xojo's standard api:
rs.IdxField(c).Name rs.IdxField(c).Type // Firebird's type, use rs.ColumnType(c) for the Xojo equivalent rs.IdxField(c).SubType rs.IdxField(c).Size rs.IdxField(c).Scale rs.IdxField(c).Value // this is a variant rs.IdxField(c).IsNull
Text BLOBS work just like VARCHARS, you don't have to do anything different. I have implemented Binary BLOBs (even though I hate binary BLOBs in databases) and you work with them like this...
To insert/update a binary BLOB:
Dim s As String = "UPDATE JOB SET JOB_DESCRIPTION = ? WHERE JOB_CODE = ?;" // I had to add the JOB_DESCRIPTION field Dim v() As Variant v.Append("C:\Temp\test.pdf") // to insert a binary blob specify a native path to a file v.Append("VP") Call fb.SQLExecute2(s,v)
When you retrieve a binary BLOB a file will be written to your Temp folder and the field's string value will contain the path to that file. n.b. It will have the file suffix *.bin so you will have to know the file type and rename it accordingly.
I should point out the included app is the only testing it's had so far so any feedback is welcome.