Hello,

I am having some trouble with dates again and using params and stored procedures again.  I would like to move and remove records that are 90 days less than an orderdate.

First I cannot access or set a param value in the query component window to add a new param in an EDBQuery. I eg. param 0 = AsDate, or anything else for all that matters.

EDBQrypurge.Params[0].AsDate := Now - 90;  

Also getting a list index out of bounds error if I try above, which worked for me before in DBISAM.



Secondly I thought I would try a stored procedure and test in the Elevated DB Manager.  I seem to execute without errors, however I have no results, the tables remain the same unchanged without any error showing.  In this example I am trying to archive any orders more than 90 days into one table and purge anything older than 90 days in the first one. Here is what I tried. This is a new attempt to use CAST this way.

ALTER PROCEDURE "ShipmentsArchive" ()
BEGIN

START TRANSACTION ON TABLES 'ShipOrders', 'ShipOrdersArchive';
BEGIN
 EXECUTE IMMEDIATE 'INSERT INTO ShipOrdersArchived SELECT * FROM ShipOrders WHERE Orderdate < CAST(Current_Date - 90)';

 EXECUTE IMMEDIATE 'DELETE FROM ShipOrders WHERE orderdate < CAST(Current_Date - 90)';
 COMMIT;
EXCEPTION
 ROLLBACK;
END;

END


Thanks for any tips or support.

John,

> I am having some trouble with dates again and using params and stored procedures again.
I would like to move and remove records that are 90 days less than an
orderdate.
> First I cannot access or set a param value in the query component window to add a new param in an EDBQuery.
I eg. param 0 = AsDate, or anything else for all that matters.

The "list index out of bounds" indicates that there is a problem with
the list of params. Did you try to prepare the query?
What I generally use is:

EDBQrypurge.ParamByName('FOrderDate').asDateTime := Date - 90;

And that works.

> Secondly I thought I would try a stored procedure and test in the Elevated DB Manager.  I seem to execute without errors, however I have no results, the tables remain the same unchanged without any error showing.  In this example I am trying to archive any orders more than 90 days into one table and purge anything older than 90 days in the first one. Here is what I tried. This is a new attempt to use CAST this way.

You have to deal with intervals in EDB. Use this:

SELECT * FROM ShipOrders WHERE Orderdate < (CURRENT_DATE - interval '90'
day)

Please pay attention to the quotes around the number of days: '90'.

Regards uli

John


Depending on the rest of the query code this

EDBQrypurge.Params[0].AsDate := Now - 90;

should work. However, you have to Prepare the query first otherwise it doesn't understand parameters. That one's caught me out a lot

The stored procedure has no chance. ElevateDB no longer does data arithmetic in the nice, easy to understand, simple way that was DBISAM. You now have the joys of INTERVAL.

ALTER PROCEDURE "ShipmentsArchive" ()
BEGIN

START TRANSACTION ON TABLES 'ShipOrders', 'ShipOrdersArchive';
BEGIN
 EXECUTE IMMEDIATE 'INSERT INTO ShipOrdersArchived SELECT * FROM ShipOrders WHERE (CURRENT_DATE - Orderdate) >= INTERVAL ''90'' DAY';

 EXECUTE IMMEDIATE 'DELETE FROM ShipOrders WHERE (CURRENT_DATE - orderdate) >=  INTERVAL ''90'' DAY';
 COMMIT;
EXCEPTION
 ROLLBACK;
END;

should work. Note the quotes around the 90 - they are needed

Roy Lambert [Team Elevate]

Roy

> ALTER PROCEDURE "ShipmentsArchive" ()
> BEGIN
>
> START TRANSACTION ON TABLES 'ShipOrders', 'ShipOrdersArchive';
> BEGIN
>   EXECUTE IMMEDIATE 'INSERT INTO ShipOrdersArchived SELECT * FROM ShipOrders WHERE (CURRENT_DATE - Orderdate) >=
INTERVAL ''90'' DAY';
>
>   EXECUTE IMMEDIATE 'DELETE FROM ShipOrders WHERE (CURRENT_DATE - orderdate) >=  INTERVAL ''90'' DAY';
>   COMMIT;
> EXCEPTION
>   ROLLBACK;
> END;

Do you know if current_date is guaranteed to be the same in the two calls?  I know it's maybe a little paranoid (or a
lot!) but I have always assigned it to a variable and then used the variable.

John

John

>Do you know if current_date is guaranteed to be the same in the two calls? I know it's maybe a little paranoid (or a
>lot!) but I have always assigned it to a variable and then used the variable.

I don't think you're being unreasonably paranoid, depending on when the task is run. If its during a normal working day its unlikely to be a problem, if an automated run overnight it could cause a glitch.

If it was CURRENT_TIMESTAMP / CURRENT_TIME then there would definitely be a problem.

Roy Lambert

First thank for your great support. Sadly I am still having trouble with both options.

1. Here is my code to purge a table.  The question still remains why I cannot access the param property in a query component and add parameters, although I am OK to use code as below. Still get a list out of bounds.

Qrypurge.Params[0].AsDate := Now - 90;
Qrypurge.Prepare;
Qrypurge.ExecSql;
Qrypurge.Close;

2. I am still not showing any results after preparing and executing within the DBManager and Intervals within the stored procedure. I tried to copy and past your code and now get and error 700 on the very last line stating a missing label. I also tried a simple statement without Intervals or dates and am having the same problem so I am thinking there is an execution problem that I am not seeing with use of intervals, or possibly the transaction itself.  I can copy data from one table to the other without the use of dates in a stored procedure.

And to answer John Hay's question.  The date is the same on both calls, otherwise I would use vars as well.

I look forward to further help.

John

Roy Lambert wrote:

John

>Do you know if current_date is guaranteed to be the same in the two calls? I know it's maybe a little paranoid (or a
>lot!) but I have always assigned it to a variable and then used the variable.

I don't think you're being unreasonably paranoid, depending on when the task is run. If its during a normal working day its unlikely to be a problem, if an automated run overnight it could cause a glitch.

If it was CURRENT_TIMESTAMP / CURRENT_TIME then there would definitely be a problem.

Roy Lambert

John,

> First thank for your great support. Sadly I am still having trouble with both options.
> 1. Here is my code to purge a table.  The question still remains why I cannot access the param property in a query component and add parameters, although I am OK to use code as below. Still get a list out of bounds.
>
>   Qrypurge.Params[0].AsDate := Now - 90;
>   Qrypurge.Prepare;
>   Qrypurge.ExecSql;
>   Qrypurge.Close;

You have to prepare the query *before* assigning the value of the param:

Qrypurge.Prepare;
Qrypurge.Params[0].AsDate := Now - 90;
Qrypurge.ExecSql;
Qrypurge.Close;

Uli

John,

<< 1. Here is my code to purge a table.  The question still remains why I
cannot access the param property in a query component and add parameters,
although I am OK to use code as below. Still get a list out of bounds. >>

Does your TEDBQuery component have its ParamCheck property set to True (the
default) ?  If so, then just setting the SQL will populate a list of
parameters for the TEDBQuery component.  Preparing a query will take the
extra step of automatically setting the data type, length, scale of the
parameters based upon their usage in the query, but they can be overriden.

<< 2. I am still not showing any results after preparing and executing
within the DBManager and Intervals within the stored procedure. I tried to
copy and past your code and now get and error 700 on the very last line
stating a missing label. I also tried a simple statement without Intervals
or dates and am having the same problem so I am thinking there is an
execution problem that I am not seeing with use of intervals, or possibly
the transaction itself.  I can copy data from one table to the other without
the use of dates in a stored procedure. >>

Try this instead:

ALTER PROCEDURE "ShipmentsArchive" ()
BEGIN

START TRANSACTION ON TABLES 'ShipOrders', 'ShipOrdersArchive';
BEGIN
 EXECUTE IMMEDIATE 'INSERT INTO ShipOrdersArchived SELECT * FROM ShipOrders
WHERE (CURRENT_DATE - Orderdate) >= INTERVAL ''90'' DAY';

 EXECUTE IMMEDIATE 'DELETE FROM ShipOrders WHERE (CURRENT_DATE - orderdate)
>=  INTERVAL ''90'' DAY';
 COMMIT;
EXCEPTION
 ROLLBACK;
END;

END <<<<<< Make sure that this is here

--
Tim Young
Elevate Software
www.elevatesoft.com