Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 42521 invoked from network); 1 Mar 2006 00:16:45 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 1 Mar 2006 00:16:45 -0000 Received: (qmail 88395 invoked by uid 500); 1 Mar 2006 00:16:43 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 88361 invoked by uid 500); 1 Mar 2006 00:16:43 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 88350 invoked by uid 99); 1 Mar 2006 00:16:43 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 28 Feb 2006 16:16:43 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of jta@bristowhill.com designates 66.75.162.133 as permitted sender) Received: from [66.75.162.133] (HELO ms-smtp-01-eri0.socal.rr.com) (66.75.162.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 28 Feb 2006 16:16:41 -0800 Received: from [192.168.15.53] (cpe-204-210-23-212.san.res.rr.com [204.210.23.212]) by ms-smtp-01-eri0.socal.rr.com (8.13.4/8.13.4) with ESMTP id k210GJil027210 for ; Tue, 28 Feb 2006 16:16:20 -0800 (PST) Message-ID: <4404E7D3.8020207@bristowhill.com> Date: Tue, 28 Feb 2006 16:16:19 -0800 From: "Jean T. Anderson" Reply-To: jta@bristowhill.com User-Agent: Mozilla Thunderbird 1.0.7-1.1.fc3 (X11/20050929) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Derby Discussion Subject: Re: Create function problem References: <4404CBA0.1090801@brighton.ac.uk> <4404CFAB.8020507@bristowhill.com> <4404E53D.1050605@apache.org> In-Reply-To: <4404E53D.1050605@apache.org> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Symantec AntiVirus Scan Engine X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Daniel John Debrunner wrote: > Jean T. Anderson wrote: >> ... >>Derby maps a SQL INTEGER to Java int (a primitive) and passing SQL NULLs >>isn't supported for primitive types. There's an FAQ on this at >>http://db.apache.org/derby/faq.html#null_args that also points to the >>mail list topic that fed the FAQ. > > > That FAQ is a bit out of date (I think, I can't see it now, get timeouts > connecting to the site). > > There are two options for a function: > > 1) Declare the function RETURNS NULL ON NULL INPUT, this means that if > any parameter is NULL the result is NULL and the Java method is never > called. There are a couple of bugs outstanding on this which will be > fixed in 10.2. > http://issues.apache.org/jira/browse/DERBY-479 > http://issues.apache.org/jira/browse/DERBY-1030 > > CREATE FUNCTION DoubleFormat (value FLOAT, > places INTEGER) > RETURNS FLOAT > PARAMETER STYLE Java > LANGUAGE Java > RETURNS NULL ON NULL INPUT > EXTERNAL NAME 'utils.Utils.derbyDouble' > > 2) In the external name clause define the Java method signature, e.g. > > CREATE FUNCTION DoubleFormat (value FLOAT, > places INTEGER) > RETURNS FLOAT > PARAMETER STYLE Java > LANGUAGE Java > EXTERNAL NAME > 'utils.Utils.derbyDouble(java.lang.Float,java.lang.Integer)' > > This will then resolve to the method with that signature (assuming it > exists). > > Method signature support was added in 10.1. thanks! I'll update the FAQ. -jean