More ambitious MySQL-JDBC examples

The Java classes described in my last two entries were ideal for a first try at writing code for the MySQL Connector/J driver, because they were essentially trivial - they made a connection, submitted a single SQL query (I call them all queries - some people call them commands or statements) and then disconnected from the database. I shall now try a slightly more ambitious class, intended to populate a table from a text file. Before running it, I need to create the text file in the following format:

Somebody very clever would probably write an SQL query to do that, but as the raw data resides in an Access database, I use the export wizard.

The pane above is the first pane of the export wizard. The default selection for export type is delimited, so I leave that and click Next.

The pane above is the second pane of the export wizard. I select Comma as my delimiter, and I change my text qualifier to {none}. I also click on the Advanced button.

The pane above is the pane opened by the Advanced button in Pane 2. Here I change the date delimiter to a full stop, and I select the Leading Zeros in Dates check box. I then click OK to return to Pane 2 and Next to move to Pane 3.

The pane above is the third pane of the export wizard. Here I simply change the file name to Trans110630.csv and click finish. I then open the file with Notepad and make 3 modifications.

The pane above shows the first modification. Here I replace [space]0:00:00 with nothing.

The pane above shows the second modification. Here I replace .00 with nothing.

The pane above shows the third modification. Here I replace $ with nothing. And I end up with something looking like Pane 1 above. I then move this file to the fin folder in my local code files folder. It is from here that I will run the new class, which in it's JDBC-Derby format is shown on this page. I shall refrain from reprinting the entire code file here, but simply confirm that the following 5 lines from the original file:

// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );

were replaced by the following 5 lines in the new code file:

// Load database driver
Class.forName("com.mysql.jdbc.Driver");
// Make connection
String url = "jdbc:mysql://localhost:3306/dbFin";
connect = DriverManager.getConnection(url,"jhipp", "h1pp0");

Obviously, the text file name was updated as well, and the message came back:

try connection
connection succeeds
try delete
delete succeeds
502 records successfully inserted
disconnection succeeds

which confirmed that the code also worked with the MySQL database.

So far so good, but the real manipulation is in the class, FindbQ1, shown in full on this page. So I converted and ran the code. Once again, there was no problem connecting, but there was an SQL syntax error, which will need investigating:

try connection
connection succeeds
try delete
delete succeeds
try query
INSERT INTO TransComp VALUES
(337, '2001-06-20', 'Sell', 'CRT', 0.45, -6000)
try insert
insert succeeds
query fails
SQLException: 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 'of TrQ' at line 1
disconnection succeeds

To investigate, I reran my Derby-JDBC code with the same text file to see if it comes up with the same or a similar error, and it did. This seems to be a problem with my own code or data, and as it is beyond the scope of the current topic, I shall go silent again until I have fixed it.