SQL Server Insert with Select @@Identity?

, ,
1

Greetings,

I am just begininning to experiment with accessing databases, and have managed to connect to SQL Server using Microsoft’s beta JDBC driver. The ultimate goal is to acquire records from a mainframe real-time via MQ Series, and insert them into a SQL Server database. I’m just starting on the proof of concept at this point, but we may need to have it in production in about a month.

The main SQL Server table uses a numeric primary key that is automatically assigned and incremented. In my ASP pages that do this insert, the SQL Server syntax

SET NOCOUNT ON;
INSERT INTO MyTable ( col1 [, ... ] )
   VALUES
   ( value1 [, ... ] )
SELECT @@IDENTITY AS UNID;
SET NOCOUNT OFF

will do the insert and return the primary key that was assigned, all in one statement. Obviously, in a multi-user environment it is ideal to do this all as a single transaction.

So my question is, can this be done in webMethods, and how? I’ve already tried changing the execSQL’s $dbSQL input to the above statement, and the insert still works, but nevertheless I get no results variable back. It could be that some other settings need to change that I have missed.

Has anyone successfully done this?

  • John Brooking
2

John,

Have you figured out the solution to your problem? If so, can you please share it with me. I am trying to do something similiar and I get no results variable back.

Thanks,

Phillip

3

This currently cannot be done using the JDBC Adapter but I have recently done this in wM 6.0.1 using the WmDB package. Generate the INSERT statement and then add the NOCOUNT / IDENTITY stuff into the dbSQL field. I have also raised an enhancement request for this functionality in the JDBC adapter.

4

Would you please inform me when there is an enhancement in the JDBC adapter. Thanks in advance.

How did you add the NOCOUNT/IDENITY syntax? I received a sql syntax error when I added this to the dbSQL field:

INSERT into x_table(?,?) VALUES(?,?) NOCOUNT/IDENTITY

Phillip

5

I simply changed the dbSQL string to something like this:

SET NOCOUNT ON INSERT INTO tMembers (MemberName) VALUES (‘Allen’) SELECT @@IDENTITY AS NewID SET NOCOUNT OFF

The SQL Server query parser somehow understands that there are four separate commands in the one string!

Cheers,
Allen.