jdbc uuid columns are empty /null

[Michael Müller]

Hello. 

I observe a strange behaviour.

I am using a proprietary application specific jdbc driver to access the application data.

the driver maps sql queries to some kind of internal object oriented query, which is finally executed by the application, which access the data stored in an oracle DB.

In general , all is running well in sql-workbench. but I have a problem in accessing specific information which is stored in specific columns in the oracle DB.

The Oracle column type is RAW. The information stored in these columns is a 16 Byte UUID.

In sql-workbenchs Database Explorer the column has the  following properties:

DATA_TYPE: SQL_BINARY(UUID)

JDBC Type: BINARY

if I now select such a column in sql-workbench, the result seems to be null

The column value just displays a three dot button "..." .

If I click on one of these buttons, the Popup shows : Blob details: Blob size (null).

I know, that these values are not null in the DB.

First I had the JDBC driver under suspection.

But I did another test with JasperSoft Studio, using the same driver.

Here in the "Dataset and Query Dialog", the "class type" of the column  is "java.lang.Object"

And the values are displayed as HEX characters (32 byte).

This observation tells me that the jdbc driver delivers the column values to the jdbc client somehow.

I assume, that the empty result in sql-workbench is related to some auto type mapping and auto conversion of column values.

Is there any chance to influence this mapping/auto conversion in sql-workbench, to be able to access/display the data in sql-workbench as well ?

Thank you and kind regards.

...Michael

[Thomas Kellerer]

Hmm, strange.

this works just fine with the Oracle JDBC driver.

SQL Workbench does indeed do automatic conversion for RAW column. But the reason it doesn't happen for you is this:

  DATA_TYPE: SQL_BINARY(UUID)

  JDBC Type: BINARY

Because the code that decides whether the conversion should be done, checks for:

  DATA_TYPE: RAW

  JDBC Type: VARBINARY

which is what the Oracle driver reports for such a column.

From my perspective that JDBC driver doesn't comply with the JDBC API, because the column DATA_TYPE is documented to return the DBMS' data type name (and I don't think there is a SQL_BINARY(UUID) in Oracle).

Currently I don't have a way to configure this dynamically I am afraid.

The only workaround I can suggest is to use rawtohex() when selecting from that table.

I am a bit surprised though why you don't get a BLOB display instead.

Something like:

Because that is used when the column is reported as BLOB, BINARY, VARBINARY or LONGVARBINARY.

Regards
Thomas

[Michael Müller]

 Hello Thomas.

Great ... well at least from a "getting an answer" point of view.

I would not wonder, if this specific JDBC Driver is not compliant in all aspects of the JDBC API.

I appreciate your findings and answer ( as always ;) 

Even though, it seems that I cannot resolve my problem for now. 

I think about contacting the vendor support, because of this. But, of course, officially only the access through JasperSoft is supported and there it is magically working. So this might end up in a "works as designed" answer, I assume.

Anyway , thanks a ton for everything.

And not at last : I am again impressed by your unbeatable response time :D

Best Regards

.....Michael

[Thomas Kellerer]

I will make the detection when to use the implicit "hex string" conversion configurable, so it can be adjusted to more exotic drivers.

However I am still unsure why nothing is displayed at all. At least the "(BLOB)" indicator should be shown.

Maybe this is related to the way the blob data is retrieved? Do you have any errors in the logfile when you display such a column?

But without access to that driver, this is really hard to analyze.

Regards
Thomas

Michael Müller schrieb am 19.01.2021 um 17:14:
>  Hello Thomas.
>
> Great ... well at least from a "getting an answer" point of view.
> I would not wonder, if this specific JDBC Driver is not compliant in all aspects of the JDBC API.
> I appreciate your findings and answer ( as always ;)
> Even though, it seems that I cannot resolve my problem for now.
> I think about contacting the vendor support, because of this. But, of course, officially only the access through JasperSoft is supported and there it is magically working. So this might end up in a "works as designed" answer, I assume.
> Anyway , thanks a ton for everything.
> And not at last : I am again impressed by your unbeatable response time :D
>
> Best Regards
> .....Michael
>
> Thomas Kellerer schrieb am Dienstag, 19. Januar 2021 um 16:55:09 UTC+1:
>
> Hmm, strange.
>
> this works just fine with the Oracle JDBC driver.
>
>

> SQL Workbench does indeed do automatic conversion for RAW column. But the reason it doesn't happen for you is this:
>
>   DATA_TYPE: SQL_BINARY(UUID)
>   JDBC Type: BINARY
>
> Because the code that decides whether the conversion should be done, checks for:
>
>   DATA_TYPE: RAW
>   JDBC Type: VARBINARY
>
> which is what the Oracle driver reports for such a column.
>
> From my perspective that JDBC driver doesn't comply with the JDBC API, because the column DATA_TYPE is documented to return the DBMS' data type name (and I don't think there is a SQL_BINARY(UUID) in Oracle).
>
> Currently I don't have a way to configure this dynamically I am afraid.
>
> The only workaround I can suggest is to use rawtohex() when selecting from that table.
>
> I am a bit surprised though why you don't get a BLOB display instead.
>
> Something like:
>

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/15874865-c0db-47f5-a5eb-0f1dfd3870aan%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/15874865-c0db-47f5-a5eb-0f1dfd3870aan%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Michael Müller]

Hi Thomas.

No, as far I am aware, there are no errors related to this kind of query: select id from cnt where userid='xxx'

Here id is of type UUID (RAW(16) in oracle).

the log (.sqlworkbench\workbench.log)  just shows the connect, but no message related to the query itself.

----

2021-01-20 08:20:47 INFO  Creating new connection for "{Default group}/New profile": [user=srvcdesk, url=jdbc:sdm:bop:@192.168.2.225:2100;impersonateUserUuid=, driverClass=com.ca.sdm.dal.sql.jdbc.driver.DalJdbcDriver, driverName=SDM 17.3.0.3] 

2021-01-20 08:20:47 INFO  Adding ClassLoader URL=file:/C:/Sicherung/SDM/jars_17.3.0.3/BOPIntegration.jar 

2021-01-20 08:20:47 INFO  Adding ClassLoader URL=file:/C:/Sicherung/SDM/jars_17.3.0.3/sd-utils.jar 

2021-01-20 08:20:47 INFO  Adding ClassLoader URL=file:/C:/Sicherung/SDM/jars_17.3.0.3/log4j-1.2.17.jar 

2021-01-20 08:20:47 INFO  Adding ClassLoader URL=file:/C:/Sicherung/SDM/jars_17.3.0.3/slump.jar 

2021-01-20 08:20:47 INFO  Adding ClassLoader URL=file:/C:/Sicherung/SDM/jars_17.3.0.3/domsrvr_utils.jar 

2021-01-20 08:20:47 INFO  Adding ClassLoader URL=file:/C:/Sicherung/SDM/jars_17.3.0.3/bc-fips-1.0.1.jar 

2021-01-20 08:20:53 INFO  WbWin-1: Using DBID=sdm 

2021-01-20 08:20:53 INFO  WbWin-1: Using identifier quote character: " 

2021-01-20 08:20:53 INFO  WbWin-1: Using search string escape character: null 

2021-01-20 08:20:53 INFO  WbWin-1: Table types returned by the JDBC driver: [TABLE] 

2021-01-20 08:20:53 INFO  WbWin-1: Using catalog separator: . 

2021-01-20 08:20:53 INFO  Connected to: [SDM], Database version info: [17.2], Database version number: [0.0], Driver version: [null], JDBC version: [0.0], ID: [WbWin-1] 

----

Regards

.....Michael

[Michael Müller]

Just for sure, this is how it looks like at my side :

Regards

....Michael

You received this message because you are subscribed to a topic in the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sql-workbench/2NV7wEOpg3E/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sql-workbenc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/bc8c82e1-666d-47ab-b330-ebefbf208324n%40googlegroups.com.

[Thomas Kellerer]

So, this:

> 2021-01-20 08:20:53 INFO Connected to: [SDM], Database version info: [17.2], Database version number: [0.0], Driver version: [null], JDBC version: [0.0]

> 2021-01-20 08:20:53 INFO WbWin-1: Using DBID=sdm

is the real problem.

The name in the first part "Connected to:" is taken from DatabaseMetaData.getProductName() which should report the name of the underlying database product, not the driver "name".

The driver's name should not be reported as the database name. And the driver's version should be reported as the driver version, not the database version. So this meta information breaks a lot of things that SQL Workbench relies on from the JDBC API.

For comparison, the log line for an Oracle connection looks like this:

Connected to: [Oracle], Database version info: [Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.7.0.0.0], Database version number: [18.0], Driver version: [19.3.0.0.0], JDBC version: [4.3]

And because of that, SQL Workbench doesn't treat it as an Oracle database.

If you are using build 127, you can define a configuration setting that makes SDM an alias for Oracle. So all Oracle settings will be applied.

To that, you need to add the following line to workbench.settings

workbench.db.sdm.aliasid=oracle

Because of the (invalid) version 17.2 reported, SQL Workbench will treat this as an Oracle 12.x database then. I am not sure how far you get with that, if the driver so blatantly lies about the underlying database.

I am not sure why you don't see any values in the BLOB column. There are essentially three different ways to get a BLOB value from a ResultSet.
The default I use, is ResultSet.getBinaryStream() as from my experience that is the one that works across most drivers.

Typically if a driver doesn't support a method, it throws an error.
If there is no error in the logfile, then maybe the driver silently ignores it and returns nothing.

You can try to change the underlying method that is called using:

workbench.db.sdm.blob.read.method=jdbcBlob

which uses ResultSet.getBlob()

Or you can try:

workbench.db.sdm.blob.read.method=byteArray

which uses ResultSet.getBytes()

I am wondering: if you are connecting directly to the Oracle database (outside of the JasperSoft), why can't you use the Oracle JDBC driver?

Regards
Thomas

> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/bc8c82e1-666d-47ab-b330-ebefbf208324n%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/bc8c82e1-666d-47ab-b330-ebefbf208324n%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Michael Müller]

As said, this is a proprietary application specific JDBC driver. It hides , at least it tries to, the DB specifics from the jdbc client. In fact the application supports different types of Databases, Oracle is just one of them. It has its own DB access layer which harmonizes the differences in the different DB systems.

On top of this , there is a kind of object layer and so on, and so on.

From an application point of view, you can use this JDBC driver to do your reporting, DB system independent. 

This might be the reason the vendor decides to return "SDM" as the DB system name, (SDM is the application name), 17.2 is some kind of application version. and so on....

Thanks again for providing these insights. I'll give your suggestions a try , and let you know...

Regards

...Michael

[Michael Müller]

Okay , did some tests:

workbench.db.sdm.aliasid=oracle 

and

workbench.db.sdm.blob.read.method=byteArray

have obviously no effect. Same behaviour as before .

but when using 

workbench.db.sdm.blob.read.method=jdbcBlob  

I get an exception when doing the query :

...

2021-01-20 11:49:08 INFO  Connected to: [SDM], Database version info: [17.2], Database version number: [0.0], Driver version: [null], JDBC version: [0.0], ID: [WbWin-1] 

2021-01-20 11:49:22 ERROR SQL Error during retrieve java.lang.NullPointerException

java.sql.SQLException: java.lang.NullPointerException

at workbench.storage.reader.RowDataReader.readColumnData(RowDataReader.java:412)

at workbench.storage.reader.RowDataReader.read(RowDataReader.java:268)

at workbench.storage.DataStore.initData(DataStore.java:1463)

at workbench.storage.DataStore.initData(DataStore.java:1399)

at workbench.sql.SqlCommand.processResults(SqlCommand.java:798)

at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:147)

at workbench.sql.StatementRunner.runStatement(StatementRunner.java:562)

at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3493)

at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2216)

at workbench.gui.sql.SqlPanel$16.run(SqlPanel.java:2154)

Caused by: java.lang.NullPointerException

at workbench.storage.reader.RowDataReader.readBlob(RowDataReader.java:604)

at workbench.storage.reader.RowDataReader.readColumnData(RowDataReader.java:346)

... 9 more

2021-01-20 11:49:22 ERROR Error executing:

select id from cnt

  java.lang.NullPointerException 

....

Don't worry. I'm fine with the functionality sql-workbench provides so far. Thanks again for all your work .

Regards

.........Michael

[Michael Müller]

One last thing ;)

It seems that the only implemented jdbc api method is ResultSet.getObject().

All others ,like getBinaryStream(), getBlob(), getArray() or getBytes(), are just returning null.

Any chance to force sql-workbench to use getObject() ?

Regards

...Michael

[Thomas Kellerer]

> but when using
> workbench.db.sdm.blob.read.method=jdbcBlob
>
> I get an exception when doing the query:

Ah, thanks. I'll fix that

> It seems that the only implemented jdbc api method is ResultSet.getObject().
> All others ,like getBinaryStream(), getBlob(), getArray() or getBytes(), are just returning null.
> Any chance to force sql-workbench to use getObject() ?

Currently this is not possible, but it absolutely makes sense to add that.

Thomas

[Michael Müller]

Sounds great. Let me know, if  any additional information is needed.

in my case getObject() already converts the binary 16 Byte value to a hex string and returns that as java.lang.String.

looking like

---

public Object getObject(int i) throws SQLException {
   try {
      logger.debug("getObject called");
      Object retVal = this.resultSet.getObject(i);
      if (retVal instanceof UUID) return ((UUID)retVal).toHexString();
      return retVal;
   } catch (DALException e) {
      throw new SQLException(e);
   }
}

---

...Michael