Sorry, I've been out for a while. The numbers are the values that SQL7 attributes to each data type. The second list are CF types. There may well be a better way, but this was the fastest.
Did you actually try out the code? I'd be interested to know if anyone else got it working. Paolo -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 04 December 2002 11:35 To: [EMAIL PROTECTED] Subject: Re: [ cf-dev ] Automated Stored Procedure Writing - The Solution in CF why these numbers for your array? <cfset xtypes[56]='INT'> <cfset xtypes[60]='CURRENCY'> <cfset xtypes[61]='DATETIME'> <cfset xtypes[104]='BIT'> <cfset xtypes[167]='VARCHAR(length)'> <cfset xtypes[175]='CHAR(length)'> I'm sure there's an obvious reason that I don't know about. Duncan Cumming IT Manager http://www.alienationdesign.co.uk mailto:[EMAIL PROTECTED] Tel: 0141 575 9700 Fax: 0141 575 9600 Creative solutions in a technical world ---------------------------------------------------------------------- Get your domain names online from: http://www.alienationdomains.co.uk Reseller options available! ---------------------------------------------------------------------- ---------------------------------------------------------------------- Paolo Piponi <[EMAIL PROTECTED] To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> o.uk> cc: Subject: [ cf-dev ] Automated Stored Procedure Writing - The Solution 12/04/02 in CF 11:34 AM Please respond to dev Since nobody had a solution to my previous query I've gone and done it myself and thought I'd share it with you. It's crude but it'll automate the writing of basic SQL INSERTS and UPDATES and the related CF code. I haven't attempted to write good code, but simply wrote it as fast as possible. If anyone thinks it's any use I'll clean up the code. Naturally the correct database must be inserted into the code and I have only included a limited set of data types. Just save as a page 'SP.cfm', insert the DSN on the first line and run. Paolo PS It'll only work in SQL Server. Not sure what you need for other DBs. =========================================================================== <!--- SET GLOBAL VARIABLES ---> <cfset databasename=""> <cfif databasename eq "">Please define database in code<cfabort></cfif> <!--- SET TYPE NAME (names not listed must be added) ---> <cfset xtypes=arrayNew(1)> <cfset xtypes[56]='INT'> <cfset xtypes[60]='CURRENCY'> <cfset xtypes[61]='DATETIME'> <cfset xtypes[104]='BIT'> <cfset xtypes[167]='VARCHAR(length)'> <cfset xtypes[175]='CHAR(length)'> <cfset CFtypes=arrayNew(1)> <cfset CFtypes[56]='CF_SQL_INTEGER'> <cfset CFtypes[60]='CF_SQL_MONEY'> <cfset CFtypes[61]='CF_SQL_DATE'> <cfset CFtypes[104]='CF_SQL_BIT'> <cfset CFtypes[167]='CF_SQL_VARCHAR'> <cfset CFtypes[175]='CF_SQL_CHAR'> <!--- SELECT TABLE PAGE ---> <cfif not isdefined('url.id') and not isdefined('form.cols')> <h3>SELECT TABLE</h3> <cfquery name="reports" datasource="#databasename#"> SELECT id,name FROM sysobjects where uid=1 and (xtype ='u' or xtype='v') and name not like 'dt%' and name not like 'sys%' ORDER BY name </cfquery> <cfoutput query="reports"> <a href="SP.cfm?id=#id#&tablename=#name#"> #name#</a><br> </cfoutput> </cfif> <!--- SELECT COLUMNS PAGE ---> <cfif isdefined('url.id')> <a href="sp.cfm">start again</a> <h3>SELECT COLUMNS FOR <cfoutput>#url.tablename#</cfoutput></h3> <cfquery name="columns" datasource="#databasename#"> SELECT id,name,xtype,length FROM syscolumns WHERE syscolumns.id=#url.id# ORDER BY name </cfquery> <form action="SP.cfm?table=<cfoutput>#url.id#&tablename =#url.tablename#</cfoutput> " method="post" name="postform"> <cfoutput query="columns"> <input type="checkbox" name="cols" value="'#name#'"> #name#<br> </cfoutput><br> <input type="radio" name="sptype" value="INSERT" checked> INSERT <input type="radio" name="sptype" value="UPDATE"> UPDATE<br><br> <input type="submit" value="Compile SQL"> </form> </cfif> <!--- RESULTS PAGE ---> <cfif isdefined('form.cols')> <a href="sp.cfm">start again</a> <cfquery name="columns" datasource="#databasename#"> SELECT id,name,xtype,length FROM syscolumns WHERE syscolumns.id=#url.table# and name in (#preservesinglequotes(form.cols)#) ORDER BY name </cfquery> <h3>SQL CODE</h3> CREATE PROCEDURE [sp_<cfoutput><i>#url.tablename#</i></cfoutput>]<br> <br> <cfset rowcount=1> <cfoutput query="columns"> @#name# #rereplacenocase(xtypes[xtype],'length','#length#')##iif(rowcount eq columns.recordcount,de(''),de(','))#<br> <cfset rowcount=rowcount+1> </cfoutput> <br> AS<br> <br> <cfif form.sptype eq 'INSERT'> INSERT INTO <cfoutput>#url.tablename#</cfoutput> (<br> <cfset rowcount=1> <cfoutput query="columns"> #name##iif(rowcount eq columns.recordcount,de(''),de(','))#<br> <cfset rowcount=rowcount+1> </cfoutput> ) <br> VALUES (<br> <cfset rowcount=1> <cfoutput query="columns"> @#name##iif(rowcount eq columns.recordcount,de(''),de(','))#<br> <cfset rowcount=rowcount+1> </cfoutput> ) <cfelse> UPDATE <cfoutput>#url.tablename#</cfoutput><br> SET<br> <cfset rowcount=1> <cfoutput query="columns"> #name#=@#name##iif(rowcount eq columns.recordcount,de(''),de(','))#<br> <cfset rowcount=rowcount+1> </cfoutput> WHERE <i>[where clause]</i> </cfif> <br><br> <h3>CF CODE</h3> <cfstoredproc procedure ="<cfoutput>sp_#url.tablename#</cfoutput>" datasource="#attributes.datasource#"><br> <cfoutput query="columns"> <cfprocparam type ="In" cfsqltype="#CFtypes[xtype]#" dbvarname="@#name#" value="##attributes.#name###"><br> </cfoutput> <i> <cfprocresult name="<cfoutput>#url.tablename#</cfoutput>"></i><br> </cfstoredproc> </cfif> -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
