Sybase Tips

View all indexes

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

<geshi lang="sql"> 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) </geshi>

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.

<geshi lang="ruby">

1. !/bin/env ruby
3. Create triggers on each of the tables

require 'erb'

puts '-- Generated by $HeadURL: $'

1. 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 </geshi>

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: <geshi lang="java5">

   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();

</geshi>

and you get an exception like <geshi lang="java5"> 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) </geshi>

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:

<geshi lang="java5"> 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) </geshi>