Ovo je kod za pristupanje Firebirdu 3.0 iz MS Office VBA. Koristim obični Firebird_ODBC driver sa ove stranice
https://www.firebirdsql.org/en/odbc-driver/ . Firebird baza koristi UTF8 enkoding.
Prvo je potrebna funkcija koja pretvara slova ukucana u VBA IDE u UTF8. Ona nije neophodna ako nemamo kukasta slova, ali za upis [čćžđš] moramo je koristiti.
Code:
Public Function ADO_EncodeUTF8(ByVal strStringToEncode As String) As String
Dim objStream As Object
Dim data() As Byte
Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8"
objStream.Mode = adModeReadWrite
objStream.Type = adTypeText
objStream.Open
objStream.WriteText strStringToEncode
objStream.Flush
objStream.Position = 0
objStream.Type = adTypeBinary
objStream.Read 3 ' skip BOM
data = objStream.Read()
objStream.Close
ADO_EncodeUTF8 = StrConv(data, vbUnicode)
End Function
Ovo je funkcija za čitanje podataka. Pretpostavimo da u bazi postoji tabela "Firebird" sa kolonom "Firebird" koja je VARCHAR(100) formata. U njoj neka postoji barem jedan red sa podatkom "Pera čćžšđ".
Code:
Sub readFromFBird()
Dim conn As ADODB.Connection, rst As ADODB.Recordset
Dim row As Long
Set conn = New ADODB.Connection
conn.Open "DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=fff;DBNAME=C:\Users\Pegazus\Desktop\EEE2.FDB"
Set rst = New ADODB.Recordset
strEncoded = ADO_EncodeUTF8("Pera čćžšđ")
rst.Open "Select * From Firebird Where Firebird = '" & strEncoded & "';", conn, adOpenDynamic, adLockOptimistic
Do While Not rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
End Sub
Ovo je funkcija za upisivanje podataka. Opet upisujemo u tabelu Firebird, u kolonu Firebird.
Code:
Sub writeToFBird()
Dim conn As ADODB.Connection, rst As ADODB.Recordset
Dim row As Long
Set conn = New ADODB.Connection
conn.Open "DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=fff;DBNAME=C:\Users\Pegazus\Desktop\EEE2.FDB"
Set rst = New ADODB.Recordset
strEncoded = ADO_EncodeUTF8("Sima čćžđš")
strSQL = "INSERT INTO Firebird ( Firebird ) VALUES ( '" & strEncoded & "');"
conn.Execute strSQL
End Sub
Nikada ne enkodirajte čitav SQL izraz ( SELECT * FROM ... ). Tada neće da radi, dobija se "malformed string" greška. Funkciju "ADO_EncodeUTF8" koristiti samo za podatke iz tabela ("Pera čćžšđ").
Regional settings podešavanja Windowsa nemaju nikakav uticaj.