Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

Bump this to the proper place if necessary. Not sure where I can get the best response.

I need to create a reporting application and would like to do it with Ubuntu if possible. The originating data comes from an appliance based application that can periodically FTP a delimited log file to a designated location. I would like to develop a mechanism to use that data to update a MySQL database (to be created) that will be used for building scheduled reports that are eventually emailed to various distribution groups (either as PDFs of Excel formatted). We have the internal smarts to create the various MySQL queries (and to design the database) but don't have the Linux/Ubuntu know how to determine the best approach. We are doing something similar for another application on the Windows side using Crystal Reports, but given the need to distribute this application, would love to not have to deal with Windows licensing costs etc...

I need advise on the proper tools/approach, but also recommendation of where I might be able to find qualified resources who can do this effectively without significant cost.

share|improve this question

closed as off topic by Bruno Pereira, Lekensteyn, jrg Feb 25 '12 at 1:31

Questions on Ask Ubuntu are expected to relate to Ubuntu within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
This is a whole heap of questions in one.. To answer your title question. Goto StackOverflow.com, to answer "what is the best approach" you should probably goto programmers.SE... I just don't see what is Ubuntu specific other than "i'm building this app for Ubuntu, how?" –  rlemon Feb 6 '12 at 14:26
    
This is more like a job spec/advertisement than a factual Q&A (as this site is intended for) :) I agree, split it up into small chunks upon which we can give concise answers. –  Caesium Feb 6 '12 at 14:43
1  
I knew that I was potentially treading on shakey ground asking the question here... however, I appreciate the feedback and now I can chunk it up properly. –  Kendor Feb 6 '12 at 15:20

1 Answer 1

You can parse commands from commandline with the following syntax:

mysql -u {user} -p{password} {database} <{text.file}

This will parse all the lines from {text.file} to {database}. The text file needs to contain valid MySQL instructions and the password is shown plain text in ps so you might want to stick that in my.cnf (security comes first).

2 methods...

  • You can use the MySQL command load data (see link for more options):

    LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE {text2.file} INTO TABLE {table}

    inside the text.file to insert a text file into a table. This probably overwrites exisiting lines so you would need to create new files everytime. LOAD DATA is very very fast. Shorter way (one-liner from comment by Caesium):

    mysql -u {user} -p {pass} -e "LOAD DATA {rest of command}" {db}
    
  • You can put lots of INSERT INTO's into the text.file. This is slower than a LOAD DATA but could be easier to implement.

share|improve this answer
1  
Just as a slightly cleaner way of doing the LOAD DATA, you could use the -e switch to mysql rather than pipe in an otherwise useless textfile. So mysql -u {user} -p {pass} -e "LOAD DATA .." {db} –  Caesium Feb 6 '12 at 14:49
    
Ill edit it in :D –  Rinzwind Feb 6 '12 at 14:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.