RE: servlet-jdbc-SQL server
With oracle there's a way to specify that a insert does a return, it's something like insert into tablea ( a ) values ( 'a' ) returning id. I know this isn't oracle that you're asking about, but you might try using that syntax on ms sql and see what happens. Or it might help to find it in their documentation (there's a guide oracle vs ms sql in the documentation). --mikej -=- mike jackson [EMAIL PROTECTED] -Original Message- From: Matt Brennan [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 8:36 AM To: [EMAIL PROTECTED] Subject: servlet-jdbc-SQL server Just taking some stumbling first steps talking to SQL server from a servlet using the Microsoft JDBC driver. Servlet container is tomcat 4.0.4. Quick question if I may: I need to insert a row into an SQL Server table and have the servlet generate a page that includes an automatically generated IDENTITY from the newly created row. How after the insert can I find the generated ID without conducting a subsequent query (which would have no guarantee of returning uniquely the newly inserted record)? // 'con' is a connection to MS-SQLserver with SelectMethod=cursor Statement state = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); // Generate an empty result set ResultSet content = state.executeQuery(sqlQuery); content.moveToInsertRow(); // Column 1 is id int IDENTITY NOT NULL PRIMARY KEY // Column 2 is name VARCHAR(20) content.updateString (2, something); content.insertRow(); // If I then query the inserted row, to find the newly generated // IDENTITY value I get MIN_INT instead of '1' (the default first // identity generated by SQL server if no seed is given) int id = content.getInt (1); Is this some peculiarity of the servlet interface to the driver, to the driver itself or just my bad undestanding of the insert process? Is it possible to configure the Statement so that the Insert row in the Result Set gets updated when the insert takes place and the 'id' gets generated? Please excuse the fact that this query starts to look more like a JDBC query than a tomcat query - not quite sure where the problem lies - tomcat+driver? driver? my understanding (most likely!). I am hoping that other servlet developers have crossed this bridge before. matt -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: servlet-jdbc-SQL server
As long as you run a second query to retrieve the @@IDENTITY value using the same connection handle, then you are guaranteed to receive the correct value. If you would like to verify that you are using the same connection handle, run the profiler application, and watch your app run. driver itself or just my bad undestanding of the insert process? Is it possible to configure the Statement so that the Insert row in the Result Set gets updated when the insert takes place and the 'id' gets generated? There are two ways of doing this. The first one is to use a stored procedure to do the insert/update and have it return the value. The second , and I haven't tried this would be to pass a compound statement insert into ...; select @@IDENTITY. I will give you one final warning about @@IDENTITY. If your statement invokes another statement (trigger, etc) that performs an insert into a table that contains an identity, you will receive the value of the last created identity, which probably is not the one you expect. George Sexton MH Software, Inc. Home of Connect Daily Web Calendar Software http://www.mhsoftware.com/connectdaily.htm -Original Message- From: Matt Brennan [mailto:[EMAIL PROTECTED]] Sent: 19 August, 2002 9:36 AM To: [EMAIL PROTECTED] Subject: servlet-jdbc-SQL server Just taking some stumbling first steps talking to SQL server from a servlet using the Microsoft JDBC driver. Servlet container is tomcat 4.0.4. Quick question if I may: I need to insert a row into an SQL Server table and have the servlet generate a page that includes an automatically generated IDENTITY from the newly created row. How after the insert can I find the generated ID without conducting a subsequent query (which would have no guarantee of returning uniquely the newly inserted record)? // 'con' is a connection to MS-SQLserver with SelectMethod=cursor Statement state = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); // Generate an empty result set ResultSet content = state.executeQuery(sqlQuery); content.moveToInsertRow(); // Column 1 is id int IDENTITY NOT NULL PRIMARY KEY // Column 2 is name VARCHAR(20) content.updateString (2, something); content.insertRow(); // If I then query the inserted row, to find the newly generated // IDENTITY value I get MIN_INT instead of '1' (the default first // identity generated by SQL server if no seed is given) int id = content.getInt (1); Is this some peculiarity of the servlet interface to the driver, to the driver itself or just my bad undestanding of the insert process? Is it possible to configure the Statement so that the Insert row in the Result Set gets updated when the insert takes place and the 'id' gets generated? Please excuse the fact that this query starts to look more like a JDBC query than a tomcat query - not quite sure where the problem lies - tomcat+driver? driver? my understanding (most likely!). I am hoping that other servlet developers have crossed this bridge before. matt -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: servlet-jdbc-SQL server
MySQL has a last_insert_id() function which you can use on a connection - Original Message - From: Mike Jackson [EMAIL PROTECTED] To: Tomcat Users List [EMAIL PROTECTED] Sent: Monday, August 19, 2002 4:38 PM Subject: RE: servlet-jdbc-SQL server With oracle there's a way to specify that a insert does a return, it's something like insert into tablea ( a ) values ( 'a' ) returning id. I know this isn't oracle that you're asking about, but you might try using that syntax on ms sql and see what happens. Or it might help to find it in their documentation (there's a guide oracle vs ms sql in the documentation). --mikej -=- mike jackson [EMAIL PROTECTED] -Original Message- From: Matt Brennan [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 8:36 AM To: [EMAIL PROTECTED] Subject: servlet-jdbc-SQL server Just taking some stumbling first steps talking to SQL server from a servlet using the Microsoft JDBC driver. Servlet container is tomcat 4.0.4. Quick question if I may: I need to insert a row into an SQL Server table and have the servlet generate a page that includes an automatically generated IDENTITY from the newly created row. How after the insert can I find the generated ID without conducting a subsequent query (which would have no guarantee of returning uniquely the newly inserted record)? // 'con' is a connection to MS-SQLserver with SelectMethod=cursor Statement state = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); // Generate an empty result set ResultSet content = state.executeQuery(sqlQuery); content.moveToInsertRow(); // Column 1 is id int IDENTITY NOT NULL PRIMARY KEY // Column 2 is name VARCHAR(20) content.updateString (2, something); content.insertRow(); // If I then query the inserted row, to find the newly generated // IDENTITY value I get MIN_INT instead of '1' (the default first // identity generated by SQL server if no seed is given) int id = content.getInt (1); Is this some peculiarity of the servlet interface to the driver, to the driver itself or just my bad undestanding of the insert process? Is it possible to configure the Statement so that the Insert row in the Result Set gets updated when the insert takes place and the 'id' gets generated? Please excuse the fact that this query starts to look more like a JDBC query than a tomcat query - not quite sure where the problem lies - tomcat+driver? driver? my understanding (most likely!). I am hoping that other servlet developers have crossed this bridge before. matt -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
