1ODBC(3)               User Contributed Perl Documentation              ODBC(3)
2
3
4

NAME

6       DBD::ODBC - ODBC Driver for DBI
7

VERSION

9       This documentation refers to DBD::ODBC version 1.61.
10

WARNING

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
18       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
22       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
27       2 if you set odbc_force_bind_type then all parameters are bound as you
28         specified.
29
30       3 if you override the parameter type in the bind_param method, the
31         type you specified would be used.
32
33       4 if the driver does not support SQLDescribeParam or SQLDescribeParam
34         was called and failed then the bind type defaulted as in 1.
35
36       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
41       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
46       What might this might mean to you?
47
48       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
59       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
64       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

SYNOPSIS

71         use DBI;
72
73         $dbh = DBI->connect('dbi:ODBC:DSN=mydsn', 'user', 'password');
74
75       See DBI for more information.
76

DESCRIPTION

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
82       The FAQs have also moved to DBD::ODBC::FAQ.pm. To access the FAQs use
83       "perldoc DBD::ODBC::FAQ".
84
85   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
93   DBI attribute handling
94       If a DBI defined attribute is not mentioned here it behaves as per the
95       DBI specification.
96
97       ReadOnly (boolean)
98
99       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
105       Note: There is no equivalent of setting ReadOnly on a statement handle
106       in ODBC.
107
108       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
113       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
119       This attribute requires DBI version 1.55 or better.
120
121   Private attributes common to connection and statement handles
122       odbc_ignore_named_placeholders
123
124       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
128        $dbh->{odbc_ignore_named_placeholders} = 1;
129        $dbh->do("create trigger foo as if :new.x <> :old.x then ... etc");
130
131       Without this, DBD::ODBC will think :new and :old are placeholders for
132       binding and get confused.
133
134       odbc_default_bind_type
135
136       This value defaults to 0.
137
138       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
148       N.B If you call the "bind_param" method with a SQL type this overrides
149       everything else above.
150
151       odbc_force_bind_type
152
153       This value defaults to 0.
154
155       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
159       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
168       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
172       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
178       N.B If you call the "bind_param" method with a SQL type this overrides
179       everything else above.
180
181       odbc_force_rebind
182
183       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
193       odbc_async_exec
194
195       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
201       odbc_query_timeout
202
203       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
210         { odbc_query_timeout => 30 }
211
212       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
216       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
220       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
225       Note that some drivers may not support this attribute.
226
227       See t/20SqlServer.t for an example.
228
229       odbc_putdata_start
230
231       "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
238       The default for odbc_putdata_start is 32768 because this value was
239       hard-coded in DBD::ODBC until 1.16_1.
240
241       odbc_column_display_size
242
243       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
249       The default for odbc_column_display_size is 2001 because this value was
250       hard-coded in DBD::ODBC until 1.17_3.
251
252       odbc_utf8_on
253
254       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
261       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
265       odbc_describe_parameters
266
267       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
271       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
275       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
279       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
283       You can also set this attribute in the attributes passed to the prepare
284       method.
285
286       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
293   Private methods common to connection and statement handles
294       odbc_getdiagrec
295
296         @diags = $handle->odbc_getdiagrec($record_number);
297
298       Introduced in 1.34_3.
299
300       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
309       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
314       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
318       NOTE: calling this method does not clear DBI's error values as usually
319       happens.
320
321       odbc_getdiagfield
322
323         $diag = $handle->odbc_getdiagfield($record, $identifier);
324
325       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
331         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
347       DBD::ODBC exports these constants as 'diags' e.g.,
348
349         use DBD::ODBC qw(:diags);
350
351       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
355       NOTE: calling this method does not clear DBI's error values as usually
356       happens.
357
358   Private connection attributes
359       odbc_err_handler
360
361       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
368       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
372       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
376       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
381       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
385         $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
397       odbc_SQL_ROWSET_SIZE
398
399       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
403       The ODBC default for SQL_ROWSET_SIZE is 1.
404
405       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
410       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
414       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
420       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
426       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
430       Thanks to Andrew Brown for the original patch.
431
432       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
459       odbc_exec_direct
460
461       Force DBD::ODBC to use "SQLExecDirect" instead of
462       "SQLPrepare"/"SQLExecute".
463
464       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
468       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
476       There are currently two ways to get this:
477
478           $dbh->prepare($sql, { odbc_exec_direct => 1});
479
480       and
481
482           $dbh->{odbc_exec_direct} = 1;
483
484       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
489       odbc_SQL_DRIVER_ODBC_VER
490
491       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
494       odbc_cursortype
495
496       This allows multiple concurrent statements on SQL*Server.  In your
497       connect, add
498
499         { odbc_cursortype => 2 }.
500
501       If you are using DBI > 1.41, you should also be able to use
502
503        { odbc_cursortype => DBI::SQL_CURSOR_DYNAMIC }
504
505       instead.  For example:
506
507           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
517       See t/20SqlServer.t for an example.
518
519       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
523       odbc_has_unicode
524
525       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
529       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
533       When odbc_has_unicode is 1, DBD::ODBC will:
534
535       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
540       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
549       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
553       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
561       NOTE: You will need at least Perl 5.8.1 to use UNICODE with DBD::ODBC.
562
563       NOTE: Binding of unicode output parameters is coded but untested.
564
565       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
573       You can also specify that you want UNICODE support by setting the
574       "DBD_ODBC_UNICODE" environment variable prior to install:
575
576         export DBD_ODBC_UNICODE=1
577         cpanm DBD::ODBC
578
579       UNICODE support in ODBC Drivers differs considerably. Please read the
580       README.unicode file for further details.
581
582       odbc_out_connect_string
583
584       After calling the connect method this will be the ODBC driver's out
585       connection string - see documentation on SQLDriverConnect.
586
587       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
591       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
599       odbc_version
600
601       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
607         $dbh = DBI->connect("dbi:ODBC:$DSN", $user, $pass,
608                             { odbc_version =>2});
609
610       odbc_driver_complete
611
612       This attribute was added to DBD::ODBC in 1.32_2.
613
614       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
617       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
629       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
633       An example is:
634
635         my $h = DBI->connect('dbi:ODBC:DRIVER={SQL Server}', "username", "password",
636                              {odbc_driver_complete => 1});
637
638       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
642       odbc_batch_size
643
644       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
649       odbc_array_operations
650
651       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
656       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
664       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
670       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
677       odbc_taf_callback
678
679       NOTE: this is experimental until I at least see more than one ODBC
680       driver which supports TAF.
681
682       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
687       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
691       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
695         sub taf_handler {
696          my ($dbh, $event, $type) = @_;
697          # do something here
698         }
699
700       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
710       Here is a commented example:
711
712         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
720         sub taf_handler {
721            my ($dbh, $event, $type) = @_;
722
723            #print "taf_handler $dbh, $event, $type\n";
724
725            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
754       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
763       odbc_trace_file
764
765       Specify the name and path to a file you want ODBC API trace information
766       to be written to. See "odbc_trace".
767
768       odbc_trace
769
770       Enable or disable ODBC API tracing. Set to 1 to enable and 0 to
771       disable.
772
773       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
778       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
782       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
787       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
792   Private statement attributes
793       odbc_more_results
794
795       Use this attribute to determine if there are more result sets
796       available.
797
798       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
804       Use odbc_more_results as follows:
805
806         do {
807            my @row;
808            while (@row = $sth->fetchrow_array()) {
809               # do stuff here
810            }
811         } while ($sth->{odbc_more_results});
812
813       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
817       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
822   Private statement methods
823       odbc_rows
824
825       This method was added in 1.42_1.
826
827       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
832       From DBD::ODBC 1.42_1 DBD::ODBC
833
834       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
838       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
841       At some stage DBI may change to fix the issue this works around.
842
843       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
847       odbc_lob_read
848
849         $chrs_or_bytes_read = $sth->odbc_lob_read($column_no, \$lob, $length, \%attr);
850
851       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
856       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
861       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
869       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
876       You can retrieve a lob in chunks like this:
877
878         $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
883       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
887       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
892       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
900       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
905   Private DBD::ODBC Functions
906       You use DBD::ODBC private functions like this:
907
908         $dbh->func(arg, private_function_name, @args);
909
910       GetInfo
911
912       This private function is now superseded by DBI's get_info method.
913
914       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
917         $value = $dbh->func(6, 'GetInfo');
918
919       which returns the "SQL_DRIVER_NAME".
920
921       This function returns a scalar value, which can be a numeric or string
922       value depending on the information value requested.
923
924       GetTypeInfo
925
926       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
930       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
935       e.g.
936
937         use DBI qw(:sql_types);
938
939         $sth = $dbh->func(SQL_ALL_TYPES, GetTypeInfo);
940         DBI::dump_results($sth);
941
942       This function returns a DBI statement handle for the SQLGetTypeInfo
943       result-set containing many columns of type attributes (see ODBC
944       specification).
945
946       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
952       GetFunctions
953
954       This function maps to the ODBC SQLGetFunctions API which returns
955       information on whether a function is supported by the ODBC driver.
956
957       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
962       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
966       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
970       e.g.
971
972           my @x = $dbh->func(0,"GetFunctions");
973           print "SQLDescribeParam is supported\n" if ($x[58]);
974
975       or
976
977           print "SQLDescribeParam is supported\n"
978               if $dbh->func(58, "GetFunctions");
979
980       GetStatistics
981
982       This private function is now superseded by DBI's statistics_info
983       method.
984
985       See the ODBC specification for the SQLStatistics API.  You call
986       SQLStatistics like this:
987
988         $dbh->func($catalog, $schema, $table, $unique, 'GetStatistics');
989
990       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
997       GetForeignKeys
998
999       This private function is now superseded by DBI's foreign_key_info
1000       method.
1001
1002       See the ODBC specification for the SQLForeignKeys API.  You call
1003       SQLForeignKeys like this:
1004
1005         $dbh->func($pcatalog, $pschema, $ptable,
1006                    $fcatalog, $fschema, $ftable,
1007                    "GetForeignKeys");
1008
1009       GetPrimaryKeys
1010
1011       This private function is now superseded by DBI's primary_key_info
1012       method.
1013
1014       See the ODBC specification for the SQLPrimaryKeys API.  You call
1015       SQLPrimaryKeys like this:
1016
1017         $dbh->func($catalog, $schema, $table, "GetPrimaryKeys");
1018
1019       data_sources
1020
1021       This private function is now superseded by DBI's data_sources method
1022       and was finally removed in 1.49_1
1023
1024       GetSpecialColumns
1025
1026       See the ODBC specification for the SQLSpecialColumns API.  You call
1027       SQLSpecialColumns like this:
1028
1029         $dbh->func($identifier, $catalog, $schema, $table, $scope,
1030                    $nullable, 'GetSpecialColumns');
1031
1032       Handled as of version 0.28
1033
1034       ColAttributes
1035
1036       This private function is now superseded by DBI's statement attributes
1037       NAME, TYPE, PRECISION, SCALE, NULLABLE etc).
1038
1039       See the ODBC specification for the SQLColAttributes API.  You call
1040       SQLColAttributes like this:
1041
1042         $sth->func($column, $ftype, "ColAttributes");
1043
1044         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
1064       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
1068       DescribeCol
1069
1070       Removed in DBD::ODBC 1.40_3
1071
1072       Use the DBI's statement attributes NAME, TYPE, PRECISION, SCALE,
1073       NULLABLE etc instead.
1074
1075   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
1079       DiscardString
1080
1081       See DBI's sql_type_cast utility function.
1082
1083       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
1089       NOTE: post DBD::ODBC 1.37, DBD::ODBC binds all SQL_INTEGER columns as
1090       SQL_C_LONG and DiscardString is irrelevant.
1091
1092       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
1098       NOTE: For DiscardString you need at least DBI 1.611.
1099
1100       StrictlyTyped
1101
1102       See DBI's sql_type_cast utility function.
1103
1104       See "DiscardString" above.
1105
1106       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
1110       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
1115       NOTE: For StrictlyTyped you need at least DBI 1.611.
1116
1117       TreatAsLOB
1118
1119       See "odbc_lob_read".
1120
1121   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
1128       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
1135       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
1139       To enable tracing of particular flags you use:
1140
1141         $h->trace($h->parse_trace_flags('SQL|odbcconnection'));
1142         $h->trace($h->parse_trace_flags('1|odbcunicode'));
1143
1144       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
1148       If you want to enable a DBD::ODBC private trace flag before connecting
1149       you need to do something like:
1150
1151         use DBD::ODBC;
1152         DBI->trace(DBD::ODBC->parse_trace_flag('odbcconnection'));
1153
1154       or
1155
1156         use DBD::ODBC;
1157         DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode'));
1158
1159       or
1160
1161         DBI_TRACE=odbcconnection|odbcunicode perl myscript.pl
1162
1163       From DBI 1.617 you can output only DBD::ODBC trace messages using
1164
1165         DBI_TRACE=DBD perl myscript.pl
1166
1167       DBD::ODBC outputs tracing at levels 3 and above (as levels 1 and 2 are
1168       reserved for DBI).
1169
1170       For comprehensive tracing of DBI method calls without all the DBI
1171       internals see DBIx::Log4perl.
1172
1173   Deviations from the DBI specification
1174       last_insert_id
1175
1176       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
1182       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
1188       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
1193       Comments in SQL
1194
1195       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
1200         select '1' /* placeholder ? in comment */
1201         select -- named placeholder :named in comment
1202           '1'
1203
1204       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
1211       do
1212
1213       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
1217       DBD::ODBC implements "do" by calling SQLExecDirect in ODBC and not
1218       SQLPrepare followed by SQLExecute so "do" is not the same as:
1219
1220         $dbh->prepare($sql)->execute()
1221
1222       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
1228       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
1233       Mixed placeholder types
1234
1235       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
1242         Can't mix placeholder styles (1/2)
1243
1244       Using the same placeholder more than once
1245
1246       DBD::ODBC does not support (currently) the use of one named placeholder
1247       more than once in a single SQL string. i.e.,
1248
1249         insert into foo values (:bar, :p1, :p2, :bar);
1250
1251       is not supported because 'bar' is used more than once but:
1252
1253         insert into foo values(:bar, :p1, :p2)
1254
1255       is ok. If you do the former you will get an error like:
1256
1257         DBD::ODBC does not yet support binding a named parameter more than once
1258
1259       Binding named placeholders
1260
1261       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
1266         prepare("insert into mytable values (:fred)");
1267         bind_param(":foo", 1);
1268
1269       DBD::ODBC does not support binding named parameters with the ':'
1270       introducer.  In the above example you must use:
1271
1272         bind_param("foo", 1);
1273
1274       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
1278       Sticky Parameter Types
1279
1280       The DBI specification post 1.608 says in bind_param:
1281
1282         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
1287       DBD::ODBC does allow a parameter to be rebound with another data type
1288       as ODBC inherently allows this. Therefore you can do:
1289
1290         # 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
1300       disconnect and transactions
1301
1302       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
1308       execute_for_fetch and execute_array
1309
1310       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
1318       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
1324       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
1334       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
1339       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
1348       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
1367       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
1373       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
1379       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
1383       o DO NOT attempt to do an insert/update/delete and a select in the same
1384       SQL with execute_array e.g.,
1385
1386         SET IDENTITY_INSERT mytable ON
1387         insert into mytable (id, name) values (?,?)
1388         SET IDENTITY_INSERT mytable OFF
1389         SELECT SCOPE_IDENTITY()
1390
1391       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
1394       type_info_all
1395
1396       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
1402       Binding Columns
1403
1404       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
1413       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
1417         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
1424       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
1427       Significant changes occurred in DBD::ODBC at 1.38_1 for binding
1428       columns. Please see the Changes file.
1429
1430       bind_param
1431
1432       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
1436       The \%attr parameter can be used to hint at the data type the
1437       placeholder should have. This is rarely needed.
1438
1439       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
1444       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
1448       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
1453       See
1454       http://technet.microsoft.com/en-US/library/ms190234%28v=SQL.90%29.aspx
1455
1456       The only times when you might want to add a type are:
1457
1458       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
1464       2. If for some reason your driver describes the parameter incorrectly.
1465       It is difficult to describe an example of this.
1466
1467       3. If SQLDescribeParam is supported but fails e.g., MS SQL Server has
1468       problems with SQL like "select myfunc(?) where 1 = 1".
1469
1470       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
1474       tables and table_info
1475
1476       These are not really deviations from the DBI specification but a
1477       clarification of a DBI optional feature.
1478
1479       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
1486       SQLGetInfo for SQL_ACCESSIBLE_TABLES can affect what tables you can
1487       list.
1488
1489       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
1495       Although DBD::ODBC supports all the usage cases listed by DBI, your
1496       ODBC driver may not.
1497
1498   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
1503       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
1510       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
1515           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
1519           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
1523           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
1527           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
1539       iODBC
1540           The wide character versions expect and return wchar_t types.
1541
1542       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
1547       Enabling and Disabling Unicode support
1548
1549       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
1553       e.g.,
1554
1555         perl Makfile.PL -nou
1556
1557       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
1560       e.g.,
1561
1562         perl Makefile.PL -u
1563
1564       Unicode - What is supported?
1565
1566       As of version 1.17 DBD::ODBC has the following unicode support:
1567
1568       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
1572       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
1576       column names
1577           Unicode column names are returned.
1578
1579       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
1585       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
1591       metadata calls like table_info, column_info
1592           As of DBD::ODBC 1.32_3 meta data calls accept Unicode strings.
1593
1594       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
1599       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
1603       Unicode - What is not supported?
1604
1605       You cannot use unicode parameter names e.g.,
1606
1607         select * from table where column = :unicode_param_name
1608
1609       You cannot use unicode strings in calls to prepare if you set the
1610       odbc_execdirect attribute.
1611
1612       You cannot use the iODBC driver manager with DBD::ODBC built for
1613       unicode.
1614
1615       Unicode - Caveats
1616
1617       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
1620       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
1628       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
1632       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
1636       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
1649       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
1654       Unicode implementation in DBD::ODBC
1655
1656       DBD::ODBC uses the wide character versions of the ODBC API and the
1657       SQL_WCHAR ODBC type to support unicode in Perl.
1658
1659       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
1663       IMPORTANT
1664
1665       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
1688       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
1697       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
1701       Unicode and Oracle
1702
1703       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
1709       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
1714       Unless you need to use ODBC, if you want Unicode support with Oracle
1715       you are better off using DBD::Oracle.
1716
1717       Unicode and PostgreSQL
1718
1719       See the odbc_utf8_on parameter to treat all strings as utf8.
1720
1721       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
1724       Unicode tests fail because PostgreSQL seems not to give any hints about
1725       Unicode, so all data is treated as non-Unicode.
1726
1727       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
1731       Unicode and Easysoft ODBC Drivers
1732
1733       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
1738       Unicode and other ODBC drivers
1739
1740       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
1743   ODBC Support in ODBC Drivers
1744       Drivers without SQLDescribeParam
1745
1746       Some drivers do not support the "SQLDescribeParam" ODBC API (e.g.,
1747       Microsoft Access, FreeTDS).
1748
1749       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
1757       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
1763         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
1771   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
1776       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
1782       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
1785       On SQL Server
1786
1787         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
1798       You need to set these SQL Server permissions unless the subscriber is a
1799       sysadmin:
1800
1801         GRANT RECEIVE ON QueryNotificationErrorsQueue TO "<login-for-subscriber>"
1802         GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "<login-for-subscriber>"
1803
1804       To subscribe to query notification for this example:
1805
1806         # 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
1818       To wait for notification:
1819
1820         # Avoid "String data, right truncation" error when retrieving
1821         # the message.
1822         $dbh->{LongReadLen} = 800;
1823
1824         # 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
1829         # in the mean time someone does UPDATE dbo.QNtest SET c = '19981212' WHERE a = 1
1830
1831         # 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
1837   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
1842   Contributing
1843       There are a number of ways you may help with the development and
1844       maintenance of this module:
1845
1846       Submitting patches
1847           Please send me a git pull request or email a unified diff.
1848
1849           Please try and include a test which demonstrates the fix/change
1850           working.
1851
1852       Reporting installs
1853           Install CPAN::Reporter and report you installations. This is easy
1854           to do - see "CPAN Testers Reporting".
1855
1856       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
1862           If you are unsure whether you have found a bug report it anyway or
1863           post it to the dbi-users mailing list.
1864
1865       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
1871       Review DBD::ODBC
1872           Add your review of DBD::ODBC on <http://cpanratings.perl.org>.
1873
1874           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
1877       submit test cases
1878           Most DBDs are built against a single client library for the
1879           database.
1880
1881           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
1888           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
1893       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
1901           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
1908   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
1917       See this guide on how to get started with sending test reports:
1918       <http://wiki.cpantesters.org/wiki/QuickStart>.
1919
1920   Others/todo?
1921       Level 2
1922
1923           SQLColumnPrivileges
1924           SQLProcedureColumns
1925           SQLProcedures
1926           SQLTablePrivileges
1927           SQLDrivers
1928           SQLNativeSql
1929
1930   Random Links
1931       These are in need of sorting and annotating. Some are relevant only to
1932       ODBC developers.
1933
1934       You can find DBD::ODBC on ohloh now at:
1935
1936       <http://www.ohloh.net/projects/perl_dbd_odbc>
1937
1938       If you use ohloh and DBD::ODBC please say you use it and rate it.
1939
1940       There is a good search engine for the various Perl DBI lists at the
1941       following URLS:
1942
1943       <http://perl.markmail.org/search/list:org.perl.dbi-users>
1944
1945       <http://perl.markmail.org/search/list:org.perl.dbi-dev>
1946
1947       <http://perl.markmail.org/search/list:org.perl.dbi-announce>
1948
1949       <http://www.syware.com>
1950
1951       <http://www.microsoft.com/odbc>
1952
1953       For Linux/Unix folks, compatible ODBC driver managers can be found at:
1954
1955       <http://www.unixodbc.org> (unixODBC source and rpms)
1956
1957       <http://www.iodbc.org> (iODBC driver manager source)
1958
1959       For Linux/Unix folks, you can checkout the following for ODBC Drivers
1960       and Bridges:
1961
1962       <http://www.easysoft.com>
1963
1964       <http://www.openlinksw.com>
1965
1966       <http://www.datadirect.com>
1967
1968       <http://www.atinet.com>
1969
1970   Some useful tutorials:
1971       Debugging Perl DBI:
1972
1973       <http://www.easysoft.com/developer/languages/perl/dbi-debugging.html>
1974
1975       Enabling ODBC support in Perl with Perl DBI and DBD::ODBC:
1976
1977       <http://www.easysoft.com/developer/languages/perl/dbi_dbd_odbc.html>
1978
1979       Perl DBI/DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and
1980       Connection:
1981
1982       <http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html>
1983
1984       Perl DBI/DBD::ODBC Tutorial Part 2 - Introduction to retrieving data
1985       from your database:
1986
1987       <http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html>
1988
1989       Perl DBI/DBD::ODBC Tutorial Part 3 - Connecting Perl on UNIX or Linux
1990       to Microsoft SQL Server:
1991
1992       <http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html>
1993
1994       Perl DBI - Put Your Data On The Web:
1995
1996       <http://www.easysoft.com/developer/languages/perl/tutorial_data_web.html>
1997
1998       Multiple Active Statements (MAS) and DBD::ODBC
1999
2000       <http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html>
2001
2002       64-bit ODBC
2003
2004       <http://www.easysoft.com/developer/interfaces/odbc/64-bit.html>
2005
2006       How do I insert Unicode supplementary characters into SQL Server from
2007       Perl?
2008
2009       <http://www.easysoft.com/support/kb/kb01043.html>
2010
2011       Some Common Unicode Problems and Solutions using Perl DBD::ODBC and MS
2012       SQL Server
2013
2014       <http://www.easysoft.com/developer/languages/perl/sql-server-unicode.html>
2015
2016       and a version possibly kept more up to date:
2017
2018       <https://github.com/mjegh/dbd_odbc_sql_server_unicode/blob/master/common_problems.pod>
2019
2020       How do I use SQL Server Query Notifications from Linux and UNIX?
2021
2022       <http://www.easysoft.com/support/kb/kb01069.html>
2023
2024   Frequently Asked Questions
2025       Frequently asked questions are now in DBD::ODBC::FAQ. Run "perldoc
2026       DBD::ODBC::FAQ" to view them.
2027

CONFIGURATION AND ENVIRONMENT

2029       You should consult the documentation for the ODBC Driver Manager you
2030       are using.
2031

DEPENDENCIES

2033       DBI
2034
2035       Test::Simple
2036

INCOMPATIBILITIES

2038       None known.
2039

BUGS AND LIMITATIONS

2041       None known other than the deviations from the DBI specification
2042       mentioned above in "Deviations from the DBI specification".
2043
2044       Please report any to me via the CPAN RT system. See
2045       <http://rt.cpan.org/> for more details.
2046

AUTHOR

2048       Tim Bunce
2049
2050       Jeff Urlwin
2051
2052       Thomas K. Wenrich
2053
2054       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
2063       Portions of this software are Copyright Tim Bunce, Thomas K. Wenrich,
2064       Jeff Urlwin and Martin J. Evans - see the source.
2065

SEE ALSO

2067       DBI
2068
2069       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
2075       DBIx::LogAny or DBIx::Log4perl for logging DBI method calls, SQL,
2076       parameters and results.
2077
2078
2079
2080perl v5.36.0                      2023-01-20                           ODBC(3)
Impressum