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 10:23:29 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=27&rev2=28

  == Functions vs. Procedures ==
  <<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 pretty much anywhere
a SQL expression is allowed, such as in the {{{SELECT}}} list or the {{{WHERE}}} clause. SQL
functions can also be invoked in triggers. 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 {{{CallableStatement}}}
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.apache.org/jira/browse/DERBY-551|Derby 10.2]], a java
procedure can also be invoked in a trigger.
+ '''Procedures''' are invoked with the {{{CALL}}} statement or the {{{CallableStatement}}}
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.apache.org/jira/browse/DERBY-551|Derby 10.2]], a java
procedure can also be invoked 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 database and the second, {{{SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY}}},
sets my 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 its corresponding Long value using Java]]
  
  === 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.
+ Below are a few code samples developed for Apache Derby by Students of Indian 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 queries.
  
  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: There is no column named: set_id."
  
- If the number of columns in the data is greater than the number of columns 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 exception: "ERROR 38000: The exception 'SQL Exception: Column
'COLUMN2' is either 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 columns 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 exception: "ERROR 38000: The exception 'SQL Exception: Column
'COLUMN2' is either 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."
  

Mime
View raw message