One Star

Trigger job by web-service ping, and schedule job on Windows 2003

Good Evening!
I am new to TalenD but am totally loving the concept and the interface. I am a sql guy and love being able to 'touch' the data.
I now have a need to synchronize my local sql server 2005 database with a web application database (Business Catalyst). The web application data is not 'touchable' but I do have access to it via two web-services; Lets just call them Ping and GetRecords for now.
What I need to do is build a job in TalenD that I can export into a .bat file (I'm guessing) that can be both scheduled via windows scheduler, and ALSO respond to a 'Ping' coming from the web service.
The scheduling part I think I got figured out almost, so I can run a 'DailySalesReport' at 12:05 am every day to get all the sales for the previous day.
The part I don't get is how do I set up a workflow to keep the two databases in sync?
Lets say for example, that the web database sells 3 widgets. I want my local database to reflect that asap!
So when the web DB sells 3, it 'pings' my local sql server.
My local sql server then queries the web service 'GetRecords' and asks 'how many widgets just sold?"
It returns three, and updates the database, by reducing the inventory by three.
So my big question is this: How do I export a job (in Java) to sit on a sql server, to listen to a web service for a ping, and reply with a select statement to a different web service?
Anty leads to a tutorial/cook book approach would be wonderful!
Thank you all in advance for any help.
Dan
4 REPLIES
Community Manager

Re: Trigger job by web-service ping, and schedule job on Windows 2003

Hello
So my big question is this: How do I export a job (in Java) to sit on a sql server, to listen to a web service for a ping, and reply with a select statement to a different web service?

In Talend, you can use tWebServiceInput to call a web service and get the return result, and then you can parse the result and do a selecting/inserting/updating action to other database in the job. After designing the job, export the job and deploy it with windows scheduler or other scheduler tool.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Trigger job by web-service ping, and schedule job on Windows 2003

Thank You Shong for the reply!
I have a job in TOS that takes a tWebServiceInput passes it to a tMap and outputs to my local sql server. This runs perfectly within TOS, and makes me very happy. However, I need to schedule it to run on scheduled intervals, as well as respond to a webservice 'ping'.
Two follow up questions if I may?:
1. When I go to export job scripts, I am choosing autonomous job, and accepting all the defaults. (Ive also tried just Shell Launcher: Windows, and all combinations of the above with no luck) when I run the .bat file created, it executes, but no update to my database.
Here is the guts of the bat file created:
%~d0
cd %~dp0
java -Xms256M -Xmx1024M -cp classpath.jar; webservicetest.fridayjob_0_1.FridayJob --context=Default %*

Am I doing something wrong? Need to set classpaths on my local windows (vista) machine?
2. Providing I DO get this .bat file to work, I get how to schedule it to run via windows scheduler. But how to I get it to run in response to a ping from a web server?
Thanks in advance, and sorry for the inexperienced questions.
Dan
Community Manager

Re: Trigger job by web-service ping, and schedule job on Windows 2003

Hello
1. When I go to export job scripts, I am choosing autonomous job, and accepting all the defaults.

Yes, you are getting the correct approach. The job should works the same as you run it within TOS when you click on .bat file out of TOS, is there a tMssqlConnection in your job? Do you use a tmssqlCommit component to commit the records? Can you upload a screenshot of your job.
But how to I get it to run in response to a ping from a web server?

I don't understand you well here.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Trigger job by web-service ping, and schedule job on Windows 2003

Hi Shong-
Here is my job
where wednesdaysql is created by dragging my wednesdaysql datasource that I created under Repository>metadata>Db Connections, and then choosing tMSSqlInput from the pop up menu.
The data source looks like this:
I then create a connection using a tMap that looks like this:
Like I said, when I run this INSIDE TOS, it updates my SQL with data from a web service, and makes me happy.
But when I export the job, and double-click the bat file, I get no update to my sql table. (Bat file code above)

The other issue I need to resolve, is this:
I need two sql jobs to run. One that I can schedule using windows task scheduler (If I can get my bat file to run, I think I have this issue resolved) BUT I also need a .bat file to respond to a 'ping' coming in from the internet. Is there any way I can keep TalenD running, almost like a service, and have it listen to a specific port, and reply to a ping by kicking a TOS job off?
If there were a 101 tutorial that would step me through this, I would LOVE to learn it. I am just in a bit of a time crunch!
Thanks again for any help
Dan