Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 19 total |
Stored Procedure and Execution |
Thu, May 11 2017 3:51 AM | Permanent Link |
Andrew Hill | 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 |
Thu, May 11 2017 5:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, May 11 2017 10:21 AM | Permanent Link |
Adam Brett Orixa Systems | Andrew
Within an EDB SCRIPT or JOB (but not Statement) you can also use the following syntax: CALL <ProcedureName>(<list-params> ...); |
Thu, May 11 2017 4:34 PM | Permanent Link |
Andrew Hill | 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 |
Thu, May 11 2017 4:37 PM | Permanent Link |
Andrew Hill | 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
|
Thu, May 11 2017 5:01 PM | Permanent Link |
Andrew Hill | 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) |
Fri, May 12 2017 3:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, May 12 2017 11:48 PM | Permanent Link |
Andrew Hill | 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 |
Sat, May 13 2017 3:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, May 14 2017 4:07 PM | Permanent Link |
Andrew Hill | Roy it errors on ?
|
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |