Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 91626 invoked from network); 4 Apr 2006 21:00:25 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 4 Apr 2006 21:00:25 -0000 Received: (qmail 61964 invoked by uid 500); 4 Apr 2006 21:00:23 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 61938 invoked by uid 500); 4 Apr 2006 21:00:23 -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 61923 invoked by uid 99); 4 Apr 2006 21:00:23 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Apr 2006 14:00:23 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of anil.samuel@gmail.com designates 66.249.82.195 as permitted sender) Received: from [66.249.82.195] (HELO xproxy.gmail.com) (66.249.82.195) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Apr 2006 14:00:22 -0700 Received: by xproxy.gmail.com with SMTP id s9so1003208wxc for ; Tue, 04 Apr 2006 14:00:02 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=AfZPnlEXMclrSZUUIwM4qSIZi7NHuwVFCULrcf+x/wsicpKLjiSOuyYAaQTfF3bLPzc5Uor/9Bh9yLWJ1GQtpXtIAkS3Cx0saVDfU9GtsuiQBT79wDp2uQBi9IDnl+UFi25+H5uKrNrii4ZAYrq9ZfvlTHw9yuFXQoh70uMNDDQ= Received: by 10.70.17.9 with SMTP id 9mr675767wxq; Tue, 04 Apr 2006 14:00:01 -0700 (PDT) Received: by 10.70.24.4 with HTTP; Tue, 4 Apr 2006 14:00:01 -0700 (PDT) Message-ID: <3535996c0604041400j3c72e495t569472ea3fbaac1d@mail.gmail.com> Date: Wed, 5 Apr 2006 07:00:01 +1000 From: "Anil Samuel" To: derby-user@db.apache.org Subject: Re: unable to execute procedure In-Reply-To: <3535996c0604032059j52de8f50s7073f167d816e9d2@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11392_5016672.1144184401189" References: <3535996c0604032059j52de8f50s7073f167d816e9d2@mail.gmail.com> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_11392_5016672.1144184401189 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I also did 3. CREATE PROCEDURE dtp.TOTAL_LEAVE_FOR_TYPE ( EMPLOYEE CHAR(80), LEAVE_TYPE_ID INT, OUT TOTAL_LEAVE INT) LANGUAGE JAVA EXTERNAL NAME 'oracle.dtp.derby.sample.LeaveHelper.TotalLeaveForTyp= e ' PARAMETER STYLE JAVA; Anthos On 4/4/06, Anil Samuel wrote: > > Hi > > 1. I created a Java class as below > package oracle.dtp.derby.sample; > > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.PreparedStatement ; > import java.sql.ResultSet ; > import java.sql.SQLException; > > public class LeaveHelper { > > public static int TotalLeaveForType(String employee, int leaveTypeId) > { > int total =3D 0; > try { > Connection con =3D DriverManager.getConnection( > "jdbc:default:connection"); > PreparedStatement ps =3D con.prepareStatement("select > TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=3D" + employee + " an= d > LEAVE_TYPE_ID=3D" + leaveTypeId); > ResultSet rs =3D ps.executeQuery(); > ps.close(); > con.close(); > total =3D rs.getInt(1); > } > catch (SQLException e) > { > e.printStackTrace (); > } > > return total; > } > } > > 2. Compiled and created dtpLeaveApp.jar with this class. > 3 . Added this jar to derby by > CALL SQLJ.install_jar( > 'file:/tmp/dtpLeaveApp.jar', > 'dtp.LeaveApp_jar', > 0 > ); > 4. Tried to execuet this procedure by doing > CALL dtp.TOTAL_LEAVE_FOR_TYPE('TOM',3); > but it results > ERROR 42Y03: 'SQLJ.INSTALL_JAR' is not recognized as a function or > procedure. > > How do I get this working ? > > Regards > Anthos > -- > Anil Samuel < anil.samuel@oracle.com> > Senior Technical Member > Product Development > Oracle Corporation > -- Anil Samuel Senior Technical Member Product Development Oracle Corporation ------=_Part_11392_5016672.1144184401189 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I also did
3. CREATE PROCEDURE dtp.TOTAL_LEAVE_FOR_TYPE (
  EMPL= OYEE CHAR(80),
  LEAVE_TYPE_ID INT,
  OUT TOTAL_LEAVE INT)<= br>        LANGUAGE JAVA
  =       EXTERNAL NAME 'oracle.dtp.derby.sample.Leave= Helper.TotalLeaveForType '
        PARAMETER STYLE JAVA;
Anthos

On 4/4/06, Anil Samuel < anil.samuel@gmail.com> wrote:
Hi

1. I created a Java class as below=
package oracle.dtp.derby.sample;

import java.sql.C= onnection;
import java.sql.DriverManager;
import java.sql.PreparedSta= tement ;
import java.sql.ResultSet ;
import java.sql.SQLException;

public class LeaveHelper {
    public static int TotalLeaveForType(String employee, in= t leaveTypeId)
    {
      &n= bsp; int total =3D 0;
        try {            Connec= tion con =3D=20 DriverManager.getConnection( "jdbc:default:connection");
 = ;           PreparedState= ment ps =3D con.prepareStatement("select TOTAL_TAKEN from DTP.LEAVE_AP= PROV where EMPLOYEE_ID=3D" + employee + " and LEAVE_TYPE_ID=3D&qu= ot; + leaveTypeId);
            Resu= ltSet rs =3D ps.executeQuery();
      &nbs= p;     ps.close();
     &nb= sp;      con.close();
    &= nbsp;       total =3D rs.getInt(1);
 =        }
     &nb= sp;  catch (SQLException e)
      &nb= sp; {
            e.pr= intStackTrace ();
        }
   &n= bsp;   
        retur= n total;
    }
}

2. Compiled and created dtpLea= veApp.jar with this class.
3 . Added this jar to derby by
CALL SQLJ.i= nstall_jar(
    'file:/tmp/dtpLeaveApp.jar',
    'dtp.LeaveApp_jar',
    0
);4. Tried to execuet this procedure by doing
CALL dtp.TOTAL_LEAVE_FOR_TY= PE('TOM',3);
but it results
ERROR 42Y03: 'SQLJ.INSTALL_JAR' is not re= cognized as a function or procedure.

How do I get this working ?

Regards
Anthos
--
Anil Samuel < anil.samuel@oracle.com>
Senior Technical Member
Product Develo= pment
Oracle Corporation



--
Anil Samue= l <anil.samuel@oracle.com>
Senior Technical Member
Product Development
Oracle Corporation ------=_Part_11392_5016672.1144184401189--