unable to read data from database

[dppalepu]

Hi
I wanted to read data from oracle objects for which Iam using Jpublisher to generate the file. But when i execute the character data is not diplayed while the numric data is displayed properly. I am reading the data as a String but when i display the data it is simple HEX CODE. Pls help in this.
here is the code
import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class OBJ_TYPE_CITY implements ORAData, ORADataFactory
{
public static final String _SQL_NAME = "SCOTT.OBJ_TYPE_CITY";
public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

protected MutableStruct _struct;

private static int[] _sqlType = { 12,12,12,2 };
private static ORADataFactory[] _factory = new ORADataFactory[4];
protected static final OBJ_TYPE_CITY _OBJ_TYPE_CITYFactory = new OBJ_TYPE_CITY(false);

public static ORADataFactory getORADataFactory()
{ return _OBJ_TYPE_CITYFactory; }
/* constructor */
protected OBJ_TYPE_CITY(boolean init)
{ if(init) _struct = new MutableStruct(new Object[4], _sqlType, _factory); }
public OBJ_TYPE_CITY()
{ this(true); }
public OBJ_TYPE_CITY(String name, String province, String cityCode, java.math.BigDecimal populationDensity) throws SQLException
{ this(true);
setName(name);
setProvince(province);
setCityCode(cityCode);
setPopulationDensity(populationDensity);
}

/* ORAData interface */
public Datum toDatum(Connection c) throws SQLException
{
return _struct.toDatum(c, _SQL_NAME);
}


/* ORADataFactory interface */
public ORAData create(Datum d, int sqlType) throws SQLException
{ return create(null, d, sqlType); }
protected ORAData create(OBJ_TYPE_CITY o, Datum d, int sqlType) throws SQLException
{
if (d == null) return null;
if (o == null) o = new OBJ_TYPE_CITY(false);
o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
return o;
}
/* accessor methods */
public String getName() throws SQLException
{ return (String) _struct.getAttribute(0); }

public void setName(String name) throws SQLException
{ _struct.setAttribute(0, name); }


public String getProvince() throws SQLException
{ return (String) _struct.getAttribute(1); }

public void setProvince(String province) throws SQLException
{ _struct.setAttribute(1, province); }


public String getCityCode() throws SQLException
{ return (String) _struct.getAttribute(2); }

public void setCityCode(String cityCode) throws SQLException
{ _struct.setAttribute(2, cityCode); }


public java.math.BigDecimal getPopulationDensity() throws SQLException
{ return (java.math.BigDecimal) _struct.getAttribute(3); }

public void setPopulationDensity(java.math.BigDecimal populationDensity) throws SQLException
{ _struct.setAttribute(3, populationDensity); }

public String toString()
{ try {
return "SCOTT.OBJ_TYPE_CITY" + "(" +
((getName()==null)?"null": "'"+getName()+"'" ) + "," +
((getProvince()==null)?"null": "'"+getProvince()+"'" ) + "," +
((getCityCode()==null)?"null": "'"+getCityCode()+"'" ) + "," +
getPopulationDensity() +
")";
} catch (Exception e) { return e.toString(); }
}

}

the program which uses the above code is:

import java.util.Enumeration;
import java.util.Properties;
import java.util.ResourceBundle;
import java.math.BigDecimal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.io.IOException;

//Package for Oracle Extensions to JDBC
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.jdbc.driver.OracleResultSet;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.CallableStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import oracle.jdbc.OracleTypes;

public class ObjectSample {
Connection connection = null; // database connection object

private Properties loadParams(String file) throws IOException {

// Loads a ResourceBundle and creates Properties from it
Properties prop = new Properties();
ResourceBundle bundle = ResourceBundle.getBundle(file);
Enumeration enm = bundle.getKeys();
String key = null;
while(enm.hasMoreElements()) {
key = (String)enm.nextElement();
prop.put(key, bundle.getObject(key));
}
return prop;
}

/**
* Creates a database connection object using DataSource object. Please
* substitute the database connection parameters with appropriate values in
* Connection.properties file
*/
public boolean dbConnection() {
try {
// Load the properties file to get the connection information
Properties prop = this.loadParams("Connection");

// Create a OracleDataSource instance
OracleDataSource ods = new OracleDataSource();

// Sets the driver type
ods.setDriverType("thin");

// Sets the database server name
ods.setServerName((String)prop.get("HostName"));

// Sets the database name
ods.setDatabaseName((String)prop.get("SID"));

// Sets the port number
ods.setPortNumber(new Integer((String)prop.get("Port")).intValue());

// Sets the user name
ods.setUser((String)prop.get("UserName"));

// Sets the password
ods.setPassword((String)prop.get("Password"));
connection=ods.getConnection();
System.out.println(" Connected to " + prop.get("SID") +
" Database as " + prop.get("UserName"));

} catch(SQLException ex) { // Trap SQL errors
System.out.println("Error in Connecting to the Database "+'\n'+ex.toString());
} catch(IOException ex) { // Trap I/O errors
System.out.println("Error in reading the properties file "+'\n'+ex.toString());
}

// Return false if failed to obtain connection object
if( connection != null )
return true;

return false;
}

/* the Object Created in database is :
create type OBJ_TYPE_CITY as OBJECT(
name varchar2(40),
province varchar2(40),
city_code varchar2(10),
population_density number);

also a table "TABLE_CITY_OBJ" is created with column OBJCITY of type OBJ_TYPE_CITY (above object )

*/

/**
* This function selects the rows from the TABLE_CITY_OBJ table, based on the query condition entered
*/
public void selectRecord() {
String code="";
String name = "";
//The SQL query for selecting the rows from the TABLE_CITY_OBJ table
String query = "SELECT * FROM TABLE_CITY_OBJ a "+
"WHERE a.OBJCITY.CITY_CODE LIKE ? AND a.OBJCITY.NAME LIKE ?";
try {
//Create a PreparedStatement based on the query in query
PreparedStatement pst = connection.prepareStatement(query);

//If no query condition has been entered, change the bind values
//to select all records
if(code.equals(""))
code = "%";
else
code = code;
if(name.equals(""))
name = "%";
else name = name;

//Bind the PreparedStatement with corresponding values
pst.setString(1,code);
pst.setString(2,name);

//Execute the PreparedStatement
OracleResultSet resultSet = (OracleResultSet)pst.executeQuery();

// create a type map using java.util.Hashtable object for specifying the
// mapping between the Oracle Object-Type and the generated java class
// representing the Object-Type
// Instantiate a HashTable to specify mapping
java.util.Hashtable<Object,Object> mymap = new java.util.Hashtable<Object,Object>();

// Load Java Class
Class obj = Class.forName("obj.OBJ_TYPE_CITY");

// Map the loaded java class to the object-type
mymap.put ("OBJ_TYPE_CITY", obj);

// loop through the resultset and retrieve all rows
while(resultSet.next()) {

// specify the type map while calling getObject
OBJ_TYPE_CITY city;

// Retrieve the column from the ResultSet into the OBJ_TYPE_CITY
// class. This class represents the OBJ_TYPE_CITY column in the
// database, and hence the retrieved object is cast to OBJ_TYPE_CITY
city = (OBJ_TYPE_CITY)resultSet.getObject(1,mymap);

// print the row values
System.out.println("Name: " + city.getName());
System.out.println("Province: "+city.getProvince());
System.out.println("Code: "+city.getCityCode());
System.out.println("Density:" +city.getPopulationDensity());
}
pst.close(); // close the statement which also closes the resultset

} catch (SQLException ex) { // trap sql errors
System.out.println(" Error in Querying: ");
System.out.println("\n"+ex.toString());
} catch (Exception ex) { //trap general errors
System.out.println(" Error in Querying: ");
System.out.println("\n"+ex.toString());
}
}

/**
* Closes the connection and exits from the program when exit button is
* pressed
*/

public static void main(String[] args){
ObjectSample os = new ObjectSample();
os.dbConnection();
os.selectRecord();
os.exitApplication();
}

private void exitApplication(){
if (connection != null) {
try {
connection.close(); // close the connection
} catch (Exception ex){ // Trap SQL Errors
System.out.println("error: " + ex);
}
}
System.exit(0);
}



}

[Phaelax]

Can you elaborate on the problem? Are you getting errors or what?

[dppalepu]

hi
I got that.... as i forgot to include nls_charset12.jar in my classpath because of which iam unable to retrieve character data.
But now i have a new problem....
Iam calling a stored procedure using CallableStatement through Java. The procedure returns an object as out parameter.
iam trying to retrieve that using
CallableStatement cstmt;
cstmt.registerOutParameter(4,java.sql.Types.OTHER);
then after executing the procedure...
cstmt = connection.prepareCall("{ call p_sm_api.createServiceOrder(?,?,?,?)}");
......
and trying to retrieving it like
CallStatus status = (CallStatus)cstmt.getObject(4);
//Callstatus is the object which the procedure returns.

but iam getting error as
java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.
java:6164)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterBytes(
OracleCallableStatement.java:244)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(Oracl
eCallableStatement.java:393)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(Oracl
eCallableStatement.java:462)
at oracle.otnsamples.jdbc.javaobjects.ObjectJavaSample.createSO(ObjectJa
vaSample.java:199)
at oracle.otnsamples.jdbc.javaobjects.ObjectJavaSample.<init>(ObjectJava
Sample.java:62)
at oracle.otnsamples.jdbc.javaobjects.PMTThread.Work(LogToFile.java:95)
at oracle.otnsamples.jdbc.javaobjects.PMTThread.run(LogToFile.java:87)
at java.lang.Thread.run(Thread.java:595)
Exception: java.sql.SQLException: Invalid column type
can you provide a solution to this.
or let me know how to retrieve objects returned by procedures as out parameter with an example.
Many thanks in advance....