Send message from cloud server - "hear" it with Winbatch

[stevengraff]

Presently my script works by querying a cloud-based mySQL server every 10 seconds to see if any new records have been inserted in a particular table.

I'd like to improve on this by creating a trigger on the mySQL table that, upon insertion of a new record, sends (pushes?) a signal of some sort to a "listening" WinBatch script, which would immediately fetch the new record(s).

Any idea how I could go about a) sending a "signal" using mySQL and b) "hearing" it with WinBatch?

[JTaylor]

You can implement a listening script and if MySQL server can make a call to your machine/port WinBatch can "hear" that.   I think there is code in the Tech Database that Kirby wrote.  It is a good starting point.

Jim

[stevengraff]

Thanks Jim, that sounds "dead on." I'll look for it.

[JTaylor]

Found it.....http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/Samples~from~Users+Snapshot~Server.txt

Jim

[Deana]

You will probably want to check out the WinSock Extender. Windows Sockets is the underlying system that allows communication over the Internet (or over any network that supports TCP/IP). See the code sample in the WinSock's sAccept topic.

[stevengraff]

;;;The CONNECTER script
AddExtender("wwwsk44I.dll")
while 1
   what=AskLine("Connecter","Enter TIME or QUOTE to get information from listener","QUOTE")
   talksocket=sOpen()
   ret=sConnect(talksocket,"localhost","3399")
   sSendLine(talksocket,what)
   response=sRecvLine(talksocket,555)
   sClose(talksocket)
   Message(what,response)
endwhile

Yes, Deana, that really is perfect.  Now if I can only find a way of generating that communication on my Linux box. You don't happen to make something called LinBatch, do you?

[stevengraff]

I see I can use telnet to communicate with the listener.

[JTaylor]

From the linux box all you have to do is submit a request such as http://your.pc.com:8080/New_Lines

Not certain but can probably do that directly from the database trigger.

If your PC is accessible and listening on that port you can read that and know you need to take action.  You don't need any back and forth communication, assuming I understand.


Jim

[JTaylor]

Maybe something like this will work?  Just need enough of it to submit the request.

http://code.google.com/p/mysql-udf-http/

Jim

[stevengraff]

From the linux box all you have to do is submit a request such as http://your.pc.com:8080/New_Lines

Not certain but can probably do that directly from the database trigger.

If your PC is accessible and listening on that port you can read that and know you need to take action.  You don't need any back and forth communication, assuming I understand.


Jim

Correct.

I thought about it and, since the new line in the db is inserted by my php script, I focused on learning php instead of mySQL. I just added these lines to the script:

$mode = "r";
$command = "telnet myserveripaddr 3399";
$handle = popen ( $command , $mode );
pclose($handle);

[stevengraff]

The "essence" of the listening is:

;;;The LISTENER script
; Use CTRL-Break to exit
AddExtender("wwwsk44I.dll")
listensocket=sOpen()
sListen(listensocket,"3399")   ;
Served=0
while 1
   datasocket=sAccept(listensocket,@TRUE)  ; Block for a connection
   if datasocket
       gosub fetchNewMsgs
   Endif
Endwhile

Doing it this way I can probably drop my old method of polling every 10 seconds, but, then again, maybe not so fast. After all, this method will probably require the IT staff to add the proper port redirect into the company's firewall/router, and I can see some resistance to that. So I'd like to hybrid, and without forcing the user to choose timed vs. triggered.

Is there a way to modify the above so that in addition to waiting for a connection, it would also do the fetchNewMsgs sub every 60 seconds?

I'm thinking one alternative would be to leave the socket open and listening and use    datasocket=sAccept(listensocket,@FALSE)   to check once per second during the countdown.

[JTaylor]

This is more pseudo-code than anything else so adjust as necessary...

Code (winbatch) Select Expand


;;;The LISTENER script
; Use CTRL-Break to exit
AddExtender("wwwsk44I.dll")
listensocket=sOpen()
sListen(listensocket,"3399")   ;
Served=0
t_begin = TimeYmdhms()
t_chk = TimeAdd(t_begin,"0000:00:00:00:00:60")
while 1
   datasocket=sAccept(listensocket,@TRUE)  ; Block for a connection
   if datasocket
       gosub fetchNewMsgs
   Endif
  If TimeYmdhms() >= t_chk Then
    Get_New_Rows()
    t_begin = TimeYmdhms()
    t_chk = TimeAdd(t_begin,"0000:00:00:00:00:60")
EndIf
Endwhile

[stevengraff]

Won't the @True in the datasocket=sAccept line cause the script to wait indefinitely?

[JTaylor]

Didn't pay much attention to that code as, I thought, you indicated you had that part sorted....I generally set that value to @FALSE though.

Jim

[stevengraff]

In that case I think we're on the same page... thanks a lot.