Datatype matching

Thread view:

h4hrr - 20 Feb 2006 15:51 GMT

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..

Reply to this Message

send2r@gmail.com - 20 Feb 2006 16:12 GMT

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
RC

> Hi all
> I am trying to write a code for insert statement with JDBC. But my
[quoted text clipped - 11 lines]
>
> any help wud be appreciated..

Reply to this Message

h4hrr - 20 Feb 2006 16:24 GMT

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)

Reply to this Message

Oliver Wong - 20 Feb 2006 17:19 GMT

> what i am trying to achieve here is that:
> 1) i have to create an INSERT statement for JBDC and for creating
[quoted text clipped - 17 lines]
> (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

Reply to this Message

Rob Skedgell - 20 Feb 2006 17:39 GMT

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#getColumn
s(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>

Signature

Rob Skedgell <rob+news@nephelococcygia.demon.co.uk>
GnuPG/PGP: 7DA3 1579 C0DD 8748 C05A B984 E2A2 3234 D14B 6DD7

Reply to this Message

h4hrr - 21 Feb 2006 11:25 GMT

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

Reply to this Message

h4hrr - 21 Feb 2006 14:16 GMT

It works NOW :-)

Reply to this Message

Datatype matching

Free Magazines