tMSSQL_LastInsertID returning 0 after changing Select @@IDENTITY to SCOOE_IDENTITY

Four Stars

tMSSQL_LastInsertID returning 0 after changing Select @@IDENTITY to SCOOE_IDENTITY

Hello,

Im having an issue where tMSSQLLastInsertID object is returning a 0 after changing the code located here:

\Talend\Talend\Studio\plugins\org.talend.designer.components.localprovider_6.5.1.20180116_1512\components\tMSSqlLastInsertId\tMSSqlLastInsertId_begin.javajet

to the following:

<%
log4jCodeGenerateUtil.useExistConnection(node);
log4jCodeGenerateUtil.logInfo(node,"info",cid + " - Query:'SELECT @@IDENTITY'");
%>
java.sql.PreparedStatement pstmt_<%=cid %> = conn_<%=cid %>.prepareStatement("SELECT SCOPE_IDENTITY() as 'last_insert_id'");
int nb_line_<%=cid%> = 0;

 

I have a trigger on the table and that is why I need to change to Scope_Identity instead of using @@identity.  When I use @@identity it returns the incorect ID.  With the change I made above it returns 0, I have also tried just SCOPE_IDENTITY() without the as and it also returns 0.  I have also tried select SCOPE_IDENTITY() in the line above in blue still returns 0.

The ID field is a bigint that is auto incrementing.

 

Any help would be greatly appreciated

Moderator

Re: tMSSQL_LastInsertID returning 0 after changing Select @@IDENTITY to SCOOE_IDENTITY

Hello,

Please try to disable "Use Batch Size" in the tMSSqlOutput component that does the insert.

Let us know if it is OK with you.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: tMSSQL_LastInsertID returning 0 after changing Select @@IDENTITY to SCOOE_IDENTITY

I already had that disabled and for future reference or anyone who comes across this post...We opened up the java for the tMSSQLOutput component and found that it closes the connection to the database after the record is inserted and the tMSSQL_LastInsert makes a second call to the database so there is no way it can accurately get the last insert ID for the exact record that was inserted into that table.  

In order to solve this issue we actually make a 2nd call to the database to get the ID of the record inserted using a select statement and in the where clause 2 fields that make the record unique.  

Moderator

Re: tMSSQL_LastInsertID returning 0 after changing Select @@IDENTITY to SCOOE_IDENTITY

Hello,

Thanks for sharing your solution with us. We will appreciate it if you could post your select statement on forum.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.