How to Execute 2 or more insert statements using CFQuery in coldfusion?

Question

Is it possible to Execute 2 insert or Update Statements using cfquery?

If yes how?

if no, what is the best way to execute multiple queries in Coldfusion, by opening only one Connection to DB.

I think every time we call cfquery we are opening new connection DB

Copybook

Answer 1

Within the data source settings you can tell it whether to keep connections open or not with the Maintain Connections setting.

Starting with, I believe, ColdFusion 8 datasources are set up to run only one query at a time due to concerns with SQL injection. To change this you would need to modify with the connection string.

Your best bet is to turn on Maintain Connections and if needed use cftransaction:

<cftransaction>
<cfquery name="ins" datasource="dsn">
insert into table1 values(<cfqueryparam value="#url.x#">)
</cfquery>
<cfquery name="ins" datasource="dsn">
insert into table2 values(<cfqueryparam value="#url.x#">)
</cfquery>
</cftransaction>

And always, always use cfqueryparam for values submitted by users.

Villon

HI, Sam Thanks for Response, Can you tell me where can I found that maintain Connection setting in coldfusion? and is that True if I call <cfquery> 2 times, it ll be like opening DB connection 2 times? – Copybook

You can run as many SQL statements inside a single cfquery as you want, even jumping across physical databases. – Tuscarora

@Sam - Can you elaborate on "run only one query at a time"? I have never heard of anything like this. Just default restrictions at a driver/connection level. – Incantation

@Leigh: I just tested it with a Derby db and SQLServer 2008. Derby will not allow multiple queries whereas SQLServer 2008 will. I'm pretty sure the MySQL driver is configured to not allow multiple queries. Either way I'd recommend using multiple cfquery statements. – Villon

@Sam - Oh, okay. That is what I was talking about ie Driver defaults. At first read, it sounded like you were talking about a system wide CF setting ;) – Incantation

@Leigh: Sorry for the confusion. – Villon

@Sam if you are going to use cftransaction you should use a cftry and cfcatch with a cftransaction attribute of BEIGN and COMMIT or ROLLBACK especially with multiple inserts – Partiality

Answer 2

Is it possible to Execute 2 insert or Update Statements using cfquery?

Most likely yes. But whether you can run multiple statements is determined by your database type and driver/connection settings. For example, when you create an MS SQL datasource, IIRC multiple statements are allowed by default. Whereas MySQL drivers often disable multiple statements by default. That is to help avoid sql injection. So in that case you must to enable multiple statements explicitly in your connection settings. Otherwise, you cannot use multiple statements. There are also some databases (usually desktop ones like MS Access) that do not support multiple statements at all. So I do not think there is a blanket answer to this question.

If the two insert/update statements are related, you should definitely use a cftransaction as Sam suggested. That ensures the statements are treated as a single unit: ie Either they all succeed or they all fail. So you are not left with partial or inconsistent data. In order to accomplish that, a single connection will be used for both queries in the transaction.

I think every time we call cfquery we are opening new connection DB

As Sam mentioned, that depends on your settings and whether you are using cftransaction. If you enable Maintain Connections (under Datasource settings in the CF Administrator) CF will maintain a pool of open connections. So when you run a query, CF just grabs an open connection from the pool, rather than opening a new one each time. When using cftransaction, the same connection should be used for all queries. Regardless of whether Maintain Connections is enabled or not.

Incantation

Answer 3

the mySQL driver in CF8 does now allow multiple statements. as Sam says, you can use to group many statements together or in the coldfusion administrator | Data & Services | Data sources, add allowMultiQueries=true to the Connection String field

Francklyn

Answer 4

I don't have CF server to try, but it should work fine IIRC.

something like:

<cfquery name="doubleInsert" datasource="dsn">
insert into table1 values(x,y,z)
insert into table1 values(a,b,c)
</cfquery>

if you want a more specific example you will have to give more specific information.

Edit: Thanks to @SamFarmer : Newer versions of CF than I have used may prevent this

Intendance

Probably need semi-colons between those statements. – Polaris

Answer 5

Sorry for the Necro (I'm new to the site).

You didn't mention what DB you're using. If you happen to use mySQL you can add as many records as the max heap size will allow.

I regularly insert up to ~4500 records at a time with the default heap size (but that'll depend on the amount of data you have).

INSERT INTO yourTable (x,y,z) VALUES ('a','b','c'),('d','e','f'),('g','h','i')

All DBs should do this IMO.

HTH

Strapper

Answer 6

Use CFTRANSACTION to group multiple queries into a single unit.

Any queries executed with CFQUERY and placed between and tags are treated as a single transaction. Changes to data requested by these queries are not committed to the database until all actions within the transaction block have executed successfully. If an error occurs in a query, all changes made by previous queries within the transaction block are rolled back.

Use the ISOLATION attribute for additional control over how the database engine performs locking during the transaction.

For more information visit http://www.adobe.com/livedocs/coldfusion/5.0/CFML_Reference/Tags103.htm

Emotion