Heres an example of where you might call a Stored Proc that takes a defined date as it’s input and does something with it. Start an iSQL session and …
declare @my_date datetime
select @my_date=CONVERT (datetime,’Jan 18 2011 00:00:000′)
exec myStoredProc @my_date
Copies definitions for specified views, rules, defaults, triggers, or procedures from a database to an operating system file or from an operating system file to a database
defncopy -SmyDatabaseServer -UmyUser –PmyPassword out myStoredProc.out myDatabase myStoredProc
more info on defncopy (12.5)
You’d want to do this to perhaps drop the Stored Proc, alter it in an editor (say vim) and reload it.
Occasionally you might get this error when running sql queries (especially against a “new” database)
ct_connect(): directory service layer: internal directory control layer error:
Which means that there is no details on how to connect to your database (like a tns_names.ora thing).
Short term, you can add a new interfaces file to your profile and use the –I iSQL parameter to override whats in $SYBASE/interfaces.
master tcp ether DBSERV01.systems.uk 10099
query tcp ether DBSERV01.systems.uk 10099
isql -UmyUser -SmyDatabaseServer -DmyDatabase –PmyPassword -I/home/users/kieran/tempIfaceFile
But you’ll be wanting to get your Unix admin to update the Inferaces file to provide a permanent link.
iSQL is the Sybase equivalent of Oracle’s SQL+ I guess. It’s handy to know how to use it as you may now have a nice Sybase GUI front end to use.
From unix try
isql -UmyUser -SmyDatabaseServer -DmyDatabase –PmyPassword
set nocount on
Now simply type your sql followed by “go”
This is a handy example of how to read a file of sql commands and spool the result to an output file.
isql -UmyUser -SmyDatabaseServer -DmyDatabase –PmyPassword -i imput.sql -o result.txt -w600
Comparing dates to timestamps in Sybase isn’t as nice as Oracle but this should do the trick.
select count(*) from MY_TABLE where M_SYS_DATE >= CONVERT (datetime,’Jan 18 2011 00:00:000′) and M_SYS_DATE < CONVERT (datetime,’Jan 19 2011 00:00:000′)