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

Error when binding NULL to nullable parameter in a prepared statement with PDO PostgreSQL ODBC client

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 11.2.2, 12.0.1, 12.1
    • 11.0, 11.2
    • ODBC
    • None
    • Hide

      1. Use Docker to install and configure the following containers

      a. PostgreSQL database (library/postgres:latest)
      b. Teiid with Wildfly (jboss/teiid:latest)
      c. Apache/PHP (php:7.1-apache)

      2. For Teiid deploy JDBC driver for PostgreSQL (latest from Maven) and create a datasource in standalone-teiid.xml

      3. For PHP, install PDO extension (pdo_pgsql) for PostgreSQL, in Dockerfile, using docker-php-ext-install, for example.

      4. Create a table in Postgres DB with an optional (nullable) field

      CREATE TABLE public.foobar (
	id int4 NOT NULL,
	optional varchar NULL,
	CONSTRAINT foobar_pk PRIMARY KEY (id)
);

      5. In Teiid create and deploy a VDB with a model using the above Postgres datasource.

      6. In PHP try to create a prepared statement for an INSERT into the table created above and bind a null value to the optional parameter.

      $dbh = new PDO('pgsql:host=<teiid_host>;port=35432;dbname=<vdb_name>;user=<teiid_user>;password=<teiid_password>');
$stmt = $dbh->prepare("INSERT INTO foobar (id, optional) VALUES(?, ?)");
$stmt->bindValue(1, 1);
$stmt->bindValue(2, null, PDO::PARAM_NULL);
$stmt->execute();

      The exception is thrown.

      As a temporary fix we changed the code in org.teiid.transport.PgFrontendProtocol class method "buildBind" to 

      // ...
for (int i = 0; i < paramCount; i++) {
            int paramLen = data.readInt();
            
            // do not create an array for paramLen = -1
            if (paramLen == -1){
            	continue;
	    }
// ...
}

      After deploying this change (in a JAR teiid-runtime-11.2.0.jar) to WildFly the binding seems to work.

      Show
      1. Use Docker to install and configure the following containers a. PostgreSQL database (library/postgres:latest) b. Teiid with Wildfly (jboss/teiid:latest) c. Apache/PHP (php:7.1-apache) 2. For Teiid deploy JDBC driver for PostgreSQL (latest from Maven) and create a datasource in standalone-teiid.xml 3. For PHP, install PDO extension (pdo_pgsql) for PostgreSQL, in Dockerfile, using docker-php-ext-install, for example. 4. Create a table in Postgres DB with an optional (nullable) field CREATE TABLE public .foobar ( id int4 NOT NULL , optional varchar NULL , CONSTRAINT foobar_pk PRIMARY KEY (id) ); 5. In Teiid create and deploy a VDB with a model using the above Postgres datasource. 6. In PHP try to create a prepared statement for an INSERT into the table created above and bind a null value to the optional parameter. $dbh = new PDO( 'pgsql:host=<teiid_host>;port=35432;dbname=<vdb_name>;user=<teiid_user>;password=<teiid_password>' ); $stmt = $dbh ->prepare("INSERT INTO foobar (id, optional) VALUES(?, ?)"); $stmt ->bindValue(1, 1); $stmt ->bindValue(2, null , PDO::PARAM_NULL); $stmt ->execute(); The exception is thrown. As a temporary fix we changed the code in org.teiid.transport.PgFrontendProtocol class method "buildBind" to // ... for ( int i = 0; i < paramCount; i++) { int paramLen = data.readInt(); // do not create an array for paramLen = -1 if (paramLen == -1){ continue ; } // ... } After deploying this change (in a JAR teiid-runtime-11.2.0.jar) to WildFly the binding seems to work.

      On Teiid 11.0 (and probably later versions, as well) running in a WildFly there is a problem when binding to a NULLABLE parameter in a prepared statement with NULL value when running against a PostgreSQL source from a PDO (PHP) ODBC client.

      PDO calls causing the error are related to PDOStatement::bindValue
      and PDOStatement::bindParam.

      It occurs exclusively when the client is trying to bind a NULL value (corresponding to a NULLABLE column in a table) to the prepared statement using PDO::PARAM_NULL type.

      Example PHP code

      $dbh = new PDO('pgsql:host=<teiid_host>;port=35432;dbname=<vdb_name>;user=<teiid_user>;password=<teiid_password>');
$stmt = $dbh->prepare("INSERT INTO foobar (id, optional) VALUES(?, ?)");
$stmt->bindValue(1, 1);
$stmt->bindValue(2, null, PDO::PARAM_NULL);
$stmt->execute();

      Exception thrown is

      Caused by: java.lang.NegativeArraySizeException
      at org.teiid.transport.PgFrontendProtocol.createByteArray(PgFrontendProtocol.java:328)
      at org.teiid.transport.PgFrontendProtocol.buildBind(PgFrontendProtocol.java:266)
      at org.teiid.transport.PgFrontendProtocol.createRequestMessage(PgFrontendProtocol.java:153)
      at org.teiid.transport.PgFrontendProtocol.decode(PgFrontendProtocol.java:133)
      at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:411)
      ... 15 more

              rhn-engineering-shawkins Steven Hawkins
              george.ushakov George Ushakov (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: