Geek Answers Handbook

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

Is it possible to perform an insert or update using cfquery ?

If so, how?

if not, which is the best way to execute multiple queries in Coldfusion by opening only one database connection.

I think every time we call cfquery , we open a new DB connection

+4
source share
6 answers

In the data source settings, you can specify whether to open or not open connections with the "Maintain connections" setting.

Starting with, I believe, ColdFusion 8 data sources are configured to run only one query at a time due to problems with SQL injection. To change this, you will need to change the connection string.

It is best to enable Linking and use cftransaction if necessary:

 <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 ​​provided by users.

+4
source

Is it possible to perform 2 insert or Update statements using cfquery?

Probably yes. But whether it is possible to run several statements is determined by the type of your database and the driver / connection. For example, when you create an MS SQL data source, multiple IIRC statements are enabled by default. While MySQL drivers often disable several statements by default. This will help avoid sql injection. Therefore, in this case, you must explicitly enable several operators in the connection settings. Otherwise, you cannot use multiple operators. There are also some databases (usually desktop ones like MS Access) that do not support multiple statements at all. Therefore, I do not think that there is a general answer to this question.

If the two insert / update statements are related to each other, you should definitely use cftransaction, as Sam suggested. This ensures that statements are treated as a whole: i.e. either they all succeed, or they all fail. Thus, you are not left with partial or inconsistent data. To achieve this, a single connection will be used for both queries in a transaction.

I think every time we call cfquery we open a new DB connection

As Sam said, it depends on your settings and whether you use cftransaction. If you enable “Liaison” (in the “Data source settings in CF-administrator” section), CF will support the open connection pool. Therefore, when you run a query, CF just grabs an open connection from the pool, rather than opening every new one. When using cftransaction, the same connection should be used for all requests. Regardless of whether connection support is enabled or not.

+5
source

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

sort of:

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

if you need a more specific example, you will need to provide more specific information.

Edit: Thanks @SamFarmer: newer versions of CF than I used can prevent this

+3
source

The CF8 mySQL driver now allows multiple statements. as Sam says, you can use to group many statements together or in a cold administrator | Data and Services | Data Sources, Add allowMultiQueries = True in Connection String

+2
source

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

You did not indicate which database you are using. If you use mySQL, you can add as many records as the maximum heap size allows.

I regularly insert up to ~ 4,500 records at a time with the default heap size (but it depends on the amount of data you have).

INSERT TYPICAL (x, y, z) VALUES ('a', 'b', 'c'), ('d', 'e', ​​'f'), ('g', 'h', 'I am')

All databases must do this IMO.

NTN

+1
source

Use CFTRANSACTION to group multiple queries into one block.

Any queries made using CFQUERY and placed between them and tags are treated as a single transaction. Changes to the data requested by these queries are not transferred to the database until all actions in the transaction block are completed successfully. If an error occurs in the request, all changes made by previous requests in the transaction block will be rolled back.

Use the ISOLATION attribute for additional control over how the database engine locks during a transaction.

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

0
source

More articles:


All Articles