References to the MySQL manual

The MySQL manual can be downloaded from the MySQL Documentation: MySQL Reference Manuals page of the MySQL web site. A wide range of formats and versions are offered. I chose a PDF file for version 5.5.

My first port of call was Chapter 10 on Data types, and specifically 10.3 on date and time types. I presume I must have done the same for Derby, and used that to guide the design of my text export file. Anyway, it is already obvious that the rules are different for MySQL, and for clarity I quote from the manual:

Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere.

I therefore need to change my selections in the MS Access data export wizard advanced options as follows, with the date delimiter changed to "-" and the date order changed to YMD:

and the dataset should now look like this:

1,2006-09-05,Buy,BSL,6.86,200
2,2006-09-12,Buy,TFC,0.38,2500
3,2006-09-12,Buy,ZFX,10.65,100

I now need to re-import the data to the MySQL database. Fortunately my FindbFill import class deletes everything first (to allow for frequent stuff-ups), so the new dataset should be clean. Unfortunately the name of the text file is hard coded, so I do need to go in and change this, before running it. I then run the modified FindbQ1 class to check the modified screen output. For any reader with a very high boredom threshold, these classes are shown in their original form here.

try sell query loop
Sell Query = SELECT * FROM TransRaw where TrType = 'Sell' order by dDate, TRidx
INSERT INTO TransComp VALUES (420, '2006-09-12', 'Sell', 'ZXF', 10.65, -100)
INSERT INTO TransComp VALUES (410, '2006-12-12', 'Sell', 'BIL', 12.44, -80)
INSERT INTO TransComp VALUES (418, '2007-08-01', 'Sell', 'QCH', 5.9, -300)
INSERT INTO TransComp VALUES (412, '2007-08-15', 'Sell', 'CDO', 6.2, -220)
INSERT INTO TransComp VALUES (417, '2007-11-07', 'Sell', 'LIP', 0.95, -1300)

The output, shown above confirms that the dates have now been stored correctly, and the program is looping through the data in the correct order.

Now to address the second problem identified in my last post, from the Derby reference manual:

A SELECT statement consists of a query with an optional ORDER BY clause and an optional FOR UPDATE clause...The FOR UPDATE clause makes the result an updatable cursor.

And the format seems to be:

Select [fields] from [table] order by [fields] for update of [fields]

Which is precisely the format used in my soft query, shown at the base of this page. Now from the MySQL manual, the syntax of the Select statement is as follows:

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

So at first sight, the only thing wrong with my SQL statement is the little word "of". The field name TrQ also seems to be redundant. So I took them both out and re-ran the FindbQ1S (simply the MySQL version of the FindbQ1) class.

Now the original error dump was:

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

This time, the error dump was:

try connection
connection succeeds
try delete
delete succeeds
try sell query
Sell insert Query = INSERT INTO TransComp VALUES
(420, '2006-09-12', 'Sell', 'ZFX', 10.65, -100)
try insert sell
insert sell succeeds
Buy Query = SELECT * FROM TransRaw where
TrType = 'Buy' and StCode = 'ZFX' for update
try Buy Query
Buy Query succeeds
fBCumQ= 0.0
fSQ= 100.0
query fails
SQLException: Before start of result set
disconnection succeeds

And it represents progress to the extent that second query, the buy query, which caused the initial problem, is now running. However, it appears the diagnostic work must continue, because there is another exception, perhaps further inside the loops.