SQL "CREATE TABLE" Query not working in java

Question

I'm getting an error on the following query that I can't find how to fix, here's the query code:

CREATE TABLE IF NOT EXISTS `minecraftitems.blocks` (`player` varchar(16) NOT NULL, `itemid` text NOT NULL, `action` text NOT NULL,`time` big(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The error code:

10:48:05 [GRAVE] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CREATE TABLE IF NOT EXISTS `blocks` (`player` varchar(16) NOT NULL, `itemid` tex' at line 1

EDIT: Changed the query to:

            PreparedStatement sql = con.prepareStatement(
                  "SELECT '" + db + "'; CREATE TABLE IF NOT EXISTS `minecraftitems.blocks` (\r\n" + 
                        "    `player` varchar(16) NOT NULL,\r\n" + 
                        "    `itemid` text NOT NULL,\r\n" + 
                        "    `action` text NOT NULL,\r\n" + 
                        "    `time` bigint(20) NOT NULL\r\n" + 
                        "    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;");

and i'm getting this error now:

11:07:22 [GRAVE] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CREATE TABLE IF NOT EXISTS `minecraftitems.blocks` (`player` varchar(16) N' at line 1

PhpMyAdmin runs the query without any problem.

This question appears to be off-topic because it is a "find my typo question" — bummi
– bummi, Commented Nov 1, 2014 at 18:51

Vicky Thakor · Accepted Answer · 2014-11-01 14:58:32Z

1

You have syntax error in your query. Its bigint not just big change it to time bigint(20) NOT NULL

answered Nov 1, 2014 at 14:58

Vicky Thakor

3,9389 gold badges45 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-11-01 15:04:03Z

1

This is your query:

CREATE TABLE IF NOT EXISTS `minecraftitems.blocks` (
    `player` varchar(16) NOT NULL,
    `itemid` text NOT NULL,
    `action` text NOT NULL,
    `time` big(20) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The problem is that big is not a data type. Do you mean bigint or time or timestamp or something else? Storing a field named time as an integer seems suspicious.

Note: This creates a table called "mincraftitems.blocks", which seems the intent of the statement. If you are trying to create blocks in minecraftitems, then you would want:

CREATE TABLE IF NOT EXISTS `minecraftitems`.`blocks` (

edited Nov 1, 2014 at 15:04

answered Nov 1, 2014 at 14:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

user330315 Over a year ago

Shouldn't that also be `minecraftitems`.`blocks`? (backticks around the database and table name each)

Gordon Linoff Over a year ago

@a_horse_with_no_name . . . Perhaps for some perverse reason the OP wants a period in the name of the table. I added a note to this effect.

ax752 Over a year ago

it's for a timestamp & I want to create a table in a database named blocks.

user330315 Over a year ago

@davidp027: if the database is named blocks then you have the name the wrong way round. It must be `blocks`.`minecraftitems`. First you specify the database, then the table name. Please see the manual for details: dev.mysql.com/doc/refman/5.5/en/identifier-qualifiers.html

ax752 Over a year ago

I already selected the database "minecraftitems" & my query works fine when I do it in phpmyadmin. Seems to be my formatting with java

ax752 · Accepted Answer · 2014-11-01 22:17:58Z

0

Made in in one line, and removed the SELECT db; and it worked. Here's my query

PreparedStatement sql = con.prepareStatement("CREATE TABLE IF NOT EXISTS " + db + ".blocks (`player` varchar(16) NOT NULL, `itemid` text NOT NULL, `location` text NOT NULL, `action` text NOT NULL, `time` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1");

answered Nov 1, 2014 at 22:17

ax752

1423 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL "CREATE TABLE" Query not working in java

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related