Re: PreparedStatement and DB default values - Mailing list pgsql-jdbc

I doubt this is jdbc related.  Can you turn on query tracing in the
server to see the exact SQL that the jdbc driver is sending to the
server and then run that same sql through psql?

thanks,
--Barry

Tomisław Kityński wrote:

>Hello,
>
>I have strange problem here. I have table declared as below:
>
>                                      Table "users"
> Column  |         Type          |                       Modifiers
>---------+-----------------------+------------------------------------------
>-------------
> id_user | integer               | not null default
>nextval('"users_id_user_seq"'::text)
> first   | character varying(24) | not null default '(imię)'
> last    | character varying(32) | not null default '(nazwisko)'
> email   | character varying(24) |
> vip     | boolean               | not null default 'f'
> ed      | boolean               | not null default 'f'
>Primary key: users_pkey
>Rules: on_delete_to_users,
>       on_insert_to_users
>Triggers: RI_ConstraintTrigger_27009,
>          RI_ConstraintTrigger_27011,
>          RI_ConstraintTrigger_27019,
>          RI_ConstraintTrigger_27021,
>          RI_ConstraintTrigger_27028,
>          RI_ConstraintTrigger_27030,
>          RI_ConstraintTrigger_27063,
>          RI_ConstraintTrigger_27065,
>          RI_ConstraintTrigger_27086,
>          RI_ConstraintTrigger_27088
>
>When I issue a statement from psql console like this:
>
>INSERT INTO users (first, last, email) VALUES ('Frst', 'Lst', 'em@il');
>
>(or AFAIR also from Statement by executeUpdate()), then id_user would be
>properly autogenerated. Unfortunately it does not work for
>PreparedStatement,
>thats what I get:
>
>INSERT INTO "users" ("first", "last", "email", "ed") VALUES (?, ?, ?, ?);
>java.sql.SQLException: ERROR:  ExecAppend: Fail to add null value in not
>null attribute id_user
>
>        at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
>        at org.postgresql.Connection.ExecSQL(Connection.java:398)
>        at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
>        at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
>        at
>org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:
>113)
>
>The first line is not the stack trace fragment, but update string construed
>by application, that is passed (the string) to the prepareStatement()
>method.
>
>Why it wants to add the null value to the column, which is not named in
>the statement and which have defined default value in database?
>
>I could use usual Statement, if there were no problems with escaping
>and/or quoting inserted/updated values. First--this is pain in a** to
>compose query and second, for different databases (I inted to write
>the code as far flexible and DB independent as I can) the escaping
>could be different (especially considering PSQL regexps). Is there
>any way to pass it by? Thank you for your help!
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
>