Thread: Prepared statements and default values

Prepared statements and default values

From

Assad Jarrahian

Date:

09 January 2006, 23:24:35

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

Re: Prepared statements and default values

From

David Wall

Date:

09 January 2006, 23:30:27

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

Re: Prepared statements and default values

From

Assad Jarrahian

Date:

10 January 2006, 00:02:23

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

Re: Prepared statements and default values

From

Dave Cramer

Date:

10 January 2006, 00:21:20

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:
>> 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
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Re: Prepared statements and default values

From

Marc Herbert

Date:

10 January 2006, 06:00:54

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.