Обсуждение: stringtype=unspecified problem

I'm setting stringtype=unspecified in my connection URL to allow
prepared statements to manipulate non-jdbc types as strings, since
jdbc seems to choke on them otherwise.

I'm running into a problem when I want to explicitly set one of those
unspecified types to NULL. It appears that jdbc is preparing that
statement using an empty string, which is causing PostgreSQL to
error-out on the execution of that statement.

The postgresql type in particular is the contrib type "uniqueidentifier" ...

I didn't want to file a bug report until I brought it up on the list
in case I'm overlooking something.

CG

Chris Gamache wrote:

> I'm running into a problem when I want to explicitly set one of those
> unspecified types to NULL.

How exactly are you doing this, in terms of JDBC API calls? A testcase
showing the problem would be useful.

-O

Here's a test case... Since I'm handling these UUIDs as Strings
throughout the java code, they are indistinguishable from an actual
string.

/*
 * CREATE TABLE bomb_test (uuid uniqueidentifier);
 */

try {
       DriverManager.registerDriver(new org.postgresql.Driver());
       Connection conn =

DriverManager.getConnection("jdbc:postgresql://10.10.0.1:5432/data/public?stringtype=unspecified","postgres","password");
       PreparedStatement bombOnNull = conn.prepareStatement("INSERT INTO
bomb_test (uuid) values (?)");
       //bombOnNull.setString(1, UUID.randomUUID().toString()); //this works
       //bombOnNull.setString(1, null); //this works
       //bombOnNull.setNull(1, java.sql.Types.NULL); //this works
       //bombOnNull.setNull(1, java.sql.Types.OTHER); //this works
       bombOnNull.setNull(1, java.sql.Types.VARCHAR); //this bombs
       bombOnNull.executeUpdate();
       bombOnNull.close();
       conn.close();
} catch (Exception e) {
       e.printStackTrace();
}

Thank you for taking a look!

On 2/11/07, Oliver Jowett <oliver@opencloud.com> wrote:
> Chris Gamache wrote:
>
> > I'm running into a problem when I want to explicitly set one of those
> > unspecified types to NULL.
>
> How exactly are you doing this, in terms of JDBC API calls? A testcase
> showing the problem would be useful.
>
> -O
>

On Wed, 14 Feb 2007, Chris Gamache wrote:

> Here's a test case... Since I'm handling these UUIDs as Strings
> throughout the java code, they are indistinguishable from an actual
> string.
>
>      //bombOnNull.setNull(1, java.sql.Types.OTHER); //this works
>      bombOnNull.setNull(1, java.sql.Types.VARCHAR); //this bombs

Here you're explicitly saying that it is a VARCHAR, not that you have a
String that you're not sure what the real type is.  It would be possible
to adjust setNull to check the stringtype setting and use Oid.UNSPECIFIED
here, but I'm not sure that would be correct.

Kris Jurka

On 2/14/07, Kris Jurka <books@ejurka.com> wrote:
> Here you're explicitly saying that it is a VARCHAR, not that you have a
> String that you're not sure what the real type is.  It would be possible
> to adjust setNull to check the stringtype setting and use Oid.UNSPECIFIED
> here, but I'm not sure that would be correct.

We've been batting this around in the office as well. I wish I knew
what the absolute correct behavior would be in this situation.

An observation that might be insightful is if I can figure out how

setString(1,null);

differs from

setNull(1,java.sql.Types.VARCHAR);

...

When connected using stringtype=unspecified it would seem to me that
the two should behave the same way. What do you think?

On Thu, 15 Feb 2007, Chris Gamache wrote:

> On 2/14/07, Kris Jurka <books@ejurka.com> wrote:
>> Here you're explicitly saying that it is a VARCHAR, not that you have a
>> String that you're not sure what the real type is.  It would be possible
>> to adjust setNull to check the stringtype setting and use Oid.UNSPECIFIED
>> here, but I'm not sure that would be correct.
>
> We've been batting this around in the office as well. I wish I knew
> what the absolute correct behavior would be in this situation.
>
> An observation that might be insightful is if I can figure out how
>
> setString(1,null);
>
> differs from
>
> setNull(1,java.sql.Types.VARCHAR);
>

What would you expect to happen for these two:

setObject(1, "hi", Types.VARCHAR);
setObject(1, null, Types.VARCHAR);

compared with:

setObject(1, "hi");
setObject(1, null);

By specifying the type you're saying that it really is a VARCHAR, not
indicating that it's an unknown type in a String representation.

Kris Jurka