Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL ERROR

Posted on 2004-11-08
11
Medium Priority
?
150 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:Fern2
  • 5
  • 4
10 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 12526287
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
 
LVL 1

Author Comment

by:Fern2
ID: 12526498
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 12526655
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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
LVL 1

Author Comment

by:Fern2
ID: 12527380
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 12527434
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
 
LVL 1

Author Comment

by:Fern2
ID: 12528127
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 12528616
hmm - yes it is weird if it works directly like that.....

I don't really know....
0
 
LVL 1

Author Comment

by:Fern2
ID: 12528938
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 12528979
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12574343
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month20 days, 16 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question