[Dbix-class] Sybase ASE 15.7 Issue: Quoted Identifiers and compound SQL statements

[Phillip Moore]

I've been working with some of the Sybase experts at work, and they
have fed me a few ideas that helped me get to what I think it a pretty
significant clue.   For context, this is a continuation of the recent
thread:

     Sybase identity column value not being retrieved during create()

Turns out that subject is misleading, as the problems more subtle than that.

First observation: nowhere in the t/746sybase.t tests are
quoted_identifiers used.  THAT is where the crux of the problem lies,
and the text below (extracted from the mail thread I had at work),
explains my finding.

Summary: if you use quoted_identifiers, then for some reason I can't
explain, the compound INSERT/SELECT statement that is auto-generated
by DBIx::Class fails, but only if you specify quoted_identifiers.
However, I also show in my tests that a plain INSERT using
quoted_identifiers actually works.   I found that by accident, by
mistakenly omitting the is_auto_increment attribute for my id columns,
and the test suite in my fake distro leverages this fact to
demonstrate that quoted_identifiers actually work.

The attached mini-distro shows the problem, but I suspect noone out
there has a Sybase server to test this against.

This setup is on dataless VMs I build for testing my application, and
I have no problem granting temporary access to anyone who's interested
in looking into this further.  Alternately, if anyone has further
suggestions for where to start debugging, let me know.

> OK, the test results below show a bit more about the problem.
>  It's hard to read, so I'll walk through it.
>
> I have a test case which reproduces the problem.  I try
> creating the object with and without the use of
> quoted_identifiers, and then I try it with and without
> telling DBIx::Class that the "id" colume is an auto_increment
> column.  Note that the underlying Sybase table is *identical*
> in this case, other than the table name, however I simply use
> a different DBIx::Class specification to access it.   The
> "is_auto_increment" attribute, when set, is what controls how
> DBIx::Class generates the SQL to get the id value that has
> just been inserted.
>
> (a) Set is_auto_increment, but don't use quoted_identifiers:
>
> This SQL is generated, and it works:
>
> BEGIN WORK
> INSERT INTO withauto ( name) VALUES ( ? ) SELECT MAX(id) FROM
> withauto: 'no'
> COMMIT
>
> (b) Do NOT set is_auto_increment, and don't use quoted_identifiers:
>
> This SQL is generated, and it works:
>
> INSERT INTO withoutauto ( name) VALUES ( ? ): 'no'
>
> (c) Set is_auto_increment, and DO use quoted_identifiers:
>
> This SQL is generated:
>
> BEGIN WORK
> INSERT INTO "withauto" ( "name") VALUES ( ? ) SELECT
> MAX("id") FROM "withauto": 'yes'
>
> But it results in this error from the server:
>
> DBIx::Class::ResultSet::create(): DBI Exception:
> DBD::Sybase::db prepare_cached failed: Server message
> number=3D102 severity=3D15 state=3D1 81 line=3D1 server=3DRPCORE
> procedure=3DDBD7 text=3DIncorrect syntax near '('.
> Server message number=3D102 severity=3D15 state=3D181 line=3D2
> server=3DRPCORE procedure=3DDBD7 text=3DIncorrect syntax near 'FROM'.
> [for Statement "INSERT INTO "withauto" ( "name") VALUES ( ? )
> SELECT MAX("id") FROM "withauto""] at t/create.t line 89
>
> (d) Do NOT set is_auto_increment, and DO use quoted_identifiers:
>
> This SQL is generated, and it works:
>
> INSERT INTO "withoutauto" ( "name") VALUES ( ? ): 'yes'
>
> OK, so what have I concluded from all of this, other than the
> fact that I don't understand Sybase or SQL very well :-/
>
> First of all, this clearly shows that the placeholders (?) DO
> WORK, via the Sybase client libraries.  There's some
> client-side magic happening, because you can NOT run that
> same SQL string via isql; you get syntax errors (if you would
> like, I can demonstrate this).
>
> Second, there is at least one example where the use of
> quoted_identifiers works as expected.
>
> Third, there is at least one example where the compound
> INSERT/SELECT syntax, WITH the use of placeholders, works.
>
> It is only when the compound INSERT/SELECT is used, WITH
> placeholders, AND with quoted_identifiers, that the SQL fails.
>
> Now what?
>
> > -----Original Message-----
> > From: Phillip Moore [mailto:w.phillip.moore at gmail.com]
> > Sent: Friday, January 11, 2013 9:44 AM
> > To: Moore, Phillip (Enterprise Infrastructure)
> > Subject: sample text 4
> >
> > efstest at rpefst01$ make test
> > PERL_DL_NONLAZY=3D1 /usr/efs-2012-12-14/bin/perl
> > "-MExtUtils::Command::MM" "-e" "test_harness(0, 'inc', 'blib/lib',
> > 'blib/arch')" t/*.t t/create.t .. BEGIN WORK INSERT INTO withauto (
> > name) VALUES ( ? ) SELECT MAX(id) FROM
> > withauto: 'no'
> > COMMIT
> > t/create.t .. 1/? INSERT INTO withoutauto ( name) VALUES ( ? ): 'no'
> > t/create.t .. 3/? BEGIN WORK
> > INSERT INTO "withauto" ( "name") VALUES ( ? ) SELECT
> > MAX("id") FROM "withauto": 'yes'
> > ct_result(ct_dynamic(CS_PREPARE)) returned -205 at
> > /usr/efs/lib/perl5/DBD/Sybase.pm line 138.
> > ROLLBACK
> > t/create.t .. 5/? DBIx::Class::ResultSet::create(): DBI Exception:
> > DBD::Sybase::db prepare_cached failed: Server message number=3D102
> > severity=3D15 state=3D1\
> > 81 line=3D1 server=3DRPCORE procedure=3DDBD7 text=3DIncorrect
> syntax near '('.
> >  Server message number=3D102 severity=3D15 state=3D181 line=3D2
> server=3DRPCORE
> > procedure=3DDBD7 text=3DIncorrect syntax near 'FROM'.
> >   [for Statement "INSERT INTO "withauto" ( "name") VALUES ( ?
> > ) SELECT MAX("id") FROM "withauto""] at t/create.t line 89
> >
> > #   Failed test 'SQL works for quote_char of '"', class of WithAuto'
> > #   at t/create.t line 95.
> >
> > #   Failed test 'id expands for quote_char of '"', class of
> WithAuto'
> > #   at t/create.t line 100.
> > INSERT INTO "withoutauto" ( "name") VALUES ( ? ): 'yes'
> > t/create.t .. 7/? # Looks like you failed 2 tests of 8.
> > t/create.t .. Dubious, test returned 2 (wstat 512, 0x200)
> Failed 2/8
> > subtests
> >
> > Test Summary Report
> > -------------------
> > t/create.t (Wstat: 512 Tests: 8 Failed: 2)
> >   Failed tests:  5-6
> >   Non-zero exit status: 2
> > Files=3D1, Tests=3D8, 14 wallclock secs ( 0.01 usr  0.01 sys +
> 0.16 cusr
> > 0.07 csys =3D  0.25 CPU)
> > Result: FAIL
> > Failed 1/1 test programs. 2/8 subtests failed.
> > make: *** [test_dynamic] Error 2
> > [PWD =3D ~/dev/efs/core/dbic-sample]
> >

---------------------------------------------------------------------------=
-----

NOTICE: Morgan Stanley is not acting as a municipal advisor and the
opinions or views contained herein are not intended to be, and do not
constitute, advice within the meaning of Section 975 of the Dodd-Frank
Wall Street Reform and Consumer Protection Act. If you have received
this communication in error, please destroy all electronic and paper
copies and notify the sender immediately. Mistransmission is not
intended to waive confidentiality or privilege. Morgan Stanley
reserves the right, to the extent permitted under applicable law, to
monitor electronic communications. This message is subject to terms
available at the following link:
http://www.morganstanley.com/disclaimers. If you cannot access these
links, please notify us by reply message and we will send the contents
to you. By messaging with Morgan Stanley you consent to the foregoing.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: DBICSybaseTest-0.2.tar.gz
Type: application/x-gzip
Size: 19246 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130111/45=
731d92/DBICSybaseTest-0.2.tar-0001.bin