Syntax problem 2

[Plogu101]

It keeps throwing a syntax error, I've been banging my head against a wall over it, can any one spot it? I trying to use AUTO_INCREMENT to create a primary key, the tutorial on the MySQL website I don't think is very clear?

statement.executeUpdate("CREATE TABLE `"
                                                + beachName
                                                + "` ( `priKey` INT NOT NULL AUTO_INCREMENT, "
                                                + "`BEACH_NAME` VARCHAR(30), `SCORE` INTEGER, `DATE` VARCHAR(15), "
                                                + "`TIME` INTEGER, `DATEOFFILE` VARCHAR(15), PRIMARY KEY (priKey) )");
                                
                                statement.executeUpdate("INSERT INTO `" + beachName
                                        + "` values ('" + beachName  + "','" + score + "', '"
                                        + date + "' , '" + time + "','" + dateOfFile + "' )"
                                        + Statement.RETURN_GENERATED_KEYS);

 

[r937]

your table has 6 columns, but your INSERT statement provides only 5 values

you can either specify the 5 column names explicitly --

insert into [i]beachname[/i]
( BEACH_NAME,SCORE,`DATE`,`TIME`,DATEOFFILE )
values ( [i]five values[/i] )

or else you can omit the list and provide as many values as columns --

insert into [i]beachname[/i]
values ( NULL, [i]five values[/i] )

note: you should not use DATE and TIME as column names, you should probably combine them into a single DATETIME datatype column, and the datatype for DATEOFFILE should probably be DATE and not VARCHAR(15)

also, it looks like you are creating multiple tables, each with the same layout, these should probably be combined into one table

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Plogu101]

Thanks for your post r937 , I tried passing in Date objects but as I am using MySQL 4.0 something it prefers that date in the format 2005-03-23 ? Yes and the coloumn names are a bit dubious but this is just a prototype.

Anyway I am trying to use the AUTO_INCREMENT function, which I thought would automatically put a value in the 'priKey' column, for me?

The syntax error that I get is 'Check the manual that corresponds to your MySQL server version for the right syntax to use near '(priKey) )' at line one' ?

Using this code

 statement.executeUpdate("CREATE TABLE `"
+ beachName
+ "` ( priKey INT NOT NULL AUTO_INCREMENT, "
+ "`BEACH_NAME` VARCHAR(30), `SCORE` INTEGER, `DATE` VARCHAR(15), "
+ "`TIME` 	   INTEGER, `DATEOFFILE` VARCHAR(15), `PRIMARY_KEY` (priKey) )");

 

[r937]

don't backtick `PRIMARY KEY`, that will be treated as a column name

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Plogu101]

This is nearly working now......The first time round the loop, it creates a table with a primary key column that that is set to 'auto increment'. Fine. Then it will insert the first row, I'm sure that the Statement.RETURN_GENERATED_KEYS
is being used wrongly, and as a result it won't insert the second row next time round the loop, I think. It falls through to the catch , and fails as the table has already been created with that name.

 try
{    
System.out.println("In try   ::");
statement.executeUpdate("INSERT INTO `" + beachName
+ "` values ('" + beachName  + "','" + score + "', '"
+ date + "' , '" + time + "','" + dateOfFile + "' , '" + Statement.RETURN_GENERATED_KEYS + "')");                               
}
catch (Exception b)
{
System.out.println("Inside catch  :::");
                             
statement.executeUpdate("CREATE TABLE `"
+ beachName
+ "`(`BEACH_NAME` VARCHAR(30), `SCORE` INTEGER, `DATE` VARCHAR(15), "
+ "`TIME` INTEGER, `DATEOFFILE` VARCHAR(15), `priKey` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (priKey) )");
                                                              
statement.executeUpdate("INSERT INTO `" + beachName
+ "` values ('" + beachName  + "','" + score + "', '"
+ date + "' , '" + time + "','" + dateOfFile + "' , '" + Statement.RETURN_GENERATED_KEYS + "')");                                        
                            }

When I have the Statement.RETURN_GENERATED_KEYS out side the brackets as in the MySQL tutorial at

"

http://dev.mysql.com/tech-resources/articles/autoincrement-with-connectorj.html"

i get the error 'Column count doesn't match value count at row one' ?

statement.executeUpdate("INSERT INTO `" + beachName
+ "` values ('" + beachName  + "','" + score + "', '"
+ date + "' , '" + time + "','" + dateOfFile + "')", Statement.RETURN_GENERATED_KEYS);

I'm going wrong somewhere.....

 

[r937]

Column count doesn't match value count"

that's pretty specific

if you would please go back to my earlier reply, i did mention two ways to deal with this

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[azzazzello]

There are 6 tables, and only 5 entries in the INSERT, as r937 said. Yes, one of the columns is autoincrementing, but that does not affect the syntax problem here. If you have 6 fields in the table, you must provide values for all of them unless you explicitely specify fields you are inserting. For the autoincrementing field, give a value of 0. It will autoincrement just fine.

 

[r937]

for the auto_increment field, give it a NULL as i showed in the very first reply

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[azzazzello]

0 should work too, no?

 

[r937]

yes

and you'd be surprised, an empty string works too!! for what is patently a numeric column!!!

just another example of mysql's loosey-goosey syntax



however, NULL is best because that is compatible with other databases which utilize an auto-incrementing type of column

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Plogu101]

Just tested it - fantastic it works. Thanks r937 and azzazzello I think that I have been look at it for to long - if you know what I mean? And you did mention it in the first post r937 I just hadn't cottoned on to it......