Postgres Pro
Downloads
Home   >   mailing lists

Thread: issuing insert preparedstatement queries with default values

issuing insert preparedstatement queries with default values

From
Johnny Luong
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Is there a way to use the DEFAULT per column value on an INSERT sql in
PG through the PreparedStatement JDBC API?  It'd be nice so that I don't
have to maintain a secondary list of PreparedStatements or a list of
default values maintained outside of the database definition.

e.g:

INSERT INTO foo (a, b) (?, ?)

where b has a default value in the database and a is something that I
might set to some other value.


I'm using postgresql-8.3-603.jdbc4.jar against a 8.3 postgresql server.

Thanks,
Johnny
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAktHx+kACgkQnQTBLXttTeVEmgCePgPqSGOtZvii/nGWEVaGeF+l
VX4An0LVEB9Rhlsc0s8j91oTwLOoKS4L
=s1Fo
-----END PGP SIGNATURE-----

Re: issuing insert preparedstatement queries with default values

From
dmp
Date:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> Is there a way to use the DEFAULT per column value on an INSERT sql in
> PG through the PreparedStatement JDBC API?  It'd be nice so that I don't
> have to maintain a secondary list of PreparedStatements or a list of
> default values maintained outside of the database definition.
>
> e.g:
>
> INSERT INTO foo (a, b) (?, ?)
>
> where b has a default value in the database and a is something that I
> might set to some other value.
>
>
> I'm using postgresql-8.3-603.jdbc4.jar against a 8.3 postgresql server.
>
> Thanks,
> Johnny


I know this may be not the appropriate approach, but when a user enters
default I just skip the PrepareStatement value assignment for that field
and set in the initial assignment as such. See example below.

danap.


--
-- MyJSQLView SQL Dump
-- Version: 3.06
-- WebSite: http://myjsqlview.org
--
-- Host: 127.0.0.1
-- Generated On: 2010.01.08 AD at 07:07:55 MST
-- SQL version: PostgreSQL 8.3.3
-- Database: key_tables
--

-- ------------------------------------------

--
-- Table structure for table "public"."key_table4"
--

CREATE TABLE "public"."key_table4" (
    "avatar_id" integer DEFAULT 0 NOT NULL,
    "user_id" integer DEFAULT 0 NOT NULL,
    "bing_id" smallint DEFAULT 0 NOT NULL,
    PRIMARY KEY ("avatar_id","user_id")
);

TableEntryForm addUpdateTableEntry() Connection Created
INSERT INTO "public"."key_table4" ("avatar_id", "user_id", "bing_id")
VALUES (?, ?, default)
TableEntryForm addUpdateTableEntry() Connection Closed

Re: issuing insert preparedstatement queries with default values

From
Johnny Luong
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

dmp wrote:
|> -----BEGIN PGP SIGNED MESSAGE-----
|> Hash: SHA1
|>
|> Hi,
|>
|> Is there a way to use the DEFAULT per column value on an INSERT sql in
|> PG through the PreparedStatement JDBC API?  It'd be nice so that I don't
|> have to maintain a secondary list of PreparedStatements or a list of
|> default values maintained outside of the database definition.
|>
|> e.g:
|>
|> INSERT INTO foo (a, b) (?, ?)
|>
|> where b has a default value in the database and a is something that I
|> might set to some other value.
|>
|>
|> I'm using postgresql-8.3-603.jdbc4.jar against a 8.3 postgresql server.
|>
|> Thanks,
|> Johnny
|
|
| I know this may be not the appropriate approach, but when a user enters
| default I just skip the PrepareStatement value assignment for that field
| and set in the initial assignment as such. See example below.
|
| danap.
|
|
| --
| -- MyJSQLView SQL Dump
| -- Version: 3.06
| -- WebSite: http://myjsqlview.org
| --
| -- Host: 127.0.0.1
| -- Generated On: 2010.01.08 AD at 07:07:55 MST
| -- SQL version: PostgreSQL 8.3.3
| -- Database: key_tables
| --
|
| -- ------------------------------------------
|
| --
| -- Table structure for table "public"."key_table4"
| --
|
| CREATE TABLE "public"."key_table4" (
|    "avatar_id" integer DEFAULT 0 NOT NULL,
|    "user_id" integer DEFAULT 0 NOT NULL,
|    "bing_id" smallint DEFAULT 0 NOT NULL,
|    PRIMARY KEY ("avatar_id","user_id")
| );
|
| TableEntryForm addUpdateTableEntry() Connection Created
| INSERT INTO "public"."key_table4" ("avatar_id", "user_id", "bing_id")
| VALUES (?, ?, default)
| TableEntryForm addUpdateTableEntry() Connection Closed
|

The only issue with this approach I think is that I would probably have
to use a normal java.sql.Statement to perform the load and the actual
table I'm dealing with is a legacy table with 60+ columns, some of which
have defaults already in place.  I already explicitly setup the query
and set each individual parameter so I can avoid the reparsing efforts
but I'm having to actually take the defaults from the database and
putting them into the code...

Thanks for the help though.
Johnny Luong
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAktH71MACgkQnQTBLXttTeUY4QCfbaBr2jnsG3Fg6DUhWZmsu0wQ
ZmsAn3WwFdKFbVXOERURCekivEUCVGBf
=e606
-----END PGP SIGNATURE-----

Re: issuing insert preparedstatement queries with default values

From
Lew
Date:
Johnny Luong wrote:
> |> Is there a way to use the DEFAULT per column value on an INSERT sql in
> |> PG through the PreparedStatement JDBC API?  It'd be nice so that I don't
> |> have to maintain a secondary list of PreparedStatements or a list of
> |> default values maintained outside of the database definition.
...
> | TableEntryForm addUpdateTableEntry() Connection Created
> | INSERT INTO "public"."key_table4" ("avatar_id", "user_id", "bing_id")
> | VALUES (?, ?, default)
> | TableEntryForm addUpdateTableEntry() Connection Closed

I just leave out the columns in the INSERT for which I want default values.

--
Lew