aspose file tools*
The moose likes JDBC and the fly likes Running MySql query as a sql script Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Running MySql query as a sql script" Watch "Running MySql query as a sql script" New topic
Author

Running MySql query as a sql script

Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
This query executes just fine at the command line; but I am unsuccessful running it as a script even after looking at examples that seem identical!


mysql> source /WorkspaceForResourceProject/Scripts/ResourceTableCreate.sql
ERROR 1064 (42000): 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 '->resourceId INT(3) auto_increment primary key, ->aCate' at line 1
ERROR 1064 (42000): 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 '?' at line 1
mysql>


Here is the text of the ResourceTableCreate.sql file:

CREATE TABLE resources (->resourceId INT(3) auto_increment primary key,
->aCategory VARCHAR (20),
->url VARCHAR (200),
->title VARCHAR (25),
->createdDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
->UNIQUE (url) )
->ENGINE=MyISAM;

Thank you for any feedback.
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Delete all '->' strings from the query:

CREATE TABLE resources (->resourceId INT(3) auto_increment primary key,
->aCategory VARCHAR (20),
->url VARCHAR (200),
->title VARCHAR (25),
->createdDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
->UNIQUE (url) )
->ENGINE=MyISAM;
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
Here is the output with the continuation characters, "->", removed. I found instructions to use them all over the web as a continuation character. Has anyone exececuted a MySql script from the command line? I'm sure you have so what is incorrect with the above sql when run as a script? The syntax is not the same when running as a script as when running at the command line. It takes seconds to run so please try executing the script and let me know what error message you get. If you know how to modify the script so it can be executed, then please share that knowledge.


mysql> source /Users/userName/WorkspaceForResourceProject/Scripts/ResourceTableCreate.sql
Query OK, 0 rows affected (0.04 sec)

ERROR 1064 (42000): 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 '?' at line 1
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

bother read the whole post first.
So have you tried making the script 1 long line? (don't have mysql here)

Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39436
    
  28
I alwys thought the -> was displayed by MySQL as a continuation prompt; you are not supposed to have -> in the script.
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
Thanks for letting me know to remove the continuation characters and execute the query as one long line; I had done so as an experiment the last time I ran the script producing the following output:


mysql> source /Users/userName/WorkspaceForResourceProject/Scripts/ResourceTableCreate.sqlQuery OK, 0 rows affected (0.28 sec)

ERROR 1064 (42000): 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 '?' at line 1
mysql>


Here is the query executed: CREATE TABLE resources (resourceId INT(3) auto_increment primary key, aCategory VARCHAR (20), url VARCHAR (200), title VARCHAR (25), createdDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE (url) )ENGINE=MyISAM;
 
Thanks for taking a look at this.
 
Don't get me started about those stupid light bulbs.
 
subject: Running MySql query as a sql script