Run sql in the background

Using Servoy to administrate the content of your website? Discuss all webrelated Servoy topics on this forum!

Run sql in the background

Postby banff.moon » Sat Dec 20, 2008 8:59 pm

Hi,
I created a web app. I want to add a function in a form to send the query to another solution and run this query in the background because the query will retrieve more than 100,000 records, which is going to take long time. After the query is done then export the data to a file. Users still can do other things while the query is running. Is it possible?
Multiple users might send different query at the same time. My understanding is when users call this solution, they will get a instance of this solution so the queries won't be in conflict, right?
Or someone has other idea to run query in the background?

Thanks,
banff.moon
 
Posts: 28
Joined: Mon Dec 15, 2008 10:22 pm

Re: Run sql in the background

Postby IT2Be » Sat Dec 20, 2008 10:13 pm

I have a Plug-in for internal that executes a method in the background 'plugins.xxx.executeMonitoredMethod(method, arguments)'.

But I guess you could use the Scheduler Plug-in as well.
Have it fired immediately and execute only once. The job will run in the background without you noticing it.
Don't forget to remove the job so that you can re-use the name.

Hope this helps.
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Re: Run sql in the background

Postby Harjo » Sun Dec 21, 2008 9:45 pm

Marcel, that would be a great plugin!! :-)

the scheduler, does not fire the method in the background. :-(
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Run sql in the background

Postby pbakker » Mon Dec 22, 2008 10:12 am

Harjo,

I think Marcel means: Fire a client in the background, in which the scheduler plugin fires of a method to run once and kill the client afterwards.

Running methods multithreaded within 1 client is not supported right now and allthough you could start another thread in an existing client using some inline Java code, it might get you in trouble, as the different threads might start influencing eachother etc.

Paul
pbakker
 
Posts: 2822
Joined: Wed Oct 01, 2003 8:12 pm
Location: Amsterdam, the Netherlands

Re: Run sql in the background

Postby IT2Be » Mon Dec 22, 2008 11:09 am

I think Marcel means: Fire a client in the background, in which the scheduler plugin fires of a method to run once and kill the client afterwards.
Yes, I guess my thinking is sooooo fast that I miss pieces while writing my thoughts down.

Running methods multithreaded within 1 client is not supported right now and allthough you could start another thread in an existing client using some inline Java code, it might get you in trouble, as the different threads might start influencing eachother etc.
I agree, that is why it is for internal use right now.
You have to make damn sure resources are not used twice at the same time.
When resources are not synchronizable (and I can only be sure about this when the resources are mine) you could bump into trouble.
To avoid too much risk I use the Servoy threadpool but I admit that this could be risky business...
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Re: Run sql in the background

Postby pbakker » Mon Dec 22, 2008 11:15 am

Note: Using the mechanism to start a separate client in the background (headless or Webclient) you also get the benefit that the process runs serverside, so close to DB's etc. making the process of extracting data from the DB faster.

Note that when you use the WebClient for this, the process you run in the background shouldn't take longer than the timeout of the application server for webclients (30 minutes in default install) or else, due to the lack of "UI" updtae traffic, the Application Server will "kill" the session.

Paul
pbakker
 
Posts: 2822
Joined: Wed Oct 01, 2003 8:12 pm
Location: Amsterdam, the Netherlands

Re: Run sql in the background

Postby IT2Be » Mon Dec 22, 2008 4:33 pm

Note: Using the mechanism to start a separate client in the background (headless or Webclient) you also get the benefit that the process runs serverside, so close to DB's etc. making the process of extracting data from the DB faster.

Note that when you use the WebClient for this, the process you run in the background shouldn't take longer than the timeout of the application server for webclients (30 minutes in default install) or else, due to the lack of "UI" updtae traffic, the Application Server will "kill" the session.
Valuable feedback Paul, thank you.
I use it in a batchprocessor. I have the batchprocessor listening to web requests continuously and did not (yet) want to create a task list.
To avoid issues with the listener I have another process running in its own thread as described above.
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Re: Run sql in the background

Postby banff.moon » Mon Dec 22, 2008 8:42 pm

Note that when you use the WebClient for this, the process you run in the background shouldn't take longer than the timeout of the application server for webclients (30 minutes in default install) or else, due to the lack of "UI" updtae traffic, the Application Server will "kill" the session.

Thank you all for replying.
My query probably runs for 2 or more hours. Then there is no way to have Application Server to keep running?
I think if users submit a query and the query will run in the background then as long as the application server is running the query should keep running till it gets data retrieved even users log out.
banff.moon
 
Posts: 28
Joined: Mon Dec 15, 2008 10:22 pm

Re: Run sql in the background

Postby IT2Be » Mon Dec 22, 2008 9:26 pm

You can follow the suggestion of Paul:
Note: Using the mechanism to start a separate client in the background (headless or Webclient) you also get the benefit that the process runs serverside, so close to DB's etc. making the process of extracting data from the DB faster.
And fire a headless client.

1. Another option, but a bit less 'natural' is that you use the tools plugin to write a file with your query to the server and have a batch processor run the scheduler that polls the directory where queries are dropped every x interval. The batch processor can perform the query for you and write the result file. Disadvantage is that you don't know when the file is finished unless you would use the udp plugin to signal other clients.

2. Your last option is that your write (or ask us) a client-server plugin that 'asks' the server to perform a query and write the result file.
When the file is written the plugin can broadcast a flag to a method in your client solution. All clients will receive the flag but you can of course check the source against the receiver.
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Re: Run sql in the background

Postby IT2Be » Mon Dec 22, 2008 9:28 pm

The nice thing about option 1 might be that you can influence the time when queries are run very easy.
For instance have them running when workload is low and send an email with a download link to the requesting client.

Just some idea's to get you going...
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Re: Run sql in the background

Postby banff.moon » Mon Dec 22, 2008 10:09 pm

IT2Be wrote:2. Your last option is that your write (or ask us) a client-server plugin that 'asks' the server to perform a query and write the result file.
When the file is written the plugin can broadcast a flag to a method in your client solution. All clients will receive the flag but you can of course check the source against the receiver.

This is what I'm trying to do. I added a button to the form. When clicking this button it will call a global method; this method will add a job to scheduler plugins.scheduler.addCronJob(time, globalmethod-1, argument); in globalmethod-1 the query will run and write to a file on the server; when it's done it will send an email to users with a download url attached.
Is it implementable? If it's not can you tell me how to write a client-server plug in?

Thanks,
banff.moon
 
Posts: 28
Joined: Mon Dec 15, 2008 10:22 pm

Re: Run sql in the background

Postby ROCLASI » Mon Dec 22, 2008 10:19 pm

Why not use a batch processor and a table where you put in your batched processes.
I.e. you hit a button in your client and that adds a record in your 'batchedproccesses' table.
The already running batch processor on the server checks that table very x time (like every minute) and when something new was added it sets the scheduler with the provided data.
When the scheduler process is triggered it deletes the event from the 'batchedprocesses' table.

Makes it all database driven and manageble.

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Run sql in the background

Postby IT2Be » Mon Dec 22, 2008 10:27 pm

This is what I'm trying to do. I added a button to the form. When clicking this button it will call a global method; this method will add a job to scheduler plugins.scheduler.addCronJob(time, globalmethod-1, argument); in globalmethod-1 the query will run and write to a file on the server; when it's done it will send an email to users with a download url attached.
This will not run anything in the background (as Paul pointed out). You could/should either follow my suggestion 1 or 2.
Is it implementable?
As said, it will not run in the background.
If it's not can you tell me how to write a client-server plug in?
That is quite a story. There is some documentation on how to take off with a simple plugin on the Servoy website.
The rest is not too difficult but it will take time to explain what to do.
You can buy support hours on our website if you want my help or aks your questions on our forum (http://www.it2be.com) when the time comes and you think you can do most on your own.
The word is RMI...
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany


Return to Web Development

Who is online

Users browsing this forum: No registered users and 7 guests