[Dbix-class] Fwd: Sybase identity column value not being retrieved
during create()
Phillip Moore
w.phillip.moore at gmail.com
Wed Jan 9 15:27:52 GMT 2013
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.moore at 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 at 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__ );
More information about the DBIx-Class
mailing list