Unistoreproc Get boolean value

[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

[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

[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

Code: Select all

UniStoredProc1.Execute;

and work with the parameter as

Code: Select all

UniStoredProc1.ParamByName('result').As..

.

[Suhaimin]

thanks a lot

[AlexP]

Hello,

If you have any other questions, feel free to contact us