topic Re: Error on tDBOutput using dynamic schema when there is a VARBINERY data type in Talend Studio

Error on tDBOutput using dynamic schema when there is a VARBINERY data type

Qing — Fri, 15 Nov 2024 22:19:14 GMT

Hi,

My source and target are both jdbc mssqlserver. I'm using dynamic schema to load a table contains two columns in VARBINARY. I'm getting errors on the tDbOutput component saying: java.lang.UnsupportedOperationException: Unrecognized type id_Object

In the tDBInput component is's uing the default MSSQL mapping file for Dynamic with below setting:

</talendType>

</dbType>

Could anyone please help this?

Thanks in advance,

Qing

Re: Error on tDBOutput using dynamic schema when there is a VARBINERY data type

Anonymous — Wed, 30 Nov 2022 03:49:07 GMT

Hello,

What's JDK version are you using and could you please clarify in which Talend version/edition you are?

"java.lang.UnsupportedOperationException: Unrecognized type id_Object"

The above error was due to the db Type in the mapping files are not compatible.

As you mentioned that your source and target are both jdbc mssql server, what's your source and target mssql server version? The same version?

Best regards

Sabrina

Re: Error on tDBOutput using dynamic schema when there is a VARBINERY data type

Qing — Wed, 30 Nov 2022 04:04:59 GMT

Hi Sabrina,

Thanks for your reply. JDK is at 11.0.16. Talend is on 8.0.1 R2022-07.

Source is on SQL Server 14.0 (2017) and target is 13.0 (2016).

Thanks,

Qing

Re: Error on tDBOutput using dynamic schema when there is a VARBINERY data type

Anonymous — Wed, 30 Nov 2022 05:14:40 GMT

Hello,

I have seen a similar issue before but not the same case as the source is MSSql server and target is snowflake.

Please see the following details

Exception in component tSnowflakeOutput_1 (testJob)

java.lang.UnsupportedOperationException: Unrecognized type id_Object

at org.talend.daikon.di.DiIncomingSchemaEnforcer.diToAvro(DiIncomingSchemaEnforcer.java:199)

at org.talend.daikon.di.DiIncomingSchemaEnforcer.addDynamicField(DiIncomingSchemaEnforcer.java:149)

at local_project651.testjob_0_1.testJob.tMSSqlInput_1Process(testJob.java:1220)

at local_project651.testjob_0_1.testJob.runJobInTOS(testJob.java:2728)

at local_project651.testjob_0_1.testJob.main(testJob.java:2541)

Root Cause

The above error was due to the dbType in the mapping files are not compatible. For example of the test job for the error above; the job retrieve data from the table name account1 in Microsoft Server database then insert data into SnowFlake database. Both tables in both database has the same columns and the same datatype (note: datatype is not the same as dbType) in the schema.

When customer used the dynamic schema in their job to retrieve data from the dabase (ex: SQL Server in this case) then insert data into SnowFlake with dynamic schema. The dbType of input columns in the job (ex: tMSSqlInput) will be rely on the setting in the Talend Type Mapping Files. For this case, it is the mapping_MSSQL.xml. For the dbType of Money in the mapping_MSSQL.xml file for acct_balance column; the setting is only has

</dbType>

But in the mapping_Snowflake.xml, there is no dbType of MONEY. It only has the type (which is datatype) of Double:

</dbType>

Therefore, when it passes data from the tMSSqlInput component to tSnowflakeOutput component, it is error out in the tSnowflakeOutput component for not recognize the id_Object.

Solution

The easy way to fix this error is, we need to change the dbType from id_Object to id_Double for the MONEY in the mapping_MSSQL.xml file. So, it is compatible to the mapping_Snowflake.xml. To do this by follow below steps:

Open the studio

Navigate to File

Click on Edit Project properties

Expand the General

Click on the Metadata of TalendType

Scroll down then click on the mapping_MSSQL.xml and then click Edit

Scroll down the mapping_MSSQL.xml file to the tag <dbType type="MONEY">

</dbType>

Then change from <talendType type="id_Object" default="true" /> in the tag <dbType type="MONEY"> to <talendType type="id_Double" default="true" />

Example:

From:

</dbType>

To:

</dbType>

After, made the above change then ran the job; the job should works.

Or maybe you could choose just the columns which are not causing the problem if you have a mass volume of tables and data, the tables which probably causes the problem.

Hope it helps you!

Best regards

Sabrina

Re: Error on tDBOutput using dynamic schema when there is a VARBINERY data type

Qing — Wed, 30 Nov 2022 06:11:39 GMT

Hi Sabrina,

Thanks and I understand this issue but my case is SQL Server to SQL Server. Correct me if I'm wrong, it should use the same mapping_MSSQL.xml file for both input and output. In my current file, it is :

</dbType>

and I don't understand why I'm getting Unrecognized type id_Object.

Regards,

Qing

Re: Error on tDBOutput using dynamic schema when there is a VARBINERY data type

Anonymous — Wed, 30 Nov 2022 07:39:23 GMT

Hello @Qing Yang

I have the same feeling with you actually. Could you show the full exception stack?

Let me check it with our component experts firstly and then come back to you as soon as possible.

Best regards

Sabrina

Re: Error on tDBOutput using dynamic schema when there is a VARBINERY data type

Anonymous — Wed, 30 Nov 2022 09:16:36 GMT

Hello,

Could you please also raise a case with Talend Support, this issue should be reproducable by support with remote assistance and if that's the case then R&D can be involved easily.

Feel free to post your issue on Community.

Best regards

Sabrina