copying db info with cf
Hi. I work on a site that has development, preview/testing, and production environments. These are fully independent right down to the database environments. It's all Oracle on the back end. I've taken care that the data structure is identical from environment to environment. However, the data itself is a little trickier. Some of it is basic data that I set up when the tables are established. But some stuff gets entered later, and I am trying to figure out a clever way to move data from one of the db environments to another. I could, of course, pull the data, try to have cf spit out sql statements that I then paste into another page, but I am wondering whether there is some smarter way? I am imagining some simple export format that I import on the other boxes. Note: this is probably obvious, but just in case: the CF servers are all independent, so no CF code can access another server's DBs. Thanks, Matthieu Pfizer Information Center ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: copying db info with cf
Why not just set up replication between the databases to keep them in sync? +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst TeleCommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis Let's Roll - Todd Beamer, Flight 93 -Original Message- From: Cornillon, Matthieu [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 10, 2002 10:20 AM To: CF-Talk Subject: copying db info with cf Hi. I work on a site that has development, preview/testing, and production environments. These are fully independent right down to the database environments. It's all Oracle on the back end. I've taken care that the data structure is identical from environment to environment. However, the data itself is a little trickier. Some of it is basic data that I set up when the tables are established. But some stuff gets entered later, and I am trying to figure out a clever way to move data from one of the db environments to another. I could, of course, pull the data, try to have cf spit out sql statements that I then paste into another page, but I am wondering whether there is some smarter way? I am imagining some simple export format that I import on the other boxes. Note: this is probably obvious, but just in case: the CF servers are all independent, so no CF code can access another server's DBs. Thanks, Matthieu Pfizer Information Center ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: copying db info with cf
One way is to use RDBMS facilities to batch transfer data between databases -- SQL-Server has this capability -- I don't know whether Oracle does. Another way I have used is this: System A CF A DB A Caller A System B CF B DB B Stub B Install a CF Caller program on System A Install a CF Stub program on System B Caller A creates a SQL Query (with associated data as necessary) Instead of issuing the query, it serializes it into a WDDX packet. The WDDX packet is passed to Stub B via a cfhttp tag. Stub B ins invoked and receives the WDDX packet. It deserialiazes the WDDX packet and issues the SQL query against DB B Stub B then serializes the query results and returns them to Caller A. So, Caller A is able to execute queries against DB B. It is a simple matter for Caller A to copy data from DB A to DB B or vice versa. I use this all the time -- I have even used it to move complete databases between to remote hosts where I didn't have admin privileges on either host. Dick On Thursday, October 10, 2002, at 10:19 AM, Cornillon, Matthieu wrote: Hi. I work on a site that has development, preview/testing, and production environments. These are fully independent right down to the database environments. It's all Oracle on the back end. I've taken care that the data structure is identical from environment to environment. However, the data itself is a little trickier. Some of it is basic data that I set up when the tables are established. But some stuff gets entered later, and I am trying to figure out a clever way to move data from one of the db environments to another. I could, of course, pull the data, try to have cf spit out sql statements that I then paste into another page, but I am wondering whether there is some smarter way? I am imagining some simple export format that I import on the other boxes. Note: this is probably obvious, but just in case: the CF servers are all independent, so no CF code can access another server's DBs. Thanks, Matthieu Pfizer Information Center ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: copying db info with cf
um...replication? Is this an Oracle thing? I don't want to copy the whole DB, just parts of it. Certain tables, certain records. If this is Oracle, I can do more research on my own. Thanks for the lead, though. Matthieu -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 10, 2002 3:40 PM To: CF-Talk Subject: RE: copying db info with cf Why not just set up replication between the databases to keep them in sync? +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst TeleCommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis Let's Roll - Todd Beamer, Flight 93 -Original Message- From: Cornillon, Matthieu [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 10, 2002 10:20 AM To: CF-Talk Subject: copying db info with cf Hi. I work on a site that has development, preview/testing, and production environments. These are fully independent right down to the database environments. It's all Oracle on the back end. I've taken care that the data structure is identical from environment to environment. However, the data itself is a little trickier. Some of it is basic data that I set up when the tables are established. But some stuff gets entered later, and I am trying to figure out a clever way to move data from one of the db environments to another. I could, of course, pull the data, try to have cf spit out sql statements that I then paste into another page, but I am wondering whether there is some smarter way? I am imagining some simple export format that I import on the other boxes. Note: this is probably obvious, but just in case: the CF servers are all independent, so no CF code can access another server's DBs. Thanks, Matthieu Pfizer Information Center ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: copying db info with cf
Login to the servers as the oracle user and do an 'exp' command (look it up for help, but I think that command alone will prompt you for the user/database you want to export). It'll create a binary file you can then 'imp' on the other servers after ftping them over. Well, this is how you do it on a Unix box anyway. The Windows version is probably similar, but gets at it a little differently. You didn't say what environment you have. Cathy - Original Message - From: Cornillon, Matthieu [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, October 10, 2002 1:19 PM Subject: copying db info with cf Hi. I work on a site that has development, preview/testing, and production environments. These are fully independent right down to the database environments. It's all Oracle on the back end. I've taken care that the data structure is identical from environment to environment. However, the data itself is a little trickier. Some of it is basic data that I set up when the tables are established. But some stuff gets entered later, and I am trying to figure out a clever way to move data from one of the db environments to another. I could, of course, pull the data, try to have cf spit out sql statements that I then paste into another page, but I am wondering whether there is some smarter way? I am imagining some simple export format that I import on the other boxes. Note: this is probably obvious, but just in case: the CF servers are all independent, so no CF code can access another server's DBs. Thanks, Matthieu Pfizer Information Center ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: copying db info with cf
Hi. I work on a site that has development, preview/testing, and production environments. These are fully independent right down to the database environments. It's all Oracle on the back end. I've taken care that the data structure is identical from environment to environment. However, the data itself is a little trickier. Some of it is basic data that I set up when the tables are established. But some stuff gets entered later, and I am trying to figure out a clever way to move data from one of the db environments to another. I could, of course, pull the data, try to have cf spit out sql statements that I then paste into another page, but I am wondering whether there is some smarter way? I am imagining some simple export format that I import on the other boxes. Note: this is probably obvious, but just in case: the CF servers are all independent, so no CF code can access another server's DBs. mmm... Well, if you want a CF solution, you could add a dsn to the server where you want to import the data to and point it at the db server you want to import from, then a script could generate all your data into cfquery inserts using cfqueryparam to ensure consistency, etc... This is liable to be slow and tedious. I did this recently to create an installer for my Tapestry cms application, however, this is assuming an environment where no source database exists and we needed to simply create all of our tables and initial data set for a new database from a series of coldfusion modules. In an environment where you're talking about moving data from oen existing db to another existing db I suspect that your best solution is going to bypass CF completely. In MS SQL Server I would suggest linked servers or DTS -- I'm not familiar enough with Oracle yet to make an intelligent suggestion as to the tools in that environment. S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: copying db info with cf
Just do an exp on the production db and do an imp on the dev db... do it as often as you need to keep relevant data available - once a week is prob plenty -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 10, 2002 12:40 PM To: CF-Talk Subject: RE: copying db info with cf Why not just set up replication between the databases to keep them in sync? +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst TeleCommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis Let's Roll - Todd Beamer, Flight 93 -Original Message- From: Cornillon, Matthieu [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 10, 2002 10:20 AM To: CF-Talk Subject: copying db info with cf Hi. I work on a site that has development, preview/testing, and production environments. These are fully independent right down to the database environments. It's all Oracle on the back end. I've taken care that the data structure is identical from environment to environment. However, the data itself is a little trickier. Some of it is basic data that I set up when the tables are established. But some stuff gets entered later, and I am trying to figure out a clever way to move data from one of the db environments to another. I could, of course, pull the data, try to have cf spit out sql statements that I then paste into another page, but I am wondering whether there is some smarter way? I am imagining some simple export format that I import on the other boxes. Note: this is probably obvious, but just in case: the CF servers are all independent, so no CF code can access another server's DBs. Thanks, Matthieu Pfizer Information Center ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: copying db info with cf
With Oracle, you can log in with sqlplus and do something like this: (Obviously, you'd have to fill in the schema names, passwords, instance names, table names, etc.) //disable all constraints MODIFY CONSTRAINT myconstraintname DISABLE; //disable any triggers that might fire ALTER TRIGGER mytrigger DISABLE; //delete old data from table you want to refill DELETE FROM myschema.mytable; //copy from the other instance and insert into this instance COPY FROM oldschemaname/password@instancename- TO newschemaname/password@instancename - INSERT tabletoinsertinto- USING SELECT * from schemaname.oldtablename; //reenable any triggers ALTER TRIGGER mytrigger ENABLE; //reenable any constraints ALTER TABLE mytable MODIFY CONSTRAINT myconstraint ENABLE; Deanna Schneider Interactive Media Developer [EMAIL PROTECTED] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: copying db info with cf
Why not just do a dump of the data from the development environment and import it into the other DBs? Granted, this is often considered a Bad Thing in place of importing a fresh schema complete with lookup data, but it sounds like it's what you're after. -- Mosh Teitelbaum evoch, LLC Tel: (301) 625-9191 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: Cornillon, Matthieu [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 10, 2002 1:20 PM To: CF-Talk Subject: copying db info with cf Hi. I work on a site that has development, preview/testing, and production environments. These are fully independent right down to the database environments. It's all Oracle on the back end. I've taken care that the data structure is identical from environment to environment. However, the data itself is a little trickier. Some of it is basic data that I set up when the tables are established. But some stuff gets entered later, and I am trying to figure out a clever way to move data from one of the db environments to another. I could, of course, pull the data, try to have cf spit out sql statements that I then paste into another page, but I am wondering whether there is some smarter way? I am imagining some simple export format that I import on the other boxes. Note: this is probably obvious, but just in case: the CF servers are all independent, so no CF code can access another server's DBs. Thanks, Matthieu Pfizer Information Center ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: copying db info with cf
Thanks, everyone, for your feedback on this. Looks like some sort of Oracle-based operation is the way to go. I more or less just wanted to make sure that there was no easy and obvious CF solution that I was missing. Thanks again for your posts. Matthieu -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 10, 2002 4:43 PM To: CF-Talk Subject: Re: copying db info with cf With Oracle, you can log in with sqlplus and do something like this: (Obviously, you'd have to fill in the schema names, passwords, instance names, table names, etc.) //disable all constraints MODIFY CONSTRAINT myconstraintname DISABLE; //disable any triggers that might fire ALTER TRIGGER mytrigger DISABLE; //delete old data from table you want to refill DELETE FROM myschema.mytable; //copy from the other instance and insert into this instance COPY FROM oldschemaname/password@instancename- TO newschemaname/password@instancename - INSERT tabletoinsertinto- USING SELECT * from schemaname.oldtablename; //reenable any triggers ALTER TRIGGER mytrigger ENABLE; //reenable any constraints ALTER TABLE mytable MODIFY CONSTRAINT myconstraint ENABLE; Deanna Schneider Interactive Media Developer [EMAIL PROTECTED] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
