European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Databases :: MySql :: How to automate updating MySQL database from remote?
By: Skyline-g	Date: 10/06/2003 00:00:00	Points: 125	Status: Answered Quality : Excellent
My goal is automate UPDATE job. I have a small inventory database, about 2000 records, at shared web host which runs MySQL 4.0.12. The only ways I know to update records are (1)using phpMyAdmin 2.4.0, (2)running PHP script, and (3)running script from Zeos Database Explorer. Now I am tired doing this everyday, and trying to find a way to update database automatically. I have a database at office running old DBase III on Windows 2000 server. And I wrote a tiny VB program to create a plain text file with many SQL's INSERT commands and delete all records at the beginning. Everyday I am uploading (executing) that file (about 500KB) with phpMyAdmin's web page to shared web server. I think I can automate file upload since we have DSL connection, off course, and FTP account. But I am not sure if I could automate updates on MySQL database records for shared web server, or execute a script from remote automatically. Agina I have FTP access, but don't have a shell access. All of the maintenance are done with so called Control Panel. I can write a program to do this, but I just want to know if somebody has better ideas. Thank you very much. Skyline
By: VGR	Date: 10/06/2003 01:47:00		Type : Comment
Yes, I've ideas : 1) either stay 100% local for running VB program + the PHP script that will update the REMOTE MySql database - this implies installing - 2 minutes chrono - an Apache+PHP on your server -also you can schedule the VB program execution 2) either modify/enhance your VB program, not to dump the textfile locally, but to send it via FTP to the server and then use my RobotOuaibe Windows program, that will execute an HTTP query to the remote webserver, for a specially crafted PHP page (the same as in (1) in my humble opinion) that will perform the update. The benefit is not to block the webserver for doing the update, while serving web clients' requests.

By: Skyline-g	Date: 10/06/2003 02:08:00		Type : Comment
Thank you VGR for quick reply. I am sorry I did not explain well, but the reason showing inventory database is for our sales persons who are out of state. So I cannot run everything on local. What's your RobotOuaibe program? That sounds good anyway. I did some search on Google, and not much was returned. Would you explain it or give me a link that I can read about it? Thank you.

By: VGR	Date: 10/06/2003 02:55:00		Type : Assist
it's just what it's name tells : a robot for the Web it works like cron and its crontab on *nix, but on Zindoze : a TTimer, a HTTP component, and vogue la galère. It'll perform specified request for URLs at the specified times of each day (yes, it's meant for working on a dayly basis, unlike cron who can be set to run each month, each year...) it is in the third party tools at <A HREF="http://www.edainworks.com">www.edainworks.com</a> The interface is in English but logging messages are not (they're not that important :D ) Here's its config file : (you can have as many lines as you wish, and yes, the first one is the PPoE Dialer to re-establish the DSL line if it went down...) Dialer C:\Program Files\iVasion\WinPoET\WrDialer.exe update1 <A HREF="http://localhost/firstpage/maj.php">http://localhost/firstpage/maj.php</a> 00:01 HackAlerts <A HREF="http://localhost/secondpage.php">http://localhost/secondpage.php</a> 06:00

By: Skyline-g	Date: 10/06/2003 07:51:00		Type : Comment
VGR, thank you. I will try this program and let you know the outcome.

By: VGR	Date: 10/06/2003 07:59:00		Type : Comment
for instance the 00:01 will very soon trigger itself ;-) you can see the progress of the update, synchronized by the DB, from the client's part of the site at <A HREF="http://www.bdniouzes.net">http://www.bdniouzes.net</a> it'mm start very soon and will last 10 minuets at most (5% CPU in stead of 100% hen running interactively, don't ask me why :D )

By: VGR	Date: 10/06/2003 08:02:00		Type : Comment
46%, 115 KB/s download

By: VGR	Date: 10/06/2003 08:03:00		Type : Comment
and the robot just logged : 11/06/2003 0:01:32 Appel de update1

By: VGR	Date: 10/06/2003 08:10:00		Type : Comment
done. I hope you saw the IFRAME. It disappeared automatically when the update is done and on the next user action, of course.

By: psadac	Date: 10/06/2003 09:41:00		Type : Assist
just an alternative to VGR tools :-) i suppose you have already made a php script (update_script.php) which updates your database with a file (file.sql) you upload on your ftp server. just do this : create the 2 files below replacing ftp_user, ftp_password, ftp_host, .... with their real values. --------- ftp_instructions.txt --------- quote user ftp_user quote pass ftp_password binary put d:\local\path\to\my\file.sql /remote/path/to/my/file.sql quit --------- ftp_instructions.txt --------- --------- update_db.bat --------- db3_to_sql_converter.exe ftp -n ftp_host < ftp_instructions.txt wget <A HREF="http://http_host/path/to/my/update_script.php">http://http_host/path/to/my/update_script.php</a> --------- update_db.bat --------- download unixutils.zip at <A HREF="http://unxutils.sourceforge.net/">http://unxutils.sourceforge.net/</a> and extract wget.exe in a directory in your path (c:\winnt for example). wget is a powerful command line utility used to retrieve/mirror web/ftp pages or entires sites. then add update_db.bat in your scheduled tasks using at command at (or winat for ease of use). PS to VGR : does <A HREF="http://www.edainworks.com">www.edainworks.com</a> make some "frame balancing" between fecj.org.hebergement-dynamique.org and <A HREF="http://www.on101.co.uk">www.on101.co.uk</a> ? ;-) it seems to be down tonight. :-(

By: Skyline-g	Date: 10/06/2003 10:02:00		Type : Comment
psadac, thank you for your idea. That sounds much interested. I don't have PHP script to update (update_script.php) now, but I will have to make it to try this out. Then I will download and test it as well. Thanks again.

By: VGR	Date: 10/06/2003 14:05:00		Type : Comment
yes, but your problem will begin here : "then add update_db.bat in your scheduled tasks using at command at (or winat for ease of use)." : it's here that RobobotOuaibe intervenes, not before (automating the FTP transfer is good) and you lack an automation of "producing file.sql". 1) file.sql has to be produced automatically by a local script => we fall into the "local server case" again (quoting : "I cannot run everything on local") 2) how do you invoke update_db.bat with AT 3) what is db3_to_sql_converter.exe ? is this also supposed to be findable in the PATH ? or is it the VB program ? 4) the rest is fine, as long as there is some local file to send ! in fact, you implemented my solution #2 : "2) either modify/enhance your VB program, not to dump the textfile locally, but to send it via FTP to the server and then use my RobotOuaibe Windows program" :D but i don't care. I'm just trying to help and advise the most misely the Asker regards

By: Skyline-g	Date: 10/06/2003 14:57:00		Type : Comment
VGT, please take it easy on me. I am just trying out ideas you guys gave me. Sorry I could not explain exactly, but when I wrote "I cannot run everything on local," I meant I cannot host database locally since Windows 2000 server is behind firewall and don't accept incoming access. Also I did not understand your 3 comments starting "46%, 115 KB/s download." I was pretty much confused. And the link you gave me shows all French, I guess. I cannot understand it at all. Your program seems very interesting, but I need more time to understand it. If there are English instruction, please send it to me. Lastly, my excuse is some problem arose today and it got to be solved very quickly and I don't have time to try ideas you guys gave me. Please be patient. Thank you.

By: VGR	Date: 10/06/2003 15:10:00		Type : Comment
I am. (easy taking :D ) 1) if you can run local webserver and outwards access the MySql database, so my first-first suggestion now stands ;-) 2) I was just showing you (and others ;-) a site where you can browse while a DB update is "running in the background" thanks to RobotOuaibe. There was a top-right progress bar saying "update in progress X %" I was just demonstrating live. I know a lot of people don't try to read French for such difficult words as "éditeur" (editor), "oublie" (oblivion), portail (portal), signaler (to signal), "bienvenue", "site", "phase", "editions" or "premier" :D Anyway, it was just a sugegstion to see the progress bar, updated every 5 seconds... Nothing to do with the small program, except that it was an example of its use :D 3) no problem. I'm just trying to help at your rythm

By: gamingz	Date: 16/06/2003 11:40:00		Type : Answer
Well there was a daemon made for this exact thing called Cron. The job is called a Cron Job :) and can be accessed through control panel. You write a script that updates the database and does whatever you want, then go to control panel and click on "Cron Jobs". If it is not their then your host does not offer them by the way. Back to the subject, the cron job runs your script at whatever times of day you want, or time of month or time of year or whenever. It is pretty easy to setup, but here is a small example: minute hour day month weekday 0 0 * * * GET <A HREF="http://www.filename.com/filename/">http://www.filename.com/filename/</a> This would run the file eveyr day at midnight server time. The * symbol means all or every, which makes the cron job run eveyr day of every month of evyer weekday at 0 hours(midnight) and 0 minutes. gamingz PS I think VGR mentioned Cron but not in very high detail ;).

By: Skyline-g	Date: 16/06/2003 13:53:00		Type : Comment
gamingz, thank you. But I still have to finish the other things first. I hope I can try ideas you guys gave me soon.

By: Skyline-g	Date: 08/07/2003 09:21:00		Type : Comment
I have not yet solved my problem, but I think I got enouth ideas. Thanks everyone.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 330.166100 milliseconds

Why Google AdSense ads ?

New Improved Search!