Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
On May 10, 2012, at 3:39 AM, Anthony Kong wrote: I think I have found the root cause of the problem. I am using a sybase database server of version 15 (Adaptive Server Enterprise/15.0.3/EBF 17163) But for various reasons we are still using sybase ODBC driver 12.5. If I switch to 15.5, it just works as expected. A follow-up question: Says I must stay with driver 12.5, what are the possible workaround for this issue? I think there are at least two possible solutions: A) Use direct query to insert and retrieve @@identity back form the server B) 'hack' the self.dispatch in session to retrieve @@identity and assign it to the 'id' field in flush() event Do they sound plausible? Are there any pointers/resource on how to achieve the above? not sure what the issue with 12.5 is, SQLAlchemy issues @@identity after the INSERT in the same way. If it doesn't work with sqlalchemy, it won't work without it, either, unless the driver has some other workaround which can be implemented. The call to @@identity happens at a very particular place and can't be re-implemented on the outside. Cheers On Thursday, May 10, 2012 7:43:43 AM UTC+10, Anthony Kong wrote: 1) Yes, I am working with existing table 2) The id column is not included in the SQL, which is the expected behaviour if we want the server to generate value for the IDENTITY column in sybase/ms-sql 3) ok Any tracing option I can use to see how SQLA deals with the 'id' column? On Thursday, May 10, 2012 5:09:59 AM UTC+10, Michael Bayer wrote: On May 9, 2012, at 4:46 PM, Anthony Kong wrote: Hi, Micheal, Thanks for the reply. I have added autoincrement to the Column definition of 'id', like so: id = Column(Integer, name='id_trade', primary_key=True, autoincrement=True) that's fine, but the table has to be created that way in the database also, that is, using the IDENTITY keyword explicitly. if you are dealing with an existing database, that may not be the case. In the log I can see the id column is not included in the INSERT statement. I have taken that SQL statement out and run it, and a row can be successfully created in the table. that's also fine but is there a value for the primary key column ? or is it NULL? Do I need to do anything related to reload? I suspect may be the row cannot be inserted after all but sqlalchemy does not retrieve @@IDENTITY? the work SQLA does with @@IDENTITY may not be present in the log output of SQLAlchemy itself. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/GzOcXxqMSk0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
Hi, Micheal, Thanks for the reply. I have added autoincrement to the Column definition of 'id', like so: id = Column(Integer, name='id_trade', primary_key=True, autoincrement=True) In the log I can see the id column is not included in the INSERT statement. I have taken that SQL statement out and run it, and a row can be successfully created in the table. However, it still throws the same exception in python land. And it follows by a ROLLBACK Do I need to do anything related to reload? I suspect may be the row cannot be inserted after all but sqlalchemy does not retrieve @@IDENTITY? Cheers -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/yhFVlHz44FUJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
On May 9, 2012, at 4:46 PM, Anthony Kong wrote: Hi, Micheal, Thanks for the reply. I have added autoincrement to the Column definition of 'id', like so: id = Column(Integer, name='id_trade', primary_key=True, autoincrement=True) that's fine, but the table has to be created that way in the database also, that is, using the IDENTITY keyword explicitly. if you are dealing with an existing database, that may not be the case. In the log I can see the id column is not included in the INSERT statement. I have taken that SQL statement out and run it, and a row can be successfully created in the table. that's also fine but is there a value for the primary key column ? or is it NULL? Do I need to do anything related to reload? I suspect may be the row cannot be inserted after all but sqlalchemy does not retrieve @@IDENTITY? the work SQLA does with @@IDENTITY may not be present in the log output of SQLAlchemy itself. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
1) Yes, I am working with existing table 2) The id column is not included in the SQL, which is the expected behaviour if we want the server to generate value for the IDENTITY column in sybase/ms-sql 3) ok Any tracing option I can use to see how SQLA deals with the 'id' column? On Thursday, May 10, 2012 5:09:59 AM UTC+10, Michael Bayer wrote: On May 9, 2012, at 4:46 PM, Anthony Kong wrote: Hi, Micheal, Thanks for the reply. I have added autoincrement to the Column definition of 'id', like so: id = Column(Integer, name='id_trade', primary_key=True, autoincrement=True ) that's fine, but the table has to be created that way in the database also, that is, using the IDENTITY keyword explicitly. if you are dealing with an existing database, that may not be the case. In the log I can see the id column is not included in the INSERT statement. I have taken that SQL statement out and run it, and a row can be successfully created in the table. that's also fine but is there a value for the primary key column ? or is it NULL? Do I need to do anything related to reload? I suspect may be the row cannot be inserted after all but sqlalchemy does not retrieve @@IDENTITY? the work SQLA does with @@IDENTITY may not be present in the log output of SQLAlchemy itself. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/I6OriBExVeAJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
I think I have found the root cause of the problem. I am using a sybase database server of version 15 (Adaptive Server Enterprise/15.0.3/EBF 17163) But for various reasons we are still using sybase ODBC driver 12.5. If I switch to 15.5, it just works as expected. *A follow-up question**:* ** Says I must stay with driver 12.5, what are the possible workaround for this issue? I think there are at least two possible solutions: A) Use direct query to insert and retrieve @@identity back form the server B) 'hack' the self.dispatch in session to retrieve @@identity and assign it to the 'id' field in flush() event Do they sound plausible? Are there any pointers/resource on how to achieve the above? Cheers On Thursday, May 10, 2012 7:43:43 AM UTC+10, Anthony Kong wrote: 1) Yes, I am working with existing table 2) The id column is not included in the SQL, which is the expected behaviour if we want the server to generate value for the IDENTITY column in sybase/ms-sql 3) ok Any tracing option I can use to see how SQLA deals with the 'id' column? On Thursday, May 10, 2012 5:09:59 AM UTC+10, Michael Bayer wrote: On May 9, 2012, at 4:46 PM, Anthony Kong wrote: Hi, Micheal, Thanks for the reply. I have added autoincrement to the Column definition of 'id', like so: id = Column(Integer, name='id_trade', primary_key=True, autoincrement=True) that's fine, but the table has to be created that way in the database also, that is, using the IDENTITY keyword explicitly. if you are dealing with an existing database, that may not be the case. In the log I can see the id column is not included in the INSERT statement. I have taken that SQL statement out and run it, and a row can be successfully created in the table. that's also fine but is there a value for the primary key column ? or is it NULL? Do I need to do anything related to reload? I suspect may be the row cannot be inserted after all but sqlalchemy does not retrieve @@IDENTITY? the work SQLA does with @@IDENTITY may not be present in the log output of SQLAlchemy itself. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/GzOcXxqMSk0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
