Text array in prepared statements returns null when using binary tranfer

Поиск
Список
Период
Сортировка
От Asier Lostalé
Тема Text array in prepared statements returns null when using binary tranfer
Дата
Msg-id CABtr+CJC54wue94UweBocnDnL0CfM3s6kC4Ckt48NmztwQMdKQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Text array in prepared statements returns null when using binary tranfer  (Dave Cramer <pg@fastcrypt.com>)
Список pgsql-jdbc
Дерево обсуждения
Hi, 

When using binary transfer, I'm having problems using a prepared statement that returns a text array type: it returns null when it uses the prepared statement where it should have value.

If I set the binaryTransfer property to false, it works fine. 

I've tested it using postgresql-9.3-1102.jdbc4.jar in a PG 9.2 and 9.3 database. 

With older jdbc versions (ie. postgresql-9.0-801.jdbc4.jar), which don't implement the binary transfer, it works fine. 

Here is the code I tested: 

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class TestMe {
  public static void main(String[] args) throws SQLException {
    String url = "jdbc:postgresql://localhost:5433/pi1";
    Connection conn = null;
    // org.postgresql.Driver.setLogLevel(org.postgresql.Driver.DEBUG);

    Properties connectionProps = new Properties();
    connectionProps.put("user", "tad");
    connectionProps.put("password", "password");
    connectionProps.put("binaryTransfer", "true");
    conn = DriverManager.getConnection(url, connectionProps);

    Driver driver = DriverManager.getDriver(url);
    conn = driver.connect(url, connectionProps);
    System.out.println(org.postgresql.Driver.MAJORVERSION + "."
        + org.postgresql.Driver.MINORVERSION);

    PreparedStatement fs = conn
        .prepareStatement("SELECT proargnames FROM pg_proc where proname ='pg_cursor'");

    ((org.postgresql.PGStatement) fs).setPrepareThreshold(1);
    // execute twice to use prepared statement 2nd time
    for (int i = 0; i < 2; i++) {
      ResultSet rs = fs.executeQuery();
      rs.next();

      System.out.println(rs.getArray(1));
      rs.close();
    }
  }
}

This is the output:

9.3
{name,statement,is_holdable,is_binary,is_scrollable,creation_time}
null

note second time it return null for the same query.

Here is the output setting log to debug.

14:20:58.961 (1) PostgreSQL 9.3 JDBC4 (build 1102)
14:20:58.967 (1) Trying to establish a protocol version 3 connection to localhost:5433
14:20:58.991 (1) Receive Buffer Size is 131003
14:20:58.991 (1) Send Buffer Size is 331875
14:20:58.991 (1)  FE=> StartupPacket(user=tad, database=pi1, client_encoding=UTF8, DateStyle=ISO, extra_float_digits=2, TimeZone=Europe/Madrid)
14:20:58.994 (1)  <=BE AuthenticationOk
14:20:59.008 (1)  <=BE ParameterStatus(application_name = )
14:20:59.008 (1)  <=BE ParameterStatus(client_encoding = UTF8)
14:20:59.009 (1)  <=BE ParameterStatus(DateStyle = ISO, DMY)
14:20:59.009 (1)  <=BE ParameterStatus(integer_datetimes = on)
14:20:59.009 (1)  <=BE ParameterStatus(IntervalStyle = postgres)
14:20:59.009 (1)  <=BE ParameterStatus(is_superuser = on)
14:20:59.009 (1)  <=BE ParameterStatus(server_encoding = UTF8)
14:20:59.009 (1)  <=BE ParameterStatus(server_version = 9.3.5)
14:20:59.009 (1)  <=BE ParameterStatus(session_authorization = tad)
14:20:59.009 (1)  <=BE ParameterStatus(standard_conforming_strings = on)
14:20:59.009 (1)  <=BE ParameterStatus(TimeZone = Europe/Madrid)
14:20:59.009 (1)  <=BE BackendKeyData(pid=28270,ckey=1496394801)
14:20:59.009 (1)  <=BE ReadyForQuery(I)
14:20:59.010 (1) simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@6bc947, maxRows=0, fetchSize=0, flags=23
14:20:59.011 (1)  FE=> Parse(stmt=null,query="SET extra_float_digits = 3",oids={})
14:20:59.012 (1)  FE=> Bind(stmt=null,portal=null)
14:20:59.012 (1)  FE=> Execute(portal=null,limit=1)
14:20:59.012 (1)  FE=> Sync
14:20:59.013 (1)  <=BE ParseComplete [null]
14:20:59.013 (1)  <=BE BindComplete [null]
14:20:59.013 (1)  <=BE CommandStatus(SET)
14:20:59.013 (1)  <=BE ReadyForQuery(I)
14:20:59.014 (1)     compatible = 9.3
14:20:59.014 (1)     loglevel = 2
14:20:59.015 (1)     prepare threshold = 5
14:20:59.020 (1)     types using binary send = INT8_ARRAY,TIMESTAMPTZ,FLOAT4_ARRAY,FLOAT8_ARRAY,UUID,TEXT_ARRAY,VARCHAR_ARRAY,BYTEA,TIME,FLOAT4,FLOAT8,INT2_ARRAY,TIMETZ,INT2,INT8,INT4,INT4_ARRAY,TIMESTAMP,POINT,BOX
14:20:59.023 (1)     types using binary receive = INT8_ARRAY,TIMESTAMPTZ,FLOAT4_ARRAY,FLOAT8_ARRAY,UUID,TEXT_ARRAY,VARCHAR_ARRAY,BYTEA,TIME,DATE,FLOAT4,FLOAT8,INT2_ARRAY,TIMETZ,INT2,INT8,INT4,INT4_ARRAY,TIMESTAMP,POINT,BOX
14:20:59.023 (1)     integer date/time = true
getConnection returning org.postgresql.Driver
DriverManager.getDriver("jdbc:postgresql://localhost:5433/pi1")
getDriver returning org.postgresql.Driver
14:20:59.035 (driver) Connecting with URL: jdbc:postgresql://localhost:5433/pi1
14:20:59.035 (2) PostgreSQL 9.3 JDBC4 (build 1102)
14:20:59.035 (2) Trying to establish a protocol version 3 connection to localhost:5433
14:20:59.036 (2) Receive Buffer Size is 131003
14:20:59.036 (2) Send Buffer Size is 331875
14:20:59.036 (2)  FE=> StartupPacket(user=tad, database=pi1, client_encoding=UTF8, DateStyle=ISO, extra_float_digits=2, TimeZone=Europe/Madrid)
14:20:59.038 (2)  <=BE AuthenticationOk
14:20:59.039 (2)  <=BE ParameterStatus(application_name = )
14:20:59.039 (2)  <=BE ParameterStatus(client_encoding = UTF8)
14:20:59.039 (2)  <=BE ParameterStatus(DateStyle = ISO, DMY)
14:20:59.039 (2)  <=BE ParameterStatus(integer_datetimes = on)
14:20:59.039 (2)  <=BE ParameterStatus(IntervalStyle = postgres)
14:20:59.039 (2)  <=BE ParameterStatus(is_superuser = on)
14:20:59.039 (2)  <=BE ParameterStatus(server_encoding = UTF8)
14:20:59.039 (2)  <=BE ParameterStatus(server_version = 9.3.5)
14:20:59.039 (2)  <=BE ParameterStatus(session_authorization = tad)
14:20:59.039 (2)  <=BE ParameterStatus(standard_conforming_strings = on)
14:20:59.039 (2)  <=BE ParameterStatus(TimeZone = Europe/Madrid)
14:20:59.039 (2)  <=BE BackendKeyData(pid=28271,ckey=447213844)
14:20:59.040 (2)  <=BE ReadyForQuery(I)
14:20:59.040 (2) simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@1118efc0, maxRows=0, fetchSize=0, flags=23
14:20:59.040 (2)  FE=> Parse(stmt=null,query="SET extra_float_digits = 3",oids={})
14:20:59.040 (2)  FE=> Bind(stmt=null,portal=null)
14:20:59.040 (2)  FE=> Execute(portal=null,limit=1)
14:20:59.040 (2)  FE=> Sync
14:20:59.041 (2)  <=BE ParseComplete [null]
14:20:59.041 (2)  <=BE BindComplete [null]
14:20:59.041 (2)  <=BE CommandStatus(SET)
14:20:59.041 (2)  <=BE ReadyForQuery(I)
14:20:59.041 (2)     compatible = 9.3
14:20:59.041 (2)     loglevel = 2
14:20:59.041 (2)     prepare threshold = 5
14:20:59.043 (2)     types using binary send = INT8_ARRAY,TIMESTAMPTZ,FLOAT4_ARRAY,FLOAT8_ARRAY,UUID,TEXT_ARRAY,VARCHAR_ARRAY,BYTEA,TIME,FLOAT4,FLOAT8,INT2_ARRAY,TIMETZ,INT2,INT8,INT4,INT4_ARRAY,TIMESTAMP,POINT,BOX
14:20:59.044 (2)     types using binary receive = INT8_ARRAY,TIMESTAMPTZ,FLOAT4_ARRAY,FLOAT8_ARRAY,UUID,TEXT_ARRAY,VARCHAR_ARRAY,BYTEA,TIME,DATE,FLOAT4,FLOAT8,INT2_ARRAY,TIMETZ,INT2,INT8,INT4,INT4_ARRAY,TIMESTAMP,POINT,BOX
14:20:59.044 (2)     integer date/time = true
9.3
14:20:59.056 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@216f6006, maxRows=0, fetchSize=0, flags=16
14:20:59.057 (2)  FE=> Parse(stmt=S_1,query="SELECT proargnames FROM pg_proc where proname ='pg_cursor'",oids={})
14:20:59.057 (2)  FE=> Bind(stmt=S_1,portal=null)
14:20:59.057 (2)  FE=> Describe(portal=null)
14:20:59.057 (2)  FE=> Execute(portal=null,limit=0)
14:20:59.057 (2)  FE=> Sync
14:20:59.059 (2)  <=BE ParseComplete [S_1]
14:20:59.059 (2)  <=BE BindComplete [null]
14:20:59.060 (2)  <=BE RowDescription(1)
14:20:59.060 (2)         Field(,TEXT_ARRAY,65535,T)
14:20:59.060 (2)  <=BE DataRow(len=66)
14:20:59.060 (2)  <=BE CommandStatus(SELECT 1)
14:20:59.072 (2)  <=BE ReadyForQuery(I)
{name,statement,is_holdable,is_binary,is_scrollable,creation_time}
14:20:59.077 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@3a5f2465, maxRows=0, fetchSize=0, flags=16
14:20:59.077 (2)  FE=> Bind(stmt=S_1,portal=null)
14:20:59.077 (2)  FE=> Execute(portal=null,limit=0)
14:20:59.077 (2)  FE=> Sync
14:20:59.078 (2)  <=BE BindComplete [null]
14:20:59.078 (2)  <=BE DataRow(len=103)
14:20:59.078 (2)  <=BE CommandStatus(SELECT 1)
14:20:59.078 (2)  <=BE ReadyForQuery(I)
null

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Dave Cramer
Дата:
Сообщение: Re: 9.4 driver
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: Text array in prepared statements returns null when using binary tranfer