Firebird 3.x DECIMAL Type

[Andreas_Ahlen]

For completion. Plugin used:

Name: MBS Xojo SQL Plugin
Date: 2021-10-30 13:59:15
Version: 21.5

The one @Christian_Schmitz provided.

[Christian_Schmitz]

Works here:

I’ll upload pr5 later, so you can try that version.

[Andreas_Ahlen]

Please find below my way (Desktop UI App) to connect to Firebird and run a prepared statement. My approach and execution is slightly differenr from the way @Christian_Schmitz used perfomed his test.

I use RowSet with SQLPreparedStatementMBS instead of RecordSet.

Is there any difference in using db classes XOJO / MBS

— BEGIN CODE —

Dim db As New SQLDatabaseMBS

Try
  
  // where is the library?
  
  If TargetWin32 Then
    db.Option(SQLConnectionMBS.kOptionLibraryFirebird) = "fbclient.dll"
  ElseIf TargetLinux Then
    db.Option(SQLConnectionMBS.kOptionLibraryFirebird) = "libfbclient.so"
  ElseIf TargetMachO Then
    db.Option(SQLConnectionMBS.kOptionLibraryFirebird) = "libfbclient.dylib"
  Else
    MsgBox "Platform not supported."
    Quit
  End If
  
  // connect to database
  // in this example it is Firebird/InterBase,
  // but can also be Sybase, Informix, DB2, MySQL
  // SQLServer, SQLBase and ODBC
  
  // Read more here: http://www.sqlapi.com/ServerSpecific/InterBase.html
  
  // For local databases, this can be a file name.
  // To connect to a database on a remote server using TCP/IP the syntax is <server name>:<filename>.
  // To connect to a database on a remote server using NetBEUI, the syntax is \\<server name>\<filename>.
  // To connect to a database on a remote server using SPX, the syntax is <server name>@<filename>.
  
  
  
  Dim client As Integer = -1
  Select Case conn.ConnectionType
  Case RDBMS.eRDBMSType.FIREBIRD
    client = SQLConnectionMBS.kFirebirdClient
    db.Client = SQLConnectionMBS.kFirebirdClient
  End Select
  
  // con.Connect(conn.ConnectionString, conn.Username, conn.Password, SQLConnectionMBS.kFirebirdClient)
  
  db.UserName = conn.Username
  db.Password = conn.Password
  db.DatabaseName = conn.ConnectionString
  
  
  If db.Connect Then
    
    MsgBox "We are connected!"
    
    // but here we need to cast as this database has no character set defined for the tables
    Dim sql As String = "SELECT * FROM test AS t WHERE .ID > :1"
    
    Dim r As SQLPreparedStatementMBS = db.Prepare("SELECT * FROM test AS t WHERE t.ID > :1")
    
    Dim rs As RowSet = r.SelectSQL(1)
    
    If rs <> Nil Then
      System.DebugLog("-------")
      For Each row As DatabaseRow In rs
        System.DebugLog("AMOUNT as DOUBLE: " + row.Column("AMOUNT").DoubleValue.ToString)
        System.DebugLog("AMOUNT Data Type: " + row.Column("AMOUNT").Type.ToString)
        System.DebugLog("CURRENCY as DOUBLE: " + row.Column("CURRENCY").DoubleValue.ToString)
        System.DebugLog("CURRENCY Data Type: " + row.Column("CURRENCY").Type.ToString)
        System.DebugLog("-------")
      Next
    End If
    
    db.Close
    
    MsgBox "We are disconnected!"
  End If
  
Catch r As RuntimeException
  MsgBox r.message
  
  // SAConnection::Rollback()
  // can also throw an exception
  // (if a network error for example),
  // we will be ready
  Try
    
    // on error rollback changes
    // con.Rollback
    db.RollbackTransaction
    
  Catch rr As runtimeexception
    MsgBox rr.message
  End Try
End Try

— END CODE —

[Christian_Schmitz]

Well, I first wait for the upload of pr5 later today.
Then you can try that plugin.

I am always unsure whether I recompiled everything properly, uploaded the right file and you installed it correctly.

[Steve_Wilson]

Firebird stores and returns Decimals as INT64. The field’s metadata returned via FB’s client api includes a “scale” figure which tells you how many digits there are to the right of the decimal point. It’s the job of any wrapper to use that info to return the appropriate value.

Firebird has a very nice feature in that the database engine is also built into the client library (fbclient.dll) which means scaling your application from embedded to client / server is just a matter of changing the connection string passed to the client library.

[Christian_Schmitz]

new plugin uploaded: MBS Xojo Plugins, version 21.5pr5

[Andreas_Ahlen]

Nice done. Now everything turns. Thank you very much, guys.

Thanks @Christian_Schmitz for his excellent support

System.DebugLog Output:

1:12:47 PM : -------
             AMOUNT as DOUBLE: 2.2
             AMOUNT Data Type: 7
             CURRENCY as DOUBLE: 2.200000
             CURRENCY Data Type: 7
1:13:46 PM : -------
             AMOUNT as DOUBLE: 4.1
             AMOUNT Data Type: 7
             CURRENCY as DOUBLE: 4.100000
             CURRENCY Data Type: 7
             -------
             AMOUNT as DOUBLE: 2.009
             AMOUNT Data Type: 7
             CURRENCY as DOUBLE: 2.009000
             CURRENCY Data Type: 7
             -------
             AMOUNT as DOUBLE: 0
             AMOUNT Data Type: 7
             CURRENCY as DOUBLE: 0.000000
             CURRENCY Data Type: 7
             -------
             AMOUNT as DOUBLE: 0
             AMOUNT Data Type: 7
             CURRENCY as DOUBLE: 0.000000
             CURRENCY Data Type: 7
             -------
             AMOUNT as DOUBLE: 0
             AMOUNT Data Type: 7
             CURRENCY as DOUBLE: 0.000000
             CURRENCY Data Type: 7
             -------
             AMOUNT as DOUBLE: 0
             AMOUNT Data Type: 7
             CURRENCY as DOUBLE: 0.000000
             CURRENCY Data Type: 7
             -------
             AMOUNT as DOUBLE: 0
             AMOUNT Data Type: 7
             CURRENCY as DOUBLE: 0.000000
             CURRENCY Data Type: 7
             -------

[Christian_Schmitz]

Thanks for finding this issue.