[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Executing a stored procedure

Subject: Executing a stored procedure
From: carlos.gonzalez at bancoval.es (Carlos Gonzalez)
Date: Tue May 20 02:32:01 2003
References: <C9DF8DB2-89FA-11D7-84B7-0003936C1E28@effigent.com>
--Boundary_(ID_A9sxNctxGQk/2gjq93jmRw)
Content-type: text/plain; format=flowed; charset=us-ascii
Content-transfer-encoding: QUOTED-PRINTABLE

We have had the same nightmare with Sybase + WO + Stored Procedures=
=20
since we decided to migrate from WO4.5 to WO5.x. We thought that with=
=20
WO5.2, as it comes with a SybasePlugIn, this issue would have been=
=20
solved but no way. So we tried this workaround: get the=20
java.sql.Connection object and execute the stored procedure using a=
=20
CallableStatement, and then if needed take the resultset into an arra=
y=20
of EOEnterpriseObjects to be treated seamlessly by WebObjects. Here i=
s=20
some sample code which by the way I got from a webobjects-dev thread.=
=20
Hope it helps.

import com.webobjects.foundation.*;
import com.webobjects.appserver.*;
import com.webobjects.eocontrol.*;
import com.webobjects.eoaccess.*;
import com.webobjects.jdbcadaptor.*;
=20
public class Main extends WOComponent {
=20
    /** @TypeInfo Company */
    protected Company company;
=20
    /** @TypeInfo Company */
    protected NSMutableArray companies;
   =20
    public Main(WOContext context) {
        super(context);
=20
        EOEditingContext ec =3D session().defaultEditingContext();
=20
         NSArray array =3D EOUtilities.objectsForEntityNamed(ec,"Comp=
any");
=20
        EODatabaseContext dbContext =3D EOUtilities.databaseContextFo=
rModelNamed(ec, "testval");
        JDBCContext myContext =3D (JDBCContext)dbContext.adaptorConte=
xt();
        try {
         java.sql.Connection conexion =3D myContext.connection();
         java.sql.CallableStatement cs =3D null;
         java.sql.ResultSet rs =3D null;
        //
        // create proc sp_getCompanies=20
        // as=20
        // select companyName, companyDescription=20
        // from Company
        //
        cs =3D conexion.prepareCall("{call sp_getCompanies}");
        rs =3D cs.executeQuery();
       =20
        companies =3D new NSMutableArray();
        while (rs.next()) {
            Company comp =3D new Company();
            comp.setCompanyName(rs.getString(1));
            comp.setCompanyDescription(rs.getString(2));         =
=20
            companies.addObject(comp);
        }
        rs.close();
        cs.close();
        } catch (java.sql.SQLException e) {
            System.out.println(e.getMessage());
        }
       =20
    }
=20
}
=20



Venkatesh wrote:

> Hi ,
>
> I am trying to execute a stored procedure from Web Objects applicat=
ion=20
> but getting below mentioned exception.
>
>
>     java.lang.NullPointerException
>     at
>     com.webobjects.eoaccess.EOUtilities.executeStoredProcedureNamed=
(EOUtilities.java:668)
>
>     at
>     com.effigent.reusables.ums.businessclasses.FormattedAutoNumberB=
usinessClass.getNextRunningNumber(FormattedAutoNumberBusinessClass.ja=
va:391)
>
>     at
>     com.effigent.reusables.ums.businessclasses.FormattedAutoNumberB=
usinessClass.getValueForSymbol(FormattedAutoNumberBusinessClass.java:=
319)
>
>     at
>     com.effigent.reusables.ums.businessclasses.FormattedAutoNumberB=
usinessClass.getNextNumberForFormat(FormattedAutoNumberBusinessClass.=
java:263)....
>
>
>
> Data base used is Sybase.
>
> I tried to execute the stored procedure from the isql and it works =
fine.
>
> I also checked the parameters which are passed to the methods and t=
hey=20
> are not null.
>
> resultDict =3D=20
> EOUtilities.executeStoredProcedureNamed(aFormatAutoNumObj.editingCo=
ntext(),spName,dict);=20
>
>
> is the line where stored procedure is invoked. Variable 'spName'=
=20
> contains the name of the Stored procedure to be invoked. 'dict' is=
=20
> dictionary that contains attributes which need to be passed to the=
=20
> stored procedure as key and value. There are two elements in the=
=20
> dictionary. One is the output parameter(of type numeric) and the ot=
her=20
> is the input parameters if type string.
>
> I checked the bindings in the eomodel and they seem to be fine(thin=
gs=20
> like the stored procedure name is ok, name and columnname of the=
=20
> attributes used are same etc).
>
> Inputs on this issue would be of great help.
>
> Thanks
>
> Venkatesh=20



AVISO LEGAL
Este mensaje de correo electr=F3nico y sus documentos adjuntos est=
=E1n dirigidos
exclusivamente a los destinatarios especificados. Puede contener info=
rmaci=F3n
confidencial o legalmente protegida. No hay renuncia a la confidencia=
lidad o
privilegio por cualquier transmisi=F3n err=F3nea. Si usted no es el d=
estinatario
indicado, le rogamos que lo elimine y se lo comunique al remitente. N=
o debe,
directa o indirectamente, usar, revelar, distribuir, imprimir o copia=
r=20
ninguna de las partes de este mensaje. Si siendo destinatario de este=
=20
mensaje no consintiera el uso de correo electr=F3nico, rogamos nos lo=
=20
comunique inmediatamente.
Bancoval,S.A. y sus filiales no ser=E1n responsables de las opiniones=
 o
informaciones incluidas en este mensaje salvo cuando el remitente est=
=E9=20
autorizado para establecer que dichas opiniones proceden de Bancoval,=
S.A
y sus filiales.

DISCLAIMER
Addressee/s identified herein. It may contain confidential or legally=
=20
privileged information. No confidentiality privilege is waived or los=
t
by any mistransmission. If you are not the intended recipient, please
immediately delete it and notify the sender. You must not, directly o=
r
indirectly, disclose, distribute, print, or copy any part of this mes=
sage.
If you are the addressee of this message and do not consent to the us=
e of
e-mail, please communicate it to us immediately. Bancoval, S.A. and i=
ts
subsidiaries are not responsible for the opinions or information incl=
uded
in this message except when the sender is authorised to state them to=
 be the
views of Bancoval, S.A and its subsidiaries.


--Boundary_(ID_A9sxNctxGQk/2gjq93jmRw)
Content-type: text/html; charset=us-ascii
Content-transfer-encoding: QUOTED-PRINTABLE

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
We have had the same nightmare with Sybase + WO + Stored Procedures s=
ince
we decided to migrate from WO4.5 to WO5.x. We thought that with WO5.2=
, as
it comes with a SybasePlugIn, this issue would have been solved but n=
o way.
So we tried this workaround: get the java.sql.Connection object and e=
xecute
the stored procedure using a CallableStatement, and then if needed ta=
ke the
resultset into an array of EOEnterpriseObjects to be treated seamless=
ly by
WebObjects. Here is some sample code which by the way I got from a we=
bobjects-dev
thread. Hope it helps.<br>
<br>
<font size=3D"-1" face=3D"arial,sans-serif" color=3D"#000000">       =
 =20
<pre><font color=3D"navy"><b>import</b></font> com.webobjects.foundat=
ion.*;
<font color=3D"navy"><b>import</b></font> com.webobjects.appserver.*;
<font color=3D"navy"><b>import</b></font> com.webobjects.eocontrol.*;
<font color=3D"navy"><b>import</b></font> com.webobjects.eoaccess.*;
<font color=3D"navy"><b>import</b></font> com.webobjects.jdbcadaptor.=
*;
&nbsp;
<font color=3D"navy"><b>public</b></font> <font color=3D"navy"><b>cla=
ss</b></font> Main <font
 color=3D"navy"><b>extends</b></font> WOComponent <font color=3D"navy=
">{</font>
&nbsp;
    <font color=3D"darkgreen">/** @TypeInfo Company */</font>
    <font color=3D"navy"><b>protected</b></font> Company company;
&nbsp;
    <font color=3D"darkgreen">/** @TypeInfo Company */</font>
    <font color=3D"navy"><b>protected</b></font> NSMutableArray compa=
nies;
   =20
    <font color=3D"navy"><b>public</b></font> Main(WOContext context)=
 <font
 color=3D"navy">{</font>
        <font color=3D"navy"><b>super</b></font>(context);
&nbsp;
        EOEditingContext ec =3D session().defaultEditingContext();
=20
         NSArray array =3D EOUtilities.objectsForEntityNamed(ec,<font
 color=3D"red">"Company"</font>);
=20
        EODatabaseContext dbContext =3D EOUtilities.databaseContextFo=
rModelNamed(ec, <font
 color=3D"red">"testval"</font>);
        JDBCContext myContext =3D (JDBCContext)dbContext.adaptorConte=
xt();
        <font color=3D"navy"><b>try</b></font> <font color=3D"navy">{=
</font>
         java.sql.Connection conexion =3D myContext.connection();
         java.sql.CallableStatement cs =3D <font color=3D"navy"><b>nu=
ll</b></font>;
         java.sql.ResultSet rs =3D <font color=3D"navy"><b>null</b></=
font>;
        <font color=3D"darkgreen">//</font>
        <font color=3D"darkgreen">// create proc sp_getCompanies </fo=
nt>
        <font color=3D"darkgreen">// as </font>
        <font color=3D"darkgreen">// select companyName, companyDescr=
iption </font>
        <font color=3D"darkgreen">// from Company</font>
        <font color=3D"darkgreen">//</font>
        cs =3D conexion.prepareCall(<font color=3D"red">"{call sp_get=
Companies}"</font>);
        rs =3D cs.executeQuery();
       =20
        companies =3D <font color=3D"navy"><b>new</b></font> NSMutabl=
eArray();
        <font color=3D"navy"><b>while</b></font> (rs.next()) <font
 color=3D"navy">{</font>
            Company comp =3D <font color=3D"navy"><b>new</b></font> C=
ompany();
            comp.setCompanyName(rs.getString(1));
            comp.setCompanyDescription(rs.getString(2));         =
=20
            companies.addObject(comp);
        <font color=3D"navy">}</font>
        rs.close();
        cs.close();
        <font color=3D"navy">}</font> <font color=3D"navy"><b>catch</=
b></font> (java.sql.SQLException e) <font
 color=3D"navy">{</font>
            System.out.println(e.getMessage());
        <font color=3D"navy">}</font>
       =20
    <font color=3D"navy">}</font>
&nbsp;
<font color=3D"navy">}</font>
&nbsp;
</pre>
         </font><br>
<br>
Venkatesh wrote:<br>
<blockquote type=3D"cite"
 cite=3D"midC9DF8DB2-89FA-11D7-84B7-0003936C1E28@xxxxxxxxxxxx">Hi , <=
br>
  <br>
I am trying to execute a stored procedure from Web Objects applicatio=
n but
getting below mentioned exception. <br>
  <br>
  <br>
  <blockquote><tt><!-- 8837,3ACA,FFFC -->java.lang.NullPointerExcepti=
on <br>
=09at com.webobjects.eoaccess.EOUtilities.executeStoredProcedureNamed=
(EOUtilities.java:668)=20
    <br>
=09at com.effigent.reusables.ums.businessclasses.FormattedAutoNumberB=
usinessClass.getNextRunningNumber(FormattedAutoNumberBusinessClass.ja=
va:391)=20
    <br>
=09at com.effigent.reusables.ums.businessclasses.FormattedAutoNumberB=
usinessClass.getValueForSymbol(FormattedAutoNumberBusinessClass.java:=
319)=20
    <br>
=09at com.effigent.reusables.ums.businessclasses.FormattedAutoNumberB=
usinessClass.getNextNumberForFormat(FormattedAutoNumberBusinessClass.=
java:263)....=20
    <br>
    </tt></blockquote>
 <br>
Data base used is Sybase.  <br>
  <br>
I tried to execute the stored procedure from the isql and it works fi=
ne. <br>
  <br>
 I also checked the parameters which are passed to the methods and th=
ey are
not null.  <br>
  <tt> <br>
resultDict =3D EOUtilities.executeStoredProcedureNamed(aFormatAutoNum=
Obj.editingContext(),spName,dict);=20
  <br>
  <br>
is the line where stored procedure is invoked. Variable 'spName' cont=
ains
the name of the Stored procedure to be invoked. 'dict' is dictionary =
that
contains attributes which need to be passed to the stored procedure a=
s key
and value. There are two elements in the dictionary. One is the outpu=
t parameter(of
type numeric) and the other is the input parameters if type string.  =
<br>
  </tt> <br>
I checked the bindings in the eomodel and they seem to be fine(things=
 like
the stored procedure name is ok, name and columnname of the attribute=
s used
are same etc). <br>
  <br>
Inputs on this issue  would be of great help. <br>
  <br>
Thanks  <br>
  <br>
Venkatesh </blockquote>
<br>
</body>
</html>


--Boundary_(ID_A9sxNctxGQk/2gjq93jmRw)--
References:
- Executing a stored procedure
  - From: Venkatesh
Prev by Date: Executing a stored procedure
Next by Date: WO 5 and mysql-connector installation
Previous by thread: Executing a stored procedure
Next by thread: WO 5 and mysql-connector installation
Index(es):
- Date
- Thread