RE: Inserting and CFTransaction
It seems that many problems I see in this list revolve around determining the next record for an insert and methods to use cftransaction as a form of database locking to achieve this purpose. So, I figured I would add two cents that might help some of you. First, what you can do is determined by what product you are using. Oracle has a nifty little table called DUAL. It's a system table that will always have only one row. So, if you Select nextval from Dual you will have a unique id you can then store in a variable and use for inserts. If you use this for all your tables, then your ID values will be different in all of your tables, an interesting thing to play with in joins or table merges. No record will ever have the same ID. Beware, I have heard of resetting the Dual Table. Never had it happen to me. Access doesn't have any good way to prevent the multiple simultaneous insert problem. So, by using CFCatch, and CFTransaction you can catch the error of the second insert, rollback the changes and try again with a new value. CFTransaction seems to suggest that your set of queries will all run sequentially and then allow others to access the instance. But, in truth, I don't think the tables are locked. Surrounding the code with CFLock will supposedly single thread your server's requests, and if your server has exclusive access to the datasource, that's great. If others also access your datasource, then even that won't help you. But, let's get realistic. If you're using Access, you can achieve a more than reasonable level of success using CFLock, CFCatch and CFTransaction. (does CFTransaction roll back in Access, I can't remember) SQL Server is a bit different. You could use the Access method above with reasonable success. However, you would be better off to use something like the following code. cfquery name=insertPeople datasource=#dsn1# set nocount on insert into people values ('#thepassword#', '#theusername#'') SELECT lastid = @@identity set nocount off /cfquery Surround this query with CFLock, CFCatch, and CFTransaction to make it almost bulletproof. Simply refer to #insertPeople.lastid# in the subsequent queries wherever you want to sync the new id. Of course, the very best way to make 100% certain that you are preserving the ID in SQL server or Oracle is to use a Stored Procedure and lock the tables while executing the methods above. Good Fortune, Rick Walters Webmaster, Davita Laboratory Services Office: (800) 604-5227 Cell: (407) 491-9848 __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting and CFTransaction (correction)
cfquery name=insertPeople datasource=#dsn1# set nocount on insert into people (password, username) values ('#thepassword#', '#theusername#'') SELECT lastid = @@identity set nocount off /cfquery Good Fortune, Rick Walters Webmaster, Davita Laboratory Services Office: (800) 604-5227 Cell: (407) 491-9848 -Original Message- From: Rick Walters [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 10:48 AM To: CF-Talk Subject: RE: Inserting and CFTransaction It seems that many problems I see in this list revolve around determining the next record for an insert and methods to use cftransaction as a form of database locking to achieve this purpose. So, I figured I would add two cents that might help some of you. First, what you can do is determined by what product you are using. Oracle has a nifty little table called DUAL. It's a system table that will always have only one row. So, if you Select nextval from Dual you will have a unique id you can then store in a variable and use for inserts. If you use this for all your tables, then your ID values will be different in all of your tables, an interesting thing to play with in joins or table merges. No record will ever have the same ID. Beware, I have heard of resetting the Dual Table. Never had it happen to me. Access doesn't have any good way to prevent the multiple simultaneous insert problem. So, by using CFCatch, and CFTransaction you can catch the error of the second insert, rollback the changes and try again with a new value. CFTransaction seems to suggest that your set of queries will all run sequentially and then allow others to access the instance. But, in truth, I don't think the tables are locked. Surrounding the code with CFLock will supposedly single thread your server's requests, and if your server has exclusive access to the datasource, that's great. If others also access your datasource, then even that won't help you. But, let's get realistic. If you're using Access, you can achieve a more than reasonable level of success using CFLock, CFCatch and CFTransaction. (does CFTransaction roll back in Access, I can't remember) SQL Server is a bit different. You could use the Access method above with reasonable success. However, you would be better off to use something like the following code. cfquery name=insertPeople datasource=#dsn1# set nocount on insert into people values ('#thepassword#', '#theusername#'') SELECT lastid = @@identity set nocount off /cfquery Surround this query with CFLock, CFCatch, and CFTransaction to make it almost bulletproof. Simply refer to #insertPeople.lastid# in the subsequent queries wherever you want to sync the new id. Of course, the very best way to make 100% certain that you are preserving the ID in SQL server or Oracle is to use a Stored Procedure and lock the tables while executing the methods above. Good Fortune, Rick Walters Webmaster, Davita Laboratory Services Office: (800) 604-5227 Cell: (407) 491-9848 __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting and CFTransaction
Access doesn't have any good way to prevent the multiple simultaneous insert problem. So, by using CFCatch, and CFTransaction you can catch the error of the second insert, rollback the changes and try again with a new value. CFTransaction seems to suggest that your set of queries will all run sequentially and then allow others to access the instance. But, in truth, I don't think the tables are locked. In Access, whenever a record within a table is modified, the entire table is exclusively locked. So, with CFTRANSACTION around both the query that modifies a record, and the query that retrieves the autonumber value, no one else will be able to access the table and insert another record until the transaction has completed. With other platforms, however, such as SQL Server, the entire table won't be locked. Note that there's no need to use CFTRY/CFCATCH in the above example, unless your intent is to check a unique index or something along those lines. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting and CFTransaction
Thanks for the clarification on Access, I believe you're right since it's not a truly relational product. However, I always use CFTry and CFCatch with Inserts and Update queries. The majority of errors occur when modifying the data. So, even if I roll back the change, I want to catch the error and redirect the user as well as send myself a note along with the malformed query to troubleshoot the code. CFTry and CFCatch were the tags I hated the most when I first started using them since they made it harder to debug. But after I made up my own tags to handle the errors, they became my best friends. I will admit, however, that they clutter the code a bit. Good Fortune, Rick Walters Webmaster, Davita Laboratory Services Office: (800) 604-5227 Cell: (407) 491-9848 -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 11:34 AM To: CF-Talk Subject: RE: Inserting and CFTransaction Access doesn't have any good way to prevent the multiple simultaneous insert problem. So, by using CFCatch, and CFTransaction you can catch the error of the second insert, rollback the changes and try again with a new value. CFTransaction seems to suggest that your set of queries will all run sequentially and then allow others to access the instance. But, in truth, I don't think the tables are locked. In Access, whenever a record within a table is modified, the entire table is exclusively locked. So, with CFTRANSACTION around both the query that modifies a record, and the query that retrieves the autonumber value, no one else will be able to access the table and insert another record until the transaction has completed. With other platforms, however, such as SQL Server, the entire table won't be locked. Note that there's no need to use CFTRY/CFCATCH in the above example, unless your intent is to check a unique index or something along those lines. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting and CFTransaction
Thanks for the clarification on Access, I believe you're right since it's not a truly relational product. I don't think that's really the reason, though, but rather that its locking is less granular than SQL Server's or Oracle's. However, I always use CFTry and CFCatch with Inserts and Update queries. The majority of errors occur when modifying the data. There's nothing wrong with that, I don't think. What I should have said, was that you don't need to use it within the CFTRANSACTION. The CFTRANSACTION won't run subsequent queries if the first one fails (assuming they're using the same datasource, of course). There's nothing wrong with placing the entire CFTRANSACTION within a CFTRY block, to catch exceptions returned by the CFTRANSACTION. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists