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

Clear, I'm changing my code now to adapt it to your suggestions.

Thanks again.

Asier

On Tue, Feb 3, 2015 at 3:03 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> 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
>>>>
>>>>
>>>
>>
>