Win a copy of Machine Learning Systems: Designs that scale this week in the Scala forum
or Xamarin in Action: Creating native cross-platform mobile apps in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Can I use a Clob as an attribute of a structured type (to map to a Java VO)?  RSS feed

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I'm trying to use a Clob as an attribute of a structured type, but I'm having trouble retrieving the Clob from Oracle, resorting to explicitly retrieving it in a seperate query on a database column. Is there a way of retrieving it as part of a Java VO? I haven't found it explicitly printed where it's not possible, but I have a nagging idea that it isn't. Any ideas?

I'm using Java 1.4 (soon to be Java 1.5), and Oracle 10.1.2.

Any help, greatly appreciated.


The code :

My VO (CharacterDataVO) contains 2 attributes - an id (BigDecimal), and data (the Clob). I'm calling a stored procedure, and it bombs out when I hit callable.execute(), and reports a StringIndexOutOfBoundsException:33. (I'm going to convert it to text once it's back - hence the String returned. Not an issue.)

The Clob is only ~10chars long at the moment(for test purposes, will get much bigger).


public String getClobVO(BigDecimal id) {
Connection con = null;
CallableStatement callable = null;
CharacterDataVO charData = new CharacterDataVO();

String clobString = null;
try {

con = getConnection();

Map map;
map = con.getTypeMap();
map.put(SQLObjectTypeNames.OT_CHARACTER_DATA_VO, CharacterDataVO.class);

con.setTypeMap(map);

//set call
callable = con.prepareCall("{? = call " + LARGE_DATA_PACKAGE + ".GET(?)");


//set parameters
callable.setBigDecimal(2, id);

callable.registerOutParameter(1, OracleTypes.STRUCT, SQLObjectTypeNames.OT_CHARACTER_DATA_VO);
callable.execute();

charData = (CharacterDataVO)callable.getObject(1);

} catch (final SQLException e) {
e.printStackTrace();
} catch (final Exception e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
if (callable != null) {
callable.close();
}
con.close();
} catch (final SQLException e) {
e.printStackTrace();
}
}
}

return clobString;
}






The exception : java.lang.StringIndexOutOfBoundsException: String index out of range: 33
at java.lang.String.charAt(Unknown Source)
at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:1066)
at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:984)
at oracle.jdbc.driver.OracleSql.getSql(OracleSql.java:312)
at oracle.jdbc.driver.OracleSql.getSqlBytes(OracleSql.java:557)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:201)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at com.inpses.vision.common.clobmapspike.dao.ClobMapSpikeDaoImpl.getClobVO(ClobMapSpikeDaoImpl.java:128)
 
Graham Wagg
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Argh.

Obviously you can - or at least you can get the Clob locator - a schoolboy error from me, missing the "}" from the stored procedure call prevented progress (and some correcting of the signature call). Whoopsie.

Cheers to anyone that looked.
 
Yes, my master! Here is the tiny ad you asked for:
Why should you try IntelliJ IDEA ?
https://coderanch.com/wiki/696337/IntelliJ-IDEA
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!