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 );
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"
785 $sth->func('CS_GET', 1, 'ct_data_info') or die $sth->errstr;
788 $sth->func('ct_prepare_send') or die $sth->errstr;
790 my $log_on_update = $self->_blob_log_on_update;
791 $log_on_update = 1 if not defined $log_on_update;
793 $sth->func('CS_SET', 1, {
794 total_txtlen => length($blob),
795 log_on_update => $log_on_update
796 }, 'ct_data_info') or die $sth->errstr;
798 $sth->func($blob, length($blob), 'ct_send_data') or die $sth->errstr;
800 $sth->func('ct_finish_send') or die $sth->errstr;
803 if ($self->_using_freetds) {
804 $self->throw_exception (
805 "TEXT/IMAGE operation failed, probably because you are using FreeTDS: $_"
809 $self->throw_exception($_);
813 $sth->finish if $sth;
818 sub _insert_blobs_array {
819 my ($self, $source, $blob_cols, $cols, $data) = @_;
821 for my $i (0..$#$data) {
822 my $datum = $data->[$i];
825 @row{ @$cols } = @$datum;
828 for my $j (0..$#$cols) {
829 if (exists $blob_cols->[$i][$j]) {
830 $blob_vals{ $cols->[$j] } = $blob_cols->[$i][$j];
834 $self->_insert_blobs ($source, \%blob_vals, \%row);
838 =head2 connect_call_datetime_setup
842 on_connect_call => 'datetime_setup'
844 In L<connect_info|DBIx::Class::Storage::DBI/connect_info> to set:
846 $dbh->syb_date_fmt('ISO_strict'); # output fmt: 2004-08-21T14:36:48.080Z
847 $dbh->do('set dateformat mdy'); # input fmt: 08/13/1979 18:08:55.080
849 This works for both C<DATETIME> and C<SMALLDATETIME> columns, note that
850 C<SMALLDATETIME> columns only have minute precision.
854 sub connect_call_datetime_setup {
856 my $dbh = $self->_get_dbh;
858 if ($dbh->can('syb_date_fmt')) {
859 # amazingly, this works with FreeTDS
860 $dbh->syb_date_fmt('ISO_strict');
864 'Your DBD::Sybase is too old to support '
865 .'DBIx::Class::InflateColumn::DateTime, please upgrade!';
867 # FIXME - in retrospect this is a rather bad US-centric choice
868 # of format. Not changing as a bugwards compat, though in reality
869 # the only piece that sees the results of $dt object formatting
870 # (as opposed to parsing) is the database itself, so theoretically
871 # changing both this SET command and the formatter definition of
872 # ::S::D::Sybase::ASE::DateTime::Format below should be safe and
875 $dbh->do('SET DATEFORMAT mdy');
880 sub _exec_txn_begin {
883 # bulkLogin=1 connections are always in a transaction, and can only call BEGIN
884 # TRAN once. However, we need to make sure there's a $dbh.
885 return if $self->_is_bulk_storage && $self->_dbh && $self->_began_bulk_work;
887 $self->next::method(@_);
889 $self->_began_bulk_work(1) if $self->_is_bulk_storage;
892 # savepoint support using ASE syntax
894 sub _exec_svp_begin {
895 my ($self, $name) = @_;
897 $self->_dbh->do("SAVE TRANSACTION $name");
900 # A new SAVE TRANSACTION with the same name releases the previous one.
901 sub _exec_svp_release { 1 }
903 sub _exec_svp_rollback {
904 my ($self, $name) = @_;
906 $self->_dbh->do("ROLLBACK TRANSACTION $name");
909 package # hide from PAUSE
910 DBIx::Class::Storage::DBI::Sybase::ASE::DateTime::Format;
912 my $datetime_parse_format = '%Y-%m-%dT%H:%M:%S.%3NZ';
913 my $datetime_format_format = '%m/%d/%Y %H:%M:%S.%3N';
915 my ($datetime_parser, $datetime_formatter);
919 require DateTime::Format::Strptime;
920 $datetime_parser ||= DateTime::Format::Strptime->new(
921 pattern => $datetime_parse_format,
924 return $datetime_parser->parse_datetime(shift);
927 sub format_datetime {
929 require DateTime::Format::Strptime;
930 $datetime_formatter ||= DateTime::Format::Strptime->new(
931 pattern => $datetime_format_format,
934 return $datetime_formatter->format_datetime(shift);
939 =head1 Schema::Loader Support
941 As of version C<0.05000>, L<DBIx::Class::Schema::Loader> should work well with
942 most versions of Sybase ASE.
946 This driver supports L<DBD::Sybase> compiled against FreeTDS
947 (L<http://www.freetds.org/>) to the best of our ability, however it is
948 recommended that you recompile L<DBD::Sybase> against the Sybase Open Client
949 libraries. They are a part of the Sybase ASE distribution:
951 The Open Client FAQ is here:
952 L<http://www.isug.com/Sybase_FAQ/ASE/section7.html>.
954 Sybase ASE for Linux (which comes with the Open Client libraries) may be
955 downloaded here: L<http://response.sybase.com/forms/ASE_Linux_Download>.
957 To see if you're using FreeTDS run:
959 perl -MDBI -le 'my $dbh = DBI->connect($dsn, $user, $pass); print $dbh->{syb_oc_version}'
961 It is recommended to set C<tds version> for your ASE server to C<5.0> in
962 C</etc/freetds/freetds.conf>.
964 Some versions or configurations of the libraries involved will not support
965 placeholders, in which case the storage will be reblessed to
966 L<DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars>.
968 In some configurations, placeholders will work but will throw implicit type
969 conversion errors for anything that's not expecting a string. In such a case,
970 the C<auto_cast> option from L<DBIx::Class::Storage::DBI::AutoCast> is
971 automatically set, which you may enable on connection with
972 L<connect_call_set_auto_cast|DBIx::Class::Storage::DBI::AutoCast/connect_call_set_auto_cast>.
973 The type info for the C<CAST>s is taken from the
974 L<DBIx::Class::ResultSource/data_type> definitions in your Result classes, and
975 are mapped to a Sybase type (if it isn't already) using a mapping based on
978 In other configurations, placeholders will work just as they do with the Sybase
979 Open Client libraries.
981 Inserts or updates of TEXT/IMAGE columns will B<NOT> work with FreeTDS.
983 =head1 INSERTS WITH PLACEHOLDERS
985 With placeholders enabled, inserts are done in a transaction so that there are
986 no concurrency issues with getting the inserted identity value using
987 C<SELECT MAX(col)>, which is the only way to get the C<IDENTITY> value in this
990 In addition, they are done on a separate connection so that it's possible to
991 have active cursors when doing an insert.
993 When using C<DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars> transactions
994 are unnecessary and not used, as there are no concurrency issues with C<SELECT
995 @@IDENTITY> which is a session variable.
999 Due to limitations of the TDS protocol and L<DBD::Sybase>, you cannot begin a
1000 transaction while there are active cursors, nor can you use multiple active
1001 cursors within a transaction. An active cursor is, for example, a
1002 L<ResultSet|DBIx::Class::ResultSet> that has been executed using C<next> or
1003 C<first> but has not been exhausted or L<reset|DBIx::Class::ResultSet/reset>.
1005 For example, this will not work:
1007 $schema->txn_do(sub {
1008 my $rs = $schema->resultset('Book');
1009 while (my $result = $rs->next) {
1010 $schema->resultset('MetaData')->create({
1011 book_id => $result->id,
1019 my $first_row = $large_rs->first;
1020 $schema->txn_do(sub { ... });
1022 Transactions done for inserts in C<AutoCommit> mode when placeholders are in use
1023 are not affected, as they are done on an extra database handle.
1029 =item * use L<DBIx::Class::Storage::DBI::Replicated>
1031 =item * L<connect|DBIx::Class::Schema/connect> another L<Schema|DBIx::Class::Schema>
1033 =item * load the data from your cursor with L<DBIx::Class::ResultSet/all>
1037 =head1 MAXIMUM CONNECTIONS
1039 The TDS protocol makes separate connections to the server for active statements
1040 in the background. By default the number of such connections is limited to 25,
1041 on both the client side and the server side.
1043 This is a bit too low for a complex L<DBIx::Class> application, so on connection
1044 the client side setting is set to C<256> (see L<DBD::Sybase/maxConnect>.) You
1045 can override it to whatever setting you like in the DSN.
1048 L<http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1272.htm>
1049 for information on changing the setting on the server side.
1053 See L</connect_call_datetime_setup> to setup date formats
1054 for L<DBIx::Class::InflateColumn::DateTime>.
1056 =head1 LIMITED QUERIES
1058 Because ASE does not have a good way to limit results in SQL that works for
1059 all types of queries, the limit dialect is set to
1060 L<GenericSubQ|DBIx::Class::SQLMaker::LimitDialects/GenericSubQ>.
1062 Fortunately, ASE and L<DBD::Sybase> support cursors properly, so when
1063 L<GenericSubQ|DBIx::Class::SQLMaker::LimitDialects/GenericSubQ> is too slow
1064 you can use the L<software_limit|DBIx::Class::ResultSet/software_limit>
1065 L<DBIx::Class::ResultSet> attribute to simulate limited queries by skipping
1068 =head1 TEXT/IMAGE COLUMNS
1070 L<DBD::Sybase> compiled with FreeTDS will B<NOT> allow you to insert or update
1071 C<TEXT/IMAGE> columns.
1073 Setting C<< $dbh->{LongReadLen} >> will also not work with FreeTDS use either:
1075 $schema->storage->dbh->do("SET TEXTSIZE $bytes");
1079 $schema->storage->set_textsize($bytes);
1083 However, the C<LongReadLen> you pass in
1084 L<connect_info|DBIx::Class::Storage::DBI/connect_info> is used to execute the
1085 equivalent C<SET TEXTSIZE> command on connection.
1087 See L</connect_call_blob_setup> for a
1088 L<connect_info|DBIx::Class::Storage::DBI/connect_info> setting you need to work
1089 with C<IMAGE> columns.
1093 The experimental L<DBD::Sybase> Bulk API support is used for
1094 L<populate|DBIx::Class::ResultSet/populate> in B<void> context, in a transaction
1095 on a separate connection.
1097 To use this feature effectively, use a large number of rows for each
1098 L<populate|DBIx::Class::ResultSet/populate> call, eg.:
1100 while (my $rows = $data_source->get_100_rows()) {
1101 $rs->populate($rows);
1104 B<NOTE:> the L<add_columns|DBIx::Class::ResultSource/add_columns>
1105 calls in your C<Result> classes B<must> list columns in database order for this
1106 to work. Also, you may have to unset the C<LC_ALL> environment variable before
1107 loading your app, as C<BCP -Y> is not yet supported in DBD::Sybase .
1109 When inserting IMAGE columns using this method, you'll need to use
1110 L</connect_call_blob_setup> as well.
1112 =head1 COMPUTED COLUMNS
1114 If you have columns such as:
1116 created_dtm AS getdate()
1118 represent them in your Result classes as:
1122 default_value => \'getdate()',
1124 inflate_datetime => 1,
1127 The C<data_type> must exist and must be C<undef>. Then empty inserts will work
1128 on tables with such columns.
1130 =head1 TIMESTAMP COLUMNS
1132 C<timestamp> columns in Sybase ASE are not really timestamps, see:
1133 L<http://dba.fyicenter.com/Interview-Questions/SYBASE/The_timestamp_datatype_in_Sybase_.html>.
1135 They should be defined in your Result classes as:
1138 data_type => 'timestamp',
1140 inflate_datetime => 0,
1143 The C<<inflate_datetime => 0>> is necessary if you use
1144 L<DBIx::Class::InflateColumn::DateTime>, and most people do, and still want to
1145 be able to read these values.
1147 The values will come back as hexadecimal.
1155 Transitions to AutoCommit=0 (starting a transaction) mode by exhausting
1156 any active cursors, using eager cursors.
1160 Real limits and limited counts using stored procedures deployed on startup.
1164 Blob update with a LIKE query on a blob, without invalidating the WHERE condition.
1168 bulk_insert using prepare_cached (see comments.)
1172 =head1 FURTHER QUESTIONS?
1174 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
1176 =head1 COPYRIGHT AND LICENSE
1178 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
1179 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
1180 redistribute it and/or modify it under the same terms as the
1181 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.