Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 19 of 19 total |
Stored Procedure and Execution |
Mon, May 15 2017 2:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Andrew
I'm guessing, but if you try simple sql in EDBManager like SELECT _Created FROM Companies WHERE _Created >= DATE :xyz then you get ElevateDB Error #700 An error was found in the statement at line 3 and column 18 (Expected date constant but instead found ?) So its not happy with a parameter but is with a constant. Try this CREATE PROCEDURE ProcessHoldInfo(IN MyDate VARCHAR(20), OUT MyCount INTEGER) BEGIN DECLARE CallsCursor CURSOR WITH RETURN FOR Stmt; DECLARE OnHold BOOLEAN DEFAULT FALSE; DECLARE tmp VARCHAR; SET MyCount = 0; SET tmp = '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 '+QUOTEDSTR(MyDate) +') AND LENGTH(ExternalParty) > 5 AND CallRecordTimeStamp >= DATE '+ QUOTEDSTR(MyDate) ORDER BY CallRecordTimeStamp, ExternalParty, CallID, ConnectionID, Status' ; PREPARE Stmt FROM tmp; OPEN CallsCursor; 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 If that doesn't work can you reverse engineer your database and post the code and I'll try debugging it here and if I succeed post code back that prepares and runs (even if it gives the wrong answer Roy Lambert |
Mon, May 15 2017 2:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Andrew,
<< 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 >> The issue is this: DATE :MyDate in your SQL statement. You don't need the DATE part when using a parameter, only when using a DATE constant. So, it should just be: :MyDate Tim Young Elevate Software www.elevatesoft.com |
Tue, May 16 2017 5:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>DATE :MyDate > >in your SQL statement. You don't need the DATE part when using a parameter, only when using a DATE constant. So, it should just be: > >:MyDate Is that true in this case? Initially Andrew had the parameter as DATE but changed it to VARCHAR(20) for testing (not sure why) so is ElevateDB going to do an automatic transform? Roy Lambert |
Tue, May 16 2017 7:24 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Is that true in this case? Initially Andrew had the parameter as DATE but changed it to VARCHAR(20) for testing (not sure why) so is ElevateDB going to do an automatic transform? >> You can't do that (combine DATE with a VARCHAR parameter). The correct method is to use a date parameter, period. If you need to convert data, you do it when assigning the parameter using CAST, etc., not by trying to get EDB to perform data type conversions when executing the SQL (hint: it won't ). Tim Young Elevate Software www.elevatesoft.com |
Tue, May 16 2017 8:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>You can't do that (combine DATE with a VARCHAR parameter). The correct method is to use a date parameter, period. If you need to convert data, you do it when assigning the parameter using CAST, etc., not by trying to get EDB to perform data type conversions when executing the SQL (hint: it won't ). That's what I thought which is why I queried your post. Andrew moved from a date parameter to a varchar for testing but its why I suggested building the sql string first then executing it. Looking back at his posts it seems he's trying to call this SP from script or something and used varchar so he could simply type the data in. Roy Lambert |
Wed, May 17 2017 5:58 PM | Permanent Link |
Andrew Hill | Thanks Roy and Tim.
I had to tweak the code a little to compile so now how do I see the MyCount return value when called :- SCRIPT BEGIN CALL ProcessHoldInfo('2017-05-11'); END CREATE PROCEDURE ProcessHoldInfo(IN MyDate VARCHAR(20), OUT MyCount INTEGER) BEGIN DECLARE CallsCursor CURSOR WITH RETURN FOR Stmt; DECLARE OnHold BOOLEAN DEFAULT FALSE; DECLARE tmp VARCHAR; SET MyCount = 0; SET tmp = '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 '+QUOTEDSTR(MyDate) +') AND LENGTH(ExternalParty) > 5 AND CallRecordTimeStamp >= DATE ' + QUOTEDSTR(MyDate) + ' ORDER BY CallRecordTimeStamp, ExternalParty, CallID, ConnectionID, Status ' ; PREPARE Stmt FROM tmp; OPEN CallsCursor; FETCH FIRST FROM CallsCursor ('OnHoldFlag') INTO OnHold; WHILE NOT EOF(CallsCursor) DO IF OnHold = TRUE THEN SET MyCount = MyCount + 1; END IF; FETCH NEXT FROM CallsCursor ('OnHoldFlag') INTO OnHold; END WHILE; CLOSE CallsCursor; END |
Thu, May 18 2017 5:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Andrew
<< SCRIPT BEGIN CALL ProcessHoldInfo('2017-05-11'); END >> has me slightly flummoxed. I know if you don't pass an IN parameter the value is set as null which I found very handy and do not want to see changed so I imagine its a bit the same with an OUT parameter, however, if you're not passing in a variable I'm not sure how you can get it out. I'm guessing here but try SCRIPT BEGIN DECLARE Counter INTEGER DEFAULT -1; CALL ProcessHoldInfo('2017-05-11', Counter); do whatever using Counter END Roy Lambert |
Thu, May 18 2017 5:22 PM | Permanent Link |
Andrew Hill | Roy, How do I get a Result Set returned by the procedure ?
CREATE PROCEDURE ProcessHoldInfo(IN MyDate VARCHAR(20), OUT Counter INTEGER) BEGIN DECLARE CallsCursor CURSOR FOR Stmt; DECLARE OnHold BOOLEAN DEFAULT FALSE; DECLARE tmp VARCHAR; SET Counter = 0; SET tmp = '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 '+QUOTEDSTR(MyDate) +') AND LENGTH(ExternalParty) > 5 AND CallRecordTimeStamp >= DATE ' + QUOTEDSTR(MyDate) + ' ORDER BY CallRecordTimeStamp, ExternalParty, CallID, ConnectionID, Status ' ; PREPARE Stmt FROM tmp; OPEN CallsCursor; FETCH FIRST FROM CallsCursor ('OnHoldFlag') INTO OnHold; WHILE NOT EOF(CallsCursor) DO IF OnHold = TRUE THEN SET Counter = Counter + 1; END IF; FETCH NEXT FROM CallsCursor ('OnHoldFlag') INTO OnHold; END WHILE; CLOSE CallsCursor; END SCRIPT BEGIN DECLARE Counter INTEGER DEFAULT -1; CALL ProcessHoldInfo('2017-05-11', Counter); END |
Fri, May 19 2017 2:07 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Andrew,
<< Roy, How do I get a Result Set returned by the procedure ? >> You can only retrieve result sets from procedures from a client (TEDBStoredProc). You can't do so in other scripts, procedures/functions, etc. Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |