aspose file tools*
The moose likes Other Application Frameworks and the fly likes trouble passing array to stored procedure in spring HELP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Other Application Frameworks
Bookmark "trouble passing array to stored procedure in spring HELP" Watch "trouble passing array to stored procedure in spring HELP" New topic
Author

trouble passing array to stored procedure in spring HELP

Darvesh Niz
Ranch Hand

Joined: May 12, 2008
Posts: 119
I dont know why the hell this is so complicated, i am trying to pass a simple array of Strings to an oracle stored procedure from Spring in my package here it is

In my package decleration



i have the method declared


then in my stored procedure i have


i purposely did not use the array variable, i want to make it sure i want the stored proc atleast without any error.

Now coming to the java side, there are so many people who are having issues with this how to pass some say use array descriptor some say do this, this is what i was trying



i get an exception saying java.sql.SQLException: invalid name pattern: evdb.dct_2_2.t_att_name

Can anyone please tell me whats the easiest way to pass this array.

Thanks
Rashid

>
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Personally, I don't use Stored Procedures anymore, especially for these types of reasons. But soon Hibernate in version 4 will have full support for stored procedures like these.

Anyway, the only thing that I think I notice is that in the execute method you are setting the parameter's value to an array of Oracle ARRAYs, I would think you would just set it to a single instance of Oracle's ARRAY object.

And it goes to show how Oracle just decides to do things their own way, rather than follow standards. I mean a class named "ARRAY" all caps. That totally goes against the standard naming of classes.

Anyway, maybe you want your returnStringArray method to just return a single oracle.sql.ARRAY class, instead of ARRAY[]

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Darvesh Niz
Ranch Hand

Joined: May 12, 2008
Posts: 119
FYI
The error i am getting is at the line where i do
ArrayDescriptor.createDescriptor("evdb.dct_2_2.t_att_name",conn);

i tried ddifferent things like removing the schema name adding the schema name but still the same issue cant help it.


Exception in thread "main" java.sql.SQLException: invalid name pattern: EVDB.t_att_name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:412)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:327)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1300)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:156)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:113)
at com.edmunds.evdb.SimpleSPArray$ObtainStringArrayFromJava.returnStringArray(SimpleSPArray.java:112)
at com.edmunds.evdb.SimpleSPArray.execute(SimpleSPArray.java:47)
at com.edmunds.evdb.SimpleSPArray.main(SimpleSPArray.java:34)
Joe ONeil
Greenhorn

Joined: Jan 16, 2009
Posts: 12
I got it to work by hacking I just passed the datasource into the execute. I think spring closes the connection and by passing the data source the connection stays open it is really strange

This Spring stored procedure passes in an array of tab delimited strings the procedure validates the strings if there are errors in validation an error is added to the error array and the array is passed back



public class JdbcUpLoadPOLinesDAOImpl extends StoredProcedure implements JdbcUploadPOLinesDAO {

private static final String UPLOAD_PO_LINES = "necxadm.po_upload_lines";
private static final String p_po_line_array= "p_po_line_array";

public JdbcUpLoadPOLinesDAOImpl(DataSource dataSource) {
super(dataSource, UPLOAD_PO_LINES);
final String[] errorList=new String[50];
declareParameter(new SqlParameter("poid", Types.BIGINT));
declareParameter(new SqlParameter("insert_user",Types.BIGINT));
declareParameter(new SqlParameter("version",Types.VARCHAR));
declareParameter(new SqlParameter("PO_LINES", Types.ARRAY,"STRING_ARRAY"));
declareParameter(new SqlOutParameter("results",Types.VARCHAR));
declareParameter(new SqlOutParameter("ERRORS", Types.ARRAY, "STRING_ARRAY", new SqlReturnType() {
public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException {
Connection con = cs.getConnection();
Map typeMap = con.getTypeMap();
typeMap.put(typeName, errorList);
Object o = cs.getObject(paramIndex);
return o;
}
}));
compile();
}

public Map upLoadLines(DriverManagerDataSource ds ,Long poid,Long loginUser,String version,String[] poLines) {
Map inParameters = new HashMap();
final String[] temp = poLines;
inParameters.put( "poid", poid );
inParameters.put( "insert_user", loginUser );
inParameters.put( "version", version );
inParameters.put("PO_LINES", new AbstractSqlTypeValue() {

protected Object createTypeValue(Connection con, int type, String typeName)
throws SQLException {
if( con.isClosed()){
System.out.println("connection is closed");
}else{
System.out.println("connection is open");
}
ArrayDescriptor desc = new ArrayDescriptor(typeName, con);
return new ARRAY(desc, con, temp);
}
});

Map out = execute( inParameters ); // Call on parent class


System.out.println("results="+out.get("results"));
ARRAY simpleArray=(ARRAY )out.get("ERRORS");
Object[] obj;
try {
obj = (Object[]) simpleArray.getArray(out);
System.out.println("obj[0]"+obj[0]);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


return out;
}

This is the the outpput and it is what I expected
[STDOUT] obj[0]Header: Invalid Label for 'Part Number': xPart Number

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: trouble passing array to stored procedure in spring HELP