Returning datatype
I am using the resultset and the metadata functions to get the column type from an EXCEL sheet. I use the getString(6) function in the resultset..which returns the data type..
When the zips are arranged in random order..this retuens the column type as NUMBER which messes up the program...however if I arrange the column in the descending order ( because of which the zips having a character in them are placed first)...it returns VARCHAR and the program runs perfectly...How can I get it to return NUMBER even if the contents of the column are arranged randomly?
[576 byte] By [
Saurabn84a] at [2007-11-27 9:54:47]
# 1
> How can I get it to return NUMBER even if the contents of the column are arranged randomly?
Do you mean how can you get it to return VARCHAR? as the problem is that you do not wish to work with numbers
As far as i know the type is taken from the first row of teh resultset. There are always problems here because an Excel file is a spreadsheet and is not designed to simulate a database table...
My first reaction would be to convert your excel file into a DBMS which will not make these 'mistakes'.
Otherwise: I am assuming getString() or getInt() are called because the metadata states that this is the appropriate method... you could just explicitly call getString() which can (mostly) return data of any type because most types in the java.sql package has a .toString() method. (The exception being BLOBs) ect.
Bamkin
(appolegies if I have not interpreted the question properly)
# 2
Try using Microsoft Access for a start, if i remember correctly there should be an import/export facility that can help u speed up d process
# 3
As far as i know the type is taken from the first row
> of teh resultset. There are always problems here
> because an Excel file is a spreadsheet and is not
> designed to simulate a database table...
> My first reaction would be to convert your excel file
> into a DBMS which will not make these 'mistakes'.
> Otherwise: I am assuming getString() or getInt() are
> called because the metadata states that this is the
> appropriate method... you could just explicitly call
> getString() which can (mostly) return data of any
> type because most types in the java.sql package has a
> .toString() method. (The exception being BLOBs) ect.
>
> Bamkin
>
> (appolegies if I have not interpreted the question
> properly)
@bamkin
Thanks for your help..I have used Java exactly for that reason...to convert the Excel file into an Access database..I use the getstring function as well..but as you said it checks the first entry and returns the data type..anyway to change that?
# 4
If the datatype is VARCHAR, use Resultset.getString(column),
if the datatype is NUMBER, use Resultset.getString(column),
The JDBC API will convert it into a string. (but not the other way round as a field that contains characters cannot 'become' a number - even if the first couple of lines are retrieved as numbers, an exception will be thrown later down the Resultset).
This cannot be changed as scanning all the way down a (possibly very varge) resultset to determine the datatype would be very processor heavy. For example it may examine the first field and determine "it could be a number string, date or option", then scan the 2nd line and rule out the possibility of a number, then continue and may still have the 'choice' between data or string when it gets to the last row and will still have to guess...DBMSs do just what their titles indicate - they manage the data, and manage the datatypes, ect. A spreadsheet pretending to be a database table will always be flawed.
You could use excel and macros to format the data so it all conforms to the same datatype then use JDBC API and code it in such a way that it assumes to know what the datatype is, explicitly using Resultset.getString() or Resultset.getNumber() as you would then know that it is formatted correctly and will not raise an exception
