Windows 7 connecting to LibreOffice Base 4.3.5

Can you provide a test case? getShort/getInt/getLong should return correct data . getByte won't because Java's byte is signed.

The sole case is use of connector in LibreOffice base is described in the link, i.e., and direct table access and data entry via the LibreOffice UI for table and form access. LO team said in comment: "[s]ince the JDBC drivers (by necessity) shows us the datatypes as signed, I'm going to set this as 'NOTOURBUG' for the JDBC part." I do not have enough of a granular view of functionality to evaluate that statement by LO team.

the test case that I keep running into is to create a data field of TINYINT UNSIGNED which should have a range 0 to 255, but if I enter anything higher than 127 through the Java connector, I get error "Error updating the current record / Out of range value for 'FIELD' at row x". SQL Status: 22003. Error code: 1264.

I also get similar errors through other connectors, but Heidi interfaces directly in the correct way.

NOTOURBUG seems wrong. they should be using the integer type, which is wide enough to incorporate 0-255. Like, short, or int, or if they wish even long.
They can consult http://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html to figure out how unsigned types are mapped to correct java types. MariaDB driver uses the same type conversion as ConnectorJ

Vladislav, LibreOffice uses the type that the JDBC Connector tells it to use. Assume a table "foo" having a column "bar" of type "tinyint(3) unsigned NOT NULL". Assume rs is a recordset that includes that column and the index of that column is 1. For example the result of executing "SELECT bar FROM foo".

Assume the user is trying to write the value "128" into that column.

Then "rs.getColumnType(1)" returns java.sql.Types.TINYINT. This leads LibreOffice to call rs.setByte(1, XXX), and not setInt(1, XXX); LibreOffice uses a prepared statement to do the update. If the JDBC driver would return java.sql.Types.SMALLINT for the type of that column, then LibreOffice would call setShort(1, XXX).

Now, about the XXX, LibreOffice (written in C+) casts (in C+) the value it has (which is 128) into a "signed char", which makes -128, so in the end the Java code called is "rs.setByte(1, -128)" which leads to an error.

How do you suggest that, while LibreOffice DOES NOT KNOW it is connecting to MySQL (because connecting through JDBC) and thus cannot do anything specific to MySQL, LibreOffice should detect this situation and instead decide to use setShort?

Hmm... We could use rs.isSigned(1)...

or you can use ResutSet.getColumnClassName(),very direct way to know which java type you should be using.

Essentially, LibreOffice assumes all there is to know about the type of the column is in the type, precision, scale, and ignores the "is signed". I'm realising this could be changed, but it is not a small fix.

Vladislav, LibreOffice does not use only JDBC, so it needs something that works also for other database APIs, and in fine it needs to do stuff at a level that is database-API-agnostic and gets mapped through drivers (in this case the "LibreOffice JDBC driver"). If I dare make a quick judgement, getColumnClassName is too Java-specific.

Well, I was answer the question about how the LibreOffice might detect the correct type of variable, or API to use if it does not know it connects to MySQL. You can use either getColumnClassName(), isSigned() as you correctly mention, or also with getColumnTypeName(), the last would contain "UNSIGNED" for unsigned columns. We now seem agree that the property is detectable. As for the best API to use, it is of course up to you to decide.

I'd only like to point out to another less trivial case so it is not forgotten. BIGINT UNSIGNED maps to Java's BigInteger, because Long can only represent signed 64 bit values, but not unsigned with the most significant bit set. getColumnClassName() would handle that case. in C++ you can trivially use unsigned long longs.

closing since not a connector bug