Trouble running SQL scripts in MySQL through JDBC ODBC 1

[Jooky68]

I am trying to connect to a mySQL database.I set it up on the ODBC after installing myODBC. I am attempting to dump all the tables in the database through a executequery command and than attempting to run a script with the \. command. Here is my code, but for some rerason I am running into errors with the executequery commands. Any help would be greatly appreciated.

//connection instance
Connection dbConnection_MySQL;
//get URL for mySQL connection
String urlMySQL = "jdbcdbc:WAG";

//load driver for odbc to access MS Access and MySQL
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//make connection to database
dbConnection_MySQL=DriverManager.getConnection (urlMySQL,"root","paul"); //mysql

//**1 This first section drops the tables out of the database
//create a statment
Statement statement=dbConnection_MySQL.createStatement();

//make query
String drop= "Drop Table If Exists ";
for(int i=0;i<table.length;i++) //add all table names
{if(i==(table.length)-1)
drop=drop+"`" +table+"`;";
else
drop=drop+"`" +table+"`,";
}

System.out.println(drop);

//run sql statment
ResultSet DropTables = statement.executeQuery(drop);

//**2 Now we run the scipt to create all the database structure
//set up sql file for the creation of the tables
String createtables ="\\. c:\\Program Files\\MySQL\\MySQL Server 4.1\\bin\\watson2.sql";

System.out.println(createtables);
ResultSet AddTables = statement.executeQuery(createtables);


I keep getting jdbc odbc errors. If the two executeQuery commands are removed program runs smoothly, without updating the databases of course.


Paul

 

[sedj]

5 points :

1) Why are you using JDBC-ODBC with a MySQL database ? It will be MUCH more efficient and stable to use MySQL's own JDBC driver and forget the ODBC bridge.

2) Statement.executeQuery() method takes a SQL string as its argument - but you are passing a string which appears to be a file - this will clearly not work ! (And even if you could pass a file string as a parameter, yours would not work because there are spaces in the path, and you have not encapsulated them in double quotes).

3) If your programme is causing an error, always post the error rather than just saying "it gives an error" - this tells us nothing. We know your programme is causing an error, becaue you posted it here !

4) Not the actual error, but you are not closing any of your db-related objects - which will cause you problems in the future.

5) You should not use executeQuery() to execute an update statement - it is for *queries* (hence its name). You should use executeUpdate() intead (hence the name, *update*).

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Jooky68]

1. I am aware that using the connector/J driver would be better than using the ODBC, but using the ODBC is what was decided on (not by me).

2. I am new to Java, very new as in just started yesterday. I was a little confused with the huge amount of objects, and have had difficutly finding object definitions. I am used to using C++ and Oracle, where I mimic the command I would use in Oracle through a shell in C++. The \. <script file name> is the command that I would use in MySQL to run a script. (Also thank you for spotting the spaces in the file path, I did not realize that and def. solves one of my problems.) How would I run the \. command through Java ?

3.Sorry
4.I did not include all of the code. I do close the connection at the end of my routine.

5. This solved my problem with the first execute command.

Thanks for the quick response, I really appreciate it. It was a big help. Does anyone know a good reference for object/class definitions?

Thanks again

Paul

 

[Jooky68]

Oh also this is ther error that I have been getting. It def has to do with the \. command that I am trying to run.

Problem with JDBC : java.sql.SQLException: [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\. c:\Program Files\MySQL\MySQL Server 4.1\bin\watson2.sql' at line 1
java.sql.SQLException: [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\. c:\Program Files\MySQL\MySQL Server 4.1\bin\watson2.sql' at line 1
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at AccesstoMySQL.mySQLroutines(AccesstoMySQL.java:170)
at AccesstoMySQL.main(AccesstoMySQL.java:92)

 

[sedj]

Basic Java tutorial :

http://java.sun.com/docs/books/tutorial/index.html

Specific tutorials :

http://java.sun.com/developer/onlineTraining/index.html

JDBC tutorial :

http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/

I would really try arguing the case for not using JDBC-ODBC bridge - this really is a very lame way of communicating with a db. If the decision was not made by you, then I would ask for a reasoning as to why you must use it, pointing out that your code will be less efficient using an ODBC bridge

Good luck !

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Jooky68]

thanks for the sites, I'll take a look at them.

Does anyone know how I could run a script file through the JDBC??? I have two files that I want to run. In mySQL i use the \. is there a way that I can run this through JDBC?