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