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