• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

error in creating new table

this is my xml :
<update id="backupPlayerGameTable" parameterClass="java.lang.String">
         CREATE TABLE player_game_#dateTime# FROM  SELECT * FROM player_game;
 </update>

in java:
 public static void backupPlayerGameTable(String dateTime) throws SQLException {
SqlMapClient sqlMap = UPUWSqlConfig.getSqlMapInstance();
 sqlMap.update("backupPlayerGameTable",dateTime);
}

but it dint work on it..
and the error is
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 ''05012010' FROM  SELECT * FROM player_game' at line 1
0
melwong
Asked:
melwong
  • 6
  • 4
1 Solution
 
Gurvinder Pal SinghCommented:
check this
http://www.informit.com/articles/article.aspx?p=30875

_ and $ are the only chars allowed in a name.

also you dont need 'from' between create and select. It should be
CREATE TABLE player_game_#dateTime# SELECT * FROM player_game;

see this to refer the syntax
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

 
0
 
melwongAuthor Commented:
I need to backup the table player_game as player_game_05012010.
0
 
melwongAuthor Commented:
<update id="backupPlayerGameTable" parameterClass="java.lang.String">
         CREATE TABLE player_game_#dateTime#  SELECT * FROM player_game;
 </update>

this is my xml
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SharathData EngineerCommented:
Did you try as gurvinder mentioned. Are you still getting any error?
0
 
melwongAuthor Commented:
I fix it myself already.
0
 
Gurvinder Pal SinghCommented:
you need to execute a procedure for that then, to create the table name dynamically

select current_timestamp from dual; \\this will give you the date time

set tablename: = concat(tableNm , current_timestamp); \\this will give you the concatenated table and datetime

SET @s = concat('CREATE TABLE', tablename, 'SELECT * FROM player_game');
PREPARE stmt FROM @s;
EXECUTE stmt;

0
 
Gurvinder Pal SinghCommented:
post your solution
0
 
melwongAuthor Commented:
<update id="backupPlayerGameTable" parameterClass="java.lang.String" >
      CREATE  TABLE player_game_$dateTime$ SELECT * FROM player_game;
    </update>
0
 
melwongAuthor Commented:
SimpleDateFormat dateFormat = new SimpleDateFormat("ddMMyyyy");
String dateTime = dateFormat.format(new java.util.Date());

where this should be the way of getting the current date.
0
 
Gurvinder Pal SinghCommented:
@melwong: Isn't this what i suggested to you?

that '_' and '$' are the only valid chars and remove 'from' between create and select
0
 
melwongAuthor Commented:
close the case
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now