topic Re: Database values are not imported but are visible in logrow in Talend Studio

Database values are not imported but are visible in logrow

amba1 — Fri, 15 Nov 2024 23:55:21 GMT

Hi everyone,

I have a strange issue here.

Basically I retrieve data from some files and import them in a database.

In my database I have a a table with a column called licenseplatekey. If no match is found, the value is set to -1.

When testing my job with a tlogrow, everything works perfectly, as you can see in the screenshot:

But when I import the data in my database, none of the data for the licenseplatekey column is imported. Each row just contains a NULL

All other columns are imported normally...

I also tried outputting the data in a csv files and it works as well.

Does anyone know what I might be doing wrong here?

Thanks!

Re: Database values are not imported but are visible in logrow

gjeremy1617088143 — Mon, 26 Jul 2021 13:53:17 GMT

Hi @Not defined Not defined , could you share picture of your job ,component used and schema of them ?

also when you try to import them have you specific message in the console ?

When you say you try to import, does carpark_id already exist.(do you update or insert or both ?)

Maybe you have to check autocommit in you dbconnection component advanced settings.

Send me love and kudos

Re: Database values are not imported but are visible in logrow

amba1 — Mon, 26 Jul 2021 13:57:49 GMT

hi @guenneguez jeremy,

Here's a screenshot of the whole job to give you an idea of what I'm doing:

And here's a picture of the last tMap (tmap-5)

licenseplatekey if else: Relational.ISNULL(licenseplate.licenseplatekey)?

(Relational.ISNULL(row7.key)?-1:row7.key):licenseplate.licenseplatekey

Schema of target table:

Re: Database values are not imported but are visible in logrow

gjeremy1617088143 — Mon, 26 Jul 2021 14:09:25 GMT

why you don't use tpostjob and tdbclose to close all your db connection ?

do you select auto commit on advanced settings of you tDBConnection component ?

the issue don't seem to be on the data but more on the database

also i see you have different db type for carpark id et licenceplate :

INT8 and INT4,maybe check your database tp see if you have the good db type

Re: Database values are not imported but are visible in logrow

amba1 — Mon, 26 Jul 2021 14:12:45 GMT

I do use it, had no place left for the screenshot sorry:

Re: Database values are not imported but are visible in logrow

gjeremy1617088143 — Mon, 26 Jul 2021 14:20:32 GMT

if for example you use a tdbinput and a query on your table , a select * for example, and you do guess schema have you the same DB type in the schema as your db output ?

Re: Database values are not imported but are visible in logrow

amba1 — Mon, 26 Jul 2021 14:29:08 GMT

The Schema seems to be the same as in the database:

When trying to get the schema with a tdbinput, i get the following error, database connection failed:

jdbc:postgresql://localhost:5432/USERNAME?"" does not work as string of connection, am i missing something?=

Re: Database values are not imported but are visible in logrow

gjeremy1617088143 — Mon, 26 Jul 2021 14:31:01 GMT

in your tDBOutput advanced settings you can select debug query mode and you can acces to the query with this variable :

((String)globalMap.get("tDBOutput_1_QUERY"))

it can be a good way to investigate

Re: Database values are not imported but are visible in logrow

gjeremy1617088143 — Mon, 26 Jul 2021 14:32:51 GMT

put an empty string "" on jdbc parameters

Re: Database values are not imported but are visible in logrow

amba1 — Mon, 26 Jul 2021 14:34:16 GMT

Database connection failed, but works when testing it from the metadata directly...

Re: Database values are not imported but are visible in logrow

gjeremy1617088143 — Mon, 26 Jul 2021 14:45:51 GMT

just try to force the jdbc parameter with empty string "", in your tdbconnection component : additionnal jdbc parameters : ""

Re: Database values are not imported but are visible in logrow

amba1 — Mon, 26 Jul 2021 14:49:59 GMT

Tried the empty strings as well, unfortunately no success. The error message under Details is "Schema not present in Database".

The schema is indeed present...

Re: Database values are not imported but are visible in logrow

amba1 — Mon, 26 Jul 2021 15:14:46 GMT

Managed to make it work.

The schema is exactly the same as the one I use as output

Re: Database values are not imported but are visible in logrow

gjeremy1617088143 — Mon, 26 Jul 2021 15:35:37 GMT

do you try my suggest for debugging query ?

Re: Database values are not imported but are visible in logrow

amba1 — Mon, 26 Jul 2021 15:38:19 GMT

@guenneguez jeremy I was able to find the issue!

The licenseplatekey is a column that i added with an alter table after the table was created. I use a function to partition the tables and forgot to update the function with the new column. It is now working! Thanks for your help 🙂