Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Date tips |
Mon, Apr 2 2012 4:36 PM | Permanent Link |
John Postnikoff | 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. |
Tue, Apr 3 2012 4:14 AM | Permanent Link |
Uli Becker | 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 |
Tue, Apr 3 2012 4:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Apr 3 2012 11:25 AM | Permanent Link |
John Hay | 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 |
Tue, Apr 3 2012 12:12 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Apr 3 2012 11:16 PM | Permanent Link |
John Postnikoff | 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 |
Wed, Apr 4 2012 2:11 AM | Permanent Link |
Uli Becker | 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 |
Thu, Apr 5 2012 1:10 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |