[Dbix-class] any Sybase users around?

[Rafael Kitover]

Hello, I've done some hacking last night on getting various Sybase
stuffs to work.

I have a branch you can play with, though things are a bit rough around
the edges and need to be made more configurable, it should generally
work:

svn co \
http://dev.catalyst.perl.org/repos/bast/DBIx-Class/dbic/0.08/branches/sybase \
dbic-sybase

What's new:

* auto-pk for identity columns (this went into trunk too)
* placeholder support on Sybase versions that have it, which means you
  get query caching but no last_insert_id (emulated with select max(col),
  which should be good enough.)
* InflateColumn::DateTime support

What's old:

* limits/offset still don't work (more on that below)
* quoting stuffs is still not fixed (don't know much about that, only been
 using Sybase for a day!)

Please try it out and let me know what you think.

THINGS THAT NEED MORE DISCUSSION FROM REAL SYBASE USERS:

-- PLACEHOLDERS

The branch code checks $dbh->{syb_dynamic_supported} to decide whether
or not to enable placeholder support.

There are advantages and disadvantages to having vs. not having it, so
it should really be configurable, through storage_type or a connect_info
option, WILL FIX.

On older versions of Sybase, using placeholders can actually be slower,
however on new versions it is significantly faster.

This is discussed in detail here:
http://search.cpan.org/~mewp/DBD-Sybase-1.09/Sybase.pm#Using_?_Placeholders_&_bind_parameters_to_$sth->execute

When not using placeholders (NoBindVars), it is possible to get the
last_insert_id using 'select @@identity', when using placeholders it
isn't possible and is emulated using 'select max(col)'.

This is slower of course, but since PKs are indexed it shouldn't be too
big of a deal.

Multiple autoinc columns are not an issue because Sybase can only have
one identity column per table.

-- NoBindVars and select @@identity

Can I do $dbh->prepare_cached('select @@identity') (for when not using
 placeholders) or is that completely pointless?

-- DATES

The code is in DBIx::Class::Storage::DBI::Sybase::DateTime currently,
but needs to be factored out into a DateTime::Format::Sybase module,
which I'll try to do soon.

For output format, the most precise available in DBD::Sybase is ISO or
ISO_strict, I used ISO_strict:

2004-08-21T14:36:48.080Z
pattern => '%Y-%m-%dT%H:%M:%S.%3NZ'

Using $dbh->syb_date_fmt('ISO_strict') on connection.

More on this in the DBD::Sybase perldoc:
http://search.cpan.org/~mewp/DBD-Sybase-1.09/Sybase.pm#Controlling_DATETIME_output_formats

For input format I used $dbh->do('set dateformat mdy') and

pattern => '%m/%d/%Y %H:%M:%S.%3N'

There's no way (that I saw) to have the same format for both inflation
and deflation.

Since it really shouldn't be executing things by default (or without a
 way to turn it off) this will be made configurable.

On input date formats:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/25357;pt=25420

On input time formats:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=25357?target=%25N_25420_START_RESTART_N%25

Full input datetime format is "$date $time"

I didn't add support for SMALLDATETIME, this would either require IC::DT
changes, or making DATETIME map to a "timestamp" format and
SMALLDATETIME to a "date" format, which makes more sense, as
SMALLDATETIME has up to minute precision, while DATETIME has millisecond
precision.

Which would be the best solution?

SQL::Translator maps "timestamp" to DATETIME, but doesn't have a
mapping for SMALLDATETIME apparently in the Sybase producer.

Types:
http://www.colostate.edu/Services/ACNS/swmanuals/sasdoc/sashtml/accdb/z0439559.htm

-- LIMITS

There doesn't seem to be a good way to get a slice of a resultset with
offset in Sybase.

There's a discussion here:
http://www.dbforums.com/sybase/1616373-sybases-rownum-function.html

Some versions are supposed to have a ROWID function, but the version I
used: http://www.sybase.com/linuxpromo (page does not always load) does
not have it.

E.g.:

select rowid(table), table.* from table

Some versions of Sybase have TOP support, however, Sybase does not
support ORDER BY in a subquery (which breaks SQL::Abstract::Limit's TOP
support with order_by.)

E.g.:

select top 25 * from table

Another way is to set the session variable rowcount, this doesn't
support offsets however.

E.g.:

set rowcount 5
select * from table

The most promising method seemed to be a temp table with an IDENTITY
column, however, this breaks if you already have an IDENTITY column in
your table.

E.g:

select rownum=identity(10), * into #tempbar from bar
select * from #tempbar where rownum between 2 and 5
drop table #tempbar

Perhaps we can deploy some sort of iterator function and use an int
rownum column for the temp table. That seems to be the only way.

-- QUOTING

If I understand the issue correctly, this fails:

create table bar (baz int, quux int)
insert into bar (baz) values ('1')
Msg 257, Level 16, State 1:
Server 'HLAGH', Line 1:
Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.  Use the
CONVERT function to run this query.

There's a sybase_noquote branch, haven't looked at it yet.

I'm Caelum on irc.perl.org #dbix-class by the way.