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

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

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

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

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

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

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

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

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