Skip to main content
Participant
August 10, 2010
Question

View SQL before execution?

  • August 10, 2010
  • 3 replies
  • 1115 views

I'm a newbie to CF, so I apologize in advance as I've searched everywhere and cannot find the answer to the following.  I have a .html forms page that submits user-entered data to a .cfc for insertion into a MySQL db (insert code is below.)  The insert statement isn't working, and I don't get an error returned, so I'd like to view what the compiled SQL INSERT statement looks like before it actually attempts to hit the database.  This way I can copy and paste the SQL into my DB and see if I can debug.  I can confirm I am capturing all data from the .html page before submission to the .cfc.  Any and all assistance is greatly appreciated.  Thanks!

<cfquery name="qInsertEvent" datasource="dsnMySQL">

              INSERT INTO

                  tblEvents

                   (event_type, event_status, event_category, event_name, event_date, event_stateCode

<!--Optional fields: NULL-->

<cfif isDefined("arguments.event_city")>,event_city</cfif>

<cfif isDefined("arguments.event_venue")>,event_venue</cfif> 

<cfif isDefined("arguments.event_timeStr")>,event_time</cfif>) 

                   

                  VALUES

                    (2, 'A',

<cfqueryparam value="#arguments.event_category#" cfsqltype="cf_sql_varchar"/>,

<cfqueryparam value="#arguments.event_name#" cfsqltype="cf_sql_varchar"/>,

                    <cfqueryparam value="#arguments.event_dateStr#" cfsqltype="cf_sql_varchar"/>,

<cfqueryparam value="#arguments.event_stateCode#" cfsqltype="cf_sql_varchar"/>,

<!--Optional fields: NULL-->

<cfif isDefined("arguments.event_city")>

                          <cfqueryparam value="#arguments.event_city#" cfsqltype="cf_sql_varchar"/>,

                    </cfif>

<cfif isDefined("arguments.event_venue")>

  <cfqueryparam value="#arguments.event_venue#" cfsqltype="cf_sql_varchar"/>,

                    </cfif>

                    <cfif isDefined("arguments.event_timeStr")>

                          <cfqueryparam value="#arguments.event_timeStr#" cfsqltype="cf_sql_varchar"/>

                    </cfif>)

          </cfquery>

This topic has been closed for replies.

3 replies

Inspiring
August 11, 2010

I noticed that you have an HTML style comment inside your CFQUERY block.  Try changing this comment to a CFML comment block.

<!--Optional fields: NULL-->

should be:

<!--- Optional fields: NULL --->

Note the extra dashes.

SABmoreAuthor
Participant
August 11, 2010

Thanks.  The combo of answers has helped me get to a point where I can display the query.  Problem I'm experiencing now is my parameters aren't getting passed into the query.

This is what I get:

INSERT INTO tblEvents (event_type, event_status, event_category, event_name, event_date, event_stateCode ,event_city ,event_venue ,event_time) VALUES (2, 'A', ?, ?, ?, ? ,? ,? ,? )

Above the query, I have the following:

<cfargument name="event_category"     type="string"  required="true"/>

<cfargument name="event_name"   type="string"  required="true"/>

<cfargument name="event_venue"  type="string"  required="false"/>....

I know the parameters are being captured from the input screen as I've manually appended them to the URL for the CFC, and ran it only to get an error on any missing required parameters.

Any thoughts?  Thanks again!

SABmoreAuthor
Participant
August 11, 2010

Scratch my last post.  I now realize that the ?s are part of CF.  ugh.  Thanks!

Inspiring
August 11, 2010

You need to look @ how you're handling your commas in you option <cfqueryparam> tags.

If arguments.event_timeStr isn't defined, you'll end up with a syntax error in your SQL (a dangling comma).

This should cause an error message though, so might not be the entirety of your problem.

--

Adam

Inspiring
August 10, 2010

<cfsetting> enables you to see debugging information.

One thing I notice from your post though, is that you are storing dates and times as strings.  Bad idea.

SABmoreAuthor
Participant
August 10, 2010

Thanks!

SABmoreAuthor
Participant
August 11, 2010

I tried adding the <cfsetting showdebugoutput="YES">, but to no avail.  I don't have Admin access to the server, as it's being hosted by another company.   I know the SQL statement is probably incorrect (i.e.-date vs. string), but until I can actually see what is being passed, I'm sort of dead in the water.  Any other thoughts?  Thanks again.