Re: Help with DELETE statement via ODBC

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(at)lfcia(dot)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(dot)texidor(at)webbods(dot)com(dot)au [web] http://www.webbods.com.au
[phone] 02 6657 3355 [mobile] 0414 810284
[jabber] webbod1(at)jabber(dot)zim(dot)net(dot)au [aim] webbod1
[msn] nick(dot)texidor(at)webbods(dot)com(dot)au [yahoo] webbod1 [icq] 3900008

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