Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help with DELETE statement via ODBC

Help with DELETE statement via ODBC

From
Carlos Abalde
Date:
Hi!

I've been all the day fighting against a DB access poblem, and I'm
sure that the problem is related with the ODBC postgresql driver,
but I don't know how to solve it.

The problem is that when I execute a DELETE stament over a indetifier
not found in a table, the driver returns an error instead of a counter
with 0 rows affected. I'm using the last version of the driver.

Any idea? Best regards,

+---.---.---.---.---.---.---.---.---+---.---.---.---.---.---.---.---+
| Carlos Abalde (carlos@lfcia.org)  | http://www.lfcia.org/~carlos  |
+ LFCIA Lab, Dept. Computer Science | +34.981.167000 ext. 1275      +
| University of A Coruna, Spain     | PGP Key ID = 0x04DF0EAF       |
+-.---.---.---.---.---.---.---.---.-+-.---.---.---.---.---.---.---.-+

Re: Help with DELETE statement via ODBC

From
Håkon Clausen
Date:
Hi Carlos,

This is the correct behavior. Deleting 0 rows will give an ODBC error.
You will have to rewrite your query so that this does not occur.

Håkon


On Mon, 31 May 2004 19:19:16 +0200, Carlos Abalde <carlos@lfcia.org> wrote:

> Hi!
>
> I've been all the day fighting against a DB access poblem, and I'm
> sure that the problem is related with the ODBC postgresql driver,
> but I don't know how to solve it.
>
> The problem is that when I execute a DELETE stament over a indetifier
> not found in a table, the driver returns an error instead of a counter
> with 0 rows affected. I'm using the last version of the driver.
>
> Any idea? Best regards,
>
> +---.---.---.---.---.---.---.---.---+---.---.---.---.---.---.---.---+
> | Carlos Abalde (carlos@lfcia.org)  | http://www.lfcia.org/~carlos  |
> + LFCIA Lab, Dept. Computer Science | +34.981.167000 ext. 1275      +
> | University of A Coruna, Spain     | PGP Key ID = 0x04DF0EAF       |
> +-.---.---.---.---.---.---.---.---.-+-.---.---.---.---.---.---.---.-+
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>



--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Re: Help with DELETE statement via ODBC

From
Carlos Abalde
Date:
Håkon Clausen wrote...

> Hi Carlos,
>
> This is the correct behavior. Deleting 0 rows will give an ODBC error.
> You will have to rewrite your query so that this does not occur.

Hi Håkon,

I'm not an expert on ODBC and maybe this is and philosophical
discussion, buy I think that DELETE querys are very usual in
DB applications, and querys like,

    DELETE FROM table WHERE field = foreign_key

that are very common when you are working with 1-N relations,
need a normal behaviour when 0 rows are deleted.

For example,

    +---------+ 1            N +-----------+
    | Formula |----------------| Parameter |
    +---------+                +-----------+
    for_key               par_key
    for_txt               par_for (foreign key)
    ...               par_nam
                   par_val
                   ...

Represents a formula with several parameters. If we have a
new list of parameters for a existing formula, the best way to update
then is to delete the old parameters and create the new ones.
With the current DELETE behaviour, the normal case of a
formula without parameters must be considered as a special
case!

I think that there is a lot of examples like this in DB aplications.
Moreover, as DBs are highly related with Set Theory, it's an error the
current behaviour, because the empty set is dealed as a special case.

These are the mains reasons I think the current behaviour is erroneus.
However, as I said before, maybe this is a philosophical discussion :).

Best regards,


+---.---.---.---.---.---.---.---.---+---.---.---.---.---.---.---.---+
| Carlos Abalde (carlos@lfcia.org)  | http://www.lfcia.org/~carlos  |
+ LFCIA Lab, Dept. Computer Science | +34.981.167000 ext. 1275      +
| University of A Coruna, Spain     | PGP Key ID = 0x04DF0EAF       |
+-.---.---.---.---.---.---.---.---.-+-.---.---.---.---.---.---.---.-+

Re: Help with DELETE statement via ODBC

From
Nick Texidor
Date:
I agree with Carlos.

I have a web-based Content System that used to run over SQL Server &
Access databases, and in both cases the delete statements did NOT cause
errors when deleting 0 records.  I ended up having to spend a lot of
time amending a lot of code to handle, what I believe, shouldn't happen
in the ODBC driver.

Further back than this, in my Visual Basic/ODBC days (eek!), again, I
don't ever recall having to do a SELECT statement first to see whether
there were any records to delete.

Whether throwing an error is considered "by the book" behaviour, it does
seem to be doubling up the workload.  For example, to delete a section
in my content system, I have to delete all the pages in that section,
which in turn needs to delete all the associated information in
additional tables.  For any one page, there could be additional data in
6 or so other tables.  So rather than 6 hits to the database, I know
have to do 12.

Could you please reconsider changing the behaviour of the delete
statment to not return an error?  Do we really need to double up the
work load to the database unnecesarily?  At the very least, either ask
the list participants for a vote on it, or even add a parameter to the
ODBC settings so that we can choose the behaviour?

Nick



Carlos Abalde wrote:
> Håkon Clausen wrote...
>
>
>>Hi Carlos,
>>
>>This is the correct behavior. Deleting 0 rows will give an ODBC error.
>>You will have to rewrite your query so that this does not occur.
>
>
> Hi Håkon,
>
> I'm not an expert on ODBC and maybe this is and philosophical
> discussion, buy I think that DELETE querys are very usual in
> DB applications, and querys like,
>
>     DELETE FROM table WHERE field = foreign_key
>
> that are very common when you are working with 1-N relations,
> need a normal behaviour when 0 rows are deleted.
>
> For example,
>
>     +---------+ 1            N +-----------+
>     | Formula |----------------| Parameter |
>     +---------+                +-----------+
>     for_key               par_key
>     for_txt               par_for (foreign key)
>     ...               par_nam
>                    par_val
>                    ...
>
> Represents a formula with several parameters. If we have a
> new list of parameters for a existing formula, the best way to update
> then is to delete the old parameters and create the new ones.
> With the current DELETE behaviour, the normal case of a
> formula without parameters must be considered as a special
> case!
>
> I think that there is a lot of examples like this in DB aplications.
> Moreover, as DBs are highly related with Set Theory, it's an error the
> current behaviour, because the empty set is dealed as a special case.
>
> These are the mains reasons I think the current behaviour is erroneus.
> However, as I said before, maybe this is a philosophical discussion :).
>
> Best regards,
>
>
> +---.---.---.---.---.---.---.---.---+---.---.---.---.---.---.---.---+
> | Carlos Abalde (carlos@lfcia.org)  | http://www.lfcia.org/~carlos  |
> + LFCIA Lab, Dept. Computer Science | +34.981.167000 ext. 1275      +
> | University of A Coruna, Spain     | PGP Key ID = 0x04DF0EAF       |
> +-.---.---.---.---.---.---.---.---.-+-.---.---.---.---.---.---.---.-+
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

--
nick texidor
technical director
webbods

[email] nick.texidor@webbods.com.au [web] http://www.webbods.com.au
[phone] 02 6657 3355 [mobile] 0414 810284
[jabber] webbod1@jabber.zim.net.au [aim] webbod1
[msn] nick.texidor@webbods.com.au [yahoo] webbod1 [icq] 3900008

public key: http://www.nick.texidor.name/public_key.asc

Re: Help with DELETE statement via ODBC

From
Carlos Abalde
Date:
Steve Lutz wrote...

> If we are going to turn this into a Philosophical discussion, what
> happens when a select statement updates zero rows? It doesn't cause an
> error, why does a delete?

Hi Steve!

In my previous mail I wrote about DELETE statement because I want
to focus in a specific problem, but I think that the behaviour of
the UPDATE statement is erroneous too. The reasons are exactly the
same as for the DELETE statement. Once again the Sets Theory behind
DBs is the main reason. 0 updated rows must not be an ODBC/DB error.
Maybe 0 updated rows is an application (DB client) error, but that is
a decission of the application. DBs only known about sets of tuples.

Cheers,


+---.---.---.---.---.---.---.---.---+---.---.---.---.---.---.---.---+
| Carlos Abalde (carlos@lfcia.org)  | http://www.lfcia.org/~carlos  |
+ LFCIA Lab, Dept. Computer Science | +34.981.167000 ext. 1275      +
| University of A Coruna, Spain     | PGP Key ID = 0x04DF0EAF       |
+-.---.---.---.---.---.---.---.---.-+-.---.---.---.---.---.---.---.-+

Re: Help with DELETE statement via ODBC

From
"Steve Lutz"
Date:
If we are going to turn this into a Philosophical discussion, what
happens when a select statement updates zero rows? It doesn't cause an
error, why does a delete?



-----Original Message-----
From: Carlos Abalde [mailto:carlos@lfcia.org]
Sent: Monday, June 07, 2004 4:14 PM
To: pgsql-odbc@postgresql.org
Cc: Håkon Clausen
Subject: Re: [ODBC] Help with DELETE statement via ODBC

Håkon Clausen wrote...

> Hi Carlos,
>
> This is the correct behavior. Deleting 0 rows will give an ODBC error.
> You will have to rewrite your query so that this does not occur.

Hi Håkon,

I'm not an expert on ODBC and maybe this is and philosophical
discussion, buy I think that DELETE querys are very usual in
DB applications, and querys like,

    DELETE FROM table WHERE field = foreign_key

that are very common when you are working with 1-N relations,
need a normal behaviour when 0 rows are deleted.

For example,

    +---------+ 1            N +-----------+
    | Formula |----------------| Parameter |
    +---------+                +-----------+
    for_key               par_key
    for_txt               par_for (foreign key)
    ...               par_nam
                   par_val
                   ...

Represents a formula with several parameters. If we have a
new list of parameters for a existing formula, the best way to update
then is to delete the old parameters and create the new ones.
With the current DELETE behaviour, the normal case of a
formula without parameters must be considered as a special
case!

I think that there is a lot of examples like this in DB aplications.
Moreover, as DBs are highly related with Set Theory, it's an error the
current behaviour, because the empty set is dealed as a special case.

These are the mains reasons I think the current behaviour is erroneus.
However, as I said before, maybe this is a philosophical discussion :).

Best regards,


+---.---.---.---.---.---.---.---.---+---.---.---.---.---.---.---.---+
| Carlos Abalde (carlos@lfcia.org)  | http://www.lfcia.org/~carlos  |
+ LFCIA Lab, Dept. Computer Science | +34.981.167000 ext. 1275      +
| University of A Coruna, Spain     | PGP Key ID = 0x04DF0EAF       |
+-.---.---.---.---.---.---.---.---.-+-.---.---.---.---.---.---.---.-+

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

               http://archives.postgresql.org