Firebird - numeric & decimal

[Lionel Elie Mamane]

Here's some example code to test this in Basic:

Sub Tst
	dim stmt as object
	stmt=ThisDatabaseDocument.currentController.ActiveConnection.createStatement()

	dim rs as object
	rs = stmt.executeQuery("SELECT * FROM ""Table1""")
	dim rsmd as object
	rsmd = rs.getMetaData()
	dim i as integer
	for i = 1 to rsmd.getColumnCount()
		MsgBox "Driver says column " & i & " has type " & rsmd.getColumnType(i)
	next i

End Sub

On Mon, Oct 31, 2016 at 05:53:17PM +0100, Lionel Elie Mamane wrote:
> On Mon, Oct 31, 2016 at 04:42:12PM +0100, Bunth Tamás wrote:
> 
> > I tried to solve bug 74172
> > https://bugs.documentfoundation.org/show_bug.cgi?id=74172
> > but I got stuck:
> 
> > In case of setting a numeric or decimal number the LO code calls the
> > prepared statement's setInt function, which has only an integer
> > value as parameter, but no scale.
> 
> My guess is that the driver tells the rest of the LibreOffice code
> that this is an integer column. You updated getColumnTypeName in
> ResultSetMetadata.cxx, but not getColumnType, which still calls
> getColumnTypeFromFBType without passing a subtype. My guess is that
> this is the problem.
> 
> When developing a driver, I find it convenient to test it with Macro
> (Basic or Python or whatever) code rather than with "all the rest of
> LibreOffice". Just make a statement (or prepartedstatement) object,
> execute SQL code on it, get the resulting resultset object and test
> how it behaves by calling member functions of it. In this case, to
> look that getColumType() returns the right value for DECIMAL and
> NUMERIC columns.
> 
> If the above is not the problem, then hmm... How do you set the
> numeric or decimal number? With an UI element (which one), or with
> some macro code? You type a number with digits after the decimal
> point, right? Something like "1.5", not "15"?
> 
> If yes, then, you know how to use gdb? Then set a breakpoint on the
> firebird driver's setInt. You should have a backtrace a bit like:
> 
> #23 0x00007ff068cf6b09 in dbaccess::OCacheSet::setParameter (nPos=nPos at entry=1, 
>     _xParameter=uno::Reference to (dbaccess::OPreparedStatement *) 0x3eb5270, _rValue=..., _nType=_nType at entry=3, 
>     _nScale=_nScale at entry=4)
>     at libreoffice-5-2/dbaccess/source/core/api/CacheSet.cxx:382
> #24 0x00007ff068d391d7 in dbaccess::OKeySet::executeUpdate (this=this at entry=0x3fb80b0, 
>     _rInsertRow=rtl::Reference to 0x3e91ea0, _rOriginalRow=rtl::Reference to 0x3b6fce0, 
>     i_sSQL="UPDATE \"Table1\" SET \"dfdf\" = ? WHERE \"id\" = ?", i_sTableName="", 
>     _aIndexColumnPositions=std::__debug::vector of length 0, capacity 0)
>     at libreoffice-5-2/dbaccess/source/core/api/KeySet.cxx:581
> #25 0x00007ff068d389e0 in dbaccess::OKeySet::updateRow (this=0x3fb80b0, _rInsertRow=rtl::Reference to 0x3e91ea0, 
>     _rOriginalRow=rtl::Reference to 0x3b6fce0, _xTable=uno::Reference to (dbaccess::ODBTableDecorator *) 0x3fface0)
>     at libreoffice-5-2/dbaccess/source/core/api/KeySet.cxx:552
> #26 0x00007ff068ddb0cb in dbaccess::ORowSetCache::updateRow (this=0x3ff79c0, _rUpdateRow=rtl::Reference to 0x3e91ea0, 
>     o_aBookmarks=std::__debug::vector of length 0, capacity 0)
>     at libreoffice-5-2/dbaccess/source/core/api/RowSetCache.cxx:1353
> #27 0x00007ff068d9cf52 in dbaccess::ORowSet::updateRow (this=0x3e2c180)
>     at libreoffice-5-2/dbaccess/source/core/api/RowSet.cxx:967
> #28 0x00007ff030990fd7 in frm::ODatabaseForm::updateRow (this=0x3e2b520)
>     at libreoffice-5-2/forms/source/component/DatabaseForm.cxx:3470
> 
> What is the value of _nType? Is it some value that indicates an
> integer? (like 4, 5, -6, -5) Or is it 2 or 3 (for NUMERIC,
> respectively DECIMAL)?
> 
> See offapi/com/sun/star/sdbc/DataType.idl in the source code, or
> http://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sdbc_1_1DataType.html
> for the meaning of those values.
> 
> > Another problem is that I cannot change the "Length" (precision) and
> > "Decimal places" (scale) field properties in Table Design mode.
> 
> Probably a similar problem to above. The driver does not signal to the
> rest of LibreOffice that the database is ready to accept values there?
> 
> It works for HSQLDB, so the LibreOffice code must be able to handle
> it, if the driver correctly supports it.
>