----- Original Message ----- From: "Bert Dawson" <bdawson@redbanner.com> To: "Fusebox" <Fusebox@houseoffusion.com> 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:Adam.Reynolds@unilever.com] > > 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:TimPrice@btinternet.com] > > 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:Adam.Reynolds@unilever.com] > > 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 > > * adam.reynolds@unilever.com > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/fusebox@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists