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

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

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.

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

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.