Sybase ASE
11.x 12.x 15.x
Last updated: 18 Mar 2013
User-Defined SQL Functions in ASE
 
Contents


ASE supports user-defined function in different ways, depending on the ASE version:
  • SQL UDFs: As of version 15.0.2, ASE supports user-defined functions in SQL. This functionality is included in the core ASE product and does not require an ASE license option.
    Note that there are some limitations to what you can do in a SQL UDF (you cannot do database insert/update/delete actions for example), so from a functional perspective, Java-based UDFs provide more functionality.
  • Java UDFs: ASE 12.0 and later supports user-defined functions that can be implemented in Java, and are executed from SQL in a dedicated JVM inside the ASE server. Java-based UDFs are still supported in ASE 15.x



User-Defined SQL Functions (ASE 15.0.2)

Scalar-valued SQL UDFs
ASE 15.0.2 supports 'scalar-valued SQL UDFs', which are UDFs returning a single value only (i.e. the 'classic' UDF). This is to distinguish from 'table-valued SQL UDFs', which return a table: ASE does not support such UDFs at this point.

Here's an example. I like my dates formatted as 'dd-MMM-yyyy', e.g. '02-Jan-2010' (this avoids the ambiguities you get with formatting like '01/02/03').
Instead of always formatting date values explicitly in every query I write, now I can use a SQL UDF for that:
use my_db
go
create function datefmt_robv(@d datetime)
returns char(11)
as
   return str_replace(convert(char(11), @d, 106), ' ', '-')
go


1> select dbo.datefmt_robv(getdate())
2> go
 -----------
 22-Jul-2007

(1 row affected)


1> select name, dbo.datefmt_robv(crdate) from sysobjects
2> go
 ------------------------- -----------
 your_tab                  30-Apr-2007
 my_tab                    11-May-2007
 proc1                     03-Jun-2007
 bigtab                    24-Apr-2007
 oldtab                    13-Sep-2006

(5 rows affected)


1> select name from sysobjects
2> where dbo.datefmt_robv(crdate) = '11-May-2007'
3> go
 ------------------------- 
 my_tab                    

(1 row affected)


SQL UDFs are located in the database where they are created, but they can be called from other databases:
use your_db
go

1> select my_db.dbo.datefmt_robv(getdate())
2> go
 -----------
 24-Jul-2007

(1 row affected)

Note that a SQL UDF must be prefixed with the owner name of the function. This follows the same convention as used by SQL UDFs in MS SQL Server.

An important practical aspect of SQL UDFs is that you can name them with an "sp_" prefix, and place them in the sybsystemprocs database so that the UDF will be accessible from anywhere in the ASE server. Without this trick, you'd have to explicitly specify the database where the UDF is located. The downloadable SQL UDFs are all named "sp_f_", and placed in sybsystemprocs.
For more background, see the quiz question for July 2009. (Note, BTW, that you cannot use this trick with UDFs in MS SQL Server)

Examples of SQL UDFs (+downloadable collection)
Here are some examples of SQL UDFs to get you started:
  • Below is a collection of some examples of potentially useful SQL UDFs, all using the "sp_f_" naming approach described here. Download the collection here.
    Check out the downloadable file for the full contents. If you want to share any useful UDFs you have created yourself, please contact me.
    Among others, this collection contains:

    • Various generic examples, like a function to format numbers with leading zeroes (sp_fmt_int_zero() and a function to convert a number of seconds to a time interval (sp_secs_to_interval() ). Also, there's a function to convert temperature in degrees Fahrenheit to Celsius (handy for myself when I'm in the USA).

    • I've always wanted a better version of the str_replace() built-in function in ASE, so I wrote the function sp_str_replace2(). This function lets you specify which strings to replace with an additional start and stop parameter (as opposed to str_replace() which replaces all occurrences):
      -- replace only the second space by a dash
      1> select dbo.sp_str_replace2('a b c d e f', ' ', '-', 2, 1)
      2> go
       --------------------
       a b-c d e f
      
    • In the Q4, 2009 ISUG Journal article "Decoding Captured “Missing Statistics” In ASE 15.0.3 ESD#1", I'm using a SQL UDF to decode captured missing statistics.The SQL code in this artically is actually a simplification which only works for tables with less than 256 columns. A better version is contained in this collection of UDFs.

    • Robert Munson contributed a UDF named sp_range, which can be used in group by and order by to get a statistical distribution of unknown data (see the comments in the SQL UDF code for more info and examples).
  • Joseph Gama wrote a set of 123 UDFs doing lots of useful things (convert an integer to Roman numerals for example, or convert a date from the Gregorian to the Hijri calendar).
    His source code can be downloaded here. Note that these were originally written for MS SQL Server, but they can be used without changes on ASE 15.0.2 (only exception: the replace() built-in must be changed to ASE's str_replace() in a few places).
    You may find it useful to prefix them all with "sp_f_" and create them in the sybsystemprocs database.
  • The quiz question from January 2008 has some tips about recursive UDFs.



Java User-Defined Functions (ASE 12.0+)


Using Java classes inside ASE may be useful for functionality which can not, or not efficiently, be implemented in pure T-SQL, or for functionality that must be identical to that in already existing Java classes in other system components.
This page contains some examples of how you can use the Java-in-the-Server features in ASE 12.0 and later.
  • ASE 12.0 and later supports user-defined functions that can be used in SQL statements. These user-defined functions must be implemented in Java, and are executed in a dedicated JVM inside the ASE server. This requires that you have a license for the ASE_JAVA option.
    Using Java classes inside ASE may be useful for functionality which can not, or only in a less efficient way, be implemented in pure T-SQL, or for functionality that must be idnentical to that in already existing Java classes in other system components.
    Below are some examples of how you can use the Java-in-the-Server features in ASE 12.0 and later.
  • In ASE 12.5, the create function allows a more elegant syntax for using the Java functions from SQL. Examples of this syntax are also included (note that this requires that the Java method is static).
  • In ASE 15.0, the Java-in-ASE option is available at no cost; see here.



Example of Java UDF 'NumToWord' ('check writing' function)
The Java class "NumToWord" is a so-called "check writing" function, which converts a number to its equivalent in English words (i.e. 123 becomes "one hundred and twenty-three").

Example (ASE 12.0):
1> declare @A NumToWord
2> select @A = new NumToWord()
3> select convert(varchar(120), @A>>NumToWord(1234567890))
4> go

--------------------------------------------------------
----------------------------------------------

one billion two hundred thirty-four million five hundred 
sixty-seven thousand eight hundred and ninety

(1 row affected)

Download NumToWord here -- see the header of the downloaded Java source file for further information about the class.




ASE 12.5: Using 'create function' for Java-based UDFs
Here's an example of how to use the SQL create function for Java-based UDFs in ASE 12.5 (the 12.0 syntax as illustrated above is also still possible in 12.5):
1> create function n2w(n int) 
2> returns java.lang.String
3> language java parameter style java 
4> external name "NumToWord.NumToWord"
5> go

1> select n2w(123456789)
2> go

--------------------------------------------------
one hundred twenty-three million four hundred fift

(1 row affected)

1> set stringsize 200 -- don't truncate Java strings
2> go
1> select n2w(123456789)
2> go

-------------------------------------------------------
--------------------------------------

one hundred twenty-three million four hundred fifty-six 
thousand seven hundred and eighty-nine

(1 row affected)




Another Example of Java UDF: 'DateTimeFormat'
The Java class "DateTimeFormat" allows you to use the data/time formatting capabilities available in Java. These are more versatile and flexible than the functionality provided by the standard T-SQL date/time formatting functions. For example, suppose you'd want to format a date value like this:

Today is Thursday July 11, day# 192 of 2002

Using the DateTimeFormat class, this can be done as follows:

1> declare @A DateTimeFormat
2> select @A = new DateTimeFormat()
3> select @A>>DateTimeFormat(getdate(), 
4>    "'Today is' EEEE MMMM dd', day#' DDD 'of' yyyy")
5> go

-------------------------------------------
Today is Thursday July 11, day# 192 of 2002

(1 row affected)

Or, using the more elegant syntax in ASE 12.5:
1> create function dtfmt(dt datetime, c varchar(100)) 
2> returns java.lang.String
3> language java parameter style java 
4> external name "DateTimeFormat.DateTimeFormat"
5> go

1> select dtfmt(getdate(), 
2>    "'Today is' EEEE MMMM dd', day#' DDD 'of' yyyy")
3> go

-------------------------------------------
Today is Thursday July 11, day# 192 of 2002

(1 row affected)

The equivalent functionality with only T-SQL functions would be this:

1> select "Today is " + datename(dw, getdate()) +
2> " " + datename(mm, getdate()) + 
3> " " + datename(dd, getdate()) + 
4> ", day# " + datename(dy, getdate()) + 
5> " of " + datename(yy, getdate())
6> go

-------------------------------------------
Today is Thursday July 11, day# 192 of 2002

(1 row affected)
Download DateTimeFormat here -- see the header of the downloaded Java source file for further information about the class, and for a list of the Java-provided date/time formatting options.




Installing Java classes into ASE
  • Next, downloading and uncompress the archive for the Java class you're interested in (NumToWord or DateTimeFormat)
  • The uncompressed archive will contain 3 files: the .java file with the source code, the .class file with the compiled bytecode, and the .jar file with the JAR (Java Archive) that needs to be installed into ASE.
  • Install the JAR into the server and database of choice:

    installjava -f NumToWord.jar -update -Uyour_login
    -Pyour_passwd -SYOUR_SERVER -Dyour_database


    (on Windows, use instjava instead of installjava)
  • See the header of the Java source file for further information about the class.
  • Important: when compiling Java classes yourself, make sure the Java version is 1.2.2 or 1.3.1 when you're running ASE 12.x or ASE 15.0.2 or earlier. When compiling with later versions (like JDK 1.4), you'll get a runtime error when trying to use the class. This is because the JVM inside ASE is not compatible with JDK 1.4 or later for ASE 15.0.2 and before.
    You can verify the version of your Java environment with the command
    java -version. Depending on your ASE version and platform, JDK 1.2.2 or 1.3.1 is included in your ASE environment (for example: Unix: $SYBASE/shared-1_0/JRE-1_3/bin; NT: %SYBASE%\shared-1_0\jre1.2.2\bin) so you could use that version of java.

    As of ASE 15.0.3, the Java VM inside ASE is compatible with Java 6.




ASE_JAVA licensing
To use Java in ASE 12.0 or 12.5, you must license (=buy) the ASE_JAVA option; by default, this is not enabled in a regular ASE production license (though it is included in the free Developer's Edition of ASE).
In ASE 15, Java-in-ASE is available at no cost, although you still need to use a license key (which can be downloaded from www.sybase.com/content/1037457/ASE150_java.lic.

In any version, the ASE_JAVA license key must be correctly installed in the license file.
To check whether this option is installed correctly, run the query select license_enabled("ASE_JAVA"). This will return "1" if the ASE_JAVA option is licensed and active, and "0" if not.
Note that, even when you have the ASE_JAVA license key available, "0" may still be returned by the above query because the license has not been properly installed, the license manager is not properly configured, has not been started or cannot be contacted by ASE. For information about setting up the license manager, see the chapter on SySAM in the ASE Installation Guide for your platform.

Java-related configuration settings
Once the ASE_JAVA option is enabled, you must set the configuration option 'enable java' to 1. This is a static option, so ASE must be restarted to activate the changed setting.
Note that this configuration option will be automatically set back to 0 during the ASE startup if the ASE_JAVA license appears not to be available.

In ASE 15.0.3 or later, a new Java VM was included with ASE. This VM supports Java 6, which is a huge improvement over the earlier VM (which is no longer available).
To enable Java in ASE 15.0.3+, you also need set the configuration option 'enable pci' to 1 (also static).
In addition, you must create a database named sybpcidb (at least 24 MB in size), and run the script scripts/installpcidb.
All this can be done with just one ASE reboot.

Note that when you try to load a Java class into ASE with inst[all]java without having set 'enable pci' to 1, you'll get this rather alarming-looking error message;
% installjava -f DateTimeFormat.jar -SSYB155 -Usa -P -Dmy_db -update
Server Message: SYB155 - Msg 10795, Level 16, State 1:
The file 'DateTimeFormat.class' contained in the jar does not represent a 
valid Java class file.
This error message does NOT mean there is something wrong with the Java class inside the .jar file; it just means 'enable pci' was not set to 1.


 
 This document is located at www.sypron.nl/udf.html