Getting table metadata on Teradata fails

[Bert-Jan de Gier]

Hello,

I'm trying to get metadata from a Teradata database using the following code:

DSLContext dslContext = using(databaseConnection, SQLDialect.TERADATA);

dslContext.meta().getTables();

This however fails with the following stacktrace:

java.lang.NullPointerException: Cannot invoke "String.toUpperCase()" because "typeName" is null
at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:769)
at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:1141)
at org.jooq.impl.MetaImpl$MetaTable.initColumns(MetaImpl.java:1980)
at org.jooq.impl.MetaImpl$MetaTable.<init>(MetaImpl.java:1382)
at org.jooq.impl.MetaImpl$MetaSchema.lambda$getTables$4(MetaImpl.java:640)
at org.jooq.impl.Tools.map(Tools.java:2709)
at org.jooq.impl.MetaImpl$MetaSchema.getTables(MetaImpl.java:571)
at org.jooq.impl.MetaImpl.lambda$getTables0$4(MetaImpl.java:373)
at org.jooq.impl.Tools.flatMap(Tools.java:2724)
at org.jooq.impl.MetaImpl.getTables0(MetaImpl.java:373)
at org.jooq.impl.AbstractMeta.lambda$getCachedTables$3(AbstractMeta.java:342)
at org.jooq.impl.AbstractMeta$Cached.init(AbstractMeta.java:225)
at org.jooq.impl.AbstractMeta.getCachedTables(AbstractMeta.java:342)
at org.jooq.impl.AbstractMeta.getTables(AbstractMeta.java:334)
at ...

What am I doing wrong? I'm using the latest Teradata driver (20.00.00.54) and jOOQ version (3.21.1). Thanks for any help you can provide.

Best regards,

Bert-Jan de Gier

[Lukas Eder]

Hi Bert-Jan,

Thanks for your patience. It appears there are two separate problems? The first stack trace hints at a problem in the JDBC driver, the second one seems to be a bug in jOOQ. Can you provide any information about how to reproduce these issues? E.g. what specific table definitions / type names are causing this? You could try to use filterSchemas() and filterTables() to filter out all non-essential meta data until you can identify the table that is causing the problem. Then, I'd be curious about the complete CREATE TABLE definition.

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/40b4b6af-6d60-4663-9256-d87dd4129ef9n%40googlegroups.com.

[Bert-Jan de Gier]

Hi Lukas,

When calling dslContext.meta().getTables(), the metadata is retrieved for all tables in all "databases" (schemas). This includes views in the SysAdmin database (schema), which contains several views (e.g.  FastLogRestartV). When inspecting this view in a tool like DBeaver, there is no column type for any of the columns. My guess is that this is what causes the NullPointerException in String.toUpperCase(). It explicitly does not occur on the table I created myself.

I've tested this by creating a (free) trial account on Teradata. I hope this is enough information to resolve this. Thanks!

Best regards,

Bert-Jan

[Lukas Eder]

Thanks again for your patience. I cannot reproduce any such problem with the FastLogRestartV view. It is produced correctly when calling ctx.meta().getTables().

Are you using the latest version of the Teradata JDBC driver from Maven Central? What other information about reproducing this issue can you provide? E.g. does your database user have all the required grants to query system views? What Teradata product are you using? (E.g. we don't support Teradata Aster)

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/63c87993-b0bc-4879-8ac2-0a8ef668bf3dn%40googlegroups.com.

[Bert-Jan de Gier]

Hi Lukas,

Thanks for looking into this further. I'm using the latest Teradata driver, and I'm using Teradata ClearScape. I could share the credentials and connection info with you to test directly on my environment. Please let me know if this would work for you.

Best regards,
Bert-Jan de Gier

[Lukas Eder]

Hi Bert-Jan,

I haven't seen this product by Teradata yet. We don't integration test against it, only local installations of Teradata, so I can't say for sure why this is happening. However, the stack trace happens from within the JDBC driver after an ordinary call to DatabaseMetaData::getColumns. I don't see how this could be prevented from jOOQ's perspective, even if I could reproduce it against your own instance. Have you considered reporting this to Teradata directly?

As a hint, this resource documents what might be causing it within Teradata:

https://docs.teradata.com/r/Teradata-VantageCloud-Lake-Analytics-Database-Messages/Database-Messages/3844

The explanation says:

Any DML statement that references AccLogTbl is treated as a DDL statement. It must follow the rule that DDL statements are not allowed in a multi-statement request. A DDL may be used in a macro, but only when the only statement in the macro is the DDL.

It looks as though it could be possible to prevent the issue by avoiding transactions when querying database meta data? But again, I cannot reproduce this particular problem on our own Teradata Express instance...

Best Regards,

Lukas

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/948c6dc0-602c-4385-bc86-c34dea280a1en%40googlegroups.com.

[Bert-Jan de Gier]

Hi Lukas,

I'm not very familiar with Teradata or their various products myself, I'm just troubleshooting an issue our customer has when connecting to it from our software, so trying to reproduce it. :-)

I disagree with the stacktrace (at least the one I posted in my original post) coming from the JDBC driver. I think the problem is that the column in the mentioned view don't have a data type (I can see this when checking the view in DBeaver). My assumption is that the jOOQ code does a toUpperCase() on the type name, which is null, causing the NPE. Not knowing your code of course, I'd think the remedy would be to add a null-check. I'd be happy to provide access to my test instance of Teradata to test and assess it.

Best regards,
Bert-Jan de Gier

[Lukas Eder]

Hi Bert-Jan,

I see, I checked the first stack trace, which I believe to be an issue within the JDBC driver. The second one (which I had overlooked in my previous email, sorry) is also a problem of the driver in my opinion. I don't think a type name should be nullable. But we can work around that easily, indeed, we'll just translate it to SQLDataType.OTHER. I've created an issue for this, which will be backported to 3.21.3, 3.20.14, and 3.19.33:

https://github.com/jOOQ/jOOQ/issues/19921

You can test the fix by applying this patch to your 3.21.1 version:

https://github.com/jOOQ/jOOQ/commit/a4765ee2727a8d25afc830736e9b821500742f6e.patch

Or you can wait for the nightly snapshot builds to build.

Let me know if this addresses the issue for you.

Best Regards,

Lukas

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/7eeea55d-68aa-45bb-b075-80854336d4ffn%40googlegroups.com.

[Bert-Jan de Gier]

Hi Lukas,

Confirmed the fix working on the 3.22.0 nightly build. Thank you very much!

Best regards,
Bert-Jan de Gier

[Lukas Eder]

Thanks for the feedback. I'll release these versions early next week.

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/465b9872-130c-461c-b461-bf8d6c82671bn%40googlegroups.com.