1DBIx::Class::Storage::DUBsIe:r:SCyobnatsrei:b:uAtSeEdD(B3PI)exr:l:CDloacsusm:e:nSttaotriaogne::DBI::Sybase::ASE(3)
2
3
4
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.0 2019D-B0I7x-:2:6Class::Storage::DBI::Sybase::ASE(3)