Sybase Tutorial

[r***@yahoo.co.in]

Hi everyone,

I am new to this group and looking for a good tutorial to start with
sybase. I would like to know about how to execute the queries within a
script. Please help.

Thanks in advance,
Ria.

[D_Peglow]

Post by r***@yahoo.co.in
I am new to this group and looking for a good tutorial to start with
sybase. I would like to know about how to execute the queries within a
script. Please help.

There a several ways to do it.

Some Unix-Shells Scripts:

#!/bin/sh
isql -U<USER> -P<PASSWORD> <<SQL
SELECT *
FROM Table
GO
SQL
# End

Or put yor query in a file query.sql

#!/bin/sh
isql -U<USER> -P<PASSWORD> -i query.sql
# end

Dietmar

[pokerdragon]

Post by D_Peglow

Post by r***@yahoo.co.in
I am new to this group and looking for a good tutorial to start with
sybase. I would like to know about how to execute the queries within a
script. Please help.

There a several ways to do it.
#!/bin/sh
isql -U<USER> -P<PASSWORD> <<SQL
SELECT *
FROM Table
GO
SQL
# End
Or put yor query in a file query.sql
#!/bin/sh
isql -U<USER> -P<PASSWORD> -i query.sql
# end
Dietmar

Some enhancements to Dietmar's ideas:

To avoid confision... the "SQL" in the above isn't saying put your SQL
here... it's a delimiter that tells the script the beginning and the
end of the actual statement. You can put any delimiter here... I
usually use an exclamation point.

Some issues if security is a concern:
Ideally, you'll create a password variable, and populate it with some
other command that grabs it, encrypted, from some location before
calling the ISQL. Then, instead of specifying the -P on the isql
command line, put the password variable as the very first line of your
SQL. Otherwise the password will appear plain text to anyone who does
a 'ps' on the box while the isql is running.

So you would have:

#!/bin/sh

## Call some script to retrieve the password
SYBASE_PW=`/somepath/.get_password`

## Execute the sql
isql -Uuser -Sserver << ! >>
$SYBASE_PW
select * from table
go
!

At the very least, if you don't have a password generation script and
have to hard code the password into the script, put it within the isql
as above so it doesn't show up when doing a 'ps', and set read access
on the script to only those who should know the password.

You could pass the password in on the command line, but again, doing
this causes it to appear when doing a PS during the entire time the
script is running (as opposed to only when the isql statements are
running). If this is an interactive script you could prompt for the
password of course.

Just keep in mind that the password has to be on a line by itself as
the first line if your sql statement inside of your delimiter ('!',
'SQL', or whatever else you choose).

The method of passing in the sql with the -i option also works, but
again with this option you allow the password to be clearly displayed
on a 'ps' listing while the isql is running.

-Mike

[r***@yahoo.co.in]

Thanks for the information.
I would like to know, how can I execute the qurries in my local
machine.
I have cygwin installed in my machine, and tried to install sqsh.
But it does not get installed.
If anybody knows a better way to set up the environment on my windows
machine please let me know.

thanks
ria.

Post by pokerdragon

Post by D_Peglow

Post by r***@yahoo.co.in
I am new to this group and looking for a good tutorial to start with
sybase. I would like to know about how to execute the queries within a
script. Please help.

There a several ways to do it.
#!/bin/sh
isql -U<USER> -P<PASSWORD> <<SQL
SELECT *
FROM Table
GO
SQL
# End
Or put yor query in a file query.sql
#!/bin/sh
isql -U<USER> -P<PASSWORD> -i query.sql
# end
Dietmar

To avoid confision... the "SQL" in the above isn't saying put your SQL
here... it's a delimiter that tells the script the beginning and the
end of the actual statement. You can put any delimiter here... I
usually use an exclamation point.
Ideally, you'll create a password variable, and populate it with some
other command that grabs it, encrypted, from some location before
calling the ISQL. Then, instead of specifying the -P on the isql
command line, put the password variable as the very first line of your
SQL. Otherwise the password will appear plain text to anyone who does
a 'ps' on the box while the isql is running.
#!/bin/sh
## Call some script to retrieve the password
SYBASE_PW=`/somepath/.get_password`
## Execute the sql
isql -Uuser -Sserver << ! >>
$SYBASE_PW
select * from table
go
!
At the very least, if you don't have a password generation script and
have to hard code the password into the script, put it within the isql
as above so it doesn't show up when doing a 'ps', and set read access
on the script to only those who should know the password.
You could pass the password in on the command line, but again, doing
this causes it to appear when doing a PS during the entire time the
script is running (as opposed to only when the isql statements are
running). If this is an interactive script you could prompt for the
password of course.
Just keep in mind that the password has to be on a line by itself as
the first line if your sql statement inside of your delimiter ('!',
'SQL', or whatever else you choose).
The method of passing in the sql with the -i option also works, but
again with this option you allow the password to be clearly displayed
on a 'ps' listing while the isql is running.
-Mike

[b***@sybase.com]

The issue of passwords being exposed through the "ps" command
should not be an issue anymore due to the fix implemented in CR
367990,
available (on UNIX) in connectivity ebfs 12.5.1 ESD #7 and higher, and
15.0

-bret

[Juergen Bajdala]

Post by b***@sybase.com
The issue of passwords being exposed through the "ps" command
should not be an issue anymore due to the fix implemented in CR
367990,
available (on UNIX) in connectivity ebfs 12.5.1 ESD #7 and higher, and
15.0

looks good on Linux, but doesn't work on SunOS
(tested with Adaptive Server Enterprise/12.5.2/EBF 11790/P/Sun_svr4/OS 5.8
and Adaptive Server Enterprise/12.5.3/EBF 12454 ESD#2/P/Sun_svr4/OS 5.8)

Jürgen

[b***@sybase.com]

The issue of passwords being exposed through the "ps" command
should not be an issue anymore due to the fix implemented in CR
367990,
available (on UNIX) in connectivity ebfs 12.5.1 ESD #7 and higher, and
15.0

-bret

[Oliver Röwert]

Dear all,

Post by pokerdragon
Ideally, you'll create a password variable, and populate it with some
other command that grabs it, encrypted, from some location before
calling the ISQL. Then, instead of specifying the -P on the isql
command line, put the password variable as the very first line of your
SQL. Otherwise the password will appear plain text to anyone who does
a 'ps' on the box while the isql is running.
#!/bin/sh
## Call some script to retrieve the password
SYBASE_PW=`/somepath/.get_password`

for a long time, I'm searching for some example script as .get_password
described above. The main issue to me is to encrypt the passwords in
some password file, readable for only one user, which is not root.

As I'm not good in encryption, it would be nice, if you could post some
links for further reading to implement some encryption in the password
file in a standard Solaris 9 environment.

Best regards, Oliver

[Michael Peppler]

Post by Oliver Röwert
Dear all,

Post by D_Peglow
#!/bin/sh
## Call some script to retrieve the password
SYBASE_PW=`/somepath/.get_password`

for a long time, I'm searching for some example script as .get_password
described above. The main issue to me is to encrypt the passwords in some
password file, readable for only one user, which is not root.

There are various problems with this. First, you can't easily prevent root
from reading your crypted password file, unless you require the user to
enter a password to decrypt the file interactively (i.e. if the encryption
key is in the get_password program then root can execute it and get the
password).

That being said, you can write an encrypt/decrypt program using the
OpenSSL libraries fairly easily. I don't know much about encryption
algorithms, but I was able to write such a program in a day or so from
reading the docs on the openssl web site.

While such a program only gives a limited amount of security (i.e. if you
can execute the program you can get the password) it at least removes the
clear-text passwords from various files on the system (either in scripts
or in configuration files).

Michael