Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 4 of 4 total |
Stored server side procedures |
Tue, Mar 10 2015 2:49 AM | Permanent Link |
John Postnikoff | Hello,
I have not worked with stored procedures much other that the backup example. I have nearly twenty separate SQL updates that wish to apply as stored procedures on two tables with about 200,000 records. This batch process for most parts take place in the middle of the night. I am wondering how this may best be handled. I am a bit unclear how to call and verify each one. Would it be best through customization of the server engine only, or maybe by other means? As each one completes I thought I would record "success" or something to that effect to a log file for each one. I am wondering if there are any limitations, tips or traps that I may need to be aware of. Just looking for some comments before I try to get this into place. John |
Tue, Mar 10 2015 8:40 AM | Permanent Link |
Raul Team Elevate | On 3/10/2015 2:49 AM, John Postnikoff wrote:
> I have not worked with stored procedures much other that the backup example. I have nearly twenty separate SQL updates that wish to apply as stored procedures on two tables with about 200,000 records. This batch process for most parts take place in the middle of the night. I am wondering how this may best be handled. I am a bit unclear how to call and verify each one. Would it be best through customization of the server engine only, or maybe by other means? As each one completes I thought I would record "success" or something to that effect to a log file for each one. I am wondering if there are any limitations, tips or traps that I may need to be aware of. Just looking for some comments before I try to get this into place. John, Not sure if it answers what you're asking but here it goes. For DBISAM you'd need to create a scheduled event in order to have something auto-scheduled and then you can definitely have have server procedures to do the actual work. You do need to customize the engine for this to and unless you want to make the engine changes every time should design it such a way that you can add statements thru some other means (separate table or even text file). The other issue you will have is that handling errors and defining relationships can get tricky (or you need to build lot of logic into your code) - i.e. if task 1 fails then don't run task 2 but task 3 is still ok etc. Also think if yiou need to re-run the task if it fails and how would that work. In our case we were already using engine triggers but decided not to use engine for this functionality but instead wrote a small maintenance app that handles it. It started out as a command-line delphi app that we ran using windows scheduler but now it's a service. We liked the separate app for few reasons - one was that it was lot easier to add more complex tasks that just used delphi code for logic (instead of sql) and to test and deploy updated version of app was simpler since we did not have touch the dbsrvr. The app currently is a mix - it has its own "work" table where we can define sql commands to run but for some things we still do it in code as its quicker. Raul |
Tue, Mar 10 2015 4:37 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | John Postnikoff
Let me explain my solution for this: I have some applications that do it during the night. But like Raul said, I prefer to isolate things from the DBISAM server and create a service called RPServer. There you can schedule procedures to run any time, even to run every hour, etc. RPSERVER (load a DLL to run the procedures) X.DLL (here I wrote any thing I want using Delphi). When I need to update X.DLL then I stop the RPSERVER, update the X.DLL and start it again. I also create a RPCLIENT to send commands to execute complex serverside procedures to avoid network traffic. Maybe in your case a simple application with a timer running on the server solve the problem. Jose Eduardo Helminsky |
Wed, Mar 11 2015 12:57 PM | Permanent Link |
John Postnikoff | Thanks Raul and Jose. Now that I have some insight on this, my gut feeling is to create a separate application as a utility with a timer and have Windows trigger the process. I like the idea of running as a service. My concern is in this case is there are other services already running on the application server. I think I would like to start the process then terminate it after it is complete to save on system resources or possible day conflict. Even though it would be a small utility. Jose Eduardo Helminsky wrote: John Postnikoff Let me explain my solution for this: I have some applications that do it during the night. But like Raul said, I prefer to isolate things from the DBISAM server and create a service called RPServer. There you can schedule procedures to run any time, even to run every hour, etc. RPSERVER (load a DLL to run the procedures) X.DLL (here I wrote any thing I want using Delphi). When I need to update X.DLL then I stop the RPSERVER, update the X.DLL and start it again. I also create a RPCLIENT to send commands to execute complex serverside procedures to avoid network traffic. Maybe in your case a simple application with a timer running on the server solve the problem. Jose Eduardo Helminsky |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |