LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   Shell Script to insert value in database (http://www.linuxquestions.org/questions/programming-9/shell-script-to-insert-value-in-database-666785/)

deepu_linux 09-01-2008 02:25 AM

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 ?

matthewg42 09-01-2008 02:38 AM

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.

deepu_linux 09-01-2008 02:47 AM

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...

matthewg42 09-01-2008 02:57 AM

Quote:

Originally Posted by deepu_linux (Post 3265820)
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


deepu_linux 09-01-2008 03:08 AM

Thanks...

Let me try this..

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

mrcheeks 09-01-2008 04:12 AM

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.

Translucer 12-17-2009 03:44 PM

Quote:

Originally Posted by matthewg42 (Post 3265825)
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

chrism01 12-17-2009 09:21 PM

Please show your code & full error msg.

Translucer 12-18-2009 10:10 AM

Quote:

Originally Posted by chrism01 (Post 3796108)
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

AnanthaP 12-20-2009 08:37 AM

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

Translucer 12-21-2009 09:55 AM

Quote:

Originally Posted by AnanthaP (Post 3798844)
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 :-)

bloatys 04-24-2013 03:18 PM

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

Gener@l 04-24-2013 05:05 PM

Quote:

Originally Posted by deepu_linux (Post 3265802)
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


samiaeg 04-25-2013 11:43 AM

Quote:

Originally Posted by matthewg42 (Post 3265825)
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

Gener@l 04-25-2013 01:07 PM

Quote:

Originally Posted by samiaeg (Post 4938729)
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.


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