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