NOTE: I attempted to send the forwarded message below for the holidays, but it got bounced. I've re-joined the mailing list, so I'm hoping this one gets through this time....
After reading through some of the Sybase specific code, and stumbling upon the reference to is_auto_increment, I thought I had solved the problem below. However, the generated SQL is not apparently syntactically correct, at least Sybase it spitting it back at me: SELECT "me"."name", "me"."regex", "me"."description", "me"."id" FROM "ffrule" "me" WHERE ( "name" = ? ): 'cores' BEGIN WORK INSERT INTO "ffrule" ( "description", "name", "regex") VALUES ( ?, ?, ? ) SELECT MAX("id") FROM "ffrule": 'Core files', 'cores', '^core$' ct_result(ct_dynamic(CS_PREPARE)) returned -205 at /usr/efs/lib/perl5/DBD/Sybase.pm line 138. DBIx::Class::ResultSet::create(): DBI Exception: DBD::Sybase::db prepare_cached failed: Server message number=102 severity=15 state=181 line=1 \ server=RPCORE procedure=DBD3 text=Incorrect syntax near '('. Server message number=102 severity=15 state=181 line=2 server=RPCORE procedure=DBD3 text=Incorrect syntax near 'FROM'. [for Statement "INSERT INTO "ffrule" ( "description", "name", "regex") VALUES ( ?, ?, ? ) SELECT MAX("id") FROM "ffrule""] at blib/lib/EFS/CLI/ForbiddenFileRule.pm line 38 DBIx::Class::Carp::__ANON__(): A DBIx::Class::Storage::TxnScopeGuard went out of scope without explicit commit or error. Rolling back. at /usr/\ efs/lib/perl5/DBIx/Class/Storage/TxnScopeGuard.pm line 132 ROLLBACK If I remove the specification of is_auto_increment, then the code fails as described below, but it at least is able to generate syntactically correct SQL to insert values, even if it fails to retrieve the generated id columns. For example, the above SQL in that case is: SELECT "me"."name", "me"."regex", "me"."description", "me"."id" FROM "ffrule" "me" WHERE ( "name" = ? ): 'cores' INSERT INTO "ffrule" ( "description", "name", "regex") VALUES ( ?, ?, ? ): 'Core files', 'cores', '^core$' That works, in the sense that I end up with a new row in the database, but fails, because the id values aren't expanded. One of the reasons I use DBIC so heavily is that I am not much a SQL expert, so it's not clear to me what's wrong with the above syntax, and I need some suggestions on where I might have screwed up, or what other special Sybase config I might be missing. Any and all suggestions for additional debugging would be most welcome. NOTE: in the message below, I was mistaken about one important fact: the id columns were NOT being expanded at all. Further investigation showed that the test suite implodes the first time I actually try to use one to define a new row which has a foreign key that refers to one of these id values, and that was what exposed the fact that they are not expended. ---------- Forwarded message ---------- From: Phillip Moore <w.phillip.mo...@gmail.com> Date: Thu, Dec 20, 2012 at 12:39 PM Subject: Sybase identity column value not being retrieved during create() To: "DBIx::Class user and developer list" <dbix-class@lists.scsys.co.uk> I am in the middle of porting a fairly large DBIx::Class application to Sybase (using ASE 15.7), which thanks to this fantastic framework, has been fairly straight forward. However, after getting a few obvious Sybase-specific things fixed, I eagerly fired up the entire test suite for my application, and ran into the following problem. First of all, I am using the latest and greatest release of DBIx::Class, and all of it's dependencies, all freshly installed from CPAN as of early this week, and the code is run on a RHEL5 server, using perl5.14.2. All of my tables are defined with the same "id" column as the identity, for example: create table region ( id numeric(38,0) identity, name varchar(24) not null, description varchar(1024) not null, constraint region_pk primary key (id) ); All of the Result classes, have the same structure to define the various relationships and columns: __PACKAGE__->table( q{region} ); __PACKAGE__->add_columns( qw( name description ) ); __PACKAGE__->add_columns( id => { __PACKAGE__->get_id_column_attrs, }, ); __PACKAGE__->set_primary_key( q{id} ); __PACKAGE__->sequence( q{region_seq} ); NOTE: get_id_column_attrs just returns a list of vendor-specific attributes required by the id columns. When using Oracle, we have to specify auto_nextval, but for Sybase, I'm specifying nothing. Objects get created using the create() method, and thus far, in most cases, for most of these objects, the "id" column is properly retrieved, and available vie the object method of the same name. The test suite cranked through the first few setup tasks, but then fails when it creates objects in one of these tables, reproducibly. I have been trying to find something significantly different between how that table is defined, and the others, but they ALL use the same basic template. ALL have "id" as the primary column, and except for this one table, works as expected. When an object is created in that table, the id column IS being defined as expected (looking in the database shows it to be present as expected), yet the ->id() method returns undef. My question really is: how does one go about debugging this? I can't seen to get much out of DBIC at all -- if I set DBIC_TRACE, I see the expected SQL: BEGIN WORK INSERT INTO "history" ( "authuser", "command", "efshost", "efsversion", "logfile", "pid", "request", "starttime") VALUES ( ?, ?, ?, ?, ?, ?, ?,\ ? ): 'efsops', 'showcerts sslserver', 'rpefst01.test.efs', '2.999104', '/var/efs/logs/2012/12/20/1356010525.0', '18577', 'showcerts sslserver \ -serverhost rpefst01.test.efs', '12/20/2012 13:35:36.000' COMMIT And if I peek at Sybase I can cleary see that row, and it clearly has an id column value, that's the next expected value. How do I dig into the behavior of DBIC to understand why the id column is not being expanded as expected? I will happily provide any additional debugging information that might help shed some light on this problem. In the meantime, I'm trying to create a simple test case for this, but it's VERY difficult to pull subsets of this code out of the framework it lives in, and make it useful. For completeness, here's the complete definition of the history table, and it's class definition. create table history ( id numeric(38,0) identity, parent numeric(38,0) null, command varchar(32) not null, request text not null, starttime datetime null, logfile varchar(1024) null, authuser varchar(32) not null, efshost varchar(128) not null, efsversion varchar(8) not null, endtime datetime null, status integer null, pid numeric(6) default 0, release varchar(128) null, region varchar(24) null, campus varchar(64) null, location varchar(128) null, cell varchar(256) null, metaproj varchar(32) null, project varchar(64) null, "install" varchar(128) null, releasealias varchar(128) null, target varchar(128) null, hosttype varchar(8) null, stage varchar(64) null, username varchar(8) null, groupname varchar(8) null, userid numeric(12) null, groupid numeric(12) null, platform varchar(64) null, platforminstance varchar(64) null, platformalias varchar(64) null, platforminstancealias varchar(64) null, platformbuild varchar(64) null, backwards varchar(64) null, emulates varchar(64) null, fileserver varchar(128) null, fileshare varchar(128) null, fileqtree varchar(128) null, filetype varchar(4) null, efsserver varchar(256) null, domain varchar(512) null, business varchar(24) null, division varchar(64) null, pod varchar(256) null, hostclass varchar(128) null, cabinet varchar(256) null, feature varchar(64) null, parameter varchar(64) null, ffrule varchar(64) null, home varchar(8) null, constraint history_pk primary key (id) ); __PACKAGE__->table( q{history} ); our @other_keys = ( # Other keys from all the other tables qw( region campus location cell metaproj project release install releasealias target hosttype stage username groupname userid groupid platform platformalias platforminstance platforminstancealias backwards emulates fileserver fileshare fileqtree filetype efsserver domain home business division pod hostclass cabinet feature ffrule ) ); our @history_values = ( # History specific columns qw( command request logfile authuser efshost efsversion status pid parent ) ); __PACKAGE__->add_columns( @other_keys, @history_values ); __PACKAGE__->add_columns( id => { __PACKAGE__->get_id_column_attrs, }, starttime => { data_type => q{datetime}, set_on_create => 1, }, endtime => { data_type => q{datetime}, set_on_update => 1, }, ); __PACKAGE__->set_primary_key( q{id} ); __PACKAGE__->sequence( q{history_seq} ); __PACKAGE__->has_many( diststats => q{EFS::DBI::Result::DistStats} ); __PACKAGE__->belongs_to( parent => __PACKAGE__ ); _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk