Skip Menu |

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

Report information
The Basics
Id: 89255
Status: resolved
Priority: 0/

Owner: Nobody in particular
Requestors: MICHIELB [...]

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 1.49_3

Subject: Fails to create test table for tests using PostgreSQL odbc driver
Download (untitled) / with headers
text/plain 31.5k

Message body is not shown because it is too large.

** repost but with less test output - so it will be displayed in RT ** Hi, I wanted to make sure the tests run using the PostgreSQL ODBC driver. Here are the steps I took on Debian: sudo apt-get install unixodbc unixodbc-dev odbc-postgresql postgresql-server set the server to MD5 identification so I can use regular passwords, as described here: su to the 'postgres' user and add a test user and database like this: $ createuser -D -P -S -R odbct $ createdb --encoding=utf8 --owner=odbct odbct export appropriate test variables # env | grep DBI DBI_DSN=dbi:ODBC:DRIVER={PostgreSQL Unicode};Server=localhost;Port=5432;Database=odbct DBI_USER=odbct DBI_PASS=odbct it fails to create a test table, because the create table statement generated is CREATE TABLE PERL_DBD_TEST (COL_A int2 NOT NULL PRIMARY KEY , COL_B varchar(), COL_C text, COL_D timestamptz) note the missing length in COL_B varchar() Even if I hardcode the value for the length in t/ I get lots of test failures, I guess not many people bothered to test this driver recently. this is the output of 'make test' PERL_DL_NONLAZY=1 /home/michiel/.plenv/versions/5.19.3/bin/perl5.19.3 "-MExtUtils::Command::MM" "-MTest::Harness" "-e" "undef *Test::Harness::Switches; test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/01base.t ................... ok # # Perl 5.19.3 # osname=linux, osvers=3.2.0-4-686-pae, archname=i686-linux # Using DBI 1.628 # Using DBD::ODBC 1.44_3 # Using DBMS_NAME 'PostgreSQL' # Using DBMS_VER '9.1.9' # Using DRIVER_NAME '' # Using DRIVER_VER '09.01.0100' # odbc_has_unicode 0 # Failed test 'ReadOnly set' # at t/02simple.t line 57. # got: '0' # expected: '1' # Failed to create table - [unixODBC]ERROR: syntax error at or near ")"; # Error while executing the query (SQL-42601) # Failed test 'create test table' # at t/02simple.t line 114. # Failed test 'test table exists' # at t/02simple.t line 116. # '-1' # >= # '0' # [unixODBC]ERROR: relation "perl_dbd_test" does not exist; # Error while executing the query (SQL-42P01)
Download (untitled) / with headers
text/plain 1.7k
I have reproduced this issue and it is down to Postgres ODBC Driver not returning COLUMN_SIZE. I sent the following email to the postgres ODBC mailing list but it has not been answered as yet: ========== I may have discovered an issue with the postgres ODBC Driver. Postgres ODBC Driver version: perl -MDBI -le 'my $h = DBI->connect; print $h->get_info(7);' 09.00.0310 Platform: Linux, Ubuntu I maintain Perl DBD::ODBC and the test suite shows a problem because when calling SQLGetTypeInfo some types return 'CREATE_PARAMS' => 'max. length' but then the COLUMN_SIZE is missing. e.g.: $VAR1 = { 'UNSIGNED_ATTRIBUTE' => undef, 'MAXIMUM_SCALE' => undef, 'INTERVAL_PRECISION' => '0', 'CREATE_PARAMS' => 'max. length', 'NUM_PREC_RADIX' => undef, 'PRECISION' => 255, 'SEARCHABLE' => '3', 'MONEY' => '0', 'AUTO_INCREMENT' => undef, 'LOCAL_TYPE_NAME' => undef, 'LITERAL_PREFIX' => '\'', 'MINIMUM_SCALE' => undef, 'TYPE_NAME' => 'varchar', 'NULLABLE' => '1', 'DATA_TYPE' => 12, 'SQL_DATA_TYPE' => '12', 'LITERAL_SUFFIX' => '\'', 'CASE_SENSITIVE' => '1', 'SQL_DATETIME_SUB' => undef }; See where COLUMN_SIZE was introduced in ODBC 2 (decades ago) and should exist in the columns returned. Without it, the application cannot know what to put in varchar (size_here) after a column in a create table. I've put in a special case for now to work around this problem but it issues a warning too. If you need anything further from me please let me know. ========== Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.4k
Heikki Linnakangas replied: It's there with the old name "PRECISION". That Microsoft page says that: "The following columns have been renamed for ODBC 3.x. The column name changes do not affect backward compatibility because applications bind by column number." So technically, I think the driver is not doing anything wrong when it's returning the old name, although I agree it would be about time to switch to the new names... - Heikki and I replied: Agreed, the ODBC driver is not really doing anything wrong as such. The problem is that interfaces on top of ODBC (like Perl's DBI module) dictate the fields are accessed by the name of the column (see and they name COLUMN_SIZE and not PRECISION. ODBC 2 is more than 15 years old now. The result for the postgres driver is still confusing as it is as the same MS page states for ODBC 3.x the columns SQL_DATA_TYPE, INTERVAL_PRECISION, SQL_DATETIME_SUB and NUM_PREC_RADIX were added. I note the postgres result has all 4 but still refers to the old ODBC 2 PRECISION. At a minimum this is inconsistent. Martin So it seems unlikely anything is going to change in the near future. I will try and change the DBD::ODBC test suite to fallback on PRECISION. BTW, the threads from pgodbc list are at and Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 175b
This is fixed in github and will be in 1.49_3. The code now falls back on the old ODBC 2 PRECISION if ODBC 3 COLUMN_SIZE is not found. Martin -- Martin J. Evans Wetherby, UK

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

Please report any issues with to