PreparedStatement and DB default values

From: Tomisław Kityński <cromax(at)amiga(dot)pl>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: PreparedStatement and DB default values
Date: 2002-07-04 22:10:27
Message-ID: 000e01c223a7$b6de2e80$625d4cd5@xpecet
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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(at)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!

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message David Garnier 2002-07-04 22:41:50 Retrieving binary data
Previous Message Th Templ 2002-07-04 07:17:21 Re: Problem with sql function LOWER