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 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 "Handling IMAGE/TEXT data with
42 syb_ct_get_data()/syb_ct_send_data()" in DBD::Sybase.
43 connect_call_datetime_setup
45 Used as:
46 on_connect_call => 'datetime_setup'
48 In connect_info to set:
50 $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 This works for both "DATETIME" and "SMALLDATETIME" columns, note that
55 "SMALLDATETIME" columns only have minute precision.
56
58 As of version 0.05000, DBIx::Class::Schema::Loader should work well
59 with most versions of Sybase ASE.
60
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 The Open Client FAQ is here:
68 <http://www.isug.com/Sybase_FAQ/ASE/section7.html>.
69 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 To see if you're using FreeTDS run:
75 perl -MDBI -le 'my $dbh = DBI->connect($dsn, $user, $pass); print $dbh->{syb_oc_version}'
77 It is recommended to set "tds version" for your ASE server to 5.0 in
79 "/etc/freetds/freetds.conf".
80 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 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 In other configurations, placeholders will work just as they do with
96 the Sybase Open Client libraries.
97 Inserts or updates of TEXT/IMAGE columns will NOT work with FreeTDS.
99
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 In addition, they are done on a separate connection so that it's
107 possible to have active cursors when doing an insert.
108 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
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 For example, this will not work:
121 $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 This won't either:
133 my $first_row = $large_rs->first;
135 $schema->txn_do(sub { ... });
136 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 Some workarounds:
142 • use DBIx::Class::Storage::DBI::Replicated
144 • connect another Schema
146 • load the data from your cursor with "all" in DBIx::Class::ResultSet
148
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 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 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
164 See "connect_call_datetime_setup" to setup date formats for
165 DBIx::Class::InflateColumn::DateTime.
166
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 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
177 DBD::Sybase compiled with FreeTDS will NOT allow you to insert or
178 update "TEXT/IMAGE" columns.
179 Setting "$dbh->{LongReadLen}" will also not work with FreeTDS use
181 either:
182 $schema->storage->dbh->do("SET TEXTSIZE $bytes");
184 or
186 $schema->storage->set_textsize($bytes);
188 instead.
190 However, the "LongReadLen" you pass in connect_info is used to execute
192 the equivalent "SET TEXTSIZE" command on connection.
193 See "connect_call_blob_setup" for a connect_info setting you need to
195 work with "IMAGE" columns.
196
198 The experimental DBD::Sybase Bulk API support is used for populate in
199 void context, in a transaction on a separate connection.
200 To use this feature effectively, use a large number of rows for each
202 populate call, eg.:
203 while (my $rows = $data_source->get_100_rows()) {
205 $rs->populate($rows);
206 }
207 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 When inserting IMAGE columns using this method, you'll need to use
214 "connect_call_blob_setup" as well.
215
217 If you have columns such as:
218 created_dtm AS getdate()
220 represent them in your Result classes as:
222 created_dtm => {
224 data_type => undef,
225 default_value => \'getdate()',
226 is_nullable => 0,
227 inflate_datetime => 1,
228 }
229 The "data_type" must exist and must be "undef". Then empty inserts will
231 work on tables with such columns.
232
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 They should be defined in your Result classes as:
238 ts => {
240 data_type => 'timestamp',
241 is_nullable => 0,
242 inflate_datetime => 0,
243 }
244 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 The values will come back as hexadecimal.
250
252 • Transitions to AutoCommit=0 (starting a transaction) mode by
253 exhausting any active cursors, using eager cursors.
254 • Real limits and limited counts using stored procedures deployed on
256 startup.
257 • Blob update with a LIKE query on a blob, without invalidating the
259 WHERE condition.
260 • bulk_insert using prepare_cached (see comments.)
262
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.
270perl v5.34.0 2022D-B0I1x-:2:1Class::Storage::DBI::Sybase::ASE(3)