Thread: PreparedStatement and TYPE bit

Hallo,

how can I use PreparedStatement to insert or update columns of type
bit(n), varbit, ...?

I have never figoured out the right way. In former versions of the
JDBC-Driver I used setString() as shown below or constructed the whole
query string and used Statement.

String sql =
"...
WHERE f1.verkehrstage & B? <> 0::bit(384)
...";

PreparedStatement pstmt_select1 = con.prepareStatement(sql);

BigInteger mybits = ...

// padding bits 0 on the left up to 384 (type bit(384))
String querybits = mybits.or(this.bit384).toString(2).substring(1);

pstmt_select1.setString(1, querybits);
...
ResultSet rs = pstmt_select1.executeQuery();

This does not work in versions 8.x.

Thank you

Ingmar Lötzsch schrieb:
> Hallo,
>
> how can I use PreparedStatement to insert or update columns of type
> bit(n), varbit, ...?
>
> I have never figoured out the right way. In former versions of the
> JDBC-Driver I used setString() as shown below or constructed the whole
> query string and used Statement.
>
> String sql =
> "...
> WHERE f1.verkehrstage & B? <> 0::bit(384)
> ...";
>
> PreparedStatement pstmt_select1 = con.prepareStatement(sql);
>
> BigInteger mybits = ...
>
> // padding bits 0 on the left up to 384 (type bit(384))
> String querybits = mybits.or(this.bit384).toString(2).substring(1);

mybits has max. 384 bits. this.bit384 is a BigInteger with the value of
2^384. Thats why mybits.or(this.bit384) results in a BigInteger with
exact 385 bits with 1 on the most significant position and
mybits.or(this.bit384).toString(2).substring(1) is a "bitstring" with
exact 384 bits.

> pstmt_select1.setString(1, querybits);
> ...
> ResultSet rs = pstmt_select1.executeQuery();
>
> This does not work in versions 8.x.
>
> Thank you

On Tue, 28 Nov 2006, Ingmar Lötzsch wrote:

> Ingmar Lötzsch schrieb:
>> Hallo,
>>
>> how can I use PreparedStatement to insert or update columns of type
>> bit(n), varbit, ...?
>>
>> I have never figoured out the right way. In former versions of the
>> JDBC-Driver I used setString() as shown below or constructed the whole
>> query string and used Statement.

With the 8.0 and 8.1 driver you would need to write code that is specific
to postgresql using the PGobject class to pass the correct type
information to setObject.

You can does this again with the 8.2 driver if you add the URL parameter
stringtype=unspecified.

I think the best long term approach is for getObject and setObject to
return/take BitSet objects directly so no conversion to string is needed.

Kris Jurka