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. _______________________________________________ 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/[email protected]
