Register
It is currently Fri Jul 21, 2017 4:37 am

need help with script for updating database

Board index » The Scripts » Requests

All times are UTC - 6 hours


Post new topic Reply to topic  Page 1 of 1
  [ 11 posts ] 
  Print view Previous topic | Next topic 
Author Message
moose
 PostPosted: Fri Jan 21, 2011 9:29 am   

Joined: Thu Sep 16, 2010 3:57 am
Posts: 11
Hi!
I have such a file

cat /tmp/che
Code:
ALTER TABLE builds ADD COLUMN `map_x2` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE builds ADD COLUMN `map_y2` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE builds ADD COLUMN `map_x3` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE builds ADD COLUMN `map_y3` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE builds ADD COLUMN `map_x4` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';


I would like to make my script to insert every line to mysql database
the example of code is:

Code:
#!/usr/local/bin/bash
...........
for ADD_CHENGES in $(cat /tmp/che);
do
mysql -D test1 mysql -D test1 -Bse "$ADD_CHENGES;"
done


The problem is that my script does not add a row, it adds each word, - it using the separator "space" instead ";"

Could someone help me with this.
Thanks
Top
 Profile  
Patsie
 PostPosted: Fri Jan 21, 2011 12:41 pm   
User avatar

Joined: Sun Jun 27, 2010 12:57 am
Posts: 192
Code:
mysql test1 < /tmp/che

That should apply all SQL commands in /tmp/che to the test1 database.
No need for fancy scripts
Top
 Profile  
crouse
 PostPosted: Fri Jan 21, 2011 1:26 pm   
Site Admin
User avatar

Joined: Sun May 15, 2005 9:36 pm
Posts: 692
Location: Des Moines, Iowa
Sometimes having it in a script where variables can be put in place, etc can be of use.... so... something like this perhaps....

Code:
#!/bin/bash
/usr/bin/mysql -u root << EOF
ALTER TABLE builds ADD COLUMN `map_x2` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE builds ADD COLUMN `map_y2` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE builds ADD COLUMN `map_x3` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE builds ADD COLUMN `map_y3` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE builds ADD COLUMN `map_x4` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0';
EOF


or more simply something like this...

Code:
#!/bin/bash
query=`cat /tmp/che`
/usr/bin/mysql -u root << EOF
$query
EOF
Top
 Profile WWW  
crouse
 PostPosted: Fri Jan 21, 2011 1:34 pm   
Site Admin
User avatar

Joined: Sun May 15, 2005 9:36 pm
Posts: 692
Location: Des Moines, Iowa
moose wrote:
I would like to make my script to insert every line to mysql database
the example of code is:

Code:
#!/usr/local/bin/bash
...........
for ADD_CHENGES in $(cat /tmp/che);
do
mysql -D test1 mysql -D test1 -Bse "$ADD_CHENGES;"
done


The problem is that my script does not add a row, it adds each word, - it using the separator "space" instead ";"

Could someone help me with this.
Thanks



and as a side note, you could try changing the IFS at the top of your script

IE:
Code:
IFS='
'


that should read your file LINE by LINE instead of word by word... (i haven't tried it that way with your mysql example... I'll leave that to you ;) )
Top
 Profile WWW  
Watael
 PostPosted: Fri Jan 21, 2011 2:34 pm   

Joined: Mon Mar 02, 2009 3:03 am
Posts: 638
Hi,

when you want to parse a file, always use:
Code:
while read line
do ...
done < file
read's IFS environment variable may be adapted to cut line into multiple variables or an array:
Code:
while IFS=',' read first second restOfLine
do ...
done < file
or
Code:
while IFS=',' read -a array
do ...
done < file
Top
 Profile  
moose
 PostPosted: Sun Jan 23, 2011 3:20 pm   

Joined: Thu Sep 16, 2010 3:57 am
Posts: 11
Code:
mysql test1 < /tmp/che

It's not what I need

Code:
query=`cat /tmp/che`
mysql -D test1 << EOF
$query
EOF


It's not works too like I need

Code:
IFS='
'

and this too

I forgot to say that I need my script to insert anevery query separately.
because if such a row there is in a script, I will receive an error like this, and script stops with an error,
Code:
ERROR 1060 (42S21) at line 1: Duplicate column name 'map_x2'

and other rows also will not inserted
I think I need to use awk to delimitate every row, I tried different variants but it's not works


Last edited by moose on Sun Jan 23, 2011 5:07 pm, edited 1 time in total.

Top
 Profile  
moose
 PostPosted: Sun Jan 23, 2011 4:59 pm   

Joined: Thu Sep 16, 2010 3:57 am
Posts: 11
Finally with your help, I found a solution of the problem
I had to merge
Code:
IFS=';'
and
Code:
while read line
in the script.

So my script looks like this
Code:
....
IFS=';'
while read line
do
mysql -D test1  -Bse $line
done < /tmp/che


Thank you all
Top
 Profile  
crouse
 PostPosted: Sun Jan 23, 2011 5:20 pm   
Site Admin
User avatar

Joined: Sun May 15, 2005 9:36 pm
Posts: 692
Location: Des Moines, Iowa
Excellent...glad you got it sorted out :)
Top
 Profile WWW  
Watael
 PostPosted: Sun Jan 23, 2011 7:50 pm   

Joined: Mon Mar 02, 2009 3:03 am
Posts: 638
beware
there you change IFS for all the rest of your script:
Code:
IFS=';'
while read line
do
mysql -D test1  -Bse $line
done < /tmp/che

here, you change IFS only for the read command in each loop:
Code:
while IFS=';' read line
do
mysql -D test1  -Bse $line
done < /tmp/che
Top
 Profile  
moose
 PostPosted: Mon Jan 24, 2011 6:06 am   

Joined: Thu Sep 16, 2010 3:57 am
Posts: 11
Thanks for notice )
Top
 Profile  
crouse
 PostPosted: Mon Jan 24, 2011 8:56 am   
Site Admin
User avatar

Joined: Sun May 15, 2005 9:36 pm
Posts: 692
Location: Des Moines, Iowa
8>:) 8>:)
Watael wrote:
beware
there you change IFS for all the rest of your script:
Code:
IFS=';'
while read line
do
mysql -D test1  -Bse $line
done < /tmp/che

here, you change IFS only for the read command in each loop:
Code:
while IFS=';' read line
do
mysql -D test1  -Bse $line
done < /tmp/che


Yeah, I knew that too (that changing the IFS changes it for your entire script). But I hadn't tried it with your method. Thats a neat trick... I usually just copy the current IFS into another var, then move it back after the part of the script i needed it in has run, but I like your way.... pretty cool :)
Top
 Profile WWW  
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 1
  [ 11 posts ] 

Board index » The Scripts » Requests

All times are UTC - 6 hours


Who is online

Users browsing this forum: No registered users and 2 guests

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron


BashScripts | Promote Your Page Too
Powered by phpBB © 2011 phpBB Group
© 2003 - 2011 USA LINUX USERS GROUP