Shell Script to insert value in database

deepu_linux · 09-01-2008, 01:25 AM

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, 01: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, 01: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, 01:57 AM

Quote:

Originally Posted by deepu_linux

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, 02: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, 03: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, 02:44 PM

Quote:

Originally Posted by matthewg42

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, 08:21 PM

Please show your code & full error msg.

Translucer · 12-18-2009, 09:10 AM

Quote:

Originally Posted by chrism01

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, 07: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, 08:55 AM

Quote:

Originally Posted by AnanthaP

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, 02:18 PM

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, 04:05 PM

Quote:

Originally Posted by deepu_linux

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, 10:43 AM

Quote:

Originally Posted by matthewg42

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, 12:07 PM

Quote:

Originally Posted by samiaeg

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.