Sybase Tips

View all indexes

'sp_helpindex' or 'select * from sysindexes where indid = 0' seems to work better for finding clustered indexes.

select 'Table name' = object_name(id),'column_name' = index_col(object_name(id),indid,1),
'index_description' = convert(varchar(210), case when (status & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (status & 1)<>0 then ', '+'ignore duplicate keys' else '' end
+ case when (status & 2)<>0 then ', '+'unique' else '' end
+ case when (status & 4)<>0 then ', '+'ignore duplicate rows' else '' end
+ case when (status & 64)<>0 then ', '+'statistics' else case when (status & 32)<>0 then ', '+'hypothetical' else '' end end
+ case when (status & 2048)<>0 then ', '+'primary key' else '' end
+ case when (status & 4096)<>0 then ', '+'unique key' else '' end
+ case when (status & 8388608)<>0 then ', '+'auto create' else '' end
+ case when (status & 16777216)<>0 then ', '+'stats no recompute' else '' end),
'index_name' = name
from sysindexes where (status & 64) = 0
order by object_name(id)

Thanks parangiri

Floating Point Datatypes

Sybase	Java/C
real	float	32 bit, single precision
double	double	64 bit, double precision
float	defaults to double, unless an optional precision of less than 16 is specified	normally 64 bits

create 'audit' columns

This isn't as useful as lifetime tables, but it's easy to put in place. Here's a Ruby script which generates the SQL for a list of tables.

#!/bin/env ruby
#
# Create triggers on each of the tables
#
#

require 'erb'

puts '-- Generated by $HeadURL: $'
#requires an @table and @id, where @id is a unique column
template = ERB.new <<-EOF
-- start <%= @table %>
IF NOT EXISTS (select 1 from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'created_by' and o.name = '<%= @table %>')
BEGIN
    declare @query VARCHAR(4096)
    select @query = 'ALTER TABLE <%= @table %>
    add created_by VARCHAR(30) DEFAULT suser_name() NULL, created_on datetime DEFAULT getdate() NULL,
        modified_by VARCHAR(30) NULL , modified_on datetime NULL'
    EXEC(@query)

    select @query = 'update <%= @table %> set created_by = suser_name(), created_on = getdate()'
    EXEC(@query)

    select @query = 'ALTER TABLE <%= @table %>
    modify created_by NOT NULL, created_on NOT NULL'
    EXEC(@query)

    select @query = 'create trigger <%= @table[0..25] %>_Utg
    on <%= @table %>
    for update
    as
    begin
      update <%= @table %> set modified_by = suser_name(), modified_on = getdate()
      from inserted, <%= @table %>
      where <%= @table %>.<%= @id %> = inserted.<%= @id %>
    end'
    EXEC(@query)
END
GO
-- end <%= @table %>


EOF

class Table
    attr_accessor :table, :id
    def initialize(table, id='id')
        @table = table
        @id = id
    end
    # Support templating of member data.
    def get_binding
        binding
    end
end

TABLES = [
Table.new('wiki')
]

TABLES.each() do |table|
    puts template.result(table.get_binding)
end

sp_helptext on sp_help

  use sybsystemprocs;
  sp_helptext sp_help;

Visual Basic ADO connection string

    Dim Conn As New ADODB.Connection
    Conn.CursorLocation = adUseClient ' <-- needed for return params
    Conn.Open "Provider=Sybase.ASEOLEDBProvider;Server Name=myserver.foo.com,1400;Initial Catalog=my_database;User Id=sa;Password="
    Conn.Execute ("Select @@version") ' Test to make sure the connection is ok

Java CallableStatement named parameters

You're trying to do something like:

    CallableStatement cs = conn.prepareCall("{?= call sp_help ?}");
    cs.registerOutParameter("return", Types.INTEGER); // This line is key - it won't work without it!
    cs.setString("@objname", "sp_help");
    cs.execute();

and you get an exception like

java.lang.AbstractMethodError: com.sybase.jdbc2.jdbc.SybCallableStatement.setString(Ljava/lang/String;Ljava/lang/String;)V
	at ConnectionTest.testCallable(ConnectionTest.java:66)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at junit.framework.TestCase.runTest(TestCase.java:154)
	at org.jmock.core.VerifyingTestCase.runBare(Unknown Source)
	at junit.framework.TestResult$1.protect(TestResult.java:106)
	at junit.framework.TestResult.runProtected(TestResult.java:124)
	at junit.framework.TestResult.run(TestResult.java:109)
	at junit.framework.TestCase.run(TestCase.java:118)
	at junit.framework.TestSuite.runTest(TestSuite.java:208)
	at junit.framework.TestSuite.run(TestSuite.java:203)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:474)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:342)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:194)

This is fixed with jconn3.jar. I previously didn't think it worked because you must register the return parameter. If you don't register the return param, you will get an error like this:

java.sql.SQLException: JZ0SC: Callable Statement: attempt to set the return status as an input parameter.
	at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
	at com.sybase.jdbc3.jdbc.ParamManager.doSetParam(Unknown Source)
	at com.sybase.jdbc3.jdbc.ParamManager.setParam(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybCallableStatement.a(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybCallableStatement.a(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybCallableStatement.setString(Unknown Source)