I used a free tag to generate two different types of stored procedures one
using the execute and one using cfstoredproc tags and upload the sp into the
database at the same time.
----- Original Message -----
From: "Bert Dawson" <[EMAIL PROTECTED]>
To: "Fusebox" <[EMAIL PROTECTED]>
Sent: Tuesday, May 08, 2001 4:49 AM
Subject: RE: SQL Help
> I'm interested...
>
> I've hacked together something similar for sql server - its pretty crude,
> but saves typing.
> Here's something that generates the update statement:
>
> #### SQL: ####
>
> select -1
> ,'UPDATE '+so.name+'
> SET '
> FROM syscolumns sc,
> sysobjects so
> WHERE sc.id = so.id
> AND so.name = 'users'
> UNION
> select sc.colorder
> ,' '+CAST(sc.name AS varchar)+
> Left(' ',30-Len(CAST(sc.name AS
> varchar))) +
> '= ''#attributes.'+CAST(sc.name AS varchar)+'#'''
> FROM syscolumns sc,
> sysobjects so
> WHERE sc.id = so.id
> AND sc.autoval IS NULL
> AND so.name = 'users'
> UNION
> select 999
> ,'WHERE '+CAST(sc.name AS varchar)+
> Left(' ',30-Len(CAST(sc.name AS
> varchar))) +
> '= ''#attributes.'+CAST(sc.name AS varchar)+'#'''
> FROM syscolumns sc,
> sysobjects so
> WHERE sc.id = so.id
> AND sc.autoval IS NOT NULL
> AND so.name = 'users'
> order by 1
>
>
> ### RESULTS: ###
>
> -1 UPDATE users
> SET
> 2 use_username =
> '#attributes.use_username#'
> 3 use_pwd =
> '#attributes.use_pwd#'
> 4 use_email =
> '#attributes.use_email#'
> 5 use_question =
> '#attributes.use_question#'
> 6 use_answer =
> '#attributes.use_answer#'
> 999 WHERE use_id =
> '#attributes.use_id#'
>
> Bert
>
> > -----Original Message-----
> > From: Adam Reynolds [mailto:[EMAIL PROTECTED]]
> > Sent: 08 May 2001 11:33
> > To: Fusebox
> > Subject: RE: SQL Help
> >
> >
> > Yup,
> > As long as the query return the same named columns from
> > whichever system
> > tables, then there is no problem.
> >
> > I've got it working under Oracle. I'm not sure how useful
> > this would be as
> > I rarely just run basic SQL, but as a code base to start
> > from, it could
> > save you a few days.
> >
> > If anybody is interested, I can email the final version to people.
> >
> > -----Original Message-----
> > From: Tim Price [SMTP:[EMAIL PROTECTED]]
> > Sent: 08 May 2001 10:58
> > To: Fusebox
> > Subject: RE: SQL Help
> >
> > Adam
> >
> > We are doing something similar to this at the moment but
> > using SQL Server.
> > We use the "systables" to define the names of the tables and
> > columns in our
> > database so I guess if your looking for a generic solution
> > you would have
> > to
> > have a master table which defines the name of the "system
> > tables" and their
> > "columns" on which the whole operation is based. This way the
> > top level SQL
> > can also be dynamic.
> >
> > Tim
> >
> >
> > -----Original Message-----
> > From: Adam Reynolds [mailto:[EMAIL PROTECTED]]
> > Sent: 08 May 2001 09:42
> > To: Fusebox
> > Subject: SQL Help
> >
> >
> > I've written a tool that you point at a database resource and it
> > automatically generates all of the qry_insert qry_update qry_select
> > qry_delete files for each table. I'm also looking to expand
> > it to generate
> > a simple two column table form for each table.
> >
> > In Oracle I am using the USER_TABLES and the ALL_TAB_COLUMNS
> > tables to get
> > the information. As we only work with Oracle this is not a
> > problem BUT I
> > would like to expand it so that it can look at any datasource
> > and retrieve
> > the table data.
> >
> > So anybody able to help?
> >
> > Best Regards,
> >
> > Adam Reynolds
> > ColdFusion Web Developer
> > ISMG Development, Unilever
> > London
> >
> > ( +44 20 7822 5450 (ext 5450)
> > m: +44 7973 386620
> > * [EMAIL PROTECTED]
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
