jdbc/mysql syntax error when using script from file

L

lionelv

I have a text file that contains a script for creating tables in a
mysql database. It works fine when I use MySQL from command line and do
"source fileName". When I read in the file from my Java program and
then use connectionInstance.executeUpdate(scriptString); I get a syntax
error telling me to look near ";". I can't see why there is a problem.

Are there any ideas, perhaps things are handled a little differently
between commandline and jdbc?

thanks

Lionel.
 
M

Manish Pandit

Hi,

With JDBC, you need to execute the statements one by one, not in batch.
You will need to read the file, split out individual statements and
then do executeUpdate in a loop. If every statement in the file has its
own line, you can use a buffered reader to read in the strings.

There is an executeBatch() method too, but AFAIK there is no guarantee
that the JDBC driver will implement it.

-cheers,
Manish
 
L

Lionel

Manish said:
Hi,

With JDBC, you need to execute the statements one by one, not in batch.
You will need to read the file, split out individual statements and
then do executeUpdate in a loop. If every statement in the file has its
own line, you can use a buffered reader to read in the strings.

Thanks it appears you are right.

There is an executeBatch() method too, but AFAIK there is no guarantee
that the JDBC driver will implement it.

I'll look into it but I guess I want it to be certain that it will run
for any dbms on any OS.

Thanks again

Lionel.
 
M

Manish Pandit

You're welcome!

Do check the JDBC driver documentation for its support of
executeBatch(). You do not have to worry about the OS part as long as
you're using JDBC drivers..

-cheers,
Manish
 
L

Lionel

I have a text file that contains a script for creating tables in a
mysql database. It works fine when I use MySQL from command line and do
"source fileName". When I read in the file from my Java program and
then use connectionInstance.executeUpdate(scriptString); I get a syntax
error telling me to look near ";". I can't see why there is a problem.

Are there any ideas, perhaps things are handled a little differently
between commandline and jdbc?

On a related note. To create a new database on mysql I first connect to
the mysql master database called "mysql". I want to do something similar
for postgresql and then any odbc source. However I do not know how I
create the database on these servers in the first place. Does anyone
happen to have any suggestions?

Lionel.
 
L

Lionel

Manish said:
Hi,

For Mysql, I'd recommend downloading their Administrator Tool as well
as the Query Analyzer from http://www.mysql.com/products/tools. You can
log on to your mysql server via the administrator tool and create the
schema (database), user(s) and grant access.

Thanks for the suggestion. I wasn't clear enough. This needs to be part
of an application that will be installed and automatically configure the
database. My application at this stage will install MySQL automatically
if required and then setup the database.

The above is a visual tool and as such is not for these purposes. I can
created the MySQL database automatically, it's the others I'm not sure
about.

For Postgresql try http://www.aquafold.com/index-postgresql.html which
is a neat management tool. In fact, you can use this for your ODBC
datasources too.

The tools you have posted do look nice though :).

Lionel.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top