Sybase Tips: Difference between revisions

From EggeWiki
No edit summary
mNo edit summary
 
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
== Domain error ==
This error:
<syntaxhighlight lang="bash">
Msg 3622, Level 16, State 0:
Server 'XYZ1234', Line 227:
Domain error occurred.
</syntaxhighlight>
May be caused by attempting to do a sum on a column which contains NaN or Inf values.
== View all indexes ==
'sp_helpindex' or 'select * from sysindexes where indid = 0'  seems to work better for finding clustered indexes.
<syntaxhighlight 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)
</syntaxhighlight>
Thanks [http://www.dbforums.com/showthread.php?t=1625012#post6309313 parangiri]
== [http://www.sybase.com/detail?id=1024518 Floating Point Datatypes] ==
{| class="wikitable" border="1"
|-
!Sybase !! Java/C !!
|-
|real
|style="background:yellow" | float || 32 bit, single precision
|-
|double || double || 64 bit, double precision
|-
|style="background:yellow" | float
|defaults to double, unless an optional precision of less than 16 is specified || normally 64 bits
|}
==create 'audit' columns==
==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.
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.


<pre>
<syntaxhighlight lang="ruby">
#!/bin/env ruby
#!/bin/env ruby
#
#
Line 20: Line 66:
     declare @query VARCHAR(4096)
     declare @query VARCHAR(4096)
     select @query = 'ALTER TABLE <%= @table %>
     select @query = 'ALTER TABLE <%= @table %>
     add created_by VARCHAR(30) NULL, created_on datetime DEFAULT getdate() NULL,
     add created_by VARCHAR(30) DEFAULT suser_name() NULL, created_on datetime DEFAULT getdate() NULL,
         modified_by VARCHAR(30) NULL , modified_on datetime NULL'
         modified_by VARCHAR(30) NULL , modified_on datetime NULL'
     EXEC(@query)
     EXEC(@query)
Line 28: Line 74:


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


Line 67: Line 113:
     puts template.result(table.get_binding)
     puts template.result(table.get_binding)
end
end
</pre>
</syntaxhighlight>


==sp_helptext on sp_help==
==sp_helptext on sp_help==
Line 88: Line 134:


You're trying to do something like:
You're trying to do something like:
<pre>
<syntaxhighlight lang="java5">
     CallableStatement cs = conn.prepareCall("{?= call sp_help ?}");
     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.setString("@objname", "sp_help");
</pre>
    cs.execute();
</syntaxhighlight>


and you get an exception like
and you get an exception like
<pre>
<syntaxhighlight lang="java5">
java.lang.AbstractMethodError: com.sybase.jdbc2.jdbc.SybCallableStatement.setString(Ljava/lang/String;Ljava/lang/String;)V
java.lang.AbstractMethodError: com.sybase.jdbc2.jdbc.SybCallableStatement.setString(Ljava/lang/String;Ljava/lang/String;)V
at ConnectionTest.testCallable(ConnectionTest.java:66)
at ConnectionTest.testCallable(ConnectionTest.java:66)
Line 112: Line 160:
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:342)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:342)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:194)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:194)
</pre>
</syntaxhighlight>
 
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 http://espanacialis.org/levitra/:
 
<syntaxhighlight 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)
</syntaxhighlight>


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!
[[Category:Sybase]]

Latest revision as of 11:01, 3 July 2014

Domain error

This error:

Msg 3622, Level 16, State 0:
Server 'XYZ1234', Line 227:
Domain error occurred.

May be caused by attempting to do a sum on a column which contains NaN or Inf values.

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 http://espanacialis.org/levitra/:

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)