Re: "could not determine data type of parameter" with timestamp - Mailing list pgsql-jdbc

Dave,

If I am not wrong, the "working code" use an "insert" statement. Not a "select" statement with a where clause like "? IS NULL OR ? = C_TIMESTAMP".

Rémi

Le jeu. 29 mars 2018 à 15:13, Dave Cramer <pg@fastcrypt.com> a écrit :

Remi,

See this for working code

https://github.com/pgjdbc/pgjdbc/blob/b1581e99b6da96b6e44753ce231ec3acf9869fea/pgjdbc/src/test/java/org/postgresql/test/jdbc2/TimestampTest.java#L259

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 29 March 2018 at 09:07, Rémi Aubel <remi.aubel@gmail.com> wrote:

Sorry, Dave, I switched back to the version 42.2.1 of the driver, which has not your fix.

That's true, with your fix, setNull with the time name works well.

But, I've another related problem.

With the same query, I'm not able to bind a non-null timestamp value.

Timestamp timestamp = new Timestamp(new Date().getTime());
statement.setObject(1, timestamp, Types.TIMESTAMP);
statement.setObject(2, timestamp, Types.TIMESTAMP);
statement.execute();
=> ERROR: could not determine data type of parameter $1

Unfortunately, AFAIK, setObject has no signature with the type name.

What do you suggest?

Rémi 

PS: Of course, in my code, I use java.sql.Type 

Le jeu. 29 mars 2018 à 14:49, Dave Cramer <pg@fastcrypt.com> a écrit :

Remi,

Why are you using absolute values for java.sql.Types instead of java.sql.TIMESTAMP.

Timestamp is a bit strange as we tend to let the backend intuit which type it is due to the fact that before java 9 there was no TIMESTAMP_WITH_TIMEZONE type in java

setNull(pos, DATE) works because we don't have that issue.

pstmt = con.prepareStatement("SELECT * FROM TEST_TABLE WHERE ? IS NULL OR ? = C_TIMESTAMP");
pstmt.setNull(1,Types.TIMESTAMP, "timestamp");
pstmt.setNull(2,Types.OTHER, "timestamp");

Worked fine for me...

Dave Cramer

On 29 March 2018 at 08:31, Rémi Aubel <remi.aubel@gmail.com> wrote:

Hi,

Setting null for a bound value for a UUID column works fine with Dave's fix (https://github.com/pgjdbc/pgjdbc/pull/1160).

Now, I'm not able to set a null value for a timestamp.

My table:

CREATE TABLE TEST_TABLE (C_TIMESTAMP TIMESTAMP)

My query:

SELECT * FROM TEST_TABLE WHERE ? IS NULL OR ? = C_TIMESTAMP

Data type and type name for my column C_TIMESTAMP are respectively 93 and "timestamp" (checked with the metadata provided by the driver).

Using PreparedStatement.setNull(pos, 93) or PreparedStatement.setNull(pos, 93, "timestamp") for pos = 1, 2 does not work (could not determine data type of parameter).

Using PreparedStatement.setNull(pos, 91) does work!

Is it expected?

Rémi

 

Le mer. 6 déc. 2017 à 01:58, Dave Cramer <pg@fastcrypt.com> a écrit :

So ps.setNull(1, Types.TIMESTAMP);

Doesn't work ?

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 5 December 2017 at 15:42, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <remi.aubel@gmail.com> wrote:

Hello,

I know my problem has already been answered in this list (https://www.postgresql.org/message-id/CA%2BxsaB0EMDaNek0Oky9c17_8UX3-epWVP11%2BvTNgrAPoY2s9FA%40mail.gmail.com), but it was a long time ago and I would like to know if any other solution exists now.

​Not that I am aware.​

 

But this workaround is not really useful for me, because my application uses generated (dynamic) queries and targets multiple database types (not only PosgreSQL). So, when I know the targeted database, I do not know my parameter types anymore.

​I'd probably perform the null test in Java and pass the true/false boolean result along to the query:

SELECT * FROM my_table WHERE ?::bool;

ps.setBoolean(1, tsvar = null); //my Java is rusty but you get the idea.

That should work in any database.

David J.

​

--

--

--