Prepared Statements 1

[evergrean100]

Does Cold Fusion support PreparedStatements?

I use them in Java and was wondering if Cold Fusion also has them for SQL inserts and updates into Oracle and Access databases?

 

[Sarky78]

evergrean100,

Cf uses a tag called cfqueryparam to achieve this, with my limited knowledge of jsp.

a query like:

<cfquery datasource="..">
  INSERT INTO tblOne (ID, Title)
  VALUES (10, 'TEST')
</cfquery>

would become:

<cfquery datasource="..">
  INSERT INTO tblOne (ID, Title)
  VALUES (<cfqueryparam cfsqltype="cf_sql_integer" value="10">, <cfqueryparam cfsqltype="cf_sql_varchar" value="TEST">)
</cfquery>

If you have debug turned on then you will see the statement that is sent to the database is near to the jsp syntax, with the above queryparams being replaced with question marks, and the values below those. This is mainly because CF compiles down to java.

The above will work on any database platform that you have a driver for, and cf ships with drivers for oracle and access.

If you want to know more about the queryparam tag have a look on live docs:

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_18.html

You can also use stored procedures if you want to, which work in the same sort of way:

<cfstoredproc datasource="..." procedure="sp_insert">
	<cfprocparam type="in" cfsqltype="cf_sql_integer" value="10">
	<cfprocparam type="in" cfsqltype="cf_sql_varchar" value="Test">
</cfstoredproc>

Live docs:

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_r-s_22.html

Hope this helps!

Tony

 

[evergrean100]

Thanks, good info. I assume from your example the queryparam is equal to a preparedStatement in Java.

 

[cfStarlight]

The best way to determine how the statements are handled is to enable tracing and review the actual statements sent to the database. IIRC MS SQL for example does an sp_prepexec call for queries containing cfqueryparam. Subsequent calls of the query are run through sp_execute. I don't know about stored procedures.