Loading values into a MYSQL database

kshelluser · #1 (**permalink**) 12-05-2006

Where to start?

Ok, I need to pick up a Worldpay exchange rates file from a url such as:
https://select.worldpay.com/wcc/info...op=rates-today

the http response returns a exchange rates file with content-type "text/plain" content as below:

#Exchange rates for installation 12345
#Tue Dec 05 13:28:23 GMT+00:00 2006
rateDateString=2006-12-05
allRatesCurrent=true
rateDateMillis=1165276800000
GBP_GBP=1.0
GBP_USD=1.4821286404310512
GBP_EUR=1.6173201897996292

I need to load the GBP_USD & GBP_EUR values into a MYSQL database on a nightly basis. Can anyone suggest how I could do this?

I was thinking
1. Write a shell script to to pick up the exchange rates file from the url - is this possible in unix?
2. Parse the exchange rates file in unix using awk, to get the GBP_USD & GBP_EUR exchange rates.
3. pass these values to a stored procedure to load into the database? can this be down in unix? I have executed Oracle stored procedures from unix using the SQLPLUS command, but never done this to a MYSQL database.

I'm still new to unix so I just thinking things through before I start.

Any help greatly appreciated.

Thanks.

Corona688 · #2 (**permalink**) 12-05-2006

Quote:

1. Write a shell script to to pick up the exchange rates file from the url - is this possible in unix?

Yes, this'd be the natural place to start. You couldn't ask for a better input format, nothing better than UNIX for eating text. And once you've got the values it won't be difficult to stick them in a query and feed them into the database console. For getting the page itself automatically, you can use wget.

Quote:

2. Parse the exchange rates file in unix using awk, to get the GBP_USD & GBP_EUR exchange rates.

Or bash, or perl, or any other language that can read and write text. You have many options.

Quote:

3. pass these values to a stored procedure to load into the database? can this be down in unix? I have executed Oracle stored procedures from unix using the SQLPLUS command, but never done this to a MYSQL database.

mysql has a similar commandline tool. It is called, bafflingly, mysql.

Code:

tyler@mecgentoo ~ $ echo "SHOW DATABASES" | mysql -u<clipped>
Database
information_schema
inventory
mec
mec_login
parts
test
tools

kshelluser · #3 (**permalink**) 12-05-2006

Great reply that, thanks it gives me somewhere to start.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
unix script for loading a data from a file into database	grajesh_955	Shell Programming and Scripting	5	11-08-2008 11:49 PM
PHP mySQL Database Wrapper Class 1.1.2 (Default branch)	iBot	Software Releases - RSS News	0	04-01-2008 11:20 AM
How to insert data into MYSql database from a text file	shirleyeow	Shell Programming and Scripting	4	01-11-2008 05:00 AM
command(s) to find size of mysql database?	sbourgeois	UNIX for Dummies Questions & Answers	4	06-12-2006 12:40 AM
MYSQL command to take a backup of the database in Redhat linux 7.2	bache_gowda	UNIX for Advanced & Expert Users	4	01-21-2004 02:16 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by