Datatype matching

[h4hrr]

Hi all
I am trying to write a code for insert statement with JDBC. But my
problem is related to file reading. I have a *.sql file (or simple text
file) with column names and their datatypes, eg.

CREATE TABLE test (AA DATE,
BB FLOAT,
CC VARCHAR2(256),
DD NUMBER(38) )

How can i read each column name and their corresponding type and store
them for testing later while creating the insert statement. Do i have
to do pattern matching;

any help wud be appreciated..

 

[send2r]

I do not completely understand what you are trying to achieve.
But essentially it looks like a parsing problem. If it is only for a
testing purpose, you may go ahead and write some simple classes to
parse values between Create table statement's field braces.
But if it is something serious, consider using libraries(I dont know if
there are any) that would help you parse SQL. May be you can build your
own using ANTLR or Lex.
hope that helps

 

[h4hrr]

what i am trying to achieve here is that:
1) i have to create an INSERT statement for JBDC and for creating
INSERT statement i need to know the type for each column before i can
insert values. As the columns of the table created has different types;
obv. the insert statement gives me an error if i insert all values as
strings.
2) i have an sql file where i have these value pairs stored as shown in
eg in my prev post
3) Hence I need to know the types for the column from the sql file so
that I can create the INSERT statement in correct syntax. Eg.
INSERT INTO tablename (colName1, colName2, ..) VALUES
('date',float,'String',integer)

as you see, for date n string type, we need ' ' for values, which r not
reqd for float n integer types

hope i am clear..

And as you, most probably do i need to write a parser for text file??
(it can be considered as an sql file also as i added sql extension to
it)

 

[Oliver Wong]

what i am trying to achieve here is that:
1) i have to create an INSERT statement for JBDC and for creating
INSERT statement i need to know the type for each column before i can
insert values. As the columns of the table created has different types;
obv. the insert statement gives me an error if i insert all values as
strings.
2) i have an sql file where i have these value pairs stored as shown in
eg in my prev post
3) Hence I need to know the types for the column from the sql file so
that I can create the INSERT statement in correct syntax. Eg.
INSERT INTO tablename (colName1, colName2, ..) VALUES
('date',float,'String',integer)

as you see, for date n string type, we need ' ' for values, which r not
reqd for float n integer types

hope i am clear..

And as you, most probably do i need to write a parser for text file??
(it can be considered as an sql file also as i added sql extension to
it)

Why don't you query the DB directly to find out the types of the
columns, rather than relying on this file?

- Oliver

 

[Rob Skedgell]

what i am trying to achieve here is that:
1) i have to create an INSERT statement for JBDC and for creating
INSERT statement i need to know the type for each column before i can
insert values. As the columns of the table created has different
types; obv. the insert statement gives me an error if i insert all
values as strings.
2) i have an sql file where i have these value pairs stored as shown
in eg in my prev post
3) Hence I need to know the types for the column from the sql file so
that I can create the INSERT statement in correct syntax. Eg.
INSERT INTO tablename (colName1, colName2, ..) VALUES
('date',float,'String',integer)

as you see, for date n string type, we need ' ' for values, which r
not reqd for float n integer types

hope i am clear..

And as you, most probably do i need to write a parser for text file??
(it can be considered as an sql file also as i added sql extension to
it)

Rather than writing your own parser, might it not involve less pain to
rely on the SQL engine and/or JDBC driver's parser, to use
DatabaseMetadata.getColumns() after creating the table with
Statement.executeUpdate()? This returns a ResultSet with columns
including TABLE_NAME (String), COLUMN_NAME (String) and DATA_TYPE (int
from java.sql.Types). It is also worth considering that different
databases might parse the SQL DDL statement differently to a parser
which you write, or to each other, so this "should" be more portable.

The JDK 1.5.0 javadocs for the method are at
<http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String
%20java.lang.String,%20java.lang.String,%20java.lang.String)>
or (since the above URL is likely to get mangled):
<http://tinyurl.com/f8o2f>

 

[h4hrr]

Thanks for the suggestions, yeah it sounds much better than parsing sql
file and more portable too. I wonder why i thought of parsing file, may
be because due to the fact that i wanted to only use insert statement
as per my application reqd. but def. i can make some changes in it and
rather query the table.
Thanks again

 

[h4hrr]

It works NOW