One Star

tMSSqlOutput update or insert on data not working

Hello I'm reading a delimeted input file and putting that info on SQL Server. Depending on "shop_number" field (that is not PK) from the file, I have to update an existing row or insert a new one into the DB. On my tMSSqlOutput ("Pedido") I selected "update or insert" option on data, and removed the PK field from the schema, I also marked the "shop_number" as key. But when I execute the project, it gets stucked in the red circled part.
I used "sp_who" in the database, and it seems that talend is locking the table and it cant goes on.
whay should I do to get my data into the table? (if im not being clear enough, let me know, so I try to explain it another way)
20 REPLIES
One Star

Re: tMSSqlOutput update or insert on data not working

Hi
Why do you remove the PK field from the schema?
Please show me the schema of your input file and the schema of your db table.
Best regards!
Pedro
One Star

Re: tMSSqlOutput update or insert on data not working

Hi
Why do you remove the PK field from the schema?
Please show me the schema of your input file and the schema of your db table.
Best regards!
Pedro

Hello, this is the tMap1 from the other image. The problem is with the "saidaPedido" output, my table layout is exactily that, excepts for the PK I deleted from the mapping. I want to use the field "num_pedido" as key to see if this row should be inserted or updated.

EDIT: I renamed the outputs from the 1st image. 'saida1' is now 'saidaCliente', and 'saida2' is now 'saidaPedido'.
One Star

Re: tMSSqlOutput update or insert on data not working

Hi
I guess this is an issue at database side.
Restart your MSSQL server and run job again.
Can this issue be reproduced?
Best regards!
Pedro
One Star

Re: tMSSqlOutput update or insert on data not working

just restarted the computer, but the problem still persists... :/
I dont know how you can reproduce this...
One Star

Re: tMSSqlOutput update or insert on data not working

Hi,
what information do you catch from sp_who?
is the job running well without call to sp_who?
One Star

Re: tMSSqlOutput update or insert on data not working

Hi,
what information do you catch from sp_who?
is the job running well without call to sp_who?

The sp_who command doesnt affect the job's performance.
and here are the 2 screenshots from my sp_who before and during job execution.
One Star

Re: tMSSqlOutput update or insert on data not working

ok I was thinking you use sp_who in your job
maybe you can try to put a mssqlconnection and mssqlcommit after each subjob that writes to db
(even if MSSQL is rarely configured for commits and rollbacks)
One Star

Re: tMSSqlOutput update or insert on data not working

just tried the commit, it didnt work, although it gave a clue about the problem... seems like 'Pedido' output is having some trouble to execute.
One Star

Re: tMSSqlOutput update or insert on data not working

any particularies on that table, contraints etc ?
One Star

Re: tMSSqlOutput update or insert on data not working

Changing the key in your map isn't going to get the desired results as your database still thinks it has the same primary key. I think you'll need to use tMSSQLRow to do the update and specify your update query.
One Star

Re: tMSSqlOutput update or insert on data not working

any particularies on that table, contraints etc ?

I dont believe it does have, because when i tried to use insert only on the output, it inserted just fine. (but leaving it this way will generate multiple rows with the same data)

Changing the key in your map isn't going to get the desired results as your database still thinks it has the same primary key. I think you'll need to use tMSSQLRow to do the update and specify your update query.

that might work, do you know where can I find an example about how to use tMSSQLRow for updates and inserts?

Thank you.
One Star

Re: tMSSqlOutput update or insert on data not working

with a tMSSQLRow you will have to write by your own the sql update order, I don't think it's the wright way to do what you want
why won't you let insert or update key to id_cliente?
One Star

Re: tMSSqlOutput update or insert on data not working

if you absolutely want to use check id num_pedido is present in pedido table I think you should separate "cliente" job and "pedido" job
unplug pedido output and transformation from your tmap
create another subjob (a copy) and had to the tmap a lookup with table pedido where join condition is row_a_1.id_pedido=pedido.num_pedido
set back pedido insert or update key to id_cliente
One Star

Re: tMSSqlOutput update or insert on data not working

with a tMSSQLRow you will have to write by your own the sql update order, I don't think it's the wright way to do what you want
why won't you let insert or update key to id_cliente?

if you absolutely want to use check id num_pedido is present in pedido table I think you should separate "cliente" job and "pedido" job
unplug pedido output and transformation from your tmap
create another subjob (a copy) and had to the tmap a lookup with table pedido where join condition is row_a_1.id_pedido=pedido.num_pedido
set back pedido insert or update key to id_cliente

I dont believe the problem has anything to do with table Cliente or the id_cliente. Looks like when talend check for an existing row in the db, it gets stucked... I will make a test here and insert all clientes first (this part of the job works fine), and then will try to insert pedidos without a lookup into cliente table. Let's see if this changes anything.
One Star

Re: tMSSqlOutput update or insert on data not working

Why can't you just specify the correct primary key for pedido. If the record exists it will modify it, if it doesn't exist it will insert it.
One Star

Re: tMSSqlOutput update or insert on data not working

Why can't you just specify the correct primary key for pedido. If the record exists it will modify it, if it doesn't exist it will insert it.

Because the correct primary key for pedido doesnt come in the input file. If I map that attribute it will always insert a new row into the db, since the PK will be always null. Thats why I'm trying to use 'num_pedido' as key, because that field is present in the input and is unique in my db.
One Star

Re: tMSSqlOutput update or insert on data not working

If it doesn't come from the input file how do you expect to match it to your table? And trying to create it your way is always going to produce the null primary key in the output which will be locked after the first attempt at insert/update.
One Star

Re: tMSSqlOutput update or insert on data not working

If it doesn't come from the input file how do you expect to match it to your table? And trying to create it your way is always going to produce the null primary key in the output which will be locked after the first attempt at insert/update.

good point. I inteded to use the 'num_pedido' field to attempt to update or insert.. Isn't there anyway to do that?
One Star

Re: tMSSqlOutput update or insert on data not working

You wouldn't be able to insert without a proper primary key. You can use pedido as a lookup to match the num_pedido to and use the primary key from that for modify or you could use a tMSSQLRow for the update with an update where condition.
One Star

Re: tMSSqlOutput update or insert on data not working

You wouldn't be able to insert without a proper primary key. You can use pedido as a lookup to match the num_pedido to and use the primary key from that for modify or you could use a tMSSQLRow for the update with an update where condition.

I think I will use pedido as a lookup, that might work, Thank you. I cant do that right now, but ASAP I will try this and post here if it worked or not.