1DBIx::Class::Storage::DUBsIe:r:SCyobnatsrei:b:uAtSeEdD(B3PI)exr:l:CDloacsusm:e:nSttaotriaogne::DBI::Sybase::ASE(3)
2
3
4

NAME

6       DBIx::Class::Storage::DBI::Sybase::ASE - Sybase ASE SQL Server support
7       for DBIx::Class
8

SYNOPSIS

10       This subclass supports DBD::Sybase for real (non-Microsoft) Sybase
11       databases.
12

DESCRIPTION

14       If your version of Sybase does not support placeholders, then your
15       storage will be reblessed to
16       DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars.  You can also
17       enable that driver explicitly, see the documentation for more details.
18
19       With this driver there is unfortunately no way to get the
20       "last_insert_id" without doing a "SELECT MAX(col)". This is done safely
21       in a transaction (locking the table.) See "INSERTS WITH PLACEHOLDERS".
22
23       A recommended connect_info setting:
24
25         on_connect_call => [['datetime_setup'], ['blob_setup', log_on_update => 0]]
26

METHODS

28   connect_call_blob_setup
29       Used as:
30
31         on_connect_call => [ [ 'blob_setup', log_on_update => 0 ] ]
32
33       Does "$dbh->{syb_binary_images} = 1;" to return "IMAGE" data as raw
34       binary instead of as a hex string.
35
36       Recommended.
37
38       Also sets the "log_on_update" value for blob write operations. The
39       default is 1, but 0 is better if your database is configured for it.
40
41       See "Handling IMAGE/TEXT data with
42       syb_ct_get_data()/syb_ct_send_data()" in DBD::Sybase.
43
44   connect_call_datetime_setup
45       Used as:
46
47         on_connect_call => 'datetime_setup'
48
49       In connect_info to set:
50
51         $dbh->syb_date_fmt('ISO_strict'); # output fmt: 2004-08-21T14:36:48.080Z
52         $dbh->do('set dateformat mdy');   # input fmt:  08/13/1979 18:08:55.080
53
54       This works for both "DATETIME" and "SMALLDATETIME" columns, note that
55       "SMALLDATETIME" columns only have minute precision.
56

Schema::Loader Support

58       As of version 0.05000, DBIx::Class::Schema::Loader should work well
59       with most versions of Sybase ASE.
60

FreeTDS

62       This driver supports DBD::Sybase compiled against FreeTDS
63       (<http://www.freetds.org/>) to the best of our ability, however it is
64       recommended that you recompile DBD::Sybase against the Sybase Open
65       Client libraries. They are a part of the Sybase ASE distribution:
66
67       The Open Client FAQ is here:
68       <http://www.isug.com/Sybase_FAQ/ASE/section7.html>.
69
70       Sybase ASE for Linux (which comes with the Open Client libraries) may
71       be downloaded here:
72       <http://response.sybase.com/forms/ASE_Linux_Download>.
73
74       To see if you're using FreeTDS run:
75
76         perl -MDBI -le 'my $dbh = DBI->connect($dsn, $user, $pass); print $dbh->{syb_oc_version}'
77
78       It is recommended to set "tds version" for your ASE server to 5.0 in
79       "/etc/freetds/freetds.conf".
80
81       Some versions or configurations of the libraries involved will not
82       support placeholders, in which case the storage will be reblessed to
83       DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars.
84
85       In some configurations, placeholders will work but will throw implicit
86       type conversion errors for anything that's not expecting a string. In
87       such a case, the "auto_cast" option from
88       DBIx::Class::Storage::DBI::AutoCast is automatically set, which you may
89       enable on connection with connect_call_set_auto_cast.  The type info
90       for the "CAST"s is taken from the "data_type" in
91       DBIx::Class::ResultSource definitions in your Result classes, and are
92       mapped to a Sybase type (if it isn't already) using a mapping based on
93       SQL::Translator.
94
95       In other configurations, placeholders will work just as they do with
96       the Sybase Open Client libraries.
97
98       Inserts or updates of TEXT/IMAGE columns will NOT work with FreeTDS.
99

INSERTS WITH PLACEHOLDERS

101       With placeholders enabled, inserts are done in a transaction so that
102       there are no concurrency issues with getting the inserted identity
103       value using "SELECT MAX(col)", which is the only way to get the
104       "IDENTITY" value in this mode.
105
106       In addition, they are done on a separate connection so that it's
107       possible to have active cursors when doing an insert.
108
109       When using "DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars"
110       transactions are unnecessary and not used, as there are no concurrency
111       issues with "SELECT @@IDENTITY" which is a session variable.
112

TRANSACTIONS

114       Due to limitations of the TDS protocol and DBD::Sybase, you cannot
115       begin a transaction while there are active cursors, nor can you use
116       multiple active cursors within a transaction. An active cursor is, for
117       example, a ResultSet that has been executed using "next" or "first" but
118       has not been exhausted or reset.
119
120       For example, this will not work:
121
122         $schema->txn_do(sub {
123           my $rs = $schema->resultset('Book');
124           while (my $result = $rs->next) {
125             $schema->resultset('MetaData')->create({
126               book_id => $result->id,
127               ...
128             });
129           }
130         });
131
132       This won't either:
133
134         my $first_row = $large_rs->first;
135         $schema->txn_do(sub { ... });
136
137       Transactions done for inserts in "AutoCommit" mode when placeholders
138       are in use are not affected, as they are done on an extra database
139       handle.
140
141       Some workarounds:
142
143       ·   use DBIx::Class::Storage::DBI::Replicated
144
145       ·   connect another Schema
146
147       ·   load the data from your cursor with "all" in DBIx::Class::ResultSet
148

MAXIMUM CONNECTIONS

150       The TDS protocol makes separate connections to the server for active
151       statements in the background. By default the number of such connections
152       is limited to 25, on both the client side and the server side.
153
154       This is a bit too low for a complex DBIx::Class application, so on
155       connection the client side setting is set to 256 (see "maxConnect" in
156       DBD::Sybase.) You can override it to whatever setting you like in the
157       DSN.
158
159       See
160       <http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1272.htm>
161       for information on changing the setting on the server side.
162

DATES

164       See "connect_call_datetime_setup" to setup date formats for
165       DBIx::Class::InflateColumn::DateTime.
166

LIMITED QUERIES

168       Because ASE does not have a good way to limit results in SQL that works
169       for all types of queries, the limit dialect is set to GenericSubQ.
170
171       Fortunately, ASE and DBD::Sybase support cursors properly, so when
172       GenericSubQ is too slow you can use the software_limit
173       DBIx::Class::ResultSet attribute to simulate limited queries by
174       skipping over records.
175

TEXT/IMAGE COLUMNS

177       DBD::Sybase compiled with FreeTDS will NOT allow you to insert or
178       update "TEXT/IMAGE" columns.
179
180       Setting "$dbh->{LongReadLen}" will also not work with FreeTDS use
181       either:
182
183         $schema->storage->dbh->do("SET TEXTSIZE $bytes");
184
185       or
186
187         $schema->storage->set_textsize($bytes);
188
189       instead.
190
191       However, the "LongReadLen" you pass in connect_info is used to execute
192       the equivalent "SET TEXTSIZE" command on connection.
193
194       See "connect_call_blob_setup" for a connect_info setting you need to
195       work with "IMAGE" columns.
196

BULK API

198       The experimental DBD::Sybase Bulk API support is used for populate in
199       void context, in a transaction on a separate connection.
200
201       To use this feature effectively, use a large number of rows for each
202       populate call, eg.:
203
204         while (my $rows = $data_source->get_100_rows()) {
205           $rs->populate($rows);
206         }
207
208       NOTE: the add_columns calls in your "Result" classes must list columns
209       in database order for this to work. Also, you may have to unset the
210       "LANG" environment variable before loading your app, as "BCP -Y" is not
211       yet supported in DBD::Sybase .
212
213       When inserting IMAGE columns using this method, you'll need to use
214       "connect_call_blob_setup" as well.
215

COMPUTED COLUMNS

217       If you have columns such as:
218
219         created_dtm AS getdate()
220
221       represent them in your Result classes as:
222
223         created_dtm => {
224           data_type => undef,
225           default_value => \'getdate()',
226           is_nullable => 0,
227           inflate_datetime => 1,
228         }
229
230       The "data_type" must exist and must be "undef". Then empty inserts will
231       work on tables with such columns.
232

TIMESTAMP COLUMNS

234       "timestamp" columns in Sybase ASE are not really timestamps, see:
235       <http://dba.fyicenter.com/Interview-Questions/SYBASE/The_timestamp_datatype_in_Sybase_.html>.
236
237       They should be defined in your Result classes as:
238
239         ts => {
240           data_type => 'timestamp',
241           is_nullable => 0,
242           inflate_datetime => 0,
243         }
244
245       The "<inflate_datetime =" 0>> is necessary if you use
246       DBIx::Class::InflateColumn::DateTime, and most people do, and still
247       want to be able to read these values.
248
249       The values will come back as hexadecimal.
250

TODO

252       ·   Transitions to AutoCommit=0 (starting a transaction) mode by
253           exhausting any active cursors, using eager cursors.
254
255       ·   Real limits and limited counts using stored procedures deployed on
256           startup.
257
258       ·   Blob update with a LIKE query on a blob, without invalidating the
259           WHERE condition.
260
261       ·   bulk_insert using prepare_cached (see comments.)
262

FURTHER QUESTIONS?

264       Check the list of additional DBIC resources.
265

267       This module is free software copyright by the DBIx::Class (DBIC)
268       authors. You can redistribute it and/or modify it under the same terms
269       as the DBIx::Class library.
270
271
272
273perl v5.30.1                      2020D-B0I1x-:2:9Class::Storage::DBI::Sybase::ASE(3)
Impressum