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

I would create a Stored Procedure in SQL Server that handles the inserting of records. Then call the Stored Procedure (pub.db:call) from wM passing whatever parameters.

Hope this helps.

Frank