Named parameters and overloaded stored procedures

If multiple stored procedures have the same name and the same number of arguments, the procedures are overloaded (also known as overloaded UDRs).

The JDBC driver throws an SQLException for overloaded stored procedures because the call cannot resolve to a single stored procedure. To prevent an SQLException, specify the HCL OneDB™ server data type of the named parameters in the parameter list by appending ::data_type to the question mark characters where data_type is the HCL OneDB server data type. For example ?::varchar or ?::float. You must also enter the named parameters for all the arguments and in the same order as the overloaded stored arguments of procedure.

For example, the following two procedures have the same name (createProductDef) and the same number of arguments. The data type for the prod_id argument is a different data type in each procedure.

Procedure 1

create procedure createProductDef(productname   varchar(64),
          productdesc  varchar(64),
          listprice    float default 100.00,
          minprice     float default  90.00,
          prod_id      float);
	...
	let prod_id = <value for prod_id>;
end procedure;

Procedure 2

create procedure createProductDef(productname   varchar(64),
          productdesc  varchar(64),
          listprice    float default 100.00,
          minprice     float default  90.00,
          prod_id      int);
	...
	let prod_id = <value for prod_id>;
end procedure;

If you use the following Java™ code, it returns an SQLException because it cannot resolve to only one procedure:

String sqlCall = "{call CreateProductDef(?,?,?,?,?)}";
CallableStatement cstmt = con.prepareCall(sqlCall);
cstmt.setString("productname", name);   // Set Product Name.

If you specify the HCL OneDB data type for the argument that has a different data type, the Java code resolves to one procedure. The following Java code resolves to Stored Procedure 1 because the code specifies the FLOAT data type for the prod_id argument:

String sqlCall = "{call CreateProductDef(?,?,?,?,?::float)}";  
CallableStatement cstmt = con.prepareCall(sqlCall);
cstmt.setString("productname", name);   // Set Product Name