MySQL ERROR

Hi

I got a problem with creating database tabels dynamicly.
This is my script:
<cfset table_list = "">
<cfquery name="get_tables" datasource="my_old_database">
      SHOW TABLES;
</cfquery>
<cfloop query="get_tables">
      <cfset table_list = ListAppend(table_list,TABLES_IN_my_old_database)>
</cfloop>      

<cfloop list="#table_list#" index="listelement">
      <cfquery name="show_create" datasource="my_old_database">
            SHOW CREATE TABLE #listelement#
      </cfquery>
      <cfoutput query="show_create">
            <cfquery name="copytable" datasource="my_new_database">
                   #show_create['CREATE TABLE'][currentrow]#
             </cfquery>
      </cfoutput>
</cfloop>

The error I get when I do this is:

-------------- START OF ERROR MESSAGE --------------

Error Executing Database Query.  
Syntax error or access violation: 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 '0'', `newbatch` int(11) NOT NULL default ''0'', `date` time  
 
The error occurred in  line 28
 
26 :       <cfoutput query="show_create">
27 :       <cfquery name="copytable" datasource="my_new_database">
28 :              #show_create['CREATE TABLE'][currentrow]#
29 :        </cfquery>
30 :       

 

--------------------------------------------------------------------------------
 
SQL  

CREATE TABLE `history` ( `id` int(11) NOT NULL auto_increment, `oldbatch` int(11) NOT NULL default ''0'', `newbatch` int(11) NOT NULL default ''0'', `date` timestamp(14) NOT NULL, `inntime1` datetime NOT NULL default ''0000-00-00 00:00:00'', `inntime2` datetime NOT NULL default ''0000-00-00 00:00:00'', PRIMARY KEY (`id`) ) TYPE=MyISAM  
DATASOURCE   my_new_database

-------------- END OF ERROR MESSAGE --------------

Anybody have a clue what is wrong here?

Best regards, Burkni
LVL 1
Fern2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrichmonCommented:
Have you tried checking the syntax of the create table statment.  It looks like you have double quotes where you should have single quotes or maybe no quotes...
0
Fern2Author Commented:
Yes I have. The double quote only appears because IE is displaying the error on the webpage.

I made a script to find the ASC value of the character and the value is 39 which is the value for a single quote.
0
mrichmonCommented:
Have you tried this:

Before line 26 add the following

<cfdump var="#show_create['CREATE TABLE'][currentrow]#">
<cfabort>

See what the query prints as and paste that here.  If there are still double quotes then that is what is being sent in...
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Fern2Author Commented:
This is what I get when I excecute:
<cfdump var="#show_create['CREATE TABLE'][currentrow]#">
<cfabort>

CREATE TABLE `Batch_history` ( `id` int(11) NOT NULL auto_increment, `oldbatch` int(11) NOT NULL default '0', `newbatch` int(11) NOT NULL default '0', `date` timestamp(14) NOT NULL, `inntime1` datetime NOT NULL default '0000-00-00 00:00:00', `inntime2` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) TYPE=MyISAM

See this link: http://www.hugveislan.is/test.cfm
0
mrichmonCommented:
I'm still wondering if there should be any quotes around a default value for an integer column?

I'm not sure in MySQL
0
Fern2Author Commented:
I tried to delete the single quotes and execute it then. But then I got the same error when it came to the default value for datetime '0000-00-00 00:00:00'.

So the single quotes seam to be mandatory there.  

The funniest thing is though, if I copy this code which was generated dynamicly and paste it into my cfm document and then excute it, it works fine.
Like so:
<cfquery name="make_table" datasource="my_new_database">
CREATE TABLE `Batch_history` ( `id` int(11) NOT NULL auto_increment, `oldbatch` int(11) NOT NULL default '0', `newbatch` int(11) NOT NULL default '0', `date` timestamp(14) NOT NULL, `inntime1` datetime NOT NULL default '0000-00-00 00:00:00', `inntime2` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) TYPE=MyISAM
</cfquery>

This is so wierd!?
0
mrichmonCommented:
hmm - yes it is weird if it works directly like that.....

I don't really know....
0
Fern2Author Commented:
I found the answer myself.

PreserveSingleQuotes, thats the key!

<cfset temp_sql = #show_create['CREATE TABLE'][currentrow]#>
      
<cfquery name="copytable" datasource="my_new_database">
     #PreserveSingleQuotes(temp_sql)#;
 </cfquery>

Many thanks mrichmon for trying to help.
0
mrichmonCommented:
Strange that the preserve single quotes wasn't needed otherwise, but congrats.

Post here and have this question PAQ and your points refunded:

http://www.experts-exchange.com/Community_Support/
0
moduloCommented:
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

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.