I have already taken care of the creation of tables etc., the problem is
that some providers offer SQL but do not allow admin rights from a
remote location.
So my only way of thinking was to write a script that did all the
intialisation of everything needed.....
No the idea is that, I don't trust a lot of the hosting sites. I am
setting up a solution that would not only create the tables, sp etc. But
in case something goes wrong, then the script can recreate the necessary
steps. Very good if I don't have client access from a remote location.
> -----Original Message-----
> From: Andy Ewings [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 17 July 2001 2:06 AM
> To: CF-Talk
> Subject: RE: Stored Procedures
>
> I'd say you are being ultra causious if your app is managing the SP's
mthat
> it is using. I have never come across a system that will create an SP
it
> needs if it doesn't exist. Is it likely to get deleted?
>
> If you really want to pursue down this path then I suggest you take a
step
> away from CF to start with and write an SP that will create SP's on
the fly.
> Therefore it will need to accept a load of parameters in order to make
it
> generic including server,database,owner,spname,parameter list,
procedurecode
> (bulk of procedure) at a minimum.
>
> You could even look at it a step further and say it's all very well
> recreating SP's if someones deleted them but what if they've deleted
tables
> that the SP uses as well? A far better solution is to place tight
> permissions on the entities within your db so that people can't
accidiently
> delete or change them unless granted permission to do so.
>
> Don't get me wrong, I think that a utility enabling people to create
there
> own SP's could be useful in some circumstances, I just don't think it
should
> be there to create SP's if they don't exisit. For the code that you
are
> talking about it's almost worth not using SP's at all if you think
they are
> going to get deleted on a regular basis (you were only doing 1
insert). If
> you are having to recreate the SP and then run it you are pretty much
losing
> the benefits of having an SP in the first place if you are regularly
> recreating it because it has been deleted.
>
> -----Original Message-----
> From: Andrew Scott [mailto:[EMAIL PROTECTED]]
> Sent: 16 July 2001 16:51
> To: CF-Talk
> Subject: RE: Stored Procedures
>
>
> The idea that I am using is that the application becomes self
> sufficient. With this I am looking at creating the sp on the fly in
case
> they are removed or deleted by accident.
>
> The idea is that a cfml script is used to go through and check the sp,
> if it doesn't exist to then create it... Your right I might be a
little
> bit ambitious with this, but I was hoping to cut the work out of
setting
> up everything manually...
>
> Do you now of any good references on this subject!
>
>
>
> > -----Original Message-----
> > From: Andy Ewings [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, 16 July 2001 11:50 PM
> > To: CF-Talk
> > Subject: RE: Stored Procedures
> >
> > I'm not sure why you are trying to do what you want to do. By the
> looks of
> > it what you actually want to do is to creat an SP to insert a new
> users
> > details into the db. If so you don't do this using CF. You run the
> SQL you
> > have in SQL Query Analyser against the DB you want it to affect and
> then you
> > call the SP in your CF by either using CFSTOREDPROC (which you
should)
> or by
> > using CFQUERY and running the SQL "EXEC sp_AddUser param1 param2
> param3
> > etc......." (not recomended). In doing this you have tightened up
> security
> > and the SQL is cached so an execution plan exists and hence the SQL
> doesn't
> > need to be parsed.
> >
> > If however you are trying to write a generic web based utility
whereby
> users
> > can create there own sp's then you are on the write traks although
you
> > should have an SP (which you write) which accepts parameter in order
> to
> > create other sp's within your db although this is not simple and
> certainly
> > challenging if you are only starting out with SP's!
> >
> > -----Original Message-----
> > From: Andrew Scott [mailto:[EMAIL PROTECTED]]
> > Sent: 16 July 2001 14:26
> > To: CF-Talk
> > Subject: RE: Stored Procedures
> >
> >
> > Which is what I am looking for, if it doesn't exist to create it...
I
> am
> > new to storedprocs. Although I know the syntax I haven't used them
> much
> > with CF yet...
> >
> >
> > > -----Original Message-----
> > > From: Andy Ewings [mailto:[EMAIL PROTECTED]]
> > > Sent: Monday, 16 July 2001 11:14 PM
> > > To: CF-Talk
> > > Subject: RE: Stored Procedures
> > >
> > > Never tried it, you may have to put semi colons at the end of
every
> > sql
> > > statement if ODBC terms them as separate SQL stements. I would
> > suggest that
> > > you write and call an SP that creates SP's for you.
> > >
> > > -----Original Message-----
> > > From: Andrew Scott [mailto:[EMAIL PROTECTED]]
> > > Sent: 16 July 2001 14:09
> > > To: CF-Talk
> > > Subject: RE: Stored Procedures
> > >
> > >
> > > So you are saying I could do this then....
> > >
> > > <cfquery name="sp_Adduser" datasource="datasource">
> > > IF not EXISTS (SELECT * FROM sysobjects WHERE name =
> > > "sp_Adduser1" AND type = "P")
> > > CREATE PROCEDURE sp_AddUser
> > > @username varchar(50),
> > > @password varchar(12),
> > > @Firstname varchar(50),
> > > @Familyname varchar(50),
> > > @ID varchar(50),
> > > @ID_Member varchar(50) output
> > > AS
> > > set nocount on
> > > if not exists (select ID_Member from Members where Username =
> > > @username)
> > > begin
> > > insert into
> > > Members(ID_Member,Username,Password,Firstname,Familyname)
> > > values (@ID,@username,@password,@Firstname,@Familyname)
> > > select @ID_Member = @ID
> > > end
> > > else
> > > select @ID_Member = -1
> > > return
> > > END
> > > </cfquery>
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
