Thanks. Helpful answer.
I expected that any storedprocs would need to be rewritten should the DB
change (as well as the hassle of migrating the data). But what I'm thinking
is that in that manner, you're only dealing with database changes, not
database AND code changes. The goal is that if one layer changes (the
database, say), that's all that changes -- the code layer, presentation
layer and content layer remain unfazed. And visa versa ... if the code
layer changes, the db layer remains unfazed, etc.
H.
> -----Original Message-----
> From: Fregas [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, January 03, 2003 11:02 AM
> To: CF-Talk
> Subject: Re: CFGurus: StoredProcs
>
> Howard,
>
> While stored procs are better for performance, reuseability (although you
> can get the same or more reusability from putting your queries in tags,
> includes or cfc's if you are just using CFML) they do tend to make you
> more
> tied to one particular database vendor. Simple queries put into stored
> procs can migrate easily, but more complex ones using microsoft's TSQL
> extensions will not migrate without some rewriting.
>
> the two queries you mentioned are fairly simple and would migrate easily,
> but other using table datatypes, sql server variables or cursors probably
> would not as these things are implemented differently on different DBMSes.
>
> Here's how you'd implement your two queries.
>
> CREAT PROC getmydatebydate @dateinquestion smalldatetime
> AS
>
> SELECT col1, col2
> FROM myTable
> WHERE date = @dateinquestion
>
>
> CREAT PROC insertintomytable @col1 varchar(50), @col2 varchar(33)
> AS
> INSERT INTO myTable (col1, col2)
> VALUES (@col1, @col2)
>
>
>
> ----- Original Message -----
> From: "Owens, Howard" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Friday, January 03, 2003 12:28 PM
> Subject: CFGurus: StoredProcs
>
>
> > I think I understand StoredProcs from a CF view point ... I've read the
> docs
> > on the tags and understand how they work.
> >
> > And I understand that storedprocs are good for performance, good for
> code
> > reuse, are good complex db routines, and good for security ... but I
> have
> an
> > encapsulation/orthongonal-related question.
> >
> > If I used storedprocs for ALL of my queries ... will I achieve a greater
> > degree of DB independence? In other words, currently, all of my apps
> run
> on
> > SQLServer, but what if some day I want to (or am forced to) move my apps
> to
> > Oracle or MySQL or ??? ... will using StoredProcs allow me to make the
> move
> > without re-writing my CFCode? It looks like it will, but I just wanted
> to
> > make sure. (and transversely, it looks like using storedprocs will
> allow
> me
> > -- if I ever need to -- switch from say CF to JSP or PHP or ASP without
> > changing my DB layer).
> >
> > My development process has been getting progressively more orthongonal,
> > without even knowing about the concept until recently, and going to
> > storedprocs looks like the next logical step. But it will also make
> > development a little more complex, and I don't want to waste a lot of
> time
> > on the process if it isn't going to give me what I expect ... which is
> why
> > the caution of this question.
> >
> > Also, from a technical standpoint, I'm still not sure how to write the
> > storedproc. Let's say I have this query:
> >
> > SELECT col1, col2
> > FROM myTable
> > WHERE date = '#createodbcdate(dateadd("d", "-7", dateformat(now(),
> > "mm/dd/yyyy"))#'
> >
> > What would that query look like in MSSQL?
> >
> > Or
> >
> > INSERT INTO myTable (col1, col2)
> > VALUES ('#form.col1#', '#form.col#')
> >
> > If I'm now passing those form values through a storedproc, what would
> the
> > MSSQL look like?
> >
> > H.
> >
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > Howard Owens
> > Internet Operations Coordinator
> > InsideVC.com/Ventura County Star
> > [EMAIL PROTECTED]
> > AIM: GoCatGo1956
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
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
