5 Replies Latest reply on Jan 4, 2013 8:04 AM by tinu8805

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

    MareleADI Level 1

      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")#&amp;streamType=recorded&amp;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")#&amp;streamType=recorded&amp;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.

        • 1. Re: CF9 <CFQUERY> "INSERT" into a MySQL error, more detalis?
          Miguel-F Level 3

          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.

          • 2. Re: CF9 <CFQUERY> "INSERT" into a MySQL error, more detalis?
            Dan Bracuk Level 5

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

            • 3. Re: CF9 <CFQUERY> "INSERT" into a MySQL error, more detalis?
              BKBK Adobe Community Professional & MVP

              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.

              • 4. Re: CF9 <CFQUERY> "INSERT" into a MySQL error, more detalis?
                BKBK Adobe Community Professional & MVP

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

                • 5. Re: CF9 <CFQUERY> "INSERT" into a MySQL error, more detalis?
                  tinu8805 Level 1

                  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