Firebird - free open source client wrapper

  1. 12 months ago
    Edited 12 months ago

    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.

  2. 2 weeks ago

    Heiko R

    Feb 12 Pre-Release Testers, Xojo Pro

    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!

or Sign Up to reply!