LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 03-24-2004, 04:52 PM   #1
chrisk5527
Member
 
Registered: Oct 2002
Location: Michigan
Distribution: Slackware Linux 10.0
Posts: 289

Rep: Reputation: 30
Import records to MySQL Database from a shell script


How can I INSERT values into a MySQL database? I tried

INSERT INTO TableName (ID_TAG, PDF_NAME, DATE_IMPORTED) VALUES('0', $PDF_NAME, $IMPORT_DATE);

Do I have to do something special with the variables? I know thats my problem, but I'm not sure how to interpolate them. I've read the documentation on MySQL's site about MS-DOS batch mode interacting with MySQL but no shell scripting documentnation.
 
Old 03-24-2004, 06:11 PM   #2
phek
Member
 
Registered: Jul 2001
Location: California, US
Distribution: Slackware
Posts: 196

Rep: Reputation: 30
you need quotes around those variables if they're strings.
 
Old 03-24-2004, 07:36 PM   #3
chrisk5527
Member
 
Registered: Oct 2002
Location: Michigan
Distribution: Slackware Linux 10.0
Posts: 289

Original Poster
Rep: Reputation: 30
Tried it, it doesnt work.
 
Old 03-24-2004, 07:50 PM   #4
phek
Member
 
Registered: Jul 2001
Location: California, US
Distribution: Slackware
Posts: 196

Rep: Reputation: 30
well there should be a space between VALUES and ( and probally no quotes around 0 (its an integer not a string) and make sure the quotes you put around the variables are double quotes.
 
Old 03-24-2004, 08:02 PM   #5
chrisk5527
Member
 
Registered: Oct 2002
Location: Michigan
Distribution: Slackware Linux 10.0
Posts: 289

Original Poster
Rep: Reputation: 30
Do you know SQL or are you just guessing what to do? The space between VALUES and () doesnt matter, and there has to be single quotes around 0.
 
Old 03-24-2004, 08:14 PM   #6
phek
Member
 
Registered: Jul 2001
Location: California, US
Distribution: Slackware
Posts: 196

Rep: Reputation: 30
yah i do know sql and i wasn't sure about the space, but no if its an integer, there shouldn't be quotes around the value.

whats the error message anyways?
 
Old 03-24-2004, 08:22 PM   #7
chrisk5527
Member
 
Registered: Oct 2002
Location: Michigan
Distribution: Slackware Linux 10.0
Posts: 289

Original Poster
Rep: Reputation: 30
Theres supposed to quotes around all values when you Insert a new record.
 
Old 03-24-2004, 08:42 PM   #8
phek
Member
 
Registered: Jul 2001
Location: California, US
Distribution: Slackware
Posts: 196

Rep: Reputation: 30
from an example on the mysql insert manual page
http://www.mysql.com/doc/en/INSERT.html

mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
 
Old 03-24-2004, 08:58 PM   #9
chrisk5527
Member
 
Registered: Oct 2002
Location: Michigan
Distribution: Slackware Linux 10.0
Posts: 289

Original Poster
Rep: Reputation: 30
I guess both methods work then. I use MySQL Front instead of using MySQL's shell and and it outputs all the commands issued to a log windows. So both methods are correct.
 
Old 03-24-2004, 09:02 PM   #10
phek
Member
 
Registered: Jul 2001
Location: California, US
Distribution: Slackware
Posts: 196

Rep: Reputation: 30
what kind of variables are those anyways? because if they're supposed to be mysql variables, they should be @, not $. And again, what's the error message?
 
Old 03-24-2004, 09:19 PM   #11
chrisk5527
Member
 
Registered: Oct 2002
Location: Michigan
Distribution: Slackware Linux 10.0
Posts: 289

Original Poster
Rep: Reputation: 30
Well what I'm trying to do is loop through a directory with a shell script in Linux and get the attributes of the file. These attributes can be anything from Import Date, Filename, File size, etc. Once I read that information I stick it into a variable; for example:

$CURDIR="/var/log"
for $file in $CURDIR
do
$FILENAME=`ls -l $file | awk '{print $9}'`
done

mysql -u root -p < commands

---
Commands file
----
INSERT INTO TableName (ID_TAG, PDF_NAME, DATE_IMPORTED) VALUES('0', $PDF_NAME, $IMPORT_DATE);



See what I'm getting at now?
 
Old 03-24-2004, 09:41 PM   #12
phek
Member
 
Registered: Jul 2001
Location: California, US
Distribution: Slackware
Posts: 196

Rep: Reputation: 30
Heh, where to begin...

1) when declaring variables in bash, you don't include the $ so
$CURDIR="/var/log"
should be
CURDIR="/var/log"

2) that for loop wont work for what you want it to do, it'll just do 1 iteration with $file being /var/log (not to mention you need to leave the $ off $file when you intialize the loop). what your looking for is something more like
for file in `find $CURDIR -maxdepth 1 -type f`

3) when inputing from stdin file descriptor, its not going to convert variables. so maybe your mysql line should look more like this
mysql -u root -p password <(INSERT INTO TableName (ID_TAG, PDF_NAME, DATE_IMPORTED) VALUES('0', "$PDF_NAME", "$IMPORT_DATE")

though I'm not positive even that would work, you may want to escape the inner mysql paranthasese.
 
Old 03-24-2004, 09:49 PM   #13
chrisk5527
Member
 
Registered: Oct 2002
Location: Michigan
Distribution: Slackware Linux 10.0
Posts: 289

Original Poster
Rep: Reputation: 30
Sorry about those syntax errors, I was trying to give an example real quick. I just wanted you to get the idea of what I was trying to accomplish. But thanks alot for your help.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database connection using Shell Script sumitarun Linux - Newbie 2 05-09-2010 12:18 PM
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 09:45 AM
Import Database Function Script Using Perl?? domquem Programming 3 01-13-2005 09:05 AM
Exporting MySQL error when trying to Import to another database. strez4prez Linux - Software 0 05-28-2004 11:43 AM
Restoring MYSQL database without SSH shell access hct224 Linux - Newbie 5 01-08-2004 06:14 AM


All times are GMT -5. The time now is 02:52 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration