How to get the long integer from ResultSet of PreparedStatement without data loss (scientific format)

miinun · November 11, 2023, 2:07am

Hello!
I have a connection to an external datasource (Oracle DataBase) via ODBC driver in Calc.

oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
sURL$ = "sdbc:odbc:proddb"
AppendProperty(oParams(), "user", sUser)
AppendProperty(oParams(), "password", sPass)
AppendProperty(oParams(), "JavaDriverClass", "com.sun.star.comp.sdbc.ODBCDriver")
oCon = oManager.getConnectionWithInfo(sURL, oParams())

To get results from DB, I execute a prepared statement:

sSqlString = "select t.nmbr n, sum(t.amnt1) g4, sum(t.amnt2) g5, sum(t.amnt3) g6, sum(t.amnt4) g7, sum(t.amnt5) g8, sum(t.amnt6) g9, sum(t.amnt7) g10, sum(t.amnt8) g11
from table t
where ..."
oSqlStatement = oCon.prepareStatement(sSqlString)
For i = 1 To UBound(ParamList)
    setVar(oSqlStatement, i, ParamList(i))
Next i
oResult = oSqlStatement.executeQuery()

The result includes sums of big numbers, so they are expected to be long integers (8+ digits).
And then when I try to get values from my ResultSet, they end up being in scientific format with only 8 significant digits, so there is data loss on 9+ digits.

e.g., the value ‘150 150 567’ is formatted to ‘1.5015056E+008’ so when I call oResult.getLong(), it returns ‘150 150 560’ with the last ‘7’ being rounded to ‘0’.

Is there a way to turn off the scientific format of numbers upon execution of a prepared statement or somehow force it to change the datatype of column to string/varchar?
Or maybe a way other than PreparedStatement to get values from the SQL query to DB via connection?

ms777 · November 14, 2023, 9:42pm

Hi,

not sure if I can help here, as I just can test on a MariaDB database. Maybe you change the driver and url to your values and see what you get with your database.
I could not observe the truncation problem as you did.

Good luck,

ms777

Sub Main
    oDriverManager = CreateUnoService("com.sun.star.sdbc.DriverManager") 

    Dim info(2) as new com.sun.star.beans.PropertyValue
    info(0).Name = "User"
    info(0).Value = ""
    info(1).Name = "Password"
    info(1).Value = ""
    info(2).Name = "JavaDriverClass"
    info(2).Value = "com.mysql.jdbc.Driver"

    sUrl = "sdbc:mysql:jdbc:192.168.999.99:3307/test" 
    oDriver = oDriverManager.getDriverByUrl(sUrl)

    oConnection = oDriver.connect(sUrl, info)
    
    oStatement = oConnection.createStatement()
    oStatement.setPropertyValue("ResultSetType", com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE)
	oStatement.setPropertyValue("ResultSetConcurrency", com.sun.star.sdbc.ResultSetConcurrency.UPDATABLE)
  
  	oStatement.execute("DROP TABLE if exists blobtable;")
  	oStatement.execute("CREATE TABLE blobtable (blobcol BIGINT UNSIGNED);")

'insert a 12-digit number
  	oStatement.execute("INSERT INTO blobtable VALUES(123456789123);")
'insert a 2nd 12-digit number
  	oStatement.execute("INSERT INTO blobtable VALUES(876543210876);")
  	
  	
'now read the data back ...
  	oResult = oStatement.executeQuery( "select sum(blobcol) from blobtable")

 	oResult.next()
  	lSum = oResult.getLong(1)
  	
  	oStatement.close()
  	oConnection.close()

	msgbox lSum
End Sub

miinun · November 15, 2023, 1:34am

Hi, yeah, you are right.
It appears that there is a problem with the way ODBC Driver treats values of NUMBER datatype when no precision is explicitly specified.
So, if I understand correctly, there are three ways to solve this:

By specifying precision of column upon table declaration
column_name NUMBER (precision, scale)
By changing sql query and specifying precision in select via CAST
select CAST(sum(t.amnt1) As NUMBER(10)) from table t where...
As you suggested - by changing driver and url in LibreOffice.

In my case, changing tables or query would give me a hard time, so i changed driver to ADO with url: sdbc:ado:DSN=proddb on Windows, on Unix-system I’m using JDBC to connect.