topic Re: Cannot insert the value NULL into column 'headerId', table in Talend Studio

Cannot insert the value NULL into column 'headerId', table

_AnonymousUser — Sat, 16 Nov 2024 12:23:04 GMT

Hi There,
I would like to ask help you talend people. here it goes...
I have 2 tables:
TABLE1 --> headerId (primary key)
TABLE2 --> headerId (foreign key)
I insert data(csv) to TABLE1 using tFileInputDelimited -->tMAP --> MSSQLOutput
Result: TABLE1 populated
Then, I inserted data(csv) to TABLE2 using tFileInputDelimited -->tMAP --> MSSQLOutput
Result: Cannot insert the value NULL into column 'headerId', table
Question:
how can TABLE2 know that headerId already has a value inserted by TABLE1?
Best Regards,
Andres

Re: Cannot insert the value NULL into column 'headerId', table

Anonymous — Tue, 14 Feb 2012 02:21:34 GMT

Hi Andres
According to your requirment, why don't use only one tMap?
tFileInputDelimited-->tMap-->tMSSQLOutput
| |
tFileInputDelimited---- ----->tMSSQLOutput

Regards,
Pedro

Re: Cannot insert the value NULL into column 'headerId', table

_AnonymousUser — Tue, 14 Feb 2012 08:16:26 GMT

He Pedro. Good day!
Do you have the screenshot for this?
My problem here is:
I only have 1 CSV file. that CSV file has header and transactions
I have 2 tables(TABLE1 for header and TABLE2 for transactions). they are connected using headerId key.
Thanks,
Andres

Re: Cannot insert the value NULL into column 'headerId', table

Anonymous — Tue, 14 Feb 2012 09:00:51 GMT

Hi Andres
Could you offer the data of Table 1, Table 2?
And show me the result which you want.
Or I may misunderstand what you mean.
Regards,
Pedro

Re: Cannot insert the value NULL into column 'headerId', table

janhess — Tue, 14 Feb 2012 09:21:17 GMT

Probably referential integrity in your db returning the error.

Re: Cannot insert the value NULL into column 'headerId', table

_AnonymousUser — Tue, 14 Feb 2012 09:56:15 GMT

Yes. my problem is Referential Integrity.
since, TABLE1 and TABLE2 are connected using headerId, and that headerId is actually auto generated in TABLE1.
So, using TALEND inserting CSV file to TABLE1 is no problem. TABLE1 is populated by the data coming from the CSV. also, primary key headerId will be auto populated in DB.
but, once i created another job for TABLE2 and insert the same CSV, it says "CANNOT insert null values to TABLE2, headerId".
my problem is, how would TABLE2 would know that headerId already has value in TABLE1?
This is how I do it:
tFileInputDelimited(CSV) ---->tMAP -----> MSSQLOutput(TABLE1). this is ok primary key headerId is populated in DB
|
|
tFileInputDelimited(CSV) ---->tMAP -----> MSSQLOutput(TABLE2). this will produce an error saying that headerId cannot be null.
I have only 1 CSV file. it has header for name, addess, etc. in row1 this is for TABLE1. and it has a transaction data in row 2 this is for TABLE2.

Re: Cannot insert the value NULL into column 'headerId', table

_AnonymousUser — Tue, 14 Feb 2012 10:04:46 GMT

Hi, I will make my case more clearer.
This are my things:
2 tables (TABLE1 and TABLE2) and 1 CSV file.
This is what i want to do:
Insert the CSV file values to TABLE1 and TABLE2.
note: TABLE1 and TABLE2 are connected via headerId column where (TABLE1 headerId pk) and (TABLE2 headerId fk). Also, headerId is auto populated in DB. so, once you insert data into TABLE1, headerId will auto increment.

Re: Cannot insert the value NULL into column 'headerId', table

janhess — Tue, 14 Feb 2012 10:06:37 GMT

As I said before it's your database that knows about the referential integrity and returns the error.
You need to use tdbtypeLastInsertID to get the autogenerated id from table 1. There are a number of entries on the forum about how to do this.