Java SQL AND OTHER ODDITIES ( general discussion)

[steve]

Hi,

just thought i would throw this about for a general discussion.

i have an address database with about 84 fields per record ( mix of strings,
numbers keys dates etc.
The records are pulled over from the database ( single name & a key), in
groups a-b,c-d, or whatever the user chooses.

if the user has no data to pull from the database, and wants to input the
first record, or a new record
what is the best way to bring it up the object array from scratch, and how
to 'flavour' the new record object, with the correct mix of strings &
numbers, without having to hard code the field types into the record object.


I have the following rough code structure to write the record out ( note it
needs tidying up), which is a fairly generic write routine that passes the
data to an oracle pl/sql routine.
currently, to add a new record I have to flavour the "object array", by
selecting another record, then enter the new data.

What are other people doing, are you using the record metadata, or some other
system.




public boolean putOneRecord(Object[] therecord, int action) {
boolean result = false; //set it to bad result
int pointer = 0; //this is the paramater pointer

try {
// OracleCallableStatement cstmt = null;
OraclePreparedStatement cstmt = null;

String The_qry = "{call oracle.details.add_DETAILS(" +
"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + //20
"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + //20
"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + //20
"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?" + //75

//the last one is the action code
",'" + String.valueOf(action) + //this says is it insert
or update
"')}";

// cstmt = (OracleCallableStatement)
my_vars.dbconn.prepareCall(The_qry);
cstmt = (OraclePreparedStatement)
my_vars.dbconn.prepareCall(The_qry);

for (int params = 0; params < therecord.length; params++) {
//don't do the following fields
if (((params != SupplierRecordStructure.updated) &&
(params != SupplierRecordStructure.born) &&
(params != SupplierRecordStructure.i_locked) &&
(params != SupplierRecordStructure.uni_indx) &&
(
// params!=SupplierRecordStructure.nodial &&
params != SupplierRecordStructure.deleted) &&
(params != SupplierRecordStructure.site) &&
(params != SupplierRecordStructure.rep_indx00) &&
(params != SupplierRecordStructure.frst_lttr) &&
(params != SupplierRecordStructure.website))) {
pointer++; // increment the pointer for passing the
parameter

//finally check the object types
if (therecord[params] instanceof String) {
// st.setLong(2, parent); //the parent object
(root)
cstmt.setString(pointer,
therecord[params].toString()); // Bind any other index
} else {
if (therecord[params] instanceof BigDecimal) {
cstmt.setLong(pointer,
Integer.parseInt(therecord[params].toString())
); // Bind any other index

// cstmt .setString(pointer,
therecord[params].toString()); // Bind any other index
} else {
if (therecord[params] instanceof java.util.Date)
{
// new java.sql.Date(new
java.util.Date().getTime());
java.util.Date temp = (java.util.Date)
therecord[params];
cstmt.setDate(pointer,
new java.sql.Date(temp.getTime()));
} else {
//we cannot do this as they ARE ALL
INSTANCE OF OBJECT
if (therecord[params] instanceof Object) {
//we should not have any objects to
write out
// cstmt
..setLong(pointer,Integer.parseInt( therecord[params].toString())); // Bind
any other index
} else {
//all above tests have failed set
the type to something that will break
// throw "typeNotKnown ";
// cleanthis[ik] = "-";
}
}
}
}
} else {
//this is where we come if it is a field we do
not need to write out
;
}
}

cstmt.execute();

// rset.close();
cstmt.close();
result = true; //finally set flag to good
} catch (Exception e) {
Error_stuff.handleError(e, -1, -1);
}

return result;
}

 

[Chris Uppal]

if the user has no data to pull from the database, and wants to input the
first record, or a new record
what is the best way to bring it up the object array from scratch, and
how to 'flavour' the new record object, with the correct mix of strings &
numbers, without having to hard code the field types into the record
object.

One way might be to use a template object. I.e. your application code doesn't
normally ever just "create" a blank object, instead it clones a specially
designated object that has been initialised for that purpose (possibly by
reading it from the database, possibly not).

-- chris

 

[Sudsy]

steve wrote:

What are other people doing, are you using the record metadata, or some other
system.

I use DatabaseMetaData.getColumns() for the table of interest.

 

[steve]

steve wrote:


I use DatabaseMetaData.getColumns() for the table of interest.

yep it's the
column.getDataType());

that i needed.
I can get the data for "free" , by including it in the loop when i first get
a record instead of doing a "double call" to get the coltype & data.