1

I am trying to run a sybase DB query from a jsp session.

I can connect to the DB fine, but when I run my query, by default it always runs against tempdb.

Is there a way I can make a connection to a different DB and not by default connect to tempdb?

The aim is to be able to run a query for any table chosen by the user from a drop down list. I have the dropdown list working and can successfully select any DB. I know this is working as I am storing the DB selected in a variable and can confirm the name selected is always the name I can see in the variable.

1

It seems the DBA who has created your login has set tempdb as your default database. You may request him/her to do the change at login definition level as per this article. By default, if none of them are mentioned, user connects to master database. You may use below command:

sp_modifylogin login_name, defdb, "your_database"

Kindly note that above command needs to be run at Sybase Database level and only a System Administrator or System Security Officer can execute sp_modifylogin.

As already suggested by Markp, you can issue the command use <database_name> for changing your database. Alternatively you may use database name in the connection string itself.

Below is an example of isql for connecting to "your_database"

isql -S"Server_Name" -U"Login" -D"Your_Database" -P"Password"

You may refer below for isql options:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1570100/doc/html/san1367605044069.html

Hope above helps.

  • Hi Max, Thanks for getting back to me I have tried the above sp_modify.. but get the below error: javax.servlet.ServletException: java.sql.SQLException: JZ0R2: No result set for this query. Any idea on this one? – Ace Apr 17 at 12:35
  • I think you are running this query in your jsp, you need to run this query at Sybase database level and in order to change attribute of login, you need to have sysadmin privilege. – Learning_DBAdmin Apr 17 at 12:38
  • I get the same error if I try use <dbname>. – Ace Apr 17 at 12:39
  • Yes, I am running at jsp level – Ace Apr 17 at 12:40
  • You need to use embedded query in case you are firing sybase query from JSP. please refere this link --> stackoverflow.com/questions/30321423/… – Learning_DBAdmin Apr 17 at 12:55
0

Assuming this is Sybase ASE ... you could preface sysobjects with the name of the desired database (eg, pubs.sysobjects) but then you have a problem in that some of those system functions will still operate with details from tempdb; your best bet would be to have your application issue a use <dbname> (as a separate command) prior to submitting your actual query.

0

I have it working now using "use select ......"

However, somewhere in my query it still seems to be using tempdb.

select
-- Verbose stats on how full the DB is in terms of log and data - note that this is not completely accurate
CONVERT(NUMERIC(10,0),(SELECT sum(size) * 0.0078125 FROM master.dbo.sysusages WHERE dbid = db_id() AND segmap != 4)) DataSize
, CONVERT(NUMERIC(10,0),(SELECT ROUND(sum(reserved_pages(db_id(),id) * 0.0078125),0) FROM sysobjects WHERE id != 8)) DataUsed
, CONVERT(VARCHAR, CONVERT(NUMERIC(10,0),(SELECT sum(reserved_pages(db_id(),id) * 1.0) FROM sysobjects WHERE id != 8)
/
(SELECT sum(size) * 1.0 FROM master.dbo.sysusages WHERE  dbid = db_id() AND  segmap != 4) * 100 ) )+ '%' DataPercentageUsed
, CONVERT(NUMERIC(10,0),(SELECT sum(size) * 0.0078125 FROM master.dbo.sysusages WHERE dbid = db_id() AND segmap = 4) )LogSize
, CONVERT(NUMERIC(10,0),(SELECT ROUND(sum(reserved_pages(db_id(),id) * 0.0078125),0) FROM sysobjects WHERE id = 8)) LogUsed
, CONVERT(VARCHAR, CONVERT(NUMERIC(10,0),(SELECT sum(reserved_pages(db_id(),id) * 1.0) FROM sysobjects WHERE id = 8)
/
(SELECT sum(size) * 1.0 FROM master.dbo.sysusages WHERE  dbid = db_id() AND  segmap = 4) * 100) ) + '%' LogPercentageUsed



select
convert(date,getdate()) [Date],
db_name() DB,
object_name(id) Table_Name,
row_count(db_id(), id) [Rows],
reserved_pages(db_id(),id) * 8 ReservedKB -- assuming 8k page size
from sysobjects
where type = 'U'

order by 5 desc

In my jsp session I am running these like:

 rs = stmt1.executeQuery("use "+ chosendb +" select CONVERT(NUMERIC(10,0),(SELECT sum(size) * 0.0078125 FROM master.dbo.sysusages WHERE dbid = db_id() AND segmap != 4)) DataSize, CONVERT(NUMERIC(10,0),(SELECT ROUND(sum(reserved_pages(db_id(),id) * 0.0078125),0) FROM sysobjects WHERE id != 8)) DataUsed, CONVERT(VARCHAR, CONVERT(NUMERIC(10,0),(SELECT sum(reserved_pages(db_id(),id) * 1.0) FROM sysobjects WHERE id != 8)/(SELECT sum(size) * 1.0 FROM master.dbo.sysusages WHERE  dbid = db_id() AND segmap != 4) * 100 ) ) + '%' DataPercentageUsed, CONVERT(NUMERIC(10,0),(SELECT sum(size) * 0.0078125 FROM master.dbo.sysusages WHERE dbid = db_id('"+ chosendb +"') AND segmap = 4) )LogSize, CONVERT(NUMERIC(10,0),(SELECT ROUND(sum(reserved_pages(db_id(),id) * 0.0078125),0) FROM sysobjects WHERE id = 8)) LogUsed, CONVERT(VARCHAR, CONVERT(NUMERIC(10,0),(SELECT sum(reserved_pages(db_id(),id) * 1.0) FROM sysobjects WHERE id = 8)/(SELECT sum(size) * 1.0 FROM master.dbo.sysusages WHERE dbid = db_id() AND segmap = 4) * 100) ) + '%' LogPercentageUsed");  
    ts = stmt2.executeQuery("select convert(date,getdate()) [Date], db_name() DB, object_name(id) Table_Name, row_count(db_id(), id) [Rows], reserved_pages(db_id(),id) * 8 ReservedKB from sysobjects where type = 'U'order by 5 desc");

+chosendb+ is my variable that stores the DB name. The second query seems to be working fine, in the first query, Data Used and Data Percentage are always wrong. The rest of the output e.g. DataSize, LogSize, LogUsed and Log Percentage Used are all correct.

I know they are correct because I have ran the same query from my Rapid SQL session and compared answers.

  • Anyone have any ideas? Really stuck on this... – Ace Apr 24 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.