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
melwongAsked:
Who is Participating?
 
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.