Sybase Tips - Revision history

Brianegge at 15:01, 3 July 2014

2014-07-03T15:01:34Z

← Older revision		Revision as of 15:01, 3 July 2014
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 ==		== View all indexes ==

Line 153:		Line 162:
	</syntaxhighlight>		</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:		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">		<syntaxhighlight lang="java5">

Brianegge at 23:09, 10 December 2011

2011-12-10T23:09:31Z

← Older revision		Revision as of 23:09, 10 December 2011
Line 3:		Line 3:
	'sp_helpindex' or 'select * from sysindexes where indid = 0' seems to work better for finding clustered indexes.		'sp_helpindex' or 'select * from sysindexes where indid = 0' seems to work better for finding clustered indexes.

	<~~geshi~~ lang="sql">		<syntaxhighlight lang="sql">
	select 'Table name' = object_name(id),'column_name' = index_col(object_name(id),indid,1),		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		'index_description' = convert(varchar(210), case when (status & 16)<>0 then 'clustered' else 'nonclustered' end
Line 17:		Line 17:
	from sysindexes where (status & 64) = 0		from sysindexes where (status & 64) = 0
	order by object_name(id)		order by object_name(id)
	</~~geshi~~>		</syntaxhighlight>

	Thanks [http://www.dbforums.com/showthread.php?t=1625012#post6309313 parangiri]		Thanks [http://www.dbforums.com/showthread.php?t=1625012#post6309313 parangiri]
Line 40:		Line 40:
	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.

	<~~geshi~~ lang="ruby">		<syntaxhighlight lang="ruby">
	#!/bin/env ruby		#!/bin/env ruby
	#		#
Line 104:		Line 104:
	puts template.result(table.get_binding)		puts template.result(table.get_binding)
	end		end
	</~~geshi~~>		</syntaxhighlight>

	==sp_helptext on sp_help==		==sp_helptext on sp_help==
Line 125:		Line 125:

	You're trying to do something like:		You're trying to do something like:
	<~~geshi~~ lang="java5">		<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.registerOutParameter("return", Types.INTEGER); // This line is key - it won't work without it!
	cs.setString("@objname", "sp_help");		cs.setString("@objname", "sp_help");
	cs.execute();		cs.execute();
	</~~geshi~~>		</syntaxhighlight>

	and you get an exception like		and you get an exception like
	<~~geshi~~ lang="java5">		<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 151:		Line 151:
	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)
	</~~geshi~~>		</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:		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">		<syntaxhighlight lang="java5">
	java.sql.SQLException: JZ0SC: Callable Statement: attempt to set the return status as an input parameter.		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.ErrorMessage.raiseError(Unknown Source)
Line 163:		Line 163:
	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)		at com.sybase.jdbc3.jdbc.SybCallableStatement.setString(Unknown Source)
	</~~geshi~~>		</syntaxhighlight>

	[[Category:Sybase]]		[[Category:Sybase]]

Brianegge at 02:54, 7 April 2009

2009-04-07T02:54:39Z

← Older revision		Revision as of 02:54, 7 April 2009
Line 164:		Line 164:
	at com.sybase.jdbc3.jdbc.SybCallableStatement.setString(Unknown Source)		at com.sybase.jdbc3.jdbc.SybCallableStatement.setString(Unknown Source)
	</geshi>		</geshi>

			[[Category:Sybase]]

Egge at 00:15, 5 March 2008

2008-03-05T00:15:55Z

← Older revision		Revision as of 00:15, 5 March 2008
Line 1:		Line 1:
	== View all indexes ==		== View all indexes ==

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

	<geshi lang="sql">		<geshi lang="sql">

Egge at 23:41, 4 March 2008

2008-03-04T23:41:52Z

← Older revision		Revision as of 23:41, 4 March 2008
Line 1:		Line 1:
			== View all 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 [http://www.dbforums.com/showthread.php?t=1625012#post6309313 parangiri]

	== [http://www.sybase.com/detail?id=1024518 Floating Point Datatypes] ==		== [http://www.sybase.com/detail?id=1024518 Floating Point Datatypes] ==

Egge: /* create 'audit' columns */

2007-10-15T04:19:56Z

create 'audit' columns

← Older revision		Revision as of 04:19, 15 October 2007
Line 18:		Line 18:
	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~~>		<geshi lang="ruby">
	#!/bin/env ruby		#!/bin/env ruby
	#		#
Line 82:		Line 82:
	puts template.result(table.get_binding)		puts template.result(table.get_binding)
	end		end
	</~~pre~~>		</geshi>

	==sp_helptext on sp_help==		==sp_helptext on sp_help==

Egge: /* Java CallableStatement named parameters */

2007-10-15T04:18:44Z

Java CallableStatement named parameters

← Older revision		Revision as of 04:18, 15 October 2007
Line 103:		Line 103:

	You're trying to do something like:		You're trying to do something like:
	<~~pre~~>		<geshi 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();
			</geshi>

	and you get an exception like		and you get an exception like
	<~~pre~~>		<geshi 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 127:		Line 129:
	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~~>		</geshi>

	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!~~		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>

Brianegge at 06:42, 20 September 2007

2007-09-20T06:42:37Z

← Older revision		Revision as of 06:42, 20 September 2007
Line 1:		Line 1:
			== [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==

Egge: /* create 'audit' columns */ removed default from modify

2007-07-05T23:30:46Z

create 'audit' columns: removed default from modify

← Older revision		Revision as of 23:30, 5 July 2007
Line 20:		Line 20:
	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 28:

	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)

Brianegge at 05:46, 29 June 2007

2007-06-29T05:46:03Z

← Older revision		Revision as of 05:46, 29 June 2007
Line 1:		Line 1:
			==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.

			<pre>
			#!/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
			</pre>

	==sp_helptext on sp_help==		==sp_helptext on sp_help==