SQL Function execution using Cayenne

From: Shah, Megha (Megha_Sha..yntelinc.com)
Date: Fri Oct 07 2005 - 01:39:37 EDT

  • Next message: Dave Merrin: "RE: double inner join"

    Hi,

    I'am trying to execute a SQL function (return value) usign Cayenne.
    Am adding the XML entry and the code here.

    XML:

            <procedure name="FN_FC_HOLIDAY_PCS" schema="TEMP"
    returningValue="true">
                    <procedure-parameter name="_return_value" type="OTHER"/>
                    <procedure-parameter name="P_FUNDID" type="VARCHAR"
    direction="in"/>
                    <procedure-parameter name="P_OPERATORID" type="VARCHAR"
    direction="in"/>
            </procedure>

    Code:
            ProcedureQuery query = new ProcedureQuery("FN_FC_HOLIDAY_PCS");
            query.addParameter("_return_value", null);
            query.addParameter("P_FUNDID", "KT03");
            query.addParameter("P_OPERATORID", "12345");

    The error recieved is:

    INFO QueryLogger: {? = call TEMP.FN_FC_HOLIDAY_PCS(?, ?)} [bind: NULL,
    'KT03', '12345']
    INFO QueryLogger: {? = call TEMP.FN_FC_HOLIDAY_PCS(?, ?)} [bind: NULL,
    'KT03', '12345']
    INFO QueryLogger: *** error.
    java.sql.SQLException: Missing IN or OUT parameter at index:: 1

    Initially I thought it was beacuse there is no 'direction' field in the XML
    entry for _return_value. But on specifying that, the SQL error returned was
    "Invalid Column Type".

    I am also not sure whether I'am adding the parameter correctly. Does
    _return_value expect anything special. I have tried passign NULL since there
    is no explicit class defintion for the result set. Or do I need to create a
    class for the resultset. I was plannig to map the resulting list into
    cayenne.DataObject and then use ReadProperty to retrive the values.

    Pls advise as I need to confirm whether we can use Cayenne for our intended
    application.

    Thanks & Regards,
    Megha Shah

    Confidential: This electronic message and all contents contain information from Syntel, Inc. which may be privileged, confidential or otherwise protected from disclosure. The information is intended to be for the addressee only. If you are not the addressee, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have recieved this electronic message in error, please notify the sender immediately and destroy the original message and all copies.

    This archive was generated by hypermail 2.0.0 : Fri Oct 07 2005 - 01:44:55 EDT