Hi, I have a stored procedure as follows (please advise if in error):-

CREATE PROCEDURE ProcessHoldInfo(IN MyDate DATE, OUT MyCount INTEGER)
BEGIN
 DECLARE CallsCursor CURSOR WITH RETURN FOR Stmt;
 DECLARE OnHold BOOLEAN DEFAULT FALSE;

 SET MyCount = 0;

 PREPARE Stmt FROM 'SELECT ID, CallRecordTimeStamp, CallID, ConnectionID, OnHoldFlag, Status, ExternalParty, InternalParty,
                    CallDirection, ActiveRecordFlag, TracedFlag, DirectCallFlag, DevContact, QueueRef, AgentRef
                    FROM CallRecords
                    WHERE ExternalParty IN (SELECT ExternalParty FROM CallRecords WHERE OnHoldFlag = True AND CallRecordTimeStamp >= :MyDate)
                    AND LENGTH(ExternalParty) > 5
                    AND CallRecordTimeStamp >= :MyDate
                    ORDER BY CallRecordTimeStamp, ExternalParty, CallID, ConnectionID, Status' ;

 OPEN CallsCursor;
 
 FETCH FIRST FROM CallsCursor ('OnHoldFlag') INTO OnHold;
 WHILE NOT EOF(CallsCursor) DO
    ---FETCH RELATIVE 0 FROM CallsCursor ('OnHoldFlag') INTO OnHold;
    FETCH NEXT FROM CallsCursor ('OnHoldFlag') INTO OnHold;
    SET MyCount = MyCount + 1;
 END WHILE;
 CLOSE CallsCursor;

END

Please show me how to execute the procedure ?

EXEC ProcessHoldInfo('2017-05-10');

Andy

Andrew

I don't use stored procedures myself but since they are very similar to scripts I think I spot a problem

> PREPARE Stmt FROM 'SELECT ID, CallRecordTimeStamp, CallID, ConnectionID, OnHoldFlag, Status, ExternalParty, InternalParty,
> CallDirection, ActiveRecordFlag, TracedFlag, DirectCallFlag, DevContact, QueueRef, AgentRef
> FROM CallRecords
> WHERE ExternalParty IN (SELECT ExternalParty FROM CallRecords WHERE OnHoldFlag = True AND CallRecordTimeStamp >= :MyDate)
> AND LENGTH(ExternalParty) > 5
> AND CallRecordTimeStamp >= :MyDate
> ORDER BY CallRecordTimeStamp, ExternalParty, CallID, ConnectionID, Status' ;

You can't just stuff parameters (MyDate) into a script you need to place ? where you have :MyDate and then

OPEN CallsCursor;

would become

OPEN CallsCursor USING MyDate, MyDate;

As far as running it goes - drop a TEDBStoredProc onto your form set the SessionName, Databasename & StoredProcName, Prepare it, set the parameters PramByName('MyDate').AsDate := whatever and call its ExecProc method. After its done you should be able to read the OUT parameter

Roy Lambert

Andrew

Within an EDB SCRIPT or JOB (but not Statement) you can also use the following syntax:

CALL <ProcedureName>(<list-params> ...);

Adam Brett wrote:

Andrew

Within an EDB SCRIPT or JOB (but not Statement) you can also use the following syntax:

CALL <ProcedureName>(<list-params> ...);

Thanks Adam, done as instructed.

I changed the MyDate type so I can test in EDBmngr.

The procedure fails in execution when I use my IF statement, please advise - thanks.

CREATE PROCEDURE ProcessHoldInfo(IN MyDate VARCHAR(20), OUT MyCount INTEGER)
BEGIN

 DECLARE CallsCursor CURSOR WITH RETURN FOR Stmt;
 DECLARE OnHold BOOLEAN DEFAULT FALSE;

 SET MyCount = 0;

 PREPARE Stmt FROM 'SELECT ID, CallRecordTimeStamp, CallID, ConnectionID, OnHoldFlag, Status, ExternalParty, InternalParty,
                    CallDirection, ActiveRecordFlag, TracedFlag, DirectCallFlag, DevContact, QueueRef, AgentRef
                    FROM CallRecords
                    WHERE ExternalParty IN (SELECT ExternalParty FROM CallRecords WHERE OnHoldFlag = True AND CallRecordTimeStamp >= DATE :MyDate)
                    AND LENGTH(ExternalParty) > 5
                    AND CallRecordTimeStamp >= DATE :MyDate
                    ORDER BY CallRecordTimeStamp, ExternalParty, CallID, ConnectionID, Status' ;

 OPEN CallsCursor USING MyDate;
 
 FETCH FIRST FROM CallsCursor ('OnHoldFlag') INTO OnHold;
 WHILE NOT EOF(CallsCursor) DO
    IF OnHold = TRUE THEN SET MyCount = MyCount + 1 END;
    FETCH NEXT FROM CallsCursor ('OnHoldFlag') INTO OnHold;
 END WHILE;
 CLOSE CallsCursor;

END

ElevateDB Error #700 An error occurred with the ProcessHoldInfo routine at line 3 and column 8 (An error was found in the statement at line 9 and column 21 (Expected date constant but instead found

This is when I use IF and try to create proc, the error above is if I remove the if and execute.

ElevateDB Error #700 An error was found in the statement at line 23 and column 7 (Expected IF but instead found WHILE)

Andrew


It helps if you tell us what the error is.

Try altering

    IF OnHold = TRUE THEN SET MyCount = MyCount + 1 END;

to


    IF OnHold = TRUE
     THEN SET MyCount = MyCount + 1
    END IF;



Roy Lambert

Roy

All good so far except:-

ElevateDB Error #700 An error occurred with the ProcessHoldInfo routine at line 3 and column 8 (An error was found in the statement at line 9 and column 21 (Expected date constant but instead found

Andrew


Try replacing the :MyDate in the SQL with ? and this

 OPEN CallsCursor USING MyDate;

will need to become

 OPEN CallsCursor USING MyDate, MyDate;

Roy Lambert

Roy it errors on ?