0

I'm working with a legacy Sybase database, on a table that has an Identity column. When inserting a value into the table, I need to retrieve the new identity value.

I've been picking up ideas here and there. Since the database knows neither Ident_Current(), nor Scope_Identity, nor that the Insert query can have an Output clause, I've tried to initialize an OleDbCommand with the following:

Insert Into DatabaseName..TableName 
(Tbl_ColumnA, Tbl_ColumnB, Tbl_ColumnC, Tbl_ColumnD, Tbl_ColumnE, 
Tbl_ColumnF, Tbl_ColumnG) 
Values (
'Valid string for column A', 'Valid string for column B', 'Same for column C', 'And for D', 
'And E', 'And F too', Null
) ; Select Max(Tbl_Id) From DatabaseName..TableName;

When the execution reaches OleDbCommand.ExecuteScalar(), it seems to freeze. (Pausing the debug shows that it's still on that instruction, but it doesn't appear to do anything.)

Checking the contents of TableName shows that the new row has duly been inserted in TableName.

But I don't get any results.

I've also tried replacing Select Max(Tbl_Id) From DatabaseName..TableName; with Select @@Identity;, but that doesn't appear to improve the situation.

What am I doing wrong?

(Alternately, I could run the two queries separately in a single transaction, but I'd also like to know why my solution isn't working.)

Jean-David Lanz
  • 865
  • 9
  • 18
  • sorry, can't speak to the freeze/hang issue; in the meantime ... please update the question (and tag?) to state which Sybase RDBMS (ASE? IQ? SQLAnywhere? Advantage?) and version; `select max()` is never a good idea as you could pick up the value from someone else's `insert` that was executed between your `insert` and `select max()`; for Sybase ASE the `select @@identity` should suffice ... assuming you're not firigng a trigger that is running additional inserts of rows with an identity column => then `@@identity` is going to be overwritten based on the trigger's `insert`) – markp-fuso Nov 04 '21 at 19:56
  • @markp-fuso: quite right about `@@Identity`, somehow I thought it returned the last identity value generated, period (which would make it even worse than `Select Max()` because someone else could `Insert` in another table). Thanks! – Jean-David Lanz Nov 05 '21 at 09:14
  • @markp-fuso: I'm trying to find the info you're asking for about the database, but I'm not sure where to look. (I only have access to the database through a VS connection string, or SQL developer, or Sybase Central, whose Help or Properties don't give me enough clues to work with.) – Jean-David Lanz Nov 05 '21 at 09:16

0 Answers0