Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Stored server side procedures
Tue, Mar 10 2015 2:49 AMPermanent 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 AMPermanent Link

Raul

Team Elevate 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 PMPermanent 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 PMPermanent 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
Image