Re: Text array in prepared statements returns null when using binary tranfer - Mailing list pgsql-jdbc

Hi Dave,

Thanks for your prompt response.

It works fine as you suggest.

In any case, shouldn't I expect 1st time it executes when server side prepared statement is not used to get the same result as subsequent executions?

Regards

Asier

On Tue, Feb 3, 2015 at 2:50 PM, Dave Cramer <pg@fastcrypt.com> wrote:

The "bug" is that we dont' implement toString on the binary type. Which is not actually a bug.

In order to get the array you need to:

SqlArray array=rs.getArray(1)

String[] s = array.getArray()

see http://docs.oracle.com/javase/tutorial/jdbc/basics/array.html

Cheers,

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 3 February 2015 at 08:21, Asier Lostalé <asier.lostale@openbravo.com> wrote:

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