Re: Text array in prepared statements returns null when using binary tranfer

As I said toString is more of a convenience method. Real access to the
array is through SqlArray.getArray()

You are correct though we probably should fix toString on the binary array

Dave Cramer

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

On 3 February 2015 at 08:59, Asier Lostalé <asier(dot)lostale(at)openbravo(dot)com>
wrote:

> 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(at)fastcrypt(dot)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(dot)lostale(at)openbravo(dot)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(dot)postgresql(dot)core(dot)SetupQueryRunner$SimpleResultHandler(at)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(dot)postgresql(dot)core(dot)SetupQueryRunner$SimpleResultHandler(at)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(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Statement$StatementResultHandler(at)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(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Statement$StatementResultHandler(at)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
>>>
>>>
>>
>