Case study 39: How to use SQL Developer with Sybase - special case
Platforms: Windows
SAP ASE version: All
Background story: One developer asked me how to check if some table columns are nullable or not, by using SQL developer (Oracle tool) which is connected to Sybase database by utilizing jTDS JDBC driver. This driver does not return selects done inside procedure, so I could not tell him just to call sp_columns procedure. If procedure execution is tried in SQL developer, it will not return anything.
So, I've checked syscolumns system table, found out that status column holds an info about if some column is nullable or nor, and I wrote first query:
select object_name(id) table_name, name as column_name, case (status & 8)
when 0 then 'not nullable'
else 'nullable'
end as 'col_nulability'
from syscolumns where id = object_id('some_table_name')
go
Solution: BUT, SQL developer, as primarily Oracle tool, when encountered logical and sign (&), it thought that it is variable substitution sign, and when I wanted to execute it, I've got a window, where SQL developer asked me to Enter substitution variable.
So, I needed to change my query in order to avoid this & sign. And what I did? I've remembered basic logical transformations and DeMorgan's theorems, so I've transformed logical AND into equivalent logical OR combination meaning that I've transformed A & B into ~(~A | ~ B). ~ is Sybase sign for logical negation, and | is Sybase sign for logical OR. In that way I've avoided sign &, and I was able to execute query in SQL developer.
Final query than looked like:
select object_name(id) table_name, name as column_name, case ~(~status | ~ 8)
when 0 then 'not nullable'
else 'nullable'
end as 'col_nulability'
from syscolumns where id = object_id('some_table_name')
go
