[jdjlist] RE: FW:

Thu, 29 Aug 2002 23:43:58 -0700

Title: FW:
Alright, the idea that Greg has was one on my mind as well, and by the way I am using the thin driver. Now that out the way, one of our developers found a solution that not even the bea team had thought of, I would have been stuck otherwise, having to write either wrappers for stored procedures or somthing else involving ugly code.
 
Here is the solution which he came up with:
 
public static void updRow(MyDataObject inputRec, Connection conn)
    throws SQLException
  {
//this is the new stuff which utilises a class after this listing
    conn.getTypeMap().put("GRPLIFE.GM_GSCLI_IO", MyDataObject.class);
    conn.setTypeMap(conn.getTypeMap());
    // Prepare the appropriate stored procedure
    CallableStatement call = conn.prepareCall("{call GM_GSCLI_MNT.UPD_ROW(?,?,?)}");
 
    // Register all "OUT" and "IN/OUT" parameters
    call.registerOutParameter(1, Types.STRUCT, "GRPLIFE.GM_GSCLI_IO");
    call.registerOutParameter(2, Types.DECIMAL);
    call.registerOutParameter(3, Types.VARCHAR);
 
    // Set all "IN" and "IN/OUT" parameters to their passed values
    //call.setObject(1, inputRec != null ? new STRUCT(StructDescriptor.createDescriptor("GRPLIFE.GM_GSCLI_IO", conn), conn, inputRec.getValue()) : null);
    call.setObject(1, inputRec);
 
    // Execute the stored procedure
    call.execute();
 
    // Do standard Group Life Systems error checking - Test the error code:
    // if it is nonzero then an error occurred and an exception must be thrown
    BigDecimal returnCode = call.getBigDecimal(2);
    if (returnCode != null && !returnCode.toString().equals("0"))
    {
      String returnDescr = call.getString(3);
      throw new DatabaseException(returnCode, returnDescr);
    }
 
    // Populate all "OUT" and "IN/OUT" parameters with the values received
    //if (inputRec != null) inputRec.setValue(((java.sql.Struct) call.getObject(1)).getAttributes());
    //if (inputRec != null) inputRec.setValue(((java.sql.Struct) call.getObject(1)).getAttributes());
    Object value = call.getObject(1);
    System.out.println(value);
 
    // Close resources for the call (ResultSets will still be open, though.)
    call.close();
  }
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
public class MyDataObject implements SQLData
{
  public BigDecimal gscliNo;
  public String     gscliName;
  public BigDecimal gschNo;
  public String     gscliEffFrom;
  public String     gscliEffTo;
  public String     gscliDeleteInd;
  public BigDecimal gscliCorpEntNo;
 
  public void writeSQL(SQLOutput output) throws SQLException
  {
    output.writeBigDecimal(gscliNo);
    output.writeString(gscliName);
    output.writeBigDecimal(gschNo);
    output.writeString(gscliEffFrom);
    output.writeString(gscliEffTo);
    output.writeString(gscliDeleteInd);
    output.writeBigDecimal(gscliCorpEntNo);
  }
 
  public void readSQL(SQLInput input, String typeName) throws SQLException
  {
    gscliNo        = input.readBigDecimal();
    gscliName      = input.readString();
    gschNo         = input.readBigDecimal();
    gscliEffFrom   = input.readString();
    gscliEffTo     = input.readString();
    gscliDeleteInd = input.readString();
    gscliCorpEntNo = input.readBigDecimal();
  }
 
  public String getSQLTypeName()
  {
    return "GRPLIFE.GM_GSCLI_IO";
  }
 
  public String toString()
  {
    return "Yeah: " + gscliName;
  }
}
Hope this helps anybody stuck with a similar prob
 
-----Original Message-----
From: Greg Nudelman [mailto:[EMAIL PROTECTED]]
Sent: 30 August 2002 12:00
To: JDJList
Subject: [jdjlist] RE: FW:

No backward compatibility for you Mr. Powers!  BUHAHAHAH! http://www.elvis92.com/evillaff.wav
 
;->
 
Now, don't take me wrong, I'm not a huge fan of s.p., but how about sending all the Oracle object params into the stored procedure, THEN instantiating the Oracle object from these params directly in the s.p. and then running the procedure normally?
 
I know, that kind of defeats the purpose of what you're trying to do, but it may hack your problem.  Also, maybe a different driver, like Oracle thin driver would probably help.
 
BTW: Here is the link to Oracle thin driver vs. OCI driver performance.
http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html
 
Greg
-----Original Message-----
From: Martin Kisimov [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 29, 2002 1:23 AM
To: JDJList
Subject: [jdjlist] FW:

Hi guys,

I have run into a problem and went looking for answers on the google and bea websites. What I can gather Weblogic as from version 6 does not support, or has been able to eradicate the use of oracle extensions, such as oracle objects and so on. Now does anybody have a workaround to this.


Here is the problem:

Using Weblogic ver 7 and Oracle 8.1.7, I am trying to call a stored procedure, which accepts an oracl objcect. I am getting the connection from a a connection pool.

The following code does the call to the stored proc

public void updRow(GmGscliIo inputRec)
    throws SQLException
  {
    // Prepare the appropriate stored procedure
    CallableStatement call = conn.prepareCall("{call GM_GSCLI_MNT.UPD_ROW(?,?,?)}");

    // Register all "OUT" and "IN/OUT" parameters
    call.registerOutParameter(1, Types.STRUCT, "GRPLIFE.GM_GSCLI_IO");
    call.registerOutParameter(2, Types.DECIMAL);
    call.registerOutParameter(3, Types.VARCHAR);

    // Set all "IN" and "IN/OUT" parameters to their passed values
///////////THIS IS WHERE THE ERROR OCCURS
    call.setObject(1, inputRec != null ? new STRUCT(StructDescriptor.createDescriptor("GRPLIFE.GM_GSCLI_IO", conn), conn, inputRec.getValue()) : null);

    // Execute the stored procedure
    call.execute();

    // Do standard Group Life Systems error checking - Test the error code:
    // if it is nonzero then an error occurred and an exception must be thrown
    BigDecimal returnCode = call.getBigDecimal(2);
    if (returnCode != null && !returnCode.toString().equals("0"))
    {
      String returnDescr = call.getString(3);
      throw new DatabaseException(returnCode, returnDescr);
    }

    // Populate all "OUT" and "IN/OUT" parameters with the values received
    if (inputRec != null) inputRec.setValue(((java.sql.Struct) call.getObject(1)).getAttributes());

    // Close resources for the call (ResultSets will still be open, though.)
    call.close();
  }


and this is the error

java.lang.ClassCastException: weblogic.jdbc.rmi.SerialConnection       
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:87)      
at za.co.discoverylife.memreg.db.proc.GmGscliMnt.updRow(GmGscliMnt.java:193)   
at za.co.discoverylife.memreg.edit.EmployerUpdate.doPost(EmployerUpdate.java:48)       
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)        
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

To change your JDJList options, please visit: http://www.sys-con.com/java/list.cfm 
To change your JDJList options, please visit: http://www.sys-con.com/java/list.cfm

Reply via email to