Page 1 of 1
Unistoreproc Get boolean value
Posted: Sat 22 Jun 2013 14:38
by Suhaimin
Hello,
I have a Function in PostgreSQL 9.1 :
CREATE OR REPLACE FUNCTION "FINDINVOICEVENO"( "STRINVOICENO" character varying)
RETURNS boolean AS
$BODY$BEGIN
SELECT "INVOICENO" FROM "PURCHASE"
WHERE ("INVOICENO" = $1);
IF FOUND THEN RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION "FINDINVOICENO"( character varying)
OWNER TO postgres;
How to get boolean value from this funcion with Unistoredproc ? thanks a lot
Re: Unistoreproc Get boolean value
Posted: Tue 25 Jun 2013 00:34
by Suhaimin
Hello,
I used unistoredproc to get boolean value :
unistoredproc.close;
unistoredproc.parambyname('STRINVOICENO').VALUE := '1000';
Unistoredproc.open;
after open i got error message "Query has no destination for result data"... is it impossible to get boolean from that storedproc ? thanks a lot
Re: Unistoreproc Get boolean value
Posted: Wed 26 Jun 2013 05:47
by AlexP
Hello,
To solve the problem, you should modify your function in the following way:
Code: Select all
CREATE OR REPLACE FUNCTION "FINDINVOICEVENO"( "STRINVOICENO" character varying)
RETURNS boolean AS
$BODY$BEGIN
IF EXISTS (SELECT 1 FROM "PURCHASE" WHERE ("INVOICENO" = $1) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION "FINDINVOICENO"( character varying)
OWNER TO postgres;
or
Code: Select all
CREATE OR REPLACE FUNCTION findinvoiceveno(a_dname character varying)
RETURNS boolean AS
$BODY$BEGIN
PERFORM 1 FROM "PURCHASE" WHERE ("INVOICENO" = $1);
IF FOUND THEN RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION findinvoiceveno(character varying)
OWNER TO postgres;
and use UniQuery instead of UniStoredProc, if you want to get the result as DataSet:
Code: Select all
UniQuery1.SQL.Text := 'SELECT FINDINVOICEVENO(:STRINVOICENO)';
UniQuery1.parambyname('STRINVOICENO').VALUE := '1000';
UniQuery1.Open;
, or execute the method
and work with the parameter as
Code: Select all
UniStoredProc1.ParamByName('result').As..
.
Re: Unistoreproc Get boolean value
Posted: Thu 27 Jun 2013 11:10
by Suhaimin
thanks a lot
Re: Unistoreproc Get boolean value
Posted: Mon 01 Jul 2013 11:28
by AlexP
Hello,
If you have any other questions, feel free to contact us