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/;
16 DBIx::Class::Storage::DBI::Sybase - Sybase support for DBIx::Class
20 This subclass supports L<DBD::Sybase> for real Sybase databases. If you are
21 using an MSSQL database via L<DBD::Sybase>, your storage will be reblessed to
22 L<DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server>.
26 If your version of Sybase does not support placeholders, then your storage
27 will be reblessed to L<DBIx::Class::Storage::DBI::Sybase::NoBindVars>. You can
28 also enable that driver explicitly, see the documentation for more details.
30 With this driver there is unfortunately no way to get the C<last_insert_id>
31 without doing a C<select max(col)>.
33 But your queries will be cached.
35 You need a version of L<DBD::Sybase> compiled with the Sybase OpenClient
36 libraries, B<NOT> FreeTDS, for placeholder support. Otherwise your storage will
37 be automatically reblessed into C<::NoBindVars>.
39 A recommended L<DBIx::Class::Storage::DBI/connect_info> setting:
41 on_connect_call => [['datetime_setup'], ['blob_setup', log_on_update => 0]]
47 __PACKAGE__->mk_group_accessors('simple' =>
48 qw/_blob_log_on_update/
54 if (ref($self) eq 'DBIx::Class::Storage::DBI::Sybase') {
56 @{$self->dbh->selectrow_arrayref(qq{sp_server_info \@attribute_id=1})}[2]
61 my $subclass = "DBIx::Class::Storage::DBI::Sybase::${dbtype}";
63 if (!$exception && $dbtype && $self->load_optional_class($subclass)) {
64 bless $self, $subclass;
66 } else { # real Sybase
67 my $no_bind_vars = 'DBIx::Class::Storage::DBI::Sybase::NoBindVars';
69 if ($self->_using_freetds) {
72 Your version of Sybase potentially supports placeholders and query caching,
73 however you seem to be using FreeTDS which does not (yet?) support this.
75 Please recompile DBD::Sybase with the Sybase OpenClient libraries if you want
78 TEXT/IMAGE column support will also not work under FreeTDS.
80 See perldoc DBIx::Class::Storage::DBI::Sybase for more details.
82 $self->ensure_class_loaded($no_bind_vars);
83 bless $self, $no_bind_vars;
87 if (not $self->dbh->{syb_dynamic_supported}) {
88 $self->ensure_class_loaded($no_bind_vars);
89 bless $self, $no_bind_vars;
93 $self->_set_maxConnect;
101 return $self->_dbh->{syb_oc_version} =~ /freetds/i;
104 sub _set_maxConnect {
107 my $dsn = $self->_dbi_connect_info->[0];
109 return if ref($dsn) eq 'CODE';
111 if ($dsn !~ /maxConnect=/) {
112 $self->_dbi_connect_info->[0] = "$dsn;maxConnect=256";
113 my $connected = defined $self->_dbh;
115 $self->ensure_connected if $connected;
119 =head2 connect_call_blob_setup
123 on_connect_call => [ [ 'blob_setup', log_on_update => 0 ] ]
125 Does C<< $dbh->{syb_binary_images} = 1; >> to return C<IMAGE> data as raw binary
126 instead of as a hex string.
130 Also sets the C<log_on_update> value for blob write operations. The default is
131 C<1>, but C<0> is better if your database is configured for it.
134 L<DBD::Sybase/Handling_IMAGE/TEXT_data_with_syb_ct_get_data()/syb_ct_send_data()>.
138 sub connect_call_blob_setup {
141 my $dbh = $self->_dbh;
142 $dbh->{syb_binary_images} = 1;
144 $self->_blob_log_on_update($args{log_on_update})
145 if exists $args{log_on_update};
151 $type && $type =~ /(?:text|image|lob|bytea|binary|memo)/i;
154 ## This will be useful if we ever implement BLOB filehandle inflation and will
155 ## need to use the API, but for now it isn't.
157 #sub order_columns_for_select {
158 # my ($self, $source, $columns) = @_;
160 # my (@non_blobs, @blobs);
162 # for my $col (@$columns) {
163 # if ($self->_is_lob_type($source->column_info($col)->{data_type})) {
166 # push @non_blobs, $col;
170 # croak "cannot select more than a one TEXT/IMAGE column at a time"
173 # return (@non_blobs, @blobs);
176 # override to handle TEXT/IMAGE
178 my ($self, $source, $to_insert) = splice @_, 0, 3;
179 my $dbh = $self->_dbh;
181 my $blob_cols = $self->_remove_blob_cols($source, $to_insert);
183 # check if we need to set IDENTITY_INSERT
184 my $identity_insert = 0;
185 my %col_info = map { ($_, $source->column_info($_)) } keys %$to_insert;
186 my $table = $source->from;
188 if (List::Util::first { $_->{is_auto_increment} } (values %col_info)) {
189 $identity_insert = 1;
190 $dbh->do("SET IDENTITY_INSERT $table ON");
193 my $updated_cols = $self->next::method($source, $to_insert, @_);
195 if ($identity_insert) {
196 $dbh->do("SET IDENTITY_INSERT $table OFF");
199 $self->_insert_blobs($source, $blob_cols, $to_insert) if %$blob_cols;
201 return $updated_cols;
205 my ($self, $source) = splice @_, 0, 2;
206 my ($fields, $where) = @_;
207 my $wantarray = wantarray;
209 my $blob_cols = $self->_remove_blob_cols($source, $fields);
213 @res = $self->next::method($source, @_);
215 $res[0] = $self->next::method($source, @_);
218 $self->_update_blobs($source, $blob_cols, $where) if %$blob_cols;
220 return $wantarray ? @res : $res[0];
223 sub _remove_blob_cols {
224 my ($self, $source, $fields) = @_;
228 for my $col (keys %$fields) {
229 if ($self->_is_lob_type($source->column_info($col)->{data_type})) {
230 $blob_cols{$col} = delete $fields->{$col};
231 $fields->{$col} = \"''";
239 my ($self, $source, $blob_cols, $where) = @_;
241 my (@primary_cols) = $source->primary_columns;
243 croak "Cannot update TEXT/IMAGE column(s) without a primary key"
244 unless @primary_cols;
246 # check if we're updating a single row by PK
247 my $pk_cols_in_where = 0;
248 for my $col (@primary_cols) {
249 $pk_cols_in_where++ if defined $where->{$col};
253 if ($pk_cols_in_where == @primary_cols) {
255 @row_to_update{@primary_cols} = @{$where}{@primary_cols};
256 @rows = \%row_to_update;
258 my $rs = $source->resultset->search(
261 result_class => 'DBIx::Class::ResultClass::HashRefInflator',
262 select => \@primary_cols
265 @rows = $rs->all; # statement must finish
268 for my $row (@rows) {
269 $self->_insert_blobs($source, $blob_cols, $row);
274 my ($self, $source, $blob_cols, $row) = @_;
275 my $dbh = $self->dbh;
277 my $table = $source->from;
280 my (@primary_cols) = $source->primary_columns;
282 croak "Cannot update TEXT/IMAGE column(s) without a primary key"
283 unless @primary_cols;
285 if ((grep { defined $row{$_} } @primary_cols) != @primary_cols) {
286 if (@primary_cols == 1) {
287 my $col = $primary_cols[0];
288 $row{$col} = $self->last_insert_id($source, $col);
290 croak "Cannot update TEXT/IMAGE column(s) without primary key values";
294 for my $col (keys %$blob_cols) {
295 my $blob = $blob_cols->{$col};
298 if (not $self->isa('DBIx::Class::Storage::DBI::NoBindVars')) {
299 my $search_cond = join ',' => map "$_ = ?", @primary_cols;
302 "select $col from $table where $search_cond"
304 $sth->execute(map $row{$_}, @primary_cols);
306 my $search_cond = join ',' => map "$_ = $row{$_}", @primary_cols;
308 $sth = $dbh->prepare(
309 "select $col from $table where $search_cond"
315 while ($sth->fetch) {
316 $sth->func('CS_GET', 1, 'ct_data_info') or die $sth->errstr;
318 $sth->func('ct_prepare_send') or die $sth->errstr;
320 my $log_on_update = $self->_blob_log_on_update;
321 $log_on_update = 1 if not defined $log_on_update;
323 $sth->func('CS_SET', 1, {
324 total_txtlen => length($blob),
325 log_on_update => $log_on_update
326 }, 'ct_data_info') or die $sth->errstr;
328 $sth->func($blob, length($blob), 'ct_send_data') or die $sth->errstr;
330 $sth->func('ct_finish_send') or die $sth->errstr;
334 croak $exception if $exception;
338 =head2 connect_call_datetime_setup
342 on_connect_call => 'datetime_setup'
344 In L<DBIx::Class::Storage::DBI/connect_info> to set:
346 $dbh->syb_date_fmt('ISO_strict'); # output fmt: 2004-08-21T14:36:48.080Z
347 $dbh->do('set dateformat mdy'); # input fmt: 08/13/1979 18:08:55.080
349 On connection for use with L<DBIx::Class::InflateColumn::DateTime>, using
350 L<DateTime::Format::Sybase>, which you will need to install.
352 This works for both C<DATETIME> and C<SMALLDATETIME> columns, although
353 C<SMALLDATETIME> columns only have minute precision.
358 my $old_dbd_warned = 0;
360 sub connect_call_datetime_setup {
362 my $dbh = $self->_dbh;
364 if ($dbh->can('syb_date_fmt')) {
365 $dbh->syb_date_fmt('ISO_strict');
366 } elsif (not $old_dbd_warned) {
367 carp "Your DBD::Sybase is too old to support ".
368 "DBIx::Class::InflateColumn::DateTime, please upgrade!";
372 $dbh->do('set dateformat mdy');
378 sub datetime_parser_type { "DateTime::Format::Sybase" }
380 sub _dbh_last_insert_id {
381 my ($self, $dbh, $source, $col) = @_;
383 # sorry, there's no other way!
384 my $sth = $self->sth("select max($col) from ".$source->from);
385 my ($id) = $dbh->selectrow_array($sth);
393 =head1 MAXIMUM CONNECTIONS
395 L<DBD::Sybase> makes separate connections to the server for active statements in
396 the background. By default the number of such connections is limited to 25, on
397 both the client side and the server side.
399 This is a bit too low, so on connection the clientside setting is set to C<256>
400 (see L<DBD::Sybase/maxConnect>.) You can override it to whatever setting you
404 L<http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1272.htm>
405 for information on changing the setting on the server side.
409 See L</connect_call_datetime_setup> to setup date formats
410 for L<DBIx::Class::InflateColumn::DateTime>.
412 =head1 IMAGE AND TEXT COLUMNS
414 L<DBD::Sybase> compiled with FreeTDS will B<NOT> work with C<TEXT/IMAGE>
417 See L</connect_call_blob_setup> for a L<DBIx::Class::Storage::DBI/connect_info>
418 setting you need to work with C<IMAGE> columns.
422 See L<DBIx::Class/CONTRIBUTORS>.
426 You may distribute this code under the same terms as Perl itself.