Loading values into a MYSQL database

Shell Programming and Scripting
1

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&instId=12345&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.

2

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.

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

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

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

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