Message-Id: <5.2.1.1.0.20030617124517.0222a8d8@192.168.0.1>
Date: Tue, 17 Jun 2003 12:50:24 +0300
To: pgsql-sql@postgresql.org
From: Radu-Adrian Popescu <radu.popescu@aldratech.com>
Subject: Re: Catching DML exceptions in PL/pgSQL
In-Reply-To: <3EEEE304.1010106@klaster.net>
References: <5.2.1.1.0.20030617121526.02185a50@192.168.0.1>
	<5.2.1.1.0.20030617121526.02185a50@192.168.0.1>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

At  6/17/2003 11:44, Tomasz Myrta wrote:

>Dnia 2003-06-17 11:25, U=BFytkownik Radu-Adrian Popescu napisa=B3:
>
>>Hello all,
>>(and sorry if this has been aswered before)
>>Take this piece of code for example:
>>.....................
>>begin
>>     _res.code:=3D1;
>>     select id into iid from log where id=3D_id;
>>     if not found then begin
>>         _res.msg:=3D''insert'';
>>         *insert into log (log, data) values (_log, _data);
>>         if not found* then begin
>>             _res.msg:=3D_res.msg || '' error'';
>>             _res.code:=3D-1;
>>         end;
>>         end if;
>>     end;
>>     else begin
>>.....................
>>The thing is if _data (parameter) is null and table has a (data <> null)=
=20
>>check, the insert would fail and abort the function before my "if not=20
>>found" test.
>>I'm porting a java app. from mssql to postgresql, and the java code=20
>>relies on the stored procedure to always return it's status (in _res.code=
=20
>>in this case).
>>Is there anything I can do to make sure the function always returns _res ?
>>Something along the lines of Oracle's exception handling, or the @@error=
=20
>>trick in mssql ?
>You can't do it this way. Your insert is violation of some constraint=20
>(problably "not null" or "primary key" constraint). This kind of violation=
=20
>raises exception and whole transaction is aborted. I don't use java, but C=
=20
>librares raises also ordinary C exception which can be easily caught. If=
=20
>you want to avoid such cases - check your data before inserting them.

Thanks, unfortunatelly I knew that...
My java/db code currently decides whether the call was successful based on
1) return from stored procedure (currently, to be replaced by select * from=
=20
function_name(...))
2) SQLException
So basically it would work no probs: i.e. instead of reading a -1 when an=
=20
error occured, there would be an SQLException caught and the
final outcome, either way, would be that the java method would still=20
function properly and let the calling code know whether things went okay
or not.
However, the point was to be able to deal with these exceptions inside=20
pl/plgsql, as it would bring imho a load more power and fexibility to the=
=20
db code.
For instance, it could choose to log failures to a database table, or=20
choose another execution path and so on.

Anyway, thanks for the reply :-)


--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.=20