From: Radu-Adrian Popescu <radu.popescu@aldratech.com>
To: pgsql-sql@postgresql.org
Subject: Re: Catching DML exceptions in PL/pgSQL
Date: Tue, 17 Jun 2003 12:50:24 +0300
Message-ID: <5.2.1.1.0.20030617124517.0222a8d8@192.168.0.1> (raw)
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>
At 6/17/2003 11:44, Tomasz Myrta wrote:
>Dnia 2003-06-17 11:25, U¿ytkownik Radu-Adrian Popescu napisa³:
>
>>Hello all,
>>(and sorry if this has been aswered before)
>>Take this piece of code for example:
>>.....................
>>begin
>> _res.code:=1;
>> select id into iid from log where id=_id;
>> if not found then begin
>> _res.msg:=''insert'';
>> *insert into log (log, data) values (_log, _data);
>> if not found* then begin
>> _res.msg:=_res.msg || '' error'';
>> _res.code:=-1;
>> end;
>> end if;
>> end;
>> else begin
>>.....................
>>The thing is if _data (parameter) is null and table has a (data <> null)
>>check, the insert would fail and abort the function before my "if not
>>found" test.
>>I'm porting a java app. from mssql to postgresql, and the java code
>>relies on the stored procedure to always return it's status (in _res.code
>>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
>>trick in mssql ?
>You can't do it this way. Your insert is violation of some constraint
>(problably "not null" or "primary key" constraint). This kind of violation
>raises exception and whole transaction is aborted. I don't use java, but C
>librares raises also ordinary C exception which can be easily caught. If
>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
function_name(...))
2) SQLException
So basically it would work no probs: i.e. instead of reading a -1 when an
error occured, there would be an SQLException caught and the
final outcome, either way, would be that the java method would still
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
pl/plgsql, as it would bring imho a load more power and fexibility to the
db code.
For instance, it could choose to log failures to a database table, or
choose another execution path and so on.
Anyway, thanks for the reply :-)
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: radu.popescu@aldratech.com
Subject: Re: Catching DML exceptions in PL/pgSQL
In-Reply-To: <5.2.1.1.0.20030617124517.0222a8d8@192.168.0.1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox