Search code examples
javasybasesap-ase

Checking if table exist or not

I am retrieving data from database using jdbc. In my code I am using 3-4 tables to get data. But sometimes if table is not present in database my code gives exception. How to handle this situation. I want my code to continue working for other tables even if one table is not present. Please help.

I have wrote a code like this

sql="select * from table"
now Result set and all.

If table is not present in database it give exception that no such table. I want to handle it. In this code I cannot take tables which are already present in advance . I want to check here itself if table is there or not.

Please do not mark it as a duplicate question. The link you shared doesnot give me required answer as in that question they are executing queries in database not through JDBC code

Solution

  • For Sybase ASE the easiest/quickest method would consist of querying the sysobjects table in the database where you expect the (user-defined) table to reside:

    select 1 from sysobjects where name = 'table-name' and type = 'U'
    • if a record is returned => table exists
    • if no record is returned => table does not exist

    How you use the (above) query is up to you ...

    • return a 0/1-row result set to your client
    • assign a value to a @variable
    • place in a if [not] exists(...) construct
    • use in a case statement

    If you know for a fact that there won't be any other object types (eg, proc, trigger, view, UDF) in the database with the name in question then you could also use the object_id() function, eg:

    select object_id('table-name')
    • if you receive a number => the object exists
    • if you receive a NULL => the object does not exist

    While object_id() will obtain an object's id from the sysobjects table, it does not check for the object type, eg, the (above) query will return a number if there's a stored proc named 'table-name'.

    As with the select/sysobjects query, how you use the function call in your code is up to you (eg, result set, populate @variable, if [not] exists() construct, case statement).

    So, addressing the additional details provided in the comments ...

    Assuming you're submitting a single batch that needs to determine table existence prior to running the desired query(s):

    -- if table exists, run query(s); obviously if table does not exist then query(s) is not run

if exists(select 1 from sysobjects where name = 'table-name' and type = 'U')
begin
     execute("select * from table-name")
end
    • execute() is required to keep the optimizer from generating an error that the table does not exist, ie, the query is not parsed/compiled unless the execute() is actually invoked

    If your application can be written to use multiple batches, something like the following should also work:

    # application specific code; I don't work with java but the gist of the operation would be ...
run-query-in-db("select 1 from sysobjects where name = 'table-name' and type = 'U'")
if-query-returns-a-row
then
    run-query-in-db("select * from table-name")
fi