Firebird - free open source client wrapper

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

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

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.

Hello !
I was just trying your wrapper for fbclient. When I click the “Connect”-Button i run into an error, as the method isc_attach_database(xxx) is not found. I could not find it in the whole project. Any idea ? Would be nice if I could get
the wrapper working…looks interesting!