Debian Codegraph @

FAQ

raw
1 use strict;
2
3 =encoding utf8
4
5 =head1 NAME
6
7 DBD::ODBC::FAQ - Frequently Asked Questions for DBD::ODBC
8
9 =head1 SYNOPSIS
10
11 perldoc DBD::ODBC::FAQ
12
13 =head1 VERSION
14
15 ($Revision$)
16
17 =head1 QUESTIONS
18
19 =head2 How do I read more than N characters from a Memo | BLOB | LONG field?
20
21 See LongReadLen in the DBI docs.
22
23 Example:
24
25 $dbh->{LongReadLen} = 20000;
26 $sth = $dbh->prepare("select long_col from big_table");
27 $sth->execute;
28 etc
29
30 =head2 What is DBD::ODBC?
31
32 =head2 Why can't I connect?
33
34 =head2 Do I need an ODBC driver?
35
36 =head2 What is the ODBC driver manager?
37
38 These, general questions lead to needing definitions.
39
40 =over 4
41
42 =item ODBC Driver
43
44 The ODBC Driver is the driver that the ODBC manager uses to connect
45 and interact with the RDBMS. You B<DEFINITELY> need this to connect to
46 any database. For Win32, they are plentiful and installed with many
47 applications. For Linux/Unix, you can find a fairly comprehensive list
48 at L<http://www.unixodbc.org/drivers.html>.
49
50 =item ODBC Driver Manager
51
52 The ODBC driver manager is the interface between an ODBC application
53 (DBD::ODBC in this case) and the ODBC driver. The driver manager
54 principally provides the ODBC API so ODBC applications may link with a
55 single shared object (or dll) and be able to talk to a range of ODBC
56 drivers. At run time the application provides a connection string
57 which defines the ODBC data source it wants to connect to and this in
58 turn defines the ODBC driver which will handle this data source. The
59 driver manager loads the requested ODBC driver and passes all ODBC API
60 calls on to the driver. In this way, an ODBC application can be built
61 and distributed without knowing which ODBC driver it will be using.
62
63 However, this is a rather simplistic description of what the driver
64 manager does. The ODBC driver manager also:
65
66 * Controls a repository of installed ODBC drivers (on UNIX this is the
67 file odbcinst.ini).
68
69 * Controls a repository of defined ODBC data sources (on UNIX these are
70 the files odbc.ini and .odbc.ini).
71
72 * Provides the ODBC driver APIs (SQLGetPrivateProfileString and
73 SQLWritePrivateProfileString) to read and write ODBC data source
74 attributes.
75
76 * Handles ConfigDSN which the driver exports to configure data
77 sources.
78
79 * Provides APIs to install and uninstall drivers (SQLInstallDriver).
80
81 * Maps ODBC versions e.g. so an ODBC 2.0 application can work with an
82 ODBC 3.0 driver and vice versa.
83
84 * Maps ODBC states between different versions of ODBC.
85
86 * Provides a cursor library for drivers which only support
87 forward-only cursors.
88
89 * Provides SQLDataSources and SQLDrivers so an application can find
90 out what ODBC drivers are installed and what ODBC data sources are
91 defined.
92
93 * Provides an ODBC administrator which driver writers can use to
94 install ODBC drivers and users can use to define ODBC data sources.
95
96 The ODBC Driver Manager is the piece of software which interacts with
97 the drivers for the application. It "hides" some of the differences
98 between the drivers (i.e. if a function call is not supported by a
99 driver, it 'hides' that and informs the application that the call is
100 not supported. DBD::ODBC needs this to talk to drivers.
101
102 Under Win32, you usually get the ODBC Driver Manager as part of the
103 OS. Under Unix/Linux you may have to find and build the driver
104 manager yourself. The two main driver managers for Unix are unixODBC
105 (L<http://www.unixodbc.org>) and iODBC (L<http://www.iodbc.org>).
106
107 B<It is strongly advised you get an ODBC Driver Manager before trying to
108 build DBD::ODBC unless you intend linking DBD::ODBC directly with your
109 driver.>
110
111 For a reasonable description of ODBC on Unix/Linux see
112 L<http://www.easysoft.com/developer/interfaces/odbc/linux.html>
113
114 =item DBD::ODBC
115
116 DBD::ODBC uses the driver manager to talk to the ODBC driver(s) on
117 your system. You need both a driver manager and driver installed and
118 tested before working with DBD::ODBC. You need to have a DSN (see
119 below) configured and B<TESTED> before being able to test DBD::ODBC.
120
121 =item DSN (Data Source Name)
122
123
124 The DSN is a way of referring to a particular driver and database by
125 any name you wish. The DSN is usually a key to a list of attributes
126 the ODBC driver needs to connect to the database (e.g. ip address and
127 port) but there is always a key which names the driver so the driver
128 manager knows which driver to use with which data source. Do no
129 confuse DSNs with ODBC connection strings or DBI's "$data_source" (the
130 first argument to L<DBI/connect>.
131
132 The $data_source argument to DBI is composed of 'dbi:DRIVER:something_else'
133 where DRIVER is the name of the DBD driver you want to use (ODBC of
134 course for DBD::ODBC). The "something_else" for DBD::ODBC can be a DSN
135 name or it can be a normal ODBC connection string.
136
137 An ODBC connection string consists of attribute/value pairs separated
138 with semicolons (;). You can replace "something_else" above with a
139 normal ODBC connection string but as a special case for DBD::ODBC you can
140 just use the DSN name without the usual ODBC connection string prefix
141 of "DSN=dsn_name".
142
143 e.g.
144
145 =over
146
147 =item dbi:ODBC:DSN=fred
148
149 ODBC connection string using fred DSN
150
151 =item dbi:ODBC:fred
152
153 Same as above (a special case).
154
155 =item dbi:ODBC:Driver={blah blah driver};Host=1.2.3.4;Port=1000;
156
157 This is known as a DSN-less connection string for obvious reasons.
158
159 =back
160
161 =back
162
163 =head2 Where do I get an ODBC driver manager for Unix/Linux?
164
165 DBD::ODBC used to come bundled with a driver manager but this became
166 inconvenient when the driver manager was updated.
167
168 The two main ODBC Driver Managers for Unix are unixODBC (L<http://www.unixodbc/org>) and iODBC (L<http://www.iodbc.org>).
169
170 If you are running a packaged Linux like RedHat, Ubuntu, Fedora, Suse
171 etc etc you'll usually find it packaged with unixODBC and using the
172 package manager to install it is fairly straight forward. However,
173 make sure that if the driver manager is split into multiple packages
174 you install the development package as well as that contains the C
175 header files required by DBD::ODBC.
176
177 If you cannot find an ODBC Driver Manager package for your OS you can
178 download the source tar files for either of the driver managers above
179 and build it yourself.
180
181 =head2 How do I access a MS SQL Server database from Linux/UNIX?
182
183 You have loads of choices (in no particular order):
184
185 * using DBI::ProxyServer or DBD::Gofer. You'll need the former if you
186 use transactions.
187
188 * using a commercial ODBC Driver or bridge like the ones from Easysoft
189 or Openlink.
190
191 * using FreeTDS an open source TDS library which includes an ODBC Driver.
192
193 * using DBD::Sybase and Sybase libraries.
194
195 =head2 How do I access a MS-Access database from Linux?
196
197 There are basically three choices:
198
199 * a commercial MS Access ODBC Driver like the one from Easysoft.
200
201 * a commercial ODBC Bridge like the ones from Easysoft or OpenLink.
202
203 * using mdbtools although as of writing it has not been updated since
204 June 2004, only provides read access and seems to be a little buggy.
205
206 =head2 Almost all of my tests for DBD::ODBC fail. They complain about not being able to connect or the DSN is not found.
207
208 Please, please test your configuration of ODBC and driver before
209 trying to test DBD::ODBC. Most of the time, this stems from the fact
210 that the DSN (or ODBC) is not configured properly. unixODBC comes with
211 a small program isql and iODBC comes with odbctest and you should use
212 these to test your ODBC configuration is working properly first.
213
214 =head2 I'm attempting to bind a Long Var char (or other specific type)
215 and the binding is not working.
216
217 The code I'm using is below:
218
219 $sth->bind_param(1, $str, $DBI::SQL_LONGVARCHAR);
220 ^^^
221
222 The problem is that DBI::SQL_LONGVARCHAR is not the same as
223 $DBI::SQL_LONGVARCHAR and that $DBI::SQL_LONGVARCHAR is an error!
224
225 It should be:
226
227 $sth->bind_param(1, $str, DBI::SQL_LONGVARCHAR);
228
229 =head2 Does DBD::ODBC support Multiple Active Statements?
230
231 Multiple Active Statements (MAS) are concurrent statements created
232 from the same database handle which both have pending actions on them
233 (e.g. they both have executed a select statement but not retrieved all
234 the available rows yet).
235
236 DBD::ODBC does support MAS but whether you can actually use MAS is
237 down to the ODBC Driver.
238
239 By default MS SQL Server did not used to support multiple active
240 statements if any of them were select statements. You could get around
241 this (with caution) by changing to a dynamic cursor. There is a "hack"
242 in DBD::ODBC which can be used to enable MAS but you have to fully
243 understand the implications of doing so(see
244 L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> and L<DBD::ODBC/odbc_cursortype>).
245
246 In MS SQL Server 2005, there is a new thing called MARS (Multiple
247 Active Result Sets) which allows multiple active select statements but
248 it has some nasty implications if you are also doing transactions. To
249 enable MARS from DBD::ODBC add "MARS_Connection=Yes" to the connection
250 string as in:
251
252 $h->DBI->connect('dbi:ODBC:DSN=mydsn;MARS_Connection=Yes;');
253
254 NOTE: Even though you may be using MS SQL Server 2005 if you are using
255 MS SQL Server drivers you will need to use the Native Client Driver or
256 a later MS SQL Server ODBC driver (2008 or later) to use MARS.
257
258 For other drivers it depends. I believe various Oracle ODBC drivers do
259 support multiple active statements as myodbc does.
260
261 Think carefully before using multiple active statements. It is
262 probably not portable and there is nearly always a better way of doing
263 it.
264
265 If anyone wants to report success with a particular driver and
266 multiple active statements I will collect them here.
267
268 Also see L<http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html>
269
270 =head2 Why do I get "Datetime field overflow" when attempting to insert a
271 date into Oracle?
272
273 If you are using the Oracle or Microsoft ODBC drivers then you may get
274 the following error when inserting dates into an Oracle database:
275
276 [Oracle][ODBC]Datetime field overflow. (SQL-22008)
277
278 If you do then check v$nls_parameters and v$parameter to see if you are
279 using a date format containing the RR format. e.g.,
280
281 select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT'
282 select * from v$parameter where name = 'nls_date_format'
283
284 If you see a date format like 'DD-MON-RR' (e.g., contains an RR) then
285 all I can suggest is you change the date format for your session as I
286 have never been able to bind a date using this format. You can do this
287 with:
288
289 alter session set nls_date_format='YYYY/MM/DD'
290
291 and use any format you like but keep away from 'RR'.
292
293 You can find some test code in the file examples/rtcpan_28821.pl which
294 demonstrates this problem. This was originally a rt.cpan issue which
295 can be found at L<http://rt.cpan.org/Ticket/Display.html?id=28821>.
296
297 As an aside, if anyone is reading this and can shed some light on the problem
298 I'd love to hear from you. The technical details are:
299
300 create table rtcpan28821 (a date)
301 insert into rtcpan28821 values('23-MAR-62') fails
302
303 Looking at the ODBC trace, C<SQLDescribeParam> returns:
304
305 data type: 93, SQL_TYPE_TIMESTAMP
306 size: 19
307 decimal digits: 0
308 nullable: 1
309
310 and DBD::ODBC calls SQLBindParameter with:
311
312 ValueType: SQL_C_CHAR
313 ParameterType: SQL_TYPE_TIMESTAMP
314 ColumnSize: 9
315 DecimalDigits: 0
316 Data: 23-MAR-62
317 BufferLength: 9
318
319 =head2 Why do my SQL Server temporary objects disappear?
320
321 If you are creating temporary objects (e.g., temporary tables) in
322 SQL Server you find they have disappeared when you attempt to use
323 them. Temporary objects only have a lifetime of the session they
324 are created in but in addition, they cannot be created using
325 prepare/execute. e.g., the following fails:
326
327 $s = $h->prepare('select * into #tmp from mytable');
328 $s->execute;
329 $s = $h->selectall_arrayref('select * from #tmp');
330
331 with "Invalid object name '#tmp'". Your should read
332 L<http://technet.microsoft.com/en-US/library/ms131667.aspx> which
333 basically says I<Prepared statements cannot be used to create
334 temporary objects on SQL Server 2000 or later...>. The proper way to
335 avoid this is to use the C<do> method but if you cannot do that then
336 you need to add the L<DBD::ODBC/odbc_exec_direct> attribute to your prepare as
337 follows:
338
339 my $s = $h->prepare('select * into #tmp from mytable',
340 { odbc_exec_direct => 1});
341
342 See L<DBD::ODBC/odbc_exec_direct>.
343
344 =head2 Why cannot I connect to my data source on Windows 64?
345
346 If you are running a 32bit Perl on a 64bit Windows machine you will
347 need to be aware there are two ODBC administrators and you need to
348 create your DSNs with the right one. The ODBC Administrator you get to
349 from Control Panel, Administrative Tools, Data Sources is the 64bit
350 one and data sources created here will not be visible or useable from
351 32bit applications. The ODBC administrator you need to use for 32bit
352 applications can be found at X:\windows\syswow64\odbcad32.exe.
353
354 You can find more about this than you'd probably care to know
355 at http://www.easysoft.com/developer/interfaces/odbc/64-bit.html
356
357 =head2 How do I use DBD::ODBC with web servers under Win32.
358
359 =over 4
360
361 =item General Commentary re web database access
362
363 This should be a DBI faq, actually, but this has somewhat of an
364 Win32/ODBC twist to it.
365
366 Typically, the Web server is installed as an NT service or a Windows
367 95/98 service. This typically means that the web server itself does
368 not have the same environment and permissions the web developer does.
369 This situation, of course, can and does apply to Unix web servers.
370 Under Win32, however, the problems are usually slightly different.
371
372 =item Defining your DSN -- which type should I use?
373
374 Under Win32 take care to define your DSN as a system DSN, not as a user
375 DSN. The system DSN is a "global" one, while the user is local to a
376 user. Typically, as stated above, the web server is "logged in" as a
377 different user than the web developer. This helps cause the situation
378 where someone asks why a script succeeds from the command line, but
379 fails when called from the web server.
380
381 =item Defining your DSN -- careful selection of the file itself is important!
382
383 For file based drivers, rather than client server drivers, the file
384 path is VERY important. There are a few things to keep in mind. This
385 applies to, for example, MS Access databases.
386
387 1) If the file is on an NTFS partition, check to make sure that the Web
388 B<service> user has permissions to access that file.
389
390 2) If the file is on a remote computer, check to make sure the Web
391 B<service> user has permissions to access the file.
392
393 3) If the file is on a remote computer, try using a UNC path the file,
394 rather than a X:\ notation. This can be VERY important as services
395 don't quite get the same access permissions to the mapped drive letters
396 B<and>, more importantly, the drive letters themselves are GLOBAL to
397 the machine. That means that if the service tries to access Z:, the Z:
398 it gets can depend upon the user who is logged into the machine at the
399 time. (I've tested this while I was developing a service -- it's ugly
400 and worth avoiding at all costs).
401
402 Unfortunately, the Access ODBC driver that I have does not allow one to
403 specify the UNC path, only the X:\ notation. There is at least one way
404 around that. The simplest is probably to use Regedit and go to
405 (assuming it's a system DSN, of course)
406 HKEY_LOCAL_USERS\SOFTWARE\ODBC\"YOUR DSN" You will see a few settings
407 which are typically driver specific. The important value to change for
408 the Access driver, for example, is the DBQ value. That's actually the
409 file name of the Access database.
410
411 =back
412
413 =head2 How do I connect without DSN
414
415 The ability to connect without a full DSN was introduced in version 0.21.
416
417 Example (using MS Access):
418
419 my $DSN = 'driver=Microsoft Access Driver(*.mdb);dbq=\\\\cheese\\g$\\perltest.mdb';
420 my $dbh = DBI->connect("dbi:ODBC:$DSN", '','') or die "$DBI::errstr\n";
421
422 The above sample uses Microsoft's UNC naming convention to point to
423 the MSAccess file (\\cheese\g$\perltest.mdb). The dbq parameter tells
424 the access driver which file to use for the database.
425
426 Example (using MSSQL Server):
427
428 my $DSN = 'driver={SQL Server};Server=server_name;database=database_name;uid=user;pwd=password;';
429 my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";
430
431 =head2 Why do I get data truncated error from SQL Server when inserting with parameters?
432
433 Please see "Why am I getting errors with bound parameters" below which
434 collects all parameter issues together in one FAQ item.
435
436 =head2 Why do I get invalid value for cast specification (22018) from SQL Server when inserting with parameters?
437
438 Please see "Why am I getting errors with bound parameters" below which
439 collects all parameter issues together in one FAQ item.
440
441 =head2 Why do I get strange results with SQL Server and named parameters?
442
443 If you are using a MS SQL Server driver and named parameters to
444 procedures be very careful to use then in the same order they are
445 defined in the procedure. i.e., if you have a procedure like this:
446
447 create procedure test
448 @param1 varchar(50),
449 @param2 smallint
450 as
451 begin
452 ..
453 end
454
455 then ensure if you call it using named parameters you specify them in
456 the same order they are declared:
457
458 exec test @param1=?,@param2=?
459
460 and not
461
462 exec test @param2=?,@param1=?
463
464 The reason for this is that all SQL Server drivers we have seen
465 describe procedures parameters in the order they are declared and
466 ignore the order they are used in the SQL. If you specify them out of
467 order DBD::ODBC will get details on p1 which are really for p2
468 etc. This can lead to data truncation errors and all sorts of other
469 problems it is impossible for DBD::ODBC to spot or workaround.
470
471 =head2 Why do I get "Numeric value out of range" when binding dates in Oracle?
472
473 Also see "Why do I get "Datetime field overflow" when attempting to insert a
474 date into Oracle?".
475
476 Here is some example code binding dates; some work, some don't, see comments.
477
478 use DBI;
479 use strict;
480
481 # table is "create table martin (a date, b int)"
482
483 my $h = DBI->connect;
484
485 $h->do(q{alter session set nls_date_format='DD-MON-YY'});
486
487 my $s = $h->prepare(q{select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT'});
488 $s->execute;
489 print DBI::dump_results($s);
490
491 my $date = '30-DEC-99';
492 my $dateodbc = qq/{ d '1999-12-30'}/;
493
494 # the following works ok - resulting in 2099-12-30 being inserted
495 $h->do(qq{insert into martin values ('$date', 1)});
496
497 # the following works resulting in 1999-12-30 being inserted
498 $h->do(qq{insert into martin values ($dateodbc, 2)});
499
500 # fails
501 eval {
502 my $s = $h->prepare(q{insert into martin values(?,3)});
503 $s->bind_param(1, $date);
504 # fails
505 # Numeric value out of range: invalid character in date/time string (SQL-22003)
506 $s->execute;
507 };
508
509 # works resulting in 2099-12-30 being inserted
510 eval {
511 my $s = $h->prepare(q{insert into martin values(?,4)});
512 $s->bind_param(1, $date, DBI::SQL_VARCHAR);
513 $s->execute;
514 };
515
516 # works resulting in 1999-12-30 being inserted
517 eval {
518 my $s = $h->prepare(q{insert into martin values(?,5)});
519 $s->bind_param(1, $dateodbc);
520 $s->execute;
521 };
522
523 In general, when using an ODBC driver you should use the ODBC syntax
524 for dates, times and timestamps as those are the only formats an ODBC
525 has to support.
526
527 In the above case with Oracle, the date parameter is described as a
528 SQL_TYPE_DATE SQL type so by default DBD::ODBC binds your parameter as
529 a SQL_TYPE_DATE. If you use '30-DEC-99' then that means the C type is
530 SQL_CHAR and the SQL type is SQL_TYPE_DATE so the driver is forced to
531 parse the date before sending it to Oracle (that would mean knowing
532 what your NLS_DATE_FORMAT is and it would also mean knowing all the
533 magic special characters Oracle can use to define date formats).
534
535 If you override the bind type to SQL_VARCHAR then the driver sees
536 SQL_CHAR => SQL_VARCHAR, nothing to do and hence Oracle itself does
537 the translation - that is why the SQL_VARCHAR works.
538
539 =head2 CWB0111 error with System i Access ODBC Driver
540
541 The full error this relates to is:
542
543 [unixODBC][IBM][System i Access ODBC Driver]Column 1: CWB0111 - A buffer passed to a system call is too small to hold return data (SQL-22018)
544
545 The iSeries ODBC driver looks at your environment and decides that if
546 you have UTF-8 set it will encode data returned from the database in
547 UTF-8.
548
549 e.g., LC_CTYPE=fr_FR.UTF-8
550
551 If you then select data from your database containing non-ASCII
552 characters e.g., accented characters the iSeries ODBC driver will
553 encode the data in UTF-8. UTF-8 encoding increases the size of strings
554 containing characters with codes > 127.
555
556 DBD::ODBC uses SQLDescribeCol and SQLColAttribute ODBC calls to work
557 out the size of the columns you are retrieving and allocate space for
558 them. As the ODBC API specifies the sizes returned are on bytes when
559 the driver says a string column is N is size DBD::ODBC allocates N+1
560 (for NULL) bytes. If the driver then encodes N characters in UTF-8 the
561 size will be too big to fit into DBD::ODBC's buffer and you will get
562 the error above. This is most often seen with char(N) columns as the
563 trailing spaces are returned by the driver so you are bound to
564 overflow the buffer as soon as a non-ASCII characters is found.
565
566 What are your possible solutions?
567
568 You can attempt to trim the data to leave room for the encoding.
569 e.g., RTRIM(column_name) in your select on char(N) columns but this is
570 a poor choice and only going to work in a few circumstances.
571
572 You can increase the sizes of your columns in the database but this is
573 another hack.
574
575 You can rearrange your SQL to cast the columns in question to larger
576 types.
577
578 Remove UTF-8 from your locale. This is the best solution as it is
579 guaranteed to stop this error but if you have data which cannot be
580 represented in 8 bit characters this won't help.
581
582 =head2 "ConnectionWrite (send())" error and bind parameters
583
584 The following error can happen when using more than 2097 bind
585 parameters in a single query with SQL Server 2000:
586
587 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
588 (SQL-01000) [state was 01000 now 08S01]
589 [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error.
590 Check your network documentation. (SQL-08S01)
591
592 This error is most likely due to a bug in the Microsoft SQL Server
593 ODBC driver as it fails for some versions of the driver and not
594 others. It is known to fail for version 2000.85.1132.00
595
596 See bug report https://rt.cpan.org/Public/Bug/Display.html?id=49061
597 for more details.
598
599 =head2 SQL query length limited to about 65500 characters in SQL Server 2000
600
601 When using bind parameters and a query longer than about 65500
602 characters the query will fail with some versions of the SQL Server
603 2000 ODBC driver. The error message from the server can vary. Below is
604 an example:
605
606 [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
607 'TES[...]P1I'. (SQL-42S22) [state was 42S22 now 42000]
608 [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
609 be prepared. (SQL-42000)
610
611 Removing the use of binding will allow queries that are longer than
612 65500 characters.
613
614 This bug is known to affect Microsoft SQL Server ODBC driver version
615 2000.85.1132.00.
616
617 See bug report https://rt.cpan.org/Public/Bug/Display.html?id=49065
618
619 =head2 HY009, "invalid length or pointer" from SQLite
620
621 See rt 52651 at http://rt.cpan.org/Public/Bug/Display.html?id=52651.
622
623 During connection if you get a HY009 "invalid length or pointer" error
624 it is a bug in SQLSetConnectAttr in SQLite and you will need a version
625 at least 0.85pre1.
626
627 =head2 Where do I get the latest MDAC Development Kit?
628
629 MS keep moving this around. If you want to build DBD::ODBC yourself
630 from source you'll need the latest Microsoft Data Access Components
631 (MDAC) Software Development Kit. You can get it as part of the Platform
632 Development Kit, with some of the Visual Studio versions and occasionally
633 from:
634
635 http://msdn.microsoft.com/en-us/data/aa937730.aspx
636
637 where in April 2010 it listed the "Microsoft Data Access Componetns (MDAC)
638 2.8 Software Development Kit.
639
640 =head2 Why does DBD::ODBC fail to compile with missing definitions for SQLLEN/SQLULEN?
641
642 This happens because Microsoft changed their headers to add SQLLEN/SQLULEN
643 types and your C headers are probably out of date. As DBD::ODBC needs
644 to use these types you'll need an updated MDAC Development Kit. See
645 " Where do I get the latest MDAC Development Kit?".
646
647 =head2 Why do I get errors with bound parameters and MS SQL Server?
648
649 See the question "Why do I get data truncated error from SQL Server
650 when inserting with parameters?" above. These errors are often because
651 of bugs in the MS SQL Server ODBC driver in its SQLBindParameter
652 implementation and can be worked around by specifying a type at bind
653 time.
654
655 e.g.,
656
657 Instead of:
658
659
660 my $s = prepare(q/some sql with parameters/);
661 $s->execute($param1, $param2);
662
663 try:
664
665 my $s = prepare(q/some sql with parameters/);
666 $s->bind_param(1, $param1, {TYPE => SQL_VARCHAR});
667 $s->bind_param(2, $param2, {TYPE => SQL_VARCHAR});
668 $s->execute;
669
670 See https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater-datatype-lookup-returns-incorrectly and rt ticket 50852.
671
672 =head2 Why does my script pause for a while whenever my statement handle is destroyed (goes out of scope)?
673
674 The symptom is that sometimes when your statement handle goes out of
675 scope and is hence destroyed your script pauses for a while. If you
676 are using MS SQL Server and certain MS SQL Server ODBC Drivers this
677 can happen when you issue a select which would return a lot of rows
678 but you don't fetch them all.
679
680 The problem is that the TDS protocol (normally, without Multiple
681 Active Statement support, or MARS) sends all the result-set down the
682 socket until it is consumed by the client end. When your statement
683 handle is destroyed with pending results the ODBC Driver needs to read
684 all the results to clear the socket. In reality MS SQL Server will
685 only write so many rows at a time to the socket depending on its
686 buffer size and will occasionally look at the socket for new requests
687 so it is possible for ODBC Drivers which support SQLCancel to reduce
688 the number of rows sent by using DBI's cancel method. In this way the
689 statement destruction is speeded up since fewer rows you don't need
690 are sent. See DBI's cancel method and if you destroy a statement
691 handle with pending results, call cancel before destruction.
692
693 However, you are best not selecting rows you have no intention of
694 retrieving.
695
696 See cancel_big_fetch.pl in the DBD::ODBC examples dir. NOTE: more recent
697 MS SQL Server drivers are better in this respect and sometimes the test
698 script cancel_big_fetch.pl shows no difference.
699
700 =head2 Why does my backup/restore/some_other_procedure in MS SQL Server not complete?
701
702 MS SQL Server batches up results in a procedure. A result may be the
703 output of a print or a select or in some cases even an insert/update
704 (see SET NOCOUNT ON|OFF). If you attempt to call a procedure using the
705 C<do> method and it outputs results (e.g., a print statement saying
706 the % completed) the procedure probably will not fully
707 complete. Instead you should do the following:
708
709 $sth-prepare(call to my procedure);
710 $sth->execute;
711 do {
712 while (my @row = $sth->fetchrow_array()) {
713 # do stuff here
714 }
715 } while ($sth->{odbc_more_results});
716 # do not forget to check $sth->err here if not using RaiseError as
717 # the outer while loop will stop when there are no more results OR
718 # if an error occurs.
719
720 =head2 Why do I get "The data types ntext and varchar are incompatible in the equal to operator"?
721
722 Or "The data types ntext and nvarchar(max) are incompatible in the
723 equal to operator".
724
725 MS SQL Server does not like equality comparisons with ntext columns.
726 You can get this error without using any Perl or DBD::ODBC simply by doing:
727
728 select * from mytable where ntext_column = 'hello'
729
730 You unfortunately need to change your SQL to:
731
732 select * from mytable where CAST(ntext_column AS nvarchar(max)) = 'hello'
733
734 =head2 Why are my integers returned as decimals?
735
736 If you are using the MS SQL Server ODBC driver and get
737 integers/booleans back as apparently decimals e.g. 0.00 instead of 0 and
738 integer primary keys as nn.nn you've probably either:
739
740 =over
741
742 =item enabled regional settings in the ODBC DSN setup (called "use regional settings with outputting..."
743
744 =item added "Regional=Yes" to your connection string.
745
746 The MS SQL Server ODBC Driver regional settings are massively flawed
747 and break lots of applications - turn it off.
748
749 =back
750
751 =head2 Why does Connect call fail on Ubuntu with a "undefined symbol SQLxxx" error?
752
753 Sometimes SQLxxx is SQLAllocHandle or SQLFetch but it could almost be
754 any ODBC API.
755
756 Did you build DBD::ODBC against iODBC? Some versions of Ubunutu Linux
757 seem to install the libiodbc shared object without a libiodbc.so
758 symbolic link. The key giveaway when you build DBD::ODBC is a warning like
759 this:
760
761 Note (probably harmless): No library found for -liodbc.so
762
763 You can fix this manually by creating a symbolic link something like this:
764
765 sudo ln /usr/lib/libiodbc.so.2 /usr/lib/libiodbc.so
766
767 but to be honest this just moved the problem on for me and I've never got
768 iODBC working on Ubuntu.
769
770 I suggest you remove iODBC and installing the unixodbc, unixodbc-dev and
771 unixodbc-bin packages.
772
773 =head2 Why does Connect fail with "Option type out of range"
774
775 You are probably setting an ODBC connection option like
776 odbc_SQL_ROWSET_SIZE in the connect method. The problem is that some
777 options can be set in ODBC with SQLSetConnectOption but not retrieved
778 (silly but that is how some of the driver managers work). In the past
779 this did not matter but a small bug in DBI introduced years ago causes
780 attribute values to be fetched.
781
782 You need to get a DBI release newer than 1.615 or revision 14463 from
783 the DBI subversion repository. The change was:
784
785 --- dbi/trunk/DBI.pm (original)
786 +++ dbi/trunk/DBI.pm Thu Oct 7 02:45:05 2010
787 @@ -717,7 +717,8 @@
788 $dbh->{$a} = delete $apply->{$a};
789 }
790 while ( my ($a, $v) = each %$apply) {
791 - eval { $dbh->{$a} = $v } or $@ && warn $@;
792 + eval { $dbh->{$a} = $v }; # assign in void context to avoid re-FETCH
793 + warn $@ if $@;
794 }
795 }
796
797 =head2 Why do I get undefined symbol my_snprintf?
798
799 It is my fault. Basically, I changed DBD::ODBC to use Devel::PPPort's
800 my_snprintf instead of sprintf (for safety) but because of the way DBI
801 includes ppport.h (as dbipport.h) I cannot override it in DBD::ODBC.
802 This should now be fixed in DBI but I cannot retrofit it so if you
803 get this error you need to upgrade DBI to at least version 1.609.
804 Sorry, some things are as they are.
805
806 From DBD::ODBC 1.26_1 a requirement is DBI 1.609 so this should not be
807 an issue but the 1.24 and 1.25 series DBD::ODBC did not have this
808 requirement.
809
810 =head2 Why can I appear to insert more than 255 chrs into a MS Access text column but when I read them back I only get 255 chrs?
811
812 The simple answer is that MS Access only supports 255 characters in a
813 text column. You can see this if you create a table with a text column
814 then examine the PRECISION attribute (see DBI) and it returns 255.
815
816 Unfortunately some versions of the MS Access ODBC driver silently
817 truncate the data instead of issuing a data truncation error so you
818 are not aware of the truncation until you read it back.
819
820 Someone pointed out you can insert more than 255 characters into a MS
821 Access text column with DBD::ADO but I believe if you look at the
822 column types after creating the table with DBD::ADO the text column is
823 really a memo column.
824
825 =head2 Why am I getting errors with bound parameters?
826
827 There are various problems with parameter binding in ODBC Drivers most
828 of them down to bugs in the ODBC drivers. I created this FAQ to try
829 and bring them all together in one place and other FAQ entries point
830 at this one as a number of them boil down to a single problem.
831
832 DBD::ODBC used to (many many versions ago) bind all parameters as
833 SQL_VARCHAR and this mostly works because a SQL_VARCHAR can almost
834 always be converted to any other SQL type. However, because of bugs in
835 some ODBC drivers, additional SQL types which work in mysterious ways
836 (like varchar(max) in MS SQL Server) and dates, DBD::ODBC was changed
837 to ask the ODBC Driver (C<SQLDescribeParam>) about parameters before
838 binding them.
839
840 There are a few things to note first:
841
842 =over
843
844 =item default bind type
845
846 Some ODBC Drivers do not support C<SQLDescribeParam> (e.g. freeTDS) and
847 in those cases DBD::ODBC reverts to its old behaviour of binding
848 everything as a SQL_VARCHAR or SQL_WVARCHAR (UNICODE build). In some
849 rare cases the default bind type might not be what you want.
850
851 In addition, if C<SQLDescribeParam> is supported but fails the default
852 bind type is used.
853
854 =item overriding the default bind type
855
856 You can always override the default bind type used when
857 C<SQLDescribeParam> is not supported using odbc_default_bind_type.
858
859 =item forcing a bind type
860
861 Sometimes the ODBC Driver can support C<SQLDescribeParam> but get the
862 answer wrong e.g., "select myfunc(?) where 1 = 1)" often causes a
863 problem because the parameter does not correspond to a real column in
864 a table. In these cases you can use odbc_force_bind_type to stop
865 DBD::ODBC calling C<SQLDescribeParam> and use the specified type
866 instead. However, adding TYPE => xxx to the bind_param call is nearly
867 always better (as it is more specific) and always overrides
868 odbc_force_bind_type.
869
870 =item specifying a bind type on the bind_param call
871
872 In all cases this overrides anything else that DBD::ODBC might do
873 and bare in mind parameter bind types set in this way are "sticky" (see
874 DBI). This is usually the best method unless you prefer to use a
875 cast in your SQL.
876
877 =back
878
879 Now to the specific problems:
880
881 =over
882
883 =item data truncated error
884
885 DBD::ODBC attempts to use the ODBC API C<SQLDescribeParam> to obtain
886 information about parameters in parameterised SQL. e.g.,
887
888 insert into mytable (column1) values(?)
889
890 The C<?> is a parameter marker. You supply the parameter value (in
891 this case parameter 1) with a call to the C<bind_param> method or by
892 adding the parameter to the C<execute> method call. When DBD::ODBC
893 sees the parameter marker in the SQL it will call C<SQLDescribeParam>
894 to obtain information about the parameter size and type etc (assuming
895 your ODBC driver supports C<SQLDescribeParam>).
896
897 When you call C<SQLDescribeParam> in the MS SQL Server ODBC driver the
898 driver will scan your SQL attempting to discover the columns in your
899 database the parameters align with. e.g., in the above case the
900 parameter to be bound is linked with "column1" so C<SQLDescribeParam>
901 should return information about "column1". The SQL Server ODBC driver
902 finds information about "column1" (in this example) by creating SQL such
903 as:
904
905 select column1 from mytable where 1 = 2
906
907 then looking at the column details. Unfortunately, some SQL confuses
908 SQL Server and it will generate SQL to find out about your parameters
909 which examines the wrong columns and on rare occasions it may even
910 generate totally incorrect SQL. The test case F<t/rt_39841.t>
911 demonstrates a couple of these.
912
913 The upshot of this is that DBD::ODBC is sometimes lied to about
914 parameters and will then bind your parameters incorrectly. This can lead
915 to later errors when C<execute> is called. This happens most commonly
916 when using parameters in SQL with sub-selects. For example:
917
918 create table one (a1 integer, a2 varchar(10))
919 create table two (b1 varchar(10), b2 varchar(20))
920
921 insert into one values(1, 'aaaaaaaaaa')
922 insert into two values('aaaaaaaaaa','bbbbbbbbbbbbbbbbbbbb')
923
924 select b1, (select a2 from one where a2 = b1) from two where b2 = ?
925
926 param 1 bound as 'bbbbbbbbbbbbbbbbbbbb'
927
928 Clearly in this example, the one and only parameter is for two.b2 which
929 is a varchar(20) but when SQL Server rearranges your SQL to describe
930 the parameter it issues:
931
932 select a2 from one where 1 = 0
933
934 and DBD::ODBC is told the parameter is a VARCHAR(10). In DBD::ODBC
935 1.17 this would then lead to a data truncation error because parameter
936 1 would be bound as 'bbbbbbbbbbbbbbbbbbbb' but with a column size of
937 10 as that is what C<SQLDescribeParam> returned. DBD::ODBC 1.17_1 (and
938 later) works around this problem for VARCHAR columns because it is
939 obvious a VARCHAR parameter of length 20 cannot have a column size of
940 10 so the column size is increased to the length of the parameter.
941
942 =item invalid value for the cast specification (22018)
943
944 See the previous item as this follows on from that.
945
946 See L<http://support.microsoft.com/kb/269011> on the microsoft web
947 site for a bug you may have hit.
948
949 In Perl the most common reason for this is that you have bound column
950 data in SQL which does not match the column type in the database and
951 the ODBC driver cannot perform the necessary conversion. DBD::ODBC
952 mostly binds all column data as strings and lets the ODBC driver
953 convert the string to the right column type. If you supply a string
954 which cannot be converted to the native column type you will get this
955 error e.g., if you attempt to bind a non-datetime string to a datetime
956 column or a non-numeric string to a numeric column.
957
958 A more difficult error (from that above in the previous item) can
959 occur when SQL Server describes the parameter as totally the wrong
960 type. The first example in F<t/rt_39841.t> demonstrates this. SQL
961 Server describes a VARCHAR parameter as an integer which DBD::ODBC has
962 little choice to believe but when something like 'bbbbbbbbbb' is bound
963 as an integer, SQL Server will then return an error like "invalid
964 value for cast specification". The only way around this is to
965 specifically name the parameter type. e.g.,
966
967 create table one (a1 integer, a2 varchar(20))
968 create table two (b1 double precision, b2 varchar(8))
969
970 insert into one values(1, 'aaaaaaaaaa')
971 insert into two values(1, 'bbbbbbbb')
972
973 select b1, ( select a2 from one where a1 = b1 ) from two where b2 = ?
974
975 param 1 bound as 'bbbbbbbbbb'
976
977 Clearly parameter 1 is a varchar(8) but SQL Server rearranges the SQL to:
978
979 select a1 from one where 1 = 2
980
981 when it should have run
982
983 select b2 from two where 1 = 2
984
985 As a result parameter 1 is described as an integer and this leads to the
986 problem. To workaround this problem you would need to bind parameter 1
987 naming the SQL type of the parameter using something like:
988
989 use DBI qw(:sql_types);
990
991 bind_param(1, 'bbbbbbbbbb', SQL_VARCHAR);
992
993 as omitting SQL_VARCHAR will cause DBD::ODBC to use the type
994 C<SQLDescribeParam> returned.
995
996 See https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater-datatype-lookup-returns-incorrectly and rt ticket 50852.
997
998 =item problems with named parameters
999
1000 If you are using a MS SQL Server driver and named parameters to
1001 procedures be very careful to use then in the same order they are
1002 defined in the procedure. i.e., if you have a procedure like this:
1003
1004 create procedure test
1005 @param1 varchar(50),
1006 @param2 smallint
1007 as
1008 begin
1009 ..
1010 end
1011
1012 then ensure if you call it using named parameters you specify them in
1013 the same order they are declared:
1014
1015 exec test @param1=?,@param2=?
1016
1017 and not
1018
1019 exec test @param2=?,@param1=?
1020
1021 The reason for this is that all SQL Server drivers we have seen
1022 describe procedures parameters in the order they are declared and
1023 ignore the order they are used in the SQL. If you specify them out of
1024 order DBD::ODBC will get details on p1 which are really for p2
1025 etc. This can lead to data truncation errors and all sorts of other
1026 problems it is impossible for DBD::ODBC to spot or workaround.
1027
1028 =item Argument data type varchar is invalid for argument N of xxx function
1029
1030 e.g.,
1031
1032 [SQL Server]Argument data type varchar is invalid for argument 2 of dateadd function.
1033
1034 Some functions need specific argument types and as explained above
1035 some drivers have a lot of difficulties working out parameter types in
1036 function calls.
1037
1038 In this example (also from the DBIx::Class test code) the SQL was like:
1039
1040 SELECT DATEADD(hour, ?, me.date_created) FROM mytable me where me.id = ?
1041
1042 The second argument to dateadd needs to be an integer. There are 2 ways
1043 around this:
1044
1045 =over
1046
1047 =item bind parameter 1 as an SQL_INTEGER
1048
1049 =item CAST(? as integer)
1050
1051 =back
1052
1053 =item other bugs in ODBC Drivers with parameter support
1054
1055 See the question "Why do I get data truncated error from SQL Server
1056 when inserting with parameters?" above. These errors are often because
1057 of bugs in the MS SQL Server ODBC driver in its SQLBindParameter
1058 implementation and can be worked around by specifying a type at bind
1059 time.
1060
1061 e.g.,
1062
1063 Instead of:
1064
1065
1066 my $s = prepare(q/some sql with parameters/);
1067 $s->execute($param1, $param2);
1068
1069 try:
1070
1071 my $s = prepare(q/some sql with parameters/);
1072 $s->bind_param(1, $param1, {TYPE => SQL_VARCHAR});
1073 $s->bind_param(2, $param2, {TYPE => SQL_VARCHAR});
1074 $s->execute;
1075
1076 See https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater-datatype-lookup-returns-incorrectly and rt ticket 50852.
1077
1078 =item example of where overriding parameter type is required
1079
1080 Here is an example sent to me from someone (wesm) using DBIx::Class
1081 illustrating a problem with bound parameters in a complex bit of SQL
1082 and date columns:
1083
1084 use DBI qw(:sql_types);
1085 use strict;
1086 use warnings;
1087 my $h = DBI->connect();
1088
1089 eval{ $h->do(q/DROP TABLE odbctest/) };
1090
1091 $h->do(q/CREATE TABLE odbctest (
1092 id integer NOT NULL IDENTITY (1,1),
1093 name nvarchar(50) NULL,
1094 adate date NULL )/);
1095
1096 my $s = $h->prepare(q/
1097 set identity_insert odbctest on;
1098 insert into odbctest
1099 (id, name, adate)
1100 values (?,?,?);
1101
1102 set identity_insert odbctest off;
1103 select scope_identity();
1104 /);
1105
1106 my $bug
1107 = undef; # fails
1108 #= '2011-03-21'; # works
1109 my @values = (
1110 [ 1000, 2000, ],
1111 [ 'frew', 'wes', ],
1112 [ $bug, '2009-08-10', ],
1113 );
1114
1115 my $i = 1; my $tuple_status;
1116 $s->bind_param_array($i++, $_) for @values;
1117 $s->execute_array({ArrayTupleStatus => $tuple_status});
1118
1119 $s = $h->prepare(q/select * from odbctest where id = ?/);
1120 foreach (1000, 2000) {
1121 $s->execute($_);
1122 print DBI::dump_results($s);
1123 }
1124
1125 When I first ran this with SQL Server native client (pre native client 10)
1126 on Windows I got
1127
1128 DBD::ODBC::st execute_array failed: [Microsoft][SQL Native Client]Syntax error, permission violation, or other nonspecific error (SQL-42000) [err was 1 now 2000
1129
1130 The call to C<SQLDescribeParam> fails because the driver cannot rearrange
1131 the SQL into a select allowing it to identify the column. DBD::ODBC
1132 then falls back on the default parameter bind type and it still
1133 fails. When you switch to the native client 10 driver (newer) it fails
1134 with optional feature not implemented. Now I'm only guessing here but
1135 the date column type was added in SQL Server 2008 and I'm guessing
1136 when it was added they forgot to add something in the driver for full
1137 date support.
1138
1139 If you use native client 10 and change the insert code to:
1140
1141 my @values = (
1142 [ 1000, 2000, ],
1143 [ 'frew', 'wes', ],
1144 [ $bug, '2009-08-10', ],
1145 );
1146 my @types = (
1147 SQL_VARCHAR, SQL_VARCHAR, SQL_DATE);
1148
1149 my $i = 1; my $tuple_status;
1150 $s->bind_param_array($i++, $_, {TYPE => $types[$i-2]}) for @values;
1151 $s->execute_array({ArrayTupleStatus => $tuple_status});
1152
1153 you workaround the problem.
1154
1155 It is possible when you read this now that DBIx::Class has worked
1156 around this problem.
1157
1158 =back
1159
1160 =head2 Why do I get data truncated errors with type_info and Firebird ODBC Driver?
1161
1162 The Firebird ODBC driver from the open source Firebird project version
1163 02.01.0100 (as reported in ODBC via SQLGetInfo) seems to report the
1164 length of the TYPE_NAME field for types -9 'VARCHAR(x) CHARACTER SET
1165 UNICODE_' and -10 'BLOB SUB_TYPE TEXT CHARACTER SET ' as 34 but then
1166 attempt to write 36 characters to them.
1167
1168 Unfortunately this is an error via DBI and due to the way type_info
1169 is implemented (where it retrieves all the types the first time you
1170 query any type) it stops you querying any type even those which are
1171 not -9 or -10. Unless you need the TYPE_NAME field this should not matter
1172 to you in which case you can set LongTruncOk before retrieving type_info:
1173
1174 my $ti;
1175 {
1176 local $dbh->{LongTruncOk} = 1;
1177 $ti = $dbh->type_info_all;
1178 }
1179
1180 You will need to do something similar if you use the statements
1181 attributes like TYPE the first time you use them.
1182
1183 I reported this issue at http://tracker.firebirdsql.org/browse/ODBC-122.
1184
1185 =head2 Why do I get "symbol lookup error for SQLAllocHandle" on Ubuntu/Debian?
1186
1187 You've probably got iODBC installed and the Makefile.PL found it before
1188 unixODBC but on some Ubuntu and Debian distributions iODBC is not installed
1189 with the symbolic link libiodbc.so e.g., /usr/lib contains
1190
1191 lrwxrwxrwx 1 root root 22 2011-01-04 20:00 libiodbcinst.so.2 -> libiodbcinst.so.2.1.18
1192 -rw-r--r-- 1 root root 67140 2009-11-11 00:42 libiodbcinst.so.2.1.18
1193 lrwxrwxrwx 1 root root 18 2011-01-04 20:00 libiodbc.so.2 -> libiodbc.so.2.1.18
1194 -rw-r--r-- 1 root root 305108 2009-11-11 00:42 libiodbc.so.2.1.18
1195
1196 but no libiodbc.so. You could create the symbolic link:
1197
1198 cd /usr/lib
1199 sudo ln -s libiodbc.so.2 libiodbc.so
1200
1201 however, you'll probably have other problems so better to use
1202 unixODBC. Ensure you've installed unixodbc and unixodbc-dev packages
1203 and re-run Makefile.PL with the -x argument to prefer unixODBC.
1204
1205 =head2 How do I set the application and workstation names for MS SQL Server?
1206
1207 MS SQL Server supports 2 additional connection attributes which you
1208 can use to set application name and the workstation name:
1209
1210 B<APP> specifies the application name recorded in the program_name
1211 column in master.dbo.sysprocesses.
1212
1213 B<WSID> sets the workstation name recorded in the hostname column in
1214 master.dbo.sysprocesses.
1215
1216 To set these add these attributes to the call to DBIs connect like this:
1217
1218 my $h = DBI->connect('dbi:ODBC:DSN=mydsn;APP=appname;WSID=wsname',
1219 'dbuser', 'dbpass');
1220
1221 =head2 Why do I get "The specified DSN contains an architecture mismatch between the Driver and Application" on Windows?
1222
1223 You've got a 64 bit Windows.
1224
1225 Your attempting to connect to a SYSTEM DSN.
1226
1227 You are trying to connect to a 64bit SYSTEM DSN from a 32 bit
1228 application or vice versa. See my initial experience
1229 http://www.martin-evans.me.uk/node/81.
1230
1231 More confusing is if you use the data_sources method, that calls
1232 SQLDataSources and the list returned matches the architecture of your
1233 Perl binary and yet when you attempt to connect to a DSN for the
1234 wrong architecture you get this error instead of the more sensible
1235 (and usual) data source not found.
1236
1237 NOTE: User DSNs don't exhibit this - they just seem to pick the right
1238 driver.
1239
1240 See also http://www.easysoft.com/developer/interfaces/odbc/64-bit.html
1241
1242 =head2 Why does my transaction get committed when I disable AutoCommit?
1243
1244 If you are doing something like this:
1245
1246 {local $h->{AutoCommit} = 0;
1247 $h->do(q/insert into mje values(1)/);
1248 }
1249
1250 then what really happens is AutoCommit is disabled at the start of the
1251 block and when the block is exited AutoCommit is re-enabled. In ODBC
1252 enabling AutoCommit will commit any outstanding transaction. Don't do
1253 this. Instead, either rollback or commit at the end of the block or
1254 leave AutoCommit alone and call begin_work/commit/rollback yourself in
1255 the block.
1256
1257 =head2 Why do I get the wrong row count from execute_for_fetch?
1258
1259 Some drivers don't return the correct value from SQLRowCount when
1260 binding arrays of parameters. e.g., freeTDS 8 and 0.91 seems to return
1261 a 1 for each batch. e.g., if you run a SQL insert to insert 15 rows
1262 and pass an array of 15 rows to execute_array with the default array
1263 size of 10 it takes 2 batches to execute all the parameters and
1264 freeTDS will return 1 row affected for each batch hence returns 2
1265 instead of 15.
1266
1267 See rt 75687.
1268
1269 =head2 Why are my pound signs (£), dashes and quotes (and other characters) returned garbled
1270
1271 The first question in response is why do you think what you got back was incorrect? Did you print the data to a terminal and it looks wrong, or perhaps sent it to a browser in a piece of CGI or even wrote it to a file? The mantra you need to stick to is decode all input to Perl and encode all output but DBD::ODBC does the decoding of data retrieved from the database for you.
1272
1273 The classic case I keep seeing I've repeated here because it illustrates the most common problem. Database is MS SQL Server, data is viewed in the management console and looks good but when retrieved via DBD::ODBC it looks wrong. The most common cause of this is the data you've retrieved is stored as unicode in Perl and you output it to somewhere without encoding it first with an encoding appropriate for the output e.g., you printed it from a windows terminal without setting the STDOUT encoding to cp1252 (or whatever your codepage in your terminal is). The first thing I'd suggest is to print the data with Data::Dumper and if any of the output contains \x{NNNN} your data is unicode (there are other ways like using DBI's data_string_desc utility method or Encode's is_utf8).
1274
1275 Bear in mind that in a unicode build of DBD::ODBC (the default on Windows) all string data is retrieved as unicode. When you output your unicode data anywhere you need to encode it with Encode::encode e.g.,
1276
1277 binmode(STDOUT, ":encoding(cp1252)");
1278
1279 Just because you think you are working in a single codepage does not mean the data you retrieve will be returned as single byte characters in that codepage. DBD::ODBC (in a unicode build) retrieves all string data as wide (unicode) characters and most ODBC drivers will convert the codepage data returned by the database to unicode. For example, your column is windows-1252 codepage and contains a euro symbol which is character 0xA3. When retrieved by DBD::ODBC, the ODBC driver will convert this to unicode character 0x20ac. If you output this without encoding you'll likely see rubbish.
1280
1281 If you are absolutely sure you are using a single code page and don't want to be bothered with unicode, look up the odbc_old_unicode attribute but better still, rebuild DBD::ODBC without unicode support using:
1282
1283 perl Makefile.PL -nou
1284
1285 =head2 Does DBD::ODBC support the new table valued parameters?
1286
1287 Not yet. Patches welcome.
1288
1289 =head2 Why do I get "COUNT field incorrect or syntax error (SQL-07002)"?
1290
1291 In general this error is telling you the number of parameters bound or
1292 passed to execute does not match the number of parameter markers in
1293 your SQL. However, this can also happen if you attempt to use too many
1294 parameters.
1295
1296 For instance, for MS SQL Server
1297 (http://msdn.microsoft.com/en-us/library/ms143432.aspx) the maximum is
1298 2100.
1299
1300 =head2 Why are my column names truncated to 30 characters when using freeTDS?
1301
1302 You should note this is only an observed answer. The person who
1303 reported this to me was using MS SQL Server 2008. If he set TDS
1304 protocol 6.0, 9.0 or 10.0 his column names were truncated to 30
1305 charatcers. If he specified TDS protocol 7.0 or 8.0 his column names
1306 were not truncated. We guessed his server did not support protocols
1307 9.0 or 10.0 and fall back to 6.0 where column names are restricted to
1308 30 characters.
1309
1310 =head2 Why are my doubles truncated from MS Access DB?
1311
1312 If you have a double column in your MS Access DB and the retrieved
1313 values are truncated you have probably hit a known (and fixed) bug in
1314 the MS Access ODBC driver. Typical truncation lookes like this:
1315
1316 8.93601020357839E-06 returned as E-6
1317
1318 If you have the Microsoft Access 2010 accdb ODBC driver (v14) or older
1319 then try upgrading to the 2013 (v15) driver as my experiments showed
1320 that fixed the issue. Search for "Microsoft Access 2013
1321 redistributable engine" and download the appropriate version for your
1322 operating system.
1323
1324 =head2 Why is my (long)binary data inserted into MS Access incorrect?
1325
1326 A typical example of this is trying to insert binary data into a
1327 MS Access table using parameters and the data ends up full of null bytes.
1328
1329 The MS Access ODBC driver does not support the ODBC API SQLDescribeParam
1330 so DBD::ODBC has no idea what parameter type to use when binding placeholders.
1331 By default, DBD::ODBC defaults in this situations to SQL_CHAR type. However,
1332 when you bind binary data as SQL_CHAR with the MS Access ODBC Driver the data
1333 stored in your DB will no longer be the data you expect as the driver translates
1334 your data bound as a string to binary. The way around this is to bind the parameter
1335 specifying the binary type e.g.,
1336
1337 $sth->bind_param(1, undef, SQL_LONGVARBINARY)
1338
1339 or one of the other binary types. Note, that you don't actually have to pass the
1340 parameter into bind_param as parameter types are sticky so (as in this example) you
1341 can tell DBD::ODBC to use a different type in the bind_param call but still go on to
1342 pass the paramter into the execute method.
1343
1344 =head1 AUTHOR
1345
1346 Parts of this document were written by Tim Bunce,
1347 Jeff Urlwin and Martin J. Evans.
1348
1349 =head1 LICENSE
1350
1351 This library is free software; you can redistribute it and/or modify
1352 it under the same terms as Perl itself, either Perl version 5.8.7 or,
1353 at your option, any later version of Perl 5 you may have available.
1354
1355
1356 =cut
1357