unable to read data from database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: unable to read data from database

Hybrid View

  1. #1
    Join Date
    Jul 2006
    Posts
    2

    unable to read data from database

    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);
    }



    }

  2. #2
    Join Date
    Mar 2004
    Posts
    635
    Can you elaborate on the problem? Are you getting errors or what?

  3. #3
    Join Date
    Jul 2006
    Posts
    2
    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....

Similar Threads

  1. Save Image to Field
    By Chicho in forum VB Classic
    Replies: 11
    Last Post: 10-01-2003, 12:00 AM
  2. Replies: 0
    Last Post: 03-01-2003, 08:17 AM
  3. Replies: 2
    Last Post: 07-24-2002, 05:39 PM
  4. Reading Data from a Web page
    By Jawahar Rajan in forum Java
    Replies: 3
    Last Post: 06-15-2001, 11:33 AM
  5. Database questions
    By Kofi Brown in forum VB Classic
    Replies: 10
    Last Post: 05-08-2000, 09:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center