Sybase Tips

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) 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 DEFAULT suser_name() NOT NULL, created_on DEFAULT getdate() 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.setString("@objname", "sp_help");

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 life with Sybase. It doesn't matter if you use jconn2.jar, jconn3.jar, or even jTDS.jar. Maybe you could enhance jTDS.jar. If you do, please send me an email!