JDBC adapter Stored Procedure error: parameter was not supplied

[laxman_molugu]

I am trying to call a Stored Procedure 'SP_FUSION_DEBMS_ADRC_MANAGE_CUSTOMER_ADDRESS' in our SQL Server database.

XI 3.0 Sp13

Jdbc Url: jdbc:microsoft:sqlserver://161.241.255.91:1433;databaseName=FusionDB;SelectMethod=Cursor

I am getting following error:

-

---

beging of error-----

Exception caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. 'SP_FUSION_DEBMS_ADRC_MANAGE_CUSTOMER_ADDRESS' (structure 'Statement1'): java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Procedure 'SP_FUSION_DEBMS_ADRC_MANAGE_CUSTOMER_ADDRESS' expects parameter '@control_no', which was not supplied.

-

---

End of error-----

I have defined the field "control_no" in the interface defenition but I am not sure what causing this error, error message is mis-leading.

Please let me know if you have any hints/comments?

thanks,

Laxman molugu

[Former Member]

Hi Laxman,

JDBC proc is called depending on the sequence of the parameters so the sequence of XML node and stored procedures variables should match , also check that same no of XML nodes are created as that of present in stored procedure.

Regards,

Keith.

[bhavesh_kantilal]

Laxman,

apart from the info already stated, just check if you have created the TYPE attribute for each of the paramters being paasssed to the strored procedure.

And also, make sure that they are in Upper case.

From the onlin help,

<i>The attribute type=<SQL-Datatype> , which describes the valid SQL data type, is mandatory for all parameter types (IN, OUT, INOUT).

The following SQL data types are supported:

INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output),CLOB (input and output), CURSOR (output; only in conjunction with the Oracle JDBC driver).</i>

Regards,

Bhavesh

[laxman_molugu]

Thanks for all the comments and suggestions...

The problem with Type value, I mapped 'int' (as defined in the StoredProcedure)as the type but when I mapped as INTEGER then it works fine.

I have one more type called "money" in my storedProcedure what will be the 'type' value in the messageMappings? where can I get all these corresponding types.

thanks,

laxman

[Former Member]

Hi Laxman,

maybe you have checked this already...from the following help link....DECIMAL might be something you might want to try out....

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

<i>action=EXECUTE

Statements with this action result in a stored procedure being executed. The name of the element is interpreted as the name of the stored procedure in the database. If you use the optional <table> element, the value specified here is used as the stored procedure name. This enables you, for example, to define stored procedure names containing non-XML-compatible characters or characters that prevent them from being used in interface definitions in the Integration Builder/PCK. If specified, <table> must be the first element in the block within <dbTableName>.

The elements within the stored procedure are interpreted as parameters. They can optionally have the attribute isInput=“1“ (input parameter) or isOutput=“1“ (output parameter) or both (INOUT parameter). If both attributes are missing, the element is interpreted as an input parameter. The parameter names must be identical to those of the stored procedure definition.

The attribute type=<SQL-Datatype> , which describes the valid SQL data type, is mandatory for all parameter types (IN, OUT, INOUT).

The following SQL data types are supported:

INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output),CLOB (input and output), CURSOR (output; only in conjunction with the Oracle JDBC driver).</i>

[Former Member]

Laxman,

I think when the stored proc is called it is by the order of parameters, the name of the XML node might not matter. So verify the parameter list order on the stored proc to the datatype in XI and also make sure the mapping result generates the node for control_no.

-Jagathi

[moorthy]

HI Laxman,

Check the Adapter Monitoring->Adapter for the Active and Green Status

This blog may give some hints-

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

Hope this helps,

Regards,

Moorthy

[Former Member]

Hi Laxman,

For Stor Procs, there will be in and out parameters, have u defined those to a constant?.

/Raj.