LinuxQuestions.org
Review your favorite Linux distribution.
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 05-14-2014, 10:26 AM   #1
tripialos
Member
 
Registered: Apr 2012
Posts: 169

Rep: Reputation: Disabled
Question mysql insert to table bash script


Greetings

I am trying to create a shell script that will automatically insert to mysql test table about 100 rows.

i have a file which contains the data i wish to add and i made a script however it doesnt seem to work.

My script:

Code:
#!/bin/bash

cat /root/mysql/list.list | while read F
 do
        _name= $(echo $F | cut -d\| -f1)
        _lastname= echo $F | cut -d\| -f2
        _address= echo $F | cut -d\| -f3
        _tel= echo $F | cut -d\| -f4

        echo $F
        echo $_lastname
        echo $_address
        echo $_tel

echo "insert into testable (name,lastname,address,telephone) values $_name , '$_lastname', '$_address' , '$_tel'  ;"  #| mysql -uroot -proot

done
When i execute the script i get the below results

Code:
root@server:~/mysql#/insertMysql.sh
./insertMysql.sh.alterve: line 5: Shad: command not found
Morris
Buizingen
032-836-9436
Shad|Morris|Buizingen|032-836-9436



insert into testable (name,lastname,address,telephone) values   , '', '' , ''  ;
For some reason my variables are empty. On the _name variable i modified the code so it can actualy store the result of the command but as you can see the shell tries to execute it.

What am i missing ?


note1: i have commented out the mysql insert command (since the script is not working)in order to avoid the mysql error messages

note2: the specific file contains only one entry just for testing my script. Those names and numbers are random data generated by online random generation engines.

Thanks
 
Old 05-14-2014, 10:35 AM   #2
szboardstretcher
Senior Member
 
Registered: Aug 2006
Location: Detroit, MI
Distribution: GNU/Linux systemd
Posts: 4,237

Rep: Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655
I don't like using echo, perhaps EOF heredoc would work?

Code:
mysql -uroot -proot SOMEDATABASE << EOF
insert into TABLENAME (name,lastname,address,telephone) values $_name , '$_lastname', '$_address' , '$_tel';
EOF
 
Old 05-14-2014, 11:14 AM   #3
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fedora-30
Posts: 5,289

Rep: Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916
the single-quotes (') should cause it to literally print the variable name instead of the variable value. try escaping them.
 
Old 05-14-2014, 11:38 AM   #4
szboardstretcher
Senior Member
 
Registered: Aug 2006
Location: Detroit, MI
Distribution: GNU/Linux systemd
Posts: 4,237

Rep: Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655
Nah. It's fine. I had to clean up his original statement, but it works:

Creating the test database:
Code:
CREATE DATABASE SOMEDATABASE;
USE SOMEDATABASE;
CREATE TABLE TABLENAME (name VARCHAR(20), lastname VARCHAR(20), address VARCHAR(20), telephone VARCHAR(20));
DESCRIBE TABLENAME;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(20) | YES  |     | NULL    |       |
| lastname  | varchar(20) | YES  |     | NULL    |       |
| address   | varchar(20) | YES  |     | NULL    |       |
| telephone | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Creating the script:
Code:
#!/bin/bash -x
_name="sz"
_lastname="boardstretcher"
_address="usa"
_tel=411

mysql SOMEDATABASE << EOF
INSERT INTO TABLENAME (name,lastname,address,telephone) VALUES ('$_name', '$_lastname', '$_address', '$_tel');
EOF
Results of script in database table;
Code:
SELECT * FROM TABLENAME;
+------+----------------+---------+-----------+
| name | lastname       | address | telephone |
+------+----------------+---------+-----------+
| sz   | boardstretcher | usa     | 411       |
+------+----------------+---------+-----------+
1 row in set (0.00 sec)
 
1 members found this post helpful.
Old 05-14-2014, 11:48 AM   #5
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fedora-30
Posts: 5,289

Rep: Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916
i guess here docs dont require one to escape characters:
Code:
schneidz@xbmc:~$ cat sz.ksh
#!/bin/bash
_name="sz"
_lastname="boardstretcher"
_address="usa"
_tel=411

echo INSERT INTO TABLENAME \(name,lastname,address,telephone\) VALUES \('$_name', '$_lastname', '$_address', '$_tel'\);
echo INSERT INTO TABLENAME \(name,lastname,address,telephone\) VALUES \(\'$_name\', \'$_lastname\', \'$_address\', \'$_tel\'\);

schneidz@xbmc:~$ ./sz.ksh
INSERT INTO TABLENAME (name,lastname,address,telephone) VALUES ($_name, $_lastname, $_address, $_tel)
INSERT INTO TABLENAME (name,lastname,address,telephone) VALUES ('sz', 'boardstretcher', 'usa', '411')
 
Old 05-14-2014, 11:54 AM   #6
szboardstretcher
Senior Member
 
Registered: Aug 2006
Location: Detroit, MI
Distribution: GNU/Linux systemd
Posts: 4,237

Rep: Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655Reputation: 1655
If you want that functionality, you can put quotes around the heredoc word (EOF here), it will not do any expansion/replacement. Such as:

Code:
mysql SOMEDATABASE << "EOF"
INSERT INTO TABLENAME (name,lastname,address,telephone) VALUES ('$_name', '$_lastname', '$_address', '$_tel');
EOF
So now everything in the heredoc is 'literal' and not expanded or replaced.
 
Old 05-14-2014, 03:06 PM   #7
tripialos
Member
 
Registered: Apr 2012
Posts: 169

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by szboardstretcher View Post
Nah. It's fine. I had to clean up his original statement, but it works:

Creating the test database:
Code:
CREATE DATABASE SOMEDATABASE;
USE SOMEDATABASE;
CREATE TABLE TABLENAME (name VARCHAR(20), lastname VARCHAR(20), address VARCHAR(20), telephone VARCHAR(20));
DESCRIBE TABLENAME;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(20) | YES  |     | NULL    |       |
| lastname  | varchar(20) | YES  |     | NULL    |       |
| address   | varchar(20) | YES  |     | NULL    |       |
| telephone | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Creating the script:
Code:
#!/bin/bash -x
_name="sz"
_lastname="boardstretcher"
_address="usa"
_tel=411

mysql SOMEDATABASE << EOF
INSERT INTO TABLENAME (name,lastname,address,telephone) VALUES ('$_name', '$_lastname', '$_address', '$_tel');
EOF
Results of script in database table;
Code:
SELECT * FROM TABLENAME;
+------+----------------+---------+-----------+
| name | lastname       | address | telephone |
+------+----------------+---------+-----------+
| sz   | boardstretcher | usa     | 411       |
+------+----------------+---------+-----------+
1 row in set (0.00 sec)

Thanks everyone for your replays, however, all your examples use "hardcocded" values in the variables. In my script, the values in the variables are loaded from a file which are dynamic.

My problem is not that mysql statement not been parsed properly, my peoblwm is that my variables are empty. If you noticed, i echo each variable prior executing the mysql command and the actual output of echoing the variables is empty.

indeed you will see the variables echoed but this is happpening not because of my echo is happening in the part where i declare the variables.

To illustrate what i am trying to say:

OUTPUT OF SCRIPT
Code:
./insertMysql.sh.alterve: line 5: Shad: command not found  --->  resulted by this statement _name= $(echo $F | cut -d\| -f1)
Morris                      --->         _lastname= echo $F | cut -d\| -f2
Buizingen                   --->         _address= echo $F | cut -d\| -f3
032-836-9436                --->         _tel= echo $F | cut -d\| -f4
Shad|Morris|Buizingen|032-836-9436  --->  echo $F (this is the actual line readed from the cat )

empty line                  --->         echo $_lastname
empty line                  --->         echo $_address
emoty line                  --->        echo $_tel
why are my variables empty
also why in the variable _name=$(echo $F | cut -d\| -f1), which i use the method to store the result of a command to the variable, the script returns me shad command not dound? It takes the result and instead of storing it it tries to execute it as a command...?

what am i missing here..? i am very curious..

:-s

Last edited by tripialos; 05-14-2014 at 03:08 PM.
 
Old 05-14-2014, 03:13 PM   #8
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fedora-30
Posts: 5,289

Rep: Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916
what confuses me first is that in your first post you are running this script:
/insertMysql.sh

yet the system is complaining about an error on this script:
./insertMysql.sh.alterve

(copy-pasta error ?)

in the first post the variables $F $_lastname $_address $_tel are echoing.

it would help is you supplied a few lines of your input.

Last edited by schneidz; 05-14-2014 at 03:16 PM.
 
Old 05-14-2014, 03:48 PM   #9
tripialos
Member
 
Registered: Apr 2012
Posts: 169

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by schneidz View Post
what confuses me first is that in your first post you are running this script:
/insertMysql.sh

yet the system is complaining about an error on this script:
./insertMysql.sh.alterve

(copy-pasta error ?)

in the first post the variables $F $_lastname $_address $_tel are echoing.

it would help is you supplied a few lines of your input.
Yes that was a coppy paste error. Here is the exact output of the script:

Code:
root@server:~/mysql#
root@server:~/mysql#
root@server:~/mysql# cat list.list
Shad|Morris|Buizingen|032-836-9436
root@server:~/mysql#
root@server:~/mysql#
root@server:~/mysql# ./insertMysql.sh
./inserMysql.sh: line 5: Shad: command not found
Morris
Buizingen
032-836-9436
Shad|Morris|Buizingen|032-836-9436



insert into testable (name,lastname,address,telephone) values   , '', '' , ''  ;
root@serevr:~/mysql#
root@serevr:~/mysql#
root@serevr:~/mysql#
If you noticed the blank lines, these are created from the echoes i added on my script prior executing the mysql command in order to clarify that the values are stored on the variables. So as it seems, the variables are empty hence the blank lines..
Could this be caused of the fact that i am using Macbook?

Last edited by tripialos; 05-14-2014 at 03:51 PM.
 
Old 05-15-2014, 11:21 AM   #10
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fedora-30
Posts: 5,289

Rep: Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916Reputation: 916
Quote:
Originally Posted by tripialos View Post
...
Could this be caused of the fact that i am using Macbook?
i dont think so so long as you are running some linux distro -- which distro ? (or are you saying you are running this on osx )?

for us to debug it would be nice to see you post your script (without any typos so we arent chasing ghosts).

Last edited by schneidz; 05-15-2014 at 11:23 AM.
 
Old 05-15-2014, 01:02 PM   #11
michaelk
Moderator
 
Registered: Aug 2002
Posts: 20,437

Rep: Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560
Quote:
_name= $(echo $F | cut -d\| -f1)
_lastname= echo $F | cut -d\| -f2
Look at your syntax for the two lines. _name= $(echo $F | cut -d\| -f1) = $(Shad) -> bash tries to execute the command $(Shad) which does not exist and therefore the no command found error.
 
Old 05-15-2014, 01:35 PM   #12
tripialos
Member
 
Registered: Apr 2012
Posts: 169

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by schneidz View Post
i dont think so so long as you are running some linux distro -- which distro ? (or are you saying you are running this on osx )?

for us to debug it would be nice to see you post your script (without any typos so we arent chasing ghosts).
No i am running this on ubuntu server 12.04 minimal. The script can be found in my first post. This is the exact copy of the script. I changed the feeding of the loop from cating the file to redirecting STDIN. Nevertheless here is the script:

Code:
#!/bin/bash

 while read F
 do
	_name= $(echo $F | cut -d\| -f1)
	_lastname= echo $F | cut -d\| -f2
	_address= echo $F | cut -d\| -f3
	_tel= echo $F | cut -d\| -f4

	echo $F
	echo $_lastname
	echo $_address
	echo $_tel

  echo "insert into testable (name,lastname,address,telephone) values $uuname  , '$_lastname', '$_address' , '$_tel'  ;"  #| mysql -uroot -proot

done < /root/mysql/list.list

Last edited by tripialos; 05-15-2014 at 01:44 PM.
 
Old 05-15-2014, 01:39 PM   #13
tripialos
Member
 
Registered: Apr 2012
Posts: 169

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by michaelk View Post
Look at your syntax for the two lines. _name= $(echo $F | cut -d\| -f1) = $(Shad) -> bash tries to execute the command $(Shad) which does not exist and therefore the no command found error.
The reason the first variable (_name) sytax is as souch, is because i wanned to show that this should have stored the RESULT of the command. If not mistaken, if you wish to store the result of a command to a variable you can do this with two methods:

Code:
var=$(command-name-here)
    or 
var=`command-name-here`

hence why i have this systax, shouldnt _name= $(echo $F | cut -d\| -f1) = Shad ???
 
Old 05-15-2014, 02:11 PM   #14
michaelk
Moderator
 
Registered: Aug 2002
Posts: 20,437

Rep: Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560Reputation: 3560
This works
Quote:
_lastname= echo $F | cut -d\| -f2
not this
Quote:
_name= $(echo $F | cut -d\| -f1)
What do you think is the difference?

what happens when you try this:
_name= echo $F | cut -d\| -f1

Last edited by michaelk; 05-15-2014 at 02:15 PM.
 
Old 05-15-2014, 02:33 PM   #15
tripialos
Member
 
Registered: Apr 2012
Posts: 169

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by michaelk View Post
This works
not this

What do you think is the difference?

what happens when you try this:
_name= echo $F | cut -d\| -f1
yes you are right.

But my problem is still not solved! It doesnt echo my variables. Dont get confused with the first echoes of the output. This are not the actual "echo $variable" this are resulted by the "_variable=echo blah blah". To present it even better, pleae study the below output:

Code:
root@chefnode:~/mysql# cat insermysql.sh
#!/bin/bash

 while read F
 do
	_name= echo $F | cut -d\| -f1
	_lastname= echo $F | cut -d\| -f2
	_address= echo $F | cut -d\| -f3
	_tel= echo $F | cut -d\| -f4

	echo $F
	echo $_lastname
	echo $_lastname
	echo $_lastname
	echo $_lastname
	echo $_address
	echo $_address
	echo $_address
	echo $_address
	echo $_tel
	echo $_tel
	echo $_tel
	echo $_tel

echo "insert into testable (name,lastname,address,telephone) values ($uuname  , '$_lastname', '$_address' , '$_tel')  ;"  #| mysql -uroot -proot

done < /root/mysql/list.list
root@chefnode:~/mysql#
root@chefnode:~/mysql#
root@chefnode:~/mysql# ./insermysql.sh
Shad
Morris
Buizingen
032-836-9436
Shad|Morris|Buizingen|032-836-9436
      











insert into testable (name,lastname,address,telephone) values (  , '', '' , '')  ;
root@chefnode:~/mysql#
root@chefnode:~/mysql#
root@chefnode:~/mysql#

Noticed the empty lines? These should have return the values...
These lines are produced by the echo $_name statements, which indicates that the variable values are empty.
 
  


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
Insert into FOREIGN KEY field in a MySQL table rootaccess Linux - Newbie 2 06-12-2012 04:26 PM
How do I INSERT IGNORE the current date into a table in MYSQL? resetreset Programming 9 12-15-2008 01:39 AM
mysql insert using bash script venki Linux - General 3 07-07-2007 04:52 AM
Howto insert a pdf document into a mysql table jadewarrior Linux - Server 4 02-23-2007 05:18 AM
Mysql - Howto insert 'admin' account into table ??? b:z Linux - General 6 03-29-2005 03:44 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - General

All times are GMT -5. The time now is 02:03 PM.

Main Menu
Advertisement
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
Open Source Consulting | Domain Registration