CF9 <CFQUERY> "INSERT" into a MySQL error, more detalis?

[MareleADI]

Hey,

I have a INSERT cfquery to a Mysql database (Joomla website table) that returns the following error "Error Executing Database Query." and no additional information.

<cfquery datasource="mysql" name="joomla_add_article" result="joomla2_rez">  
INSERT INTO db.jos_content (`title`, `alias`, `introtext`, `state`, `sectionid`, `catid`, `created`, `created_by`, `publish_up`, `attribs`, `ordering`, `metadata`) VALUES ('Title - #LSDateFormat(Now(), "dd.mm.yyyy")# - #LSTimeFormat(Now(), "HH:mm")#', CONCAT('title-',LAST_INSERT_ID()+1), '<p>
        
<object width="640" height="480">
        
<param name="movie" value="http://domain.com/smp2/StrobeMediaPlayback.swf" />
        
<param name="flashvars" value="src=rtmp%3A%2F%2Fdomain.com%2Fdvr%2Fmp4%3A#replace("#arguments.filename#","/","%2F","All")#&streamType=recorded&verbose=true" />
        
<param name="allowFullScreen" value="true" />
        
<param name="allowscriptaccess" value="always" />
        
<param name="wmode" value="direct" /><embed type="application/x-shockwave-flash" width="640" height="480" src="smp2/StrobeMediaPlayback.swf" allowscriptaccess="always" allowfullscreen="true" wmode="direct" flashvars="src=rtmp%3A%2F%2Fdomain.com%2Fdvr%2Fmp4%3A#replace("#arguments.filename#","/","%2F","All")#&streamType=recorded&verbose=true"></embed>
        
</object>
        
</p>
        
<p>#LSDateFormat(Now(), "dd.mm.yyyy")# - #LSTimeFormat(Now(), 'HH:mm')#</p>
        
<p>Duration: #NumberFormat(arguments.duration/60,"999")# minute</p>
        
<p><a href="videos/#arguments.filename#">Download</a> - Size: #NumberFormat(arguments.size,"99999.99")# MB</p>', '1', '2', '3', DATE_SUB(NOW(), INTERVAL '2' hour), '62', DATE_SUB(NOW(), INTERVAL '2' hour), 'show_title=
        
link_titles=1
        
show_intro=
        
show_section=
        
link_section=
        
show_category=
        
link_category=
        
show_vote=
        
show_author=
        
show_create_date=
        
show_modify_date=
        
show_pdf_icon=
        
show_print_icon=
        
show_email_icon=
        
language=
        
keyref=
        
readmore=', '0', 'robots=
        
author=')
</cfquery> 

So my question is how can I get more details about this error it encounters? I can't catch the error at MySQL server level so i need to catch it at application level in CF. Please help me out were to learn how to enable a more verbouse error returning from cfquery.

[Miguel-F]

Wrap your cfquery (or any code for that matter) with a cftry/cfcatch block.  Then dump the cfcatch structure to get the details.  Like this:

<cftry>

    <cfquery datasource="mysql" name="joomla_add_article" result="joomla2_rez"> 

    <!--- all of your query code here --->

     </cfquery>

    <cfcatch type="any">

         <cfdump var="#cfcatch#">

     </cfcatch>

</cftry>

If/when an error occurs in the code contained within the cftry block then the cfcatch block will fire.  cfdump is just a quick way to see ColdFusion variables/structures/objects/queries/etc.

[Dan_Bracuk]

You are using the wrong tag.  Instead of <param> use <cfqueryparam>

[BKBK]

Dan Bracuk wrote:

You are using the wrong tag.  Instead of <param> use <cfqueryparam>

The way I read it, <param> is just part of a string MareleADI wishes to insert into the database.

[BKBK]

@MareleADI

Two suggestions.

1) Remove the 'funny' quotes around the columns names: `title`, `alias`, `introtext`, `state`, `sectionid`, `catid`, `created`, `created_by`, `publish_up`, `attribs`, `ordering`, `metadata`.

2) As I have said above, it seems to me that you wish to insert <p><object width="640" height="480">...#NumberFormat(arguments.size,"99999.99")# MB as a string. If so, defining the string outside the query, such as below, would make it easier to read and debug. Whether or not it will work is another matter.

<cfsavecontent variable="markUpString1"><p>

<object width="640" height="480">

<param name="movie" value="http://domain.com/smp2/StrobeMediaPlayback.swf" />

<param name="flashvars" value="src=rtmp%3A%2F%2Fdomain.com%2Fdvr%2Fmp4%3A#replace("#arguments .filename#","/","%2F","All")#&streamType=recorded&verbose=true" />

<param name="allowFullScreen" value="true" />

<param name="allowscriptaccess" value="always" />

<param name="wmode" value="direct" /><embed type="application/x-shockwave-flash" width="640" height="480" src="smp2/StrobeMediaPlayback.swf" allowscriptaccess="always" allowfullscreen="true" wmode="direct" flashvars="src=rtmp%3A%2F%2Fdomain.com%2Fdvr%2Fmp4%3A#replace("#argum ents.filename#","/","%2F","All")#&streamType=recorded&verbose=true"></ embed>

</object>

</p>

<p>#LSDateFormat(Now(), "dd.mm.yyyy")# - #LSTimeFormat(Now(), 'HH:mm')#</p>

<p>Duration: #NumberFormat(arguments.duration/60,"999")# minute</p>

<p><a href="videos/#arguments.filename#">Download</a> - Size: #NumberFormat(arguments.size,"99999.99")# MB</p>

</cfsavecontent>

<cfsavecontent variable="markUpString2">

show_title=     

link_titles=1     

show_intro=      

show_section=      

link_section=      

show_category=      

link_category=     

show_vote=     

show_author=     

show_create_date=     

show_modify_date=      

show_pdf_icon=     

show_print_icon=      

show_email_icon=      

language=     

keyref=     

readmore=

</cfsavecontent>

<cfquery datasource="mysql" name="joomla_add_article" result="joomla2_rez"> 

INSERT INTO db.jos_content (title, alias, introtext, state, sectionid, catid, created, created_by, publish_up, attribs, ordering, metadata) 

VALUES (

'Title - #LSDateFormat(Now(), "dd.mm.yyyy")# - #LSTimeFormat(Now(), "HH:mm")#',

CONCAT('title-',LAST_INSERT_ID()+1),

<cfqueryparam cfsqltype="cf_sql_varchar" value="#markUpString1#">,

'1',

'2',

'3',

DATE_SUB(NOW(), INTERVAL '2' hour),

'62',

DATE_SUB(NOW(), INTERVAL '2' hour),

<cfqueryparam cfsqltype="cf_sql_varchar" value="#markUpString2#">,

'0',

'robots= author='

)

</cfquery>

[tinu8805]

not sure whether the quotes around fieldnames are converted when pasting the test here. Either remove them completely or check what MySQL needs. According to standards, it should be "...".

Anyway, you can also try to assign the entire arguments string to a cf var. Just for testing. This way,if the entire args CAN be assigned, your quotes stuff in the arguments are parseable and correct.

If the assignment chokes, you could cut it into pieces until you have the part that probably also caused MySQL to complain.

hth,

Martin