1ODBC(3) User Contributed Perl Documentation ODBC(3)
2
6 DBD::ODBC - ODBC Driver for DBI
7
9 This documentation refers to DBD::ODBC version 1.61.
10
12 This version of DBD::ODBC contains a significant fix to unicode when
13 inserting into CHAR/VARCHAR columns and it is a change in behaviour
14 from 1.45. The change only applies to unicode builds of DBD::ODBC (the
15 default on Windows but you can build it for unicode on unix too) and
16 char/varchar columns and not nchar/nvarchar columns.
17 Prior to this release of DBD::ODBC when you are using the unicode build
19 of DBD::ODBC and inserted data into a CHAR/VARCHAR columns using
20 parameters DBD::ODBC did this:
21 1 if you set odbc_describe_parameters to 0, (thus preventing DBD::ODBC
23 from calling SQLDescribeParam) parameters for CHAR/VARCHAR columns
24 were bound as SQL_WVARCHAR or SQL_WLONGVARCHAR (depending on the
25 length of the parameter).
26 2 if you set odbc_force_bind_type then all parameters are bound as you
28 specified.
29 3 if you override the parameter type in the bind_param method, the
31 type you specified would be used.
32 4 if the driver does not support SQLDescribeParam or SQLDescribeParam
34 was called and failed then the bind type defaulted as in 1.
35 5 if none of the above (and I'd guess that is the normal case for most
37 people) then DBD::ODBC calls SQLDescribeParam to find the parameter
38 type. This usually returns SQL_CHAR or SQL_VARCHAR for CHAR/VARCHAR
39 columns unsurprisingly. The parameter was then bound as SQL_VARCHAR.
40 Items 1 to 4 still apply. 5 now has a different behaviour. In this
42 release, DBD::ODBC now looks at your bound data first before using the
43 type returned by SQLDescribeParam. If you data looks like unicode
44 (i.e., SvUTF8() is true) it now binds the parameter as SQL_WVARCHAR.
45 What might this might mean to you?
47 If you had Perl scalars that were bound to CHAR/VARCHAR columns in an
49 insert/update/delete and those scalars contained unicode, DBD::ODBC
50 would actually pass the individual octets in your scalar not
51 characters. For instance, if you had the Perl scalar "\x{20ac}" (the
52 Euro unicode character) and you bound it to a CHAR/VARCHAR, DBD::ODBC
53 would pass 0xe2, 0x82, 0xc2 as separate characters because those bytes
54 were Perl's UTF-8 encoding of a euro. These would probably be
55 interpreted by your database engine as 3 characters in its current
56 codepage. If you queried your database to find the length of the data
57 inserted you'd probably get back 3, not 1.
58 However, when DBD::ODBC read that column back in a select statement, it
60 would bind the column as SQL_WCHAR and you'd get back 3 characters with
61 the utf8 flag on (what those characters were depends on how your
62 database or driver translates code page characters to wide characters).
63 What should happen now is that if your bound parameters are unicode,
65 DBD::ODBC will bind them as wide characters (unicode) and your driver
66 or database will attempt to convert them into the code page it is
67 using. This means so long as your database can store the data you are
68 inserting, when you read it back you should get what you inserted.
69
71 use DBI;
72 $dbh = DBI->connect('dbi:ODBC:DSN=mydsn', 'user', 'password');
74 See DBI for more information.
76
78 Change log and FAQs
79 Please note that the change log has been moved to DBD::ODBC::Changes.
80 To access this documentation, use "perldoc DBD::ODBC::Changes".
81 The FAQs have also moved to DBD::ODBC::FAQ.pm. To access the FAQs use
83 "perldoc DBD::ODBC::FAQ".
84 Important note about the tests
86 DBD::ODBC is unlike most other DBDs in that it connects to literally
87 dozens of possible ODBC Drivers. It is practically impossible for me to
88 test every one and so some tests may fail with some ODBC Drivers. This
89 does not mean DBD::ODBC will not work with your ODBC Driver but it is
90 worth reporting any test failures on rt.cpan.org or to the dbi-users
91 mailing list.
92 DBI attribute handling
94 If a DBI defined attribute is not mentioned here it behaves as per the
95 DBI specification.
96 ReadOnly (boolean)
98 DBI documents the "ReadOnly" attribute as being settable and
100 retrievable on connection and statement handles. In ODBC setting
101 ReadOnly to true causes the connection attribute "SQL_ATTR_ACCESS_MODE"
102 to be set to "SQL_MODE_READ_ONLY" and setting it to false will set the
103 access mode to "SQL_MODE_READ_WRITE" (which is the default in ODBC).
104 Note: There is no equivalent of setting ReadOnly on a statement handle
106 in ODBC.
107 Note: See ODBC documentation on "SQL_ATTR_ACCESS_MODE" as setting it to
109 "SQL_MODE_READ_ONLY" does not prevent your script from running updates
110 or deletes; it is simply a hint to the driver/database that you won't
111 being doing updates.
112 Note: Since DBD::ODCB 1.44_3, if the driver does not support setting
114 "SQL_ATTR_ACCESS_MODE" and returns SQL_SUCCESS_WITH_INFO and "option
115 value changed" a warning is issued (which you'll only see if you have
116 DBI > 1.628). In addition, any subsequent attempts to fetch the
117 ReadOnly attribute will return the value last set.
118 This attribute requires DBI version 1.55 or better.
120 Private attributes common to connection and statement handles
122 odbc_ignore_named_placeholders
123 Use this if you have special needs (such as Oracle triggers, etc) where
125 :new or :name mean something special and are not just place holder
126 names. You must then use ? for binding parameters. Example:
127 $dbh->{odbc_ignore_named_placeholders} = 1;
129 $dbh->do("create trigger foo as if :new.x <> :old.x then ... etc");
130 Without this, DBD::ODBC will think :new and :old are placeholders for
132 binding and get confused.
133 odbc_default_bind_type
135 This value defaults to 0.
137 Older versions of DBD::ODBC assumed that the parameter binding type was
139 12 ("SQL_VARCHAR"). Newer versions always attempt to call
140 "SQLDescribeParam" to find the parameter types but if
141 "SQLDescribeParam" is unavailable DBD::ODBC falls back to a default
142 bind type. The internal default bind type is "SQL_VARCHAR" (for non-
143 unicode build) and "SQL_WVARCHAR" or "SQL_VARCHAR" (for a unicode build
144 depending on whether the parameter is unicode or not). If you set
145 "odbc_default_bind_type" to a value other than 0 you override the
146 internal default.
147 N.B If you call the "bind_param" method with a SQL type this overrides
149 everything else above.
150 odbc_force_bind_type
152 This value defaults to 0.
154 If set to anything other than 0 this will force bound parameters to be
156 bound as this type and "SQLDescribeParam" will not be used; in other
157 words it implies "odbc_describe_parameters" is set to false too.
158 Older versions of DBD::ODBC assumed the parameter binding type was 12
160 ("SQL_VARCHAR") and newer versions always attempt to call
161 "SQLDescribeParam" to find the parameter types. If your driver supports
162 "SQLDescribeParam" and it succeeds it may still fail to describe the
163 parameters accurately (MS SQL Server sometimes does this with some SQL
164 like select myfunc(?) where 1 = 1). Setting "odbc_force_bind_type" to
165 "SQL_VARCHAR" will force DBD::ODBC to bind all the parameters as
166 "SQL_VARCHAR" and ignore SQLDescribeParam.
167 Bear in mind that if you are inserting unicode data you probably want
169 to use "SQL_WVARCHAR"/"SQL_WCHAR"/"SQL_WLONGVARCHAR" and not
170 "SQL_VARCHAR".
171 As this attribute was created to work around buggy ODBC Drivers which
173 support SQLDescribeParam but describe the parameters incorrectly you
174 are probably better specifying the bind type on the "bind_param" call
175 on a per statement level rather than blindly setting
176 "odbc_force_bind_type" across a whole connection.
177 N.B If you call the "bind_param" method with a SQL type this overrides
179 everything else above.
180 odbc_force_rebind
182 This is to handle special cases, especially when using multiple result
184 sets. Set this before execute to "force" DBD::ODBC to re-obtain the
185 result set's number of columns and column types for each execute.
186 Especially useful for calling stored procedures which may return
187 different result sets each execute. The only performance penalty is
188 during execute(), but I didn't want to incur that penalty for all
189 circumstances. It is probably fairly rare that this occurs. This
190 attribute will be automatically set when multiple result sets are
191 triggered. Most people shouldn't have to worry about this.
192 odbc_async_exec
194 Allow asynchronous execution of queries. This causes a spin-loop (with
196 a small "sleep") until the ODBC API being called is complete (i.e.,
197 while the ODBC API returns "SQL_STILL_EXECUTING"). This is useful,
198 however, if you want the error handling and asynchronous messages (see
199 the "odbc_err_handler" and t/20SQLServer.t for an example of this).
200 odbc_query_timeout
202 This allows you to change the ODBC query timeout (the ODBC statement
204 attribute "SQL_ATTR_QUERY_TIMEOUT"). ODBC defines the query time out as
205 the number of seconds to wait for a SQL statement to execute before
206 returning to the application. A value of 0 (the default) means there is
207 no time out. Do not confuse this with the ODBC attributes
208 "SQL_ATTR_LOGIN_TIMEOUT" and "SQL_ATTR_CONNECTION_TIMEOUT". Add
209 { odbc_query_timeout => 30 }
211 to your connect, set on the "dbh" before creating a statement or
213 explicitly set it on your statement handle. The odbc_query_timeout on a
214 statement is inherited from the parent connection.
215 Note that internally DBD::ODBC only sets the query timeout if you set
217 it explicitly and the default of 0 (no time out) is implemented by the
218 ODBC driver and not DBD::ODBC.
219 Note that some ODBC drivers implement a maximum query timeout value and
221 will limit timeouts set above their maximum. You may see a warning if
222 your time out is capped by the driver but there is currently no way to
223 retrieve the capped value back from the driver.
224 Note that some drivers may not support this attribute.
226 See t/20SqlServer.t for an example.
228 odbc_putdata_start
230 "odbc_putdata_start" defines the size at which DBD::ODBC uses
232 "SQLPutData" and "SQLParamData" to send larger objects to the database
233 instead of simply binding them as normal with "SQLBindParameter". It is
234 mostly a placeholder for future changes allowing chunks of data to be
235 sent to the database and there is little reason for anyone to change it
236 currently.
237 The default for odbc_putdata_start is 32768 because this value was
239 hard-coded in DBD::ODBC until 1.16_1.
240 odbc_column_display_size
242 If you ODBC driver does not support the SQL_COLUMN_DISPLAY_SIZE and
244 SQL_COLUMN_LENGTH attributes to SQLColAtrributes then DBD::ODBC does
245 not know how big the column might be. odbc_column_display_size sets the
246 default value for the column size when retrieving column data where the
247 size cannot be determined.
248 The default for odbc_column_display_size is 2001 because this value was
250 hard-coded in DBD::ODBC until 1.17_3.
251 odbc_utf8_on
253 Set this flag to treat all strings returned from the ODBC driver
255 (except columns described as SQL_BINARY or SQL_TIMESTAMP and its
256 variations) as UTF-8 encoded. Some ODBC drivers (like Aster and maybe
257 PostgreSQL) return UTF-8 encoded data but do not support the SQLxxxW
258 unicode API. Enabling this flag will cause DBD::ODBC to treat driver
259 returned data as UTF-8 encoded and it will be marked as such in Perl.
260 Do not confuse this with DBD::ODBC's unicode support. The
262 "odbc_utf8_on" attribute only applies to non-unicode enabled builds of
263 DBD::ODBC.
264 odbc_describe_parameters
266 Defaults to on. When set this allows DBD::ODBC to call SQLDescribeParam
268 (if the driver supports it) to retrieve information about any
269 parameters.
270 When off/false DBD::ODBC will not call SQLDescribeParam and defaults to
272 binding parameters as SQL_CHAR/SQL_WCHAR depending on the build type
273 and whether your data is unicode or not.
274 You do not have to disable odbc_describe_parameters just because your
276 driver does not support SQLDescribeParam as DBD::ODBC will work this
277 out at the start via SQLGetFunctions.
278 Note: disabling odbc_describe_parameters when your driver does support
280 SQLDescribeParam may prevent DBD::ODBC binding parameters for some
281 column types properly.
282 You can also set this attribute in the attributes passed to the prepare
284 method.
285 This attribute was added so someone moving from freeTDS (a driver which
287 does not support SQLDescribeParam) to a driver which does support
288 SQLDescribeParam could do so without changing any Perl. The situation
289 was very specific since dates were being bound as dates when
290 SQLDescribeParam was called and chars without and the data format was
291 not a supported date format.
292 Private methods common to connection and statement handles
294 odbc_getdiagrec
295 @diags = $handle->odbc_getdiagrec($record_number);
297 Introduced in 1.34_3.
299 This is just a wrapper around the ODBC API SQLGetDiagRec. When a method
301 on a connection or statement handle fails if there are any ODBC
302 diagnostics you can use this method to retrieve them. Records start at
303 1 and there may be more than 1. It returns an array containing the
304 state, native and error message text or an empty array if the requested
305 diagnostic record does not exist. To get all diagnostics available keep
306 incrementing $record_number until odbc_getdiagrec returns an empty
307 array.
308 All of the state, native and message text are already passed to DBI via
310 its set_err method so this method does not really tell you anything you
311 cannot already get from DBI except when there is more than one
312 diagnostic.
313 You may find this useful in an error handler as you can get the ODBC
315 diagnostics as they are and not how DBD::ODBC was forced to fit them
316 into the DBI's system.
317 NOTE: calling this method does not clear DBI's error values as usually
319 happens.
320 odbc_getdiagfield
322 $diag = $handle->odbc_getdiagfield($record, $identifier);
324 This is just a wrapper around the ODBC API SQLGetDiagField. When a
326 method on a connection or statement handle fails if there are any ODBC
327 diagnostics you can use this method to retrieve the individual
328 diagnostic fields. As with "odbc_getdiagrec" records start at 1. The
329 identifier is one of:
330 SQL_DIAG_CURSOR_ROW_COUNT
332 SQL_DIAG_DYNAMIC_FUNCTION
333 SQL_DIAG_DYNAMIC_FUNCTION_CODE
334 SQL_DIAG_NUMBER
335 SQL_DIAG_RETURNCODE
336 SQL_DIAG_ROW_COUNT
337 SQL_DIAG_CLASS_ORIGIN
338 SQL_DIAG_COLUMN_NUMBER
339 SQL_DIAG_CONNECTION_NAME
340 SQL_DIAG_MESSAGE_TEXT
341 SQL_DIAG_NATIVE
342 SQL_DIAG_ROW_NUMBER
343 SQL_DIAG_SERVER_NAME
344 SQL_DIAG_SQLSTATE
345 SQL_DIAG_SUBCLASS_ORIGIN
346 DBD::ODBC exports these constants as 'diags' e.g.,
348 use DBD::ODBC qw(:diags);
350 Of particular interest is SQL_DIAG_COLUMN_NUMBER as it will tell you
352 which bound column or parameter is in error (assuming your driver
353 supports it). See params_in_error in the examples dir.
354 NOTE: calling this method does not clear DBI's error values as usually
356 happens.
357 Private connection attributes
359 odbc_err_handler
360 NOTE: You might want to look at DBI's error handler before using the
362 one in DBD::ODBC however, there are subtle differences. DBD::ODBC's
363 odbc_err_handler is called for error and informational diagnostics
364 i.e., it is called when an ODBC call fails the SQL_SUCCEEDED macro
365 which means the ODBC call returned SQL_ERROR (-1) or
366 SQL_SUCCESS_WITH_INFO (1).
367 Allow error and informational diagnostics to be handled by the
369 application. A call-back function supplied by the application to
370 handle or ignore messages.
371 The callback function receives four parameters: state (string), error
373 (string), native error code (number) and the status returned from the
374 last ODBC API. The fourth argument was added in 1.30_7.
375 If the error handler returns 0, the error is ignored, otherwise the
377 error is passed through the normal DBI error handling. Note, if the
378 status is SQL_SUCCESS_WITH_INFO this will not reach the DBI error
379 handler as it is not an error.
380 This can also be used for procedures under MS SQL Server (Sybase too,
382 probably) to obtain messages from system procedures such as DBCC.
383 Check t/20SQLServer.t and t/10handler.t.
384 $dbh->{RaiseError} = 1;
386 sub err_handler {
387 ($state, $msg, $native, $rc, $status) = @_;
388 if ($state = '12345')
389 return 0; # ignore this error
390 else
391 return 1; # propagate error
392 }
393 $dbh->{odbc_err_handler} = \&err_handler;
394 # do something to cause an error
395 $dbh->{odbc_err_handler} = undef; # cancel the handler
396 odbc_SQL_ROWSET_SIZE
398 Setting odbc_SQL_ROWSET_SIZE results in a call to SQLSetConnectAttr to
400 set the ODBC SQL_ROWSET_SIZE (9) attribute to whatever value you set
401 odbc_SQL_ROWSET_SIZE to.
402 The ODBC default for SQL_ROWSET_SIZE is 1.
404 Usually MS SQL Server does not support multiple active statements (MAS)
406 i.e., you cannot have 2 or more outstanding selects. You can set
407 odbc_SQL_ROWSET_SIZE to 2 to persuade MS SQL Server to support multiple
408 active statements.
409 Setting SQL_ROWSET_SIZE usually only affects calls to SQLExtendedFetch
411 but does allow MAS and as DBD::ODBC does not use SQLExtendedFetch there
412 should be no ill effects to DBD::ODBC.
413 Be careful with this attribute as once set to anything larger than 1
415 (the default) you must retrieve all result-sets before the statement
416 handle goes out of scope or you can upset the TDS protocol and this can
417 result in a hang. With DBI this is unlikely as DBI warns when a
418 statement goes out of scope with outstanding results.
419 NOTE: if you get an error saying "[Microsoft][ODBC SQL Server
421 Driver]Invalid attribute/option identifier (SQL-HY092)" when you set
422 odbc_SQL_ROWSET_SIZE in the connect method you need to either a)
423 upgrade to DBI 1.616 or above b) set odbc_SQL_ROWSET_SIZE after
424 connect.
425 In versions of SQL Server 2005 and later see "Multiple Active
427 Statements (MAS)" in the DBD::ODBC::FAQ instead of using this
428 attribute.
429 Thanks to Andrew Brown for the original patch.
431 DBD developer note: Here lies a bag of worms. Firstly, SQL_ROWSET_SIZE
433 is an ODBC 2 attribute and is usually a statement attribute not a
434 connection attribute. However, in ODBC 2.0 you could set statement
435 attributes on a connection handle and it acted as a default for all
436 subsequent statement handles created under that connection handle. If
437 you are using ODBC 3 the driver manager continues to map this call but
438 the ODBC Driver needs to act on it (the MS SQL Server driver still
439 appears to but some other ODBC drivers for MS SQL Server do not).
440 Secondly, somewhere a long the line MS decided it was no longer valid
441 to retrieve the SQL_ROWSET_SIZE attribute from a connection handle in
442 an ODBC 3 application (which DBD::ODBC now is). In itself, this would
443 not be a problem except for a minor bug in DBI which until release
444 1.616 mistakenly issued a FETCH on any attribute mentioned in the
445 connect method call. As a result, it you use a DBI prior to 1.616 and
446 attempt to set odbc_SQL_ROWSET_SIZE in the connect method call, DBI
447 issues a FETCH on odbc_SQL_ROWSET_SIZE and the driver manager throws it
448 out as an invalid attribute thus resulting in an error. The only way
449 around this (other than upgrading DBI) is to set odbc_SQL_ROWSET_SIZE
450 AFTER the call to connect. Thirdly, MS withdrew the SQLROWSETSIZE macro
451 from the sql header files in MDAC 2.7 for 64 bit platforms i.e.,
452 SQLROWSETSIZE is not defined on 64 bit platforms from MDAC 2.7 as it is
453 in a "#ifdef win32" (see
454 http://msdn.microsoft.com/en-us/library/ms716287%28v=vs.85%29.aspx).
455 Setting SQL_ROWSET_SIZE still seems to take effect on 64 bit platforms
456 but you can no longer retrieve its value from a connection handle
457 (hence the issue above with DBI redundant FETCH).
458 odbc_exec_direct
460 Force DBD::ODBC to use "SQLExecDirect" instead of
462 "SQLPrepare"/"SQLExecute".
463 There are drivers that only support "SQLExecDirect" and the DBD::ODBC
465 do() override does not allow returning result sets. Therefore, the way
466 to do this now is to set the attribute odbc_exec_direct.
467 NOTE: You may also want to use this option if you are creating
469 temporary objects (e.g., tables) in MS SQL Server and for some reason
470 cannot use the "do" method. see
471 <http://technet.microsoft.com/en-US/library/ms131667.aspx> which says
472 Prepared statements cannot be used to create temporary objects on SQL
473 Server 2000 or later.... Without odbc_exec_direct, the temporary object
474 will disappear before you can use it.
475 There are currently two ways to get this:
477 $dbh->prepare($sql, { odbc_exec_direct => 1});
479 and
481 $dbh->{odbc_exec_direct} = 1;
483 NOTE: Even if you build DBD::ODBC with unicode support you can still
485 not pass unicode strings to the prepare method if you also set
486 odbc_exec_direct. This is a restriction in this attribute which is
487 unavoidable.
488 odbc_SQL_DRIVER_ODBC_VER
490 This, while available via get_info() is captured here. I may get rid
492 of this as I only used it for debugging purposes.
493 odbc_cursortype
495 This allows multiple concurrent statements on SQL*Server. In your
497 connect, add
498 { odbc_cursortype => 2 }.
500 If you are using DBI > 1.41, you should also be able to use
502 { odbc_cursortype => DBI::SQL_CURSOR_DYNAMIC }
504 instead. For example:
506 my $dbh = DBI->connect("dbi:ODBC:$DSN", $user, $pass,
508 { RaiseError => 1, odbc_cursortype => 2});
509 my $sth = $dbh->prepare("one statement");
510 my $sth2 = $dbh->prepare("two statement");
511 $sth->execute;
512 my @row;
513 while (@row = $sth->fetchrow_array) {
514 $sth2->execute($row[0]);
515 }
516 See t/20SqlServer.t for an example.
518 In versions of SQL Server 2005 and later see "Multiple Active
520 Statements (MAS)" in the DBD::ODBC::FAQ instead of using this
521 attribute.
522 odbc_has_unicode
524 A read-only attribute signifying whether DBD::ODBC was built with the C
526 macro WITH_UNICODE or not. A value of 1 indicates DBD::ODBC was built
527 with WITH_UNICODE else the value returned is 0.
528 Building WITH_UNICODE affects columns and parameters which are
530 SQL_C_WCHAR, SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR, SQL, the
531 connect method and a lot more. See "Unicode".
532 When odbc_has_unicode is 1, DBD::ODBC will:
534 bind all string columns as wide characters (SQL_Wxxx)
536 This means that UNICODE data stored in these columns will be
537 returned to Perl correctly as unicode (i.e., encoded in UTF-8 and
538 the UTF-8 flag set).
539 bind parameters the database declares as wide characters or unicode
541 parameters as SQL_Wxxx
542 Parameters bound where the database declares the parameter as being
543 a wide character, or where the parameter data is unicode, or where
544 the parameter type is explicitly set to a wide type (e.g.,
545 SQL_Wxxx) are bound as wide characters in the ODBC API and
546 DBD::ODBC encodes the perl parameters as UTF-16 before passing them
547 to the driver.
548 SQL SQL passed to the "prepare" or "do" methods which has the UTF-8
550 flag set will be converted to UTF-16 before being passed to the
551 ODBC APIs "SQLPrepare" or "SQLExecDirect".
552 connection strings
554 Connection strings passed to the "connect" method will be converted
555 to UTF-16 before being passed to the ODBC API "SQLDriverConnectW".
556 This happens irrespective of whether the UTF-8 flag is set on the
557 perl connect strings because unixODBC requires an application to
558 call SQLDriverConnectW to indicate it will be calling the wide ODBC
559 APIs.
560 NOTE: You will need at least Perl 5.8.1 to use UNICODE with DBD::ODBC.
562 NOTE: Binding of unicode output parameters is coded but untested.
564 NOTE: When building DBD::ODBC on Windows ($^O eq 'MSWin32') the
566 WITH_UNICODE macro is automatically added. To disable specify -nou as
567 an argument to Makefile.PL (e.g. "perl Makefile.PL -nou"). On non-
568 Windows platforms the WITH_UNICODE macro is not enabled by default and
569 to enable you need to specify the -u argument to Makefile.PL. Please
570 bear in mind that some ODBC drivers do not support SQL_Wxxx columns or
571 parameters.
572 You can also specify that you want UNICODE support by setting the
574 "DBD_ODBC_UNICODE" environment variable prior to install:
575 export DBD_ODBC_UNICODE=1
577 cpanm DBD::ODBC
578 UNICODE support in ODBC Drivers differs considerably. Please read the
580 README.unicode file for further details.
581 odbc_out_connect_string
583 After calling the connect method this will be the ODBC driver's out
585 connection string - see documentation on SQLDriverConnect.
586 NOTE: this value is only set if DBD::ODBC calls the SQLDriverConnect
588 ODBC API (and not SQLConnect) which only happens if a) DSN or DRIVER is
589 specified in the connection string or b) SQLConnect fails.
590 Typically, applications (like MS Access and many others) which build a
592 connection string via dialogs and possibly SQLBrowseConnect eventually
593 end up with a successful ODBC connection to the ODBC driver and
594 database. The odbc_out_connect_string provides a string which you can
595 pass to SQLDriverConnect (DBI's connect prefixed with dbi:ODBC:") which
596 will connect you to the same datasource at a later date. You may also
597 want to see "odbc_driver_complete".
598 odbc_version
600 This was added prior to the move to ODBC 3.x to allow the caller to
602 "force" ODBC 3.0 compatibility. It's probably not as useful now, but
603 it allowed get_info and get_type_info to return correct/updated
604 information that ODBC 2.x didn't permit/provide. Since DBD::ODBC is
605 now 3.x, this can be used to force 2.x behavior via something like: my
606 $dbh = DBI->connect("dbi:ODBC:$DSN", $user, $pass,
608 { odbc_version =>2});
609 odbc_driver_complete
611 This attribute was added to DBD::ODBC in 1.32_2.
613 odbc_driver_complete is only relevant to the Windows operating system
615 and will be ignored on other platforms. It is off by default.
616 When set to a true value DBD::ODBC attempts to obtain a window handle
618 and calls SQLDriverConnect with the SQL_DRIVER_COMPLETE attribute
619 instead of the normal SQL_DRIVER_NOPROMPT option. What this means is
620 that if the connection string does not describe sufficient attributes
621 to enable the ODBC driver manager to connect to a data source it will
622 throw a dialogue allowing you to input the remaining attributes. Once
623 you ok that dialogue the ODBC Driver Manager will continue as if you
624 specified those attributes in the connection string. Once the
625 connection is complete you may want to look at the
626 odbc_out_connect_string attribute to obtain a connection string you can
627 use in the future to pass into the connect method without prompting.
628 As a window handle is passed to SQLDriverConnect it also means the ODBC
630 driver may throw a dialogue e.g., if your password has expired the MS
631 SQL Server driver will often prompt for a new one.
632 An example is:
634 my $h = DBI->connect('dbi:ODBC:DRIVER={SQL Server}', "username", "password",
636 {odbc_driver_complete => 1});
637 As this only provides the driver and further attributes are required a
639 dialogue will be thrown allowing you to specify the SQL Server to
640 connect to and possibly other attributes.
641 odbc_batch_size
643 Sets the batch size for execute_for_fetch which defaults to 10. Bear
645 in mind the bigger you set this the more memory DBD::ODBC will need to
646 allocate when running execute_for_fetch and the memory required is
647 max_length_of_pn * odbc_batch_size * n_parameters.
648 odbc_array_operations
650 NOTE: this was briefly odbc_disable_array_operations in 1.35 and
652 1.36_1. I did warn it was experimental and it turned out the default
653 was too ambitious and it was a poor name anyway. Also the default was
654 to use array operations and now the default is the opposite.
655 If set to true DBD::ODBC uses its own internal execute_for_fetch
657 instead of DBI's default execute_for_fetch. The default is false.
658 Using the internal execute_for_fetch should be quite a bit faster when
659 using arrays of parameters for insert/update/delete operations as
660 batches of parameters are sent to the database in one go. However, the
661 required support in some ODBC drivers is a little sketchy and there is
662 no way for DBD::ODBC to ascertain this until it is too late.
663 Please read the documentation on execute_array and execute_for_fetch
665 which details subtle differences in DBD::ODBC's implementation compared
666 with using DBI's default implementation. If these difference cause you
667 a problem you can set odbc_array_operations to false and DBD::ODBC will
668 revert to DBI's implementations of the array methods.
669 You can use the environment variable ODBC_DISABLE_ARRAY_OPERATIONS to
671 switch array operations on/off too. When set to 1 array operations are
672 disabled. When not set the default is used (which currently is off).
673 When set to 0 array operations are used no matter what. I know this is
674 slightly counter intuitive but I've found it difficult to change the
675 name (it got picked up and used in a few places very quickly).
676 odbc_taf_callback
678 NOTE: this is experimental until I at least see more than one ODBC
680 driver which supports TAF.
681 Transparent Application Failover (TAF) is a feature in OCI that allows
683 for clients to automatically reconnect to an instance in the event of a
684 failure of the instance. The reconnect happens automatically from
685 within the OCI (Oracle Call Interface) library.
686 TAF supports a callback function which once registered is called by the
688 driver to let you know what is happening and which allows you to a
689 degree, to control how the failover is handled.
690 You need to set up TAF on your instance first and that process is
692 beyond the scope of this document. Once TAF is enabled you simply set
693 "odbc_taf_callback" to a code reference which should look like this:
694 sub taf_handler {
696 my ($dbh, $event, $type) = @_;
697 # do something here
698 }
699 DBD::ODBC will pass the connection handle ($dbh), the Oracle event type
701 (OCI_FO_END, OCI_FO_ABORT, OCI_FO_REAUTH, OCI_FO_BEGIN, OCI_FO_ERROR)
702 and the Oracle type (OCI_FO_NONE, OCI_FO_SESSION, OCI_FO_SELECT,
703 OCI_FO_TXNAL). Consult the Oracle documentation for what these are.
704 You can import these constants using the :taf export tag. If your
705 instance is not TAF enabled it is likely an attempt to register a
706 callback will fail but this is driver dependent (all DBD::ODBC does is
707 make a SQLSetConnectAttr call and provide a C wrapper which calls your
708 Perl subroutine).
709 Here is a commented example:
711 my $h = DBI->connect('dbi:ODBC:oracle','xxx','yyy',
713 {RaiseError => 1,
714 odbc_taf_callback => \&taf_handler}) or die "connect";
715 while (1) {
716 my $s = $h->selectall_arrayref(q/select 1 from dual/);
717 sleep 5;
718 }
719 sub taf_handler {
721 my ($dbh, $event, $type) = @_;
722 #print "taf_handler $dbh, $event, $type\n";
724 if ($event == OCI_FO_BEGIN) {
726 print "Instance unavailable, stand by\n";
727 print "Your TAF type is : ",
728 ($type == OCI_FO_NONE ? "NONE" :
729 ($type == OCI_FO_SESSION ? "SESSION" :
730 ($type == OCI_FO_SELECT ? "SELECT" : "?"))) , "\n";
731 # start a counter and each time OCI_FO_ERROR is passed in we will
732 # count down and abort the failover when we hit 0.
733 $count = 10;
734 return 0;
735 } elsif ($event == OCI_FO_ERROR) {
736 # We get an OCI_FO_ERROR each time the failover fails
737 # sleep a while until the count hits 0
738 if (--$count < 1) {
739 print "Giving up\n";
740 return 0; # give up
741 } else {
742 print "Retrying...\n";
743 sleep 1;
744 return OCI_FO_RETRY; # tell Oracle to retry
745 }
746 } elsif ($event == OCI_FO_REAUTH) {
747 print "Failed over user. Resuming Services\n";
748 } elsif ($event == OCI_FO_END) {
749 print "Failover ended - resuming\n";
750 }
751 return 0;
752 }
753 NOTE: The above example is for use with the Easysoft Oracle ODBC
755 Driver. ODBC does not define any standard way of supporting TAF and so
756 different drivers may use different connection attributes to set it up
757 or may even pass the callback different arguments. Unfortunately, I
758 don't have access to any other ODBC driver which supports TAF. Until I
759 see others I cannot create a generic interface. I'll happily accept
760 patches for any other driver or if you send me a working copy of the
761 driver and the documentation I will add support for it.
762 odbc_trace_file
764 Specify the name and path to a file you want ODBC API trace information
766 to be written to. See "odbc_trace".
767 odbc_trace
769 Enable or disable ODBC API tracing. Set to 1 to enable and 0 to
771 disable.
772 This calls SQLSetConnectAttr for SQL_ATTR_TRACE and either sets
774 SQL_OPT_TRACE_ON or SQL_OPT_TRACE_OFF. Enabling tracing will tell the
775 ODBC driver manager to write and ODBC API trace to the file named with
776 "odbc_trace_file".
777 NOTE: If you don't set odbc_trace_file most ODBC Driver Managers write
779 to a file called SQL.LOG in the root directory (but this depends on the
780 driver manager used).
781 NOTE: This tracing is produced by the ODBC Driver Manager and has
783 nothing to do with DBD::ODBC other than it should trace the ODBC calls
784 DBD::ODBC makes i.e., DBD::ODBC is not responsible for the tracing
785 mechanism itself.
786 NOTE: Enabling tracing will probably slow your application down a lot.
788 I'd definitely think twice about it if in a production environment
789 unless you are desperate as it tends to produce very large trace files
790 for short periods of ODBC activity.
791 Private statement attributes
793 odbc_more_results
794 Use this attribute to determine if there are more result sets
796 available.
797 Any ODBC Driver which batches results or counts of inserts/updates will
799 need you to loop on odbc_more_results until there are no more results.
800 e.g., if you are performing multiple selects in a procedure or multiple
801 inserts/updates/deletes then you will probably need to loop on
802 odbc_more_results.
803 Use odbc_more_results as follows:
805 do {
807 my @row;
808 while (@row = $sth->fetchrow_array()) {
809 # do stuff here
810 }
811 } while ($sth->{odbc_more_results});
812 Note that with multiple result sets and output parameters (i.e,. using
814 bind_param_inout), don't expect output parameters to written to until
815 ALL result sets have been retrieved.
816 Under the hood this attribute causes a call to the ODBC API
818 SQLMoreResults and then any result set, insert/update/delete or output
819 parameters are described by DBD::ODBC and the statement handle will be
820 ready for processing the new result.
821 Private statement methods
823 odbc_rows
824 This method was added in 1.42_1.
826 In 64 bit ODBC SQLRowCount can return a 64bit value for the number of
828 rows affected. Unfortunately, the DBI DBD interface currently (at least
829 until 1.622) defines execute as returning an int so values which cannot
830 fit in an int are truncated. See RT 81911.
831 From DBD::ODBC 1.42_1 DBD::ODBC
833 o defines this method which will return the affected rows in an IV (and
835 IVs are guaranteed to be able to hold a pointer) so you can get the
836 real affected rows without truncation.
837 o if it detects an overflow in the execute method it will issue a
839 warning (if Warn is on which it is by default) and return INT_MAX.
840 At some stage DBI may change to fix the issue this works around.
842 NOTE: the return from odbc_rows is not the raw value returned by
844 SQLRowCount. It is the same as execute normally returns e.g., 0E0 (for
845 0), -1 for unknown and N for N rows affected where N > 0.
846 odbc_lob_read
848 $chrs_or_bytes_read = $sth->odbc_lob_read($column_no, \$lob, $length, \%attr);
850 Reads $length bytes from the lob at column $column_no returning the lob
852 into $lob and the number of bytes or characters read into
853 $chrs_or_bytes_read. If an error occurs undef will be returned. When
854 there is no more data to be read 0 is returned.
855 NOTE: This is currently an experimental method and may change in the
857 future e.g., it may support automatic concatenation of the lob parts
858 onto the end of the $lob with the addition of an extra flag or
859 destination offset as in DBI's undocumented blob_read.
860 The type the lob is retrieved as may be overridden in %attr using "TYPE
862 => sql_type". %attr is optional and if omitted defaults to SQL_C_BINARY
863 for binary columns and SQL_C_CHAR/SQL_C_WCHAR for other column types
864 depending on whether DBD::ODBC is built with unicode support.
865 $chrs_or_bytes_read will by the bytes read when the column types
866 SQL_C_CHAR or SQL_C_BINARY are used and characters read if the column
867 type is SQL_C_WCHAR.
868 When built with unicode support $length specifies the amount of buffer
870 space to be used when retrieving the lob data but as it is returned as
871 SQLWCHAR characters this means you at most retrieve "$length/2"
872 characters. When those retrieved characters are encoded in UTF-8 for
873 Perl, the $lob scalar may need to be larger than $length so DBD::ODBC
874 grows it appropriately.
875 You can retrieve a lob in chunks like this:
877 $sth->bind_col($column, undef, {TreatAsLOB=>1});
879 while(my $retrieved = $sth->odbc_lob_read($column, \my $data, $length)) {
880 print "retrieved=$retrieved lob_data=$data\n";
881 }
882 NOTE: to retrieve a lob like this you must first bind the lob column
884 specifying BindAsLOB or DBD::ODBC will 1) bind the column as normal and
885 it will be subject to LongReadLen and b) fail odbc_lob_read.
886 NOTE: Some database engines and ODBC drivers do not allow you to
888 retrieve columns out of order (e.g., MS SQL Server unless you are using
889 cursors). In those cases you must ensure the lob retrieved is the last
890 (or only) column in your select list.
891 NOTE: You can retrieve only part of a lob but you will probably have to
893 call finish on the statement handle before you do anything else with
894 that statement. When only retrieving part of a large lob you could see
895 a small delay when you call finish as some protocols used by ODBC
896 drivers send the lob down the socket synchronously and there is no way
897 to stop it (this means the ODBC driver needs to read all the lob from
898 the socket even though you never retrieved it all yourself).
899 NOTE: If your select contains multiple lobs you cannot read part of the
901 first lob, the second lob then return to the first lob. You must read
902 all lobs in order and completely or read part of a lob and then do no
903 further calls to odbc_lob_read.
904 Private DBD::ODBC Functions
906 You use DBD::ODBC private functions like this:
907 $dbh->func(arg, private_function_name, @args);
909 GetInfo
911 This private function is now superseded by DBI's get_info method.
913 This function maps to the ODBC SQLGetInfo call and the argument should
915 be a valid ODBC information type (see ODBC specification). e.g.
916 $value = $dbh->func(6, 'GetInfo');
918 which returns the "SQL_DRIVER_NAME".
920 This function returns a scalar value, which can be a numeric or string
922 value depending on the information value requested.
923 GetTypeInfo
925 This private function is now superseded by DBI's type_info and
927 type_info_all methods however as it is used by those methods it still
928 exists.
929 This function maps to the ODBC SQLGetTypeInfo API and the argument
931 should be a SQL type number (e.g. SQL_VARCHAR) or SQL_ALL_TYPES.
932 SQLGetTypeInfo returns information about a data type supported by the
933 data source.
934 e.g.
936 use DBI qw(:sql_types);
938 $sth = $dbh->func(SQL_ALL_TYPES, GetTypeInfo);
940 DBI::dump_results($sth);
941 This function returns a DBI statement handle for the SQLGetTypeInfo
943 result-set containing many columns of type attributes (see ODBC
944 specification).
945 NOTE: It is VERY important that the "use DBI" includes the
947 qw(:sql_types) so that values like SQL_VARCHAR are correctly
948 interpreted. This "imports" the sql type names into the program's name
949 space. A very common mistake is to forget the qw(:sql_types) and
950 obtain strange results.
951 GetFunctions
953 This function maps to the ODBC SQLGetFunctions API which returns
955 information on whether a function is supported by the ODBC driver.
956 The argument should be "SQL_API_ALL_FUNCTIONS" (0) for all functions or
958 a valid ODBC function number (e.g. "SQL_API_SQLDESCRIBEPARAM" which is
959 58). See ODBC specification or examine your sqlext.h and sql.h header
960 files for all the SQL_API_XXX macros.
961 If called with "SQL_API_ALL_FUNCTIONS" (0), then a 100 element array is
963 returned where each element will contain a '1' if the ODBC function
964 with that SQL_API_XXX index is supported or '' if it is not.
965 If called with a specific SQL_API_XXX value for a single function it
967 will return true if the ODBC driver supports that function, otherwise
968 false.
969 e.g.
971 my @x = $dbh->func(0,"GetFunctions");
973 print "SQLDescribeParam is supported\n" if ($x[58]);
974 or
976 print "SQLDescribeParam is supported\n"
978 if $dbh->func(58, "GetFunctions");
979 GetStatistics
981 This private function is now superseded by DBI's statistics_info
983 method.
984 See the ODBC specification for the SQLStatistics API. You call
986 SQLStatistics like this:
987 $dbh->func($catalog, $schema, $table, $unique, 'GetStatistics');
989 Prior to DBD::ODBC 1.16 $unique was not defined as being true/false or
991 SQL_INDEX_UNIQUE/SQL_INDEX_ALL. In fact, whatever value you provided
992 for $unique was passed through to the ODBC API SQLStatistics call
993 unchanged. This changed in 1.16, where $unique became a true/false
994 value which is interpreted into SQL_INDEX_UNIQUE for true and
995 SQL_INDEX_ALL for false.
996 GetForeignKeys
998 This private function is now superseded by DBI's foreign_key_info
1000 method.
1001 See the ODBC specification for the SQLForeignKeys API. You call
1003 SQLForeignKeys like this:
1004 $dbh->func($pcatalog, $pschema, $ptable,
1006 $fcatalog, $fschema, $ftable,
1007 "GetForeignKeys");
1008 GetPrimaryKeys
1010 This private function is now superseded by DBI's primary_key_info
1012 method.
1013 See the ODBC specification for the SQLPrimaryKeys API. You call
1015 SQLPrimaryKeys like this:
1016 $dbh->func($catalog, $schema, $table, "GetPrimaryKeys");
1018 data_sources
1020 This private function is now superseded by DBI's data_sources method
1022 and was finally removed in 1.49_1
1023 GetSpecialColumns
1025 See the ODBC specification for the SQLSpecialColumns API. You call
1027 SQLSpecialColumns like this:
1028 $dbh->func($identifier, $catalog, $schema, $table, $scope,
1030 $nullable, 'GetSpecialColumns');
1031 Handled as of version 0.28
1033 ColAttributes
1035 This private function is now superseded by DBI's statement attributes
1037 NAME, TYPE, PRECISION, SCALE, NULLABLE etc).
1038 See the ODBC specification for the SQLColAttributes API. You call
1040 SQLColAttributes like this:
1041 $sth->func($column, $ftype, "ColAttributes");
1043 SQL_COLUMN_COUNT = 0
1045 SQL_COLUMN_NAME = 1
1046 SQL_COLUMN_TYPE = 2
1047 SQL_COLUMN_LENGTH = 3
1048 SQL_COLUMN_PRECISION = 4
1049 SQL_COLUMN_SCALE = 5
1050 SQL_COLUMN_DISPLAY_SIZE = 6
1051 SQL_COLUMN_NULLABLE = 7
1052 SQL_COLUMN_UNSIGNED = 8
1053 SQL_COLUMN_MONEY = 9
1054 SQL_COLUMN_UPDATABLE = 10
1055 SQL_COLUMN_AUTO_INCREMENT = 11
1056 SQL_COLUMN_CASE_SENSITIVE = 12
1057 SQL_COLUMN_SEARCHABLE = 13
1058 SQL_COLUMN_TYPE_NAME = 14
1059 SQL_COLUMN_TABLE_NAME = 15
1060 SQL_COLUMN_OWNER_NAME = 16
1061 SQL_COLUMN_QUALIFIER_NAME = 17
1062 SQL_COLUMN_LABEL = 18
1063 Note:Oracle's ODBC driver for linux in instant client 11r1 often
1065 returns strange values for column name e.g., '20291'. It is wiser to
1066 use DBI's NAME and NAME_xx attributes for portability.
1067 DescribeCol
1069 Removed in DBD::ODBC 1.40_3
1071 Use the DBI's statement attributes NAME, TYPE, PRECISION, SCALE,
1073 NULLABLE etc instead.
1074 Additional bind_col attributes
1076 DBD::ODBC supports a few additional attributes which may be passed to
1077 the bind_col method in the attributes.
1078 DiscardString
1080 See DBI's sql_type_cast utility function.
1082 If you bind a column as a specific type (SQL_INTEGER, SQL_DOUBLE and
1084 SQL_NUMERIC are the only ones supported currently) and you add
1085 DiscardString to the prepare attributes then if the returned bound data
1086 is capable of being converted to that type the scalar's pv (the string
1087 portion of a scalar) is cleared.
1088 NOTE: post DBD::ODBC 1.37, DBD::ODBC binds all SQL_INTEGER columns as
1090 SQL_C_LONG and DiscardString is irrelevant.
1091 This is especially useful if you are using a module which uses a
1093 scalar's flags and/or pv to decide if a scalar is a number. JSON::XS
1094 does this and without this flag you have to add 0 to all bound column
1095 data returning numbers to get JSON::XS to encode it is N instead of
1096 "N".
1097 NOTE: For DiscardString you need at least DBI 1.611.
1099 StrictlyTyped
1101 See DBI's sql_type_cast utility function.
1103 See "DiscardString" above.
1105 Specifies that when DBI's sql_type_cast function is called on returned
1107 data where a bind type is specified that if the conversion cannot be
1108 performed an error will be raised.
1109 This is probably not a lot of use with DBD::ODBC as if you ask for say
1111 an SQL_INTEGER and the data is not able to be converted to an integer
1112 the ODBC driver will probably return "Invalid character value for cast
1113 specification (SQL-22018)".
1114 NOTE: For StrictlyTyped you need at least DBI 1.611.
1116 TreatAsLOB
1118 See "odbc_lob_read".
1120 Tracing
1122 DBD::ODBC now supports the parse_trace_flag and parse_trace_flags
1123 methods introduced in DBI 1.42 (see DBI for a full description). As of
1124 DBI 1.604, the only trace flag defined which is relevant to DBD::ODBC
1125 is 'SQL' which DBD::ODBC supports by outputting the SQL strings (after
1126 modification) passed to the prepare and do methods.
1127 From DBI 1.617 DBI also defines ENC (encoding), CON (connection) TXN
1129 (transaction) and DBD (DBD only) trace flags. DBI's ENC and CON trace
1130 flags are synonymous with DBD::ODBC's odbcunicode and odbcconnection
1131 trace flags though I may remove the DBD::ODBC ones in the future. DBI's
1132 DBD trace flag allows output of only DBD::ODBC trace messages without
1133 DBI's trace messages.
1134 Currently DBD::ODBC supports two private trace flags. The 'odbcunicode'
1136 flag traces some unicode operations and the odbcconnection traces the
1137 connect process.
1138 To enable tracing of particular flags you use:
1140 $h->trace($h->parse_trace_flags('SQL|odbcconnection'));
1142 $h->trace($h->parse_trace_flags('1|odbcunicode'));
1143 In the first case 'SQL' and 'odbcconnection' tracing is enabled on $h.
1145 In the second case trace level 1 is set and 'odbcunicode' tracing is
1146 enabled.
1147 If you want to enable a DBD::ODBC private trace flag before connecting
1149 you need to do something like:
1150 use DBD::ODBC;
1152 DBI->trace(DBD::ODBC->parse_trace_flag('odbcconnection'));
1153 or
1155 use DBD::ODBC;
1157 DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode'));
1158 or
1160 DBI_TRACE=odbcconnection|odbcunicode perl myscript.pl
1162 From DBI 1.617 you can output only DBD::ODBC trace messages using
1164 DBI_TRACE=DBD perl myscript.pl
1166 DBD::ODBC outputs tracing at levels 3 and above (as levels 1 and 2 are
1168 reserved for DBI).
1169 For comprehensive tracing of DBI method calls without all the DBI
1171 internals see DBIx::Log4perl.
1172 Deviations from the DBI specification
1174 last_insert_id
1175 DBD::ODBC does not support DBI's last_insert_id. There is no ODBC
1177 defined way of obtaining this information. Generally the mechanism (and
1178 it differs vastly between databases and ODBC drivers) it to issue a
1179 select of some form (e.g., select @@identity or select sequence.currval
1180 from dual, etc).
1181 There are literally dozens of databases and ODBC drivers supported by
1183 DBD::ODBC and I cannot have them all. If you know how to retrieve the
1184 information for last_insert_id and you mail me the ODBC Driver
1185 name/version and database name/version with a small working example I
1186 will collect examples and document them here.
1187 Microsoft Access. Recent versions of MS Access support select
1189 @@identity to retrieve the last insert ID. See
1190 http://support.microsoft.com/kb/815629. Information provided by Robert
1191 Freimuth.
1192 Comments in SQL
1194 DBI does not say anything in particular about comments in SQL.
1196 DBD::ODBC looks for placeholders in the SQL string and until 1.24_2 it
1197 did not recognise comments in SQL strings so could find what it
1198 believes to be a placeholder in a comment e.g.,
1199 select '1' /* placeholder ? in comment */
1201 select -- named placeholder :named in comment
1202 '1'
1203 I cannot be exact about support for ignoring placeholders in literals
1205 but it has existed for a long time in DBD::ODBC. Support for ignoring
1206 placeholders in comments was added in 1.24_2. If you find a case where
1207 a named placeholder is not ignored and should be, see
1208 "odbc_ignore_named_placeholders" for a workaround and mail me an
1209 example along with your ODBC driver name.
1210 do
1212 This is not really a deviation from the DBI specification since DBI
1214 allows a driver to avoid the overhead of creating an DBI statement
1215 handle for do().
1216 DBD::ODBC implements "do" by calling SQLExecDirect in ODBC and not
1218 SQLPrepare followed by SQLExecute so "do" is not the same as:
1219 $dbh->prepare($sql)->execute()
1221 It does this to avoid a round-trip to the server so it is faster.
1223 Normally this is good but some people fall foul of this with MS SQL
1224 Server if they call a procedure which outputs print statements (e.g.,
1225 backup) as the procedure may not complete. See the DBD::ODBC FAQ and in
1226 general you are better to use prepare/execute when calling procedures.
1227 In addition, you should realise that since DBD::ODBC does not create a
1229 DBI statement for do calls, if you set up an error handler the handle
1230 passed in when a do fails will be the database handle and not a
1231 statement handle.
1232 Mixed placeholder types
1234 There are 3 conventions for place holders in DBI. These are '?', ':N'
1236 and ':name' (where 'N' is a number and 'name' is an alpha numeric
1237 string not beginning with a number). DBD::ODBC supports all these
1238 methods for naming placeholders but you must only use one method
1239 throughout a particular SQL string. If you mix placeholder methods you
1240 will get an error like:
1241 Can't mix placeholder styles (1/2)
1243 Using the same placeholder more than once
1245 DBD::ODBC does not support (currently) the use of one named placeholder
1247 more than once in a single SQL string. i.e.,
1248 insert into foo values (:bar, :p1, :p2, :bar);
1250 is not supported because 'bar' is used more than once but:
1252 insert into foo values(:bar, :p1, :p2)
1254 is ok. If you do the former you will get an error like:
1256 DBD::ODBC does not yet support binding a named parameter more than once
1258 Binding named placeholders
1260 Although the DBI documentation (as of 1.604) does not say how named
1262 parameters are bound Tim Bunce has said that in Oracle they are bound
1263 with the leading ':' as part of the name and that has always been the
1264 case. i.e.,
1265 prepare("insert into mytable values (:fred)");
1267 bind_param(":foo", 1);
1268 DBD::ODBC does not support binding named parameters with the ':'
1270 introducer. In the above example you must use:
1271 bind_param("foo", 1);
1273 In discussion on the dbi-dev list is was suggested that the ':' could
1275 be made optional and there were no basic objections but it has not made
1276 it's way into the pod yet.
1277 Sticky Parameter Types
1279 The DBI specification post 1.608 says in bind_param:
1281 The data type is 'sticky' in that bind values passed to execute()
1283 are bound with the data type specified by earlier bind_param()
1284 calls, if any. Portable applications should not rely on being able
1285 to change the data type after the first bind_param call.
1286 DBD::ODBC does allow a parameter to be rebound with another data type
1288 as ODBC inherently allows this. Therefore you can do:
1289 # parameter 1 set as a SQL_LONGVARCHAR
1291 $sth->bind_param(1, $data, DBI::SQL_LONGVARCHAR);
1292 # without the bind above the $data parameter would be either a DBD::ODBC
1293 # internal default or whatever the ODBC driver said it was but because
1294 # parameter types are sticky, the type is still SQL_LONGVARCHAR.
1295 $sth->execute($data);
1296 # change the bound type to SQL_VARCHAR
1297 # some DBDs will ignore the type in the following, DBD::ODBC does not
1298 $sth->bind_param(1, $data, DBI::SQL_VARCHAR);
1299 disconnect and transactions
1301 DBI does not define whether a driver commits or rolls back any
1303 outstanding transaction when disconnect is called. As such DBD::ODBC
1304 cannot deviate from the specification but you should know it rolls back
1305 an uncommitted transaction when disconnect is called if SQLDisconnect
1306 returns state 25000 (transaction in progress).
1307 execute_for_fetch and execute_array
1309 From version 1.34_1 DBD::ODBC implements its own execute_for_fetch
1311 which binds arrays of parameters and can send multiple rows
1312 ("odbc_batch_size") of parameters through the ODBC driver in one go
1313 (this overrides DBI's default execute_for_fetch). This is much faster
1314 when inserting, updating or deleting many rows in one go. Note,
1315 execute_array uses execute_for_fetch when the parameters are passed for
1316 column-wise binding.
1317 NOTE: DBD::ODBC 1.34_1 to DBD::ODBC 1.36_1 set the default to use
1319 DBD::ODBC's own execute_for_fetch but quite a few ODBC drivers just
1320 cannot handle it. As such, from DBD::ODBC 1.36_2 the default was
1321 changed to not use DBD::ODBC's execute_for_fetch (i.e., you need to
1322 enable it with "odbc_array_operations").
1323 NOTE: Some ODBC drivers don't support setting SQL_ATTR_PARAMSET_SIZE >
1325 1, and hence cannot support binding arrays of parameters. The only way
1326 to detect this is to attempt to set SQL_ATTR_PARAMSET_SIZE to a value
1327 greater than 1 and it is too late once someone has called
1328 execute_for_fetch. I don't want to add test code on each connect to
1329 test for this as it will affect everyone, even those not using the
1330 native execute_for_fetch so for now it is a suck it and see. For your
1331 information MS Access which does not support arrays of parameters
1332 errors with HY092, "Invalid attribute/option identifier".
1333 However, there are a small number of differences between using
1335 DBD::ODBC's execute_for_fetch compared with using DBI's default
1336 implementation (which simply calls execute repeatedly once per row).
1337 The differences you may see are:
1338 o as DBI's execute_for_fetch does one row at a time the result from
1340 execute is for one row and just about all ODBC drivers can report the
1341 number of affected rows when SQLRowCount is called per execute. When
1342 batches of parameters are sent the driver can still return the number
1343 of affected rows but it is usually per batch rather than per row. As a
1344 result, the tuple_status array you may pass to execute_for_fetch (or
1345 execute_array) usually shows -1 (unknown) for each row although the
1346 total affected returned in array context is a correct total affected.
1347 o not all ODBC drivers have sufficient ODBC support (arguably a bug)
1349 for correct diagnostics support when using arrays. DBI dictates that if
1350 a row in the batch is in error the tuple_status will contain the state,
1351 native and error message text. However the batch may generate multiple
1352 errors per row (which DBI says nothing about) and more than one row may
1353 error. In ODBC we get a list of errors but to associate each one with a
1354 particular row we need to call SQLGetDiagField for SQL_DIAG_ROW_NUMBER
1355 and it should say which row in the batch the diagnostic is associated
1356 with. Some ODBC drivers do not support SQL_DIAG_ROW_NUMBER properly and
1357 then DBD::ODBC cannot know which row in the batch an error refers to.
1358 In this case DBD::ODBC will report an error saying "failed to retrieve
1359 diags", state of HY000 and a native of 1 so you'll still see an error
1360 but not necessarily the exact one. Also, when more than one diagnostic
1361 is found for a row DBD::ODBC picks the first one (which is usually most
1362 relevant) as there is no way to report more than one diagnostic per row
1363 in the tuple_status. If the first problem of SQL_DIAG_ROW_NUMBER proves
1364 to be a problem for you the DBD::ODBC tracing will show all errors and
1365 you can also use "odbc_getdiagrec" yourself.
1366 o Binding parameters with execute_array and execute_for_fetch does not
1368 allow the parameter types to be set. However, as parameter types are
1369 sticky you can call bind_param(param_num, undef, {TYPE => sql_type})
1370 before calling execute_for_fetch/execute_array and the TYPE should be
1371 sticky when the batch of parameters is bound.
1372 o Although you can insert very large columns execute_for_fetch will
1374 need "odbc_batch_size" * max length of parameter per parameter so you
1375 may hit memory limits. If you use DBI's execute_for_fetch DBD::ODBC
1376 uses the ODBC API SQLPutData (see "odbc_putdata_start") which does not
1377 require large amounts of memory as large columns are sent in pieces.
1378 o A lot of drivers have bugs with arrays of parameters (see the ODBC
1380 FAQ). e.g., as of 18-MAR-2012 I've seen the latest SQLite ODBC driver
1381 seg fault and freeTDS 8/0.91 returns the wrong row count for batches.
1382 o DO NOT attempt to do an insert/update/delete and a select in the same
1384 SQL with execute_array e.g.,
1385 SET IDENTITY_INSERT mytable ON
1387 insert into mytable (id, name) values (?,?)
1388 SET IDENTITY_INSERT mytable OFF
1389 SELECT SCOPE_IDENTITY()
1390 It just won't/can't work although you may not have noticed when using
1392 DBI's inbuilt execute_* methods. See rt 75687.
1393 type_info_all
1395 Many ODBC drivers now return 20 columns in type_info_all rather than
1397 the 19 DBI documents. The 20th column is usually called "USERTYPE".
1398 Recent MS SQL Server ODBC drivers do this. Fortunately this should not
1399 adversely affect you so long as you are using the keys provided at the
1400 start of type_info_all.
1401 Binding Columns
1403 The DBI specification allows a column type to be overridden in the call
1405 to the bind_col method. Mostly, DBD::ODBC ignores this type as it binds
1406 integers (SQL_INTEGER) as a SQL_C_LONG (since DBD::ODBC 1.38_1) and all
1407 other columns as SQL_C_CHAR or SQL_C_WCHAR and it is too late to change
1408 the bind type after the result-set has been described anyway. The only
1409 time when the TYPE passed to bind_col is used in DBD::ODBC is when it
1410 is SQL_NUMERIC or SQL_DOUBLE in which case DBD::ODBC will call DBI's
1411 sql_type_cast method.
1412 Since DBD::ODBC 1.38_1 if you attempt to change the bind type after the
1414 column has already bound DBD::ODBC will issue a warning and ignore your
1415 column type change e.g.,
1416 my $s = $h->prepare(q/select a from mytable);
1418 $s->execute; # The column type was determined here
1419 my $r;
1420 $s->bind_col(1, \$r); # and bound as the right type here
1421 $s->execute;
1422 $s->bind_col(1, \$r, {TYPE => SQL_XXX}); # warning, type changed
1423 Basically, if you are passing a TYPE to bind_col with DBD::ODBC (other
1425 than SQL_NUMERIC or SQL_DOUBLE) your code is probably wrong.
1426 Significant changes occurred in DBD::ODBC at 1.38_1 for binding
1428 columns. Please see the Changes file.
1429 bind_param
1431 DBD::ODBC follows the DBI specification for bind_param however the
1433 third argument (a type or a hashref containing a type) is loosely
1434 defined by DBI. From the DBI pod:
1435 The \%attr parameter can be used to hint at the data type the
1437 placeholder should have. This is rarely needed.
1438 As a general rule, don't specify a type when calling bind_param. If you
1440 stick to inserting appropriate data into the appropriate column
1441 DBD::ODBC will mostly do the right thing especially if the ODBC driver
1442 supports SQLDescribeParam.
1443 In particular don't just add a type of SQL_DATE because you are
1445 inserting a date (it will not work). The correct syntax in ODBC for
1446 inserting dates, times and timestamps is:
1447 insert into mytable (mydate, mttime, mytimestamp) values(?,?,?);
1449 bind_param(1, "{d 'YYYY-MM-DD'}"); bind_param(2, "{t 'HH:MM:SS.MM'}");
1450 # :MM can be omitted and some dbs support :MMM bind_param(3, "{ts
1451 'YYYY-MM-DD HH:MM:SS'}");
1452 See
1454 http://technet.microsoft.com/en-US/library/ms190234%28v=SQL.90%29.aspx
1455 The only times when you might want to add a type are:
1457 1. If your ODBC driver does not support SQLDescribeParam (or if you
1459 told DBD::ODBC not to use it) then DBD::ODBC will default to inserting
1460 each parameter as a string (which is usually the right thing anyway).
1461 This is ok, most of the time, but is probably not what you want when
1462 inserting a binary (use TYPE => SQL_BINARY).
1463 2. If for some reason your driver describes the parameter incorrectly.
1465 It is difficult to describe an example of this.
1466 3. If SQLDescribeParam is supported but fails e.g., MS SQL Server has
1468 problems with SQL like "select myfunc(?) where 1 = 1".
1469 Also, DBI exports some types which are not available in ODBC e.g.,
1471 SQL_BLOB. If you are unsure about ODBC types look at your ODBC header
1472 files or look up valid types in the ODBC specification.
1473 tables and table_info
1475 These are not really deviations from the DBI specification but a
1477 clarification of a DBI optional feature.
1478 DBD::ODBC supports wildcards (% and _) in the catalog, schema and type
1480 arguments. However, you should be aware that if the statement attribute
1481 SQL_ATTR_METADATA_ID is SQL_TRUE the values are interpreted as
1482 identifiers and the case is ignored. SQL_ATTR_METADATA_ID defaults to
1483 SQL_FALSE so normally the values are treated as patterns and the case
1484 is significant.
1485 SQLGetInfo for SQL_ACCESSIBLE_TABLES can affect what tables you can
1487 list.
1488 All the special cases listed by DBI (empty strings for all arguments
1490 but one which is '%') for catalog, schema and table type are supported
1491 by DBD::ODBC. However, using '%' for table type in a call to the tables
1492 method does not currently work with DBI up to 1.631 due to an issue in
1493 DBI.
1494 Although DBD::ODBC supports all the usage cases listed by DBI, your
1496 ODBC driver may not.
1497 Unicode
1499 The ODBC specification supports wide character versions (a postfix of
1500 'W') of some of the normal ODBC APIs e.g., SQLDriverConnectW is a wide
1501 character version of SQLDriverConnect.
1502 In ODBC on Windows the wide characters are defined as SQLWCHARs (2
1504 bytes) and are UCS-2 (but UTF-16 is accepted by some drivers now e.g.,
1505 MS SQL Server 2012 and the new collation suffix _SC which stands for
1506 Supplementary Character Support). On non-Windows, the main driver
1507 managers I know of have implemented the wide character APIs
1508 differently:
1509 unixODBC
1511 unixODBC mimics the Windows ODBC API precisely meaning the wide
1512 character versions expect and return 2-byte characters in UCS-2 or
1513 UTF-16.
1514 unixODBC will happily recognise ODBC drivers which only have the
1516 ANSI versions of the ODBC API and those that have the wide versions
1517 too.
1518 unixODBC will allow an ANSI application to work with a unicode ODBC
1520 driver and vice versa (although in the latter case you obviously
1521 cannot actually use unicode).
1522 unixODBC does not prevent you sending UTF-8 in the ANSI versions of
1524 the ODBC APIs but whether that is understood by your ODBC driver is
1525 another matter.
1526 unixODBC differs in only one way from the Microsoft ODBC driver in
1528 terms of unicode support in that it avoids unnecessary translations
1529 between single byte and double byte characters when an ANSI
1530 application is using a unicode-aware ODBC driver by requiring
1531 unicode applications to signal their intent by calling
1532 SQLDriverConnectW first. On Windows, the ODBC driver manager always
1533 uses the wide versions of the ODBC API in ODBC drivers which
1534 provide the wide versions regardless of what the application really
1535 needs and this results in a lot of unnecessary character
1536 translations when you have an ANSI application and a unicode ODBC
1537 driver.
1538 iODBC
1540 The wide character versions expect and return wchar_t types.
1541 DBD::ODBC has gone with unixODBC so you cannot use iODBC with a unicode
1543 build of DBD::ODBC. However, some ODBC drivers support UTF-8 (although
1544 how they do this with SQLGetData reliably I don't know) and so you
1545 should be able to use those with DBD::ODBC not built for unicode.
1546 Enabling and Disabling Unicode support
1548 On Windows Unicode support is enabled by default and to disable it you
1550 will need to specify "-nou" to Makefile.PL to get back to the original
1551 behavior of DBD::ODBC before any Unicode support was added.
1552 e.g.,
1554 perl Makfile.PL -nou
1556 On non-Windows platforms Unicode support is disabled by default. To
1558 enable it specify "-u" to Makefile.PL when you configure DBD::ODBC.
1559 e.g.,
1561 perl Makefile.PL -u
1563 Unicode - What is supported?
1565 As of version 1.17 DBD::ODBC has the following unicode support:
1567 SQL (introduced in 1.16_2)
1569 Unicode strings in calls to the "prepare" and "do" methods are
1570 supported so long as the "odbc_execdirect" attribute is not used.
1571 unicode connection strings (introduced in 1.16_2)
1573 Unicode connection strings are supported but you will need a DBI
1574 post 1.607 for that.
1575 column names
1577 Unicode column names are returned.
1578 bound columns (introduced in 1.15)
1580 If the DBMS reports the column as being a wide character (SQL_Wxxx)
1581 it will be bound as a wide character and any returned data will be
1582 converted from UTF-16 to UTF-8 and the UTF-8 flag will then be set
1583 on the data.
1584 bound parameters
1586 If the perl scalars you bind to parameters are marked UTF-8 and the
1587 DBMS reports the type as being a wide type or you bind the
1588 parameter as a wide type they will be converted to wide characters
1589 and bound as such.
1590 metadata calls like table_info, column_info
1592 As of DBD::ODBC 1.32_3 meta data calls accept Unicode strings.
1593 Since version 1.16_4, the default parameter bind type is SQL_WVARCHAR
1595 for unicode builds of DBD::ODBC. This only affects ODBC drivers which
1596 do not support SQLDescribeParam and only then if you do not
1597 specifically set a SQL type on the bind_param method call.
1598 The above Unicode support has been tested with the SQL Server, Oracle
1600 9.2+ and Postgres drivers on Windows and various Easysoft ODBC drivers
1601 on UNIX.
1602 Unicode - What is not supported?
1604 You cannot use unicode parameter names e.g.,
1606 select * from table where column = :unicode_param_name
1608 You cannot use unicode strings in calls to prepare if you set the
1610 odbc_execdirect attribute.
1611 You cannot use the iODBC driver manager with DBD::ODBC built for
1613 unicode.
1614 Unicode - Caveats
1616 For Unicode support on any platform in Perl you will need at least Perl
1618 5.8.1 - sorry but this is the way it is with Perl.
1619 The Unicode support in DBD::ODBC expects a WCHAR to be 2 bytes (as it
1621 is on Windows and as the ODBC specification suggests it is). Until ODBC
1622 specifies any other Unicode support it is not envisioned this will
1623 change. On UNIX there are a few different ODBC driver managers. I have
1624 only tested the unixODBC driver manager (http://www.unixodbc.org) with
1625 Unicode support and it was built with defaults which set WCHAR as 2
1626 bytes.
1627 I believe that the iODBC driver manager expects wide characters to be
1629 wchar_t types (which are usually 4 bytes) and hence DBD::ODBC will not
1630 work iODBC when built for unicode.
1631 The ODBC Driver must expect Unicode data specified in SQLBindParameter
1633 and SQLBindCol to be UTF-16 in local endianness. Similarly, in calls to
1634 SQLPrepareW, SQLDescribeColW and SQLDriverConnectW.
1635 You should be aware that once Unicode support is enabled it affects a
1637 number of DBI methods (some of which you might not expect). For
1638 instance, when listing tables, columns etc some drivers (e.g. Microsoft
1639 SQL Server) will report the column types as wide types even if the
1640 strings actually fit in 7-bit ASCII. As a result, there is an overhead
1641 for retrieving this column data as 2 bytes per character will be
1642 transmitted (compared with 1 when Unicode support is not enabled) and
1643 these strings will be converted into UTF-8 but will end up fitting (in
1644 most cases) into 7bit ASCII so a lot of conversion work has been
1645 performed for nothing. If you don't have Unicode table and column names
1646 or Unicode column data in your tables you are best disabling Unicode
1647 support.
1648 I am at present unsure if ChopBlanks processing on Unicode strings is
1650 working correctly on UNIX. If nothing else the construct L' ' in
1651 dbdimp.c might not work with all UNIX compilers. Reports of issues and
1652 patches welcome.
1653 Unicode implementation in DBD::ODBC
1655 DBD::ODBC uses the wide character versions of the ODBC API and the
1657 SQL_WCHAR ODBC type to support unicode in Perl.
1658 Wide characters returned from the ODBC driver will be converted to
1660 UTF-8 and the perl scalars will have the utf8 flag set (by using
1661 sv_utf8_decode).
1662 IMPORTANT
1664 Perl scalars which are UTF-8 and are sent through the ODBC API will be
1666 converted to UTF-16 and passed to the ODBC wide APIs or signalled as
1667 SQL_WCHARs (e.g., in the case of bound columns). Retrieved data which
1668 are wide characters are converted from UTF-16 to UTF-8. However, you
1669 should realise most ODBC drivers do not support UTF-16, ODBC only talks
1670 about wide characters being 2 bytes and UCS-2 and UCS-2 and UTF-16 are
1671 not the same. UCS-2 only supports Unicode characters in the first plane
1672 (the Basic Multilangual Plane or BMP) (code points U+0000 to U+FFFF),
1673 the most frequently used characters. So why does DBD::ODBC currently
1674 encode in UTF-16? For around 97% of Unicode characters in the range
1675 0-0xFFFF UCS-2 and UTF-16 are exactly the same (and where they differ
1676 there is no valid Unicode character as the range U+D800 to U+DFFF is
1677 reserved from use only as surrogate pairs). As the ODBC API currently
1678 uses UCS-2 it does not support Unicode characters with code points
1679 above 0xFFFF (if you know better I'd like to hear from you). However,
1680 because DBD::ODBC uses UTF-16 encoding you can still insert Unicode
1681 characters above 0xFFFF into your database and retrieve them back
1682 correctly but they may not being treated as a single Unicode character
1683 in your database e.g., a "select length(a_column) from table" with a
1684 single Unicode character above 0xFFFF may return 2 and not 1 so you
1685 cannot use database functions on that data like upper/lower/length etc
1686 but you can at least save the data in your database and get it back.
1687 When built for unicode, DBD::ODBC will always call SQLDriverConnectW
1689 (and not SQLDriverConnect) even if a) your connection string is not
1690 unicode b) you have not got a DBI later than 1.607, because unixODBC
1691 requires SQLDriverConnectW to be called if you want to call other
1692 unicode ODBC APIs later. As a result, if you build for unicode and pass
1693 ASCII strings to the connect method they will be converted to UTF-16
1694 and passed to SQLDriverConnectW. This should make no real difference to
1695 perl not using unicode connection strings.
1696 You will need a DBI later than 1.607 to support unicode connection
1698 strings because until post 1.607 there was no way for DBI to pass
1699 unicode strings to the DBD.
1700 Unicode and Oracle
1702 You have to set the environment variables "NLS_NCHAR=AL32UTF8" and
1704 "NLS_LANG=AMERICAN_AMERICA.AL32UTF8" (or any other language setting
1705 ending with ".AL32UTF8") before loading DBD::ODBC to make Oracle return
1706 Unicode data. (See also "Oracle and Unicode" in the POD of
1707 DBD::Oracle.)
1708 On Windows, using the Oracle ODBC Driver you have to enable the Force
1710 SQL_WCHAR support Workaround in the data source configuration to make
1711 Oracle return Unicode to a non-Unicode application. Alternatively, you
1712 can include "FWC=T" in your connect string.
1713 Unless you need to use ODBC, if you want Unicode support with Oracle
1715 you are better off using DBD::Oracle.
1716 Unicode and PostgreSQL
1718 See the odbc_utf8_on parameter to treat all strings as utf8.
1720 Some tests from the original DBD::ODBC 1.13 fail with PostgreSQL 8.0.3,
1722 so you may not want to use DBD::ODBC to connect to PostgreSQL 8.0.3.
1723 Unicode tests fail because PostgreSQL seems not to give any hints about
1725 Unicode, so all data is treated as non-Unicode.
1726 Unless you need to use ODBC, if you want Unicode support with Postgres
1728 you are better off with DBD::Pg as it has a specific attribute named
1729 "pg_enable_utf8" to enable Unicode support.
1730 Unicode and Easysoft ODBC Drivers
1732 We have tested the Easysoft SQL Server, Oracle and ODBC Bridge drivers
1734 with DBD::ODBC built for Unicode. All work as described without
1735 modification except for the Oracle driver you will need to set you
1736 NLS_LANG as mentioned above.
1737 Unicode and other ODBC drivers
1739 If you have a unicode-enabled ODBC driver and it works with DBD::ODBC
1741 let me know and I will include it here.
1742 ODBC Support in ODBC Drivers
1744 Drivers without SQLDescribeParam
1745 Some drivers do not support the "SQLDescribeParam" ODBC API (e.g.,
1747 Microsoft Access, FreeTDS).
1748 DBD::ODBC uses the "SQLDescribeParam" API when parameters are bound to
1750 your SQL to find the types of the parameters. If the ODBC driver does
1751 not support "SQLDescribeParam", DBD::ODBC assumes the parameters are
1752 "SQL_VARCHAR" or "SQL_WVARCHAR" types (depending on whether DBD::ODBC
1753 is built for unicode or not and whether your parameter is unicode
1754 data). In any case, if you bind a parameter and specify a SQL type this
1755 overrides any type DBD::ODBC would choose.
1756 For ODBC drivers which do not support "SQLDescribeParam" the default
1758 behavior in DBD::ODBC may not be what you want. To change the default
1759 parameter bind type set "odbc_default_bind_type". If, after that you
1760 have some SQL where you need to vary the parameter types used add the
1761 SQL type to the end of the "bind_param" method.
1762 use DBI qw(:sql_types);
1764 $h = DBI->connect;
1765 # set the default bound parameter type
1766 $h->{odbc_default_bind_type} = SQL_VARCHAR;
1767 # bind a parameter with a specific type
1768 $s = $h->prepare(q/insert into mytable values(?)/);
1769 $s->bind_param(1, "\x{263a}", SQL_WVARCHAR);
1770 MS SQL Server Query Notification
1772 Query notifications were introduced in SQL Server 2005 and SQL Server
1773 Native Client. Query notifications allow applications to be notified
1774 when data has changed.
1775 DBD::ODBC supports query notification with MS SQL Server using the
1777 additional prepare attributes odbc_qn_msgtxt, odbc_qn_options and
1778 odbc_qn_timeout. When you pass suitable values for these attributes to
1779 the prepare method, DBD::ODBC will make the appropriate SQLSetStmtAttr
1780 calls after the statement has been allocated.
1781 It is beyond the scope of this document to provide a tutorial on doing
1783 this but here are some notes that might help you get started.
1784 On SQL Server
1786 create database MyDatabase
1788 ALTER DATABASE MyDatabase SET ENABLE_BROKER
1789 use MyDatabase
1790 CREATE TABLE QNtest (a int NOT NULL PRIMARY KEY,
1791 b nchar(5) NOT NULL,
1792 c datetime NOT NULL)
1793 INSERT QNtest (a, b, c) SELECT 1, 'ALFKI', '19991212'
1794 CREATE QUEUE myQueue
1795 CREATE SERVICE myService ON QUEUE myQueue
1796 See L<http://technet.microsoft.com/en-us/library/ms175110%28v=SQL.105%29.aspx>
1797 You need to set these SQL Server permissions unless the subscriber is a
1799 sysadmin:
1800 GRANT RECEIVE ON QueryNotificationErrorsQueue TO "<login-for-subscriber>"
1802 GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "<login-for-subscriber>"
1803 To subscribe to query notification for this example:
1805 # Prepare the statement.
1807 # This is the SQL you want to know if the result changes later
1808 my $sth = $dbh->prepare(q/SELECT a, b, c FROM dbo.QNtest WHERE a = 1/,
1809 {odbc_qn_msgtxt => 'Message text',
1810 odbc_qn_options => 'service=myService',
1811 odbc_qn_timeout=> 430000});
1812 # Fetch and display the result set value.
1813 while ( my @row = $sth->fetchrow_array ) {
1814 print "@row\n";
1815 }
1816 # select * from sys.dm_qn_subscriptions will return a record now you are subscribed
1817 To wait for notification:
1819 # Avoid "String data, right truncation" error when retrieving
1821 # the message.
1822 $dbh->{LongReadLen} = 800;
1823 # This query generates a result telling you which query has changed
1825 # It will block until the timeout or the query changes
1826 my $sth = $dbh->prepare(q/WAITFOR (RECEIVE * FROM MyQueue)/);
1827 $sth->execute();
1828 # in the mean time someone does UPDATE dbo.QNtest SET c = '19981212' WHERE a = 1
1830 # Fetch and display the result set value.
1832 while ( my @row = $sth->fetchrow_array ) {
1833 print "@row\n";
1834 }
1835 # You now need to understand the result and look to decide which query has changed
1836 Version Control
1838 DBD::ODBC source code was under version control at svn.perl.org until
1839 April 2013 when svn.perl.org was closed down and it is now on github at
1840 https://github.com/perl5-dbi/DBD-ODBC.git.
1841 Contributing
1843 There are a number of ways you may help with the development and
1844 maintenance of this module:
1845 Submitting patches
1847 Please send me a git pull request or email a unified diff.
1848 Please try and include a test which demonstrates the fix/change
1850 working.
1851 Reporting installs
1853 Install CPAN::Reporter and report you installations. This is easy
1854 to do - see "CPAN Testers Reporting".
1855 Report bugs
1857 If you find what you believe is a bug then enter it into the
1858 <http://rt.cpan.org/Dist/Display.html?Name=DBD-ODBC> system. Where
1859 possible include code which reproduces the problem including any
1860 schema required and the versions of software you are using.
1861 If you are unsure whether you have found a bug report it anyway or
1863 post it to the dbi-users mailing list.
1864 pod comments and corrections
1866 If you find inaccuracies in the DBD::ODBC pod or have a comment
1867 which you think should be added then go to <http://annocpan.org>
1868 and submit them there. I get an email for every comment added and
1869 will review each one and apply any changes to the documentation.
1870 Review DBD::ODBC
1872 Add your review of DBD::ODBC on <http://cpanratings.perl.org>.
1873 If you are a member on ohloh then add your review or register your
1875 use of DBD::ODBC at <http://www.ohloh.net/projects/perl_dbd_odbc>.
1876 submit test cases
1878 Most DBDs are built against a single client library for the
1879 database.
1880 Unlike other DBDs, DBD::ODBC works with many different ODBC
1882 drivers. Although they all should be written with regard to the
1883 ODBC specification drivers have bugs and in some places the
1884 specification is open to interpretation. As a result, when changes
1885 are applied to DBD::ODBC it is very easy to break something in one
1886 ODBC driver.
1887 What helps enormously to identify problems in the many combinations
1889 of DBD::ODBC and ODBC drivers is a large test suite. I would
1890 greatly appreciate any test cases and in particular any new test
1891 cases for databases other than MS SQL Server.
1892 Test DBD::ODBC
1894 I have a lot of problems deciding when to move a development
1895 release to an official release since I get few test reports for
1896 development releases. What often happens is I call for testers on
1897 various lists, get a few and then get inundated with requests to do
1898 an official release. Then I do an official release and loads of rts
1899 appear out of nowhere and the cycle starts again.
1900 DBD::ODBC by its very nature works with many ODBC Drivers and it is
1902 impossible for me to have and test them all (this differs from
1903 other DBDs). If you depend on DBD::ODBC you should be interested in
1904 new releases and if you send me your email address suggesting you
1905 are prepared to be part of the DBD::ODBC testing network I will
1906 credit you in the Changes file and perhaps the main DBD::ODBC file.
1907 CPAN Testers Reporting
1909 Please, please, please (is that enough), consider installing
1910 CPAN::Reporter so that when you install perl modules a report of the
1911 installation success or failure can be sent to cpan testers. In this
1912 way module authors 1) get feedback on the fact that a module is being
1913 installed 2) get to know if there are any installation problems. Also
1914 other people like you may look at the test reports to see how
1915 successful they are before choosing the version of a module to install.
1916 See this guide on how to get started with sending test reports:
1918 <http://wiki.cpantesters.org/wiki/QuickStart>.
1919 Others/todo?
1921 Level 2
1922 SQLColumnPrivileges
1924 SQLProcedureColumns
1925 SQLProcedures
1926 SQLTablePrivileges
1927 SQLDrivers
1928 SQLNativeSql
1929 Random Links
1931 These are in need of sorting and annotating. Some are relevant only to
1932 ODBC developers.
1933 You can find DBD::ODBC on ohloh now at:
1935 <http://www.ohloh.net/projects/perl_dbd_odbc>
1937 If you use ohloh and DBD::ODBC please say you use it and rate it.
1939 There is a good search engine for the various Perl DBI lists at the
1941 following URLS:
1942 <http://perl.markmail.org/search/list:org.perl.dbi-users>
1944 <http://perl.markmail.org/search/list:org.perl.dbi-dev>
1946 <http://perl.markmail.org/search/list:org.perl.dbi-announce>
1948 <http://www.syware.com>
1950 <http://www.microsoft.com/odbc>
1952 For Linux/Unix folks, compatible ODBC driver managers can be found at:
1954 <http://www.unixodbc.org> (unixODBC source and rpms)
1956 <http://www.iodbc.org> (iODBC driver manager source)
1958 For Linux/Unix folks, you can checkout the following for ODBC Drivers
1960 and Bridges:
1961 <http://www.easysoft.com>
1963 <http://www.openlinksw.com>
1965 <http://www.datadirect.com>
1967 <http://www.atinet.com>
1969 Some useful tutorials:
1971 Debugging Perl DBI:
1972 <http://www.easysoft.com/developer/languages/perl/dbi-debugging.html>
1974 Enabling ODBC support in Perl with Perl DBI and DBD::ODBC:
1976 <http://www.easysoft.com/developer/languages/perl/dbi_dbd_odbc.html>
1978 Perl DBI/DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and
1980 Connection:
1981 <http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html>
1983 Perl DBI/DBD::ODBC Tutorial Part 2 - Introduction to retrieving data
1985 from your database:
1986 <http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html>
1988 Perl DBI/DBD::ODBC Tutorial Part 3 - Connecting Perl on UNIX or Linux
1990 to Microsoft SQL Server:
1991 <http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html>
1993 Perl DBI - Put Your Data On The Web:
1995 <http://www.easysoft.com/developer/languages/perl/tutorial_data_web.html>
1997 Multiple Active Statements (MAS) and DBD::ODBC
1999 <http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html>
2001 64-bit ODBC
2003 <http://www.easysoft.com/developer/interfaces/odbc/64-bit.html>
2005 How do I insert Unicode supplementary characters into SQL Server from
2007 Perl?
2008 <http://www.easysoft.com/support/kb/kb01043.html>
2010 Some Common Unicode Problems and Solutions using Perl DBD::ODBC and MS
2012 SQL Server
2013 <http://www.easysoft.com/developer/languages/perl/sql-server-unicode.html>
2015 and a version possibly kept more up to date:
2017 <https://github.com/mjegh/dbd_odbc_sql_server_unicode/blob/master/common_problems.pod>
2019 How do I use SQL Server Query Notifications from Linux and UNIX?
2021 <http://www.easysoft.com/support/kb/kb01069.html>
2023 Frequently Asked Questions
2025 Frequently asked questions are now in DBD::ODBC::FAQ. Run "perldoc
2026 DBD::ODBC::FAQ" to view them.
2027
2029 You should consult the documentation for the ODBC Driver Manager you
2030 are using.
2031
2033 DBI
2034 Test::Simple
2036
2038 None known.
2039
2041 None known other than the deviations from the DBI specification
2042 mentioned above in "Deviations from the DBI specification".
2043 Please report any to me via the CPAN RT system. See
2045 <http://rt.cpan.org/> for more details.
2046
2048 Tim Bunce
2049 Jeff Urlwin
2051 Thomas K. Wenrich
2053 Martin J. Evans
2055
2057 This program is free software; you can redistribute it and/or modify it
2058 under the same terms as Perl itself. See perlartistic. This program is
2059 distributed in the hope that it will be useful, but WITHOUT ANY
2060 WARRANTY; without even the implied warranty of MERCHANTABILITY or
2061 FITNESS FOR A PARTICULAR PURPOSE.
2062 Portions of this software are Copyright Tim Bunce, Thomas K. Wenrich,
2064 Jeff Urlwin and Martin J. Evans - see the source.
2065
2067 DBI
2068 DBD::ODBC can be used with many ODBC drivers to many different
2070 databases. If you want a generic DBD for multiple databases DBD::ODBC
2071 is probably for you. If you are only accessing a single database then
2072 you might want to look for DBD::my_database (e.g. DBD::Oracle) as
2073 database specific DBDs often have more functionality.
2074 DBIx::LogAny or DBIx::Log4perl for logging DBI method calls, SQL,
2076 parameters and results.
2077perl v5.36.0 2023-01-20 ODBC(3)