Sybase ASE / SQL Server Notes

Supporting Quoted Identifiers

The Problem

In ANSI SQL, you use single-quotes to represent strings, not double-quotes. Double-quotes would tie to identifiers in the DB (e.g. column names, tables names, in case they have spaces).

For Sybase, this is also true if you connect via JDBC, but for whatever reason, it is not true if you connect via the isql command line - see Sybase’s doc for more info.

Hence, if you deployed stored procedures via isql, and then try to re-deploy via isql or Obevo in its default setting, you will get an error, as the SP will have double-quotes

The Solution

If you specify the metadata annotation //// METADATA DISABLE_QUOTED_IDENTIFIERS, then Obevo will turn off the quotedidentifier option so that the double-quotes will be treated as strings. It will then turn it back on for subsequent deployments.

Note that the Obevo reverse-engineering step mentioned earlier will automatically add this annotation if it sees a double-quote in the text. (Though there is a chance that it is actually intended as an identifier, the likely case is that it was intended as a string. You should try to recall if the SP was originally deployed via isqlto help your decision)if you do the reverse-engineering steps above,

Temp Tables as Input to Stored Procedures

We have see some cases where a stored procedure required a temp table to be populated for running the stored procedure. However, to create the SP, we need the temp table created in the first place

To do this, you can do this in the sp sql file itself, but just remember to add the temp table beforehand and then to drop it, e.g.

create table #mytemp (a int, b int)
go
create proc PopulateSelectTradeTemps() as
...
select * from #mytemp
...
end
go
drop #mytemp

(not that we’d want to do something like this in the age of Java and Reladomo/Hibernate, but this is here in case you need it.)