I'm new to Shell scripting, and i need someone to help me with this issue:
I'm trying to do a dynamic update query on Sysbase database table using shell script.
Lets say, the Update query is "update Table set id=X" , where X is dynamic value for the loop index.
If the loop is from 1 to 3, the update query have to be for X=1, 2 and 3.
I have come across such a situation. Following is the code: Please check it
#=======================================================
# Get the Connection String for isql
#=======================================================
connection_str="-S $SERVER -U $SYBASE_USERNAME -D $DATABASE"
isql $connection_str <<EOF | egrep -v "Password:" > $TempFile1
$SYBASE_PASSWORD
{
"Please write your Sybase query in between this middle bracket."
}
go
EOF
Here
$connection_str = This would be used by shell script to connect to database in order to run the query.
$SERVER = Variable which stores the name of the remote server on which the database is installed.
$SYBASE_USERNAME = The username which the script on your behalf to connect to the database.
$DATABASE = The name of the database.
Please revert back in case of any issues.
Go on buddy...Ur prob is solved...
Thanks Johan, but i'm planning to use perl as a Plan B, if i totally screwed up with Shell. "I'll revert to you on this as well once testing".
Hey Vicky :),
Thanks for your response, i want to clear out these points:
1) What is $TempFile1 for, cause once i execute the script, i got this error
"./test3.sh: $TempFile1: ambiguous redirect".
2) Regarding the query, i want to do a loop on update, so teh loop will be shell script on DB update query, so shall i make while or for loop before the bracket, and then inculde the update query in-between !!!
Below is my code that is what i'v written:
#=======================================================
# Get the Connection String for isql
#=======================================================
SERVER=127.0.0.1
SYBASE_USERNAME=sa
DATABASE=mmsdb
SYBASE_PASSWORD=test
connection_str="-S $SERVER -U $SYBASE_USERNAME -D $DATABASE"
isql $connection_str <<EOF | egrep -v "Password:" > $TempFile1
$SYBASE_PASSWORD
{
#"Please write your Sybase query in between this middle bracket."
Insert into TEST values (3,"Test","TestDesc")
go
}
go
EOF
TempFile1: its the name of a temproary file. Suppose if due to some prob the shell script is not able to connect to your database and you want the error in some file...We ve used temp file for this purpose. If you dont specify this den the error will be displayed on the shell prompt. If dis is ok wid u den u need not use d temp file.
Or u can do something like....declare the tempfile as u ve declared the other variables(specify d full path).
For the query:
Use the database query using while loop between the middle brackets...
You can use only database queries once the connection to database is eastablished....
And the database connection gets eastablished just after you give the isql command...
Try using the below code snippet in place of middle brackets.:
declare @i int
select @i = 1
while(@i <= 3)
begin
Insert into TEST values (3,"Test","TestDesc")
select @i = @i + 1
end
U should not use middle brackets dere..I had used it just to let you know the area where u need to write the query.
Try it out,buddy.....
Finally it works now, appreciate it so so much dear.
Beside i tried it directly for the connection as below:
#=======================================================
# Get the Connection String for isql
#=======================================================
isql -Usa -Ptest -I /opt/sybase/interfaces << EOF
use testdb
go
declare @i int
select @i = 1
while(@i <= 5)
begin
Insert into TEST values (@i,"Test","TestDesc")
select @i = @i + 1
end
go
EOF
Appreciate it
Johan, I'll move to do that in perl in a while, and hopefully it gonna work that i want to learn how that could be done.
Well, the isql command enable the sybase DB connection.
isql -Usa -Ptest -I /opt/sybase/interfaces << EOF
isql: is to access the sybase DB
-U : User name
-P : password
-I : Is the sybase DB connection interface file.
"/opt/sybase/interfaces" actually this file is exist for any Sybase DB server for connection params, liek the SID in Oracle DB.
That's all what i have right now, and hope it added some value.
Regards,