1DBIx::Class::Storage::DUBsIe:r:SCyobnatsrei:b:uAtSeEdD(B3PI)exr:l:CDloacsusm:e:nSttaotriaogne::DBI::Sybase::ASE(3)
2
6 DBIx::Class::Storage::DBI::Sybase::ASE - Sybase ASE SQL Server support
7 for DBIx::Class
8
10 This subclass supports DBD::Sybase for real (non-Microsoft) Sybase
11 databases.
12
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 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 A recommended "connect_info" in DBIx::Class::Storage::DBI setting:
24 on_connect_call => [['datetime_setup'], ['blob_setup', log_on_update => 0]]
26
28 connect_call_blob_setup
29 Used as:
30 on_connect_call => [ [ 'blob_setup', log_on_update => 0 ] ]
32 Does "$dbh->{syb_binary_images} = 1;" to return "IMAGE" data as raw
34 binary instead of as a hex string.
35 Recommended.
37 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 See
42 "Handling_IMAGE/TEXT_data_with_syb_ct_get_data()/syb_ct_send_data()" in
43 DBD::Sybase.
44 connect_call_datetime_setup
46 Used as:
47 on_connect_call => 'datetime_setup'
49 In "connect_info" in DBIx::Class::Storage::DBI to set:
51 $dbh->syb_date_fmt('ISO_strict'); # output fmt: 2004-08-21T14:36:48.080Z
53 $dbh->do('set dateformat mdy'); # input fmt: 08/13/1979 18:08:55.080
54 On connection for use with DBIx::Class::InflateColumn::DateTime, using
56 DateTime::Format::Sybase, which you will need to install.
57 This works for both "DATETIME" and "SMALLDATETIME" columns, although
59 "SMALLDATETIME" columns only have minute precision.
60
62 As of version 0.05000, DBIx::Class::Schema::Loader should work well
63 with most (if not all) versions of Sybase ASE.
64
66 This driver supports DBD::Sybase compiled against FreeTDS
67 (<http://www.freetds.org/>) to the best of our ability, however it is
68 recommended that you recompile DBD::Sybase against the Sybase Open
69 Client libraries. They are a part of the Sybase ASE distribution:
70 The Open Client FAQ is here:
72 <http://www.isug.com/Sybase_FAQ/ASE/section7.html>.
73 Sybase ASE for Linux (which comes with the Open Client libraries) may
75 be downloaded here:
76 <http://response.sybase.com/forms/ASE_Linux_Download>.
77 To see if you're using FreeTDS check "$schema->storage->using_freetds",
79 or run:
80 perl -MDBI -le 'my $dbh = DBI->connect($dsn, $user, $pass); print $dbh->{syb_oc_version}'
82 Some versions of the libraries involved will not support placeholders,
84 in which case the storage will be reblessed to
85 DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars.
86 In some configurations, placeholders will work but will throw implicit
88 type conversion errors for anything that's not expecting a string. In
89 such a case, the "auto_cast" option from
90 DBIx::Class::Storage::DBI::AutoCast is automatically set, which you may
91 enable on connection with "connect_call_set_auto_cast" in
92 DBIx::Class::Storage::DBI::AutoCast. The type info for the "CAST"s is
93 taken from the "data_type" in DBIx::Class::ResultSource definitions in
94 your Result classes, and are mapped to a Sybase type (if it isn't
95 already) using a mapping based on SQL::Translator.
96 In other configurations, placeholders will work just as they do with
98 the Sybase Open Client libraries.
99 Inserts or updates of TEXT/IMAGE columns will NOT work with FreeTDS.
101
103 With placeholders enabled, inserts are done in a transaction so that
104 there are no concurrency issues with getting the inserted identity
105 value using "SELECT MAX(col)", which is the only way to get the
106 "IDENTITY" value in this mode.
107 In addition, they are done on a separate connection so that it's
109 possible to have active cursors when doing an insert.
110 When using "DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars"
112 transactions are disabled, as there are no concurrency issues with
113 "SELECT @@IDENTITY" as it's a session variable.
114
116 Due to limitations of the TDS protocol, DBD::Sybase, or both, you
117 cannot begin a transaction while there are active cursors, nor can you
118 use multiple active cursors within a transaction. An active cursor is,
119 for example, a ResultSet that has been executed using "next" or "first"
120 but has not been exhausted or reset.
121 For example, this will not work:
123 $schema->txn_do(sub {
125 my $rs = $schema->resultset('Book');
126 while (my $row = $rs->next) {
127 $schema->resultset('MetaData')->create({
128 book_id => $row->id,
129 ...
130 });
131 }
132 });
133 This won't either:
135 my $first_row = $large_rs->first;
137 $schema->txn_do(sub { ... });
138 Transactions done for inserts in "AutoCommit" mode when placeholders
140 are in use are not affected, as they are done on an extra database
141 handle.
142 Some workarounds:
144 · use DBIx::Class::Storage::DBI::Replicated
146 · connect another Schema
148 · load the data from your cursor with "all" in DBIx::Class::ResultSet
150
152 The TDS protocol makes separate connections to the server for active
153 statements in the background. By default the number of such connections
154 is limited to 25, on both the client side and the server side.
155 This is a bit too low for a complex DBIx::Class application, so on
157 connection the client side setting is set to 256 (see "maxConnect" in
158 DBD::Sybase.) You can override it to whatever setting you like in the
159 DSN.
160 See
162 <http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1272.htm>
163 for information on changing the setting on the server side.
164
166 See "connect_call_datetime_setup" to setup date formats for
167 DBIx::Class::InflateColumn::DateTime.
168
170 DBD::Sybase compiled with FreeTDS will NOT allow you to insert or
171 update "TEXT/IMAGE" columns.
172 Setting "$dbh->{LongReadLen}" will also not work with FreeTDS use
174 either:
175 $schema->storage->dbh->do("SET TEXTSIZE $bytes");
177 or
179 $schema->storage->set_textsize($bytes);
181 instead.
183 However, the "LongReadLen" you pass in "connect_info" in
185 DBIx::Class::Storage::DBI is used to execute the equivalent "SET
186 TEXTSIZE" command on connection.
187 See "connect_call_blob_setup" for a "connect_info" in
189 DBIx::Class::Storage::DBI setting you need to work with "IMAGE"
190 columns.
191
193 The experimental DBD::Sybase Bulk API support is used for populate in
194 void context, in a transaction on a separate connection.
195 To use this feature effectively, use a large number of rows for each
197 populate call, eg.:
198 while (my $rows = $data_source->get_100_rows()) {
200 $rs->populate($rows);
201 }
202 NOTE: the add_columns calls in your "Result" classes must list columns
204 in database order for this to work. Also, you may have to unset the
205 "LANG" environment variable before loading your app, if it doesn't
206 match the character set of your database.
207 When inserting IMAGE columns using this method, you'll need to use
209 "connect_call_blob_setup" as well.
210
212 If you have columns such as:
213 created_dtm AS getdate()
215 represent them in your Result classes as:
217 created_dtm => {
219 data_type => undef,
220 default_value => \'getdate()',
221 is_nullable => 0,
222 }
223 The "data_type" must exist and must be "undef". Then empty inserts will
225 work on tables with such columns.
226
228 "timestamp" columns in Sybase ASE are not really timestamps, see:
229 http://dba.fyicenter.com/Interview-Questions/SYBASE/The_timestamp_datatype_in_Sybase_.html
230 <http://dba.fyicenter.com/Interview-
231 Questions/SYBASE/The_timestamp_datatype_in_Sybase_.html>.
232 They should be defined in your Result classes as:
234 ts => {
236 data_type => 'timestamp',
237 is_nullable => 0,
238 inflate_datetime => 0,
239 }
240 The "<inflate_datetime =" 0>> is necessary if you use
242 DBIx::Class::InflateColumn::DateTime, and most people do, and still
243 want to be able to read these values.
244 The values will come back as hexadecimal.
246
248 · Transitions to AutoCommit=0 (starting a transaction) mode by
249 exhausting any active cursors, using eager cursors.
250 · Real limits and limited counts using stored procedures deployed on
252 startup.
253 · Adaptive Server Anywhere (ASA) support, with possible SQLA::Limit
255 support.
256 · Blob update with a LIKE query on a blob, without invalidating the
258 WHERE condition.
259 · bulk_insert using prepare_cached (see comments.)
261
263 See "CONTRIBUTORS" in DBIx::Class.
264
266 You may distribute this code under the same terms as Perl itself.
267perl v5.12.0 2010D-B0I5x-:1:2Class::Storage::DBI::Sybase::ASE(3)