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

[Ramesh Reddy (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1758?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ramesh Reddy updated TEIID-1758:
--------------------------------

    Summary: ODBC protocol should implement DEALLOCATE command  (was: Native PG Connector implement postgres 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

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira