1 package DBIx::Class::Storage::DBI::Sybase::ASE;
7 DBIx::Class::Storage::DBI::Sybase
8 DBIx::Class::Storage::DBI::AutoCast
9 DBIx::Class::Storage::DBI::IdentityInsert
12 use DBIx::Class::Carp;
13 use Scalar::Util qw/blessed weaken/;
16 use Context::Preserve 'preserve_context';
17 use DBIx::Class::_Util qw( sigwarn_silencer dbic_internal_try dump_value scope_guard );
20 __PACKAGE__->sql_limit_dialect ('GenericSubQ');
21 __PACKAGE__->sql_quote_char ([qw/[ ]/]);
22 __PACKAGE__->datetime_parser_type(
23 'DBIx::Class::Storage::DBI::Sybase::ASE::DateTime::Format'
26 __PACKAGE__->mk_group_accessors('simple' =>
27 qw/_identity _identity_method _blob_log_on_update _parent_storage
28 _writer_storage _is_writer_storage
29 _bulk_storage _is_bulk_storage _began_bulk_work
34 my @also_proxy_to_extra_storages = qw/
35 connect_call_set_auto_cast auto_cast connect_call_blob_setup
36 connect_call_datetime_setup
38 disconnect _connect_info _sql_maker _sql_maker_opts disable_sth_caching
39 auto_savepoint unsafe cursor_class debug debugobj schema
44 DBIx::Class::Storage::DBI::Sybase::ASE - Sybase ASE SQL Server support for
49 This subclass supports L<DBD::Sybase> for real (non-Microsoft) Sybase databases.
53 If your version of Sybase does not support placeholders, then your storage will
54 be reblessed to L<DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars>.
55 You can also enable that driver explicitly, see the documentation for more
58 With this driver there is unfortunately no way to get the C<last_insert_id>
59 without doing a C<SELECT MAX(col)>. This is done safely in a transaction
60 (locking the table.) See L</INSERTS WITH PLACEHOLDERS>.
62 A recommended L<connect_info|DBIx::Class::Storage::DBI/connect_info> setting:
64 on_connect_call => [['datetime_setup'], ['blob_setup', log_on_update => 0]]
73 my $no_bind_vars = __PACKAGE__ . '::NoBindVars';
75 if ($self->_using_freetds) {
76 carp_once <<'EOF' unless $ENV{DBIC_SYBASE_FREETDS_NOWARN};
78 You are using FreeTDS with Sybase.
80 We will do our best to support this configuration, but please consider this
83 TEXT/IMAGE columns will definitely not work.
85 You are encouraged to recompile DBD::Sybase with the Sybase Open Client libraries
88 See perldoc DBIx::Class::Storage::DBI::Sybase::ASE for more details.
90 To turn off this warning set the DBIC_SYBASE_FREETDS_NOWARN environment
94 if (not $self->_use_typeless_placeholders) {
95 if ($self->_use_placeholders) {
99 $self->ensure_class_loaded($no_bind_vars);
100 bless $self, $no_bind_vars;
106 elsif (not $self->_get_dbh->{syb_dynamic_supported}) {
107 # not necessarily FreeTDS, but no placeholders nevertheless
108 $self->ensure_class_loaded($no_bind_vars);
109 bless $self, $no_bind_vars;
112 # this is highly unlikely, but we check just in case
113 elsif (not $self->_use_typeless_placeholders) {
121 $self->next::method(@_);
123 if ($self->_using_freetds && (my $ver = $self->_using_freetds_version||999) > 0.82) {
125 "Buggy FreeTDS version $ver detected, statement caching will not work and "
126 . 'will be disabled.'
128 $self->disable_sth_caching(1);
131 $self->_set_max_connect(256);
133 # create storage for insert/(update blob) transactions,
134 # unless this is that storage
135 return if $self->_parent_storage;
137 my $writer_storage = (ref $self)->new;
139 $writer_storage->_is_writer_storage(1); # just info
140 $writer_storage->connect_info($self->connect_info);
141 $writer_storage->auto_cast($self->auto_cast);
143 weaken ($writer_storage->{_parent_storage} = $self);
144 $self->_writer_storage($writer_storage);
146 # create a bulk storage unless connect_info is a coderef
147 return if ref($self->_dbi_connect_info->[0]) eq 'CODE';
149 my $bulk_storage = (ref $self)->new;
151 $bulk_storage->_is_bulk_storage(1); # for special ->disconnect acrobatics
152 $bulk_storage->connect_info($self->connect_info);
155 $bulk_storage->_dbi_connect_info->[0] .= ';bulkLogin=1';
157 weaken ($bulk_storage->{_parent_storage} = $self);
158 $self->_bulk_storage($bulk_storage);
161 for my $method (@also_proxy_to_extra_storages) {
163 no warnings 'redefine';
165 my $replaced = __PACKAGE__->can($method);
167 *{$method} = Sub::Name::subname $method => sub {
169 $self->_writer_storage->$replaced(@_) if $self->_writer_storage;
170 $self->_bulk_storage->$replaced(@_) if $self->_bulk_storage;
171 return $self->$replaced(@_);
178 # Even though we call $sth->finish for uses off the bulk API, there's still an
179 # "active statement" warning on disconnect, which we throw away here.
180 # This is due to the bug described in _insert_bulk.
181 # Currently a noop because 'prepare' is used instead of 'prepare_cached'.
182 local $SIG{__WARN__} = sigwarn_silencer(qr/active statement/i)
183 if $self->_is_bulk_storage;
185 # so that next transaction gets a dbh
186 $self->_began_bulk_work(0) if $self->_is_bulk_storage;
191 # This is only invoked for FreeTDS drivers by ::Storage::DBI::Sybase::FreeTDS
192 sub _set_autocommit_stmt {
193 my ($self, $on) = @_;
195 return 'SET CHAINED ' . ($on ? 'OFF' : 'ON');
198 # Set up session settings for Sybase databases for the connection.
200 # Make sure we have CHAINED mode turned on if AutoCommit is off in non-FreeTDS
201 # DBD::Sybase (since we don't know how DBD::Sybase was compiled.) If however
202 # we're using FreeTDS, CHAINED mode turns on an implicit transaction which we
203 # only want when AutoCommit is off.
204 sub _run_connection_actions {
207 if ($self->_is_bulk_storage) {
208 # this should be cleared on every reconnect
209 $self->_began_bulk_work(0);
213 $self->_dbh->{syb_chained_txn} = 1
214 unless $self->_using_freetds;
216 $self->next::method(@_);
219 =head2 connect_call_blob_setup
223 on_connect_call => [ [ 'blob_setup', log_on_update => 0 ] ]
225 Does C<< $dbh->{syb_binary_images} = 1; >> to return C<IMAGE> data as raw binary
226 instead of as a hex string.
230 Also sets the C<log_on_update> value for blob write operations. The default is
231 C<1>, but C<0> is better if your database is configured for it.
234 L<DBD::Sybase/Handling IMAGE/TEXT data with syb_ct_get_data()/syb_ct_send_data()>.
238 sub connect_call_blob_setup {
241 my $dbh = $self->_dbh;
242 $dbh->{syb_binary_images} = 1;
244 $self->_blob_log_on_update($args{log_on_update})
245 if exists $args{log_on_update};
249 my ($self, $source, $column) = @_;
251 return $self->_is_lob_type($source->column_info($column)->{data_type});
254 sub _prep_for_execute {
255 my ($self, $op, $ident, $args) = @_;
257 my $limit; # extract and use shortcut on limit without offset
258 if ($op eq 'select' and ! $args->[4] and $limit = $args->[3]) {
263 my ($sql, $bind) = $self->next::method($op, $ident, $args);
265 # $limit is already sanitized by now
267 "SET ROWCOUNT $limit",
272 if (my $identity_col = $self->_perform_autoinc_retrieval) {
273 $sql .= "\n" . $self->_fetch_identity_sql($ident, $identity_col)
276 return ($sql, $bind);
279 sub _fetch_identity_sql {
280 my ($self, $source, $col) = @_;
282 return sprintf ("SELECT MAX(%s) FROM %s",
283 map { $self->sql_maker->_quote ($_) } ($col, $source->from)
287 # Stolen from SQLT, with some modifications. This is a makeshift
288 # solution before a sane type-mapping library is available, thus
289 # the 'our' for easy overrides.
290 our %TYPE_MAPPING = (
293 varchar => 'varchar',
294 varchar2 => 'varchar',
295 timestamp => 'datetime',
297 real => 'double precision',
300 tinyint => 'smallint',
301 float => 'double precision',
303 bigserial => 'numeric',
304 boolean => 'varchar',
308 sub _native_data_type {
309 my ($self, $type) = @_;
312 $type =~ s/\s* identity//x;
314 return uc($TYPE_MAPPING{$type} || $type);
320 my ($rv, $sth, @bind) = $self->next::method(@_);
322 $self->_identity( ($sth->fetchall_arrayref)->[0][0] )
323 if $self->_perform_autoinc_retrieval;
325 return wantarray ? ($rv, $sth, @bind) : $rv;
328 sub last_insert_id { shift->_identity }
330 # handles TEXT/IMAGE and transaction for last_insert_id
333 my ($source, $to_insert) = @_;
335 my $columns_info = $source->columns_info;
337 my ($identity_col) = grep
338 { $columns_info->{$_}{is_auto_increment} }
342 $identity_col = '' if ! defined $identity_col;
344 # FIXME - this is duplication from DBI.pm. When refactored towards
345 # the LobWriter this can be folded back where it belongs.
346 local $self->{_autoinc_supplied_for_op} = exists $to_insert->{$identity_col}
351 local $self->{_perform_autoinc_retrieval} = $self->{_autoinc_supplied_for_op}
356 # check for empty insert
357 # INSERT INTO foo DEFAULT VALUES -- does not work with Sybase
358 # try to insert explicit 'DEFAULT's instead (except for identity, timestamp
359 # and computed columns)
360 if (not %$to_insert) {
361 for my $col ($source->columns) {
362 next if $col eq $identity_col;
364 my $info = $source->column_info($col);
366 next if ref $info->{default_value} eq 'SCALAR'
367 || (exists $info->{data_type} && (not defined $info->{data_type}));
369 next if $info->{data_type} && $info->{data_type} =~ /^timestamp\z/i;
371 $to_insert->{$col} = \'DEFAULT';
375 my $blob_cols = $self->_remove_blob_cols($source, $to_insert);
377 # if a new txn is needed - it must happen on the _writer/new connection (for now)
380 ! $self->transaction_depth
385 # do we need the horrific SELECT MAX(COL) hack?
387 $self->_perform_autoinc_retrieval
389 ( ($self->_identity_method||'') ne '@@IDENTITY' )
393 $self = $self->_writer_storage;
394 $guard = $self->txn_scope_guard;
397 my $updated_cols = $self->next::method ($source, $to_insert);
399 $self->_insert_blobs (
404 ? ( $identity_col => $self->last_insert_id($source, $identity_col) )
412 $guard->commit if $guard;
414 return $updated_cols;
419 my ($source, $fields, $where, @rest) = @_;
422 # When *updating* identities, ASE requires SET IDENTITY_UPDATE called
424 if (my $blob_cols = $self->_remove_blob_cols($source, $fields)) {
426 # If there are any blobs in $where, Sybase will return a descriptive error
428 # XXX blobs can still be used with a LIKE query, and this should be handled.
430 # update+blob update(s) done atomically on separate connection
431 $self = $self->_writer_storage;
433 my $guard = $self->txn_scope_guard;
435 # First update the blob columns to be updated to '' (taken from $fields, where
436 # it is originally put by _remove_blob_cols .)
437 my %blobs_to_empty = map { ($_ => delete $fields->{$_}) } keys %$blob_cols;
439 # We can't only update NULL blobs, because blobs cannot be in the WHERE clause.
440 $self->next::method($source, \%blobs_to_empty, $where, @rest);
442 # Now update the blobs before the other columns in case the update of other
443 # columns makes the search condition invalid.
444 my $rv = $self->_update_blobs($source, $blob_cols, $where);
448 # Now set the identity update flags for the actual update
449 local $self->{_autoinc_supplied_for_op} = grep
450 { $_->{is_auto_increment} }
451 values %{ $source->columns_info([ keys %$fields ]) }
454 my $next = $self->next::can;
456 return preserve_context {
457 $self->$next(@$args);
458 } after => sub { $guard->commit };
466 # Set the identity update flags for the actual update
467 local $self->{_autoinc_supplied_for_op} = grep
468 { $_->{is_auto_increment} }
469 values %{ $source->columns_info([ keys %$fields ]) }
472 return $self->next::method(@_);
478 my ($source, $cols, $data) = @_;
480 my $columns_info = $source->columns_info;
483 grep { $columns_info->{$_}{is_auto_increment} }
486 # FIXME - this is duplication from DBI.pm. When refactored towards
487 # the LobWriter this can be folded back where it belongs.
488 local $self->{_autoinc_supplied_for_op}
489 = grep { $_ eq $identity_col } @$cols;
492 $self->_bulk_storage &&
493 $self->_get_dbh->{syb_has_blk};
495 if (! $use_bulk_api and ref($self->_dbi_connect_info->[0]) eq 'CODE') {
496 carp_unique( join ' ',
497 'Bulk API support disabled due to use of a CODEREF connect_info.',
498 'Reverting to regular array inserts.',
502 if (not $use_bulk_api) {
503 my $blob_cols = $self->_remove_blob_cols_array($source, $cols, $data);
505 # next::method uses a txn anyway, but it ends too early in case we need to
506 # select max(col) to get the identity for inserting blobs.
507 ($self, my $guard) = $self->transaction_depth
509 : ($self->_writer_storage, $self->_writer_storage->txn_scope_guard)
512 $self->next::method(@_);
515 if ($self->_autoinc_supplied_for_op) {
516 $self->_insert_blobs_array ($source, $blob_cols, $cols, $data);
519 my @cols_with_identities = (@$cols, $identity_col);
521 ## calculate identities
522 # XXX This assumes identities always increase by 1, which may or may not
524 my ($last_identity) =
525 $self->_dbh->selectrow_array (
526 $self->_fetch_identity_sql($source, $identity_col)
528 my @identities = (($last_identity - @$data + 1) .. $last_identity);
530 my @data_with_identities = map [@$_, shift @identities], @$data;
532 $self->_insert_blobs_array (
533 $source, $blob_cols, \@cols_with_identities, \@data_with_identities
538 $guard->commit if $guard;
543 # otherwise, use the bulk API
545 # rearrange @$data so that columns are in database order
546 # and so we submit a full column list
547 my %orig_order = map { $cols->[$_] => $_ } 0..$#$cols;
549 my @source_columns = $source->columns;
551 # bcp identity index is 1-based
552 my ($identity_idx) = grep { $source_columns[$_] eq $identity_col } (0..$#source_columns);
553 $identity_idx = defined $identity_idx ? $identity_idx + 1 : 0;
556 for my $slice_idx (0..$#$data) {
557 push @new_data, [map {
558 # identity data will be 'undef' if not _autoinc_supplied_for_op()
559 # columns with defaults will also be 'undef'
560 exists $orig_order{$_}
561 ? $data->[$slice_idx][$orig_order{$_}]
566 my $proto_bind = $self->_resolve_bindattrs(
569 [ { dbic_colname => $source_columns[$_], _bind_data_slice_idx => $_ }
570 => $new_data[0][$_] ]
571 } (0 ..$#source_columns) ],
575 ## Set a client-side conversion error handler, straight from DBD::Sybase docs.
576 # This ignores any data conversion errors detected by the client side libs, as
577 # they are usually harmless.
578 my $orig_cslib_cb = DBD::Sybase::set_cslib_cb(
579 Sub::Name::subname _insert_bulk_cslib_errhandler => sub {
580 my ($layer, $origin, $severity, $errno, $errmsg, $osmsg, $blkmsg) = @_;
582 return 1 if $errno == 36;
585 "Layer: $layer, Origin: $origin, Severity: $severity, Error: $errno" .
586 ($errmsg ? "\n$errmsg" : '') .
587 ($osmsg ? "\n$osmsg" : '') .
588 ($blkmsg ? "\n$blkmsg" : '');
595 my $bulk = $self->_bulk_storage;
597 my $guard = $bulk->txn_scope_guard;
599 ## FIXME - once this is done - address the FIXME on finish() below
600 ## XXX get this to work instead of our own $sth
601 ## will require SQLA or *Hacks changes for ordered columns
602 # $bulk->next::method($source, \@source_columns, \@new_data, {
603 # syb_bcp_attribs => {
604 # identity_flag => $self->_autoinc_supplied_for_op ? 1 : 0,
605 # identity_column => $identity_idx,
608 my $sql = 'INSERT INTO ' .
609 $bulk->sql_maker->_quote($source->name) . ' (' .
610 # colname list is ignored for BCP, but does no harm
611 (join ', ', map $bulk->sql_maker->_quote($_), @source_columns) . ') '.
612 ' VALUES ('. (join ', ', ('?') x @source_columns) . ')';
614 ## XXX there's a bug in the DBD::Sybase bulk support that makes $sth->finish for
615 ## a prepare_cached statement ineffective. Replace with ->sth when fixed, or
616 ## better yet the version above. Should be fixed in DBD::Sybase .
617 my $sth = $bulk->_get_dbh->prepare($sql,
621 identity_flag => $self->_autoinc_supplied_for_op ? 1 : 0,
622 identity_column => $identity_idx,
628 # FIXME the $sth->finish in _execute_array does a rollback for some
629 # reason. Disable it temporarily until we fix the SQLMaker thing above
630 no warnings 'redefine';
632 local *{ref($sth).'::finish'} = sub {};
634 $self->_dbh_execute_for_fetch(
635 $source, $sth, $proto_bind, \@source_columns, \@new_data
641 $bulk->_query_end($sql);
646 DBD::Sybase::set_cslib_cb($orig_cslib_cb);
648 if ($exception =~ /-Y option/) {
649 my $w = 'Sybase bulk API operation failed due to character set incompatibility, '
650 . 'reverting to regular array inserts. Try unsetting the LC_ALL environment variable'
652 $w .= "\n$exception" if $self->debug;
655 $self->_bulk_storage(undef);
660 # rollback makes the bulkLogin connection unusable
661 $self->_bulk_storage->disconnect;
662 $self->throw_exception($exception);
666 # Make sure blobs are not bound as placeholders, and return any non-empty ones
668 sub _remove_blob_cols {
669 my ($self, $source, $fields) = @_;
673 for my $col (keys %$fields) {
674 if ($self->_is_lob_column($source, $col)) {
675 my $blob_val = delete $fields->{$col};
676 if (not defined $blob_val) {
677 $fields->{$col} = \'NULL';
680 $fields->{$col} = \"''";
681 $blob_cols{$col} = $blob_val
687 return %blob_cols ? \%blob_cols : undef;
690 # same for _insert_bulk
691 sub _remove_blob_cols_array {
692 my ($self, $source, $cols, $data) = @_;
696 for my $i (0..$#$cols) {
697 my $col = $cols->[$i];
699 if ($self->_is_lob_column($source, $col)) {
700 for my $j (0..$#$data) {
701 my $blob_val = delete $data->[$j][$i];
702 if (not defined $blob_val) {
703 $data->[$j][$i] = \'NULL';
706 $data->[$j][$i] = \"''";
707 $blob_cols[$j][$i] = $blob_val
714 return @blob_cols ? \@blob_cols : undef;
718 my ($self, $source, $blob_cols, $where) = @_;
720 my @primary_cols = dbic_internal_try
721 { $source->_pri_cols_or_die }
723 $self->throw_exception("Cannot update TEXT/IMAGE column(s): $_")
730 ! grep { ! defined $where->{$_} } @primary_cols
733 @row_to_update{@primary_cols} = @{$where}{@primary_cols};
734 @pks_to_update = \%row_to_update;
737 my $cursor = $self->select ($source, \@primary_cols, $where, {});
738 @pks_to_update = map {
739 my %row; @row{@primary_cols} = @$_; \%row
743 for my $ident (@pks_to_update) {
744 $self->_insert_blobs($source, $blob_cols, $ident);
749 my ($self, $source, $blob_cols, $row_data) = @_;
751 my $table = $source->name;
753 my @primary_cols = dbic_internal_try
754 { $source->_pri_cols_or_die }
756 $self->throw_exception("Cannot update TEXT/IMAGE column(s): $_")
759 $self->throw_exception('Cannot update TEXT/IMAGE column(s) without primary key values')
760 if grep { ! defined $row_data->{$_} } @primary_cols;
762 # if we are 2-phase inserting a blob - there is nothing to retrieve anymore,
763 # regardless of the previous state of the flag
764 local $self->{_perform_autoinc_retrieval}
765 if $self->_perform_autoinc_retrieval;
767 my %where = map {( $_ => $row_data->{$_} )} @primary_cols;
769 for my $col (keys %$blob_cols) {
770 my $blob = $blob_cols->{$col};
772 my $cursor = $self->select ($source, [$col], \%where, {});
774 my $sth = $cursor->sth;
777 $self->throw_exception(
778 "Could not find row in table '$table' for blob update:\n"
783 # FIXME - it is not clear if this is needed at all. But it's been
784 # there since 2009 ( d867eedaa ), might as well let sleeping dogs
786 weaken( my $wsth = $sth );
787 my $g = scope_guard { $wsth->finish if $wsth };
791 $sth->func('CS_GET', 1, 'ct_data_info') or die $sth->errstr;
794 $sth->func('ct_prepare_send') or die $sth->errstr;
796 my $log_on_update = $self->_blob_log_on_update;
797 $log_on_update = 1 if not defined $log_on_update;
799 $sth->func('CS_SET', 1, {
800 total_txtlen => length($blob),
801 log_on_update => $log_on_update
802 }, 'ct_data_info') or die $sth->errstr;
804 $sth->func($blob, length($blob), 'ct_send_data') or die $sth->errstr;
806 $sth->func('ct_finish_send') or die $sth->errstr;
809 if ($self->_using_freetds) {
810 $self->throw_exception (
811 "TEXT/IMAGE operation failed, probably because you are using FreeTDS: $_"
815 $self->throw_exception($_);
821 sub _insert_blobs_array {
822 my ($self, $source, $blob_cols, $cols, $data) = @_;
824 for my $i (0..$#$data) {
825 my $datum = $data->[$i];
828 @row{ @$cols } = @$datum;
831 for my $j (0..$#$cols) {
832 if (exists $blob_cols->[$i][$j]) {
833 $blob_vals{ $cols->[$j] } = $blob_cols->[$i][$j];
837 $self->_insert_blobs ($source, \%blob_vals, \%row);
841 =head2 connect_call_datetime_setup
845 on_connect_call => 'datetime_setup'
847 In L<connect_info|DBIx::Class::Storage::DBI/connect_info> to set:
849 $dbh->syb_date_fmt('ISO_strict'); # output fmt: 2004-08-21T14:36:48.080Z
850 $dbh->do('set dateformat mdy'); # input fmt: 08/13/1979 18:08:55.080
852 This works for both C<DATETIME> and C<SMALLDATETIME> columns, note that
853 C<SMALLDATETIME> columns only have minute precision.
857 sub connect_call_datetime_setup {
859 my $dbh = $self->_get_dbh;
861 if ($dbh->can('syb_date_fmt')) {
862 # amazingly, this works with FreeTDS
863 $dbh->syb_date_fmt('ISO_strict');
867 'Your DBD::Sybase is too old to support '
868 .'DBIx::Class::InflateColumn::DateTime, please upgrade!';
870 # FIXME - in retrospect this is a rather bad US-centric choice
871 # of format. Not changing as a bugwards compat, though in reality
872 # the only piece that sees the results of $dt object formatting
873 # (as opposed to parsing) is the database itself, so theoretically
874 # changing both this SET command and the formatter definition of
875 # ::S::D::Sybase::ASE::DateTime::Format below should be safe and
878 $dbh->do('SET DATEFORMAT mdy');
883 sub _exec_txn_begin {
886 # bulkLogin=1 connections are always in a transaction, and can only call BEGIN
887 # TRAN once. However, we need to make sure there's a $dbh.
888 return if $self->_is_bulk_storage && $self->_dbh && $self->_began_bulk_work;
890 $self->next::method(@_);
892 $self->_began_bulk_work(1) if $self->_is_bulk_storage;
895 # savepoint support using ASE syntax
897 sub _exec_svp_begin {
898 my ($self, $name) = @_;
900 $self->_dbh->do("SAVE TRANSACTION $name");
903 # A new SAVE TRANSACTION with the same name releases the previous one.
904 sub _exec_svp_release { 1 }
906 sub _exec_svp_rollback {
907 my ($self, $name) = @_;
909 $self->_dbh->do("ROLLBACK TRANSACTION $name");
912 package # hide from PAUSE
913 DBIx::Class::Storage::DBI::Sybase::ASE::DateTime::Format;
915 my $datetime_parse_format = '%Y-%m-%dT%H:%M:%S.%3NZ';
916 my $datetime_format_format = '%m/%d/%Y %H:%M:%S.%3N';
918 my ($datetime_parser, $datetime_formatter);
922 require DateTime::Format::Strptime;
923 $datetime_parser ||= DateTime::Format::Strptime->new(
924 pattern => $datetime_parse_format,
927 return $datetime_parser->parse_datetime(shift);
930 sub format_datetime {
932 require DateTime::Format::Strptime;
933 $datetime_formatter ||= DateTime::Format::Strptime->new(
934 pattern => $datetime_format_format,
937 return $datetime_formatter->format_datetime(shift);
942 =head1 Schema::Loader Support
944 As of version C<0.05000>, L<DBIx::Class::Schema::Loader> should work well with
945 most versions of Sybase ASE.
949 This driver supports L<DBD::Sybase> compiled against FreeTDS
950 (L<http://www.freetds.org/>) to the best of our ability, however it is
951 recommended that you recompile L<DBD::Sybase> against the Sybase Open Client
952 libraries. They are a part of the Sybase ASE distribution:
954 The Open Client FAQ is here:
955 L<http://www.isug.com/Sybase_FAQ/ASE/section7.html>.
957 Sybase ASE for Linux (which comes with the Open Client libraries) may be
958 downloaded here: L<http://response.sybase.com/forms/ASE_Linux_Download>.
960 To see if you're using FreeTDS run:
962 perl -MDBI -le 'my $dbh = DBI->connect($dsn, $user, $pass); print $dbh->{syb_oc_version}'
964 It is recommended to set C<tds version> for your ASE server to C<5.0> in
965 C</etc/freetds/freetds.conf>.
967 Some versions or configurations of the libraries involved will not support
968 placeholders, in which case the storage will be reblessed to
969 L<DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars>.
971 In some configurations, placeholders will work but will throw implicit type
972 conversion errors for anything that's not expecting a string. In such a case,
973 the C<auto_cast> option from L<DBIx::Class::Storage::DBI::AutoCast> is
974 automatically set, which you may enable on connection with
975 L<connect_call_set_auto_cast|DBIx::Class::Storage::DBI::AutoCast/connect_call_set_auto_cast>.
976 The type info for the C<CAST>s is taken from the
977 L<DBIx::Class::ResultSource/data_type> definitions in your Result classes, and
978 are mapped to a Sybase type (if it isn't already) using a mapping based on
981 In other configurations, placeholders will work just as they do with the Sybase
982 Open Client libraries.
984 Inserts or updates of TEXT/IMAGE columns will B<NOT> work with FreeTDS.
986 =head1 INSERTS WITH PLACEHOLDERS
988 With placeholders enabled, inserts are done in a transaction so that there are
989 no concurrency issues with getting the inserted identity value using
990 C<SELECT MAX(col)>, which is the only way to get the C<IDENTITY> value in this
993 In addition, they are done on a separate connection so that it's possible to
994 have active cursors when doing an insert.
996 When using C<DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars> transactions
997 are unnecessary and not used, as there are no concurrency issues with C<SELECT
998 @@IDENTITY> which is a session variable.
1002 Due to limitations of the TDS protocol and L<DBD::Sybase>, you cannot begin a
1003 transaction while there are active cursors, nor can you use multiple active
1004 cursors within a transaction. An active cursor is, for example, a
1005 L<ResultSet|DBIx::Class::ResultSet> that has been executed using C<next> or
1006 C<first> but has not been exhausted or L<reset|DBIx::Class::ResultSet/reset>.
1008 For example, this will not work:
1010 $schema->txn_do(sub {
1011 my $rs = $schema->resultset('Book');
1012 while (my $result = $rs->next) {
1013 $schema->resultset('MetaData')->create({
1014 book_id => $result->id,
1022 my $first_row = $large_rs->first;
1023 $schema->txn_do(sub { ... });
1025 Transactions done for inserts in C<AutoCommit> mode when placeholders are in use
1026 are not affected, as they are done on an extra database handle.
1032 =item * use L<DBIx::Class::Storage::DBI::Replicated>
1034 =item * L<connect|DBIx::Class::Schema/connect> another L<Schema|DBIx::Class::Schema>
1036 =item * load the data from your cursor with L<DBIx::Class::ResultSet/all>
1040 =head1 MAXIMUM CONNECTIONS
1042 The TDS protocol makes separate connections to the server for active statements
1043 in the background. By default the number of such connections is limited to 25,
1044 on both the client side and the server side.
1046 This is a bit too low for a complex L<DBIx::Class> application, so on connection
1047 the client side setting is set to C<256> (see L<DBD::Sybase/maxConnect>.) You
1048 can override it to whatever setting you like in the DSN.
1051 L<http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1272.htm>
1052 for information on changing the setting on the server side.
1056 See L</connect_call_datetime_setup> to setup date formats
1057 for L<DBIx::Class::InflateColumn::DateTime>.
1059 =head1 LIMITED QUERIES
1061 Because ASE does not have a good way to limit results in SQL that works for
1062 all types of queries, the limit dialect is set to
1063 L<GenericSubQ|DBIx::Class::SQLMaker::LimitDialects/GenericSubQ>.
1065 Fortunately, ASE and L<DBD::Sybase> support cursors properly, so when
1066 L<GenericSubQ|DBIx::Class::SQLMaker::LimitDialects/GenericSubQ> is too slow
1067 you can use the L<software_limit|DBIx::Class::ResultSet/software_limit>
1068 L<DBIx::Class::ResultSet> attribute to simulate limited queries by skipping
1071 =head1 TEXT/IMAGE COLUMNS
1073 L<DBD::Sybase> compiled with FreeTDS will B<NOT> allow you to insert or update
1074 C<TEXT/IMAGE> columns.
1076 Setting C<< $dbh->{LongReadLen} >> will also not work with FreeTDS use either:
1078 $schema->storage->dbh->do("SET TEXTSIZE $bytes");
1082 $schema->storage->set_textsize($bytes);
1086 However, the C<LongReadLen> you pass in
1087 L<connect_info|DBIx::Class::Storage::DBI/connect_info> is used to execute the
1088 equivalent C<SET TEXTSIZE> command on connection.
1090 See L</connect_call_blob_setup> for a
1091 L<connect_info|DBIx::Class::Storage::DBI/connect_info> setting you need to work
1092 with C<IMAGE> columns.
1096 The experimental L<DBD::Sybase> Bulk API support is used for
1097 L<populate|DBIx::Class::ResultSet/populate> in B<void> context, in a transaction
1098 on a separate connection.
1100 To use this feature effectively, use a large number of rows for each
1101 L<populate|DBIx::Class::ResultSet/populate> call, eg.:
1103 while (my $rows = $data_source->get_100_rows()) {
1104 $rs->populate($rows);
1107 B<NOTE:> the L<add_columns|DBIx::Class::ResultSource/add_columns>
1108 calls in your C<Result> classes B<must> list columns in database order for this
1109 to work. Also, you may have to unset the C<LC_ALL> environment variable before
1110 loading your app, as C<BCP -Y> is not yet supported in DBD::Sybase .
1112 When inserting IMAGE columns using this method, you'll need to use
1113 L</connect_call_blob_setup> as well.
1115 =head1 COMPUTED COLUMNS
1117 If you have columns such as:
1119 created_dtm AS getdate()
1121 represent them in your Result classes as:
1125 default_value => \'getdate()',
1127 inflate_datetime => 1,
1130 The C<data_type> must exist and must be C<undef>. Then empty inserts will work
1131 on tables with such columns.
1133 =head1 TIMESTAMP COLUMNS
1135 C<timestamp> columns in Sybase ASE are not really timestamps, see:
1136 L<http://dba.fyicenter.com/Interview-Questions/SYBASE/The_timestamp_datatype_in_Sybase_.html>.
1138 They should be defined in your Result classes as:
1141 data_type => 'timestamp',
1143 inflate_datetime => 0,
1146 The C<<inflate_datetime => 0>> is necessary if you use
1147 L<DBIx::Class::InflateColumn::DateTime>, and most people do, and still want to
1148 be able to read these values.
1150 The values will come back as hexadecimal.
1158 Transitions to AutoCommit=0 (starting a transaction) mode by exhausting
1159 any active cursors, using eager cursors.
1163 Real limits and limited counts using stored procedures deployed on startup.
1167 Blob update with a LIKE query on a blob, without invalidating the WHERE condition.
1171 bulk_insert using prepare_cached (see comments.)
1175 =head1 FURTHER QUESTIONS?
1177 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
1179 =head1 COPYRIGHT AND LICENSE
1181 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
1182 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
1183 redistribute it and/or modify it under the same terms as the
1184 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.