Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.


Subject: Inserting more than 127 utf8 characters into MS Access Text or Memo field gives "Invalid precision value" error
Download (untitled) / with headers
text/plain 706b
When trying to insert more than 127 Unicode characters encoded as utf8 into a Text or Memo field the following error message is shown: DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]Invalid precision value (SQL-HY104) Inserting 127 characters or less into the field works without any problems. This problem was encountered on .mbd files in MS Access 2000 and 2003 format. I have attached a test script and some test data that reproduce the error. The test script assumes that the database contains a table UNICODE with the fields UNICODE_VARCHAR and UNICODE_TEXT. UNICODE_VARCHAR should be a Text field with a size larger than 127. UNICODE_TEXT should be a Memo field.
Subject: test_unicode.pl
Download test_unicode.pl
text/x-perl 654b
#!perl use strict; use warnings; use DBI; my $access_dns = "driver={Microsoft Access Driver (*.mdb)};dbq=unicode.mdb"; my $dbh = DBI->connect( "DBI:ODBC:$access_dns" ) or die DBI->errstr(); my $insert_sql = 'INSERT INTO UNICODE ( UNICODE_VARCHAR, UNICODE_TEXT ) VALUES ( ?, ? )'; my $insert_stmt = $dbh->prepare( $insert_sql ) or die $dbh->errstr(); open my $UNICODE_DATA, '<:utf8', 'unicode_data.txt' or die $!; while( my $line = <$UNICODE_DATA> ){ chomp( $line ); my @values = split ',', $line; $insert_stmt->execute( @values ) or warn $dbh->errstr(); }
Subject: unicode_data.txt
Download unicode_data.txt
text/plain 557b
ø,ø øøøøø,øøøøø 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678,fails fails,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678
Download (untitled) / with headers
text/plain 604b
After some more testing I have found that it does not seem to be relevant that the characters are encoded as utf8. Inserting more than 127 characters gives the same error for characters encoded as latin1. Inserting more than 127 characters have not been a problem with earlier versions of the DBD::ODBC driver. I have tested it against version 1.10 and with that version inserting more than 127 characters in latin1 encoding seems to work fine. I have attached an additional file of test data and a updated test script that reads both data files an attempts to insert the data into the database.
Download latin1_data.txt
text/plain 572b
latin1,latin1 ø,ø øøøøø,øøøøø 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678,fails fails,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678
Download test_unicode.pl
text/x-perl 869b
#!perl use strict; use warnings; use DBI; my $access_dns = "driver={Microsoft Access Driver (*.mdb)};dbq=unicode.mdb"; my $dbh = DBI->connect( "DBI:ODBC:$access_dns" ) or die DBI->errstr(); my $insert_sql = 'INSERT INTO UNICODE ( UNICODE_VARCHAR, UNICODE_TEXT ) VALUES ( ?, ? )'; my $insert_stmt = $dbh->prepare( $insert_sql ) or die $dbh->errstr(); open my $UNICODE_DATA, '<:utf8', 'unicode_data.txt' or die $!; while( my $line = <$UNICODE_DATA> ){ chomp( $line ); my @values = split ',', $line; $insert_stmt->execute( @values ) or warn $dbh->errstr(); } open my $LATIN_DATA, '<', 'latin1_data.txt' or die $!; while( my $line = <$LATIN_DATA> ){ chomp( $line ); my @values = split ',', $line; $insert_stmt->execute( @values ) or warn $dbh->errstr(); }
Download (untitled) / with headers
text/plain 373b
Apologies for not replying to your bug report sooner. I normally get emails from the rt system when a bug is posted or changed but I have received none recently and did not notice this until I spotted the following thread on perl monks: http://www.perlmonks.org/?node_id=746522 I will try and look in to this in the next few days. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.6k
DBD::ODBC has changed massively since 1.10. Provisional unicode support on Windows was added in 1.14 and full unicode support for all platforms was finished in 1.16_2. The default on Windows is now to build with unicode support (see -u and -nou when building). This means that DBD::ODBC will default to using SQLW functions like SQLDriverConnectW so you can use unicode connection strings and settings and SQL_WVARCHAR for data where it can. You can put DBD::ODBC back to the old behavior with odbc_default_bind_type, setting it to SQL_VARCHAR or you can rebuild without the ne unicode support. When you are binding data to an insert usually DBD::ODBC calls SQLDescribeParam to find out about the bound type but the MSAccess ODBC driver does not support that ODBC API. In the absence of this API and not being able to know the data you are binding DBD::ODBC defaults in the case of a unicode build to using SQL_WVARCHAR. This means your bound data is converted to wide characters - it does not matter if there is only ascii characters in the data - it is still converted to wide characters (of course this does mean if you have some unicode chrs in your perl data encoded in utf-8 they get in the database properly now). However, I have discovered a problem with MS Access and inserting wide chrs into text columns (bare in mind with the unicode support even ascii is converted to wide chrs) where it seems to need the number of wide chrs instead of the number of bytes for the SQLBindParameter ColDisplaySize field. I have changed this and can make a new DBD::ODBC available to you if you can build it. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 327b
I have no previous experience with building the DBD::ODBC driver from scratch, but if you make your changes available I will try to build it and see if the changes you have made fixes the problems with Unicode on MS Access. I tested setting odbc_default_bind_type to 12 and that also fixed the problem as you suggested.
On Wed Mar 11 06:36:51 2009, oyse wrote: Show quoted text
> I have no previous experience with building the DBD::ODBC driver from > scratch, but if you make your changes available I will try to build it > and see if the changes you have made fixes the problems with Unicode on > MS Access. > > I tested setting odbc_default_bind_type to 12 and that also fixed the > problem as you suggested.
It is available from cpan at http://search.cpan.org/dist/DBD-ODBC/. Select the download button next to 1.18_2. How you build it slightly depends on what perl distribution you have on Windows e.g., strawberry perl, active state etc. A reasonable start is to unpack it and try running perl Makefile.PL to see how you go. If you are using activestate perl you will need a MS C compiler and nmake on your path or you will need to install MinGW. If you are using strawberry perl it comes with a compiler and you can just do: perl -MCPAN -e shell install M/MJ/MJEVANS/DBD-ODBC-1.18_2.tar.gz I can try and help once I know what perl you are running and what facilities you have. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 2.2k
On Ons. 11. Mar. 2009 07:04:44, MJEVANS wrote: Show quoted text
> On Wed Mar 11 06:36:51 2009, oyse wrote:
> > I have no previous experience with building the DBD::ODBC driver
from Show quoted text
> > scratch, but if you make your changes available I will try to build
it Show quoted text
> > and see if the changes you have made fixes the problems with
Unicode on Show quoted text
> > MS Access. > > > > I tested setting odbc_default_bind_type to 12 and that also fixed
the Show quoted text
> > problem as you suggested.
> > It is available from cpan at http://search.cpan.org/dist/DBD-ODBC/. > Select the download button next to 1.18_2. > > How you build it slightly depends on what perl distribution you have
on Show quoted text
> Windows e.g., strawberry perl, active state etc. A reasonable start is > to unpack it and try running perl Makefile.PL to see how you go. If
you Show quoted text
> are using activestate perl you will need a MS C compiler and nmake on > your path or you will need to install MinGW. If you are using
strawberry Show quoted text
> perl it comes with a compiler and you can just do: > > perl -MCPAN -e shell > install M/MJ/MJEVANS/DBD-ODBC-1.18_2.tar.gz > > I can try and help once I know what perl you are running and what > facilities you have. > > Martin
I have both ActiveState and Strawberry Perl installed. I have trouble building with ActiveState. The code compiles just fine, but there is a problem with the dll file msvcr80.dll when I try to run the tests. The error messages is "This application has failed to start because MSVCR80.dll was not found. Re-installing the application may fix the problem." I think the problem has something to do with versions of this dll, but I haven't been able to track down the problem. I have managed to build the latest version under Strawberry Perl and I have been running some tests and your changes seems to fix the problem in most cases. That is inserting both Unicode and Latin1 text works fine with both Text and Memo for more than 127 characters. There seems to be a slight problem when inserting more than 127 Latin1 characters into a column with bind type SQL_VARCHAR. The insert works fine, but you get an error message that says "Invalid precision value". I have attached my updated test files. The test script now assumes that the size of the Text field is 150. Thanks for all the help so far.
Download latin1_data.txt
text/plain 910b
latin1,latin1 ø,ø øøøøø,øøøøø 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678,gives binding error insert ok ok,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678 ok,øøøøø12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345671234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567012345678901234567890123456789012345678901234567
Download test_unicode.pl
text/x-perl 1.1k
#!perl use strict; use warnings; use DBI qw(:sql_types);; my $access_dns = "driver={Microsoft Access Driver (*.mdb)};dbq=unicode.mdb"; my $dbh = DBI->connect( "DBI:ODBC:$access_dns" ) or die DBI->errstr(); my $insert_sql = 'INSERT INTO UNICODE ( UNICODE_VARCHAR, UNICODE_TEXT ) VALUES ( ?, ? )'; my $insert_stmt = $dbh->prepare( $insert_sql ) or die $dbh->errstr(); open my $UNICODE_DATA, '<:utf8', 'unicode_data.txt' or die $!; while( my $line = <$UNICODE_DATA> ){ chomp( $line ); my @values = split ',', $line; $insert_stmt->bind_param( 1, $values[0] ); $insert_stmt->bind_param( 2, $values[1], { TYPE => SQL_WLONGVARCHAR } ); $insert_stmt->execute() or warn $dbh->errstr(); } open my $LATIN_DATA, '<', 'latin1_data.txt' or die $!; while( my $line = <$LATIN_DATA> ){ chomp( $line ); my @values = split ',', $line; $insert_stmt->bind_param( 1, $values[0], { TYPE => SQL_VARCHAR } ); $insert_stmt->bind_param( 2, $values[1], { TYPE => SQL_LONGVARCHAR } ); $insert_stmt->execute() or warn $dbh->errstr(); }
unicode,unicode ø,ø øøøøø,øøøøø ok 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678,ok ok,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678 ok øøø34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567,ok øæåøæøåøæøæ12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345671234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567012345678901234567890123456789012345678901234567
Download (untitled) / with headers
text/plain 1.5k
On Wed Mar 11 11:02:25 2009, oyse wrote: Show quoted text
> I have both ActiveState and Strawberry Perl installed. I have trouble > building with ActiveState. The code compiles just fine, but there is a > problem with the dll file msvcr80.dll when I try to run the tests. The > error messages is "This application has failed to start because > MSVCR80.dll was not found. Re-installing the application may fix the > problem." I think the problem has something to do with versions of this > dll, but I haven't been able to track down the problem.
That does not surprise me. You need to have a pretty specific set of compilers to build it yourself for activestate. If you send your email address to mjevans@cpan.org I can forward some stuff to you I got from activestate. Show quoted text
> I have managed to build the latest version under Strawberry Perl and I > have been running some tests and your changes seems to fix the problem > in most cases.
Yes, strawberry perl seems alot easier for building modules. Show quoted text
> That is inserting both Unicode and Latin1 text works > fine with both Text and Memo for more than 127 characters. > > There seems to be a slight problem when inserting more than 127 Latin1 > characters into a column with bind type SQL_VARCHAR. The insert works > fine, but you get an error message that says "Invalid precision value". > > I have attached my updated test files. The test script now assumes that > the size of the Text field is 150. > > Thanks for all the help so far.
Can I just confirm the schema for your table that you are using now. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 563b
On Ons. 11. Mar. 2009 11:12:43, MJEVANS wrote: Show quoted text
> That does not surprise me. You need to have a pretty specific set of > compilers to build it yourself for activestate. If you send your email > address to mjevans@cpan.org I can forward some stuff to you I got from > activestate.
I have sent you an email to the above address. Show quoted text
> Can I just confirm the schema for your table that you are using now.
The test file assumes that you have a table named UNICODE with the following columns: ID AUTOINCREMENT PRIMARY KEY UNICODE_VARCHAR VARCHAR(150) UNICODE_TEXT MEMO
Download (untitled) / with headers
text/plain 302b
On Wed Mar 11 11:30:28 2009, oyse wrote: Show quoted text
> The test file assumes that you have a table named UNICODE with the > following columns: > ID AUTOINCREMENT PRIMARY KEY > UNICODE_VARCHAR VARCHAR(150) > UNICODE_TEXT MEMO
Thanks, I'll try and look into this tonight. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 424b
On Wed Mar 11 11:44:40 2009, MJEVANS wrote: Show quoted text
> On Wed Mar 11 11:30:28 2009, oyse wrote: >
> > The test file assumes that you have a table named UNICODE with the > > following columns: > > ID AUTOINCREMENT PRIMARY KEY > > UNICODE_VARCHAR VARCHAR(150) > > UNICODE_TEXT MEMO
> > Thanks, I'll try and look into this tonight. > > Martin
Try the dbdimp.c I've added to this rt report. Martin -- Martin J. Evans Wetherby, UK
Download dbdimp.c
text/x-csrc 176.9k

Message body is not shown because it is too large.

Download (untitled) / with headers
text/plain 582b
On Ons. 11. Mar. 2009 12:19:12, MJEVANS wrote: Show quoted text
> Try the dbdimp.c I've added to this rt report. > > Martin
Seems like that fixed the problem. I compiled the driver with the new file and ran the tests and the "Invalid precision error" disappeared. I also added a test cases with inserting exactly as many characters as the Text column allowed and that also worked without any problems. Inserting more than the maximum allowed characters (in this case 151) also worked fine, but truncated the value silently. Is this the intended behaviour or perhaps an MS Access feature?
Download (untitled) / with headers
text/plain 1.1k
On Wed Mar 11 15:01:05 2009, oyse wrote: Show quoted text
> On Ons. 11. Mar. 2009 12:19:12, MJEVANS wrote:
> > Try the dbdimp.c I've added to this rt report. > > > > Martin
> > Seems like that fixed the problem. I compiled the driver with the new > file and ran the tests and the "Invalid precision error" disappeared. > > I also added a test cases with inserting exactly as many characters as > the Text column allowed and that also worked without any problems. > > Inserting more than the maximum allowed characters (in this case 151) > also worked fine, but truncated the value silently. Is this the > intended behaviour or perhaps an MS Access feature? >
I guess that is a feature of access as most drivers will return a data truncated error. I will try and confirm that properly but I'm sure DBD::ODBC is not truncated it. Thanks for working through this with me and testing the changes. Are you happy for me to write this bug off as fixed now? The official changes and a test case for access will be in 1.18_3 which I will release either today but if the other person who reported the SQL Server issue comes back to me before then it may be later. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 321b
On Tor. 12. Mar. 2009 04:50:18, MJEVANS wrote: Show quoted text
> Are you happy for me to write this bug off as fixed now?
Yes. I have performed some more tests on the updated driver which also confirm that the driver behaviour is now as intended. Thanks again for your excellent help and quick respons. Best Regards Øystein Torget


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.