From: Alexey Gaidukov (gaiddc.baikal.ru)
Date: Tue Jun 21 2005 - 06:57:25 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Thank you for answer.

I have one more question. I have two RESOURCE USERS: GIS and REG.

 From GIS user:

CREATE FUNCTION GIS.TEST(......) RETURN BOOLEAN AS

GRANT EXECUTE ON TEST TO REG

 From REG user the following compiles WITHOUT errors

CREATE DBPROC REG.PROC(IN p integer)
RETURNS CURSOR AS
BEGIN
    DECLARE :$CURSOR CURSOR FOR
        select field,GIS.TEST(..)
        from gis.table;
END;

But the at almost the same procedure can't be compiled

CREATE DBPROC REG.PROC(IN p integer)
RETURNS CURSOR AS
BEGIN
    DECLARE :$CURSOR CURSOR FOR
        select field
        from gis.table
        where GIS.TEST(..)=true;
END;

General error;-4030 POS(87) Unknown schema:
CREATE DBPROC REG.PROC(IN f integer)

Sometimes it gets after 'Unknown schema:' three square symbols.

Thanks in advance.

Anhaus, Thomas пишет:

>Alexey Gaidukov wrote :
>
>
>>Gesendet: Dienstag, 21. Juni 2005 12:57
>>An: maxdblists.mysql.com
>>Betreff: Can't create function with varchar argument
>>
>>
>>I'm using 7.6.00.10.
>>
>>
>>CREATE FUNCTION GIS.GET_TARIF (
>> p_resid varchar,
>> p_date date) returns FIXED(20,2) AS
>>VAR
>> f FIXED(20,2);
>>BEGIN
>> select tarif into :f from gis.fullrestype where resid=:p_resid;
>> return f;
>>END;
>>
>>
>>
>>Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
>>General error;-9000 POS(159) System error: Not yet
>>implemented:parameter of unknown size
>>
>>
>>
>>I tried another variant
>>
>>
>>CREATE FUNCTION GIS.GET_TARIF (
>> p_resid varchar(10),
>> p_date date) returns FIXED(20,2) AS
>> f FIXED(20,2);
>>BEGIN
>> select tarif into :f from gis.fullrestype where resid=:p_resid;
>> return f;
>>END;
>>
>>
>>Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
>>General error;-9000 POS(159) System error: Not yet
>>implemented:parameter of unknown size
>>
>>
>>How can I create function?
>>
>>--
>>MaxDB Discussion Mailing List
>>For list archives: http://lists.mysql.com/maxdb
>>To unsubscribe:
>>http://lists.mysql.com/maxdb?>unsub=thomas.anhaussap.com
>>
>>
>>
>>
>
>First, I assume that the second variant returned -5016 POS(52) Missing
>delimiter: )
>since VARCHAR with length specification is not supported for functions.
>
>If you define VARCHAR for a parameter of a function you tell MaxDB, that
>the length
>of that parameter is not known at compile time. Currently it's not
>possible to use
>those parameters as parameters of sql statements.
>
>In your example you should therefore determine the maximal possible
>length (let's say 100) of parameter p_resid and define it as CHAR(100).
>
>CREATE FUNCTION GIS.GET_TARIF (
> p_resid char(100),
> p_date date) returns FIXED(20,2) AS
> f FIXED(20,2);
>BEGIN
> select tarif into :f from gis.fullrestype where resid=:p_resid;
> return f;
>END;
>
>Best Regards,
>Thomas
>
>
>

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]