| Accesing value of attributes from an array of objects [message #240828] |
Sat, 26 May 2007 04:23 |
Rashmi_Katariya
Messages: 9
Registered: April 2007
|
Junior Member |
|
|
Hi All,
Iam writing an application to read from Excel in Java and load the data into the Oracle database. These are the steps i have followed.
Java Program 1 :Company.java
/*
import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
public class Company implements SQLData, Serializable{
public Company(){
}
private String company_id;
private String company_name;
public String getCompany_id() {
return company_id;
}
public void setCompany_id(String company_id) {
this.company_id = company_id;
}
public String getCompany_name() {
return company_name;
}
public void setCompany_name(String company_name) {
this.company_name = company_name;
}
*/
Java Program 2 : ExcelReader.java
package javasample;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import jxl.*;
import jxl.read.biff.*;
import java.io.File;
import javasample.*;
public class ExcelReaderObjects {
public static void passArray() throws SQLException {
Connection conn = null;
OraclePreparedStatement ps = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getDefaultConnection()
Workbook workbook = Workbook.getWorkbook
(new File("exceltest.xls"));
Sheet sheet = workbook.getSheet(0);
int noOfRows = sheet.getRows();
int noOfCols = sheet.getColumns();
ExcelReaderObjects a = new ExcelReaderObjects();
Company[] comp = new Company[noOfRows];
Company compObj = null;
for(int i=1;i<noOfRows;i++) {
compObj = new Company();
compObj.setCompany_id(
(sheet.getCell(0,i).getContents()));
compObj.setCompany_name(
(sheet.getCell(1,i).getContents()));
comp[i] = compObj;
}
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "COMPANY_TYPE_TAB", conn );
ARRAY array_to_pass = new ARRAY( descriptor, conn, comp);
ps = (OraclePreparedStatement)conn.prepareStatement
( "begin import_excel( ); end;" );
ps.setARRAY( 1, array_to_pass );
ps.execute();
}catch(Exception e) {
System.out.println(e);
}finally{
if(ps != null){
ps.close();
}
if(conn != null){
conn.close();
}
}
}
}
-------
My table structures are as follows:
SQL > create or replace type COMPANY_TYPE is object
(company_id varchar2(10),
company_name varchar2(100))
SQL> CREATED
SQL>create or replace type COMPANY_TYPE_TAB is table of COMPANY_TYPE;
SQL>CREATED
My Procedure is :
SQL> Create or replace table test_array (company_id varchar2(10),
company_name varchar2(100) );
SQL> CREATED
SQL> Create or replace procedure import_excel(p_comparray in company_type) as
v_comp_obj company_type;
begin
execute immediate 'truncate table test_array';
insert into test_array
select * from table(p_comparray);
end import_excel;
PROBLEM : when i run my java program , blank rows get inserted in the table. If i have 10 records in my excel sheet, 10 blank rows get inserted. Iam unable to retrieve the value of company_id and cmpany_name attributes. p_comparray.company_id and p_comparray.company_name also does not work.
My procedure is accepting the array of objects of type COMPANY_TYPE. How can i get the value of the attributes of each objects?
Any help appriciated.
Thanks,
Rashmi.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
