[teiid-issues] [JBoss JIRA] Updated: (TEIID-1758) ODBC protocol should implement DEALLOCATE command

ODBC protocol should implement DEALLOCATE command ------------------------------------------------- Key: TEIID-1758 URL: https://issues.jboss.org/browse/TEIID-1758 Project: Teiid Issue Type: Bug Affects Versions: 7.5 Environment: Teiid 7.5.0.Final using java version "1.6.0_20" OpenJDK Runtime Environment (IcedTea6 1.9.8) (rhel-1.22.1.9.8.el5_6-x86_64) OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode) Running inside of JBoss EAP 5.1.1 on Red Hat Enterprise Linux Server release 5.7 problem occurs with perl-DBD-Pg versions 2.15.1-3.el6 and 2.18.0-2.fc15.i686 (and possibly others) Reporter: Graeme Gillies Assignee: Steven Hawkins Hi, We currently are using the below script to try and connect to our teiid instance and pull out a bunch of data. and we have noticed that the script works fine on Rhel5 (using perl-DBD-Pg-1.49-2.el5_3.1 for example) but does not work on newer OS's like Fedora 15 or Rhel6 (which have perl-DBD-Pg versions 2.15.1-3.el6 and 2.18.0-2.fc15.i686) {code} #!/usr/bin/env perl use strict; use warnings; use DBI; use DBD::Pg; my $dbh = DBI->connect("dbi:Pg:dbname=EngVDBF;host=vdb.engineering.redhat.com;port=35432", 'teiid', 'teiid', {AutoCommit => 0}) || die "Could not connect to database: $DBI::errstr"; my $ret = $dbh->selectall_hashref("SELECT MAX(Id) AS max_id FROM Rt4S.Transactions", 'max_id'); my ($result) = keys %$ret; printf "selectall_hashref max_id = %8d\n", $result; $ret = $dbh->selectrow_hashref("SELECT MAX(Id) AS max_id FROM Rt4S.Transactions"); printf "selectrow_hashref max_id = %8d\n", $ret->{max_id}; print "done!"; $dbh->disconnect(); {code} The output we get when the script fails on newer systems is as follows {noformat} DBD::Pg::db selectall_hashref failed: ERROR: Parsing error: Encountered "DEALLOCATE" at line 1, column 1. Was expecting one of: "alter" ... "call" ... "create" ... "delete" ... "drop" ... "exec" ... "execute" ... "insert" ... "select" ... "table" ... ... DETAIL: org.teiid.jdbc.TeiidSQLException: Parsing error: Encountered "DEALLOCATE" at line 1, column 1. Was expecting one of: "alter" ... "call" ... "create" ... "delete" ... "drop" ... "exec" ... "execute" ... "insert" ... "select" ... "table" ... ... at ./teiid-fail.pl line 12. selectall_hashref max_id = 2568235 DBD::Pg::db selectrow_hashref failed: ERROR: Parsing error: Encountered "DEALLOCATE" at line 1, column 1. Was expecting one of: "alter" ... "call" ... "create" ... "delete" ... "drop" ... "exec" ... "execute" ... "insert" ... "select" ... "table" ... ... DETAIL: org.teiid.jdbc.TeiidSQLException: Parsing error: Encountered "DEALLOCATE" at line 1, column 1. Was expecting one of: "alter" ... "call" ... "create" ... "delete" ... "drop" ... "exec" ... "execute" ... "insert" ... "select" ... "table" ... ... at ./teiid-fail.pl line 16. selectrow_hashref max_id = 2568235 {noformat} The output we get on RHEL5 is {noformat} selectall_hashref max_id = 2568235 selectrow_hashref max_id = 2568235 {noformat} As you can see the query does run and is displayed correctly, but it looks like the Postgres driver sends a DEALLOCATE command to the server http://www.postgresql.org/docs/8.1/static/sql-deallocate.html which teiid doesn't know what it means so throws the error. I tried to look in the DBD-Pg docs to see if I can get it to not call DEALLOCATE, but it looks like that might not be possible. Other languages postgres libraries might call the same command anyway. Is it possible to have the DEALLOCATE statement implemented in teiid (even if it doesn't actually do anything) so that native libraries trying to call it won't throw these errors? Regards, Graeme