Reading a SQL file in Java

[Muffcake29]

Hello everyone,

Just wondering if someone could help me with this. I'm working on a JDBC assignment but cannot quite figure out how to read in the required file.

Some example lines are:

insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account, Name, Street, Suburb, Postcode)
values ('07/02/20', '16:42:46', '(', 'S017', 'Strathfield Car Radios ', '689 South Road ', 'Black Forest SA ', 5035);
insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account, Name, Street, Suburb, Postcode)
values ('07/02/20', '16:42:58', '(', 'S018', 'Strathfield Car Radios ', 'Kiosk 4 Westfield ', 'Kilkenny SA ', 5009);

How would I be able to save each value into a string so that I could use java code to execute queries? Since I have to apply it to a table called NewCustomers. If anyone wants me to post the question I will.

Thanks

[r035198x]

Hello everyone,

Just wondering if someone could help me with this. I'm working on a JDBC assignment but cannot quite figure out how to read in the required file.

Some example lines are:

insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account, Name, Street, Suburb, Postcode)
values ('07/02/20', '16:42:46', '(', 'S017', 'Strathfield Car Radios ', '689 South Road ', 'Black Forest SA ', 5035);
insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account, Name, Street, Suburb, Postcode)
values ('07/02/20', '16:42:58', '(', 'S018', 'Strathfield Car Radios ', 'Kiosk 4 Westfield ', 'Kilkenny SA ', 5009);

How would I be able to save each value into a string so that I could use java code to execute queries? Since I have to apply it to a table called NewCustomers. If anyone wants me to post the question I will.

Thanks

You don't have to post the question. You can use Scanner or FileReader to read a file. Look them up.

[dmjpro]

a good thing u r doing.

also u need little bit of parsing the SQL statement ... not the same as ORACLE does it.

actually i telling u ... the start a nd end of a SQL statement ... u have to figure u first then u can seperately execute the code....right.

have a good day.
best of luck with ur approach.

kind regards.

dmjpro.

[Muffcake29]

Oh ok I've read those but I can't seem to apply them to the file I'm reading. I'm trying to use a FileReader and Tokenizer to read the strings but I'm not sure on how to ignore the things I don't need.

For example I want to save the values of the attributes into strings so I can use them in a subsequent query. But one of the attributes, Kind, can have the value '(' but I'm not sure how to make the tokenizer ignore opening brackets except in kind.

i.e. I want the things in bold in a variable
insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account, Name, Street, Suburb, Postcode)
values ('07/02/20', '16:42:46', '(', 'S017', 'Strathfield Car Radios ', '689 South Road ', 'Black Forest SA ', 5035);

Any hints would be greatly appreciated.

[dmjpro]

try to use a character seperator by which u can seperate each query.....because it is too difficult to parsing the SQL start and end point.

and mind it .... try to use that character that nerver occures in ur sql file except at the start and end point of each query.

then tokenize using this delimeter.

best of luck.
have a good day.

kind regards.
dmjpro.

[r035198x]

Oh ok I've read those but I can't seem to apply them to the file I'm reading. I'm trying to use a FileReader and Tokenizer to read the strings but I'm not sure on how to ignore the things I don't need.

For example I want to save the values of the attributes into strings so I can use them in a subsequent query. But one of the attributes, Kind, can have the value '(' but I'm not sure how to make the tokenizer ignore opening brackets except in kind.

i.e. I want the things in bold in a variable
insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account, Name, Street, Suburb, Postcode)
values ('07/02/20', '16:42:46', '(', 'S017', 'Strathfield Car Radios ', '689 South Road ', 'Black Forest SA ', 5035);

Any hints would be greatly appreciated.

Are all the queries in this format or do you have a file with lots of queries with different structures?

[Muffcake29]

Are all the queries in this format or do you have a file with lots of queries with different structures?

They are different structures which I think is determined by the 'Kind' (transaction type) value.

For example:

'Opening Account' kind:

insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account, Name, Street, Suburb, Postcode)
values ('07/02/20', '16:42:46', '(', 'S017', 'Strathfield Car Radios ', '689 South Road ', 'Black Forest SA ', 5035);

'Closing Account' kind:
insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account)
values ('07/02/20', '16:44:50', ')', 'D005');

'Orders' kind:
insert into Transactions (YY_MM_DD, HH_MM_SS, Kind, Account, Item_No, Qty_Ordered)
values ('07/02/20', '16:46:04', '>', 'G015', 'SANDRC', 1);

Once I can get the 'Kind' value (transaction type) I should be able to figure out what to do with the other values.

Well I'll try and do what dmjpro suggested, hopefully I can figure it out ehehe

Thanks guys