db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "DerbySQLroutines" by gisundar
Date Mon, 02 Apr 2012 08:30:54 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The "DerbySQLroutines" page has been changed by gisundar:
http://wiki.apache.org/db-derby/DerbySQLroutines?action=diff&rev1=25&rev2=26

  The [[http://db.apache.org/derby/papers/fortune_tut.html|Apache Derby Fortune Server tutorial]]
from ApacheCon 2004 shows how to create three SQL functions:
  
   * {{{tutRand}}} generates a random integer using the Jakarta Math Library.
+ 
   * {{{tutMatch}}} and {{{tutReplace}}} perform regular expression search and replace using
Jakarta Regexp
  
  The [[https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/|Derby
functional tests]] are an excellent source for function samples.
@@ -58, +59 @@

  Examples from the Derby mail archives include:
  
   * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200412.mbox/<41C3584A.3080302@Source-Zone.Org>|A
function without any parameters that returns an integer]]
+ 
   * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/<42A0E031.60003@bristowhill.com>|A
function that takes two values, adds them together and returns the result; also shows how
to invoke a function in a trigger]]
+ 
   * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/<43418926.6010407@sun.com>|A
function that given a Date, returns the day of week]]
+ 
   * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200501.mbox/<41DAC70D.7000002@debrunners.com>|A
function that converts a Timestamp to its corresponding Long value using Java]]
  
- === System.getProperty as a function ===
- Here's a very simple example of how to define and use the JDK's {{{System.getProperty()}}}
method from a Derby SQL function:
+ === A Few Working Function Examples ===
+ Below are a few code samples developed for Apache Derby by Students of Indian Institute
of Technology Guwahati. Users can download the source code for their use. Users can contact
the developers mentioned below for function specific queries.
  
+ Developers Involved:
- {{{
- ij> create function getSystemProperty(name varchar(128)) returns varchar(128) language
java external name 'java.lang.System.getProperty' parameter style java no sql;
- 0 rows inserted/updated/deleted
- ij> values getSystemProperty('derby.system.home');
- 1
- ---------------------------------------------------------------------------------------------
- NULL
  
- 1 row selected
- ij> values getSystemProperty('user.dir');
- 1
- ---------------------------------------------------------------------------------------------
- /tmp
+  1. Sreedish PS ( sreedish@iitg.ernet.in )
+ 
+  1. Swarup K Malik ( m.swarup@iitg.ernet.in )
+ 
+  1. Rinku Das ( rinku@iitg.ernet.in )
+ 
+ Project Guide and Technical Lead:
+ 
+  1. Mr. Girish Sundaram ( gisundar@in.ibm.com )
+ 
+  1. Prof. Dr. Pinaki Mitra ( pinaki@iitg.ernet.in )
+ 
+ Function Code included in the Attachment:
+ 
+ {{{
+ Function name   :dayname
+ 
+ input           :string, string
+ return  :string
+ 
+ This function takes an input string as date and a string as format of the date and returns
a
+ 
+ mixed case character string containing the name of the day for the day portion of the argument
+ 
+ based on the locale when the database was started.
+ It uses previously defined function “validateADate” to validate the date first.
+ It returns null if not valid;
+ If the argument is null, the result is null.
+ }}}
+ {{{
+ Function name   :gmtdate
+ 
+ input           :void
+ return  :string
+ 
+ This function returns a string value representing the current date/time in the GMT time
zone.
+ 
+ Create function statement:
+ create function Gmt_date() returns varchar(50) parameter style java no sql language java
+ 
+ external name 'apache.Gmtdate.gmtdate';
+ }}}
+ {{{
+ Function name   :localtimezone
+ 
+ input           :void
+ return  :string
+ 
+ This function returns an integer as interval value representing the local time zone displacement
+ 
+ from GMT .
+ 
+ Create function statement:
+ create function Local_TimeZone() returns varchar(50) parameter style java no sql language
java
+ 
+ external name 'apache.Local_Timezone.localtimezone';
+ }}}
+ {{{
+ Function name   :monthname
+ 
+ input           :string, string
+ return  :string
+ This function takes an input string as date and a string as format of the date and returns
a
+ 
+ mixed case character string containing the name of the month for the month portion of the
+ 
+ argument based on the locale when the database was started.
+ It uses previously defined function “validateADate” to validate the date first.
+ It returns null if not valid;
+ If the argument is null, the result is null.
+ 
+ Create function statement:
+ create function monthname(dt varchar(20), format2 varchar(20)) returns varchar(10) parameter
+ 
+ style java no sql language java external name 'apache.Monthname.monthname';
+ }}}
+ {{{
+ Function name   :number_format
+ 
+ input           :double, int, int, int, int, boolean
+ return  :string
+ 
+ This function returns a string representing a number with grouped thousands with specified
+ 
+ proper input.
+ 
+ Create function statement:
+ create function number_format(number double, minfracdgt integer, maxfracdgt integer, minintdgt
+ 
+ integer, maxintdgt integer, grouping boolean ) returns varchar(50) parameter style java
no sql
+ 
+ language java external name 'apache.Number_format.number_format';
+ }}}
+ {{{
+ Function name   :quarter
+ 
+ input           :string, string
+ return  :int
+ 
+ This function takes an input string as date and a string as format of the date and returns
an
+ 
+ integer value in the range 1 to 4, representing the quarter of the year for the date specified
+ 
+ in the argument.
+ It uses previously defined function “validateADate” to validate the date first.
+ It returns zero(0) if not valid.
+ If the argument is null, the result is zero(0).
+ 
+ Create function statement:
+ create function quarter(dt varchar(20), format2 varchar(20)) returns integer parameter style
+ 
+ java no sql language java external name 'apache.Quarter.quarter';
+ }}}
+ {{{
+ Function name   :replaceAt
+ 
+ input           :string, integer, string
+ return  :string
+ 
+ This function takes an input string and a string as replacement and an integer as a specified
+ 
+ position and returns a string.
+ If input string is null, it returns null.
+ 
+ Create function statement:
+ create function replace_at(str varchar(20),  pos integer, r varchar(10)) returns varchar(20)
+ 
+ parameter style java no sql language java external name
+ 
+ 'apache.ReplaceAtSpecifiedPos.replaceAt';
+ }}}
+ {{{
+ Function name   :replacewithin
+ 
+ input           :string, integer, integer, string
+ return  :string
+ 
+ This function takes an input string and a string as replacement and two integers to specify
the
+ 
+ position within which replacement takes place and returns a string.
+ If input string is null, it returns null.
+ 
+ Create function statement:
+ create function replace_within(str varchar(20),  startpos integer, endpos integer, r varchar
+ 
+ (10)) returns varchar(20) parameter style java no sql language java external name
+ 
+ 'apache.ReplaceWithin.replacewithin';
+ }}}
+ {{{
+ Function name   :sign
+ input           :double
+ return  :integer
+ 
+ This function takes a double as input and returns integer value 1, 0 and -1 if the number
is
+ 
+ positive, zero or negative respectively.
+ 
+ Create function statement:
+ create function sign_of_a_number(number double) returns integer parameter style java no
sql
+ 
+ language java external name 'apache.Sign.sign';
+ }}}
+ {{{
+ Function name   :stringreverse
+ 
+ input           :string
+ return  :string
+ 
+ This function takes a string as input and returns the reverse of the string. If string is
null
+ 
+ it returns null.
+ 
+ Create function statement:
+ create function reverse_a_string(arg varchar(20)) returns varchar(20) parameter style java
no
+ 
+ sql language java external name 'apache.StringReverse.stringreverse';
+ }}}
+ {{{
+ Function name   :truncate
+ 
+ input           :double, integer
+ return  :double
+ 
+ This function takes a double and an integer as inputs and returns double truncated to integer
+ 
+ places to the right of the decimal point if integer is positive, or to the left of the decimal
+ 
+ point if integer is zero or negative.
+ 
+ Create function statement:
+ create function truncate(value double, places integer) returns double parameter style java
no
+ 
+ sql language java external name 'apache.Truncate.truncate';
+ }}}
+ {{{
+ Function name   :validateADate
+ 
+ input           :string, string, boolean
+ return  :boolean
+ 
+ This function takes a string for a date, another string for format of date and a boolean
to set
+ 
+ lenient as inputs and returns boolean  true if the specified date is valid, and false otherwise.
+ 
+ Create function statement:
+ create function validate_a_date(maybeDate varchar(20), format1 varchar(20), lenient boolean
)
+ 
+ returns boolean parameter style java no sql language java external name
+ 
+ 'apache.ValidateAGivenDate.validateADate';
+ }}}
+ {{{
+ Function name   :week_of_month
+ 
+ input           :string, string
+ return  :int
+ 
+ This function takes an input string as date and a string as format of the date and returns
an
+ 
+ integer as the week of the month of the argument in range 1-5. The week starts with Sunday.
+ It uses previously defined function “validateADate” to validate the date first.
+ It returns zero(0) if not valid;
+ 
+ Create function statement:
+ create function week_of_month(dt varchar(20), format2 varchar(20)) returns integer parameter
+ 
+ style java no sql language java external name 'apache.Weekofmonth.week_of_month';
+ }}}
+ {{{
+ Function name   :week_of_year
+ 
+ input           :string, string
+ return  :int
+ 
+ This function takes an input string as date and a string as format of the date and returns
an
+ 
+ integer as the week of the year of the argument in range 1-52. The week starts with Sunday.
+ It uses previously defined function “validateADate” to validate the date first.
+ It returns zero(0) if not valid;
+ 
+ Create function statement:
+ create function week_of_year(dt varchar(20), format2 varchar(20)) returns integer parameter
+ 
+ style java no sql language java external name 'apache.Weekofyear.week_of_year';
+ }}}
+ {{{
+ Function name   :wordWrap
+ 
+ input           :string, integer
+ return  :string
+ 
+ This function takes a string and an integer as inputs and returns a string after wrapping
the
+ 
+ input string into new lines when it reaches a specified input length.
+ If input string is null, it returns null.
+ 
+ Create function statement:
+ create function wrap_a_word(string varchar(30), length integer) returns varchar(50) parameter
+ 
+ style java no sql language java external name 'apache.Wordwrap.wordWrap';
+ }}}
+ {{{
+ Function name   :anagram
+ 
+ input: String,String
+ return: boolean
+ 
+ This function takes two strings as input and check whether the first string is a permutation
of
+ 
+ the second one or not. Based on this the function will return true or false.
+ 
+ Create function statement:
+ create function Anagram(str1 varchar(20), str2 varchar(20)) returns boolean parameter style
java
+ 
+ no sql language java external name 'Anagram.findAnagram';
+ }}}
+ {{{
+ Function name   :daysBetween
+ 
+ input:Date,Date
+ return: int
+ 
+ This function takes two instances of Dates as input and returns the number of days between
them.
+ }}}
+ {{{
+ Function name   :findDayOfTheWeek
+ 
+ input:int,int,int
+ return: int
+ 
+ This method takes three integers(date, month, and year) as input arguments and returns values
+ 
+ from 0 to 6 indicating from Sunday to Saturday.
+ }}}
+ {{{
+ Function name   :findExpm
+ 
+ input:Double
+ return:Double
+ 
+ This function takes a double value as input and returns e^input -1 . This functions is very
+ 
+ useful while calculating values when they are very much close to zero and they will be truncated
+ 
+ because of precision issues.
+ }}}
+ {{{
+ Function name   :findDate
+ 
+ input: int, int, int, int
+ return: int
+ 
+ This functions takes four integer arguments as parameters, which are respectively values
+ 
+ corresponding to date, month, year and count. Count indicates the number of days to moved
+ 
+ forward. Example: If you want to find the date which is 100 days from 11/03/2011, make a
call
+ 
+ like FindDate(11,3,2011,100). Returns 1 if function executred perfectly.
+ }}}
+ {{{
+ Function name   :findIsPrime
+ 
+ input:int
+ return:boolean
+ 
+ This function takes an integer as input and returns true or false based on the primality
of the
+ 
+ number.
+ }}}
+ {{{
+ Function name   :findMD5
+ 
+ input:String
+ return:String
+ 
+ This function takes a string as input and calculates its MD5 checksum and returns the checksum
+ 
+ value as a string.
+ }}}
+ {{{
+ Function name   :findNextPrime
+ 
+ input:int
+ return:int
+ 
+ This function finds the next prime number followed by the given number
+ }}}
+ {{{
+ Function name   :findNumberofPrimes
+ 
+ input: int
+ return: int
+ 
+ This function returns an integer and takes an integer as input. The return value indicates
the
+ 
+ number of prime numbers which are less than or equal to the passed argument.
+ }}}
+ {{{
+ Function name   :shuffleString
+ 
+ input: String
+ return: string
+ 
+ This functions takes a string as input and returns a randomly shuffled permutation of the
+ 
+ string. This shuffling is done based on several random numbers generated.
+ }}}
+ {{{
+ Function name   :findSimilarText
+ 
+ input: string,string
+ return: double
+ 
+ This function takes two strings as input and based on Jaccard index, this function calculates
a
+ 
+ double value in the range from 0 to 100 indicating how much similar these strings are. Two
+ 
+ identical strings will produce a return value of 100 and two completely disjoint strings
will
+ 
+ produce a return value of 0.
+ }}}
+ {{{
+ Function name   :SpaceBuilder
+ 
+ input: int
+ return:int
+ 
+ This function returns a string composed only of spaces and the number of spaces is.
+ }}}
+ {{{
+ Function name   :MethodStripTag
+ 
+ input:string
+ return:string
+ 
+ This method takes a string as inpuit and based on a regular expression, it strips of all
the
+ 
+ tags such as HTML tags from the string and returns the string wihtout any tags.
+ }}}
+ {{{
+ Function name   :strSet
+ 
+ input:string , char
+ return:String
+ 
+ This function takes a string and character as input and returns a string re writed with
that
+ 
+ input argument.
+ }}}
+ {{{
+ Function name   :convert
+ 
+ input:string
+ return:String
+ 
+ This function takes a string as input and returns a string with the first letter changed
to
+ 
+ uppercase. This function can be made useful in cases like formatting names etc.
+ }}}
+ {{{
+ Function name   :bitAnd
+ 
+ input           : int , int
+ return          :int
+ 
+ The bitAnd numeric function performs a bitwise AND on the binary
+ representation of two numbers.
+ 
+ Create function statement:
+ 
+ create function BitAnd(a int,b int) returns int parameter style java no sql language java
+ 
+ external name 'BitAnd.bitAnd';
+ }}}
+ {{{
+ Function name   :bitNot
+ 
+ input           : int
+ return          :int
+ 
+ The bitNot numeric function performs a bitwise complement on the
+ binary representation of a number.
+ 
+ Create function statement:
+ 
+ create function BitNot(a int) returns int parameter style java no sql language java external
+ 
+ name 'BitNot.bitNot';
+ }}}
+ {{{
+ Function name   :bitOr
+ 
+ Input: int , int
+ Return: int
+ 
+ The BITOR numeric function performs a bitwise OR on the binary
+ representation of two numbers.
+ 
+ Create function statement:
+ 
+ create function BitOr(a int,b int) returns int parameter style java no sql language java
+ 
+ external name 'BitOr.bitOr';
+ }}}
+ {{{
+ Function name   :bitXor
+ 
+ Input: int , int
+ Return: int
+ 
+ The BITXOR numeric function performs a bitwise XOR on the binary
+ representation of two numbers.
+ 
+ Create function statement:
+ 
+ create function BitXor(a int,b int) returns int parameter style java no sql language java
+ 
+ external name 'BitXor.bitXor';
+ }}}
+ {{{
+ Function name   :contain
+ 
+ Input: String , String
+ Return: boolean
+ 
+ contain returns TRUE if the SearchExpression is present within the
+ SourceExpression, otherwise it returns FALSE.
+ 
+ Create function statement:
+ 
+ create function Contains(SourceExpression varchar(20),SearchExpression varchar(20)) returns
+ 
+ boolean parameter style java no sql language java external name 'Contains.contain';
+ }}}
+ {{{
+ Function name   :endsWith
+ 
+ input           :String , String
+ return          :boolean
+ 
+ endsWith returns TRUE if the SearchExpression is present at the end of SourceExpression,
+ 
+ otherwise it returns FALSE.
+ 
+ Create function statement:
+ 
+ create function EndsWith(SourceExpression varchar(20),SearchExpression varchar(20)) returns
+ 
+ boolean parameter style java no sql language java external name 'EndsWith.endswith';
+ }}}
+ {{{
+ Function name   :left
+ 
+ input           :String , int
+ return          :String
+ 
+ LEFT returns a string consisting of the source string truncated to the length given by the
+ 
+ length expression. The truncation discards the final characters of the source string.
+ The result is of the same type as the source string. If the length is negative or zero,
a zero
+ 
+ length string is returned. If either parameter is NULL, the result is NULL.
+ 
+ Create function statement:
+ 
+ create function Left(SourceExpression varchar(20),len int) returns boolean parameter style
java
+ 
+ no sql language java external name 'Left.left';
+ }}}
+ {{{
+ Function name   :overLay
+ 
+ input           :String , String ,int , int
+ return          :String
+ 
+ If any of the inputs are NULL, the OVERLAY function returns a NULL.The embedded_string replaces
+ 
+ the length characters in string starting at character position start. If the length is not
+ 
+ specified, then the embedded_string will replace all characters after start in string.
+ 
+ Create function statement:
+ 
+ create function OverLay(SourceString varchar(50), EmbeddedString varchar(50),  FromStart
int,
+ 
+ ForLength int)) returns varchar(50) parameter style java no sql language java external name
+ 
+ 'OverLay.overLay';
+ }}}
+ {{{
+ Function name   :position
+ 
+ input           :String , String ,int , int
+ return          :int
+ 
+ POSITION returns an integer giving the position of one string (SearchExpression) in a second
+ 
+ string (SourceExpression). A position of one corresponds to the first character of the source
+ 
+ string.
+ 
+ Create function statement:
+ 
+ create function Position(String SearchExpression , String SourceExpression) returns int)
+ 
+ parameter style java no sql language java external name 'Position.position';
+ }}}
+ {{{
+ Function name   :power
+ 
+ input           :double , double
+ return          :double
+ 
+ POWER returns the given value raised to the given power. The parameters can be any built-in
+ 
+ numeric data type. The result is FLOAT unless any parameter is NULL.
+  An exception occurs, if the value is either:
+ Zero and the power is negative, or
+ Negative and the power is not an integer. In these cases it will return -1.
+ 
+ 
+ Create function statement:
+ 
+ create function Power( a double, b double) returns double parameter style java no sql language
+ 
+ java external name 'Power.power';
+ }}}
+ {{{
+ Function name   :replace
+ 
+ Input: String ,String , String
+ Return:String
+ 
+ REPLACE replaces parts of a string with supplied substrings.
+ 
+ 
+ Create function statement:
+ 
+ create function Replace( SourceStringExpression varchar(50),  SearchStringExpression varchar
+ 
+ (50),  ReplaceStringExpression varchar(50)) returns varchar(50) parameter style java no
sql
+ 
+ language java external name 'Replace.replace';
+ }}}
+ {{{
+ Function name   :replicate
+ 
+ Input: String , int
+ Return:String
+ 
+ REPLICATE returns a string consisting of the pattern string given by PatternStringExpression
+ 
+ repeated the number of times given by CountNumericExpression.
+ 
+ Create function statement:
+ 
+ create function Replace( PatternStringExpression varchar(50),  CountNumericExpression int
)
+ 
+ returns varchar(50) parameter style java no sql language java external name
+ 
+ 'Replicate.replicate';
+ }}}
+ {{{
+ Function name   :right
+ 
+ Input: String , int
+ Return:String
+ 
+ RIGHT returns a string consisting of the source string truncated to the length given by
the
+ 
+ length expression. The truncation discards the initial characters of the source string.
The
+ 
+ result is of the same type as the source string. If the length is negative or zero, a zero
+ 
+ length string is returned. If either parameter is NULL, the result is NULL.
+ 
+ 
+ Create function statement:
+ 
+ create function Right( SourceExpression varchar(50),  len int ) returns varchar(50) parameter
+ 
+ style java no sql language java external name 'Right.right';
+ }}}
+ {{{
+ Function name   :startsWith
+ 
+ Input: String , String
+ return:boolean
+ 
+ STARTSWITH returns TRUE if SourceExpression begins with SearchExpression, otherwise it returns
+ 
+ FALSE.
+ 
+ 
+ Create function statement:
+ 
+ create function Right(SourceExpression varchar(50),  SearchExpression varchar(50) ) returns
+ 
+ boolean parameter style java no sql language java external name 'StartsWith.startswith';
  }}}
  Note that for this to work, Derby's security policy must allow System.getProperty calls
to be made to retrieve the properties in question.
  
@@ -146, +800 @@

  Items to note:
  
   * The !ResultSets are returned to the application, through the !CallableStatement, in the
order they were created.
+ 
   * The !ResultSet must be open and generated from the default connection (jdbc:default:connection)
in order to be returned. Any other !ResultSets will be ignored.
+ 
   * Closing the Statement that created the !ResultSet within the procedure's method will
close the !ResultSet, so don't do that! However closing the connection is ok.
+ 
   * The !PreparedStatement's or other Statement objects in the method must be created in
the body of the method, do not cache them as static variables, that will not work. So unlike
a JDBC client application a Java method for a procedure or a function cannot hold onto JDBC
objects after it completes.
+ 
   * The database engine (Derby) creates the one element !ResultSet arrays that hold the returned
!ResultSets.
+ 
   * Returning less !ResultSets than defined by the DYNAMIC RESULT SETS clause is ok, only
fill in the number of arrays you need. So in this example to only return one !ResultSet either
just set data1[0] to the !ResultSet or data2[0] to the !ResultSet. Remember the order returned
through !CallableStatement is driven by the order of creation, not the order of the method's
parameters.
  
  == The power of Java in SQL ==
@@ -158, +817 @@

  The ability to write functions and procedures in Java brings the complete set of Java apis
into your SQL environment as server side logic. A function or procedure may call any of the
standard Java libraries, any of the standard Java extensions, or other third party libraries.
Examples are:
  
   * SendEmailRoutine Sending e-mail from a database trigger with !JavaMail API
+ 
   * please add others
   * or even just ideas of libraries that would be useful in Derby
  

Mime
View raw message