| From: | nasim(dot)sindri(at)gmail(dot)com | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | commit failed when calling postgresql procedure | 
| Date: | 2007-06-20 09:54:56 | 
| Message-ID: | 1182333296.820506.229310@a26g2000pre.googlegroups.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
PLATEFORM: VC++ 7 , ADO, WINDOWS 2003, POSTGRE 8.2
I m having a problem while calling the procedure in prostgresql 8.2
from adoconnection, It gets executed for some time and after 5-10 call
it gives error either startTransaction failed or commit failed.
CREATE OR REPLACE FUNCTION sp_getnewfiles(IN strserverid character
varying, IN nmaxcount integer, OUT stroutrecno character varying) AS
$BODY$
DECLARE
	cur RECORD;
	i integer;
BEGIN
	i:=0;
	LOCK TABLE inputtable IN ROW EXCLUSIVE MODE NOWAIT;
	FOR cur IN select recno from InputTable where FileState=0  order by
recno limit nMaxCount for update
	LOOP
	if i=0 then
		strOutRecNo:='recno=';
	else
		strOutRecNo:=strOutRecNo || ' or recno=';
	end if;
	strOutRecNo:=strOutRecNo||cur.recno;
	update inputtable set filestate=1,serverid=strServerID where
recno=cur.recno;
	i:=i+1;
	END LOOP;
	EXCEPTION
	WHEN no_data_found THEN
		--DO NOTHING
	WHEN OTHERS THEN
	--rollback;
	RAISE EXCEPTION 'some error';
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE STRICT;
the calling code is
			CADORecordset rset(pCnn);
			rset.Open("select * from sp_getnewfiles('server',10)");
			strRecNo=rset.GetFieldValue(0);
the error I encountered after some number of calls is either -
Error message: Unspecified error
Engine Used: PgOleDb
Error type : StartTransaction failed
or
Error message: Unspecified error
Engine Used: PgOleDb
Error type : commit failed
Thanks in advance:
Nasim
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Marco Colombo | 2007-06-20 10:21:45 | Re: unexpected shutdown | 
| Previous Message | scuffio | 2007-06-20 09:44:20 | CHECK |