[thelist] CF and foreign databases: Possible?

frank,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: Frank <framar@interlog.com>
:
: The second part is tougher, the owner is putting together agreements
: with other website owners who have their own CF databases. I need to
: be able to access them directly from within the primary site. I can't
: use frames, I need to make sure that all the DB's have my client's
: branding. This means that I need to be able to pull records from
: their site, sort, search, apply style sheets, etc... There will be a
: link to each page on each record, but the rest will all *appear* to
: originate from the original site.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

michael has suggested using <CFHTTP> to do a post or a get on their form
fields or grab a full URL.

    advantages
1)  you have total control over how the results will look
2)  the visitor will be staying at your site
3)  you maintain session and client variables

    disadvantages
1)  you don't have control over the actual query to the database
2)  increased maintenance should the partner site alter their design,
queries, search forms, or URL query strings.
3)  you're consuming their bandwidth doing queries AND your bandwidth
delivering the content
4)  you're limited to searching their database using whatever format they
have
parsing #cfhttp.filecontent# to get the relevant data REALLY SUCKS.


rudy has suggested using branded query strings where the partner sites would
build the page so it'd
have your look.

    advantages
1)  you're not consuming any of your own bandwidth to search a partner's
site
2)  you could end up with lots less work to get the data out of your
partner's site
3)  you don't have to try to parse #cfhttp.filecontent#

    disadvantages
1)  you don't have control over the actual query to the database
2)  the partner site's owner(s) may not want to put the money into adding
the "branding" capability
3)  you could end up with lots more work to get the data out of your
partner's site
4)  if it's not done properly, you could end up losing some of the usability
of your own site
5)  you lose session and client variables when you switch to the partner's
server
6)  your URL isn't in the address bar causing potential frustration for
bookmarking
7)  you don't have control over errortrapping for bookmarked "dead" queries
8)  increased maintenance if you decide to make a design change and wanted
it reflected on all the partner sites


i'm going to suggest that you look into the possibility of doing a
once-every-24-hours synchronization of data between each of these partner
sites.  get each partner site to export nightly the relevant product data
out of their database into a special database that they can make available
via http or ftp.  they can do this using the <CFSCHEDULE> tag or a cron job
and some sophisticated select queries and create table queries.  you would
then do the same thing to grab the file using <CFHTTP> or <CFFTP> to save
the file to your own server in the same spot you have the dsn'ed files
already or save the file to a temporary directory where it would await an
update via SQL queries.  the transfer to the same directory would simply
overwrite the existing database.  or the transfer to a temp directory would
mean you would run some UPDATE and INSERT/DELETE queries on the linked
database and the new database from that partner.  either way it would
reflect the new information in queries on the site immediately upon
completion of the scheduled task.  if you were selling your partner site's
product from your site, you'd want to get them to set up a simple page where
you'd query the availability of their product using <CFHTTP> and a URL query
string where their page would return a simple "yes" or "no" on availability.
if you were writing to these partner provided databases so as to keep your
local reflection of inventory available, then you'd want to make these
databases available to the partner sites so they could grab them and run
queries on them to adjust their inventory accordingly.  you may also need to
setup the aforementioned availability checking template for these partner
sites so you could reflect your status of their inventory if these were
limited quantity items.

    advantages
1)  you can run whatever queries you like.
2)  you can combine queries on a single page for side by side
product/feature/price comparisons.
3)  you display the data in whatever fashion you like
4)  maintenance on the site and templates is completely under your control
5)  integrating your partner's product into your site could be done with
minimal effort on their part and without any changes whatsoever to their
site to accomodate you.
6)  you could easily add more partner sites that you list product for
without much work on your part.
7)  your partner sites could build the nightly databases using only a
certain portion of their product, ommitting non-available or back-ordered
items.

    disadvantages
1)  your host may not allow the use of <CFSCHEDULE>, <CFHTTP>, <CFFTP>,
<CFFILE>, or <CFDIRECTORY>.
2)  on the bright side availibility could be near instantaneous.  on the
dark side, your partners wouldn't be willing to offer availibility checks
meaning your availability status of their product could be as much as 24
hours old.
3)  there could be a single item available that was ordered and availability
checked on both your site and your partner's site at the the exact same
time.  you could have a sticky mess figuring out which customer ultimately
gets the product.
4)  you might run into difficulty overwriting a database that's in use.
there are utilities to cut the connection to the database while you
overwrite, but sometimes it's not easy.  if you don't make the database
unavailable from the net while you overwrite you could end up losing a
customer order or foul up their search.  this could all be avoided however
if you've got a "down-time" check on the files that call these datasources.
5) the product database you grab from each partner could potentially be very
large making the transfer slow and painful.  this would mean your
"down-time" would have to last longer to accomodate.  if this is the case,
you could transfer the database to a different directory then when the
transfer is complete you could move it from one directory to the live
directory overwriting the old database.  or, you could run a mass UPDATE or
INSERT/DELETE query to update the data.  this could result in very little
"down-time" and be quite quick, but the SQL queries could be ultra fun to
figure out.
6)  none of this is easy for a newbie or someone who doesn't have a solid
understanding of the overall picture and the concepts associated.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Is this a pipe dream? Is this possible? Is this really hard for a
: (relatively smart) CF newbie?
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

it is possible.  it could prove to be very difficult very quickly if you
don't make this as simple as possible.

just holler if you've got questions, which, no doubt, you'll have.  *grin*

good luck,

: jeff.howden
: web.development.professional
: evolt.org.member
:
: the.best.looking.developers.on.the.net
:
: http://evolt.org/
: jeff@members.evolt.org