Commit | Line | Data |
---|---|---|
86530b38 AT |
1 | .\" Automatically generated by Pod::Man v1.34, Pod::Parser v1.13 |
2 | .\" | |
3 | .\" Standard preamble: | |
4 | .\" ======================================================================== | |
5 | .de Sh \" Subsection heading | |
6 | .br | |
7 | .if t .Sp | |
8 | .ne 5 | |
9 | .PP | |
10 | \fB\\$1\fR | |
11 | .PP | |
12 | .. | |
13 | .de Sp \" Vertical space (when we can't use .PP) | |
14 | .if t .sp .5v | |
15 | .if n .sp | |
16 | .. | |
17 | .de Vb \" Begin verbatim text | |
18 | .ft CW | |
19 | .nf | |
20 | .ne \\$1 | |
21 | .. | |
22 | .de Ve \" End verbatim text | |
23 | .ft R | |
24 | .fi | |
25 | .. | |
26 | .\" Set up some character translations and predefined strings. \*(-- will | |
27 | .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left | |
28 | .\" double quote, and \*(R" will give a right double quote. | will give a | |
29 | .\" real vertical bar. \*(C+ will give a nicer C++. Capital omega is used to | |
30 | .\" do unbreakable dashes and therefore won't be available. \*(C` and \*(C' | |
31 | .\" expand to `' in nroff, nothing in troff, for use with C<>. | |
32 | .tr \(*W-|\(bv\*(Tr | |
33 | .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' | |
34 | .ie n \{\ | |
35 | . ds -- \(*W- | |
36 | . ds PI pi | |
37 | . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch | |
38 | . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch | |
39 | . ds L" "" | |
40 | . ds R" "" | |
41 | . ds C` "" | |
42 | . ds C' "" | |
43 | 'br\} | |
44 | .el\{\ | |
45 | . ds -- \|\(em\| | |
46 | . ds PI \(*p | |
47 | . ds L" `` | |
48 | . ds R" '' | |
49 | 'br\} | |
50 | .\" | |
51 | .\" If the F register is turned on, we'll generate index entries on stderr for | |
52 | .\" titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and index | |
53 | .\" entries marked with X<> in POD. Of course, you'll have to process the | |
54 | .\" output yourself in some meaningful fashion. | |
55 | .if \nF \{\ | |
56 | . de IX | |
57 | . tm Index:\\$1\t\\n%\t"\\$2" | |
58 | .. | |
59 | . nr % 0 | |
60 | . rr F | |
61 | .\} | |
62 | .\" | |
63 | .\" For nroff, turn off justification. Always turn off hyphenation; it makes | |
64 | .\" way too many mistakes in technical documents. | |
65 | .hy 0 | |
66 | .if n .na | |
67 | .\" | |
68 | .\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2). | |
69 | .\" Fear. Run. Save yourself. No user-serviceable parts. | |
70 | . \" fudge factors for nroff and troff | |
71 | .if n \{\ | |
72 | . ds #H 0 | |
73 | . ds #V .8m | |
74 | . ds #F .3m | |
75 | . ds #[ \f1 | |
76 | . ds #] \fP | |
77 | .\} | |
78 | .if t \{\ | |
79 | . ds #H ((1u-(\\\\n(.fu%2u))*.13m) | |
80 | . ds #V .6m | |
81 | . ds #F 0 | |
82 | . ds #[ \& | |
83 | . ds #] \& | |
84 | .\} | |
85 | . \" simple accents for nroff and troff | |
86 | .if n \{\ | |
87 | . ds ' \& | |
88 | . ds ` \& | |
89 | . ds ^ \& | |
90 | . ds , \& | |
91 | . ds ~ ~ | |
92 | . ds / | |
93 | .\} | |
94 | .if t \{\ | |
95 | . ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u" | |
96 | . ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u' | |
97 | . ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u' | |
98 | . ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u' | |
99 | . ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u' | |
100 | . ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u' | |
101 | .\} | |
102 | . \" troff and (daisy-wheel) nroff accents | |
103 | .ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V' | |
104 | .ds 8 \h'\*(#H'\(*b\h'-\*(#H' | |
105 | .ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#] | |
106 | .ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H' | |
107 | .ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u' | |
108 | .ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#] | |
109 | .ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#] | |
110 | .ds ae a\h'-(\w'a'u*4/10)'e | |
111 | .ds Ae A\h'-(\w'A'u*4/10)'E | |
112 | . \" corrections for vroff | |
113 | .if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u' | |
114 | .if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u' | |
115 | . \" for low resolution devices (crt and lpr) | |
116 | .if \n(.H>23 .if \n(.V>19 \ | |
117 | \{\ | |
118 | . ds : e | |
119 | . ds 8 ss | |
120 | . ds o a | |
121 | . ds d- d\h'-1'\(ga | |
122 | . ds D- D\h'-1'\(hy | |
123 | . ds th \o'bp' | |
124 | . ds Th \o'LP' | |
125 | . ds ae ae | |
126 | . ds Ae AE | |
127 | .\} | |
128 | .rm #[ #] #H #V #F C | |
129 | .\" ======================================================================== | |
130 | .\" | |
131 | .IX Title "DBI 3" | |
132 | .TH DBI 3 "2002-10-01" "perl v5.8.0" "User Contributed Perl Documentation" | |
133 | .SH "NAME" | |
134 | DBI \- Database independent interface for Perl | |
135 | .SH "SYNOPSIS" | |
136 | .IX Header "SYNOPSIS" | |
137 | .Vb 1 | |
138 | \& use DBI; | |
139 | .Ve | |
140 | .PP | |
141 | .Vb 2 | |
142 | \& @driver_names = DBI->available_drivers; | |
143 | \& @data_sources = DBI->data_sources($driver_name, \e%attr); | |
144 | .Ve | |
145 | .PP | |
146 | .Vb 1 | |
147 | \& $dbh = DBI->connect($data_source, $username, $auth, \e%attr); | |
148 | .Ve | |
149 | .PP | |
150 | .Vb 3 | |
151 | \& $rv = $dbh->do($statement); | |
152 | \& $rv = $dbh->do($statement, \e%attr); | |
153 | \& $rv = $dbh->do($statement, \e%attr, @bind_values); | |
154 | .Ve | |
155 | .PP | |
156 | .Vb 2 | |
157 | \& $ary_ref = $dbh->selectall_arrayref($statement); | |
158 | \& $ary_ref = $dbh->selectall_hashref($statement); | |
159 | .Ve | |
160 | .PP | |
161 | .Vb 1 | |
162 | \& $ary_ref = $dbh->selectcol_arrayref($statement); | |
163 | .Ve | |
164 | .PP | |
165 | .Vb 2 | |
166 | \& $ary_ref = $dbh->selectrow_arrayref($statement); | |
167 | \& @row_ary = $dbh->selectrow_array($statement); | |
168 | .Ve | |
169 | .PP | |
170 | .Vb 2 | |
171 | \& $sth = $dbh->prepare($statement); | |
172 | \& $sth = $dbh->prepare_cached($statement); | |
173 | .Ve | |
174 | .PP | |
175 | .Vb 3 | |
176 | \& $rv = $sth->bind_param($p_num, $bind_value); | |
177 | \& $rv = $sth->bind_param($p_num, $bind_value, $bind_type); | |
178 | \& $rv = $sth->bind_param($p_num, $bind_value, \e%attr); | |
179 | .Ve | |
180 | .PP | |
181 | .Vb 2 | |
182 | \& $rv = $sth->execute; | |
183 | \& $rv = $sth->execute(@bind_values); | |
184 | .Ve | |
185 | .PP | |
186 | .Vb 2 | |
187 | \& $rc = $sth->bind_col($col_num, \e$col_variable); | |
188 | \& $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind); | |
189 | .Ve | |
190 | .PP | |
191 | .Vb 3 | |
192 | \& @row_ary = $sth->fetchrow_array; | |
193 | \& $ary_ref = $sth->fetchrow_arrayref; | |
194 | \& $hash_ref = $sth->fetchrow_hashref; | |
195 | .Ve | |
196 | .PP | |
197 | .Vb 1 | |
198 | \& $ary_ref = $sth->fetchall_arrayref; | |
199 | .Ve | |
200 | .PP | |
201 | .Vb 1 | |
202 | \& $rv = $sth->rows; | |
203 | .Ve | |
204 | .PP | |
205 | .Vb 2 | |
206 | \& $rc = $dbh->commit; | |
207 | \& $rc = $dbh->rollback; | |
208 | .Ve | |
209 | .PP | |
210 | .Vb 1 | |
211 | \& $sql = $dbh->quote($string); | |
212 | .Ve | |
213 | .PP | |
214 | .Vb 3 | |
215 | \& $rc = $h->err; | |
216 | \& $str = $h->errstr; | |
217 | \& $rv = $h->state; | |
218 | .Ve | |
219 | .PP | |
220 | .Vb 1 | |
221 | \& $rc = $dbh->disconnect; | |
222 | .Ve | |
223 | .PP | |
224 | \&\fIThis synopsis above only lists the major methods.\fR | |
225 | .Sh "\s-1GETTING\s0 \s-1HELP\s0" | |
226 | .IX Subsection "GETTING HELP" | |
227 | If you have questions about \s-1DBI\s0, you can get help from | |
228 | the \fIdbi\-users@perl.org\fR mailing list. | |
229 | You can subscribe to the list by emailing: | |
230 | .PP | |
231 | .Vb 1 | |
232 | \& dbi-users-help@perl.org | |
233 | .Ve | |
234 | .PP | |
235 | Also worth a visit is the \s-1DBI\s0 home page at: | |
236 | .PP | |
237 | .Vb 1 | |
238 | \& http://dbi.perl.org/ | |
239 | .Ve | |
240 | .PP | |
241 | Before asking any questions, reread this document, consult the | |
242 | archives and read the \s-1DBI\s0 \s-1FAQ\s0. The archives are listed | |
243 | at the end of this document. | |
244 | The \s-1FAQ\s0 is installed as a \s-1DBI::FAQ\s0 module so | |
245 | you can read it by executing \f(CW\*(C`perldoc DBI::FAQ\*(C'\fR. | |
246 | .PP | |
247 | Please note that Tim Bunce does not maintain the mailing lists or the | |
248 | web page (generous volunteers do that). So please don't send mail | |
249 | directly to him; he just doesn't have the time to answer questions | |
250 | personally. The \fIdbi-users\fR mailing list has lots of experienced | |
251 | people who should be able to help you if you need it. | |
252 | .Sh "\s-1NOTE\s0" | |
253 | .IX Subsection "NOTE" | |
254 | This is the \s-1DBI\s0 specification that corresponds to the \s-1DBI\s0 version 1.17 | |
255 | (\f(CW\*(C`$Date: 2001/06/04 17:20:21 $\*(C'\fR). | |
256 | .PP | |
257 | The \s-1DBI\s0 specification is evolving at a steady pace, so it's | |
258 | important to check that you have the latest copy. The \s-1RECENT\s0 \s-1CHANGES\s0 | |
259 | section below has a summary of user-visible changes. The \fIChanges\fR | |
260 | file supplied with the \s-1DBI\s0 holds more detailed change information. | |
261 | .PP | |
262 | Note also that whenever the \s-1DBI\s0 changes, the drivers take some time to | |
263 | catch up. Recent versions of the \s-1DBI\s0 have added new features | |
264 | (marked \fI\s-1NEW\s0\fR in the text) that may not yet be supported by the drivers | |
265 | you use. Talk to the authors of those drivers if you need the features. | |
266 | .PP | |
267 | Extensions to the \s-1DBI\s0 and other \s-1DBI\s0 related modules use the \f(CW\*(C`DBIx::*\*(C'\fR | |
268 | namespace. See \*(L"Naming Conventions and Name Space\*(R" and: | |
269 | .PP | |
270 | .Vb 1 | |
271 | \& http://www.perl.com/CPAN/modules/by-module/DBIx/ | |
272 | .Ve | |
273 | .Sh "\s-1RECENT\s0 \s-1CHANGES\s0" | |
274 | .IX Subsection "RECENT CHANGES" | |
275 | Here is a brief summary of significant user-visible changes in recent versions. | |
276 | (If a recent version isn't mentioned, it simply means that there were no | |
277 | significant user-visible changes in that version.) | |
278 | .IP "\s-1DBI\s0 1.15" 4 | |
279 | .IX Item "DBI 1.15" | |
280 | Added selectall_hashref, selectrow_hashref, selectrow_arrayref methods. | |
281 | .SH "DESCRIPTION" | |
282 | .IX Header "DESCRIPTION" | |
283 | The \s-1DBI\s0 is a database access module for the Perl programming language. It defines | |
284 | a set of methods, variables, and conventions that provide a consistent | |
285 | database interface, independent of the actual database being used. | |
286 | .PP | |
287 | It is important to remember that the \s-1DBI\s0 is just an interface. | |
288 | The \s-1DBI\s0 is a layer | |
289 | of \*(L"glue\*(R" between an application and one or more database \fIdriver\fR | |
290 | modules. It is the driver modules which do most of the real work. The \s-1DBI\s0 | |
291 | provides a standard interface and framework for the drivers to operate | |
292 | within. | |
293 | .Sh "Architecture of a \s-1DBI\s0 Application" | |
294 | .IX Subsection "Architecture of a DBI Application" | |
295 | .Vb 11 | |
296 | \& |<- Scope of DBI ->| | |
297 | \& .-. .--------------. .-------------. | |
298 | \& .-------. | |---| XYZ Driver |---| XYZ Engine | | |
299 | \& | Perl | | | `--------------' `-------------' | |
300 | \& | script| |A| |D| .--------------. .-------------. | |
301 | \& | using |--|P|--|B|---|Oracle Driver |---|Oracle Engine| | |
302 | \& | DBI | |I| |I| `--------------' `-------------' | |
303 | \& | API | | |... | |
304 | \& |methods| | |... Other drivers | |
305 | \& `-------' | |... | |
306 | \& `-' | |
307 | .Ve | |
308 | .PP | |
309 | The \s-1API\s0, or Application Programming Interface, defines the | |
310 | call interface and variables for Perl scripts to use. The \s-1API\s0 | |
311 | is implemented by the Perl \s-1DBI\s0 extension. | |
312 | .PP | |
313 | The \s-1DBI\s0 \*(L"dispatches\*(R" the method calls to the appropriate driver for | |
314 | actual execution. The \s-1DBI\s0 is also responsible for the dynamic loading | |
315 | of drivers, error checking and handling, providing default | |
316 | implementations for methods, and many other non-database specific duties. | |
317 | .PP | |
318 | Each driver | |
319 | contains implementations of the \s-1DBI\s0 methods using the | |
320 | private interface functions of the corresponding database engine. Only authors | |
321 | of sophisticated/multi\-database applications or generic library | |
322 | functions need be concerned with drivers. | |
323 | .Sh "Notation and Conventions" | |
324 | .IX Subsection "Notation and Conventions" | |
325 | The following conventions are used in this document: | |
326 | .PP | |
327 | .Vb 11 | |
328 | \& $dbh Database handle object | |
329 | \& $sth Statement handle object | |
330 | \& $drh Driver handle object (rarely seen or used in applications) | |
331 | \& $h Any of the handle types above ($dbh, $sth, or $drh) | |
332 | \& $rc General Return Code (boolean: true=ok, false=error) | |
333 | \& $rv General Return Value (typically an integer) | |
334 | \& @ary List of values returned from the database, typically a row of data | |
335 | \& $rows Number of rows processed (if available, else -1) | |
336 | \& $fh A filehandle | |
337 | \& undef NULL values are represented by undefined values in Perl | |
338 | \& \e%attr Reference to a hash of attribute values passed to methods | |
339 | .Ve | |
340 | .PP | |
341 | Note that Perl will automatically destroy database and statement handle objects | |
342 | if all references to them are deleted. | |
343 | .Sh "Outline Usage" | |
344 | .IX Subsection "Outline Usage" | |
345 | To use \s-1DBI\s0, | |
346 | first you need to load the \s-1DBI\s0 module: | |
347 | .PP | |
348 | .Vb 2 | |
349 | \& use DBI; | |
350 | \& use strict; | |
351 | .Ve | |
352 | .PP | |
353 | (The \f(CW\*(C`use strict;\*(C'\fR isn't required but is strongly recommended.) | |
354 | .PP | |
355 | Then you need to \*(L"connect\*(R" to your data source and get a \fIhandle\fR for that | |
356 | connection: | |
357 | .PP | |
358 | .Vb 2 | |
359 | \& $dbh = DBI->connect($dsn, $user, $password, | |
360 | \& { RaiseError => 1, AutoCommit => 0 }); | |
361 | .Ve | |
362 | .PP | |
363 | Since connecting can be expensive, you generally just connect at the | |
364 | start of your program and disconnect at the end. | |
365 | .PP | |
366 | Explicitly defining the required \f(CW\*(C`AutoCommit\*(C'\fR behavior is strongly | |
367 | recommended and may become mandatory in a later version. This | |
368 | determines whether changes are automatically committed to the | |
369 | database when executed, or need to be explicitly committed later. | |
370 | .PP | |
371 | The \s-1DBI\s0 allows an application to \*(L"prepare\*(R" statements for later | |
372 | execution. A prepared statement is identified by a statement handle | |
373 | held in a Perl variable. | |
374 | We'll call the Perl variable \f(CW$sth\fR in our examples. | |
375 | .PP | |
376 | The typical method call sequence for a \f(CW\*(C`SELECT\*(C'\fR statement is: | |
377 | .PP | |
378 | .Vb 4 | |
379 | \& prepare, | |
380 | \& execute, fetch, fetch, ... | |
381 | \& execute, fetch, fetch, ... | |
382 | \& execute, fetch, fetch, ... | |
383 | .Ve | |
384 | .PP | |
385 | for example: | |
386 | .PP | |
387 | .Vb 1 | |
388 | \& $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?"); | |
389 | .Ve | |
390 | .PP | |
391 | .Vb 1 | |
392 | \& $sth->execute( $baz ); | |
393 | .Ve | |
394 | .PP | |
395 | .Vb 3 | |
396 | \& while ( @row = $sth->fetchrow_array ) { | |
397 | \& print "@row\en"; | |
398 | \& } | |
399 | .Ve | |
400 | .PP | |
401 | The typical method call sequence for a \fInon\fR\-\f(CW\*(C`SELECT\*(C'\fR statement is: | |
402 | .PP | |
403 | .Vb 4 | |
404 | \& prepare, | |
405 | \& execute, | |
406 | \& execute, | |
407 | \& execute. | |
408 | .Ve | |
409 | .PP | |
410 | for example: | |
411 | .PP | |
412 | .Vb 1 | |
413 | \& $sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)"); | |
414 | .Ve | |
415 | .PP | |
416 | .Vb 5 | |
417 | \& while(<CSV>) { | |
418 | \& chomp; | |
419 | \& my ($foo,$bar,$baz) = split /,/; | |
420 | \& $sth->execute( $foo, $bar, $baz ); | |
421 | \& } | |
422 | .Ve | |
423 | .PP | |
424 | The \f(CW\*(C`do()\*(C'\fR method can be used for non repeated \fInon\fR\-\f(CW\*(C`SELECT\*(C'\fR statement | |
425 | (or with drivers that don't support placeholders): | |
426 | .PP | |
427 | .Vb 1 | |
428 | \& $rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1"); | |
429 | .Ve | |
430 | .PP | |
431 | To commit your changes to the database (when \*(L"AutoCommit\*(R" is off): | |
432 | .PP | |
433 | .Vb 1 | |
434 | \& $dbh->commit; # or call $dbh->rollback; to undo changes | |
435 | .Ve | |
436 | .PP | |
437 | Finally, when you have finished working with the data source, you should | |
438 | \&\*(L"disconnect\*(R" from it: | |
439 | .PP | |
440 | .Vb 1 | |
441 | \& $dbh->disconnect; | |
442 | .Ve | |
443 | .Sh "General Interface Rules & Caveats" | |
444 | .IX Subsection "General Interface Rules & Caveats" | |
445 | The \s-1DBI\s0 does not have a concept of a \*(L"current session\*(R". Every session | |
446 | has a handle object (i.e., a \f(CW$dbh\fR) returned from the \f(CW\*(C`connect\*(C'\fR method. | |
447 | That handle object is used to invoke database related methods. | |
448 | .PP | |
449 | Most data is returned to the Perl script as strings. (Null values are | |
450 | returned as \f(CW\*(C`undef\*(C'\fR.) This allows arbitrary precision numeric data to be | |
451 | handled without loss of accuracy. Beware that Perl may not preserve | |
452 | the same accuracy when the string is used as a number. | |
453 | .PP | |
454 | Dates and times are returned as character strings in the current | |
455 | default format of the corresponding database engine. Time zone effects | |
456 | are database/driver dependent. | |
457 | .PP | |
458 | Perl supports binary data in Perl strings, and the \s-1DBI\s0 will pass binary | |
459 | data to and from the driver without change. It is up to the driver | |
460 | implementors to decide how they wish to handle such binary data. | |
461 | .PP | |
462 | Most databases that understand multiple character sets have a | |
463 | default global charset. Text stored in the database is, or should | |
464 | be, stored in that charset; if not, then that's the fault of either | |
465 | the database or the application that inserted the data. When text is | |
466 | fetched it should be automatically converted to the charset of the | |
467 | client, presumably based on the locale. If a driver needs to set a | |
468 | flag to get that behavior, then it should do so; it should not require | |
469 | the application to do that. | |
470 | .PP | |
471 | Multiple \s-1SQL\s0 statements may not be combined in a single statement | |
472 | handle (\f(CW$sth\fR), although some databases and drivers do support this | |
473 | (notably Sybase and \s-1SQL\s0 Server). | |
474 | .PP | |
475 | Non-sequential record reads are not supported in this version of the \s-1DBI\s0. | |
476 | In other words, records can only be fetched in the order that the | |
477 | database returned them, and once fetched they are forgotten. | |
478 | .PP | |
479 | Positioned updates and deletes are not directly supported by the \s-1DBI\s0. | |
480 | See the description of the \f(CW\*(C`CursorName\*(C'\fR attribute for an alternative. | |
481 | .PP | |
482 | Individual driver implementors are free to provide any private | |
483 | functions and/or handle attributes that they feel are useful. | |
484 | Private driver functions can be invoked using the \s-1DBI\s0 \f(CW\*(C`func()\*(C'\fR method. | |
485 | Private driver attributes are accessed just like standard attributes. | |
486 | .PP | |
487 | Many methods have an optional \f(CW\*(C`\e%attr\*(C'\fR parameter which can be used to | |
488 | pass information to the driver implementing the method. Except where | |
489 | specifically documented, the \f(CW\*(C`\e%attr\*(C'\fR parameter can only be used to pass | |
490 | driver specific hints. In general, you can ignore \f(CW\*(C`\e%attr\*(C'\fR parameters | |
491 | or pass it as \f(CW\*(C`undef\*(C'\fR. | |
492 | .Sh "Naming Conventions and Name Space" | |
493 | .IX Subsection "Naming Conventions and Name Space" | |
494 | The \s-1DBI\s0 package and all packages below it (\f(CW\*(C`DBI::*\*(C'\fR) are reserved for | |
495 | use by the \s-1DBI\s0. Extensions and related modules use the \f(CW\*(C`DBIx::\*(C'\fR | |
496 | namespace (see \f(CW\*(C`http://www.perl.com/CPAN/modules/by\-module/DBIx/\*(C'\fR). | |
497 | Package names beginning with \f(CW\*(C`DBD::\*(C'\fR are reserved for use | |
498 | by \s-1DBI\s0 database drivers. All environment variables used by the \s-1DBI\s0 | |
499 | or by individual DBDs begin with "\f(CW\*(C`DBI_\*(C'\fR\*(L" or \*(R"\f(CW\*(C`DBD_\*(C'\fR". | |
500 | .PP | |
501 | The letter case used for attribute names is significant and plays an | |
502 | important part in the portability of \s-1DBI\s0 scripts. The case of the | |
503 | attribute name is used to signify who defined the meaning of that name | |
504 | and its values. | |
505 | .PP | |
506 | .Vb 5 | |
507 | \& Case of name Has a meaning defined by | |
508 | \& ------------ ------------------------ | |
509 | \& UPPER_CASE Standards, e.g., X/Open, ISO SQL92 etc (portable) | |
510 | \& MixedCase DBI API (portable), underscores are not used. | |
511 | \& lower_case Driver or database engine specific (non-portable) | |
512 | .Ve | |
513 | .PP | |
514 | It is of the utmost importance that Driver developers only use | |
515 | lowercase attribute names when defining private attributes. Private | |
516 | attribute names must be prefixed with the driver name or suitable | |
517 | abbreviation (e.g., "\f(CW\*(C`ora_\*(C'\fR\*(L" for Oracle, \*(R"\f(CW\*(C`ing_\*(C'\fR" for Ingres, etc). | |
518 | .PP | |
519 | Driver Specific Prefix Registry: | |
520 | .PP | |
521 | .Vb 24 | |
522 | \& ad_ DBD::AnyData | |
523 | \& ado_ DBD::ADO | |
524 | \& best_ DBD::BestWins | |
525 | \& csv_ DBD::CSV | |
526 | \& db2_ DBD::DB2 | |
527 | \& f_ DBD::File | |
528 | \& file_ DBD::TextFile | |
529 | \& ib_ DBD::InterBase | |
530 | \& ing_ DBD::Ingres | |
531 | \& ix_ DBD::Informix | |
532 | \& msql_ DBD::mSQL | |
533 | \& mysql_ DBD::mysql | |
534 | \& odbc_ DBD::ODBC | |
535 | \& ora_ DBD::Oracle | |
536 | \& pg_ DBD::Pg | |
537 | \& proxy_ DBD::Proxy | |
538 | \& rdb_ DBD::RDB | |
539 | \& sapdb_ DBD::SAP_DB | |
540 | \& solid_ DBD::Solid | |
541 | \& syb_ DBD::Sybase | |
542 | \& tdat_ DBD::Teradata | |
543 | \& tuber_ DBD::Tuber | |
544 | \& uni_ DBD::Unify | |
545 | \& xbase_ DBD::XBase | |
546 | .Ve | |
547 | .Sh "\s-1SQL\s0 \- A Query Language" | |
548 | .IX Subsection "SQL - A Query Language" | |
549 | Most \s-1DBI\s0 drivers require applications to use a dialect of \s-1SQL\s0 | |
550 | (Structured Query Language) to interact with the database engine. | |
551 | The following links provide useful information and further links about | |
552 | \&\s-1SQL:\s0 | |
553 | .PP | |
554 | .Vb 3 | |
555 | \& http://www.altavista.com/query?q=sql+tutorial | |
556 | \& http://www.jcc.com/sql_stnd.html | |
557 | \& http://www.contrib.andrew.cmu.edu/~shadow/sql.html | |
558 | .Ve | |
559 | .PP | |
560 | The \s-1DBI\s0 itself does not mandate or require any particular language to | |
561 | be used; it is language independent. In \s-1ODBC\s0 terms, the \s-1DBI\s0 is in | |
562 | \&\*(L"pass\-thru\*(R" mode, although individual drivers might not be. The only requirement | |
563 | is that queries and other statements must be expressed as a single | |
564 | string of characters passed as the first argument to the \*(L"prepare\*(R" or | |
565 | \&\*(L"do\*(R" methods. | |
566 | .PP | |
567 | For an interesting diversion on the \fIreal\fR history of \s-1RDBMS\s0 and \s-1SQL\s0, | |
568 | from the people who made it happen, see: | |
569 | .PP | |
570 | .Vb 1 | |
571 | \& http://ftp.digital.com/pub/DEC/SRC/technical-notes/SRC-1997-018-html/sqlr95.html | |
572 | .Ve | |
573 | .PP | |
574 | Follow the \*(L"And the rest\*(R" and \*(L"Intergalactic dataspeak\*(R" links for the | |
575 | \&\s-1SQL\s0 history. | |
576 | .Sh "Placeholders and Bind Values" | |
577 | .IX Subsection "Placeholders and Bind Values" | |
578 | Some drivers support placeholders and bind values. | |
579 | \&\fIPlaceholders\fR, also called parameter markers, are used to indicate | |
580 | values in a database statement that will be supplied later, | |
581 | before the prepared statement is executed. For example, an application | |
582 | might use the following to insert a row of data into the \s-1SALES\s0 table: | |
583 | .PP | |
584 | .Vb 1 | |
585 | \& INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?) | |
586 | .Ve | |
587 | .PP | |
588 | or the following, to select the description for a product: | |
589 | .PP | |
590 | .Vb 1 | |
591 | \& SELECT description FROM products WHERE product_code = ? | |
592 | .Ve | |
593 | .PP | |
594 | The \f(CW\*(C`?\*(C'\fR characters are the placeholders. The association of actual | |
595 | values with placeholders is known as \fIbinding\fR, and the values are | |
596 | referred to as \fIbind values\fR. | |
597 | .PP | |
598 | When using placeholders with the \s-1SQL\s0 \f(CW\*(C`LIKE\*(C'\fR qualifier, you must | |
599 | remember that the placeholder substitutes for the whole string. | |
600 | So you should use "\f(CW\*(C`... LIKE ? ...\*(C'\fR" and include any wildcard | |
601 | characters in the value that you bind to the placeholder. | |
602 | .PP | |
603 | \&\fBNull Values\fR | |
604 | .PP | |
605 | Undefined values, or \f(CW\*(C`undef\*(C'\fR, can be used to indicate null values. | |
606 | However, care must be taken in the particular case of trying to use | |
607 | null values to qualify a \f(CW\*(C`SELECT\*(C'\fR statement. Consider: | |
608 | .PP | |
609 | .Vb 1 | |
610 | \& SELECT description FROM products WHERE product_code = ? | |
611 | .Ve | |
612 | .PP | |
613 | Binding an \f(CW\*(C`undef\*(C'\fR (\s-1NULL\s0) to the placeholder will \fInot\fR select rows | |
614 | which have a \s-1NULL\s0 \f(CW\*(C`product_code\*(C'\fR! Refer to the \s-1SQL\s0 manual for your database | |
615 | engine or any \s-1SQL\s0 book for the reasons for this. To explicitly select | |
616 | NULLs you have to say "\f(CW\*(C`WHERE product_code IS NULL\*(C'\fR" and to make that | |
617 | general you have to say: | |
618 | .PP | |
619 | .Vb 1 | |
620 | \& ... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL)) | |
621 | .Ve | |
622 | .PP | |
623 | and bind the same value to both placeholders. | |
624 | .PP | |
625 | \&\fBPerformance\fR | |
626 | .PP | |
627 | Without using placeholders, the insert statement shown previously would have to | |
628 | contain the literal values to be inserted and would have to be | |
629 | re-prepared and re-executed for each row. With placeholders, the insert | |
630 | statement only needs to be prepared once. The bind values for each row | |
631 | can be given to the \f(CW\*(C`execute\*(C'\fR method each time it's called. By avoiding | |
632 | the need to re-prepare the statement for each row, the application | |
633 | typically runs many times faster. Here's an example: | |
634 | .PP | |
635 | .Vb 9 | |
636 | \& my $sth = $dbh->prepare(q{ | |
637 | \& INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?) | |
638 | \& }) or die $dbh->errstr; | |
639 | \& while (<>) { | |
640 | \& chomp; | |
641 | \& my ($product_code, $qty, $price) = split /,/; | |
642 | \& $sth->execute($product_code, $qty, $price) or die $dbh->errstr; | |
643 | \& } | |
644 | \& $dbh->commit or die $dbh->errstr; | |
645 | .Ve | |
646 | .PP | |
647 | See \*(L"execute\*(R" and \*(L"bind_param\*(R" for more details. | |
648 | .PP | |
649 | The \f(CW\*(C`q{...}\*(C'\fR style quoting used in this example avoids clashing with | |
650 | quotes that may be used in the \s-1SQL\s0 statement. Use the double-quote like | |
651 | \&\f(CW\*(C`qq{...}\*(C'\fR operator if you want to interpolate variables into the string. | |
652 | See \*(L"Quote and Quote-like Operators\*(R" in perlop for more details. | |
653 | .PP | |
654 | See also the \*(L"bind_column\*(R" method, which is used to associate Perl | |
655 | variables with the output columns of a \f(CW\*(C`SELECT\*(C'\fR statement. | |
656 | .SH "THE DBI PACKAGE AND CLASS" | |
657 | .IX Header "THE DBI PACKAGE AND CLASS" | |
658 | In this section, we cover the \s-1DBI\s0 class methods, utility functions, | |
659 | and the dynamic attributes associated with generic \s-1DBI\s0 handles. | |
660 | .Sh "\s-1DBI\s0 Constants" | |
661 | .IX Subsection "DBI Constants" | |
662 | The following \s-1SQL\s0 standard type constants can be imported individually | |
663 | or, by importing the special \f(CW\*(C`:sql_types\*(C'\fR tag, all together: | |
664 | .PP | |
665 | .Vb 8 | |
666 | \& SQL_CHAR SQL_NUMERIC SQL_DECIMAL SQL_INTEGER SQL_SMALLINT | |
667 | \& SQL_FLOAT SQL_REAL SQL_DOUBLE SQL_VARCHAR | |
668 | \& SQL_DATE SQL_TIME SQL_TIMESTAMP | |
669 | \& SQL_LONGVARCHAR SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY | |
670 | \& SQL_BIGINT SQL_TINYINT | |
671 | \& SQL_WCHAR SQL_WVARCHAR SQL_WLONGVARCHAR | |
672 | \& SQL_BIT | |
673 | \& SQL_ALL_TYPES | |
674 | .Ve | |
675 | .PP | |
676 | See the \*(L"type_info\*(R", \*(L"type_info_all\*(R", and \*(L"bind_param\*(R" methods | |
677 | for possible uses. | |
678 | .Sh "\s-1DBI\s0 Class Methods" | |
679 | .IX Subsection "DBI Class Methods" | |
680 | The following methods are provided by the \s-1DBI\s0 class: | |
681 | .ie n .IP """connect""" 4 | |
682 | .el .IP "\f(CWconnect\fR" 4 | |
683 | .IX Item "connect" | |
684 | .Vb 4 | |
685 | \& $dbh = DBI->connect($data_source, $username, $password) | |
686 | \& or die $DBI::errstr; | |
687 | \& $dbh = DBI->connect($data_source, $username, $password, \e%attr) | |
688 | \& or die $DBI::errstr; | |
689 | .Ve | |
690 | .Sp | |
691 | Establishes a database connection, or session, to the requested \f(CW$data_source\fR. | |
692 | Returns a database handle object if the connection succeeds. Use | |
693 | \&\f(CW\*(C`$dbh\-\*(C'\fR>\f(CW\*(C`disconnect\*(C'\fR to terminate the connection. | |
694 | .Sp | |
695 | If the connect fails (see below), it returns \f(CW\*(C`undef\*(C'\fR and sets both \f(CW$DBI::err\fR | |
696 | and \f(CW$DBI::errstr\fR. (It does \fInot\fR set \f(CW$!\fR, etc.) You should generally | |
697 | test the return status of \f(CW\*(C`connect\*(C'\fR and \f(CW\*(C`print $DBI::errstr\*(C'\fR if it has failed. | |
698 | .Sp | |
699 | Multiple simultaneous connections to multiple databases through multiple | |
700 | drivers can be made via the \s-1DBI\s0. Simply make one \f(CW\*(C`connect\*(C'\fR call for each | |
701 | database and keep a copy of each returned database handle. | |
702 | .Sp | |
703 | The \f(CW$data_source\fR value should begin with "\f(CW\*(C`dbi:\*(C'\fR\fIdriver_name\fR\f(CW\*(C`:\*(C'\fR". The | |
704 | \&\fIdriver_name\fR specifies the driver that will be used to make the | |
705 | connection. (Letter case is significant.) | |
706 | .Sp | |
707 | As a convenience, if the \f(CW$data_source\fR parameter is undefined or empty, the | |
708 | \&\s-1DBI\s0 will substitute the value of the environment variable \f(CW\*(C`DBI_DSN\*(C'\fR. | |
709 | If just the \fIdriver_name\fR part is empty (i.e., the \f(CW$data_source\fR prefix is "\f(CW\*(C`dbi::\*(C'\fR"), | |
710 | the environment variable \f(CW\*(C`DBI_DRIVER\*(C'\fR is used. If neither variable is set, | |
711 | then \f(CW\*(C`connect\*(C'\fR dies. | |
712 | .Sp | |
713 | Examples of \f(CW$data_source\fR values are: | |
714 | .Sp | |
715 | .Vb 3 | |
716 | \& dbi:DriverName:database_name | |
717 | \& dbi:DriverName:database_name@hostname:port | |
718 | \& dbi:DriverName:database=database_name;host=hostname;port=port | |
719 | .Ve | |
720 | .Sp | |
721 | There is \fIno standard\fR for the text following the driver name. Each | |
722 | driver is free to use whatever syntax it wants. The only requirement the | |
723 | \&\s-1DBI\s0 makes is that all the information is supplied in a single string. | |
724 | You must consult the documentation for the drivers you are using for a | |
725 | description of the syntax they require. (Where a driver author needs | |
726 | to define a syntax for the \f(CW$data_source\fR, it is recommended that | |
727 | they follow the \s-1ODBC\s0 style, shown in the last example above.) | |
728 | .Sp | |
729 | If the environment variable \f(CW\*(C`DBI_AUTOPROXY\*(C'\fR is defined (and the driver in | |
730 | \&\f(CW$data_source\fR is not "\f(CW\*(C`Proxy\*(C'\fR") then the connect request will | |
731 | automatically be changed to: | |
732 | .Sp | |
733 | .Vb 1 | |
734 | \& dbi:Proxy:$ENV{DBI_AUTOPROXY};dsn=$data_source | |
735 | .Ve | |
736 | .Sp | |
737 | and passed to the DBD::Proxy module. \f(CW\*(C`DBI_AUTOPROXY\*(C'\fR is typically set as | |
738 | "\f(CW\*(C`hostname=...;port=...\*(C'\fR". See the DBD::Proxy documentation for more details. | |
739 | .Sp | |
740 | If \f(CW$username\fR or \f(CW$password\fR are undefined (rather than just empty), | |
741 | then the \s-1DBI\s0 will substitute the values of the \f(CW\*(C`DBI_USER\*(C'\fR and \f(CW\*(C`DBI_PASS\*(C'\fR | |
742 | environment variables, respectively. The \s-1DBI\s0 will warn if the | |
743 | environment variables are not defined. However, the everyday use of | |
744 | these environment | |
745 | variables is not recommended for security reasons. The mechanism is | |
746 | primarily intended to simplify testing. | |
747 | .Sp | |
748 | \&\f(CW\*(C`DBI\-\*(C'\fR>\f(CW\*(C`connect\*(C'\fR automatically installs the driver if it has not been | |
749 | installed yet. Driver installation either returns a valid driver | |
750 | handle, or it \fIdies\fR with an error message that includes the string | |
751 | "\f(CW\*(C`install_driver\*(C'\fR" and the underlying problem. So \f(CW\*(C`DBI\-\*(C'\fR>\f(CW\*(C`connect\*(C'\fR | |
752 | will die | |
753 | on a driver installation failure and will only return \f(CW\*(C`undef\*(C'\fR on a | |
754 | connect failure, in which case \f(CW$DBI::errstr\fR will hold the error message. | |
755 | .Sp | |
756 | The \f(CW$data_source\fR argument (with the "\f(CW\*(C`dbi:...:\*(C'\fR" prefix removed) and the | |
757 | \&\f(CW$username\fR and \f(CW$password\fR arguments are then passed to the driver for | |
758 | processing. The \s-1DBI\s0 does not define any interpretation for the | |
759 | contents of these fields. The driver is free to interpret the | |
760 | \&\f(CW$data_source\fR, \f(CW$username\fR, and \f(CW$password\fR fields in any way, and supply | |
761 | whatever defaults are appropriate for the engine being accessed. | |
762 | (Oracle, for example, uses the \s-1ORACLE_SID\s0 and \s-1TWO_TASK\s0 environment | |
763 | variables if no \f(CW$data_source\fR is specified.) | |
764 | .Sp | |
765 | The \f(CW\*(C`AutoCommit\*(C'\fR and \f(CW\*(C`PrintError\*(C'\fR attributes for each connection default to | |
766 | \&\*(L"on\*(R". (See \*(L"AutoCommit\*(R" and \*(L"PrintError\*(R" for more information.) | |
767 | However, it is strongly recommended that you explicitly define \f(CW\*(C`AutoCommit\*(C'\fR | |
768 | rather than rely on the default. Future versions of | |
769 | the \s-1DBI\s0 may issue a warning if \f(CW\*(C`AutoCommit\*(C'\fR is not explicitly defined. | |
770 | .Sp | |
771 | The \f(CW\*(C`\e%attr\*(C'\fR parameter can be used to alter the default settings of | |
772 | \&\f(CW\*(C`PrintError\*(C'\fR, \f(CW\*(C`RaiseError\*(C'\fR, \f(CW\*(C`AutoCommit\*(C'\fR, and other attributes. For example: | |
773 | .Sp | |
774 | .Vb 4 | |
775 | \& $dbh = DBI->connect($data_source, $user, $pass, { | |
776 | \& PrintError => 0, | |
777 | \& AutoCommit => 0 | |
778 | \& }); | |
779 | .Ve | |
780 | .Sp | |
781 | You can also define connection attribute values within the \f(CW$data_source\fR | |
782 | parameter. For example: | |
783 | .Sp | |
784 | .Vb 1 | |
785 | \& dbi:DriverName(PrintError=>0,Taint=>1):... | |
786 | .Ve | |
787 | .Sp | |
788 | Individual attributes values specified in this way take precedence over | |
789 | any conflicting values specified via the \f(CW\*(C`\e%attr\*(C'\fR parameter to \f(CW\*(C`connect\*(C'\fR. | |
790 | .Sp | |
791 | The \f(CW\*(C`dbi_connect_method\*(C'\fR attribute can be used to specify which driver | |
792 | method should be called to establish the connection. The only useful | |
793 | values are 'connect', 'connect_cached', or some specialized case like | |
794 | \&'Apache::DBI::connect' (which is automatically the default when running | |
795 | within Apache). | |
796 | .Sp | |
797 | Where possible, each session (\f(CW$dbh\fR) is independent from the transactions | |
798 | in other sessions. This is useful when you need to hold cursors open | |
799 | across transactions\*(--for example, if you use one session for your long lifespan | |
800 | cursors (typically read\-only) and another for your short update | |
801 | transactions. | |
802 | .Sp | |
803 | For compatibility with old \s-1DBI\s0 scripts, the driver can be specified by | |
804 | passing its name as the fourth argument to \f(CW\*(C`connect\*(C'\fR (instead of \f(CW\*(C`\e%attr\*(C'\fR): | |
805 | .Sp | |
806 | .Vb 1 | |
807 | \& $dbh = DBI->connect($data_source, $user, $pass, $driver); | |
808 | .Ve | |
809 | .Sp | |
810 | In this \*(L"old\-style\*(R" form of \f(CW\*(C`connect\*(C'\fR, the \f(CW$data_source\fR should not start | |
811 | with "\f(CW\*(C`dbi:driver_name:\*(C'\fR". (If it does, the embedded driver_name | |
812 | will be ignored). Also note that in this older form of \f(CW\*(C`connect\*(C'\fR, | |
813 | the \f(CW\*(C`$dbh\-\*(C'\fR>\f(CW\*(C`{AutoCommit}\*(C'\fR attribute is \fIundefined\fR, the | |
814 | \&\f(CW\*(C`$dbh\-\*(C'\fR>\f(CW\*(C`{PrintError}\*(C'\fR attribute is off, and the old \f(CW\*(C`DBI_DBNAME\*(C'\fR | |
815 | environment variable is | |
816 | checked if \f(CW\*(C`DBI_DSN\*(C'\fR is not defined. Beware that this \*(L"old\-style\*(R" | |
817 | \&\f(CW\*(C`connect\*(C'\fR will be withdrawn in a future version of \s-1DBI\s0. | |
818 | .ie n .IP """connect_cached""\fR \fI\s-1NEW\s0" 4 | |
819 | .el .IP "\f(CWconnect_cached\fR \fI\s-1NEW\s0\fR" 4 | |
820 | .IX Item "connect_cached NEW" | |
821 | .Vb 4 | |
822 | \& $dbh = DBI->connect_cached($data_source, $username, $password) | |
823 | \& or die $DBI::errstr; | |
824 | \& $dbh = DBI->connect_cached($data_source, $username, $password, \e%attr) | |
825 | \& or die $DBI::errstr; | |
826 | .Ve | |
827 | .Sp | |
828 | \&\f(CW\*(C`connect_cached\*(C'\fR is like \*(L"connect\*(R", except that the database handle | |
829 | returned is also | |
830 | stored in a hash associated with the given parameters. If another call | |
831 | is made to \f(CW\*(C`connect_cached\*(C'\fR with the same parameter values, then the | |
832 | corresponding cached \f(CW$dbh\fR will be returned if it is still valid. | |
833 | The cached database handle is replaced with a new connection if it | |
834 | has been disconnected or if the \f(CW\*(C`ping\*(C'\fR method fails. | |
835 | .Sp | |
836 | Note that the behavior of this method differs in several respects from the | |
837 | behavior of presistent connections implemented by Apache::DBI. | |
838 | .Sp | |
839 | Caching can be useful in some applications, but it can also cause | |
840 | problems and should be used with care. The exact behavior of this | |
841 | method is liable to change, so if you intend to use it in any production | |
842 | applications you should discuss your needs on the \fIdbi-users\fR mailing list. | |
843 | .Sp | |
844 | The cache can be accessed (and cleared) via the \*(L"CachedKids\*(R" attribute. | |
845 | .ie n .IP """available_drivers""" 4 | |
846 | .el .IP "\f(CWavailable_drivers\fR" 4 | |
847 | .IX Item "available_drivers" | |
848 | .Vb 2 | |
849 | \& @ary = DBI->available_drivers; | |
850 | \& @ary = DBI->available_drivers($quiet); | |
851 | .Ve | |
852 | .Sp | |
853 | Returns a list of all available drivers by searching for \f(CW\*(C`DBD::*\*(C'\fR modules | |
854 | through the directories in \f(CW@INC\fR. By default, a warning is given if | |
855 | some drivers are hidden by others of the same name in earlier | |
856 | directories. Passing a true value for \f(CW$quiet\fR will inhibit the warning. | |
857 | .ie n .IP """data_sources""" 4 | |
858 | .el .IP "\f(CWdata_sources\fR" 4 | |
859 | .IX Item "data_sources" | |
860 | .Vb 2 | |
861 | \& @ary = DBI->data_sources($driver); | |
862 | \& @ary = DBI->data_sources($driver, \e%attr); | |
863 | .Ve | |
864 | .Sp | |
865 | Returns a list of all data sources (databases) available via the named | |
866 | driver. If \f(CW$driver\fR is empty or \f(CW\*(C`undef\*(C'\fR, then the value of the | |
867 | \&\f(CW\*(C`DBI_DRIVER\*(C'\fR environment variable is used. | |
868 | .Sp | |
869 | The driver will be loaded if it hasn't been already. Note that if the | |
870 | driver loading fails then it \fIdies\fR with an error message that | |
871 | includes the string "\f(CW\*(C`install_driver\*(C'\fR" and the underlying problem. | |
872 | .Sp | |
873 | Data sources are returned in a form suitable for passing to the | |
874 | \&\*(L"connect\*(R" method (that is, they will include the "\f(CW\*(C`dbi:$driver:\*(C'\fR" prefix). | |
875 | .Sp | |
876 | Note that many drivers have no way of knowing what data sources might | |
877 | be available for it. These drivers return an empty or incomplete list | |
878 | or may require driver-specific attributes, such as a connected database | |
879 | handle, to be supplied. | |
880 | .ie n .IP """trace""" 4 | |
881 | .el .IP "\f(CWtrace\fR" 4 | |
882 | .IX Item "trace" | |
883 | .Vb 2 | |
884 | \& DBI->trace($trace_level) | |
885 | \& DBI->trace($trace_level, $trace_filename) | |
886 | .Ve | |
887 | .Sp | |
888 | \&\s-1DBI\s0 trace information can be enabled for all handles using the \f(CW\*(C`trace\*(C'\fR | |
889 | \&\s-1DBI\s0 class method. To enable trace information for a specific handle, use | |
890 | the similar \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`trace\*(C'\fR method described elsewhere. | |
891 | .Sp | |
892 | Trace levels are as follows: | |
893 | .Sp | |
894 | .Vb 8 | |
895 | \& 0 - Trace disabled. | |
896 | \& 1 - Trace DBI method calls returning with results or errors. | |
897 | \& 2 - Trace method entry with parameters and returning with results. | |
898 | \& 3 - As above, adding some high-level information from the driver | |
899 | \& and some internal information from the DBI. | |
900 | \& 4 - As above, adding more detailed information from the driver. | |
901 | \& Also includes DBI mutex information when using threaded Perl. | |
902 | \& 5 and above - As above but with more and more obscure information. | |
903 | .Ve | |
904 | .Sp | |
905 | Trace level 1 is best for a simple overview of what's happening. | |
906 | Trace level 2 is a good choice for general purpose tracing. Levels 3 | |
907 | and above (up to 9) are best reserved for investigating a | |
908 | specific problem, when you need to see \*(L"inside\*(R" the driver and \s-1DBI\s0. | |
909 | .Sp | |
910 | The trace output is detailed and typically very useful. Much of the | |
911 | trace output is formatted using the \*(L"neat\*(R" function, so strings | |
912 | in the trace output may be edited and truncated. | |
913 | .Sp | |
914 | Initially trace output is written to \f(CW\*(C`STDERR\*(C'\fR. If \f(CW$trace_filename\fR is | |
915 | specified and can be opened in append mode then all trace | |
916 | output (including that from other handles) is redirected to that file. | |
917 | A warning is generated is the file can't be opened. | |
918 | Further calls to \f(CW\*(C`trace\*(C'\fR without a \f(CW$trace_filename\fR do not alter where | |
919 | the trace output is sent. If \f(CW$trace_filename\fR is undefined, then | |
920 | trace output is sent to \f(CW\*(C`STDERR\*(C'\fR and the previous trace file is closed. | |
921 | The \f(CW\*(C`trace\*(C'\fR method returns the \fIprevious\fR tracelevel. | |
922 | .Sp | |
923 | See also the \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`trace\*(C'\fR and \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`trace_msg\*(C'\fR methods and the | |
924 | \&\*(L"\s-1DEBUGGING\s0\*(R" section | |
925 | for information about the \f(CW\*(C`DBI_TRACE\*(C'\fR environment variable. | |
926 | .Sh "\s-1DBI\s0 Utility Functions" | |
927 | .IX Subsection "DBI Utility Functions" | |
928 | In addition to the methods listed in the previous section, | |
929 | the \s-1DBI\s0 package also provides these utility functions: | |
930 | .ie n .IP """neat""" 4 | |
931 | .el .IP "\f(CWneat\fR" 4 | |
932 | .IX Item "neat" | |
933 | .Vb 1 | |
934 | \& $str = DBI::neat($value, $maxlen); | |
935 | .Ve | |
936 | .Sp | |
937 | Return a string containing a neat (and tidy) representation of the | |
938 | supplied value. | |
939 | .Sp | |
940 | Strings will be quoted, although internal quotes will \fInot\fR be escaped. | |
941 | Values known to be numeric will be unquoted. Undefined (\s-1NULL\s0) values | |
942 | will be shown as \f(CW\*(C`undef\*(C'\fR (without quotes). Unprintable characters will | |
943 | be replaced by dot (.). | |
944 | .Sp | |
945 | For result strings longer than \f(CW$maxlen\fR the result string will be | |
946 | truncated to \f(CW\*(C`$maxlen\-4\*(C'\fR and "\f(CW\*(C`...'\*(C'\fR" will be appended. If \f(CW$maxlen\fR is 0 | |
947 | or \f(CW\*(C`undef\*(C'\fR, it defaults to \f(CW$DBI::neat_maxlen\fR which, in turn, defaults to 400. | |
948 | .Sp | |
949 | This function is designed to format values for human consumption. | |
950 | It is used internally by the \s-1DBI\s0 for \*(L"trace\*(R" output. It should | |
951 | typically \fInot\fR be used for formatting values for database use. | |
952 | (See also \*(L"quote\*(R".) | |
953 | .ie n .IP """neat_list""" 4 | |
954 | .el .IP "\f(CWneat_list\fR" 4 | |
955 | .IX Item "neat_list" | |
956 | .Vb 1 | |
957 | \& $str = DBI::neat_list(\e@listref, $maxlen, $field_sep); | |
958 | .Ve | |
959 | .Sp | |
960 | Calls \f(CW\*(C`DBI::neat\*(C'\fR on each element of the list and returns a string | |
961 | containing the results joined with \f(CW$field_sep\fR. \f(CW$field_sep\fR defaults | |
962 | to \f(CW", "\fR. | |
963 | .ie n .IP """looks_like_number""" 4 | |
964 | .el .IP "\f(CWlooks_like_number\fR" 4 | |
965 | .IX Item "looks_like_number" | |
966 | .Vb 1 | |
967 | \& @bool = DBI::looks_like_number(@array); | |
968 | .Ve | |
969 | .Sp | |
970 | Returns true for each element that looks like a number. | |
971 | Returns false for each element that does not look like a number. | |
972 | Returns \f(CW\*(C`undef\*(C'\fR for each element that is undefined or empty. | |
973 | .Sh "\s-1DBI\s0 Dynamic Attributes" | |
974 | .IX Subsection "DBI Dynamic Attributes" | |
975 | Dynamic attributes are always associated with the \fIlast handle used\fR | |
976 | (that handle is represented by \f(CW$h\fR in the descriptions below). | |
977 | .PP | |
978 | Where an attribute is equivalent to a method call, then refer to | |
979 | the method call for all related documentation. | |
980 | .PP | |
981 | Warning: these attributes are provided as a convenience but they | |
982 | do have limitations. Specifically, they have a short lifespan: | |
983 | because they are associated with | |
984 | the last handle used, they should only be used \fIimmediately\fR after | |
985 | calling the method that \*(L"sets\*(R" them. | |
986 | If in any doubt, use the corresponding method call. | |
987 | .ie n .IP "$DBI::err" 4 | |
988 | .el .IP "\f(CW$DBI::err\fR" 4 | |
989 | .IX Item "$DBI::err" | |
990 | Equivalent to \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`err\*(C'\fR. | |
991 | .ie n .IP "$DBI::errstr" 4 | |
992 | .el .IP "\f(CW$DBI::errstr\fR" 4 | |
993 | .IX Item "$DBI::errstr" | |
994 | Equivalent to \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`errstr\*(C'\fR. | |
995 | .ie n .IP "$DBI::state" 4 | |
996 | .el .IP "\f(CW$DBI::state\fR" 4 | |
997 | .IX Item "$DBI::state" | |
998 | Equivalent to \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`state\*(C'\fR. | |
999 | .ie n .IP "$DBI::rows" 4 | |
1000 | .el .IP "\f(CW$DBI::rows\fR" 4 | |
1001 | .IX Item "$DBI::rows" | |
1002 | Equivalent to \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`rows\*(C'\fR. Please refer to the documentation | |
1003 | for the \*(L"rows\*(R" method. | |
1004 | .ie n .IP "$DBI::lasth" 4 | |
1005 | .el .IP "\f(CW$DBI::lasth\fR" 4 | |
1006 | .IX Item "$DBI::lasth" | |
1007 | Returns the \s-1DBI\s0 object handle used for the most recent \s-1DBI\s0 method call. | |
1008 | If the last \s-1DBI\s0 method call was a \s-1DESTROY\s0 then \f(CW$DBI::lasth\fR will return | |
1009 | the handle of the parent of the destroyed handle, if there is one. | |
1010 | .SH "METHODS COMMON TO ALL HANDLES" | |
1011 | .IX Header "METHODS COMMON TO ALL HANDLES" | |
1012 | The following methods can be used by all types of \s-1DBI\s0 handles. | |
1013 | .ie n .IP """err""" 4 | |
1014 | .el .IP "\f(CWerr\fR" 4 | |
1015 | .IX Item "err" | |
1016 | .Vb 1 | |
1017 | \& $rv = $h->err; | |
1018 | .Ve | |
1019 | .Sp | |
1020 | Returns the \fInative\fR database engine error code from the last driver | |
1021 | method called. The code is typically an integer but you should not | |
1022 | assume that. | |
1023 | .Sp | |
1024 | The \s-1DBI\s0 resets \f(CW$h\fR\->err to undef before most \s-1DBI\s0 method calls, so the | |
1025 | value only has a short lifespan. Also, most drivers share the same | |
1026 | error variables across all their handles, so calling a method on | |
1027 | one handle will typically reset the error on all the other handles | |
1028 | that are children of that driver. | |
1029 | .Sp | |
1030 | If you need to test for individual errors \fIand\fR have your program be | |
1031 | portable to different database engines, then you'll need to determine | |
1032 | what the corresponding error codes are for all those engines and test for | |
1033 | all of them. | |
1034 | .ie n .IP """errstr""" 4 | |
1035 | .el .IP "\f(CWerrstr\fR" 4 | |
1036 | .IX Item "errstr" | |
1037 | .Vb 1 | |
1038 | \& $str = $h->errstr; | |
1039 | .Ve | |
1040 | .Sp | |
1041 | Returns the native database engine error message from the last driver | |
1042 | method called. This has the same lifespan issues as the \*(L"err\*(R" method | |
1043 | described above. | |
1044 | .ie n .IP """state""" 4 | |
1045 | .el .IP "\f(CWstate\fR" 4 | |
1046 | .IX Item "state" | |
1047 | .Vb 1 | |
1048 | \& $str = $h->state; | |
1049 | .Ve | |
1050 | .Sp | |
1051 | Returns an error code in the standard \s-1SQLSTATE\s0 five character format. | |
1052 | Note that the specific success code \f(CW00000\fR is translated to '' | |
1053 | (false). If the driver does not support \s-1SQLSTATE\s0 (and most don't), | |
1054 | then state will return \f(CW\*(C`S1000\*(C'\fR (General Error) for all errors. | |
1055 | .Sp | |
1056 | The driver is free to return any value via \f(CW\*(C`state\*(C'\fR, e.g., warning | |
1057 | codes, even if it has not declared an error by returning a true value | |
1058 | via the \*(L"err\*(R" method described above. | |
1059 | .ie n .IP """trace""" 4 | |
1060 | .el .IP "\f(CWtrace\fR" 4 | |
1061 | .IX Item "trace" | |
1062 | .Vb 2 | |
1063 | \& $h->trace($trace_level); | |
1064 | \& $h->trace($trace_level, $trace_filename); | |
1065 | .Ve | |
1066 | .Sp | |
1067 | \&\s-1DBI\s0 trace information can be enabled for a specific handle (and any | |
1068 | future children of that handle) by setting the trace level using the | |
1069 | \&\f(CW\*(C`trace\*(C'\fR method. | |
1070 | .Sp | |
1071 | Trace level 1 is best for a simple overview of what's happening. | |
1072 | Trace level 2 is a good choice for general purpose tracing. Levels 3 | |
1073 | and above (up to 9) are best reserved for investigating a | |
1074 | specific problem, when you need to see \*(L"inside\*(R" the driver and \s-1DBI\s0. | |
1075 | Set \f(CW$trace_level\fR to 0 to disable the trace. | |
1076 | .Sp | |
1077 | The trace output is detailed and typically very useful. Much of the | |
1078 | trace output is formatted using the \*(L"neat\*(R" function, so strings | |
1079 | in the trace output may be edited and truncated. | |
1080 | .Sp | |
1081 | Initially, trace output is written to \f(CW\*(C`STDERR\*(C'\fR. If \f(CW$trace_filename\fR is | |
1082 | specified, then the file is opened in append mode and \fIall\fR trace | |
1083 | output (including that from other handles) is redirected to that file. | |
1084 | Further calls to trace without a \f(CW$trace_filename\fR do not alter where | |
1085 | the trace output is sent. If \f(CW$trace_filename\fR is undefined, then | |
1086 | trace output is sent to \f(CW\*(C`STDERR\*(C'\fR and the previous trace file is closed. | |
1087 | .Sp | |
1088 | See also the \f(CW\*(C`DBI\-\*(C'\fR>\f(CW\*(C`trace\*(C'\fR method and \*(L"\s-1DEBUGGING\s0\*(R" for information | |
1089 | about the \f(CW\*(C`DBI_TRACE\*(C'\fR environment variable. | |
1090 | .ie n .IP """trace_msg""" 4 | |
1091 | .el .IP "\f(CWtrace_msg\fR" 4 | |
1092 | .IX Item "trace_msg" | |
1093 | .Vb 2 | |
1094 | \& $h->trace_msg($message_text); | |
1095 | \& $h->trace_msg($message_text, $min_level); | |
1096 | .Ve | |
1097 | .Sp | |
1098 | Writes \f(CW$message_text\fR to the trace file if trace is enabled for \f(CW$h\fR or | |
1099 | for the \s-1DBI\s0 as a whole. Can also be called as \f(CW\*(C`DBI\-\*(C'\fR>\f(CW\*(C`trace_msg($msg)\*(C'\fR. | |
1100 | See \*(L"trace\*(R". | |
1101 | .Sp | |
1102 | If \f(CW$min_level\fR is defined, then the message is output only if the trace | |
1103 | level is equal to or greater than that level. \f(CW$min_level\fR defaults to 1. | |
1104 | .ie n .IP """func""" 4 | |
1105 | .el .IP "\f(CWfunc\fR" 4 | |
1106 | .IX Item "func" | |
1107 | .Vb 1 | |
1108 | \& $h->func(@func_arguments, $func_name); | |
1109 | .Ve | |
1110 | .Sp | |
1111 | The \f(CW\*(C`func\*(C'\fR method can be used to call private non-standard and | |
1112 | non-portable methods implemented by the driver. Note that the function | |
1113 | name is given as the last argument. | |
1114 | .Sp | |
1115 | This method is not directly related to calling stored procedures. | |
1116 | Calling stored procedures is currently not defined by the \s-1DBI\s0. | |
1117 | Some drivers, such as DBD::Oracle, support it in non-portable ways. | |
1118 | See driver documentation for more details. | |
1119 | .SH "ATTRIBUTES COMMON TO ALL HANDLES" | |
1120 | .IX Header "ATTRIBUTES COMMON TO ALL HANDLES" | |
1121 | These attributes are common to all types of \s-1DBI\s0 handles. | |
1122 | .PP | |
1123 | Some attributes are inherited by child handles. That is, the value | |
1124 | of an inherited attribute in a newly created statement handle is the | |
1125 | same as the value in the parent database handle. Changes to attributes | |
1126 | in the new statement handle do not affect the parent database handle | |
1127 | and changes to the database handle do not affect existing statement | |
1128 | handles, only future ones. | |
1129 | .PP | |
1130 | Attempting to set or get the value of an unknown attribute is fatal, | |
1131 | except for private driver specific attributes (which all have names | |
1132 | starting with a lowercase letter). | |
1133 | .PP | |
1134 | Example: | |
1135 | .PP | |
1136 | .Vb 2 | |
1137 | \& $h->{AttributeName} = ...; # set/write | |
1138 | \& ... = $h->{AttributeName}; # get/read | |
1139 | .Ve | |
1140 | .ie n .IP """Warn"" (boolean, inherited)" 4 | |
1141 | .el .IP "\f(CWWarn\fR (boolean, inherited)" 4 | |
1142 | .IX Item "Warn (boolean, inherited)" | |
1143 | Enables useful warnings for certain bad practices. Enabled by default. Some | |
1144 | emulation layers, especially those for Perl 4 interfaces, disable warnings. | |
1145 | Since warnings are generated using the Perl \f(CW\*(C`warn\*(C'\fR function, they can be | |
1146 | intercepted using the Perl \f(CW$SIG{_\|_WARN_\|_}\fR hook. | |
1147 | .ie n .IP """Active"" (boolean, read\-only)" 4 | |
1148 | .el .IP "\f(CWActive\fR (boolean, read\-only)" 4 | |
1149 | .IX Item "Active (boolean, read-only)" | |
1150 | True if the handle object is \*(L"active\*(R". This is rarely used in | |
1151 | applications. The exact meaning of active is somewhat vague at the | |
1152 | moment. For a database handle it typically means that the handle is | |
1153 | connected to a database (\f(CW\*(C`$dbh\-\*(C'\fR>\f(CW\*(C`disconnect\*(C'\fR sets \f(CW\*(C`Active\*(C'\fR off). For | |
1154 | a statement handle it typically means that the handle is a \f(CW\*(C`SELECT\*(C'\fR | |
1155 | that may have more data to fetch. (Fetching all the data or calling \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`finish\*(C'\fR | |
1156 | sets \f(CW\*(C`Active\*(C'\fR off.) | |
1157 | .ie n .IP """Kids"" (integer, read\-only)" 4 | |
1158 | .el .IP "\f(CWKids\fR (integer, read\-only)" 4 | |
1159 | .IX Item "Kids (integer, read-only)" | |
1160 | For a driver handle, \f(CW\*(C`Kids\*(C'\fR is the number of currently existing database | |
1161 | handles that were created from that driver handle. For a database | |
1162 | handle, \f(CW\*(C`Kids\*(C'\fR is the number of currently existing statement handles that | |
1163 | were created from that database handle. | |
1164 | .ie n .IP """ActiveKids"" (integer, read\-only)" 4 | |
1165 | .el .IP "\f(CWActiveKids\fR (integer, read\-only)" 4 | |
1166 | .IX Item "ActiveKids (integer, read-only)" | |
1167 | Like \f(CW\*(C`Kids\*(C'\fR, but only counting those that are \f(CW\*(C`Active\*(C'\fR (as above). | |
1168 | .ie n .IP """CachedKids"" (hash ref)" 4 | |
1169 | .el .IP "\f(CWCachedKids\fR (hash ref)" 4 | |
1170 | .IX Item "CachedKids (hash ref)" | |
1171 | For a database handle, returns a reference to the cache (hash) of | |
1172 | statement handles created by the \*(L"prepare_cached\*(R" method. For a | |
1173 | driver handle, returns a reference to the cache (hash) of | |
1174 | database handles created by the \*(L"connect_cached\*(R" method. | |
1175 | .ie n .IP """CompatMode"" (boolean, inherited)" 4 | |
1176 | .el .IP "\f(CWCompatMode\fR (boolean, inherited)" 4 | |
1177 | .IX Item "CompatMode (boolean, inherited)" | |
1178 | Used by emulation layers (such as Oraperl) to enable compatible behavior | |
1179 | in the underlying driver (e.g., DBD::Oracle) for this handle. Not normally | |
1180 | set by application code. | |
1181 | .ie n .IP """InactiveDestroy"" (boolean)" 4 | |
1182 | .el .IP "\f(CWInactiveDestroy\fR (boolean)" 4 | |
1183 | .IX Item "InactiveDestroy (boolean)" | |
1184 | This attribute can be used to disable the \fIdatabase engine\fR related | |
1185 | effect of DESTROYing a handle (which would normally close a prepared | |
1186 | statement or disconnect from the database etc). | |
1187 | .Sp | |
1188 | For a database handle, this attribute does not disable an \fIexplicit\fR | |
1189 | call to the disconnect method, only the implicit call from \s-1DESTROY\s0. | |
1190 | .Sp | |
1191 | This attribute is specifically designed for use in Unix applications | |
1192 | that \*(L"fork\*(R" child processes. Either the parent or the child process, | |
1193 | but not both, should set \f(CW\*(C`InactiveDestroy\*(C'\fR on all their shared handles. | |
1194 | Note that some databases, including Oracle, don't support passing a | |
1195 | database connection across a fork. | |
1196 | .ie n .IP """PrintError"" (boolean, inherited)" 4 | |
1197 | .el .IP "\f(CWPrintError\fR (boolean, inherited)" 4 | |
1198 | .IX Item "PrintError (boolean, inherited)" | |
1199 | This attribute can be used to force errors to generate warnings (using | |
1200 | \&\f(CW\*(C`warn\*(C'\fR) in addition to returning error codes in the normal way. When set | |
1201 | \&\*(L"on\*(R", any method which results in an error occuring will cause the \s-1DBI\s0 to | |
1202 | effectively do a \f(CW\*(C`warn("$class $method failed: $DBI::errstr")\*(C'\fR where \f(CW$class\fR | |
1203 | is the driver class and \f(CW$method\fR is the name of the method which failed. E.g., | |
1204 | .Sp | |
1205 | .Vb 1 | |
1206 | \& DBD::Oracle::db prepare failed: ... error text here ... | |
1207 | .Ve | |
1208 | .Sp | |
1209 | By default, \f(CW\*(C`DBI\-\*(C'\fR>\f(CW\*(C`connect\*(C'\fR sets \f(CW\*(C`PrintError\*(C'\fR \*(L"on\*(R". | |
1210 | .Sp | |
1211 | If desired, the warnings can be caught and processed using a \f(CW$SIG{_\|_WARN_\|_}\fR | |
1212 | handler or modules like CGI::Carp and CGI::ErrorWrap. | |
1213 | .ie n .IP """RaiseError"" (boolean, inherited)" 4 | |
1214 | .el .IP "\f(CWRaiseError\fR (boolean, inherited)" 4 | |
1215 | .IX Item "RaiseError (boolean, inherited)" | |
1216 | This attribute can be used to force errors to raise exceptions rather | |
1217 | than simply return error codes in the normal way. It is \*(L"off\*(R" by default. | |
1218 | When set \*(L"on\*(R", any method which results in an error will cause | |
1219 | the \s-1DBI\s0 to effectively do a \f(CW\*(C`die("$class $method failed: $DBI::errstr")\*(C'\fR, | |
1220 | where \f(CW$class\fR is the driver class and \f(CW$method\fR is the name of the method | |
1221 | that failed. E.g., | |
1222 | .Sp | |
1223 | .Vb 1 | |
1224 | \& DBD::Oracle::db prepare failed: ... error text here ... | |
1225 | .Ve | |
1226 | .Sp | |
1227 | If you turn \f(CW\*(C`RaiseError\*(C'\fR on then you'd normally turn \f(CW\*(C`PrintError\*(C'\fR off. | |
1228 | If \f(CW\*(C`PrintError\*(C'\fR is also on, then the \f(CW\*(C`PrintError\*(C'\fR is done first (naturally). | |
1229 | .Sp | |
1230 | Typically \f(CW\*(C`RaiseError\*(C'\fR is used in conjunction with \f(CW\*(C`eval { ... }\*(C'\fR | |
1231 | to catch the exception that's been thrown and followed by an | |
1232 | \&\f(CW\*(C`if ($@) { ... }\*(C'\fR block to handle the caught exception. In that eval | |
1233 | block the \f(CW$DBI::lasth\fR variable can be useful for diagnosis and reporting. | |
1234 | For example, \f(CW$DBI::lasth\fR\->{Type} and \f(CW$DBI::lasth\fR\->{Statement}. | |
1235 | .Sp | |
1236 | If you want to temporarily turn \f(CW\*(C`RaiseError\*(C'\fR off (inside a library function | |
1237 | that is likely to fail, for example), the recommended way is like this: | |
1238 | .Sp | |
1239 | .Vb 4 | |
1240 | \& { | |
1241 | \& local $h->{RaiseError}; # localize and turn off for this block | |
1242 | \& ... | |
1243 | \& } | |
1244 | .Ve | |
1245 | .Sp | |
1246 | The original value will automatically and reliably be restored by Perl, | |
1247 | regardless of how the block is exited. | |
1248 | The same logic applies to other attributes, including \f(CW\*(C`PrintError\*(C'\fR. | |
1249 | .Sp | |
1250 | Sadly, this doesn't work for Perl versions up to and including 5.004_04. | |
1251 | Even more sadly, for Perl 5.5 and 5.6.0 it does work but leaks memory! | |
1252 | For backwards compatibility, you could just use \f(CW\*(C`eval { ... }\*(C'\fR instead. | |
1253 | .ie n .IP """ShowErrorStatement""\fR (boolean, inherited) \fI\s-1NEW\s0" 4 | |
1254 | .el .IP "\f(CWShowErrorStatement\fR (boolean, inherited) \fI\s-1NEW\s0\fR" 4 | |
1255 | .IX Item "ShowErrorStatement (boolean, inherited) NEW" | |
1256 | This attribute can be used to cause the relevant Statement text to be | |
1257 | appended to the error messages generated by the \f(CW\*(C`RaiseError\*(C'\fR and | |
1258 | \&\f(CW\*(C`PrintError\*(C'\fR attributes. Only applies to errors on statement handles | |
1259 | plus the \fIprepare()\fR and \fIdo()\fR database handle methods. | |
1260 | (The exact format of the appended text is subject to change.) | |
1261 | .ie n .IP """ChopBlanks"" (boolean, inherited)" 4 | |
1262 | .el .IP "\f(CWChopBlanks\fR (boolean, inherited)" 4 | |
1263 | .IX Item "ChopBlanks (boolean, inherited)" | |
1264 | This attribute can be used to control the trimming of trailing space | |
1265 | characters from fixed width character (\s-1CHAR\s0) fields. No other field | |
1266 | types are affected, even where field values have trailing spaces. | |
1267 | .Sp | |
1268 | The default is false (although it is possible that the default may change). | |
1269 | Applications that need specific behavior should set the attribute as | |
1270 | needed. Emulation interfaces should set the attribute to match the | |
1271 | behavior of the interface they are emulating. | |
1272 | .Sp | |
1273 | Drivers are not required to support this attribute, but any driver which | |
1274 | does not support it must arrange to return \f(CW\*(C`undef\*(C'\fR as the attribute value. | |
1275 | .ie n .IP """LongReadLen"" (unsigned integer, inherited)" 4 | |
1276 | .el .IP "\f(CWLongReadLen\fR (unsigned integer, inherited)" 4 | |
1277 | .IX Item "LongReadLen (unsigned integer, inherited)" | |
1278 | This attribute may be used to control the maximum length of long fields | |
1279 | (\*(L"blob\*(R", \*(L"memo\*(R", etc.) which the driver will read from the | |
1280 | database automatically when it fetches each row of data. The | |
1281 | \&\f(CW\*(C`LongReadLen\*(C'\fR attribute only relates to fetching and reading long values; it | |
1282 | is not involved in inserting or updating them. | |
1283 | .Sp | |
1284 | A value of 0 means not to automatically fetch any long data. (\f(CW\*(C`fetch\*(C'\fR | |
1285 | should return \f(CW\*(C`undef\*(C'\fR for long fields when \f(CW\*(C`LongReadLen\*(C'\fR is 0.) | |
1286 | .Sp | |
1287 | The default is typically 0 (zero) bytes but may vary between drivers. | |
1288 | Applications fetching long fields should set this value to slightly | |
1289 | larger than the longest long field value to be fetched. | |
1290 | .Sp | |
1291 | Some databases return some long types encoded as pairs of hex digits. | |
1292 | For these types, \f(CW\*(C`LongReadLen\*(C'\fR relates to the underlying data length and not the | |
1293 | doubled-up length of the encoded string. | |
1294 | .Sp | |
1295 | Changing the value of \f(CW\*(C`LongReadLen\*(C'\fR for a statement handle after it | |
1296 | has been \f(CW\*(C`prepare\*(C'\fR'd will typically have no effect, so it's common to | |
1297 | set \f(CW\*(C`LongReadLen\*(C'\fR on the \f(CW$dbh\fR before calling \f(CW\*(C`prepare\*(C'\fR. | |
1298 | .Sp | |
1299 | Note that the value used here has a direct effect on the memory used | |
1300 | by the application, so don't be too generous. | |
1301 | .Sp | |
1302 | See \*(L"LongTruncOk\*(R" for more information on truncation behavior. | |
1303 | .ie n .IP """LongTruncOk"" (boolean, inherited)" 4 | |
1304 | .el .IP "\f(CWLongTruncOk\fR (boolean, inherited)" 4 | |
1305 | .IX Item "LongTruncOk (boolean, inherited)" | |
1306 | This attribute may be used to control the effect of fetching a long | |
1307 | field value which has been truncated (typically because it's longer | |
1308 | than the value of the \f(CW\*(C`LongReadLen\*(C'\fR attribute). | |
1309 | .Sp | |
1310 | By default, \f(CW\*(C`LongTruncOk\*(C'\fR is false and so fetching a long value that | |
1311 | needs to be truncated will cause the fetch to fail. | |
1312 | (Applications should always be sure to | |
1313 | check for errors after a fetch loop in case an error, such as a divide | |
1314 | by zero or long field truncation, caused the fetch to terminate | |
1315 | prematurely.) | |
1316 | .Sp | |
1317 | If a fetch fails due to a long field truncation when \f(CW\*(C`LongTruncOk\*(C'\fR is | |
1318 | false, many drivers will allow you to continue fetching further rows. | |
1319 | .Sp | |
1320 | See also \*(L"LongReadLen\*(R". | |
1321 | .ie n .IP """Taint"" (boolean, inherited)" 4 | |
1322 | .el .IP "\f(CWTaint\fR (boolean, inherited)" 4 | |
1323 | .IX Item "Taint (boolean, inherited)" | |
1324 | If this attribute is set to a true value \fIand\fR Perl is running in | |
1325 | taint mode (e.g., started with the \f(CW\*(C`\-T\*(C'\fR option), then all data | |
1326 | fetched from the database is tainted, and the arguments to most \s-1DBI\s0 | |
1327 | method calls are checked for being tainted. \fIThis may change.\fR | |
1328 | .Sp | |
1329 | The attribute defaults to off, even if Perl is in taint mode. | |
1330 | See perlsec for more about taint mode. If Perl is not | |
1331 | running in taint mode, this attribute has no effect. | |
1332 | .Sp | |
1333 | When fetching data that you trust you can turn off the Taint attribute, | |
1334 | for that statement handle, for the duration of the fetch loop. | |
1335 | .Sp | |
1336 | Currently only fetched data is tainted. It is possible that the results | |
1337 | of other \s-1DBI\s0 method calls, and the value of fetched attributes, may | |
1338 | also be tainted in future versions. That change may well break your | |
1339 | applications unless you take great care now. If you use \s-1DBI\s0 Taint mode, | |
1340 | please report your experience and any suggestions for changes. | |
1341 | .ie n .IP """private_*""" 4 | |
1342 | .el .IP "\f(CWprivate_*\fR" 4 | |
1343 | .IX Item "private_*" | |
1344 | The \s-1DBI\s0 provides a way to store extra information in a \s-1DBI\s0 handle as | |
1345 | \&\*(L"private\*(R" attributes. The \s-1DBI\s0 will allow you to store and retreive any | |
1346 | attribute which has a name starting with "\f(CW\*(C`private_\*(C'\fR". It is strongly | |
1347 | recommended that you use just \fIone\fR private attribute (e.g., use a | |
1348 | hash ref) and give it a long and unambiguous name that includes the | |
1349 | module or application name that the attribute relates to (e.g., | |
1350 | "\f(CW\*(C`private_YourFullModuleName_thingy\*(C'\fR"). | |
1351 | .SH "DBI DATABASE HANDLE OBJECTS" | |
1352 | .IX Header "DBI DATABASE HANDLE OBJECTS" | |
1353 | This section covers the methods and attributes associated with | |
1354 | database handles. | |
1355 | .Sh "Database Handle Methods" | |
1356 | .IX Subsection "Database Handle Methods" | |
1357 | The following methods are specified for \s-1DBI\s0 database handles: | |
1358 | .ie n .IP """do""" 4 | |
1359 | .el .IP "\f(CWdo\fR" 4 | |
1360 | .IX Item "do" | |
1361 | .Vb 3 | |
1362 | \& $rc = $dbh->do($statement) or die $dbh->errstr; | |
1363 | \& $rc = $dbh->do($statement, \e%attr) or die $dbh->errstr; | |
1364 | \& $rv = $dbh->do($statement, \e%attr, @bind_values) or ... | |
1365 | .Ve | |
1366 | .Sp | |
1367 | Prepare and execute a single statement. Returns the number of rows | |
1368 | affected or \f(CW\*(C`undef\*(C'\fR on error. A return value of \f(CW\*(C`\-1\*(C'\fR means the | |
1369 | number of rows is not known or is not available. | |
1370 | .Sp | |
1371 | This method is typically most useful for \fInon\fR\-\f(CW\*(C`SELECT\*(C'\fR statements that | |
1372 | either cannot be prepared in advance (due to a limitation of the | |
1373 | driver) or do not need to be executed repeatedly. It should not | |
1374 | be used for \f(CW\*(C`SELECT\*(C'\fR statements because it does not return a statement | |
1375 | handle (so you can't fetch any data). | |
1376 | .Sp | |
1377 | The default \f(CW\*(C`do\*(C'\fR method is logically similar to: | |
1378 | .Sp | |
1379 | .Vb 7 | |
1380 | \& sub do { | |
1381 | \& my($dbh, $statement, $attr, @bind_values) = @_; | |
1382 | \& my $sth = $dbh->prepare($statement, $attr) or return undef; | |
1383 | \& $sth->execute(@bind_values) or return undef; | |
1384 | \& my $rows = $sth->rows; | |
1385 | \& ($rows == 0) ? "0E0" : $rows; # always return true if no error | |
1386 | \& } | |
1387 | .Ve | |
1388 | .Sp | |
1389 | For example: | |
1390 | .Sp | |
1391 | .Vb 4 | |
1392 | \& my $rows_deleted = $dbh->do(q{ | |
1393 | \& DELETE FROM table | |
1394 | \& WHERE status = ? | |
1395 | \& }, undef, 'DONE') or die $dbh->errstr; | |
1396 | .Ve | |
1397 | .Sp | |
1398 | Using placeholders and \f(CW@bind_values\fR with the \f(CW\*(C`do\*(C'\fR method can be | |
1399 | useful because it avoids the need to correctly quote any variables | |
1400 | in the \f(CW$statement\fR. But if you'll be executing the statement many | |
1401 | times then it's more efficient to \f(CW\*(C`prepare\*(C'\fR it once and call | |
1402 | \&\f(CW\*(C`execute\*(C'\fR many times instead. | |
1403 | .Sp | |
1404 | The \f(CW\*(C`q{...}\*(C'\fR style quoting used in this example avoids clashing with | |
1405 | quotes that may be used in the \s-1SQL\s0 statement. Use the double-quote-like | |
1406 | \&\f(CW\*(C`qq{...}\*(C'\fR operator if you want to interpolate variables into the string. | |
1407 | See \*(L"Quote and Quote-like Operators\*(R" in perlop for more details. | |
1408 | .ie n .IP """selectrow_array""" 4 | |
1409 | .el .IP "\f(CWselectrow_array\fR" 4 | |
1410 | .IX Item "selectrow_array" | |
1411 | .Vb 3 | |
1412 | \& @row_ary = $dbh->selectrow_array($statement); | |
1413 | \& @row_ary = $dbh->selectrow_array($statement, \e%attr); | |
1414 | \& @row_ary = $dbh->selectrow_array($statement, \e%attr, @bind_values); | |
1415 | .Ve | |
1416 | .Sp | |
1417 | This utility method combines \*(L"prepare\*(R", \*(L"execute\*(R" and | |
1418 | \&\*(L"fetchrow_array\*(R" into a single call. If called in a list context, it | |
1419 | returns the first row of data from the statement. If called in a scalar | |
1420 | context, it returns the first field of the first row. The \f(CW$statement\fR | |
1421 | parameter can be a previously prepared statement handle, in which case | |
1422 | the \f(CW\*(C`prepare\*(C'\fR is skipped. | |
1423 | .Sp | |
1424 | If any method fails, and \*(L"RaiseError\*(R" is not set, \f(CW\*(C`selectrow_array\*(C'\fR | |
1425 | will return an empty list. | |
1426 | .Sp | |
1427 | In a scalar context, \f(CW\*(C`selectrow_array\*(C'\fR returns the value of the first | |
1428 | field. An \f(CW\*(C`undef\*(C'\fR is returned if there are no matching rows or an error | |
1429 | occurred. Since that \f(CW\*(C`undef\*(C'\fR can't be distinguished from an \f(CW\*(C`undef\*(C'\fR returned | |
1430 | because the first field value was \s-1NULL\s0, calling \f(CW\*(C`selectrow_array\*(C'\fR in | |
1431 | a scalar context should be used with caution. | |
1432 | .ie n .IP """selectall_arrayref""" 4 | |
1433 | .el .IP "\f(CWselectall_arrayref\fR" 4 | |
1434 | .IX Item "selectall_arrayref" | |
1435 | .Vb 3 | |
1436 | \& $ary_ref = $dbh->selectall_arrayref($statement); | |
1437 | \& $ary_ref = $dbh->selectall_arrayref($statement, \e%attr); | |
1438 | \& $ary_ref = $dbh->selectall_arrayref($statement, \e%attr, @bind_values); | |
1439 | .Ve | |
1440 | .Sp | |
1441 | This utility method combines \*(L"prepare\*(R", \*(L"execute\*(R" and | |
1442 | \&\*(L"fetchall_arrayref\*(R" into a single call. It returns a reference to an | |
1443 | array containing a reference to an array for each row of data fetched. | |
1444 | .Sp | |
1445 | The \f(CW$statement\fR parameter can be a previously prepared statement handle, | |
1446 | in which case the \f(CW\*(C`prepare\*(C'\fR is skipped. This is recommended if the | |
1447 | statement is going to be executed many times. | |
1448 | .Sp | |
1449 | If \*(L"RaiseError\*(R" is not set and any method except \f(CW\*(C`fetchall_arrayref\*(C'\fR | |
1450 | fails then \f(CW\*(C`selectall_arrayref\*(C'\fR will return \f(CW\*(C`undef\*(C'\fR; if | |
1451 | \&\f(CW\*(C`fetchall_arrayref\*(C'\fR fails then it will return with whatever data it | |
1452 | has been fetched thus far. \f(CW$DBI::err\fR should be checked to catch that. | |
1453 | .ie n .IP """selectall_hashref""" 4 | |
1454 | .el .IP "\f(CWselectall_hashref\fR" 4 | |
1455 | .IX Item "selectall_hashref" | |
1456 | .Vb 3 | |
1457 | \& $ary_ref = $dbh->selectall_hashref($statement); | |
1458 | \& $ary_ref = $dbh->selectall_hashref($statement, \e%attr); | |
1459 | \& $ary_ref = $dbh->selectall_hashref($statement, \e%attr, @bind_values); | |
1460 | .Ve | |
1461 | .Sp | |
1462 | This utility method combines \*(L"prepare\*(R", \*(L"execute\*(R" and | |
1463 | \&\*(L"fetchrow_hashref\*(R" into a single call. It returns a reference to an | |
1464 | array containing, for each row of data fetched, a reference to a hash | |
1465 | containing field name and value pairs for that row. | |
1466 | .Sp | |
1467 | The \f(CW$statement\fR parameter can be a previously prepared statement handle, | |
1468 | in which case the \f(CW\*(C`prepare\*(C'\fR is skipped. This is recommended if the | |
1469 | statement is going to be executed many times. | |
1470 | .Sp | |
1471 | If any method except \f(CW\*(C`fetchrow_hashref\*(C'\fR fails, and \*(L"RaiseError\*(R" is not set, | |
1472 | \&\f(CW\*(C`selectall_hashref\*(C'\fR will return \f(CW\*(C`undef\*(C'\fR. If \f(CW\*(C`fetchrow_hashref\*(C'\fR fails and | |
1473 | \&\*(L"RaiseError\*(R" is not set, then it will return with whatever data it | |
1474 | has fetched thus far. \f(CW$DBI::err\fR should be checked to catch that. | |
1475 | .ie n .IP """selectcol_arrayref""" 4 | |
1476 | .el .IP "\f(CWselectcol_arrayref\fR" 4 | |
1477 | .IX Item "selectcol_arrayref" | |
1478 | .Vb 3 | |
1479 | \& $ary_ref = $dbh->selectcol_arrayref($statement); | |
1480 | \& $ary_ref = $dbh->selectcol_arrayref($statement, \e%attr); | |
1481 | \& $ary_ref = $dbh->selectcol_arrayref($statement, \e%attr, @bind_values); | |
1482 | .Ve | |
1483 | .Sp | |
1484 | This utility method combines \*(L"prepare\*(R", \*(L"execute\*(R", and fetching one | |
1485 | column from all the rows, into a single call. It returns a reference to | |
1486 | an array containing the values of the first column from each row. | |
1487 | .Sp | |
1488 | The \f(CW$statement\fR parameter can be a previously prepared statement handle, | |
1489 | in which case the \f(CW\*(C`prepare\*(C'\fR is skipped. This is recommended if the | |
1490 | statement is going to be executed many times. | |
1491 | .Sp | |
1492 | If any method except \f(CW\*(C`fetch\*(C'\fR fails, and \*(L"RaiseError\*(R" is not set, | |
1493 | \&\f(CW\*(C`selectcol_arrayref\*(C'\fR will return \f(CW\*(C`undef\*(C'\fR. If \f(CW\*(C`fetch\*(C'\fR fails and | |
1494 | \&\*(L"RaiseError\*(R" is not set, then it will return with whatever data it | |
1495 | has fetched thus far. \f(CW$DBI::err\fR should be checked to catch that. | |
1496 | .ie n .IP """prepare""" 4 | |
1497 | .el .IP "\f(CWprepare\fR" 4 | |
1498 | .IX Item "prepare" | |
1499 | .Vb 2 | |
1500 | \& $sth = $dbh->prepare($statement) or die $dbh->errstr; | |
1501 | \& $sth = $dbh->prepare($statement, \e%attr) or die $dbh->errstr; | |
1502 | .Ve | |
1503 | .Sp | |
1504 | Prepares a single statement for later execution by the database | |
1505 | engine and returns a reference to a statement handle object. | |
1506 | .Sp | |
1507 | The returned statement handle can be used to get attributes of the | |
1508 | statement and invoke the \*(L"execute\*(R" method. See \*(L"Statement Handle Methods\*(R". | |
1509 | .Sp | |
1510 | Drivers for engines without the concept of preparing a | |
1511 | statement will typically just store the statement in the returned | |
1512 | handle and process it when \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`execute\*(C'\fR is called. Such drivers are | |
1513 | unlikely to give much useful information about the | |
1514 | statement, such as \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`{NUM_OF_FIELDS}\*(C'\fR, until after \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`execute\*(C'\fR | |
1515 | has been called. Portable applications should take this into account. | |
1516 | .Sp | |
1517 | In general, \s-1DBI\s0 drivers do not parse the contents of the statement | |
1518 | (other than simply counting any \*(L"Placeholders\*(R"). The statement is | |
1519 | passed directly to the database engine, sometimes known as pass-thru | |
1520 | mode. This has advantages and disadvantages. On the plus side, you can | |
1521 | access all the functionality of the engine being used. On the downside, | |
1522 | you're limited if you're using a simple engine, and you need to take extra care if | |
1523 | writing applications intended to be portable between engines. | |
1524 | .Sp | |
1525 | Portable applications should not assume that a new statement can be | |
1526 | prepared and/or executed while still fetching results from a previous | |
1527 | statement. | |
1528 | .Sp | |
1529 | Some command-line \s-1SQL\s0 tools use statement terminators, like a semicolon, | |
1530 | to indicate the end of a statement. Such terminators should not normally | |
1531 | be used with the \s-1DBI\s0. | |
1532 | .ie n .IP """prepare_cached""" 4 | |
1533 | .el .IP "\f(CWprepare_cached\fR" 4 | |
1534 | .IX Item "prepare_cached" | |
1535 | .Vb 3 | |
1536 | \& $sth = $dbh->prepare_cached($statement) | |
1537 | \& $sth = $dbh->prepare_cached($statement, \e%attr) | |
1538 | \& $sth = $dbh->prepare_cached($statement, \e%attr, $allow_active) | |
1539 | .Ve | |
1540 | .Sp | |
1541 | Like \*(L"prepare\*(R" except that the statement handle returned will be | |
1542 | stored in a hash associated with the \f(CW$dbh\fR. If another call is made to | |
1543 | \&\f(CW\*(C`prepare_cached\*(C'\fR with the same \f(CW$statement\fR and \f(CW%attr\fR values, then the | |
1544 | corresponding cached \f(CW$sth\fR will be returned without contacting the | |
1545 | database server. | |
1546 | .Sp | |
1547 | This caching can be useful in some applications, but it can also cause | |
1548 | problems and should be used with care. If the cached \f(CW$sth\fR being | |
1549 | returned is active (i.e., is a \f(CW\*(C`SELECT\*(C'\fR that may still have data to be | |
1550 | fetched) then a warning will be generated and \f(CW\*(C`finish\*(C'\fR will be called | |
1551 | for you. The warning can be suppressed by setting \f(CW$allow_active\fR to | |
1552 | true. The cache can be accessed (and cleared) via the \*(L"CachedKids\*(R" | |
1553 | attribute. | |
1554 | .Sp | |
1555 | Here's an example of one possible use of \f(CW\*(C`prepare_cached\*(C'\fR: | |
1556 | .Sp | |
1557 | .Vb 8 | |
1558 | \& while ( ($field, $value) = each %search_fields ) { | |
1559 | \& push @sql, "$field = ?"; | |
1560 | \& push @values, $value; | |
1561 | \& } | |
1562 | \& $qualifier = ""; | |
1563 | \& $qualifier = "where ".join(" and ", @sql) if @sql; | |
1564 | \& $sth = $dbh->prepare_cached("SELECT * FROM table $qualifier"); | |
1565 | \& $sth->execute(@values); | |
1566 | .Ve | |
1567 | .ie n .IP """commit""" 4 | |
1568 | .el .IP "\f(CWcommit\fR" 4 | |
1569 | .IX Item "commit" | |
1570 | .Vb 1 | |
1571 | \& $rc = $dbh->commit or die $dbh->errstr; | |
1572 | .Ve | |
1573 | .Sp | |
1574 | Commit (make permanent) the most recent series of database changes | |
1575 | if the database supports transactions and AutoCommit is off. | |
1576 | .Sp | |
1577 | If \f(CW\*(C`AutoCommit\*(C'\fR is on, then calling | |
1578 | \&\f(CW\*(C`commit\*(C'\fR will issue a \*(L"commit ineffective with AutoCommit\*(R" warning. | |
1579 | .Sp | |
1580 | See also \*(L"Transactions\*(R" in the \*(L"\s-1FURTHER\s0 \s-1INFORMATION\s0\*(R" section below. | |
1581 | .ie n .IP """rollback""" 4 | |
1582 | .el .IP "\f(CWrollback\fR" 4 | |
1583 | .IX Item "rollback" | |
1584 | .Vb 1 | |
1585 | \& $rc = $dbh->rollback or die $dbh->errstr; | |
1586 | .Ve | |
1587 | .Sp | |
1588 | Rollback (undo) the most recent series of uncommitted database | |
1589 | changes if the database supports transactions and AutoCommit is off. | |
1590 | .Sp | |
1591 | If \f(CW\*(C`AutoCommit\*(C'\fR is on, then calling | |
1592 | \&\f(CW\*(C`rollback\*(C'\fR will issue a \*(L"rollback ineffective with AutoCommit\*(R" warning. | |
1593 | .Sp | |
1594 | See also \*(L"Transactions\*(R" in the \*(L"\s-1FURTHER\s0 \s-1INFORMATION\s0\*(R" section below. | |
1595 | .ie n .IP """disconnect""" 4 | |
1596 | .el .IP "\f(CWdisconnect\fR" 4 | |
1597 | .IX Item "disconnect" | |
1598 | .Vb 1 | |
1599 | \& $rc = $dbh->disconnect or warn $dbh->errstr; | |
1600 | .Ve | |
1601 | .Sp | |
1602 | Disconnects the database from the database handle. \f(CW\*(C`disconnect\*(C'\fR is typically only used | |
1603 | before exiting the program. The handle is of little use after disconnecting. | |
1604 | .Sp | |
1605 | The transaction behavior of the \f(CW\*(C`disconnect\*(C'\fR method is, sadly, | |
1606 | undefined. Some database systems (such as Oracle and Ingres) will | |
1607 | automatically commit any outstanding changes, but others (such as | |
1608 | Informix) will rollback any outstanding changes. Applications not | |
1609 | using \f(CW\*(C`AutoCommit\*(C'\fR should explicitly call \f(CW\*(C`commit\*(C'\fR or \f(CW\*(C`rollback\*(C'\fR before | |
1610 | calling \f(CW\*(C`disconnect\*(C'\fR. | |
1611 | .Sp | |
1612 | The database is automatically disconnected by the \f(CW\*(C`DESTROY\*(C'\fR method if | |
1613 | still connected when there are no longer any references to the handle. | |
1614 | The \f(CW\*(C`DESTROY\*(C'\fR method for each driver should implicitly call \f(CW\*(C`rollback\*(C'\fR to | |
1615 | undo any uncommitted changes. This is vital behavior to ensure that | |
1616 | incomplete transactions don't get committed simply because Perl calls | |
1617 | \&\f(CW\*(C`DESTROY\*(C'\fR on every object before exiting. Also, do not rely on the order | |
1618 | of object destruction during \*(L"global destruction\*(R", as it is undefined. | |
1619 | .Sp | |
1620 | Generally, if you want your changes to be commited or rolled back when | |
1621 | you disconnect, then you should explicitly call \*(L"commit\*(R" or \*(L"rollback\*(R" | |
1622 | before disconnecting. | |
1623 | .Sp | |
1624 | If you disconnect from a database while you still have active statement | |
1625 | handles, you will get a warning. The statement handles should either be | |
1626 | cleared (destroyed) before disconnecting, or the \f(CW\*(C`finish\*(C'\fR method | |
1627 | should be called on | |
1628 | each one. | |
1629 | .ie n .IP """ping""" 4 | |
1630 | .el .IP "\f(CWping\fR" 4 | |
1631 | .IX Item "ping" | |
1632 | .Vb 1 | |
1633 | \& $rc = $dbh->ping; | |
1634 | .Ve | |
1635 | .Sp | |
1636 | Attempts to determine, in a reasonably efficient way, if the database | |
1637 | server is still running and the connection to it is still working. | |
1638 | Individual drivers should implement this function in the most suitable | |
1639 | manner for their database engine. | |
1640 | .Sp | |
1641 | The current \fIdefault\fR implementation always returns true without | |
1642 | actually doing anything. Actually, it returns "\f(CW\*(C`0 but true\*(C'\fR" which is | |
1643 | true but zero. That way you can tell if the return value is genuine or | |
1644 | just the default. Drivers should override this method with one that | |
1645 | does the right thing for their type of database. | |
1646 | .Sp | |
1647 | Few applications would have direct use for this method. See the specialized | |
1648 | Apache::DBI module for one example usage. | |
1649 | .ie n .IP """table_info""\fR \fI\s-1NEW\s0" 4 | |
1650 | .el .IP "\f(CWtable_info\fR \fI\s-1NEW\s0\fR" 4 | |
1651 | .IX Item "table_info NEW" | |
1652 | \&\fBWarning:\fR This method is experimental and may change. | |
1653 | .Sp | |
1654 | .Vb 2 | |
1655 | \& $sth = $dbh->table_info; | |
1656 | \& $sth = $dbh->table_info( \e%attr ); | |
1657 | .Ve | |
1658 | .Sp | |
1659 | Returns an active statement handle that can be used to fetch | |
1660 | information about tables and views that exist in the database. | |
1661 | .Sp | |
1662 | The following attributes (all or separate) may be used as selection criteria: | |
1663 | .Sp | |
1664 | .Vb 6 | |
1665 | \& %attr = ( | |
1666 | \& TABLE_CAT => $CatVal # String value of the catalog name | |
1667 | \& , TABLE_SCHEM => $SchVal # String value of the schema name | |
1668 | \& , TABLE_NAME => $TblVal # String value of the table name | |
1669 | \& , TABLE_TYPE => $TypVal # String value of the table type(s) | |
1670 | \& ); | |
1671 | .Ve | |
1672 | .Sp | |
1673 | Note: The support for the selection criteria is driver specific. If the | |
1674 | driver doesn't support one or more then them then you'll get back more | |
1675 | than you asked for and can do the filtering yourself. | |
1676 | .Sp | |
1677 | The arguments \s-1TABLE_CAT\s0, \s-1TABLE_SCHEM\s0 and \s-1TABLE_NAME\s0 may accept search | |
1678 | patterns according to the database/driver, for example: | |
1679 | .Sp | |
1680 | .Vb 1 | |
1681 | \& $sth = $dbh->table_info( { TABLE_NAME => '%TAB%'} ); | |
1682 | .Ve | |
1683 | .Sp | |
1684 | The value of \s-1TABLE_TYPE\s0 is a comma-separated list of one or more types | |
1685 | of tables to be returned in the result set. Each value may optionally be | |
1686 | quoted, e.g.: | |
1687 | .Sp | |
1688 | .Vb 2 | |
1689 | \& $sth = $dbh->table_info( { TABLE_TYPE => "TABLE" } ); | |
1690 | \& $sth = $dbh->table_info( { TABLE_TYPE => "'TABLE', 'VIEW'" } ); | |
1691 | .Ve | |
1692 | .Sp | |
1693 | In addition the following special cases may also be supported by some drivers: | |
1694 | .RS 4 | |
1695 | .IP "\(bu If the value of \s-1TABLE_CAT\s0 is '%' and \s-1TABLE_SCHEM\s0 and \s-1TABLE_NAME\s0 name are empty strings, the result set contains a list of catalog names. For example:" 4 | |
1696 | .IX Item "If the value of TABLE_CAT is '%' and TABLE_SCHEM and TABLE_NAME name are empty strings, the result set contains a list of catalog names. For example:" | |
1697 | .Vb 1 | |
1698 | \& $sth = $dbh->table_info({ TABLE_CAT=>'%', TABLE_SCHEM=>'', TABLE_NAME=>'' }); | |
1699 | .Ve | |
1700 | .IP "\(bu If the value of \s-1TABLE_SCHEM\s0 is '%' and \s-1TABLE_CAT\s0 and \s-1TABLE_NAME\s0 are empty strings, the result set contains a list of schema names." 4 | |
1701 | .IX Item "If the value of TABLE_SCHEM is '%' and TABLE_CAT and TABLE_NAME are empty strings, the result set contains a list of schema names." | |
1702 | .PD 0 | |
1703 | .IP "\(bu If the value of \s-1TABLE_TYPE\s0 is '%' and \s-1TABLE_CAT\s0, \s-1TABLE_SCHEM\s0, and \s-1TABLE_NAME\s0 are all empty strings, the result set contains a list of table types." 4 | |
1704 | .IX Item "If the value of TABLE_TYPE is '%' and TABLE_CAT, TABLE_SCHEM, and TABLE_NAME are all empty strings, the result set contains a list of table types." | |
1705 | .RE | |
1706 | .RS 4 | |
1707 | .PD | |
1708 | .Sp | |
1709 | The statement handle returned has at least the following fields in the | |
1710 | order show below. Other fields, after these, may also be present. | |
1711 | .Sp | |
1712 | \&\fB\s-1TABLE_CAT\s0\fR: Table catalog identifier. This field is \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) if not | |
1713 | applicable to the data source, which is usually the case. This field | |
1714 | is empty if not applicable to the table. | |
1715 | .Sp | |
1716 | \&\fB\s-1TABLE_SCHEM\s0\fR: The name of the schema containing the \s-1TABLE_NAME\s0 value. | |
1717 | This field is \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) if not applicable to data source, and | |
1718 | empty if not applicable to the table. | |
1719 | .Sp | |
1720 | \&\fB\s-1TABLE_NAME\s0\fR: Name of the table (or view, synonym, etc). | |
1721 | .Sp | |
1722 | \&\fB\s-1TABLE_TYPE\s0\fR: One of the following: \*(L"\s-1TABLE\s0\*(R", \*(L"\s-1VIEW\s0\*(R", \*(L"\s-1SYSTEM\s0 \s-1TABLE\s0\*(R", | |
1723 | \&\*(L"\s-1GLOBAL\s0 \s-1TEMPORARY\s0\*(R", \*(L"\s-1LOCAL\s0 \s-1TEMPORARY\s0\*(R", \*(L"\s-1ALIAS\s0\*(R", \*(L"\s-1SYNONYM\s0\*(R" or a type | |
1724 | identifier that is specific to the data | |
1725 | source. | |
1726 | .Sp | |
1727 | \&\fB\s-1REMARKS\s0\fR: A description of the table. May be \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR). | |
1728 | .Sp | |
1729 | Note that \f(CW\*(C`table_info\*(C'\fR might not return records for all tables. | |
1730 | Applications can use any valid table regardless of whether it's | |
1731 | returned by \f(CW\*(C`table_info\*(C'\fR. See also \*(L"tables\*(R". | |
1732 | .Sp | |
1733 | For more detailed information about the fields and their meanings, | |
1734 | you can refer to: | |
1735 | .Sp | |
1736 | .Vb 1 | |
1737 | \& http://msdn.microsoft.com/library/psdk/dasdk/odch6wqb.htm | |
1738 | .Ve | |
1739 | .Sp | |
1740 | If that \s-1URL\s0 ceases to work then use the \s-1MSDN\s0 search facility at: | |
1741 | .Sp | |
1742 | .Vb 1 | |
1743 | \& http://search.microsoft.com/us/dev/ | |
1744 | .Ve | |
1745 | .Sp | |
1746 | and search for \f(CW\*(C`SQLTables returns\*(C'\fR using the exact phrase option. | |
1747 | The link you want will probably just be called \f(CW\*(C`SQLTables\*(C'\fR and will | |
1748 | be part of the Data Access \s-1SDK\s0. | |
1749 | .Sp | |
1750 | See also page 306 of the (very large) \s-1SQL/CLI\s0 specification: | |
1751 | .Sp | |
1752 | .Vb 1 | |
1753 | \& http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/DF86E81BE70151D58525699800643F56/$FILE/32N0595T.PDF | |
1754 | .Ve | |
1755 | .RE | |
1756 | .ie n .IP """tables""\fR \fI\s-1NEW\s0" 4 | |
1757 | .el .IP "\f(CWtables\fR \fI\s-1NEW\s0\fR" 4 | |
1758 | .IX Item "tables NEW" | |
1759 | \&\fBWarning:\fR This method is experimental and may change. | |
1760 | .Sp | |
1761 | .Vb 2 | |
1762 | \& @names = $dbh->tables; | |
1763 | \& @names = $dbh->tables( \e%attr ); | |
1764 | .Ve | |
1765 | .Sp | |
1766 | Returns a list of table and view names, possibly including a schema prefix. | |
1767 | This list should include all | |
1768 | tables that can be used in a \f(CW\*(C`SELECT\*(C'\fR statement without further | |
1769 | qualification. | |
1770 | .Sp | |
1771 | Note that \f(CW\*(C`table_info\*(C'\fR might not return records for all tables. | |
1772 | Applications can use any valid table regardless of whether it's | |
1773 | returned by tables. See also \*(L"table_info\*(R". | |
1774 | .ie n .IP """type_info_all""\fR \fI\s-1NEW\s0" 4 | |
1775 | .el .IP "\f(CWtype_info_all\fR \fI\s-1NEW\s0\fR" 4 | |
1776 | .IX Item "type_info_all NEW" | |
1777 | \&\fBWarning:\fR This method is experimental and may change. | |
1778 | .Sp | |
1779 | .Vb 1 | |
1780 | \& $type_info_all = $dbh->type_info_all; | |
1781 | .Ve | |
1782 | .Sp | |
1783 | Returns a reference to an array which holds information about each data | |
1784 | type variant supported by the database and driver. The array and its | |
1785 | contents should be treated as read\-only. | |
1786 | .Sp | |
1787 | The first item is a reference to an 'index' hash of \f(CW\*(C`Name =\*(C'\fR> \f(CW\*(C`Index\*(C'\fR pairs. | |
1788 | The items following that are references to arrays, one per supported data | |
1789 | type variant. The leading index hash defines the names and order of the | |
1790 | fields within the arrays that follow it. | |
1791 | For example: | |
1792 | .Sp | |
1793 | .Vb 25 | |
1794 | \& $type_info_all = [ | |
1795 | \& { TYPE_NAME => 0, | |
1796 | \& DATA_TYPE => 1, | |
1797 | \& COLUMN_SIZE => 2, # was PRECISION originally | |
1798 | \& LITERAL_PREFIX => 3, | |
1799 | \& LITERAL_SUFFIX => 4, | |
1800 | \& CREATE_PARAMS => 5, | |
1801 | \& NULLABLE => 6, | |
1802 | \& CASE_SENSITIVE => 7, | |
1803 | \& SEARCHABLE => 8, | |
1804 | \& UNSIGNED_ATTRIBUTE=> 9, | |
1805 | \& FIXED_PREC_SCALE => 10, # was MONEY originally | |
1806 | \& AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally | |
1807 | \& LOCAL_TYPE_NAME => 12, | |
1808 | \& MINIMUM_SCALE => 13, | |
1809 | \& MAXIMUM_SCALE => 14, | |
1810 | \& NUM_PREC_RADIX => 15, | |
1811 | \& }, | |
1812 | \& [ 'VARCHAR', SQL_VARCHAR, | |
1813 | \& undef, "'","'", undef,0, 1,1,0,0,0,undef,1,255, undef | |
1814 | \& ], | |
1815 | \& [ 'INTEGER', SQL_INTEGER, | |
1816 | \& undef, "", "", undef,0, 0,1,0,0,0,undef,0, 0, 10 | |
1817 | \& ], | |
1818 | \& ]; | |
1819 | .Ve | |
1820 | .Sp | |
1821 | Note that more than one row may have the same value in the \f(CW\*(C`DATA_TYPE\*(C'\fR | |
1822 | field if there are different ways to spell the type name and/or there | |
1823 | are variants of the type with different attributes (e.g., with and | |
1824 | without \f(CW\*(C`AUTO_UNIQUE_VALUE\*(C'\fR set, with and without \f(CW\*(C`UNSIGNED_ATTRIBUTE\*(C'\fR, etc). | |
1825 | .Sp | |
1826 | The rows are ordered by \f(CW\*(C`DATA_TYPE\*(C'\fR first and then by how closely each | |
1827 | type maps to the corresponding \s-1ODBC\s0 \s-1SQL\s0 data type, closest first. | |
1828 | .Sp | |
1829 | The meaning of the fields is described in the documentation for | |
1830 | the \*(L"type_info\*(R" method. The index values shown above (e.g., | |
1831 | \&\f(CW\*(C`NULLABLE =\*(C'\fR> \f(CW6\fR) are for illustration only. Drivers may define the | |
1832 | fields with a different order. | |
1833 | .Sp | |
1834 | This method is not normally used directly. The \*(L"type_info\*(R" method | |
1835 | provides a more useful interface to the data. | |
1836 | .Sp | |
1837 | Even though an 'index' hash is provided, all the field names in the | |
1838 | index hash defined above will always have the index values defined | |
1839 | above. This is defined behaviour so that you don't need to rely on the | |
1840 | index hash, which is handy because the lettercase of the keys is not | |
1841 | defined. It is usually uppercase, as show here, but drivers are free to | |
1842 | return names with any lettercase. Drivers are also free to return extra | |
1843 | driver-specific columns of information \- though it's recommended that | |
1844 | they start at column index 50 to leave room for expansion of the | |
1845 | \&\s-1DBI/ODBC\s0 specification. | |
1846 | .ie n .IP """type_info""\fR \fI\s-1NEW\s0" 4 | |
1847 | .el .IP "\f(CWtype_info\fR \fI\s-1NEW\s0\fR" 4 | |
1848 | .IX Item "type_info NEW" | |
1849 | \&\fBWarning:\fR This method is experimental and may change. | |
1850 | .Sp | |
1851 | .Vb 1 | |
1852 | \& @type_info = $dbh->type_info($data_type); | |
1853 | .Ve | |
1854 | .Sp | |
1855 | Returns a list of hash references holding information about one or more | |
1856 | variants of \f(CW$data_type\fR. The list is ordered by \f(CW\*(C`DATA_TYPE\*(C'\fR first and | |
1857 | then by how closely each type maps to the corresponding \s-1ODBC\s0 \s-1SQL\s0 data | |
1858 | type, closest first. If called in a scalar context then only the first | |
1859 | (best) element is returned. | |
1860 | .Sp | |
1861 | If \f(CW$data_type\fR is undefined or \f(CW\*(C`SQL_ALL_TYPES\*(C'\fR, then the list will | |
1862 | contain hashes for all data type variants supported by the database and driver. | |
1863 | .Sp | |
1864 | If \f(CW$data_type\fR is an array reference then \f(CW\*(C`type_info\*(C'\fR returns the | |
1865 | information for the \fIfirst\fR type in the array that has any matches. | |
1866 | .Sp | |
1867 | The keys of the hash follow the same letter case conventions as the | |
1868 | rest of the \s-1DBI\s0 (see \*(L"Naming Conventions and Name Space\*(R"). The | |
1869 | following items should exist: | |
1870 | .RS 4 | |
1871 | .IP "\s-1TYPE_NAME\s0 (string)" 4 | |
1872 | .IX Item "TYPE_NAME (string)" | |
1873 | Data type name for use in \s-1CREATE\s0 \s-1TABLE\s0 statements etc. | |
1874 | .IP "\s-1DATA_TYPE\s0 (integer)" 4 | |
1875 | .IX Item "DATA_TYPE (integer)" | |
1876 | \&\s-1SQL\s0 data type number. | |
1877 | .IP "\s-1COLUMN_SIZE\s0 (integer)" 4 | |
1878 | .IX Item "COLUMN_SIZE (integer)" | |
1879 | For numeric types, this is either the total number of digits (if the | |
1880 | \&\s-1NUM_PREC_RADIX\s0 value is 10) or the total number of bits allowed in the | |
1881 | column (if \s-1NUM_PREC_RADIX\s0 is 2). | |
1882 | .Sp | |
1883 | For string types, this is the maximum size of the string in bytes. | |
1884 | .Sp | |
1885 | For date and interval types, this is the maximum number of characters | |
1886 | needed to display the value. | |
1887 | .IP "\s-1LITERAL_PREFIX\s0 (string)" 4 | |
1888 | .IX Item "LITERAL_PREFIX (string)" | |
1889 | Characters used to prefix a literal. A typical prefix is "\f(CW\*(C`'\*(C'\fR\*(L" for characters, | |
1890 | or possibly \*(R"\f(CW\*(C`0x\*(C'\fR" for binary values passed as hexadecimal. \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is | |
1891 | returned for data types for which this is not applicable. | |
1892 | .IP "\s-1LITERAL_SUFFIX\s0 (string)" 4 | |
1893 | .IX Item "LITERAL_SUFFIX (string)" | |
1894 | Characters used to suffix a literal. Typically "\f(CW\*(C`'\*(C'\fR" for characters. | |
1895 | \&\s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned for data types where this is not applicable. | |
1896 | .IP "\s-1CREATE_PARAMS\s0 (string)" 4 | |
1897 | .IX Item "CREATE_PARAMS (string)" | |
1898 | Parameter names for data type definition. For example, \f(CW\*(C`CREATE_PARAMS\*(C'\fR for a | |
1899 | \&\f(CW\*(C`DECIMAL\*(C'\fR would be "\f(CW\*(C`precision,scale\*(C'\fR" if the \s-1DECIMAL\s0 type should be | |
1900 | declared as \f(CW\*(C`DECIMAL(\*(C'\fR\fIprecision,scale\fR\f(CW\*(C`)\*(C'\fR where \fIprecision\fR and \fIscale\fR | |
1901 | are integer values. For a \f(CW\*(C`VARCHAR\*(C'\fR it would be "\f(CW\*(C`max length\*(C'\fR". | |
1902 | \&\s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned for data types for which this is not applicable. | |
1903 | .IP "\s-1NULLABLE\s0 (integer)" 4 | |
1904 | .IX Item "NULLABLE (integer)" | |
1905 | Indicates whether the data type accepts a \s-1NULL\s0 value: | |
1906 | \&\f(CW0\fR or an empty string = no, \f(CW1\fR = yes, \f(CW2\fR = unknown. | |
1907 | .IP "\s-1CASE_SENSITIVE\s0 (boolean)" 4 | |
1908 | .IX Item "CASE_SENSITIVE (boolean)" | |
1909 | Indicates whether the data type is case sensitive in collations and | |
1910 | comparisons. | |
1911 | .IP "\s-1SEARCHABLE\s0 (integer)" 4 | |
1912 | .IX Item "SEARCHABLE (integer)" | |
1913 | Indicates how the data type can be used in a \s-1WHERE\s0 clause, as | |
1914 | follows: | |
1915 | .Sp | |
1916 | .Vb 4 | |
1917 | \& 0 - Cannot be used in a WHERE clause | |
1918 | \& 1 - Only with a LIKE predicate | |
1919 | \& 2 - All comparison operators except LIKE | |
1920 | \& 3 - Can be used in a WHERE clause with any comparison operator | |
1921 | .Ve | |
1922 | .IP "\s-1UNSIGNED_ATTRIBUTE\s0 (boolean)" 4 | |
1923 | .IX Item "UNSIGNED_ATTRIBUTE (boolean)" | |
1924 | Indicates whether the data type is unsigned. \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned | |
1925 | for data types for which this is not applicable. | |
1926 | .IP "\s-1FIXED_PREC_SCALE\s0 (boolean)" 4 | |
1927 | .IX Item "FIXED_PREC_SCALE (boolean)" | |
1928 | Indicates whether the data type always has the same precision and scale | |
1929 | (such as a money type). \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned for data types | |
1930 | for which | |
1931 | this is not applicable. | |
1932 | .IP "\s-1AUTO_UNIQUE_VALUE\s0 (boolean)" 4 | |
1933 | .IX Item "AUTO_UNIQUE_VALUE (boolean)" | |
1934 | Indicates whether a column of this data type is automatically set to a | |
1935 | unique value whenever a new row is inserted. \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned | |
1936 | for data types for which this is not applicable. | |
1937 | .IP "\s-1LOCAL_TYPE_NAME\s0 (string)" 4 | |
1938 | .IX Item "LOCAL_TYPE_NAME (string)" | |
1939 | Localized version of the \f(CW\*(C`TYPE_NAME\*(C'\fR for use in dialog with users. | |
1940 | \&\s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned if a localized name is not available (in which | |
1941 | case \f(CW\*(C`TYPE_NAME\*(C'\fR should be used). | |
1942 | .IP "\s-1MINIMUM_SCALE\s0 (integer)" 4 | |
1943 | .IX Item "MINIMUM_SCALE (integer)" | |
1944 | The minimum scale of the data type. If a data type has a fixed scale, | |
1945 | then \f(CW\*(C`MAXIMUM_SCALE\*(C'\fR holds the same value. \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned for | |
1946 | data types for which this is not applicable. | |
1947 | .IP "\s-1MAXIMUM_SCALE\s0 (integer)" 4 | |
1948 | .IX Item "MAXIMUM_SCALE (integer)" | |
1949 | The maximum scale of the data type. If a data type has a fixed scale, | |
1950 | then \f(CW\*(C`MINIMUM_SCALE\*(C'\fR holds the same value. \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned for | |
1951 | data types for which this is not applicable. | |
1952 | .IP "\s-1SQL_DATA_TYPE\s0 (integer)" 4 | |
1953 | .IX Item "SQL_DATA_TYPE (integer)" | |
1954 | This column is the same as the \f(CW\*(C`DATA_TYPE\*(C'\fR column, except for interval | |
1955 | and datetime data types. For interval and datetime data types, the | |
1956 | \&\f(CW\*(C`SQL_DATA_TYPE\*(C'\fR field will return \f(CW\*(C`SQL_INTERVAL\*(C'\fR or \f(CW\*(C`SQL_DATETIME\*(C'\fR, and the | |
1957 | \&\f(CW\*(C`SQL_DATETIME_SUB\*(C'\fR field below will return the subcode for the specific | |
1958 | interval or datetime data type. If this field is \s-1NULL\s0, then the driver | |
1959 | does not support or report on interval or date subtypes. | |
1960 | .IP "\s-1SQL_DATETIME_SUB\s0 (integer)" 4 | |
1961 | .IX Item "SQL_DATETIME_SUB (integer)" | |
1962 | For interval or datetime data types, where the \f(CW\*(C`SQL_DATA_TYPE\*(C'\fR field | |
1963 | above is \f(CW\*(C`SQL_INTERVAL\*(C'\fR or \f(CW\*(C`SQL_DATETIME\*(C'\fR, this field will hold the subcode | |
1964 | for the specific interval or datetime data type. Otherwise it will be | |
1965 | \&\s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR). | |
1966 | .IP "\s-1NUM_PREC_RADIX\s0 (integer)" 4 | |
1967 | .IX Item "NUM_PREC_RADIX (integer)" | |
1968 | The radix value of the data type. For approximate numeric types, | |
1969 | \&\f(CW\*(C`NUM_PREC_RADIX\*(C'\fR | |
1970 | contains the value 2 and \f(CW\*(C`COLUMN_SIZE\*(C'\fR holds the number of bits. For | |
1971 | exact numeric types, \f(CW\*(C`NUM_PREC_RADIX\*(C'\fR contains the value 10 and \f(CW\*(C`COLUMN_SIZE\*(C'\fR holds | |
1972 | the number of decimal digits. \s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) is returned either for data types | |
1973 | for which this is not applicable or if the driver cannot report this information. | |
1974 | .IP "\s-1INTERVAL_PRECISION\s0 (integer)" 4 | |
1975 | .IX Item "INTERVAL_PRECISION (integer)" | |
1976 | The interval leading precision for interval types. \s-1NULL\s0 is returned | |
1977 | either for data types for which this is not applicable or if the driver | |
1978 | cannot report this information. | |
1979 | .RE | |
1980 | .RS 4 | |
1981 | .Sp | |
1982 | For example, to find the type name for the fields in a select statement | |
1983 | you can do: | |
1984 | .Sp | |
1985 | .Vb 1 | |
1986 | \& @names = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} } | |
1987 | .Ve | |
1988 | .Sp | |
1989 | Since \s-1DBI\s0 and \s-1ODBC\s0 drivers vary in how they map their types into the | |
1990 | \&\s-1ISO\s0 standard types you may need to search for more than one type. | |
1991 | Here's an example looking for a usable type to store a date: | |
1992 | .Sp | |
1993 | .Vb 1 | |
1994 | \& $my_date_type = $dbh->type_info( [ SQL_DATE, SQL_TIMESTAMP ] ); | |
1995 | .Ve | |
1996 | .Sp | |
1997 | Similarly, to more reliably find a type to store small integers, you could | |
1998 | use a list starting with \f(CW\*(C`SQL_SMALLINT\*(C'\fR, \f(CW\*(C`SQL_INTEGER\*(C'\fR, \f(CW\*(C`SQL_DECIMAL\*(C'\fR, etc. | |
1999 | .Sp | |
2000 | For more detailed information about these fields and their meanings, you | |
2001 | can refer to: | |
2002 | .Sp | |
2003 | .Vb 1 | |
2004 | \& http://msdn.microsoft.com/library/psdk/dasdk/odch6yy7.htm | |
2005 | .Ve | |
2006 | .Sp | |
2007 | If that \s-1URL\s0 ceases to work then use the \s-1MSDN\s0 search facility at | |
2008 | .Sp | |
2009 | .Vb 1 | |
2010 | \& http://search.microsoft.com/us/dev/ | |
2011 | .Ve | |
2012 | .Sp | |
2013 | and search the \s-1MSDN\s0 Library for \f(CW\*(C`SQLGetTypeInfo returns\*(C'\fR using the exact phrase option. | |
2014 | The link you want will probably just be called \f(CW\*(C`SQLGetTypeInfo\*(C'\fR (there | |
2015 | may be more than one). | |
2016 | .Sp | |
2017 | The individual data types are currently described here: | |
2018 | .Sp | |
2019 | .Vb 1 | |
2020 | \& http://msdn.microsoft.com/library/psdk/dasdk/odap8fcj.htm | |
2021 | .Ve | |
2022 | .Sp | |
2023 | If that \s-1URL\s0 ceases to work, or to get more general information, use the | |
2024 | \&\s-1MSDN\s0 search facility as described above and search for \f(CW\*(C`SQL Data Types\*(C'\fR. | |
2025 | .RE | |
2026 | .ie n .IP """quote""" 4 | |
2027 | .el .IP "\f(CWquote\fR" 4 | |
2028 | .IX Item "quote" | |
2029 | .Vb 2 | |
2030 | \& $sql = $dbh->quote($value); | |
2031 | \& $sql = $dbh->quote($value, $data_type); | |
2032 | .Ve | |
2033 | .Sp | |
2034 | Quote a string literal for use as a literal value in an \s-1SQL\s0 statement, | |
2035 | by escaping any special characters (such as quotation marks) | |
2036 | contained within the string and adding the required type of outer | |
2037 | quotation marks. | |
2038 | .Sp | |
2039 | .Vb 2 | |
2040 | \& $sql = sprintf "SELECT foo FROM bar WHERE baz = %s", | |
2041 | \& $dbh->quote("Don't"); | |
2042 | .Ve | |
2043 | .Sp | |
2044 | For most database types, quote would return \f(CW'Don''t'\fR (including the | |
2045 | outer quotation marks). | |
2046 | .Sp | |
2047 | An undefined \f(CW$value\fR value will be returned as the string \f(CW\*(C`NULL\*(C'\fR (without | |
2048 | quotation marks) to match how NULLs are represented in \s-1SQL\s0. | |
2049 | .Sp | |
2050 | If \f(CW$data_type\fR is supplied, it is used to try to determine the required | |
2051 | quoting behavior by using the information returned by \*(L"type_info\*(R". | |
2052 | As a special case, the standard numeric types are optimized to return | |
2053 | \&\f(CW$value\fR without calling \f(CW\*(C`type_info\*(C'\fR. | |
2054 | .Sp | |
2055 | Quote will probably \fInot\fR be able to deal with all possible input | |
2056 | (such as binary data or data containing newlines), and is not related in | |
2057 | any way with escaping or quoting shell meta\-characters. There is no | |
2058 | need to quote values being used with \*(L"Placeholders and Bind Values\*(R". | |
2059 | .Sh "Database Handle Attributes" | |
2060 | .IX Subsection "Database Handle Attributes" | |
2061 | This section describes attributes specific to database handles. | |
2062 | .PP | |
2063 | Changes to these database handle attributes do not affect any other | |
2064 | existing or future database handles. | |
2065 | .PP | |
2066 | Attempting to set or get the value of an unknown attribute is fatal, | |
2067 | except for private driver-specific attributes (which all have names | |
2068 | starting with a lowercase letter). | |
2069 | .PP | |
2070 | Example: | |
2071 | .PP | |
2072 | .Vb 2 | |
2073 | \& $h->{AutoCommit} = ...; # set/write | |
2074 | \& ... = $h->{AutoCommit}; # get/read | |
2075 | .Ve | |
2076 | .ie n .IP """AutoCommit"" (boolean)" 4 | |
2077 | .el .IP "\f(CWAutoCommit\fR (boolean)" 4 | |
2078 | .IX Item "AutoCommit (boolean)" | |
2079 | If true, then database changes cannot be rolled-back (undone). If false, | |
2080 | then database changes automatically occur within a \*(L"transaction\*(R", which | |
2081 | must either be committed or rolled back using the \f(CW\*(C`commit\*(C'\fR or \f(CW\*(C`rollback\*(C'\fR | |
2082 | methods. | |
2083 | .Sp | |
2084 | Drivers should always default to \f(CW\*(C`AutoCommit\*(C'\fR mode (an unfortunate | |
2085 | choice largely forced on the \s-1DBI\s0 by \s-1ODBC\s0 and \s-1JDBC\s0 conventions.) | |
2086 | .Sp | |
2087 | Attempting to set \f(CW\*(C`AutoCommit\*(C'\fR to an unsupported value is a fatal error. | |
2088 | This is an important feature of the \s-1DBI\s0. Applications that need | |
2089 | full transaction behavior can set \f(CW\*(C`$dbh\-\*(C'\fR>\f(CW\*(C`{AutoCommit} = 0\*(C'\fR (or | |
2090 | set \f(CW\*(C`AutoCommit\*(C'\fR to 0 via \*(L"connect\*(R") | |
2091 | without having to check that the value was assigned successfully. | |
2092 | .Sp | |
2093 | For the purposes of this description, we can divide databases into three | |
2094 | categories: | |
2095 | .Sp | |
2096 | .Vb 3 | |
2097 | \& Databases which don't support transactions at all. | |
2098 | \& Databases in which a transaction is always active. | |
2099 | \& Databases in which a transaction must be explicitly started (C<'BEGIN WORK'>). | |
2100 | .Ve | |
2101 | .Sp | |
2102 | \&\fB* Databases which don't support transactions at all\fR | |
2103 | .Sp | |
2104 | For these databases, attempting to turn \f(CW\*(C`AutoCommit\*(C'\fR off is a fatal error. | |
2105 | \&\f(CW\*(C`commit\*(C'\fR and \f(CW\*(C`rollback\*(C'\fR both issue warnings about being ineffective while | |
2106 | \&\f(CW\*(C`AutoCommit\*(C'\fR is in effect. | |
2107 | .Sp | |
2108 | \&\fB* Databases in which a transaction is always active\fR | |
2109 | .Sp | |
2110 | These are typically mainstream commercial relational databases with | |
2111 | \&\*(L"\s-1ANSI\s0 standard\*(R" transaction behavior. | |
2112 | If \f(CW\*(C`AutoCommit\*(C'\fR is off, then changes to the database won't have any | |
2113 | lasting effect unless \*(L"commit\*(R" is called (but see also | |
2114 | \&\*(L"disconnect\*(R"). If \*(L"rollback\*(R" is called then any changes since the | |
2115 | last commit are undone. | |
2116 | .Sp | |
2117 | If \f(CW\*(C`AutoCommit\*(C'\fR is on, then the effect is the same as if the \s-1DBI\s0 | |
2118 | called \f(CW\*(C`commit\*(C'\fR automatically after every successful database | |
2119 | operation. In other words, calling \f(CW\*(C`commit\*(C'\fR or \f(CW\*(C`rollback\*(C'\fR explicitly while | |
2120 | \&\f(CW\*(C`AutoCommit\*(C'\fR is on would be ineffective because the changes would | |
2121 | have already been commited. | |
2122 | .Sp | |
2123 | Changing \f(CW\*(C`AutoCommit\*(C'\fR from off to on should issue a \*(L"commit\*(R" in most drivers. | |
2124 | .Sp | |
2125 | Changing \f(CW\*(C`AutoCommit\*(C'\fR from on to off should have no immediate effect. | |
2126 | .Sp | |
2127 | For databases which don't support a specific auto-commit mode, the | |
2128 | driver has to commit each statement automatically using an explicit | |
2129 | \&\f(CW\*(C`COMMIT\*(C'\fR after it completes successfully (and roll it back using an | |
2130 | explicit \f(CW\*(C`ROLLBACK\*(C'\fR if it fails). The error information reported to the | |
2131 | application will correspond to the statement which was executed, unless | |
2132 | it succeeded and the commit or rollback failed. | |
2133 | .Sp | |
2134 | \&\fB* Databases in which a transaction must be explicitly started\fR | |
2135 | .Sp | |
2136 | For these databases, the intention is to have them act like databases in | |
2137 | which a transaction is always active (as described above). | |
2138 | .Sp | |
2139 | To do this, the \s-1DBI\s0 driver will automatically begin a transaction when | |
2140 | \&\f(CW\*(C`AutoCommit\*(C'\fR is turned off (from the default \*(L"on\*(R" state) and will | |
2141 | automatically begin another transaction after a \*(L"commit\*(R" or \*(L"rollback\*(R". | |
2142 | In this way, the application does not have to treat these databases as a | |
2143 | special case. | |
2144 | .Sp | |
2145 | See \*(L"commit\*(R", \*(L"disconnect\*(R" and \*(L"Transactions\*(R" for other important | |
2146 | notes about transactions. | |
2147 | .ie n .IP """Driver"" (handle)" 4 | |
2148 | .el .IP "\f(CWDriver\fR (handle)" 4 | |
2149 | .IX Item "Driver (handle)" | |
2150 | Holds the handle of the parent driver. The only recommended use for this | |
2151 | is to find the name of the driver using: | |
2152 | .Sp | |
2153 | .Vb 1 | |
2154 | \& $dbh->{Driver}->{Name} | |
2155 | .Ve | |
2156 | .ie n .IP """Name"" (string)" 4 | |
2157 | .el .IP "\f(CWName\fR (string)" 4 | |
2158 | .IX Item "Name (string)" | |
2159 | Holds the \*(L"name\*(R" of the database. Usually (and recommended to be) the | |
2160 | same as the "\f(CW\*(C`dbi:DriverName:...\*(C'\fR\*(L" string used to connect to the database, | |
2161 | but with the leading \*(R"\f(CW\*(C`dbi:DriverName:\*(C'\fR" removed. | |
2162 | .ie n .IP """Statement"" (string, read\-only)" 4 | |
2163 | .el .IP "\f(CWStatement\fR (string, read\-only)" 4 | |
2164 | .IX Item "Statement (string, read-only)" | |
2165 | Returns the statement string passed to the most recent \*(L"prepare\*(R" method | |
2166 | called in this database handle, even if that method failed. This is especially | |
2167 | useful where \f(CW\*(C`RaiseError\*(C'\fR is enabled and the exception handler checks $@ | |
2168 | and sees that a 'prepare' method call failed. | |
2169 | .ie n .IP """RowCacheSize"" (integer)" 4 | |
2170 | .el .IP "\f(CWRowCacheSize\fR (integer)" 4 | |
2171 | .IX Item "RowCacheSize (integer)" | |
2172 | A hint to the driver indicating the size of the local row cache that the | |
2173 | application would like the driver to use for future \f(CW\*(C`SELECT\*(C'\fR statements. | |
2174 | If a row cache is not implemented, then setting \f(CW\*(C`RowCacheSize\*(C'\fR is ignored | |
2175 | and getting the value returns \f(CW\*(C`undef\*(C'\fR. | |
2176 | .Sp | |
2177 | Some \f(CW\*(C`RowCacheSize\*(C'\fR values have special meaning, as follows: | |
2178 | .Sp | |
2179 | .Vb 4 | |
2180 | \& 0 - Automatically determine a reasonable cache size for each C<SELECT> | |
2181 | \& 1 - Disable the local row cache | |
2182 | \& >1 - Cache this many rows | |
2183 | \& <0 - Cache as many rows that will fit into this much memory for each C<SELECT>. | |
2184 | .Ve | |
2185 | .Sp | |
2186 | Note that large cache sizes may require a very large amount of memory | |
2187 | (\fIcached rows * maximum size of row\fR). Also, a large cache will cause | |
2188 | a longer delay not only for the first fetch, but also whenever the | |
2189 | cache needs refilling. | |
2190 | .Sp | |
2191 | See also the \*(L"RowsInCache\*(R" statement handle attribute. | |
2192 | .SH "DBI STATEMENT HANDLE OBJECTS" | |
2193 | .IX Header "DBI STATEMENT HANDLE OBJECTS" | |
2194 | This section lists the methods and attributes associated with \s-1DBI\s0 | |
2195 | statement handles. | |
2196 | .Sh "Statement Handle Methods" | |
2197 | .IX Subsection "Statement Handle Methods" | |
2198 | The \s-1DBI\s0 defines the following methods for use on \s-1DBI\s0 statement handles: | |
2199 | .ie n .IP """bind_param""" 4 | |
2200 | .el .IP "\f(CWbind_param\fR" 4 | |
2201 | .IX Item "bind_param" | |
2202 | .Vb 3 | |
2203 | \& $rc = $sth->bind_param($p_num, $bind_value) or die $sth->errstr; | |
2204 | \& $rv = $sth->bind_param($p_num, $bind_value, \e%attr) or ... | |
2205 | \& $rv = $sth->bind_param($p_num, $bind_value, $bind_type) or ... | |
2206 | .Ve | |
2207 | .Sp | |
2208 | The \f(CW\*(C`bind_param\*(C'\fR method can be used to bind a value | |
2209 | with a placeholder embedded in the prepared statement. Placeholders | |
2210 | are indicated with question mark character (\f(CW\*(C`?\*(C'\fR). For example: | |
2211 | .Sp | |
2212 | .Vb 5 | |
2213 | \& $dbh->{RaiseError} = 1; # save having to check each method call | |
2214 | \& $sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE ?"); | |
2215 | \& $sth->bind_param(1, "John%"); # placeholders are numbered from 1 | |
2216 | \& $sth->execute; | |
2217 | \& DBI::dump_results($sth); | |
2218 | .Ve | |
2219 | .Sp | |
2220 | Note that the \f(CW\*(C`?\*(C'\fR is not enclosed in quotation marks, even when the | |
2221 | placeholder represents a string. Some drivers also allow placeholders | |
2222 | like \f(CW\*(C`:\*(C'\fR\fIname\fR and \f(CW\*(C`:\*(C'\fR\fIn\fR (e.g., \f(CW\*(C`:1\*(C'\fR, \f(CW\*(C`:2\*(C'\fR, and so on) | |
2223 | in addition to \f(CW\*(C`?\*(C'\fR, but their use | |
2224 | is not portable. Undefined bind values or \f(CW\*(C`undef\*(C'\fR can be used to | |
2225 | indicate null values. | |
2226 | .Sp | |
2227 | Some drivers do not support placeholders. | |
2228 | .Sp | |
2229 | With most drivers, placeholders can't be used for any element of a | |
2230 | statement that would prevent the database server from validating the | |
2231 | statement and creating a query execution plan for it. For example: | |
2232 | .Sp | |
2233 | .Vb 2 | |
2234 | \& "SELECT name, age FROM ?" # wrong (will probably fail) | |
2235 | \& "SELECT name, ? FROM people" # wrong (but may not 'fail') | |
2236 | .Ve | |
2237 | .Sp | |
2238 | Also, placeholders can only represent single scalar values. | |
2239 | For example, the following | |
2240 | statement won't work as expected for more than one value: | |
2241 | .Sp | |
2242 | .Vb 1 | |
2243 | \& "SELECT name, age FROM people WHERE name IN (?)" # wrong | |
2244 | .Ve | |
2245 | .Sp | |
2246 | \&\fBData Types for Placeholders\fR | |
2247 | .Sp | |
2248 | The \f(CW\*(C`\e%attr\*(C'\fR parameter can be used to hint at the data type the | |
2249 | placeholder should have. Typically, the driver is only interested in | |
2250 | knowing if the placeholder should be bound as a number or a string. | |
2251 | .Sp | |
2252 | .Vb 1 | |
2253 | \& $sth->bind_param(1, $value, { TYPE => SQL_INTEGER }); | |
2254 | .Ve | |
2255 | .Sp | |
2256 | As a short-cut for this common case, the data type can be passed | |
2257 | directly, in place of the \f(CW\*(C`\e%attr\*(C'\fR hash reference. This example is | |
2258 | equivalent to the one above: | |
2259 | .Sp | |
2260 | .Vb 1 | |
2261 | \& $sth->bind_param(1, $value, SQL_INTEGER); | |
2262 | .Ve | |
2263 | .Sp | |
2264 | The \f(CW\*(C`TYPE\*(C'\fR value indicates the standard (non\-driver\-specific) type for | |
2265 | this parameter. To specify the driver-specific type, the driver may | |
2266 | support a driver-specific attribute, such as \f(CW\*(C`{ ora_type =\*(C'\fR>\f(CW\*(C` 97 }\*(C'\fR. The | |
2267 | data type for a placeholder cannot be changed after the first | |
2268 | \&\f(CW\*(C`bind_param\*(C'\fR call. However, it can be left unspecified, in which case it | |
2269 | defaults to the previous value. | |
2270 | .Sp | |
2271 | The \s-1SQL_INTEGER\s0 and other related constants can be imported using | |
2272 | .Sp | |
2273 | .Vb 1 | |
2274 | \& use DBI qw(:sql_types); | |
2275 | .Ve | |
2276 | .Sp | |
2277 | See \*(L"\s-1DBI\s0 Constants\*(R" for more information. | |
2278 | .Sp | |
2279 | Perl only has string and number scalar data types. All database types | |
2280 | that aren't numbers are bound as strings and must be in a format the | |
2281 | database will understand. | |
2282 | .Sp | |
2283 | As an alternative to specifying the data type in the \f(CW\*(C`bind_param\*(C'\fR call, | |
2284 | you can let the driver pass the value as the default type (\f(CW\*(C`VARCHAR\*(C'\fR). | |
2285 | You can then use an \s-1SQL\s0 function to convert the type within the statement. | |
2286 | For example: | |
2287 | .Sp | |
2288 | .Vb 1 | |
2289 | \& INSERT INTO price(code, price) VALUES (?, CONVERT(MONEY,?)) | |
2290 | .Ve | |
2291 | .Sp | |
2292 | The \f(CW\*(C`CONVERT\*(C'\fR function used here is just an example. The actual function | |
2293 | and syntax will vary between different databases and is non\-portable. | |
2294 | .Sp | |
2295 | See also \*(L"Placeholders and Bind Values\*(R" for more information. | |
2296 | .ie n .IP """bind_param_inout""" 4 | |
2297 | .el .IP "\f(CWbind_param_inout\fR" 4 | |
2298 | .IX Item "bind_param_inout" | |
2299 | .Vb 3 | |
2300 | \& $rc = $sth->bind_param_inout($p_num, \e$bind_value, $max_len) or die $sth->errstr; | |
2301 | \& $rv = $sth->bind_param_inout($p_num, \e$bind_value, $max_len, \e%attr) or ... | |
2302 | \& $rv = $sth->bind_param_inout($p_num, \e$bind_value, $max_len, $bind_type) or ... | |
2303 | .Ve | |
2304 | .Sp | |
2305 | This method acts like \*(L"bind_param\*(R", but also enables values to be | |
2306 | updated by the statement. The statement is typically | |
2307 | a call to a stored procedure. The \f(CW$bind_value\fR must be passed as a | |
2308 | reference to the actual value to be used. | |
2309 | .Sp | |
2310 | Note that unlike \*(L"bind_param\*(R", the \f(CW$bind_value\fR variable is not | |
2311 | read when \f(CW\*(C`bind_param_inout\*(C'\fR is called. Instead, the value in the | |
2312 | variable is read at the time \*(L"execute\*(R" is called. | |
2313 | .Sp | |
2314 | The additional \f(CW$max_len\fR parameter specifies the minimum amount of | |
2315 | memory to allocate to \f(CW$bind_value\fR for the new value. If the value | |
2316 | returned from the database is too | |
2317 | big to fit, then the execution should fail. If unsure what value to use, | |
2318 | pick a generous length, i.e., a length larger than the longest value that would ever be | |
2319 | returned. The only cost of using a larger value than needed is wasted memory. | |
2320 | .Sp | |
2321 | It is expected that few drivers will support this method. The only | |
2322 | driver currently known to do so is DBD::Oracle (\s-1DBD::ODBC\s0 may support | |
2323 | it in a future release). Therefore it should not be used for database | |
2324 | independent applications. | |
2325 | .Sp | |
2326 | Undefined values or \f(CW\*(C`undef\*(C'\fR are used to indicate null values. | |
2327 | See also \*(L"Placeholders and Bind Values\*(R" for more information. | |
2328 | .ie n .IP """execute""" 4 | |
2329 | .el .IP "\f(CWexecute\fR" 4 | |
2330 | .IX Item "execute" | |
2331 | .Vb 2 | |
2332 | \& $rv = $sth->execute or die $sth->errstr; | |
2333 | \& $rv = $sth->execute(@bind_values) or die $sth->errstr; | |
2334 | .Ve | |
2335 | .Sp | |
2336 | Perform whatever processing is necessary to execute the prepared | |
2337 | statement. An \f(CW\*(C`undef\*(C'\fR is returned if an error occurs. A successful | |
2338 | \&\f(CW\*(C`execute\*(C'\fR always returns true regardless of the number of rows affected, | |
2339 | even if it's zero (see below). It is always important to check the | |
2340 | return status of \f(CW\*(C`execute\*(C'\fR (and most other \s-1DBI\s0 methods) for errors. | |
2341 | .Sp | |
2342 | For a \fInon\fR\-\f(CW\*(C`SELECT\*(C'\fR statement, \f(CW\*(C`execute\*(C'\fR returns the number of rows | |
2343 | affected, if known. If no rows were affected, then \f(CW\*(C`execute\*(C'\fR returns | |
2344 | "\f(CW0E0\fR", which Perl will treat as 0 but will regard as true. Note that it | |
2345 | is \fInot\fR an error for no rows to be affected by a statement. If the | |
2346 | number of rows affected is not known, then \f(CW\*(C`execute\*(C'\fR returns \-1. | |
2347 | .Sp | |
2348 | For \f(CW\*(C`SELECT\*(C'\fR statements, execute simply \*(L"starts\*(R" the query within the | |
2349 | database engine. Use one of the fetch methods to retreive the data after | |
2350 | calling \f(CW\*(C`execute\*(C'\fR. The \f(CW\*(C`execute\*(C'\fR method does \fInot\fR return the number of | |
2351 | rows that will be returned by the query (because most databases can't | |
2352 | tell in advance), it simply returns a true value. | |
2353 | .Sp | |
2354 | If any arguments are given, then \f(CW\*(C`execute\*(C'\fR will effectively call | |
2355 | \&\*(L"bind_param\*(R" for each value before executing the statement. | |
2356 | Values bound in this way are usually treated as \f(CW\*(C`SQL_VARCHAR\*(C'\fR types | |
2357 | unless the driver can determine the correct type (which is rare), or | |
2358 | unless | |
2359 | \&\f(CW\*(C`bind_param\*(C'\fR (or \f(CW\*(C`bind_param_inout\*(C'\fR) has already been used to specify the | |
2360 | type. | |
2361 | .ie n .IP """fetchrow_arrayref""" 4 | |
2362 | .el .IP "\f(CWfetchrow_arrayref\fR" 4 | |
2363 | .IX Item "fetchrow_arrayref" | |
2364 | .Vb 2 | |
2365 | \& $ary_ref = $sth->fetchrow_arrayref; | |
2366 | \& $ary_ref = $sth->fetch; # alias | |
2367 | .Ve | |
2368 | .Sp | |
2369 | Fetches the next row of data and returns a reference to an array | |
2370 | holding the field values. Null fields are returned as \f(CW\*(C`undef\*(C'\fR | |
2371 | values in the array. | |
2372 | This is the fastest way to fetch data, particularly if used with | |
2373 | \&\f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`bind_columns\*(C'\fR. | |
2374 | .Sp | |
2375 | If there are no more rows or if an error occurs, then \f(CW\*(C`fetchrow_arrayref\*(C'\fR | |
2376 | returns an \f(CW\*(C`undef\*(C'\fR. You should check \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`err\*(C'\fR afterwards (or use the | |
2377 | \&\f(CW\*(C`RaiseError\*(C'\fR attribute) to discover if the \f(CW\*(C`undef\*(C'\fR returned was due to an | |
2378 | error. | |
2379 | .Sp | |
2380 | Note that the same array reference is returned for each fetch, so don't | |
2381 | store the reference and then use it after a later fetch. Also, the | |
2382 | elements of the array are also reused for each row, so take care if you | |
2383 | want to take a reference to an element. See also \*(L"bind_columns\*(R". | |
2384 | .ie n .IP """fetchrow_array""" 4 | |
2385 | .el .IP "\f(CWfetchrow_array\fR" 4 | |
2386 | .IX Item "fetchrow_array" | |
2387 | .Vb 1 | |
2388 | \& @ary = $sth->fetchrow_array; | |
2389 | .Ve | |
2390 | .Sp | |
2391 | An alternative to \f(CW\*(C`fetchrow_arrayref\*(C'\fR. Fetches the next row of data | |
2392 | and returns it as a list containing the field values. Null fields | |
2393 | are returned as \f(CW\*(C`undef\*(C'\fR values in the list. | |
2394 | .Sp | |
2395 | If there are no more rows or if an error occurs, then \f(CW\*(C`fetchrow_array\*(C'\fR | |
2396 | returns an empty list. You should check \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`err\*(C'\fR afterwards (or use | |
2397 | the \f(CW\*(C`RaiseError\*(C'\fR attribute) to discover if the empty list returned was | |
2398 | due to an error. | |
2399 | .Sp | |
2400 | In a scalar context, \f(CW\*(C`fetchrow_array\*(C'\fR returns the value of the first | |
2401 | field. An \f(CW\*(C`undef\*(C'\fR is returned if there are no more rows or if an error | |
2402 | occurred. Since that \f(CW\*(C`undef\*(C'\fR can't be distinguished from an \f(CW\*(C`undef\*(C'\fR returned | |
2403 | because the first field value was \s-1NULL\s0, you should exercise some | |
2404 | caution if you use \f(CW\*(C`fetchrow_array\*(C'\fR in a scalar context. | |
2405 | .ie n .IP """fetchrow_hashref""" 4 | |
2406 | .el .IP "\f(CWfetchrow_hashref\fR" 4 | |
2407 | .IX Item "fetchrow_hashref" | |
2408 | .Vb 2 | |
2409 | \& $hash_ref = $sth->fetchrow_hashref; | |
2410 | \& $hash_ref = $sth->fetchrow_hashref($name); | |
2411 | .Ve | |
2412 | .Sp | |
2413 | An alternative to \f(CW\*(C`fetchrow_arrayref\*(C'\fR. Fetches the next row of data | |
2414 | and returns it as a reference to a hash containing field name and field | |
2415 | value pairs. Null fields are returned as \f(CW\*(C`undef\*(C'\fR values in the hash. | |
2416 | .Sp | |
2417 | If there are no more rows or if an error occurs, then \f(CW\*(C`fetchrow_hashref\*(C'\fR | |
2418 | returns an \f(CW\*(C`undef\*(C'\fR. You should check \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`err\*(C'\fR afterwards (or use the | |
2419 | \&\f(CW\*(C`RaiseError\*(C'\fR attribute) to discover if the \f(CW\*(C`undef\*(C'\fR returned was due to an | |
2420 | error. | |
2421 | .Sp | |
2422 | The optional \f(CW$name\fR parameter specifies the name of the statement handle | |
2423 | attribute. For historical reasons it defaults to "\f(CW\*(C`NAME\*(C'\fR\*(L", however using either | |
2424 | \&\*(R"\f(CW\*(C`NAME_lc\*(C'\fR\*(L" or \*(R"\f(CW\*(C`NAME_uc\*(C'\fR" is recomended for portability. | |
2425 | .Sp | |
2426 | The keys of the hash are the same names returned by \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`{$name}\*(C'\fR. If | |
2427 | more than one field has the same name, there will only be one entry in | |
2428 | the returned hash for those fields. | |
2429 | .Sp | |
2430 | Because of the extra work \f(CW\*(C`fetchrow_hashref\*(C'\fR and Perl have to perform, it | |
2431 | is not as efficient as \f(CW\*(C`fetchrow_arrayref\*(C'\fR or \f(CW\*(C`fetchrow_array\*(C'\fR. | |
2432 | .Sp | |
2433 | Currently, a new hash reference is returned for each row. \fIThis will | |
2434 | change\fR in the future to return the same hash ref each time, so don't | |
2435 | rely on the current behaviour. | |
2436 | .ie n .IP """fetchall_arrayref""" 4 | |
2437 | .el .IP "\f(CWfetchall_arrayref\fR" 4 | |
2438 | .IX Item "fetchall_arrayref" | |
2439 | .Vb 3 | |
2440 | \& $tbl_ary_ref = $sth->fetchall_arrayref; | |
2441 | \& $tbl_ary_ref = $sth->fetchall_arrayref( $slice_array_ref ); | |
2442 | \& $tbl_ary_ref = $sth->fetchall_arrayref( $slice_hash_ref ); | |
2443 | .Ve | |
2444 | .Sp | |
2445 | The \f(CW\*(C`fetchall_arrayref\*(C'\fR method can be used to fetch all the data to be | |
2446 | returned from a prepared and executed statement handle. It returns a | |
2447 | reference to an array that contains one reference per row. | |
2448 | .Sp | |
2449 | If there are no rows to return, \f(CW\*(C`fetchall_arrayref\*(C'\fR returns a reference | |
2450 | to an empty array. If an error occurs, \f(CW\*(C`fetchall_arrayref\*(C'\fR returns the | |
2451 | data fetched thus far, which may be none. You should check \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`err\*(C'\fR | |
2452 | afterwards (or use the \f(CW\*(C`RaiseError\*(C'\fR attribute) to discover if the data is | |
2453 | complete or was truncated due to an error. | |
2454 | .Sp | |
2455 | When passed an array reference, \f(CW\*(C`fetchall_arrayref\*(C'\fR uses \*(L"fetchrow_arrayref\*(R" | |
2456 | to fetch each row as an array ref. If the parameter array is not empty | |
2457 | then it is used as a slice to select individual columns by index number. | |
2458 | .Sp | |
2459 | With no parameters, \f(CW\*(C`fetchall_arrayref\*(C'\fR acts as if passed an empty array ref. | |
2460 | .Sp | |
2461 | When passed a hash reference, \f(CW\*(C`fetchall_arrayref\*(C'\fR uses \*(L"fetchrow_hashref\*(R" | |
2462 | to fetch each row as a hash reference. If the parameter hash is empty then | |
2463 | fetchrow_hashref is simply called in a tight loop and the keys in the hashes | |
2464 | have whatever name lettercase is returned by default from fetchrow_hashref. | |
2465 | .Sp | |
2466 | If the parameter hash is not empty, then it is used as a slice to | |
2467 | select individual columns by name. The names should be lower case | |
2468 | regardless of the letter case in \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`{NAME}\*(C'\fR. The values of | |
2469 | the hash should be set to 1. | |
2470 | .Sp | |
2471 | For example, to fetch just the first column of every row: | |
2472 | .Sp | |
2473 | .Vb 1 | |
2474 | \& $tbl_ary_ref = $sth->fetchall_arrayref([0]); | |
2475 | .Ve | |
2476 | .Sp | |
2477 | To fetch the second to last and last column of every row: | |
2478 | .Sp | |
2479 | .Vb 1 | |
2480 | \& $tbl_ary_ref = $sth->fetchall_arrayref([-2,-1]); | |
2481 | .Ve | |
2482 | .Sp | |
2483 | To fetch all fields of every row as a hash ref: | |
2484 | .Sp | |
2485 | .Vb 1 | |
2486 | \& $tbl_ary_ref = $sth->fetchall_arrayref({}); | |
2487 | .Ve | |
2488 | .Sp | |
2489 | To fetch only the fields called \*(L"foo\*(R" and \*(L"bar\*(R" of every row as a hash ref: | |
2490 | .Sp | |
2491 | .Vb 1 | |
2492 | \& $tbl_ary_ref = $sth->fetchall_arrayref({ foo=>1, bar=>1 }); | |
2493 | .Ve | |
2494 | .Sp | |
2495 | The first two examples return a reference to an array of array refs. The last | |
2496 | returns a reference to an array of hash refs. | |
2497 | .ie n .IP """fetchall_hashref""" 4 | |
2498 | .el .IP "\f(CWfetchall_hashref\fR" 4 | |
2499 | .IX Item "fetchall_hashref" | |
2500 | .Vb 1 | |
2501 | \& $tbl_ary_ref = $sth->fetchall_hashref; | |
2502 | .Ve | |
2503 | .Sp | |
2504 | The \f(CW\*(C`fetchall_hashref\*(C'\fR method can be used to fetch all the data to be | |
2505 | returned from a prepared and executed statement handle. It returns a | |
2506 | reference to an array that contains one hash of field name and value | |
2507 | pairs per row. | |
2508 | .Sp | |
2509 | If there are no rows to return, \f(CW\*(C`fetchall_hashref\*(C'\fR returns a reference | |
2510 | to an empty array. If an error occurs, \f(CW\*(C`fetchall_hashref\*(C'\fR returns the | |
2511 | data fetched thus far, which may be none. You should check \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`err\*(C'\fR | |
2512 | afterwards (or use the \f(CW\*(C`RaiseError\*(C'\fR attribute) to discover if the data is | |
2513 | complete or was truncated due to an error. | |
2514 | .ie n .IP """finish""" 4 | |
2515 | .el .IP "\f(CWfinish\fR" 4 | |
2516 | .IX Item "finish" | |
2517 | .Vb 1 | |
2518 | \& $rc = $sth->finish; | |
2519 | .Ve | |
2520 | .Sp | |
2521 | Indicates that no more data will be fetched from this statement handle | |
2522 | before it is either executed again or destroyed. The \f(CW\*(C`finish\*(C'\fR method | |
2523 | is rarely needed, but can sometimes be helpful in very specific | |
2524 | situations to allow the server to free up resources (such as sort | |
2525 | buffers). | |
2526 | .Sp | |
2527 | When all the data has been fetched from a \f(CW\*(C`SELECT\*(C'\fR statement, the driver | |
2528 | should automatically call \f(CW\*(C`finish\*(C'\fR for you. So you should not normally | |
2529 | need to call it explicitly. | |
2530 | .Sp | |
2531 | Consider a query like: | |
2532 | .Sp | |
2533 | .Vb 1 | |
2534 | \& SELECT foo FROM table WHERE bar=? ORDER BY foo | |
2535 | .Ve | |
2536 | .Sp | |
2537 | where you want to select just the first (smallest) \*(L"foo\*(R" value from a | |
2538 | very large table. When executed, the database server will have to use | |
2539 | temporary buffer space to store the sorted rows. If, after executing | |
2540 | the handle and selecting one row, the handle won't be re-executed for | |
2541 | some time and won't be destroyed, the \f(CW\*(C`finish\*(C'\fR method can be used to tell | |
2542 | the server that the buffer space can be freed. | |
2543 | .Sp | |
2544 | Calling \f(CW\*(C`finish\*(C'\fR resets the \*(L"Active\*(R" attribute for the statement. It | |
2545 | may also make some statement handle attributes (such as \f(CW\*(C`NAME\*(C'\fR and \f(CW\*(C`TYPE\*(C'\fR) | |
2546 | unavailable if they have not already been accessed (and thus cached). | |
2547 | .Sp | |
2548 | The \f(CW\*(C`finish\*(C'\fR method does not affect the transaction status of the | |
2549 | database connection. It has nothing to do with transactions. It's mostly an | |
2550 | internal \*(L"housekeeping\*(R" method that is rarely needed. There's no need | |
2551 | to call \f(CW\*(C`finish\*(C'\fR if you're about to destroy or re-execute the statement | |
2552 | handle. See also \*(L"disconnect\*(R" and the \*(L"Active\*(R" attribute. | |
2553 | .Sp | |
2554 | The \f(CW\*(C`finish\*(C'\fR method should have been called \f(CW\*(C`cancel_select\*(C'\fR. | |
2555 | .ie n .IP """rows""" 4 | |
2556 | .el .IP "\f(CWrows\fR" 4 | |
2557 | .IX Item "rows" | |
2558 | .Vb 1 | |
2559 | \& $rv = $sth->rows; | |
2560 | .Ve | |
2561 | .Sp | |
2562 | Returns the number of rows affected by the last row affecting command, | |
2563 | or \-1 if the number of rows is not known or not available. | |
2564 | .Sp | |
2565 | Generally, you can only rely on a row count after a \fInon\fR\-\f(CW\*(C`SELECT\*(C'\fR | |
2566 | \&\f(CW\*(C`execute\*(C'\fR (for some specific operations like \f(CW\*(C`UPDATE\*(C'\fR and \f(CW\*(C`DELETE\*(C'\fR), or | |
2567 | after fetching all the rows of a \f(CW\*(C`SELECT\*(C'\fR statement. | |
2568 | .Sp | |
2569 | For \f(CW\*(C`SELECT\*(C'\fR statements, it is generally not possible to know how many | |
2570 | rows will be returned except by fetching them all. Some drivers will | |
2571 | return the number of rows the application has fetched so far, but | |
2572 | others may return \-1 until all rows have been fetched. So use of the | |
2573 | \&\f(CW\*(C`rows\*(C'\fR method or \f(CW$DBI::rows\fR with \f(CW\*(C`SELECT\*(C'\fR statements is not | |
2574 | recommended. | |
2575 | .Sp | |
2576 | One alternative method to get a row count for a \f(CW\*(C`SELECT\*(C'\fR is to execute a | |
2577 | \&\*(L"\s-1SELECT\s0 \s-1COUNT\s0(*) \s-1FROM\s0 ...\*(R" \s-1SQL\s0 statement with the same \*(L"...\*(R" as your | |
2578 | query and then fetch the row count from that. | |
2579 | .ie n .IP """bind_col""" 4 | |
2580 | .el .IP "\f(CWbind_col\fR" 4 | |
2581 | .IX Item "bind_col" | |
2582 | .Vb 1 | |
2583 | \& $rc = $sth->bind_col($column_number, \e$var_to_bind); | |
2584 | .Ve | |
2585 | .Sp | |
2586 | Binds an output column (field) of a \f(CW\*(C`SELECT\*(C'\fR statement to a Perl variable. | |
2587 | See \f(CW\*(C`bind_columns\*(C'\fR below for an example. Note that column numbers count | |
2588 | up from 1. | |
2589 | .Sp | |
2590 | Whenever a row is fetched from the database, the corresponding Perl | |
2591 | variable is automatically updated. There is no need to fetch and assign | |
2592 | the values manually. The binding is performed at a very low level | |
2593 | using Perl aliasing so there is no extra copying taking place. This | |
2594 | makes using bound variables very efficient. | |
2595 | .Sp | |
2596 | For maximum portability between drivers, \f(CW\*(C`bind_col\*(C'\fR should be called after | |
2597 | \&\f(CW\*(C`execute\*(C'\fR. This restriction may be removed in a later version of the \s-1DBI\s0. | |
2598 | .Sp | |
2599 | You do not need to bind output columns in order to fetch data, but it | |
2600 | can be useful for some applications which need either maximum performance | |
2601 | or greater clarity of code. The \*(L"bind_param\*(R" method | |
2602 | performs a similar but opposite function for input variables. | |
2603 | .ie n .IP """bind_columns""" 4 | |
2604 | .el .IP "\f(CWbind_columns\fR" 4 | |
2605 | .IX Item "bind_columns" | |
2606 | .Vb 1 | |
2607 | \& $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind); | |
2608 | .Ve | |
2609 | .Sp | |
2610 | Calls \*(L"bind_col\*(R" for each column of the \f(CW\*(C`SELECT\*(C'\fR statement. | |
2611 | The \f(CW\*(C`bind_columns\*(C'\fR method will die if the number of references does not | |
2612 | match the number of fields. | |
2613 | .Sp | |
2614 | For maximum portability between drivers, \f(CW\*(C`bind_columns\*(C'\fR should be called | |
2615 | after \f(CW\*(C`execute\*(C'\fR. | |
2616 | .Sp | |
2617 | For example: | |
2618 | .Sp | |
2619 | .Vb 4 | |
2620 | \& $dbh->{RaiseError} = 1; # do this, or check every call for errors | |
2621 | \& $sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region }); | |
2622 | \& $sth->execute; | |
2623 | \& my ($region, $sales); | |
2624 | .Ve | |
2625 | .Sp | |
2626 | .Vb 2 | |
2627 | \& # Bind Perl variables to columns: | |
2628 | \& $rv = $sth->bind_columns(\e$region, \e$sales); | |
2629 | .Ve | |
2630 | .Sp | |
2631 | .Vb 2 | |
2632 | \& # you can also use Perl's \e(...) syntax (see perlref docs): | |
2633 | \& # $sth->bind_columns(\e($region, $sales)); | |
2634 | .Ve | |
2635 | .Sp | |
2636 | .Vb 4 | |
2637 | \& # Column binding is the most efficient way to fetch data | |
2638 | \& while ($sth->fetch) { | |
2639 | \& print "$region: $sales\en"; | |
2640 | \& } | |
2641 | .Ve | |
2642 | .Sp | |
2643 | For compatibility with old scripts, the first parameter will be | |
2644 | ignored if it is \f(CW\*(C`undef\*(C'\fR or a hash reference. | |
2645 | .Sp | |
2646 | Here's a more fancy example that binds columns to the values \fIinside\fR | |
2647 | a hash (thanks to H.Merijn Brand): | |
2648 | .Sp | |
2649 | .Vb 6 | |
2650 | \& $sth->execute; | |
2651 | \& my %row; | |
2652 | \& $sth->bind_columns( \e( @row{ @{$sth->{NAME_lc} } } )); | |
2653 | \& while ($sth->fetch) { | |
2654 | \& print "$row{region}: $row{sales}\en"; | |
2655 | \& } | |
2656 | .Ve | |
2657 | .ie n .IP """dump_results""" 4 | |
2658 | .el .IP "\f(CWdump_results\fR" 4 | |
2659 | .IX Item "dump_results" | |
2660 | .Vb 1 | |
2661 | \& $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh); | |
2662 | .Ve | |
2663 | .Sp | |
2664 | Fetches all the rows from \f(CW$sth\fR, calls \f(CW\*(C`DBI::neat_list\*(C'\fR for each row, and | |
2665 | prints the results to \f(CW$fh\fR (defaults to \f(CW\*(C`STDOUT\*(C'\fR) separated by \f(CW$lsep\fR | |
2666 | (default \f(CW"\en"\fR). \f(CW$fsep\fR defaults to \f(CW", "\fR and \f(CW$maxlen\fR defaults to 35. | |
2667 | .Sp | |
2668 | This method is designed as a handy utility for prototyping and | |
2669 | testing queries. Since it uses \*(L"neat_list\*(R" to | |
2670 | format and edit the string for reading by humans, it is not recomended | |
2671 | for data transfer applications. | |
2672 | .Sh "Statement Handle Attributes" | |
2673 | .IX Subsection "Statement Handle Attributes" | |
2674 | This section describes attributes specific to statement handles. Most | |
2675 | of these attributes are read\-only. | |
2676 | .PP | |
2677 | Changes to these statement handle attributes do not affect any other | |
2678 | existing or future statement handles. | |
2679 | .PP | |
2680 | Attempting to set or get the value of an unknown attribute is fatal, | |
2681 | except for private driver specific attributes (which all have names | |
2682 | starting with a lowercase letter). | |
2683 | .PP | |
2684 | Example: | |
2685 | .PP | |
2686 | .Vb 1 | |
2687 | \& ... = $h->{NUM_OF_FIELDS}; # get/read | |
2688 | .Ve | |
2689 | .PP | |
2690 | Note that some drivers cannot provide valid values for some or all of | |
2691 | these attributes until after \f(CW\*(C`$sth\-\*(C'\fR>\f(CW\*(C`execute\*(C'\fR has been called. | |
2692 | .PP | |
2693 | See also \*(L"finish\*(R" to learn more about the effect it | |
2694 | may have on some attributes. | |
2695 | .ie n .IP """NUM_OF_FIELDS"" (integer, read\-only)" 4 | |
2696 | .el .IP "\f(CWNUM_OF_FIELDS\fR (integer, read\-only)" 4 | |
2697 | .IX Item "NUM_OF_FIELDS (integer, read-only)" | |
2698 | Number of fields (columns) the prepared statement will return. Non\-\f(CW\*(C`SELECT\*(C'\fR | |
2699 | statements will have \f(CW\*(C`NUM_OF_FIELDS == 0\*(C'\fR. | |
2700 | .ie n .IP """NUM_OF_PARAMS"" (integer, read\-only)" 4 | |
2701 | .el .IP "\f(CWNUM_OF_PARAMS\fR (integer, read\-only)" 4 | |
2702 | .IX Item "NUM_OF_PARAMS (integer, read-only)" | |
2703 | The number of parameters (placeholders) in the prepared statement. | |
2704 | See \s-1SUBSTITUTION\s0 \s-1VARIABLES\s0 below for more details. | |
2705 | .ie n .IP """NAME"" (array\-ref, read\-only)" 4 | |
2706 | .el .IP "\f(CWNAME\fR (array\-ref, read\-only)" 4 | |
2707 | .IX Item "NAME (array-ref, read-only)" | |
2708 | Returns a reference to an array of field names for each column. The | |
2709 | names may contain spaces but should not be truncated or have any | |
2710 | trailing space. Note that the names have the letter case (upper, lower | |
2711 | or mixed) as returned by the driver being used. Portable applications | |
2712 | should use \*(L"NAME_lc\*(R" or \*(L"NAME_uc\*(R". | |
2713 | .Sp | |
2714 | .Vb 1 | |
2715 | \& print "First column name: $sth->{NAME}->[0]\en"; | |
2716 | .Ve | |
2717 | .ie n .IP """NAME_lc"" (array\-ref, read\-only)" 4 | |
2718 | .el .IP "\f(CWNAME_lc\fR (array\-ref, read\-only)" 4 | |
2719 | .IX Item "NAME_lc (array-ref, read-only)" | |
2720 | Like \*(L"\s-1NAME\s0\*(R" but always returns lowercase names. | |
2721 | .ie n .IP """NAME_uc"" (array\-ref, read\-only)" 4 | |
2722 | .el .IP "\f(CWNAME_uc\fR (array\-ref, read\-only)" 4 | |
2723 | .IX Item "NAME_uc (array-ref, read-only)" | |
2724 | Like \*(L"\s-1NAME\s0\*(R" but always returns uppercase names. | |
2725 | .ie n .IP """TYPE"" (array\-ref, read\-only)" 4 | |
2726 | .el .IP "\f(CWTYPE\fR (array\-ref, read\-only)" 4 | |
2727 | .IX Item "TYPE (array-ref, read-only)" | |
2728 | Returns a reference to an array of integer values for each | |
2729 | column. The value indicates the data type of the corresponding column. | |
2730 | .Sp | |
2731 | The values correspond to the international standards (\s-1ANSI\s0 X3.135 | |
2732 | and \s-1ISO/IEC\s0 9075) which, in general terms, means \s-1ODBC\s0. Driver-specific | |
2733 | types that don't exactly match standard types should generally return | |
2734 | the same values as an \s-1ODBC\s0 driver supplied by the makers of the | |
2735 | database. That might include private type numbers in ranges the vendor | |
2736 | has officially registered with the \s-1ISO\s0 working group: | |
2737 | .Sp | |
2738 | .Vb 1 | |
2739 | \& ftp://jerry.ece.umassd.edu/isowg3/dbl/SQL_Registry | |
2740 | .Ve | |
2741 | .Sp | |
2742 | Where there's no vendor-supplied \s-1ODBC\s0 driver to be compatible with, the | |
2743 | \&\s-1DBI\s0 driver can use type numbers in the range that is now officially reserved | |
2744 | for use by the \s-1DBI:\s0 \-9999 to \-9000. | |
2745 | .Sp | |
2746 | All possible values for \f(CW\*(C`TYPE\*(C'\fR should have at least one entry in the | |
2747 | output of the \f(CW\*(C`type_info_all\*(C'\fR method (see \*(L"type_info_all\*(R"). | |
2748 | .ie n .IP """PRECISION"" (array\-ref, read\-only)" 4 | |
2749 | .el .IP "\f(CWPRECISION\fR (array\-ref, read\-only)" 4 | |
2750 | .IX Item "PRECISION (array-ref, read-only)" | |
2751 | Returns a reference to an array of integer values for each | |
2752 | column. For non-numeric columns, the value generally refers to either | |
2753 | the maximum length or the defined length of the column. For numeric | |
2754 | columns, the value refers to the maximum number of significant digits | |
2755 | used by the data type (without considering a sign character or decimal | |
2756 | point). Note that for floating point types (\s-1REAL\s0, \s-1FLOAT\s0, \s-1DOUBLE\s0), the | |
2757 | \&\*(L"display size\*(R" can be up to 7 characters greater than the precision. | |
2758 | (for the sign + decimal point + the letter E + a sign + 2 or 3 digits). | |
2759 | .ie n .IP """SCALE"" (array\-ref, read\-only)" 4 | |
2760 | .el .IP "\f(CWSCALE\fR (array\-ref, read\-only)" 4 | |
2761 | .IX Item "SCALE (array-ref, read-only)" | |
2762 | Returns a reference to an array of integer values for each column. | |
2763 | \&\s-1NULL\s0 (\f(CW\*(C`undef\*(C'\fR) values indicate columns where scale is not applicable. | |
2764 | .ie n .IP """NULLABLE"" (array\-ref, read\-only)" 4 | |
2765 | .el .IP "\f(CWNULLABLE\fR (array\-ref, read\-only)" 4 | |
2766 | .IX Item "NULLABLE (array-ref, read-only)" | |
2767 | Returns a reference to an array indicating the possibility of each | |
2768 | column returning a null. Possible values are \f(CW0\fR | |
2769 | (or an empty string) = no, \f(CW1\fR = yes, \f(CW2\fR = unknown. | |
2770 | .Sp | |
2771 | .Vb 1 | |
2772 | \& print "First column may return NULL\en" if $sth->{NULLABLE}->[0]; | |
2773 | .Ve | |
2774 | .ie n .IP """CursorName"" (string, read\-only)" 4 | |
2775 | .el .IP "\f(CWCursorName\fR (string, read\-only)" 4 | |
2776 | .IX Item "CursorName (string, read-only)" | |
2777 | Returns the name of the cursor associated with the statement handle, if | |
2778 | available. If not available or if the database driver does not support the | |
2779 | \&\f(CW"where current of ..."\fR \s-1SQL\s0 syntax, then it returns \f(CW\*(C`undef\*(C'\fR. | |
2780 | .ie n .IP """Statement"" (string, read\-only)" 4 | |
2781 | .el .IP "\f(CWStatement\fR (string, read\-only)" 4 | |
2782 | .IX Item "Statement (string, read-only)" | |
2783 | Returns the statement string passed to the \*(L"prepare\*(R" method. | |
2784 | .ie n .IP """RowsInCache"" (integer, read\-only)" 4 | |
2785 | .el .IP "\f(CWRowsInCache\fR (integer, read\-only)" 4 | |
2786 | .IX Item "RowsInCache (integer, read-only)" | |
2787 | If the driver supports a local row cache for \f(CW\*(C`SELECT\*(C'\fR statements, then | |
2788 | this attribute holds the number of un-fetched rows in the cache. If the | |
2789 | driver doesn't, then it returns \f(CW\*(C`undef\*(C'\fR. Note that some drivers pre-fetch | |
2790 | rows on execute, whereas others wait till the first fetch. | |
2791 | .Sp | |
2792 | See also the \*(L"RowCacheSize\*(R" database handle attribute. | |
2793 | .SH "FURTHER INFORMATION" | |
2794 | .IX Header "FURTHER INFORMATION" | |
2795 | .Sh "Transactions" | |
2796 | .IX Subsection "Transactions" | |
2797 | Transactions are a fundamental part of any robust database system. They | |
2798 | protect against errors and database corruption by ensuring that sets of | |
2799 | related changes to the database take place in atomic (indivisible, | |
2800 | all\-or\-nothing) units. | |
2801 | .PP | |
2802 | This section applies to databases that support transactions and where | |
2803 | \&\f(CW\*(C`AutoCommit\*(C'\fR is off. See \*(L"AutoCommit\*(R" for details of using \f(CW\*(C`AutoCommit\*(C'\fR | |
2804 | with various types of databases. | |
2805 | .PP | |
2806 | The recommended way to implement robust transactions in Perl | |
2807 | applications is to use \f(CW\*(C`RaiseError\*(C'\fR and \f(CW\*(C`eval\ {\ ...\ }\*(C'\fR | |
2808 | (which is very fast, unlike \f(CW\*(C`eval\ "..."\*(C'\fR). For example: | |
2809 | .PP | |
2810 | .Vb 13 | |
2811 | \& $dbh->{AutoCommit} = 0; # enable transactions, if possible | |
2812 | \& $dbh->{RaiseError} = 1; | |
2813 | \& eval { | |
2814 | \& foo(...) # do lots of work here | |
2815 | \& bar(...) # including inserts | |
2816 | \& baz(...) # and updates | |
2817 | \& $dbh->commit; # commit the changes if we get this far | |
2818 | \& }; | |
2819 | \& if ($@) { | |
2820 | \& warn "Transaction aborted because $@"; | |
2821 | \& $dbh->rollback; # undo the incomplete changes | |
2822 | \& # add other application on-error-clean-up code here | |
2823 | \& } | |
2824 | .Ve | |
2825 | .PP | |
2826 | If the \f(CW\*(C`RaiseError\*(C'\fR attribute is not set, then \s-1DBI\s0 calls would need to be | |
2827 | manually checked for errors, typically like this: | |
2828 | .PP | |
2829 | .Vb 1 | |
2830 | \& $h->method(@args) or die $h->errstr; | |
2831 | .Ve | |
2832 | .PP | |
2833 | With \f(CW\*(C`RaiseError\*(C'\fR set, the \s-1DBI\s0 will automatically \f(CW\*(C`die\*(C'\fR if any \s-1DBI\s0 method | |
2834 | call on that handle (or a child handle) fails, so you don't have to | |
2835 | test the return value of each method call. See \*(L"RaiseError\*(R" for more | |
2836 | details. | |
2837 | .PP | |
2838 | A major advantage of the \f(CW\*(C`eval\*(C'\fR approach is that the transaction will be | |
2839 | properly rolled back if \fIany\fR code (not just \s-1DBI\s0 calls) in the inner | |
2840 | application dies for any reason. The major advantage of using the | |
2841 | \&\f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`{RaiseError}\*(C'\fR attribute is that all \s-1DBI\s0 calls will be checked | |
2842 | automatically. Both techniques are strongly recommended. | |
2843 | .PP | |
2844 | After calling \f(CW\*(C`commit\*(C'\fR or \f(CW\*(C`rollback\*(C'\fR many drivers will not let you | |
2845 | fetch from a previously active \f(CW\*(C`SELECT\*(C'\fR statement handle that's a child | |
2846 | of the same database handle. A typical way round this is to connect the | |
2847 | the database twice and use one connection for \f(CW\*(C`SELECT\*(C'\fR statements. | |
2848 | .Sh "Handling \s-1BLOB\s0 / \s-1LONG\s0 / Memo Fields" | |
2849 | .IX Subsection "Handling BLOB / LONG / Memo Fields" | |
2850 | Many databases support \*(L"blob\*(R" (binary large objects), \*(L"long\*(R", or similar | |
2851 | datatypes for holding very long strings or large amounts of binary | |
2852 | data in a single field. Some databases support variable length long | |
2853 | values over 2,000,000,000 bytes in length. | |
2854 | .PP | |
2855 | Since values of that size can't usually be held in memory, and because | |
2856 | databases can't usually know in advance the length of the longest long | |
2857 | that will be returned from a \f(CW\*(C`SELECT\*(C'\fR statement (unlike other data | |
2858 | types), some special handling is required. | |
2859 | .PP | |
2860 | In this situation, the value of the \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`{LongReadLen}\*(C'\fR attribute is used | |
2861 | to determine how much buffer space to allocate when fetching such | |
2862 | fields. The \f(CW\*(C`$h\-\*(C'\fR>\f(CW\*(C`{LongTruncOk}\*(C'\fR attribute is used to determine how to | |
2863 | behave if a fetched value can't fit into the buffer. | |
2864 | .PP | |
2865 | When trying to insert long or binary values, placeholders should be used | |
2866 | since there are often limits on the maximum size of an \f(CW\*(C`INSERT\*(C'\fR | |
2867 | statement and the \*(L"quote\*(R" method generally can't cope with binary | |
2868 | data. See \*(L"Placeholders and Bind Values\*(R". | |
2869 | .Sh "Simple Examples" | |
2870 | .IX Subsection "Simple Examples" | |
2871 | Here's a complete example program to select and fetch some data: | |
2872 | .PP | |
2873 | .Vb 2 | |
2874 | \& my $dbh = DBI->connect("dbi:DriverName:db_name", $user, $password) | |
2875 | \& or die "Can't connect to $data_source: $DBI::errstr"; | |
2876 | .Ve | |
2877 | .PP | |
2878 | .Vb 4 | |
2879 | \& my $sth = $dbh->prepare( q{ | |
2880 | \& SELECT name, phone | |
2881 | \& FROM mytelbook | |
2882 | \& }) or die "Can't prepare statement: $DBI::errstr"; | |
2883 | .Ve | |
2884 | .PP | |
2885 | .Vb 2 | |
2886 | \& my $rc = $sth->execute | |
2887 | \& or die "Can't execute statement: $DBI::errstr"; | |
2888 | .Ve | |
2889 | .PP | |
2890 | .Vb 2 | |
2891 | \& print "Query will return $sth->{NUM_OF_FIELDS} fields.\en\en"; | |
2892 | \& print "Field names: @{ $sth->{NAME} }\en"; | |
2893 | .Ve | |
2894 | .PP | |
2895 | .Vb 5 | |
2896 | \& while (($name, $phone) = $sth->fetchrow_array) { | |
2897 | \& print "$name: $phone\en"; | |
2898 | \& } | |
2899 | \& # check for problems which may have terminated the fetch early | |
2900 | \& die $sth->errstr if $sth->err; | |
2901 | .Ve | |
2902 | .PP | |
2903 | .Vb 1 | |
2904 | \& $dbh->disconnect; | |
2905 | .Ve | |
2906 | .PP | |
2907 | Here's a complete example program to insert some data from a file. | |
2908 | (This example uses \f(CW\*(C`RaiseError\*(C'\fR to avoid needing to check each call). | |
2909 | .PP | |
2910 | .Vb 3 | |
2911 | \& my $dbh = DBI->connect("dbi:DriverName:db_name", $user, $password, { | |
2912 | \& RaiseError => 1, AutoCommit => 0 | |
2913 | \& }); | |
2914 | .Ve | |
2915 | .PP | |
2916 | .Vb 3 | |
2917 | \& my $sth = $dbh->prepare( q{ | |
2918 | \& INSERT INTO table (name, phone) VALUES (?, ?) | |
2919 | \& }); | |
2920 | .Ve | |
2921 | .PP | |
2922 | .Vb 7 | |
2923 | \& open FH, "<phone.csv" or die "Unable to open phone.csv: $!"; | |
2924 | \& while (<FH>) { | |
2925 | \& chomp; | |
2926 | \& my ($name, $phone) = split /,/; | |
2927 | \& $sth->execute($name, $phone); | |
2928 | \& } | |
2929 | \& close FH; | |
2930 | .Ve | |
2931 | .PP | |
2932 | .Vb 2 | |
2933 | \& $dbh->commit; | |
2934 | \& $dbh->disconnect; | |
2935 | .Ve | |
2936 | .PP | |
2937 | Here's how to convert fetched NULLs (undefined values) into empty strings: | |
2938 | .PP | |
2939 | .Vb 5 | |
2940 | \& while($row = $sth->fetchrow_arrayref) { | |
2941 | \& # this is a fast and simple way to deal with nulls: | |
2942 | \& foreach (@$row) { $_ = '' unless defined } | |
2943 | \& print "@$row\en"; | |
2944 | \& } | |
2945 | .Ve | |
2946 | .PP | |
2947 | The \f(CW\*(C`q{...}\*(C'\fR style quoting used in these examples avoids clashing with | |
2948 | quotes that may be used in the \s-1SQL\s0 statement. Use the double-quote like | |
2949 | \&\f(CW\*(C`qq{...}\*(C'\fR operator if you want to interpolate variables into the string. | |
2950 | See \*(L"Quote and Quote-like Operators\*(R" in perlop for more details. | |
2951 | .Sh "Threads and Thread Safety" | |
2952 | .IX Subsection "Threads and Thread Safety" | |
2953 | Perl versions 5.004_50 and later include optional experimental support | |
2954 | for multiple threads on many platforms. If the \s-1DBI\s0 is built using a | |
2955 | Perl that has threads enabled then it will use a per-driver mutex to | |
2956 | ensure that only one thread is with a driver at any one time. | |
2957 | Please note that support for threads in Perl is still experimental and | |
2958 | is known to have some significant problems. It's use is not recommended. | |
2959 | .Sh "Signal Handling and Canceling Operations" | |
2960 | .IX Subsection "Signal Handling and Canceling Operations" | |
2961 | The first thing to say is that signal handling in Perl is currently | |
2962 | \&\fInot\fR safe. There is always a small risk of Perl crashing and/or | |
2963 | core dumping when, or after, handling a signal. (The risk was reduced | |
2964 | with 5.004_04 but is still present.) | |
2965 | .PP | |
2966 | The two most common uses of signals in relation to the \s-1DBI\s0 are for | |
2967 | canceling operations when the user types Ctrl-C (interrupt), and for | |
2968 | implementing a timeout using \f(CW\*(C`alarm()\*(C'\fR and \f(CW$SIG{ALRM}\fR. | |
2969 | .PP | |
2970 | To assist in implementing these operations, the \s-1DBI\s0 provides a \f(CW\*(C`cancel\*(C'\fR | |
2971 | method for statement handles. The \f(CW\*(C`cancel\*(C'\fR method should abort the current | |
2972 | operation and is designed to be called from a signal handler. | |
2973 | .PP | |
2974 | However, it must be stressed that: a) few drivers implement this at | |
2975 | the moment (the \s-1DBI\s0 provides a default method that just returns \f(CW\*(C`undef\*(C'\fR); | |
2976 | and b) even if implemented, there is still a possibility that the statement | |
2977 | handle, and possibly the parent database handle, will not be usable | |
2978 | afterwards. | |
2979 | .PP | |
2980 | If \f(CW\*(C`cancel\*(C'\fR returns true, then it has successfully | |
2981 | invoked the database engine's own cancel function. If it returns false, | |
2982 | then \f(CW\*(C`cancel\*(C'\fR failed. If it returns \f(CW\*(C`undef\*(C'\fR, then the database | |
2983 | engine does not have cancel implemented. | |
2984 | .SH "DEBUGGING" | |
2985 | .IX Header "DEBUGGING" | |
2986 | In addition to the \*(L"trace\*(R" method, you can enable the same trace | |
2987 | information by setting the \f(CW\*(C`DBI_TRACE\*(C'\fR environment variable before | |
2988 | starting Perl. | |
2989 | .PP | |
2990 | On Unix-like systems using a Bourne-like shell, you can do this easily | |
2991 | on the command line: | |
2992 | .PP | |
2993 | .Vb 1 | |
2994 | \& DBI_TRACE=2 perl your_test_script.pl | |
2995 | .Ve | |
2996 | .PP | |
2997 | If \f(CW\*(C`DBI_TRACE\*(C'\fR is set to a non-numeric value, then it is assumed to | |
2998 | be a file name and the trace level will be set to 2 with all trace | |
2999 | output appended to that file. If the name begins with a number | |
3000 | followed by an equal sign (\f(CW\*(C`=\*(C'\fR), then the number and the equal sign are | |
3001 | stripped off from the name, and the number is used to set the trace | |
3002 | level. For example: | |
3003 | .PP | |
3004 | .Vb 1 | |
3005 | \& DBI_TRACE=1=dbitrace.log perl your_test_script.pl | |
3006 | .Ve | |
3007 | .PP | |
3008 | See also the \*(L"trace\*(R" method. | |
3009 | .PP | |
3010 | It can sometimes be handy to compare trace files from two different | |
3011 | runs of the same script. However using a tool like \f(CW\*(C`diff\*(C'\fR doesn't work | |
3012 | well because the trace file is full of object addresses that may | |
3013 | differ each run. Here's a handy little command to strip those out: | |
3014 | .PP | |
3015 | .Vb 1 | |
3016 | \& perl -pe 's/\eb0x[\eda-f]{6,}/0xNNNN/gi; s/\eb[\eda-f]{6,}/<long number>/gi' | |
3017 | .Ve | |
3018 | .SH "WARNING AND ERROR MESSAGES" | |
3019 | .IX Header "WARNING AND ERROR MESSAGES" | |
3020 | .Sh "Fatal Errors" | |
3021 | .IX Subsection "Fatal Errors" | |
3022 | .ie n .IP "Can't call method ""prepare"" without a package or object reference" 4 | |
3023 | .el .IP "Can't call method ``prepare'' without a package or object reference" 4 | |
3024 | .IX Item "Can't call method prepare without a package or object reference" | |
3025 | The \f(CW$dbh\fR handle you're using to call \f(CW\*(C`prepare\*(C'\fR is probably undefined because | |
3026 | the preceding \f(CW\*(C`connect\*(C'\fR failed. You should always check the return status of | |
3027 | \&\s-1DBI\s0 methods, or use the \*(L"RaiseError\*(R" attribute. | |
3028 | .ie n .IP "Can't call method ""execute"" without a package or object reference" 4 | |
3029 | .el .IP "Can't call method ``execute'' without a package or object reference" 4 | |
3030 | .IX Item "Can't call method execute without a package or object reference" | |
3031 | The \f(CW$sth\fR handle you're using to call \f(CW\*(C`execute\*(C'\fR is probably undefined because | |
3032 | the preceeding \f(CW\*(C`prepare\*(C'\fR failed. You should always check the return status of | |
3033 | \&\s-1DBI\s0 methods, or use the \*(L"RaiseError\*(R" attribute. | |
3034 | .IP "\s-1DBI/DBD\s0 internal version mismatch" 4 | |
3035 | .IX Item "DBI/DBD internal version mismatch" | |
3036 | The \s-1DBD\s0 driver module was built with a different version of \s-1DBI\s0 than | |
3037 | the one currently being used. You should rebuild the \s-1DBD\s0 module under | |
3038 | the current version of \s-1DBI\s0. | |
3039 | .Sp | |
3040 | (Some rare platforms require \*(L"static linking\*(R". On those platforms, there | |
3041 | may be an old \s-1DBI\s0 or \s-1DBD\s0 driver version actually embedded in the Perl | |
3042 | executable being used.) | |
3043 | .IP "\s-1DBD\s0 driver has not implemented the AutoCommit attribute" 4 | |
3044 | .IX Item "DBD driver has not implemented the AutoCommit attribute" | |
3045 | The \s-1DBD\s0 driver implementation is incomplete. Consult the author. | |
3046 | .ie n .IP "Can't [sg]et %s\->{%s}: unrecognised attribute" 4 | |
3047 | .el .IP "Can't [sg]et \f(CW%s\fR\->{%s}: unrecognised attribute" 4 | |
3048 | .IX Item "Can't [sg]et %s->{%s}: unrecognised attribute" | |
3049 | You attempted to set or get an unknown attribute of a handle. Make | |
3050 | sure you have spelled the attribute name correctly; case is significant | |
3051 | (e.g., \*(L"Autocommit\*(R" is not the same as \*(L"AutoCommit\*(R"). | |
3052 | .Sh "Warnings" | |
3053 | .IX Subsection "Warnings" | |
3054 | .IP "Database handle destroyed without explicit disconnect" 4 | |
3055 | .IX Item "Database handle destroyed without explicit disconnect" | |
3056 | A \f(CW$dbh\fR handle went out of scope or the program ended before the handle | |
3057 | was disconnected from the database. You should always explicitly call | |
3058 | \&\f(CW\*(C`disconnect\*(C'\fR when you are finished using a database handle. If using | |
3059 | transactions then you should also explicitly call \f(CW\*(C`commit\*(C'\fR or \f(CW\*(C`rollback\*(C'\fR | |
3060 | before \f(CW\*(C`disconnect\*(C'\fR. | |
3061 | .ie n .IP "\s-1DBI\s0 Handle cleared whilst still holding %d cached kids!" 4 | |
3062 | .el .IP "\s-1DBI\s0 Handle cleared whilst still holding \f(CW%d\fR cached kids!" 4 | |
3063 | .IX Item "DBI Handle cleared whilst still holding %d cached kids!" | |
3064 | Most probably due to a \s-1DBI\s0 bug. Possibly a \s-1DBD\s0 driver bug. Please report it. | |
3065 | .IP "\s-1DBI\s0 Handle cleared whilst still active!" 4 | |
3066 | .IX Item "DBI Handle cleared whilst still active!" | |
3067 | Most probably due to a \s-1DBI\s0 bug. Possibly a \s-1DBD\s0 driver bug. Please report it. | |
3068 | .IP "\s-1DBI\s0 Handle has uncleared implementors data" 4 | |
3069 | .IX Item "DBI Handle has uncleared implementors data" | |
3070 | Most probably a \s-1DBD\s0 driver bug. Please report it. | |
3071 | .ie n .IP "\s-1DBI\s0 Handle has %d uncleared child handles" 4 | |
3072 | .el .IP "\s-1DBI\s0 Handle has \f(CW%d\fR uncleared child handles" 4 | |
3073 | .IX Item "DBI Handle has %d uncleared child handles" | |
3074 | Most probably due to a \s-1DBI\s0 bug. Possibly a \s-1DBD\s0 driver bug. Please report it. | |
3075 | .SH "SEE ALSO" | |
3076 | .IX Header "SEE ALSO" | |
3077 | .Sh "Driver and Database Documentation" | |
3078 | .IX Subsection "Driver and Database Documentation" | |
3079 | Refer to the documentation for the \s-1DBD\s0 driver that you are using. | |
3080 | .PP | |
3081 | Refer to the \s-1SQL\s0 Language Reference Manual for the database engine that you are using. | |
3082 | .Sh "Books and Journals" | |
3083 | .IX Subsection "Books and Journals" | |
3084 | .Vb 1 | |
3085 | \& Programming the Perl DBI, by Alligator Descartes and Tim Bunce. | |
3086 | .Ve | |
3087 | .PP | |
3088 | .Vb 1 | |
3089 | \& Programming Perl 2nd Ed. by Larry Wall, Tom Christiansen & Randal Schwartz. | |
3090 | .Ve | |
3091 | .PP | |
3092 | .Vb 1 | |
3093 | \& Learning Perl by Randal Schwartz. | |
3094 | .Ve | |
3095 | .PP | |
3096 | .Vb 1 | |
3097 | \& Dr Dobb's Journal, November 1996. | |
3098 | .Ve | |
3099 | .PP | |
3100 | .Vb 1 | |
3101 | \& The Perl Journal, April 1997. | |
3102 | .Ve | |
3103 | .Sh "Manual Pages" | |
3104 | .IX Subsection "Manual Pages" | |
3105 | \&\fIperl\fR\|(1), \fIperlmod\fR\|(1), \fIperlbook\fR\|(1) | |
3106 | .Sh "Mailing List" | |
3107 | .IX Subsection "Mailing List" | |
3108 | The \fIdbi-users\fR mailing list is the primary means of communication among | |
3109 | users of the \s-1DBI\s0 and its related modules. For details send email to: | |
3110 | .PP | |
3111 | .Vb 1 | |
3112 | \& dbi-users-help@perl.org | |
3113 | .Ve | |
3114 | .PP | |
3115 | There are typically between 700 and 900 messages per month. You have | |
3116 | to subscribe in order to be able to post. However you can opt for a | |
3117 | \&'post\-only' subscription. | |
3118 | .PP | |
3119 | Mailing list archives are held at: | |
3120 | .PP | |
3121 | .Vb 6 | |
3122 | \& http://www.xray.mpe.mpg.de/mailing-lists/dbi/ | |
3123 | \& http://groups.yahoo.com/group/dbi-users | |
3124 | \& http://www.bitmechanic.com/mail-archives/dbi-users/ | |
3125 | \& http://marc.theaimsgroup.com/?l=perl-dbi&r=1&w=2 | |
3126 | \& http://www.mail-archive.com/dbi-users%40perl.org/ | |
3127 | \& http://www.mail-archive.com/dbi-users%40perl.org/ | |
3128 | .Ve | |
3129 | .Sh "Assorted Related \s-1WWW\s0 Links" | |
3130 | .IX Subsection "Assorted Related WWW Links" | |
3131 | The \s-1DBI\s0 \*(L"Home Page\*(R": | |
3132 | .PP | |
3133 | .Vb 1 | |
3134 | \& http://dbi.perl.org/ | |
3135 | .Ve | |
3136 | .PP | |
3137 | Other \s-1DBI\s0 related links: | |
3138 | .PP | |
3139 | .Vb 5 | |
3140 | \& http://tegan.deltanet.com/~phlip/DBUIdoc.html | |
3141 | \& http://dc.pm.org/perl_db.html | |
3142 | \& http://wdvl.com/Authoring/DB/Intro/toc.html | |
3143 | \& http://www.hotwired.com/webmonkey/backend/tutorials/tutorial1.html | |
3144 | \& http://bumppo.net/lists/macperl/1999/06/msg00197.html | |
3145 | .Ve | |
3146 | .PP | |
3147 | Other database related links: | |
3148 | .PP | |
3149 | .Vb 2 | |
3150 | \& http://www.jcc.com/sql_stnd.html | |
3151 | \& http://cuiwww.unige.ch/OSG/info/FreeDB/FreeDB.home.html | |
3152 | .Ve | |
3153 | .PP | |
3154 | Commercial and Data Warehouse Links | |
3155 | .PP | |
3156 | .Vb 6 | |
3157 | \& http://www.dwinfocenter.org | |
3158 | \& http://www.datawarehouse.com | |
3159 | \& http://www.datamining.org | |
3160 | \& http://www.olapcouncil.org | |
3161 | \& http://www.idwa.org | |
3162 | \& http://www.knowledgecenters.org/dwcenter.asp | |
3163 | .Ve | |
3164 | .PP | |
3165 | Recommended Perl Programming Links | |
3166 | .PP | |
3167 | .Vb 1 | |
3168 | \& http://language.perl.com/style/ | |
3169 | .Ve | |
3170 | .Sh "\s-1FAQ\s0" | |
3171 | .IX Subsection "FAQ" | |
3172 | Please also read the \s-1DBI\s0 \s-1FAQ\s0 which is installed as a \s-1DBI::FAQ\s0 module. | |
3173 | You can use \fIperldoc\fR to read it by executing the \f(CW\*(C`perldoc DBI::FAQ\*(C'\fR command. | |
3174 | .SH "AUTHORS" | |
3175 | .IX Header "AUTHORS" | |
3176 | \&\s-1DBI\s0 by Tim Bunce. This pod text by Tim Bunce, J. Douglas Dunlop, | |
3177 | Jonathan Leffler and others. Perl by Larry Wall and the | |
3178 | \&\f(CW\*(C`perl5\-porters\*(C'\fR. | |
3179 | .SH "COPYRIGHT" | |
3180 | .IX Header "COPYRIGHT" | |
3181 | The \s-1DBI\s0 module is Copyright (c) 1994\-2000 Tim Bunce. England. | |
3182 | All rights reserved. | |
3183 | .PP | |
3184 | You may distribute under the terms of either the \s-1GNU\s0 General Public | |
3185 | License or the Artistic License, as specified in the Perl \s-1README\s0 file. | |
3186 | .SH "ACKNOWLEDGEMENTS" | |
3187 | .IX Header "ACKNOWLEDGEMENTS" | |
3188 | I would like to acknowledge the valuable contributions of the many | |
3189 | people I have worked with on the \s-1DBI\s0 project, especially in the early | |
3190 | years (1992\-1994). In no particular order: Kevin Stock, Buzz Moschetti, | |
3191 | Kurt Andersen, Ted Lemon, William Hails, Garth Kennedy, Michael Peppler, | |
3192 | Neil S. Briscoe, Jeff Urlwin, David J. Hughes, Jeff Stander, | |
3193 | Forrest D Whitcher, Larry Wall, Jeff Fried, Roy Johnson, Paul Hudson, | |
3194 | Georg Rehfeld, Steve Sizemore, Ron Pool, Jon Meek, Tom Christiansen, | |
3195 | Steve Baumgarten, Randal Schwartz, and a whole lot more. | |
3196 | .PP | |
3197 | Then, of course, there are the poor souls who have struggled through | |
3198 | untold and undocumented obstacles to actually implement \s-1DBI\s0 drivers. | |
3199 | Among their ranks are Jochen Wiedmann, Alligator Descartes, Jonathan | |
3200 | Leffler, Jeff Urlwin, Michael Peppler, Henrik Tougaard, Edwin Pratomo, | |
3201 | Davide Migliavacca, Jan Pazdziora, Peter Haworth, Edmund Mergl, Steve | |
3202 | Williams, Thomas Lowery, and Phlip Plumlee. Without them, the \s-1DBI\s0 would | |
3203 | not be the practical reality it is today. I'm also especially grateful | |
3204 | to Alligator Descartes for starting work on the \*(L"Programming the Perl | |
3205 | \&\s-1DBI\s0\*(R" book and letting me jump on board. | |
3206 | .SH "TRANSLATIONS" | |
3207 | .IX Header "TRANSLATIONS" | |
3208 | A German translation of this manual (possibly slightly out of date) is | |
3209 | available, thanks to O'Reilly, at: | |
3210 | .PP | |
3211 | .Vb 1 | |
3212 | \& http://www.oreilly.de/catalog/perldbiger/ | |
3213 | .Ve | |
3214 | .PP | |
3215 | Some other translations: | |
3216 | .PP | |
3217 | .Vb 2 | |
3218 | \& http://cronopio.net/perl/ - Spanish | |
3219 | \& http://member.nifty.ne.jp/hippo2000/dbimemo.htm - Japanese | |
3220 | .Ve | |
3221 | .SH "SUPPORT / WARRANTY" | |
3222 | .IX Header "SUPPORT / WARRANTY" | |
3223 | The \s-1DBI\s0 is free software. \s-1IT\s0 \s-1COMES\s0 \s-1WITHOUT\s0 \s-1WARRANTY\s0 \s-1OF\s0 \s-1ANY\s0 \s-1KIND\s0. | |
3224 | .PP | |
3225 | Commercial support for Perl and the \s-1DBI\s0, DBD::Oracle and | |
3226 | Oraperl modules can be arranged via The Perl Clinic. | |
3227 | For more details visit: | |
3228 | .PP | |
3229 | .Vb 1 | |
3230 | \& http://www.perlclinic.com | |
3231 | .Ve | |
3232 | .SH "TRAINING" | |
3233 | .IX Header "TRAINING" | |
3234 | References to \s-1DBI\s0 related training resources. No recommendation implied. | |
3235 | .PP | |
3236 | .Vb 2 | |
3237 | \& http://www.treepax.co.uk/ | |
3238 | \& http://www.keller.com/dbweb/ | |
3239 | .Ve | |
3240 | .SH "FREQUENTLY ASKED QUESTIONS" | |
3241 | .IX Header "FREQUENTLY ASKED QUESTIONS" | |
3242 | See the \s-1DBI\s0 \s-1FAQ\s0 for a more comprehensive list of FAQs. Use the | |
3243 | \&\f(CW\*(C`perldoc DBI::FAQ\*(C'\fR command to read it. | |
3244 | .Sh "How fast is the \s-1DBI\s0?" | |
3245 | .IX Subsection "How fast is the DBI?" | |
3246 | To measure the speed of the \s-1DBI\s0 and DBD::Oracle code, I modified | |
3247 | DBD::Oracle so you can set an attribute that will cause the | |
3248 | same row to be fetched from the row cache over and over again (without | |
3249 | involving Oracle code but exercising *all* the \s-1DBI\s0 and DBD::Oracle code | |
3250 | in the code path for a fetch). | |
3251 | .PP | |
3252 | The results (on my lightly loaded old Sparc 10) fetching 50000 rows using: | |
3253 | .PP | |
3254 | .Vb 1 | |
3255 | \& 1 while $csr->fetch; | |
3256 | .Ve | |
3257 | .PP | |
3258 | were: | |
3259 | one field: 5300 fetches per cpu second (approx) | |
3260 | ten fields: 4000 fetches per cpu second (approx) | |
3261 | .PP | |
3262 | Obviously results will vary between platforms (newer faster platforms | |
3263 | can reach around 50000 fetches per second), but it does give a feel for | |
3264 | the maximum performance: fast. By way of comparison, using the code: | |
3265 | .PP | |
3266 | .Vb 1 | |
3267 | \& 1 while @row = $csr->fetchrow_array; | |
3268 | .Ve | |
3269 | .PP | |
3270 | (\f(CW\*(C`fetchrow_array\*(C'\fR is roughly the same as \f(CW\*(C`ora_fetch\*(C'\fR) gives: | |
3271 | .PP | |
3272 | .Vb 2 | |
3273 | \& one field: 3100 fetches per cpu second (approx) | |
3274 | \& ten fields: 1000 fetches per cpu second (approx) | |
3275 | .Ve | |
3276 | .PP | |
3277 | Notice the slowdown and the more dramatic impact of extra fields. | |
3278 | (The fields were all one char long. The impact would be even bigger for | |
3279 | longer strings.) | |
3280 | .PP | |
3281 | Changing that slightly to represent actually doing something in Perl | |
3282 | with the fetched data: | |
3283 | .PP | |
3284 | .Vb 3 | |
3285 | \& while(@row = $csr->fetchrow_array) { | |
3286 | \& $hash{++$i} = [ @row ]; | |
3287 | \& } | |
3288 | .Ve | |
3289 | .PP | |
3290 | gives: ten fields: 500 fetches per cpu second (approx) | |
3291 | .PP | |
3292 | That simple addition has *halved* the performance. | |
3293 | .PP | |
3294 | I therefore conclude that \s-1DBI\s0 and DBD::Oracle overheads are small | |
3295 | compared with Perl language overheads (and probably database overheads). | |
3296 | .PP | |
3297 | So, if you think the \s-1DBI\s0 or your driver is slow, try replacing your | |
3298 | fetch loop with just: | |
3299 | .PP | |
3300 | .Vb 1 | |
3301 | \& 1 while $csr->fetch; | |
3302 | .Ve | |
3303 | .PP | |
3304 | and time that. If that helps then point the finger at your own code. If | |
3305 | that doesn't help much then point the finger at the database, the | |
3306 | platform, the network etc. But think carefully before pointing it at | |
3307 | the \s-1DBI\s0 or your driver. | |
3308 | .PP | |
3309 | (Having said all that, if anyone can show me how to make the \s-1DBI\s0 or | |
3310 | drivers even more efficient, I'm all ears.) | |
3311 | .Sh "Why doesn't my \s-1CGI\s0 script work right?" | |
3312 | .IX Subsection "Why doesn't my CGI script work right?" | |
3313 | Read the information in the references below. Please do \fInot\fR post | |
3314 | \&\s-1CGI\s0 related questions to the \fIdbi-users\fR mailing list (or to me). | |
3315 | .PP | |
3316 | .Vb 5 | |
3317 | \& http://www.perl.com/cgi-bin/pace/pub/doc/FAQs/cgi/perl-cgi-faq.html | |
3318 | \& http://www3.pair.com/webthing/docs/cgi/faqs/cgifaq.shtml | |
3319 | \& http://www-genome.wi.mit.edu/WWW/faqs/www-security-faq.html | |
3320 | \& http://www.boutell.com/faq/ | |
3321 | \& http://www.perl.com/perl/faq/ | |
3322 | .Ve | |
3323 | .PP | |
3324 | General problems and good ideas: | |
3325 | .PP | |
3326 | .Vb 2 | |
3327 | \& Use the CGI::ErrorWrap module. | |
3328 | \& Remember that many env vars won't be set for CGI scripts. | |
3329 | .Ve | |
3330 | .Sh "How can I maintain a \s-1WWW\s0 connection to a database?" | |
3331 | .IX Subsection "How can I maintain a WWW connection to a database?" | |
3332 | For information on the Apache httpd server and the \f(CW\*(C`mod_perl\*(C'\fR module see | |
3333 | .PP | |
3334 | .Vb 1 | |
3335 | \& http://perl.apache.org/ | |
3336 | .Ve | |
3337 | .Sh "What about \s-1ODBC\s0?" | |
3338 | .IX Subsection "What about ODBC?" | |
3339 | A \s-1DBD::ODBC\s0 module is available. | |
3340 | .Sh "Does the \s-1DBI\s0 have a year 2000 problem?" | |
3341 | .IX Subsection "Does the DBI have a year 2000 problem?" | |
3342 | No. The \s-1DBI\s0 has no knowledge or understanding of dates at all. | |
3343 | .PP | |
3344 | Individual drivers (DBD::*) may have some date handling code but are | |
3345 | unlikely to have year 2000 related problems within their code. However, | |
3346 | your application code which \fIuses\fR the \s-1DBI\s0 and \s-1DBD\s0 drivers may have | |
3347 | year 2000 related problems if it has not been designed and written well. | |
3348 | .PP | |
3349 | See also the \*(L"Does Perl have a year 2000 problem?\*(R" section of the Perl \s-1FAQ:\s0 | |
3350 | .PP | |
3351 | .Vb 1 | |
3352 | \& http://www.perl.com/CPAN/doc/FAQs/FAQ/PerlFAQ.html | |
3353 | .Ve | |
3354 | .SH "OTHER RELATED WORK AND PERL MODULES" | |
3355 | .IX Header "OTHER RELATED WORK AND PERL MODULES" | |
3356 | .IP "Apache::DBI by E.Mergl@bawue.de" 4 | |
3357 | .IX Item "Apache::DBI by E.Mergl@bawue.de" | |
3358 | To be used with the Apache daemon together with an embedded Perl | |
3359 | interpreter like \f(CW\*(C`mod_perl\*(C'\fR. Establishes a database connection which | |
3360 | remains open for the lifetime of the \s-1HTTP\s0 daemon. This way the \s-1CGI\s0 | |
3361 | connect and disconnect for every database access becomes superfluous. | |
3362 | .IP "\s-1JDBC\s0 Server by Stuart 'Zen' Bishop zen@bf.rmit.edu.au" 4 | |
3363 | .IX Item "JDBC Server by Stuart 'Zen' Bishop zen@bf.rmit.edu.au" | |
3364 | The server is written in Perl. The client classes that talk to it are | |
3365 | of course in Java. Thus, a Java applet or application will be able to | |
3366 | comunicate via the \s-1JDBC\s0 \s-1API\s0 with any database that has a \s-1DBI\s0 driver installed. | |
3367 | The \s-1URL\s0 used is in the form \f(CW\*(C`jdbc:dbi://host.domain.etc:999/Driver/DBName\*(C'\fR. | |
3368 | It seems to be very similar to some commercial products, such as jdbcKona. | |
3369 | .IP "Remote Proxy \s-1DBD\s0 support" 4 | |
3370 | .IX Item "Remote Proxy DBD support" | |
3371 | As of \s-1DBI\s0 1.02, a complete implementation of a DBD::Proxy driver and the | |
3372 | DBI::ProxyServer are part of the \s-1DBI\s0 distribution. | |
3373 | .IP "\s-1SQL\s0 Parser" 4 | |
3374 | .IX Item "SQL Parser" | |
3375 | .Vb 2 | |
3376 | \& Hugo van der Sanden <hv@crypt.compulink.co.uk> | |
3377 | \& Stephen Zander <stephen.zander@mckesson.com> | |
3378 | .Ve | |
3379 | .Sp | |
3380 | Based on the O'Reilly lex/yacc book examples and \f(CW\*(C`byacc\*(C'\fR. | |
3381 | .Sp | |
3382 | See also the SQL::Statement module, a very simple \s-1SQL\s0 parser and engine, | |
3383 | base of the \s-1DBD::CSV\s0 driver. |