1 package DBIx::Class::Storage::DBI::Sybase;
7 DBIx::Class::Storage::DBI::Sybase::Base
8 DBIx::Class::Storage::DBI
11 use Carp::Clan qw/^DBIx::Class/;
15 DBIx::Class::Storage::DBI::Sybase - Storage::DBI subclass for Sybase
19 This subclass supports L<DBD::Sybase> for real Sybase databases. If you are
20 using an MSSQL database via L<DBD::Sybase>, your storage will be reblessed to
21 L<DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server>.
25 If your version of Sybase does not support placeholders, then your storage
26 will be reblessed to L<DBIx::Class::Storage::DBI::Sybase::NoBindVars>. You can
27 also enable that driver explicitly, see the documentation for more details.
29 With this driver there is unfortunately no way to get the C<last_insert_id>
30 without doing a C<select max(col)>.
32 But your queries will be cached.
34 You need a version of L<DBD::Sybase> compiled with the Sybase OpenClient
35 libraries, B<NOT> FreeTDS, for placeholder support. Otherwise your storage will
36 be automatically reblessed into C<::NoBindVars>.
38 A recommended L<DBIx::Class::Storage::DBI/connect_info> settings:
40 on_connect_call => [['datetime_setup'], [blob_setup => log_on_update => 0]]
46 __PACKAGE__->mk_group_accessors('simple' =>
47 qw/_blob_log_on_update/
53 if (ref($self) eq 'DBIx::Class::Storage::DBI::Sybase') {
55 @{$self->dbh->selectrow_arrayref(qq{sp_server_info \@attribute_id=1})}[2]
60 my $subclass = "DBIx::Class::Storage::DBI::Sybase::${dbtype}";
62 if (!$exception && $dbtype && $self->load_optional_class($subclass)) {
63 bless $self, $subclass;
65 } else { # real Sybase
66 my $no_bind_vars = 'DBIx::Class::Storage::DBI::Sybase::NoBindVars';
68 if (not $self->dbh->{syb_dynamic_supported}) {
69 $self->ensure_class_loaded($no_bind_vars);
70 bless $self, $no_bind_vars;
74 if ($self->_using_freetds) {
77 Your version of Sybase potentially supports placeholders and query caching,
78 however you seem to be using FreeTDS which does not (yet?) support this.
80 Please recompile DBD::Sybase with the Sybase OpenClient libraries if you want
83 TEXT/IMAGE column support will also not work under FreeTDS.
85 See perldoc DBIx::Class::Storage::DBI::Sybase for more details.
87 $self->ensure_class_loaded($no_bind_vars);
88 bless $self, $no_bind_vars;
91 $self->_set_maxConnect;
97 my $using_freetds = undef;
101 my $dbh = $self->_dbh;
103 return $using_freetds if defined $using_freetds;
105 local $dbh->{syb_rowcount} = 1; # this is broken in freetds
106 $using_freetds = @{ $dbh->selectall_arrayref('sp_help') } != 1;
108 return $using_freetds;
112 sub _set_maxConnect {
115 my $dsn = $self->_dbi_connect_info->[0];
117 return if ref($dsn) eq 'CODE';
119 if ($dsn !~ /maxConnect=/) {
120 $self->_dbi_connect_info->[0] = "$dsn;maxConnect=256";
121 my $connected = defined $self->_dbh;
123 $self->ensure_connected if $connected;
127 =head2 connect_call_blob_setup
131 on_connect_call => [ [ blob_setup => log_on_update => 0 ] ]
133 Does C<< $dbh->{syb_binary_images} = 1; >> to return C<IMAGE> data as raw binary
134 instead of as a hex string.
138 Also sets the C<log_on_update> value for blob write operations. The default is
139 C<1>, but C<0> is better if your database is configured for it.
142 L<DBD::Sybase/Handling_IMAGE/TEXT_data_with_syb_ct_get_data()/syb_ct_send_data()>.
146 sub connect_call_blob_setup {
149 my $dbh = $self->_dbh;
150 $dbh->{syb_binary_images} = 1;
152 $self->_blob_log_on_update($args{log_on_update})
153 if exists $args{log_on_update};
159 $type && $type =~ /(?:text|image|lob|bytea|binary|memo)/i;
162 ## This will be useful if we ever implement BLOB filehandle inflation and will
163 ## need to use the API, but for now it isn't.
165 #sub order_columns_for_select {
166 # my ($self, $source) = @_;
168 # my (@non_blobs, @blobs);
170 # for my $col ($source->columns) {
171 # if ($self->_is_lob_type($source->column_info($col)->{data_type})) {
174 # push @non_blobs, $col;
178 # croak "cannot select more than a one TEXT/IMAGE column at a time"
181 # return (@non_blobs, @blobs);
184 # override to handle TEXT/IMAGE
186 my ($self, $source, $to_insert) = splice @_, 0, 3;
188 my $blob_cols = $self->_remove_blob_cols($source, $to_insert);
190 my $updated_cols = $self->next::method($source, $to_insert, @_);
192 $self->_insert_blobs($source, $blob_cols, $to_insert) if %$blob_cols;
194 return $updated_cols;
198 my ($self, $source) = splice @_, 0, 2;
199 my ($fields, $where) = @_;
200 my $wantarray = wantarray;
202 my $blob_cols = $self->_remove_blob_cols($source, $fields);
206 @res = $self->next::method($source, @_);
208 $res[0] = $self->next::method($source, @_);
211 $self->_update_blobs($source, $blob_cols, $where) if %$blob_cols;
213 return $wantarray ? @res : $res[0];
216 sub _remove_blob_cols {
217 my ($self, $source, $fields) = @_;
221 for my $col (keys %$fields) {
222 if ($self->_is_lob_type($source->column_info($col)->{data_type})) {
223 $blob_cols{$col} = delete $fields->{$col};
224 $fields->{$col} = \"''";
232 my ($self, $source, $blob_cols, $where) = @_;
234 my (@primary_cols) = $source->primary_columns;
236 croak "Cannot update TEXT/IMAGE column(s) without a primary key"
237 unless @primary_cols;
239 # check if we're updating a single row by PK
240 my $pk_cols_in_where = 0;
241 for my $col (@primary_cols) {
242 $pk_cols_in_where++ if defined $where->{$col};
246 if ($pk_cols_in_where == @primary_cols) {
248 @row_to_update{@primary_cols} = @{$where}{@primary_cols};
249 @rows = \%row_to_update;
251 my $rs = $source->resultset->search(
254 result_class => 'DBIx::Class::ResultClass::HashRefInflator',
255 select => \@primary_cols
258 @rows = $rs->all; # statement must finish
261 for my $row (@rows) {
262 $self->_insert_blobs($source, $blob_cols, $row);
267 my ($self, $source, $blob_cols, $row) = @_;
268 my $dbh = $self->dbh;
270 my $table = $source->from;
273 my (@primary_cols) = $source->primary_columns;
275 croak "Cannot update TEXT/IMAGE column(s) without a primary key"
276 unless @primary_cols;
278 if ((grep { defined $row{$_} } @primary_cols) != @primary_cols) {
279 if (@primary_cols == 1) {
280 my $col = $primary_cols[0];
281 $row{$col} = $self->last_insert_id($source, $col);
283 croak "Cannot update TEXT/IMAGE column(s) without primary key values";
287 for my $col (keys %$blob_cols) {
288 my $blob = $blob_cols->{$col};
291 if (not $self->isa('DBIx::Class::Storage::DBI::NoBindVars')) {
292 my $search_cond = join ',' => map "$_ = ?", @primary_cols;
295 "select $col from $table where $search_cond"
297 $sth->execute(map $row{$_}, @primary_cols);
299 my $search_cond = join ',' => map "$_ = $row{$_}", @primary_cols;
301 $sth = $dbh->prepare(
302 "select $col from $table where $search_cond"
308 while ($sth->fetch) {
309 $sth->func('CS_GET', 1, 'ct_data_info') or die $sth->errstr;
311 $sth->func('ct_prepare_send') or die $sth->errstr;
313 my $log_on_update = $self->_blob_log_on_update;
314 $log_on_update = 1 if not defined $log_on_update;
316 $sth->func('CS_SET', 1, {
317 total_txtlen => length($blob),
318 log_on_update => $log_on_update
319 }, 'ct_data_info') or die $sth->errstr;
321 $sth->func($blob, length($blob), 'ct_send_data') or die $sth->errstr;
323 $sth->func('ct_finish_send') or die $sth->errstr;
327 croak $exception if $exception;
331 =head2 connect_call_datetime_setup
335 on_connect_call => 'datetime_setup'
337 In L<DBIx::Class::Storage::DBI/connect_info> to set:
339 $dbh->syb_date_fmt('ISO_strict'); # output fmt: 2004-08-21T14:36:48.080Z
340 $dbh->do('set dateformat mdy'); # input fmt: 08/13/1979 18:08:55.080
342 On connection for use with L<DBIx::Class::InflateColumn::DateTime>, using
343 L<DateTime::Format::Sybase>, which you will need to install.
345 This works for both C<DATETIME> and C<SMALLDATETIME> columns, although
346 C<SMALLDATETIME> columns only have minute precision.
351 my $old_dbd_warned = 0;
353 sub connect_call_datetime_setup {
355 my $dbh = $self->_dbh;
357 if ($dbh->can('syb_date_fmt')) {
358 $dbh->syb_date_fmt('ISO_strict');
359 } elsif (not $old_dbd_warned) {
360 carp "Your DBD::Sybase is too old to support ".
361 "DBIx::Class::InflateColumn::DateTime, please upgrade!";
365 $dbh->do('set dateformat mdy');
371 sub datetime_parser_type { "DateTime::Format::Sybase" }
373 sub _dbh_last_insert_id {
374 my ($self, $dbh, $source, $col) = @_;
376 # sorry, there's no other way!
377 my $sth = $self->sth("select max($col) from ".$source->from);
378 my ($id) = $dbh->selectrow_array($sth);
386 =head1 MAXIMUM CONNECTIONS
388 L<DBD::Sybase> makes separate connections to the server for active statements in
389 the background. By default the number of such connections is limited to 25, on
390 both the client side and the server side.
392 This is a bit too low, so on connection the clientside setting is set to C<256>
393 (see L<DBD::Sybase/maxConnect>.) You can override it to whatever setting you
397 L<http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1272.htm>
398 for information on changing the setting on the server side.
402 See L</connect_call_datetime_setup> to setup date formats
403 for L<DBIx::Class::InflateColumn::DateTime>.
405 =head1 IMAGE AND TEXT COLUMNS
407 L<DBD::Sybase> compiled with FreeTDS will B<NOT> work with C<TEXT/IMAGE>
410 See L</connect_call_blob_setup> for a L<DBIx::Class::Storage::DBI/connect_info>
411 setting you need to work with C<IMAGE> columns.
415 See L<DBIx::Class/CONTRIBUTORS>.
419 You may distribute this code under the same terms as Perl itself.