Обсуждение: PreparedStatement and DB default values

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!

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
>
>
>
>
>

----- Original Message -----
From: "Barry Lind" <barry@xythos.com>
To: "Tomisław Kityński" <cromax@amiga.pl>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Friday, July 05, 2002 9:08 PM
Subject: Re: [JDBC] PreparedStatement and DB default values


> 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?
>
  This is part of the log from postmaster fired off with -d 4:

DEBUG:  query: INSERT INTO "users" ("first", "last", "email", "ed") VALUES
('Jan', 'Nowak', 'jan@poczta.pl', 'f');

  And now the best part--this query pasted into psql... didn't work too!
As I'm almost sure, that similar insert string worked for psql, I looked
into
.psql_history to find that exact insert that worked. I found it, I pasted
it,
and... it didn't work too! Uhm, I am very confused now. Well, I issued
also such statement:

  INSERT INTO users DEFAULT VALUES;

and it refuses to insert null value in not null attribute id_user,
which of course have defined not-null default value. And this _really_
is something strange, 'cos I am almost 100% sure that, this one worked
some time ago (I have it in .psql_history). Well, I checked the table for
tuples with "first" and "last" set to default values and there is only
one tuple which consists of exactly the same values as default values.
And I am sure this tuple exists because above statement worked--but now
it does not (what this depend on? debug level??? uhm, I guess not--
I just started postmaster in silent mode and the same happens).

Well, this looks very misterious to me, but you're right in one place:
this isn't probably JDBC related. But how to explain the existence of
'default' tuple in table? I really have strong impression, that those
statements were working when issued from psql few days ago, and now
they're not. 8((

The worst thing is, that on sunday I have to pass db-project (studies),
and now I made some steps back 8(

Do you have any triggers or rules on this table that might be causing
the problem?

--Barry

Tomisław Kityński wrote:

>----- Original Message -----
>From: "Barry Lind" <barry@xythos.com>
>To: "Tomisław Kityński" <cromax@amiga.pl>
>Cc: <pgsql-jdbc@postgresql.org>
>Sent: Friday, July 05, 2002 9:08 PM
>Subject: Re: [JDBC] PreparedStatement and DB default values
>
>
>
>
>>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?
>>
>>
>>
>  This is part of the log from postmaster fired off with -d 4:
>
>DEBUG:  query: INSERT INTO "users" ("first", "last", "email", "ed") VALUES
>('Jan', 'Nowak', 'jan@poczta.pl', 'f');
>
>  And now the best part--this query pasted into psql... didn't work too!
>As I'm almost sure, that similar insert string worked for psql, I looked
>into
>.psql_history to find that exact insert that worked. I found it, I pasted
>it,
>and... it didn't work too! Uhm, I am very confused now. Well, I issued
>also such statement:
>
>  INSERT INTO users DEFAULT VALUES;
>
>and it refuses to insert null value in not null attribute id_user,
>which of course have defined not-null default value. And this _really_
>is something strange, 'cos I am almost 100% sure that, this one worked
>some time ago (I have it in .psql_history). Well, I checked the table for
>tuples with "first" and "last" set to default values and there is only
>one tuple which consists of exactly the same values as default values.
>And I am sure this tuple exists because above statement worked--but now
>it does not (what this depend on? debug level??? uhm, I guess not--
>I just started postmaster in silent mode and the same happens).
>
>Well, this looks very misterious to me, but you're right in one place:
>this isn't probably JDBC related. But how to explain the existence of
>'default' tuple in table? I really have strong impression, that those
>statements were working when issued from psql few days ago, and now
>they're not. 8((
>
>The worst thing is, that on sunday I have to pass db-project (studies),
>and now I made some steps back 8(
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>
>
>
>

----- Original Message -----
From: "Barry Lind" <barry@xythos.com>
To: "Tomisław Kityński" <cromax@amiga.pl>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Monday, July 08, 2002 6:40 PM
Subject: Re: [JDBC] PreparedStatement and DB default values


> Do you have any triggers or rules on this table that might be causing
> the problem?
>
  As stated in previous mail, I have defined two rules for the table
and few constraint triggers. The rules are:

CREATE RULE "on_delete_to_users" AS
ON DELETE TO "users"
DO
(
 UPDATE "news"
 SET "id_author" = -1
 WHERE "id_author" = OLD."id_user";

 UPDATE "comments"
 SET "id_author" = -1
 WHERE "id_author" = OLD."id_user"
);

CREATE RULE "on_insert_to_users" AS
ON INSERT TO "users"
DO
(
 INSERT INTO "given_permissions" VALUES (1, NEW."id_user");
 INSERT INTO "given_permissions" VALUES (2, NEW."id_user");
 INSERT INTO "given_permissions" VALUES (3, NEW."id_user");
);

In meantime I figured out, that dropping the "on_insert_to_users"
rule allowed me to insert default value into "users" table.

However, in PSQL documentation I have found this:

"The action [defined in rule] is done instead of the original query
if INSTEAD is specified; otherwise it is done after the original
query in the case of ON INSERT, or before the original query in the
case of ON UPDATE or ON DELETE".

So, in this particular case, in NEW tuple inside of rule
"on_insert_to_users",
there should be already--as stated in documentation--default value of
id_user taken from sequence. This is obviously not true. And in fact
this is related to PSQL itself and not to JDBC drivers.

Anyway thank you for your time and help. It seems like I have to post
my problem to different mailing group 8)