LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 09-01-2008, 01:25 AM   #1
deepu_linux
LQ Newbie
 
Registered: Sep 2008
Location: India
Posts: 9

Rep: Reputation: 0
Shell Script to insert value in database


Hi,


I wanted to insert a row in database using shell script.. (I do use HSQL and Oracle databases).

How do I write my shell script .. ? How to specify the database details ?
 
Old 09-01-2008, 01:38 AM   #2
matthewg42
Senior Member
 
Registered: Oct 2003
Location: UK
Distribution: Kubuntu 12.10 (using awesome wm though)
Posts: 3,530

Rep: Reputation: 63
Create a .sql file with the appropriate insert statement.

use sqlplus to run the file, i.e.

Code:
sqlplus -s user/pass@database @ file.sql
I would also re-direct the output (stdout and stderr) to a file and then grep that or error messages (i.e. check for "ORA-").

Bear in mind this will reveal your database password in the process list on the computer, so it is a bad solution from a security standpoint.

A better solution would be to use Perl/DBI to do the whole thing. If you want to do that, google for a Perl/DBI tutorial.

Last edited by matthewg42; 09-01-2008 at 01:44 AM.
 
Old 09-01-2008, 01:47 AM   #3
deepu_linux
LQ Newbie
 
Registered: Sep 2008
Location: India
Posts: 9

Original Poster
Rep: Reputation: 0
But is it not possible to achieve this using a shell script..?

Cos my application has to be used by a Tester and he wants every time to enter the value in database...
 
Old 09-01-2008, 01:57 AM   #4
matthewg42
Senior Member
 
Registered: Oct 2003
Location: UK
Distribution: Kubuntu 12.10 (using awesome wm though)
Posts: 3,530

Rep: Reputation: 63
Quote:
Originally Posted by deepu_linux View Post
But is it not possible to achieve this using a shell script..?

Cos my application has to be used by a Tester and he wants every time to enter the value in database...
Well, there is no shell API for inserting directly into the database, so you need to use some tool or other. sqlplus is such a tool which works with oracle. I don't know about HSQL - I would assume there is some analogue to sqlplus.

When I say "create an .sql file", you can do that in a script. You didn't say what data needs to be entered, so here's a pretty abstract example:
Code:
#!/bin/bash

read -p "enter value for field1> " field1
read -p "enter value for field2> " field2
read -p "enter value for field3> " field3

cat <<EOD > temp.sql
INSERT INTO mytable
VALUES ('$field1', '$field2', '$field3'); 

commit

quit
EOD

sqlplus -s user/pass@database @ temp.sql

rm -f temp.sql
 
Old 09-01-2008, 02:08 AM   #5
deepu_linux
LQ Newbie
 
Registered: Sep 2008
Location: India
Posts: 9

Original Poster
Rep: Reputation: 0
Thanks...

Let me try this..

btw.. HSQL is a file/memory based developed using Java.. And mainly help developers with easy debug...

Last edited by deepu_linux; 09-01-2008 at 02:11 AM.
 
Old 09-01-2008, 03:12 AM   #6
mrcheeks
Senior Member
 
Registered: Mar 2004
Location: far enough
Distribution: OS X 10.6.7
Posts: 1,690

Rep: Reputation: 50
There are many scripting engines for Java, you can use one, open a connection to a database and do what you have to do.
Google for JDBC and Groovy, Jython, BSH, Rhino, JRuby, etc.
 
Old 12-17-2009, 02:44 PM   #7
Translucer
LQ Newbie
 
Registered: Dec 2009
Posts: 3

Rep: Reputation: 0
Quote:
Originally Posted by matthewg42 View Post
Well, there is no shell API for inserting directly into the database, so you need to use some tool or other. sqlplus is such a tool which works with oracle. I don't know about HSQL - I would assume there is some analogue to sqlplus.

When I say "create an .sql file", you can do that in a script. You didn't say what data needs to be entered, so here's a pretty abstract example:
Code:
#!/bin/bash

read -p "enter value for field1> " field1
read -p "enter value for field2> " field2
read -p "enter value for field3> " field3

cat <<EOD > temp.sql
INSERT INTO mytable
VALUES ('$field1', '$field2', '$field3'); 

commit

quit
EOD

sqlplus -s user/pass@database @ temp.sql

rm -f temp.sql
---------------------

Hi Matthew,

I am new to shell scripting and PL SQL. I tried executing your script. I am encountering an error as ': not a valid identifier

Please suggest me what to be done.

Thanks
 
Old 12-17-2009, 08:21 PM   #8
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,261

Rep: Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028
Please show your code & full error msg.
 
Old 12-18-2009, 09:10 AM   #9
Translucer
LQ Newbie
 
Registered: Dec 2009
Posts: 3

Rep: Reputation: 0
Quote:
Originally Posted by chrism01 View Post
Please show your code & full error msg.
Hi Chris,

Below is my code:

read -p "enter value for c1 " c1
read -p "enter value for c2 " c2
read -p "enter value for c3 " c3

cat <<EOD > temp.sql

INSERT INTO emp VALUES ('$c1', '$c2', '$c3');

commit

quit
EOD

sqlplus -s pimco/pimco@pimdev @temp.sql

rm -f temp.sql
----------------------------------------

Error is:


/usr/home/kiran> ./temp.sql
enter value for c1 test1
': not a valid identifier
enter value for c2 test2
': not a valid identifier
enter value for c3 test3
': not a valid identifier
: command not found
---------------------------------------

Please help me

Thanks
Kiran
 
Old 12-20-2009, 07:37 AM   #10
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Distribution: UBUNTU 5.10 since Jul-18,2006 on Intel 820 DC
Posts: 621

Rep: Reputation: 136Reputation: 136
Kiran,

temp.sql is created within a separate bash shell script file (say "temp_runner.shl").

So temp_runner.shl contains the code that mathewg42 suggested.

It is as below:
Quote:
#!/bin/bash

read -p "enter value for c1 " c1
read -p "enter value for c2 " c2
read -p "enter value for c3 " c3

cat <<EOD > temp.sql

INSERT INTO emp VALUES ('$c1', '$c2', '$c3');

commit

quit
EOD

sqlplus -s pimco/pimco@pimdev @temp.sql

rm -f temp.sql
Once you create it (temp_runner.shl), test it out and give it to your tester.

Hope this clariufies the error.

End
 
Old 12-21-2009, 08:55 AM   #11
Translucer
LQ Newbie
 
Registered: Dec 2009
Posts: 3

Rep: Reputation: 0
Quote:
Originally Posted by AnanthaP View Post
Kiran,

temp.sql is created within a separate bash shell script file (say "temp_runner.shl").

So temp_runner.shl contains the code that mathewg42 suggested.

It is as below:
Once you create it (temp_runner.shl), test it out and give it to your tester.

Hope this clariufies the error.

End
---------------------------------

Thank you Anantha...It worked :-)
 
Old 04-24-2013, 02:18 PM   #12
bloatys
LQ Newbie
 
Registered: Apr 2013
Posts: 1

Rep: Reputation: Disabled
hide password

i know it's been a long time since this post, but for what it's worth....

you can use an oracle wallet to hide the password
 
Old 04-24-2013, 04:05 PM   #13
Gener@l
LQ Newbie
 
Registered: Sep 2011
Posts: 11

Rep: Reputation: Disabled
Quote:
Originally Posted by deepu_linux View Post
Hi,


I wanted to insert a row in database using shell script.. (I do use HSQL and Oracle databases).

How do I write my shell script .. ? How to specify the database details ?
you can do this from a shell script. You do not have to create and remove a .sql script within the bash script.

Code:
sqlplus -s user/pass@database <<-EOF
INSERT INTO emp VALUES ('$c1', '$c2', '$c3');

commit
EOF

Last edited by Gener@l; 04-24-2013 at 04:06 PM.
 
Old 04-25-2013, 10:43 AM   #14
samiaeg
LQ Newbie
 
Registered: Apr 2013
Posts: 2

Rep: Reputation: 0
Quote:
Originally Posted by matthewg42 View Post
Well, there is no shell API for inserting directly into the database, so you need to use some tool or other. sqlplus is such a tool which works with oracle. I don't know about HSQL - I would assume there is some analogue to sqlplus.

When I say "create an .sql file", you can do that in a script. You didn't say what data needs to be entered, so here's a pretty abstract example:
Code:
#!/bin/bash

read -p "enter value for field1> " field1
read -p "enter value for field2> " field2
read -p "enter value for field3> " field3

cat <<EOD > temp.sql
INSERT INTO mytable
VALUES ('$field1', '$field2', '$field3'); 

commit

quit
EOD

sqlplus -s user/pass@database @ temp.sql

rm -f temp.sql
nice explanation
 
Old 04-25-2013, 12:07 PM   #15
Gener@l
LQ Newbie
 
Registered: Sep 2011
Posts: 11

Rep: Reputation: Disabled
Quote:
Originally Posted by samiaeg View Post
nice explanation

But again, that can be done without creating an sql script outside of the bash script to be called.
Code:
#!/bin/bash

read -p "enter value for field1> " field1
read -p "enter value for field2> " field2
read -p "enter value for field3> " field3

sqlplus -s user/pass@database <<-EOF
INSERT INTO emp VALUES ('$c1', '$c2', '$c3');

commit
EOF

replace the sql script creation and deletion with the above and you are done. The - in front of EOF ignores white space if you indent your sql commands.
 
  


Reply

Tags
script, shell


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 Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SHELL Script to insert a line. santhoshv Programming 9 04-03-2008 06:10 AM
SHELL Script to insert lines after certain Intervals rahulruns Linux - General 9 02-26-2008 01:21 AM
shell script: insert line in a file noir911 Programming 6 02-04-2008 10:42 PM
Shell Script: want to insert values in database when update script runs ring Programming 2 10-25-2007 10:48 PM
insert lines in a function using shell script shyamdey Programming 1 08-30-2006 07:48 AM


All times are GMT -5. The time now is 06:37 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