Writing binary data (bytea) fails in PostgreSQL 9.3, succesful in PostgreSQL 9.0 - Mailing list pgsql-odbc
From | Esa Östring |
---|---|
Subject | Writing binary data (bytea) fails in PostgreSQL 9.3, succesful in PostgreSQL 9.0 |
Date | |
Msg-id | 54FED966.7070705@infotripla.fi Whole thread Raw |
List | pgsql-odbc |
Dear list, I am trying to write binary data to bytea column in PostgreSQL 9.3 with unixODBC, but the process fails. Symptom is, that some data is escaped in bytea column after write, and the overall length of binary data is thus increased during write,resulting corrupted data. Same code works perfectly with PostgreSQL 9.0, length of binary data is preserved. Exact versions of servers are PostreSQL 9.3.6 and 9.0.3. Versions of drivers used (rpm packages, up to date Fedora 20): ----8<---- postgresql-odbc-09.03.0400-3.fc20.x86_64 perl-DBD-ODBC-1.50-3.fc20.x86_64 unixODBC-2.3.2-4.fc20.x86_64 ----8<---- I have tried to isolate the problem, and wrote a perl script to be able to recreate the problem. Please see the details below. Should I use some extra configuration parameters to force binary data write for postgres 9.3, or is there something else I'm doing wrong? Perl DBD::Pg -driver (not odbc) seems to handle binary data write correctly (code not provided here), but odbc is really THE option for me, since I am using odbc functionality from R, using RODBC. This code excerpt is simply an isolation of problems I faced in RODBC. Details follow: * perl script to reconstruct the problem in postgresql 9.0 and postgresql 9.3 databases. ----8<---- #!/usr/bin/perl -w use strict; use DBI qw(:sql_types); # postgresql 9.0 my $data_source90 = q/dbi:ODBC:pg90/; # postgresql 9.3 my $data_source93 = q/dbi:ODBC:pg93/; # Connect to the data source and get a handle for that connection. my $dbh90 = DBI->connect($data_source90) or die "Can't connect to $data_source90: $DBI::errstr"; my $dbh93 = DBI->connect($data_source93) or die "Can't connect to $data_source93: $DBI::errstr"; # read file my $buffer = ""; #my $infile = "read_me_in.png"; my $infile = "read_me_in.wld"; open (INFILE, "<", $infile) or die "Not able to open the file. \n"; binmode (INFILE); my $file; #Read file in 64K blocks while ( (read (INFILE, $buffer, 65536)) != 0 ) { $file .= $buffer; } close (INFILE) or die "Not able to close the file: $infile \n"; print length($file), "\n"; my $sth90 = $dbh90->prepare("INSERT INTO data_queue (data) VALUES (?)") or die "Can't prepare statement: $DBI::errstr"; $sth90->bind_param(1, $file, {TYPE => SQL_BINARY}); $sth90->execute(); my $sth93 = $dbh93->prepare("INSERT INTO data_queue (data) VALUES (?)") or die "Can't prepare statement: $DBI::errstr"; $sth93->bind_param(1, $file, {TYPE => SQL_BINARY}); $sth93->execute(); # Disconnect the database from the database handle. $dbh90->disconnect; $dbh93->disconnect; ----8<---- Excerpt from psql, after data is input to database: postgres 9.3 (binary write failed) ----8<---- data_static=> select length(data) from data_queue ; length -------- 134 (1 row) data_static=> select encode(data,'escape') from data_queue ; encode ----------------------------------------------------------------------------------------------------------------------------------------------------- 1802\\0568571428571\\0120\\0560000000000\\0120\\0560000000000\\012\\0551742\\0568571428573\\01298901\\0564285714286\\0127779128\\0565714286640\\012 (1 row) data_static=> \d data_queue Table "public.data_queue" Column | Type | Modifiers --------------------------+--------------------------+----------- id | bigint | data | bytea | ----8<---- postgres 9.0 (all OK) ----8<---- data_static=> select encode(data,'escape') from data_queue ; encode -------------------- 1802.8571428571 + 0.0000000000 + 0.0000000000 + -1742.8571428573 + 98901.4285714286 + 7779128.5714286640+ (1 row) data_static=> \d data_queue Table "public.data_queue" Column | Type | Modifiers --------------------------+--------------------------+----------- id | bigint | data | bytea | data_static=> select length(data) from data_queue ; length -------- 95 (1 row) ----8<---- Best regards, M.Sc Esa Östring Data scientist Infotripla Oy Tampere, Finland
pgsql-odbc by date: