Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
ColdFusion 9 or 9.01, I think added this, not 100% certain on it though. On Wed, Sep 7, 2011 at 9:23 PM, Mike Kear afpwebwo...@gmail.com wrote: The scenario: I have a local application, which updates a local database as a business process goes on.But I want the application also to update a remote database, with the details of this event, then set a flag in the local record that the record has been exported to the remote database. However I'm building error handling to provide for the possibility that the remote database might not be available. I dont want that to stop the local application, or it will close the business down. I want the business to go on, the customer satisfied and the web site can be updated later on when the internet connection is back up again Does CFTRANSACTION handle the commit/rollback of both databases if any part of the queries fails?I would code it kind of like this: cftransaction cfquery name=qRemoteInsert datasource=remoteDSN INSERT into eventrecord yada yada yada /cfquery cfquery name=qlocalUpdate datasource=localDSN Update eventrecord SET exportedToRemoteDB = true WHERE eventID = cfqueryparam value=#thiseventID# cfsqltype=cf_sql_integer/ /cfquery /cftransaction -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
Is a big ask - what about a temporary table or backup table you can flag for records that made it through - you can compare commits to? I have a similar issue but for sending emails AND the database issue. Peter Tilbrook Web Administrator, The Club Group (ACT) Pty. Ltd. Managing Director, ColdGen Internet Solutions Professional Adobe ColdFusion 9 Application Development President, ACT and Region ColdFusion Users Group PO Box 2247 Queanbeyan, NSW, 2620 AUSTRALIA Tel: +61-2-6284-2727 Mob: +61-2-0457-449-016 Email Address: pe...@coldgen.com WWW: http://www.coldgen.com/ WWW2: http://www.clubgroup.com.au/ ABN: 80 826 226 128 On 7 September 2011 21:36, Andrew Scott andr...@andyscott.id.au wrote: ColdFusion 9 or 9.01, I think added this, not 100% certain on it though. On Wed, Sep 7, 2011 at 9:23 PM, Mike Kear afpwebwo...@gmail.com wrote: The scenario: I have a local application, which updates a local database as a business process goes on. But I want the application also to update a remote database, with the details of this event, then set a flag in the local record that the record has been exported to the remote database. However I'm building error handling to provide for the possibility that the remote database might not be available. I dont want that to stop the local application, or it will close the business down. I want the business to go on, the customer satisfied and the web site can be updated later on when the internet connection is back up again Does CFTRANSACTION handle the commit/rollback of both databases if any part of the queries fails? I would code it kind of like this: cftransaction cfquery name=qRemoteInsert datasource=remoteDSN INSERT into eventrecord yada yada yada /cfquery cfquery name=qlocalUpdate datasource=localDSN Update eventrecord SET exportedToRemoteDB = true WHERE eventID = cfqueryparam value=#thiseventID# cfsqltype=cf_sql_integer/ /cfquery /cftransaction -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
I don't think ColdFusion cares about temp tables, as long as they exist at that time of the query. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 On Wed, Sep 7, 2011 at 9:51 PM, ColdGen Internet Solutions coldgen.internet.soluti...@gmail.com wrote: Is a big ask - what about a temporary table or backup table you can flag for records that made it through - you can compare commits to? I have a similar issue but for sending emails AND the database issue. Peter Tilbrook Web Administrator, The Club Group (ACT) Pty. Ltd. Managing Director, ColdGen Internet Solutions Professional Adobe ColdFusion 9 Application Development President, ACT and Region ColdFusion Users Group PO Box 2247 Queanbeyan, NSW, 2620 AUSTRALIA Tel: +61-2-6284-2727 Mob: +61-2-0457-449-016 Email Address: pe...@coldgen.com WWW: http://www.coldgen.com/ WWW2: http://www.clubgroup.com.au/ ABN: 80 826 226 128 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
No idont think a temp table will solve the problem, which is bascially how can i be sure that the remote database has updated, and if not, be sure of finding the correct records later when i try it again - without doubling up on records in teh remote database, or alternatively missing any? One way i could do it is to perform the remote insert, then immediately do a query on the remote database for that record, but i was hoping to do it without requiring another trip out to the remote database. Hence the question about CFTRANSACTION. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Wed, Sep 7, 2011 at 9:57 PM, Andrew Scott andr...@andyscott.id.au wrote: I don't think ColdFusion cares about temp tables, as long as they exist at that time of the query. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
Hi Mike, I won't comment about CF or any other database server, but this is possible with SQL server. you need to create a link to your distant server first. Then in your example, instead of using a cftransaction and 2 cfquery with different dsn, you'd have something along the line of cfquery dsn=local being tran t1 begin try INSERT into linkedserver.distantdatabase.dbo.table values(...) Update eventrecord SET exportedToRemoteDB = true COMMIT begin catch Update eventrecord SET exportedToRemoteDB = true end catch end try cfquery I'm sure ant modern dbms would have a similar mechanism, but I'm very much a sql server fan boy, so it's the only thing I know ;-) as for actual cross-server transactions, you can configure something called DTC (Distributed Transaction Coordinator). It's pretty much an engine that allows a tran between 2 or more linked servers to be shared, and that manages commits and rollbacks as a whole. hope this helps. Tof On Wed, Sep 7, 2011 at 9:57 PM, Andrew Scott andr...@andyscott.id.auwrote: I don't think ColdFusion cares about temp tables, as long as they exist at that time of the query. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 On Wed, Sep 7, 2011 at 9:51 PM, ColdGen Internet Solutions coldgen.internet.soluti...@gmail.com wrote: Is a big ask - what about a temporary table or backup table you can flag for records that made it through - you can compare commits to? I have a similar issue but for sending emails AND the database issue. Peter Tilbrook Web Administrator, The Club Group (ACT) Pty. Ltd. Managing Director, ColdGen Internet Solutions Professional Adobe ColdFusion 9 Application Development President, ACT and Region ColdFusion Users Group PO Box 2247 Queanbeyan, NSW, 2620 AUSTRALIA Tel: +61-2-6284-2727 Mob: +61-2-0457-449-016 Email Address: pe...@coldgen.com WWW: http://www.coldgen.com/ WWW2: http://www.clubgroup.com.au/ ABN: 80 826 226 128 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
Mike, You don't say which version of ColdFusion your using either. btw http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c6b.html On Wed, Sep 7, 2011 at 10:22 PM, Chris Velevitch chris.velevi...@gmail.comwrote: I think the solution is a message queue. It's a system that guarantees the message (record) is delivered to the other system (database) and it can give that guarantee because it stores all the messages in it's own database. So when the remote system is unreachable the message queue stores all the messages in order and when the remote system comes back up, the stored messages are resent in order they where received. There are a number of free open source message queues, so only issue is how much time, effort and resources (a second server) do you what to spend? Chris -- Chris Velevitch Manager - Adobe Platform Users Group, Sydney m: 0415 469 095 www.apugs.org.au Adobe Platform Users Group, Sydney Topic: Deploying Coldfusion into the Cloud Date: 26th September 6pm for 6:30 start Details and RSVP on http://apugs.groups.adobe.com/index.cfm?event=post.displaypostid=38239 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
sorry, I meant to update that second update statement to false On Wed, Sep 7, 2011 at 10:23 PM, christophe albrech christophe.albr...@gmail.com wrote: Hi Mike, I won't comment about CF or any other database server, but this is possible with SQL server. you need to create a link to your distant server first. Then in your example, instead of using a cftransaction and 2 cfquery with different dsn, you'd have something along the line of cfquery dsn=local being tran t1 begin try INSERT into linkedserver.distantdatabase.dbo.table values(...) Update eventrecord SET exportedToRemoteDB = true COMMIT begin catch Update eventrecord SET exportedToRemoteDB = true end catch end try cfquery I'm sure ant modern dbms would have a similar mechanism, but I'm very much a sql server fan boy, so it's the only thing I know ;-) as for actual cross-server transactions, you can configure something called DTC (Distributed Transaction Coordinator). It's pretty much an engine that allows a tran between 2 or more linked servers to be shared, and that manages commits and rollbacks as a whole. hope this helps. Tof On Wed, Sep 7, 2011 at 9:57 PM, Andrew Scott andr...@andyscott.id.auwrote: I don't think ColdFusion cares about temp tables, as long as they exist at that time of the query. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 On Wed, Sep 7, 2011 at 9:51 PM, ColdGen Internet Solutions coldgen.internet.soluti...@gmail.com wrote: Is a big ask - what about a temporary table or backup table you can flag for records that made it through - you can compare commits to? I have a similar issue but for sending emails AND the database issue. Peter Tilbrook Web Administrator, The Club Group (ACT) Pty. Ltd. Managing Director, ColdGen Internet Solutions Professional Adobe ColdFusion 9 Application Development President, ACT and Region ColdFusion Users Group PO Box 2247 Queanbeyan, NSW, 2620 AUSTRALIA Tel: +61-2-6284-2727 Mob: +61-2-0457-449-016 Email Address: pe...@coldgen.com WWW: http://www.coldgen.com/ WWW2: http://www.clubgroup.com.au/ ABN: 80 826 226 128 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
Sorry Andrew, you're quite right - i forgot to say it's CF9.0.1. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Wed, Sep 7, 2011 at 10:24 PM, Andrew Scott andr...@andyscott.id.au wrote: Mike, You don't say which version of ColdFusion your using either. btw http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c6b.html -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Does CFTRANSACTION work across multiple datasources?
That’s ok Mike, I would assume that unless otherwise stated. So the answer is yes to your question. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 -Original Message- From: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On Behalf Of Mike Kear Sent: Wednesday, 7 September 2011 10:31 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Does CFTRANSACTION work across multiple datasources? Sorry Andrew, you're quite right - i forgot to say it's CF9.0.1. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
Ok i have the answer - NO.For those who are just tuning in, I asked whether i could wrap two queries on different datasources with CFTRANSACTION tags, and in CF9.0.1 I get an error message saying that the datasource for all queries inside CFTRANSACTION tags must be the same. So i think i am going to have to do the insert on the remote datasource, then do a query on the same datasource to verify it has happened correctly, unless someone has a better idea.The most likely problem I foresee is that the internet might be down for one reason or another during a transaction. In that case, I want the local processing to continue without a pause, and the remote database updated later on when the internet is up again, and be able to ensure [a] no duplicate records are inserted on the remote database, and [b] no records are missed either. Anyone have a better idea? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Wed, Sep 7, 2011 at 10:28 PM, Andrew Scott andr...@andyscott.id.au wrote: That’s ok Mike, I would assume that unless otherwise stated. So the answer is yes to your question. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
Mike, I reread the documentation it looks like the across multiple databases is in fact an enterprise only feature, surprise surprise... -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 On Thu, Sep 8, 2011 at 12:05 AM, Mike Kear afpwebwo...@gmail.com wrote: Ok i have the answer - NO.For those who are just tuning in, I asked whether i could wrap two queries on different datasources with CFTRANSACTION tags, and in CF9.0.1 I get an error message saying that the datasource for all queries inside CFTRANSACTION tags must be the same. So i think i am going to have to do the insert on the remote datasource, then do a query on the same datasource to verify it has happened correctly, unless someone has a better idea.The most likely problem I foresee is that the internet might be down for one reason or another during a transaction. In that case, I want the local processing to continue without a pause, and the remote database updated later on when the internet is up again, and be able to ensure [a] no duplicate records are inserted on the remote database, and [b] no records are missed either. Anyone have a better idea? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Wed, Sep 7, 2011 at 10:28 PM, Andrew Scott andr...@andyscott.id.au wrote: That’s ok Mike, I would assume that unless otherwise stated. So the answer is yes to your question. -- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/108193156965451149543 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Does CFTRANSACTION work across multiple datasources?
+1 - A message queue is the best solution for this type of problem. Cheers Kai I think the solution is a message queue. It's a system that guarantees the message (record) is delivered to the other system (database) and it can give that guarantee because it stores all the messages in it's own database. So when the remote system is unreachable the message queue stores all the messages in order and when the remote system comes back up, the stored messages are resent in order they where received. There are a number of free open source message queues, so only issue is how much time, effort and resources (a second server) do you what to spend? Chris -- -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
