jdbc/mysql syntax error when using script from file

Discussion in 'Java' started by lionelv@gmail.com, Oct 16, 2006.

  1. Guest

    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.
    , Oct 16, 2006
    #1
    1. Advertising

  2. 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
    Manish Pandit, Oct 16, 2006
    #2
    1. Advertising

  3. Lionel Guest

    Manish Pandit wrote:
    > 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.
    Lionel, Oct 16, 2006
    #3
  4. 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
    Manish Pandit, Oct 16, 2006
    #4
  5. Lionel Guest

    Manish Pandit wrote:
    > 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 :).
    Lionel, Oct 17, 2006
    #5
  6. Lionel Guest

    wrote:
    > 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.
    Lionel, Oct 17, 2006
    #6
  7. 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
    Manish Pandit, Oct 17, 2006
    #7
  8. Lionel Guest

    Manish Pandit wrote:
    > 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.
    Lionel, Oct 17, 2006
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    733
    Keith Wansbrough
    Aug 16, 2004
  2. Betty
    Replies:
    1
    Views:
    9,843
    Juha Laiho
    May 21, 2005
  3. Jesus M. Salvo Jr.
    Replies:
    2
    Views:
    4,016
    robert
    Feb 11, 2006
  4. Bruce
    Replies:
    4
    Views:
    735
    Bjorn Abelli
    Mar 25, 2006
  5. RVic
    Replies:
    7
    Views:
    194
    Mike Amling
    Nov 17, 2013
Loading...

Share This Page