finally I did the move to use Xojo. I try to use Firebird with MBS SQL Plugin. Working well. Even via SSH connection.
The problem I am running into is that DECIMAL types loaded in RS are not displayed correctly. The Amount “2.2” is display “2” as integer. I checked RS.NativeValue as well and it returns “2” instead of “2.2.”
SQL via DBeaver shows “2.2” correctly.
Below the code and table SQL.
Any suggestions?
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(row.Column("ID").Int64Value.ToString)
System.DebugLog(row.Column("CKEY").StringValue)
System.DebugLog(row.Column("CVALUE").StringValue)
Var value As Variant = row.Column("AMOUNT").Value
System.DebugLog(row.Column("AMOUNT").Value.CurrencyValue.ToString)
System.DebugLog(row.Column("CHANGED_AT").DateTimeValue.ToString)
System.DebugLog("-------")
Next
end if
CREATE TABLE TEST (
ID BIGINT NOT NULL,
CKEY INTEGER DEFAULT 0 NOT NULL,
CVALUE VARCHAR(100) NOT NULL,
AMOUNT DECIMAL(18,5) NOT NULL,
CHANGED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT INTEG_6 PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX RDB$PRIMARY1 ON TEST (ID);
What’s your system locale? Maybe a bug in the MBS plugin not normalizing the data to a correct expected value to be sent to Xojo, or Xojo not handling correctly a correct data received. It’s usually expected that at a DB level the decimal separator is fixed as a dot (".") and at the presentation level it can change by user level operations taking locale in consideration.
Also, why are-you asking a String to change that to a DOUBLE ?
At last, make one statement / a line and check the result
something like:
Var cur As Currency
dbData = row.Column("AMOUNT")
// Set the variable type change in this line
cur = Currency.ToString // Use the correct code here too
Looking on source code of the SQL library, it seems like we only report Int64, if we get Int64.
Your decimal should come through as numeric, which may end up as double for the .value.
@Christian_Schmitz : seems to be a problem of the underlying SQL Library…
I altered the table (DDL):
CREATE TABLE TEST (
ID BIGINT NOT NULL,
CKEY INTEGER DEFAULT 0 NOT NULL,
CVALUE VARCHAR(100) NOT NULL,
AMOUNT DECIMAL(18,5) NOT NULL,
CHANGED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
CURRENCY DOUBLE PRECISION, // added values doubled
CONSTRAINT INTEG_6 PRIMARY KEY (ID)
);
I created a new “CURRENCY” column of type DOUBLE PRECISION and doubled the values of the “AMOUNT” column - and voilá data reading is correct now. It seems that Firebird 3.x DECIMAL datatype is passed as Int64 rather than floating-point number (DECIMAL) for Xojo. JAVA Driver used by DBeaver shows DECIMAL data correctly (see former Screenshot). DECIMAL is offically supported by Firebird 3.x., See here
In fact: data mapping DECIMAL reading from Firebird 3.x is not correct. Hope to you help with this information.
For now I can change datatype to DOUBLE or FLOAT (Firebird 3.x) converting it to CURRENCY (Xojo) and work with it.
AMOUNT as DOUBLE: 2.000000 ' Should be 2.2
AMOUNT Data Type: 19 ' Int64
CURRENCY as DOUBLE: 2.200000 ' Correct
CURRENCY Data Type: 7 ' Double
Firebird DECIMAL Type value is “2.2”
Code lines:
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
AMOUNT is reported an Int64 by row.Column().Type but apprently not returned as DOUBLE value (only integer-part is passed through) “2” instead of “2.2” (see above showed code lines)
Things are weird here.
I would prefer Christian installing, using, creating such column and value on the supported Firebird DB, and testing the new MBS plugin (read/write) by himself to exclude user error, and fixing it (if not already done) based on what will be discovered.
Well, this is a long and scary weekend. It’s state holiday today.
I was happy to quickly identify a problem 2 days ago, which may be the issue he run into. So outside of regular beta builds, I’ll sent Andreas a new build, but I am not sure whether he runs the new version.
Tomorrow, pr5 will be uploaded, so he can try it again. Also I asked my SQL coder to verify the issue.
If that doesn’t help, I can install a Firebird this week to check it myself.
I replaced the Plugin using Christian’s rebuild for the last test performed this morning. And I did not change test conditions. I am able to drop source codes anywhere for testing purpose (github, bitbucket or whatever you prefer).
That’s unnecessary. All Christian needs for a test is a basic table with the problematic data type and previous messed value and from there he can prove that it’s reading 2.2 in a Double and Currency column values in Xojo as expected (or finding some mysterious bug):
CREATE TABLE TEST (
ID BIGINT NOT NULL,
AMOUNT DECIMAL(18,5) NOT NULL
);
INSERT INTO TEST (ID, AMOUNT) VALUES (1, 2.2);
