Bug #89255 for DBD-ODBC: Fails to create test table for tests using PostgreSQL odbc driver

** 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: https://help.ubuntu.com/10.04/serverguide/postgresql.html 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/ODBCTEST.pm 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 'psqlodbcw.so' # 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)

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 http://search.cpan.org/~timb/DBI-1.631/DBI.pm#type_info) 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 http://www.postgresql.org/message-id/5315E336.40603@vmware.com and http://www.postgresql.org/message-id/5315E622.2010904@ntlworld.com Martin -- Martin J. Evans Wetherby, UK

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