jdbc/mysql syntax error when using script from file

Thread view:

lionelv@gmail.com - 16 Oct 2006 05:33 GMT

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.

Reply to this Message

Manish Pandit - 16 Oct 2006 17:15 GMT

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

Reply to this Message

Lionel - 16 Oct 2006 22:19 GMT

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

Reply to this Message

Manish Pandit - 16 Oct 2006 22:39 GMT

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

Reply to this Message

Lionel - 17 Oct 2006 00:31 GMT

> You do not have to worry about the OS part as long as
> you're using JDBC drivers..

Yeah true. That was a sill comment on my behalf :).

Reply to this Message

Lionel - 17 Oct 2006 07:32 GMT

> 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
[quoted text clipped - 4 lines]
> 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.

Reply to this Message

Manish Pandit - 17 Oct 2006 10:18 GMT

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.

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.

-cheers,
Manish

Reply to this Message

Lionel - 17 Oct 2006 22:47 GMT

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

Reply to this Message

jdbc/mysql syntax error when using script from file

Free Magazines