Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-1758

ODBC protocol should implement DEALLOCATE command

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Done
    • 7.5
    • 7.6
    • None
    • None

    Description

      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)

      #!/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();
      

      The output we get when the script fails on newer systems is as follows

      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
      

      The output we get on RHEL5 is

      selectall_hashref max_id =  2568235
      selectrow_hashref max_id =  2568235
      

      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

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            graeme.gillies Graeme Gillies (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: