Ron,
Here is a simple example.
<cfquery name="createContactUsStates" datasource="myDatasource">
CREATE TABLE
CONTACTUS_states
(
stateCode varchar(2) NOT NULL,
stateName varchar(25) NOT NULL
)
</cfquery>
Mike
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ron Mast
Sent: Tuesday, March 06, 2007 3:09 PM
To: Dallas/Fort Worth ColdFusion User Group Mailing List
Subject: RE: [DFW CFUG] AS/400 gurus
Ooops...got too excited there...lol.
I was asking for the SQL syntax to create/delete tables and fields for
an AS/400 database.
Sorry if I wasn't clear.
Ron Mast
Truth Hardware
Webmaster
507-444-4693
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ron Mast
Sent: Tuesday, March 06, 2007 2:50 PM
To: Dallas/Fort Worth ColdFusion User Group Mailing List
Subject: RE: [DFW CFUG] AS/400 gurus
Thanks Jimmay!
Ron Mast
Truth Hardware
Webmaster
507-444-4693
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jimmy Harrell
Sent: Tuesday, March 06, 2007 2:47 PM
To: Dallas/Fort Worth ColdFusion User Group Mailing List
Subject: Re: [DFW CFUG] AS/400 gurus
Forta's CFWACK uses this interface.
4 files.
1.sql.cfm
<!---code below-->
<HTML>
<HEAD>
<TITLE>SQL Query Tool</TITLE>
</HEAD>
<FRAMESET ROWS="200,*" FRAMEBORDER="NO" BORDER="0" FRAMESPACING="0">
<FRAME SRC="query.cfm" NAME="top" SCROLLING="NO" NORESIZE>
<FRAME SRC="results.cfm" NAME="bottom">
</FRAMESET><NOFRAMES></NOFRAMES>
</HTML>
2.query.cfm
<!--code below-->
<!--- Form to obtain SQL --->
<BODY>
<FORM ACTION="results.cfm" METHOD="post" TARGET="bottom">
<STRONG>SQL Query:</STRONG><BR>
<TEXTAREA NAME="sql" ROWS="8" COLS="40"></TEXTAREA><BR>
<!--- Prompt for datasource only if so specified --->
<CFIF datasource_prompt>
<CFOUTPUT><STRONG>Data Source:</STRONG> <INPUT TYPE="text"
NAME="datasource" VALUE="#datasource_default#"
CLASS="input1"></CFOUTPUT>
</CFIF>
<INPUT TYPE="submit" VALUE="Execute">
</FORM>
</BODY>
3.results.cfm
<!--code below-->
<!--- Init variables --->
<CFPARAM NAME="FORM.datasource" DEFAULT="#datasource_default#">
<CFPARAM NAME="FORM.sql" DEFAULT="">
<CFSET whitespace=" #Chr(10)##Chr(13)#">
<!--- If no SQL, quit --->
<CFIF Trim(FORM.sql) IS "">
<CFABORT>
</CFIF>
<BODY>
<!--- Check if only SELECT allowed --->
<CFIF select_only>
<!--- Check first word is SELECT --->
<CFIF UCase(ListFirst(Trim(FORM.sql), whitespace)) NEQ "SELECT">
<!--- Abort --->
<H1>Only SELECT statements are allowed</H1>
<CFABORT>
</CFIF>
</CFIF>
<!--- Try/catch block --->
<CFTRY>
<!--- Execute SQL --->
<CFQUERY NAME="results" DATASOURCE="#FORM.datasource#" DEBUG="No">
#PreserveSingleQuotes(FORM.sql)#
</CFQUERY>
<!--- Number of results --->
<CFIF IsDefined("results.RecordCount")>
<CFOUTPUT><STRONG>#results.RecordCount# #IIf(results.RecordCount IS 1,
DE("Row"), DE("Rows"))# Returned</STRONG></CFOUTPUT>
</CFIF>
<!--- Dump results --->
<CFIF IsDefined("results")>
<CFDUMP VAR="#results#">
<CFELSE>
<H1>No results returned</H1>
</CFIF>
<!--- Catch errors --->
<CFCATCH>
<!--- Something went wrong --->
<CFOUTPUT><H1>#CFCATCH.Message#</H1><H3>#CFCATCH.Detail#</H3></CFOUTPUT>
<!--- Provide extra hints, if possible --->
<CFIF FindNoCase("Too few parameters", CFCATCH.Detail)>
Hint: This usually means you've mistyped a column name or have missed
a comma.
<CFELSEIF FindNoCase("missing operator", CFCATCH.Detail)>
Hint: Make sure that you have not missed a comma or mistyped a
keyword.
</CFIF>
</CFCATCH>
</CFTRY>
</BODY>
4.Application.cfm (or cfc if you want to be current)
<!--code below-->
<!--- Settings --->
<!--- To prompt for data source set this to "yes"> --->
<CFSET datasource_prompt="No">
<!--- Specify a default data source --->
<CFSET datasource_default="ows">
<!--- Set to "yes" to only allow SELECT statements, "No" for all --->
<CFSET select_only="yes">
<!--- To restrict access by IP address specify the allowed client IP
address here --->
<CFSET restrict_ip="127.0.0.1">
<!--- Check IP address --->
<CFIF (restrict_ip NEQ "") AND (restrict_ip NEQ CGI.remote_addr)>
<!--- Quit of not allowed --->
<CFABORT>
</CFIF>
If you want to be albe to use delete, update, etc just change this line
in your Application.cfm file
<cfset select_only="yes">
to "no"
Also make sure you're pointing to your datasource and place all four
files in the same directory. Wouldn't use it on a production server
though.
Good Luck.
Jimmy L. Harrell
MBA: E-Business
Website:www.jimmyharrell.com <http://www.jimmyharrell.com/>
Blog: http://jimmyharrell.instantspot.com
Local User Groups: Dallas-Ft. Worth Adobe User Group
<http://www.dfwaug.net/> & Dallas-Ft. Worth Coldfusion User Group
<http://dfwcfug.instantspot.com/>
CONFIDENTIALITY NOTICE: The information contained in this e-mail and attached
document(s) may contain confidential information that is intended only for the
addressee(s). If you are not the intended recipient, you are hereby advised
that any disclosure, copying, distribution or the taking of any action in
reliance upon the information is prohibited. If you have received this e-mail
in error, please immediately notify the sender and delete it from your system._______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/
www.teksystems.com/
