• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

use database query

I am working on UNIX sun solaris platform and sybase database.
If i run "select @@version" in my database session , i get following output

Adaptive Server Enterprise/12.5.0.3/EBF 11441 ESD#4/P/Sun_svr4/OS 5.8/rel12503/1939/32-bit/FBO/Sun Sep 21 03:36:06 2003

This might help you to understand, where i am working.
Now question:

I have one file called FILE containg
use devncs_aus
select count(*) from CAA_CA

use  devncs_nzl
select count(*) from CAA_CA

use  dmtncs_aus
select count(*) from CAA_CA

use  dmtncs_nzl
select count(*) from CAA_CA


Now i use isql command to start database session , then i run
au1646s singhg1:/home/singhg1 > isql -c
Password:
1> :r FILE
But output returned is as shown below:

-----------
         119

(1 row affected)

 -----------
         119

(1 row affected)

 -----------
         119

(1 row affected)

 -----------
         119

(1 row affected)

But this is not true, all databases don't have same number of rows for given table, then why i am getting this result.
How i can get correct result?

Regards
pattha
0
pattha
Asked:
pattha
  • 4
1 Solution
 
Joe WoodhousePrincipal ConsultantCommented:
Sybase's T-SQL is an interpreted language. That means that each new line of the current command batch is added to the input buffer, but nothing is executed until the current command batch ends.

When you remap the normal command terminator using the "-c" option to isql, the command terminator must appear on its own in a line, with no leading characters. That means the newline at the end of each line of T-SQL isn't interpreted as a command terminator. Given you're reading the file in using :r, you must manually terminate the command batch.

I'll translate this to the default command terminator because it's easier to explain that way. The SQL you're executing is basically the equivalent to:

use devncs_aus
select count(*) from CAA_CA
use  devncs_nzl
select count(*) from CAA_CA
use  dmtncs_aus
select count(*) from CAA_CA
use  dmtncs_nzl
select count(*) from CAA_CA
go

And that makes it more clear. It's all being treated as a single batch. Each "use [database]" isn't executed until the end of the command batch, so when ASE interprets and optimises each "select count(*) ...", the "use [database]" hasn't been executed yet.

In other words, each query is being run in the first database. This is correct and expected behaviour, and the Sybase manuals warn that for this reason "use [database]" should usually be in a batch of its own.

You have two options here. Either (1) split this into multiple batches, or (2) use fully qualified names to each of the tables.

The first option won't work for the way you're running this. You're running isql in interactive mode (reading a file using ":r" basically just fills your interactive buffer from the file, but you're still in manual interactive mode). This limits you to a single command batch at a time. If you want your file to remain substantially the same, you will need to run isql in non-interactive mode:

isql [other options] -i [input file]

Doing it this way (and using "go" instead of your terminator), your file would look like this:

use devncs_aus
go
select count(*) from CAA_CA
go
use  devncs_nzl
go
select count(*) from CAA_CA
go
use  dmtncs_aus
go
select count(*) from CAA_CA
go
use  dmtncs_nzl
go
select count(*) from CAA_CA
go

If you want to be able to make this work in a single command batch interactively, you'll need to remove the "use [database]" commands and fully qualify the table names instead:

select count(*) from devncs_aus..CAA_CA
select count(*) from devncs_nzl..CAA_CA
select count(*) from dmtncs_aus..CAA_CA
select count(*) from dmtncs_nzl..CAA_CA
0
 
Joe WoodhousePrincipal ConsultantCommented:
Did that help? Did my explanation make sense?
0
 
patthaAuthor Commented:
Hi Joe,

I don't know why system is behaving like this.
In morning only, i accepted yr solution, but system is still showing question as OPEN.
i will try once more.

Thanks

Pattha.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Oh, don't worry about that, I wasn't jogging your elbow - I just wanted to make sure that everything was working at your end.

Let EE sort itself out. 8->
0
 
Joe WoodhousePrincipal ConsultantCommented:
And it seems to have worked this time anyway. Thank you for looking into it, and I'm glad my answer was helpful!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now