Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
From: Apache Wiki <wikidiffs@apache.org>
To: Apache Wiki <wikidiffs@apache.org>
Date: Mon, 02 Apr 2012 10:23:29 -0000
Message-ID: <20120402102329.19665.55263@eos.apache.org>
Subject: 
 =?utf-8?q?=5BDb-derby_Wiki=5D_Update_of_=22DerbySQLroutines=22_by_gisunda?=
 =?utf-8?q?r?=
Auto-Submitted: auto-generated

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=3Ddiff&rev1=3D27&re=
v2=3D28

  =3D=3D Functions vs. Procedures =3D=3D
  <<Anchor(FunctionsVsProcedures)>>
  =

- There is overlap between SQL Functions and Procedures,  but each can also=
 do things the other cannot and the syntax for invoking each is different.
+ There is overlap between SQL Functions and Procedures, but each can also =
do things the other cannot and the syntax for invoking each is different.
  =

  '''SQL functions''' execute as part of a SQL statement and can be used pr=
etty much anywhere a SQL expression is allowed, such as in the {{{SELECT}}}=
 list or the {{{WHERE}}} clause. SQL functions can also be invoked in trigg=
ers. However, they are read-only -- they cannot modify data in the database.
  =

@@ -19, +19 @@

  -------------
  lowercase me!
  }}}
- '''Procedures''' are invoked with the {{{CALL}}} statement or the {{{Call=
ableStatement}}} method in a Java client application.  Procedures support {=
{{IN}}}, {{{OUT}}}, and {{{INOUT}}} parameters. If the procedure has just {=
{{IN}}} parameters, you can invoke it anywhere with the {{{CALL}}} statemen=
t, including in ij. If the procedure has {{{OUT}}} or {{{INOUT}}} parameter=
s, it can't be invoked from ij, it must be invoked from a client applicatio=
n using the {{{CallableStatement}}} method. Starting in [[http://issues.apa=
che.org/jira/browse/DERBY-551|Derby 10.2]], a java procedure can also be in=
voked in a trigger.
+ '''Procedures''' are invoked with the {{{CALL}}} statement or the {{{Call=
ableStatement}}} method in a Java client application. Procedures support {{=
{IN}}}, {{{OUT}}}, and {{{INOUT}}} parameters. If the procedure has just {{=
{IN}}} parameters, you can invoke it anywhere with the {{{CALL}}} statement=
, including in ij. If the procedure has {{{OUT}}} or {{{INOUT}}} parameters=
, it can't be invoked from ij, it must be invoked from a client application=
 using the {{{CallableStatement}}} method. Starting in [[http://issues.apac=
he.org/jira/browse/DERBY-551|Derby 10.2]], a java procedure can also be inv=
oked in a trigger.
  =

  Here's an example of invoking two built-in procedures using {{{ij}}}. The=
 first, {{{SQLJ.install_jar}}}, loads my 'myStuff.jar' jar file into the da=
tabase and the second, {{{SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY}}}, sets m=
y database class path to include that jar:
  =

@@ -67, +67 @@

   * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200501.mbox/<=
41DAC70D.7000002@debrunners.com>|A function that converts a Timestamp to it=
s corresponding Long value using Java]]
  =

  =3D=3D=3D A Few Working Function Examples =3D=3D=3D
- Below are a few code samples developed for Apache Derby by Students of In=
dian Institute of Technology Guwahati. Users can download the source code f=
or their use. Users can contact the developers mentioned below for function=
 specific queries.
+ Below are a few code samples developed for Apache Derby by Students of In=
dian Institute of Technology Guwahati under the guidance of Girish Sundaram=
 ( gisundar@in.ibm.com ). Users can download the source code for their use.=
 Users can contact the developers mentioned below for function specific que=
ries.
  =

  Developers Involved:
  =

@@ -846, +846 @@

  =

  The table and field names must be given in ALL UPPER CASE. Otherwise you =
will get "ERROR XIE0M: Table 'property' does not exist." or "ERROR XIE08: T=
here is no column named: set_id."
  =

- If the number of columns in the data is greater than the number of column=
s in the insertColumns list then the extra columns are ignored. However, if=
 the reverse is true then you get this ugly and nearly meaningless SQL exce=
ption: "ERROR 38000: The exception 'SQL Exception: Column 'COLUMN2' is eith=
er not in any table in the FROM list or appears within a join specification=
 and is outside the scope of the join specification or appears in a HAVING =
clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE =
 statement then 'COLUMN2' is not a column in the target table.' was thrown =
while evaluating an expression."
+ If the number of columns in the data is greater than the number of column=
s in the insertColumns list then the extra columns are ignored. However, if=
 the reverse is true then you get this ugly and nearly meaningless SQL exce=
ption: "ERROR 38000: The exception 'SQL Exception: Column 'COLUMN2' is eith=
er not in any table in the FROM list or appears within a join specification=
 and is outside the scope of the join specification or appears in a HAVING =
clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE =
statement then 'COLUMN2' is not a column in the target table.' was thrown w=
hile evaluating an expression."
 =20