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,
Could you layout the webMethods components you are using, including how you are accessing SQL Server from webMethods (which adapter)?
There is often a way, even if not the most direct. I have not done exactly what you describe, but I have done and seen similar.
Mark Thomsen
Mark,
Thanks for the reply! I’ve only got a few months experience with webMethods, and no formal training, so I hope I can answer your questions satisfactorily. I admit I’m not completely sure what you’re asking, but I’ll give what I think is the relevant information. Sorry if it’s too much you don’t need and/or not enough that you do.
We have webMethods B2B Server, 4.0.1. If I click “Database” in the “Adapters” section of the Administrator page, I get to a page that gives me the choice of “Service Generation” and “Alias Management”. I don’t know how to find out any more information about “which adapter” this is, there is no “About” link there.
Anyway, I set up an alias to the DB URL “jdbc:microsoft:sqlserver://cdf02252:1433”, using the driver “com.microsoft.jdbc.sqlserver.SQLServerDriver”. Then I created a service using this alias using the “Service Generation” series of screens.
When I go into the Business Connector integrator, the service is there, in the package and folder I specified. It has a single flow step, which is a call to the pub.db:execSQL service, which I can see is in the WmDB package. This service, of course, is a webMethods-delivered Java service. The service generation code has set up values in the $dbAlias, $dbSQL, $dbParamNames, and $dbParamTypes inputs to the execSQL for me based on the answers I gave it, and I subsequently modified the $dbSQL as I described previously. The only outputs are those created by default from the execSQL service (gray lines, not black). I run it, key in some input data, and it inserts the data into the table, but the service returns no value.
That’s all I can think of. Does this help?
John,
That is fine description.
Not what I have seen - I have not attempted to get the assigned ID back from an INSERT, from within a service. But I suspect it is possible. I do not have the product or documentation immediately at hand.
If the original INSERT has one of a combination of columns that are unique, perhaps a second statement, a SELECT, could retrieve the ID without worrying about multiple users (unless the row might be updated or deleted before the SELECT occurs).
Without the docs, here is the path I would take. Go through the steps to set up a SELECT. Note the handling of output from the statement, the words in the UI. Then do the INSERT development steps again, and look for those words. If you don’t see, it may be that handling output/results from non-SELECT statements is not supported.
Sorry I don’t have better to offer at the moment.
Mark Thomsen
Thanks for the reply. My fallback solution is to do what you suggest, a SELECT immediately after the INSERT, but that’s not quite as elegant. The problem is that the value being automatically assigned is the primary key, so not knowing the one piece of data whose entire job is to uniquely identify the row, how then do I ask for the row I just inserted? I’m sure that in my situation I can safely assume that the row has not yet been updated or deleted by someone else. But it is possible (not common, but possible) that another row may have been inserted by someone else immediately afterwards, so I can’t just go for the row with the max primary key. However, I think I can combine that with another piece of identifying information (in this case, the userid of the person submitting the information), so asking for the highest primary key for this userid is probably good enough.
As I say, it’s just not as elegant! 
Thanks for the conversation.
I may be wrong in some of this, so take my comments with a grain of salt.
I haven’t seen any adapters support the return of data during an insert, delete or update operation. My guess is that’s because there is no standard for what can be returned, and trying to account for all possibilities would result in significant overhead trying to return data that may or may not be used.
Doing a select after an insert, like you’ve done, is rather risky. You’ll have to code for the times when no rows are returned. This may or may not be reasonable.
Another approach is to use stored procedures. You can have the proc do anything you want and return anything you need (identity, rowcount, etc.) You can get some performance benefit too, depending on what you’re doing. We did that on a B2B project a couple years ago and it worked well. The downside of course is working with stored procs, which can be an administrative bottleneck depending upon your company and DBAs.