PostgreSQL JDBC - Prepared statements and default values

Author

Prepared statements and default values

Assad Jarrahian

2006-01-10, 3:24 am

I have a preparedStatement with the following query
"UPDATE usersettings SET gpsfrequency = ? , serverurl = ? where
username = ? ;";

In the db, the table userSettings has a default value specified for
gps_frequency .

So when I write code
if (client.getSettings().getField(Settings.GPS_FREQUENCY).compareTo(""))==0)
<what goes here> //pick up the default value from the db.
else
setUserSettings.setInt(++i,
Integer.parseInt(client.getSettings().getField(Settings.GPS_FREQUENCY)));

<what goes here> .. I basically want to send something that lets the
db know to use the DEFAULT value. (its of type int0)

So what does go here?

any help would be much appreciated.

-a

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

David Wall

2006-01-10, 3:24 am

You'll probably need to use two statements, so that when you want to use
the default, you don't specify anything, and when you want a special
value, you use the command you gave.

If you want the default, use:

UPDATE usersettings SET serverurl = ? where username = ?

If you want to specify, then use:

UPDATE usersettings SET gpsfrequency = ? , serverurl = ? where username = ?

David

Assad Jarrahian wrote:

>I have a preparedStatement with the following query
> "UPDATE usersettings SET gpsfrequency = ? , serverurl = ? where
>username = ? ;";
>
>In the db, the table userSettings has a default value specified for
>gps_frequency .
>
>So when I write code
>if (client.getSettings().getField(Settings.GPS_FREQUENCY).compareTo(""))==0)
> <what goes here> //pick up the default value from the db.
>else
> setUserSettings.setInt(++i,
>Integer.parseInt(client.getSettings().getField(Settings.GPS_FREQUENCY)));
>
>
><what goes here> .. I basically want to send something that lets the
>db know to use the DEFAULT value. (its of type int0)
>
>So what does go here?
>
>any help would be much appreciated.
>
>-a
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Assad Jarrahian

2006-01-10, 3:24 am

thanks for your response David!

Is that the only way, cause that surely does not seem easy when you
have say 20 columns, of which 15 have default values.

given all the combinations (sometimes this set of column values is
needed to be default, otherwise another set .... and so on.

Am I stuck with writing out all possible statements ...or is there another way?

thanks.
-assad

On 1/9/06, David Wall <d.wall@computer.org> wrote:
> You'll probably need to use two statements, so that when you want to use
> the default, you don't specify anything, and when you want a special
> value, you use the command you gave.
>
> If you want the default, use:
>
> UPDATE usersettings SET serverurl = ? where username = ?
>
> If you want to specify, then use:
>
> UPDATE usersettings SET gpsfrequency = ? , serverurl = ? where username = ?
>
> David
>
>
> Assad Jarrahian wrote:
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dave Cramer

2006-01-10, 3:24 am

Actually, I don't think not specifying the column will work. This
works for insert, but not update.

I don't think there is a way using JDBC, as you can't even use the
keyword DEFAULT. The driver won't let you bind a string to a non-
string parameter.

Dave
On 9-Jan-06, at 11:02 PM, Assad Jarrahian wrote:

> thanks for your response David!
>
> Is that the only way, cause that surely does not seem easy when you
> have say 20 columns, of which 15 have default values.
>
> given all the combinations (sometimes this set of column values is
> needed to be default, otherwise another set .... and so on.
>
> Am I stuck with writing out all possible statements ...or is there
> another way?
>
> thanks.
> -assad
>
>
> On 1/9/06, David Wall <d.wall@computer.org> wrote:
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Marc Herbert

2006-01-10, 7:23 am

Assad Jarrahian <jarraa@gmail.com> writes:

> thanks for your response David!
>
> Is that the only way, cause that surely does not seem easy when you
> have say 20 columns, of which 15 have default values.
>
> given all the combinations (sometimes this set of column values is
> needed to be default, otherwise another set .... and so on.
>
> Am I stuck with writing out all possible statements ...or is there
> another way?

Move the location of your default values out of the DB into the JDBC
application. Of course your application has to be the only DB user :-(

Else "import" the defaults from the DB into your application. Insert
into the DB a fake user with defaults for every field and get it back
immediately after; like this you easily "import"/duplicate all the
default values. Clean-up the fake user then systematically
..set(defaults) on all your prepared statements, and re- .set()
them/override them only if the user provided some input.

The issue of course is the duplication/desync of the default values
between the DB and application. How often should they be updated?

My 2 cents.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links