asked on

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

mrichmon

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...

Fern2

ASKER

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.

mrichmon

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...

Fern2

ASKER

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

mrichmon

I'm still wondering if there should be any quotes around a default value for an integer column?

I'm not sure in MySQL

Fern2

ASKER

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!?

mrichmon

hmm - yes it is weird if it works directly like that.....

I don't really know....

Fern2

ASKER

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.

mrichmon

Strange that the preserve single quotes wasn't needed otherwise, but congrats.

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

https://www.experts-exchange.com/Community_Support/

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial