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]>
