[ cf-dev ] Automated Stored Procedure Writing - The Solution in CF

[Paolo Piponi]

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">
                &nbsp;&nbsp;&nbsp;&nbsp;@#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">
                        &nbsp;&nbsp;&nbsp;&nbsp;#name##iif(rowcount eq
columns.recordcount,de(''),de(','))#<br>
                        <cfset rowcount=rowcount+1>
                </cfoutput>
                )
                <br>
                VALUES (<br>
                <cfset rowcount=1>
                <cfoutput query="columns">
                        &nbsp;&nbsp;&nbsp;&nbsp;@#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">
                        &nbsp;&nbsp;&nbsp;&nbsp;#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>
        &lt;cfstoredproc procedure="<cfoutput>sp_#url.tablename#</cfoutput>"
datasource="#attributes.datasource#"&gt;<br>
        <cfoutput query="columns">
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;cfprocparam type="In"
cfsqltype="#CFtypes[xtype]#" dbvarname="@#name#"
value="##attributes.#name###"&gt;<br>
        </cfoutput>
        <i>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;cfprocresult
name="<cfoutput>#url.tablename#</cfoutput>"&gt;</i><br>
        &lt;/cfstoredproc&gt;
</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]