David,
Wow! This is a very thorough example. I'm going to try it out today.
Thanks for all of your help.
Lyndon Hughey
----- Original Message -----
From: "David L. Penton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 15, 2001 2:00 AM
Subject: Using getChunk() and appendChunk()
> Here are some code snips that demonstrates how you insert and retrieve
data
> from a TEXT field in SQL Server. This is similar to an NTEXT, BINARY, and
> VARBINARY. This is the recommended way to insert/retrieve data from a
> MSAccess database memo field as well. I did not comment this very much at
> all. Sorry about that :-) These are only test files. I would think you
> would want some error checking and validation on some of this. Perhaps a
> CFMODULE or something similar. If I was to redo the append_chunk page, I
> would use ADO for all of it (including using ADO transactions) If you
want
> me to send files, let me know.
>
>
> --begin database information
>
> CREATE TABLE Table1 (
> [id] [int] IDENTITY (1,1) NOT NULL
> , [name] [varchar] (10) NOT NULL
> , [blob] [text]
> )
>
> -- end database infromation
>
> -- begin application.cfm
>
> <cfscript>
>
> variables.username="sa";
> variables.password="thetanu";
> variables.datasource="CONSTELLATION";
> variables.dbname="TEST";
>
> variables.connectionString = "Network Library=DBMSSOCN;" &
> "Provider=SQLOLEDB.1;Persist Security Info=True;" &
> "Password=#variables.password#;User ID=#variables.username#;" &
> "Initial Catalog=#variables.dbname#;Data
> Source=#variables.datasource#";
>
> </cfscript>
>
> -- end application.cfm
>
>
> -- begin append_chunk.cfm
>
> <html>
> <body>
>
> <a href="append_chunk.cfm">Append</a> - <a href="get_chunk.cfm">Get</a><br
> />
>
> <CFPARAM name="form.exists" default="no">
> <CFPARAM name="form.data" default="">
> <CFSET variables.data = form.data>
>
> <CFIF #form.exists# IS "no">
>
> <form action="append_chunk.cfm" method="post">
> <input type="hidden" name="exists" value="yes" />
>
> <table border="0" cellpadding="2" cellspacing="3">
> <tr>
> <td>Name:</td><td><input type="text" name="name" /></td>
> </tr>
> <tr>
> <td>Text:</td><td><textarea name="data" rows="10" cols="40"
> wrap="physical"></textarea></td>
> </tr>
> <tr>
> <td colspan="2" class="t14"><input type="submit" value="Append
> Chunk" /></td>
> </tr>
> </table>
> </form>
>
> <CFELSE>
>
> <CFQUERY NAME="newBlob" DATASOURCE="#variables.datasource#"
> DBTYPE="OLEDB" DBSERVER="#variables.datasource#"
> DBNAME="#variables.dbname#" USERNAME="#variables.username#"
> PASSWORD="#variables.password#" PROVIDER="SQLOLEDB">
>
> SET NOCOUNT ON
>
> DECLARE @id int
>
> INSERT INTO [dbo].[Table1] (
> [name]
> )
> VALUES (
> '#form.name#'
> )
>
> SELECT @@IDENTITY [NewId]
>
> </CFQUERY>
> <CFSET variables.fieldName = "blob">
> <CFSET sql = "SELECT a.[blob] [#variables.fieldName#] " &
> "FROM [dbo].[Table1] a " &
> "WHERE a.[id] = #newBlob.NewId#">
> <CFINCLUDE template="string_to_blob.cfm">
>
> <CFOUTPUT>The new id is #newBlob.NewId#</CFOUTPUT>
>
> </CFIF>
>
> </body>
> </html>
>
> -- end append_chunk.cfm
>
> -- begin string_to_blob.cfm
>
> <CFOBJECT TYPE="COM" ACTION="create" CLASS="ADODB.Connection" NAME="conn">
> <CFOBJECT TYPE="COM" ACTION="create" CLASS="ADODB.Recordset" NAME="rs">
> <cfscript>
> variables.chunkSize = 8192;
> variables.adOpenForwardOnly = 0;
> variables.adLockOptimistic = 3;
> variables.adCmdText = 1;
> variables.adConnectUnspecified = -1;
> start = 1;
> bytesLeft = Len(variables.data);
> conn.Open(variables.connectionString
> , "#variables.username#"
> , "#variables.password#"
> , variables.adConnectUnspecified);
> rs.Open(sql, conn, adOpenForwardOnly, adLockOptimistic, adCmdText);
> if (rs.EOF IS FALSE) {
> flds = rs.Fields;
> fld = flds.Item(fieldName);
> for (;;) {
> bytes = bytesLeft;
> if (bytes GT chunkSize) bytes = chunkSize;
> fld.appendChunk(Mid(variables.data, start, bytes));
> bytesLeft = bytesLeft - bytes;
> if (bytesLeft LTE 0) break;
> }
> rs.Update();
> }
> rs.Close();
> conn.Close();
> </cfscript>
>
> -- end string_to_blob.cfm
>
> -- begin get_chunk.cfm
>
> <html>
> <body>
>
> <a href="append_chunk.cfm">Append</a> - <a href="get_chunk.cfm">Get</a><br
> />
>
> <CFPARAM name="form.id" default="">
> <CFPARAM name="variables.data" default="">
>
> <CFQUERY NAME="ddlist" DATASOURCE="#variables.datasource#"
> DBTYPE="OLEDB" DBSERVER="#variables.datasource#"
> DBNAME="#variables.dbname#" USERNAME="#variables.username#"
> PASSWORD="#variables.password#" PROVIDER="SQLOLEDB">
> SELECT
> a.[id], a.[name]
> FROM
> [dbo].[Table1] a
>
> </CFQUERY>
>
> <CFIF IsNumeric(form.id)>
> <CFSET variables.fieldName = "blob">
> <CFSET sql = "SELECT a.[blob] [#variables.fieldName#] " &
> "FROM [dbo].[Table1] a " &
> "WHERE a.[id] = #form.id#">
> <CFINCLUDE template="blob_to_string.cfm">
> </CFIF>
>
> <form action="get_chunk.cfm" method="post">
> <input type="hidden" name="exists" value="yes" />
>
> <table border="0" cellpadding="2" cellspacing="3">
> <tr>
> <td>Name:</td>
> <td><select name="id"
> onChange="this.form.dummy.value='';this.form.submit()">
> <option value="">Names</option>
> <CFOUTPUT QUERY="ddlist">
> <option value="#ddlist.id#"<CFIF ddlist.id IS form.id
> > SELECTED="SELECTED"</CFIF>>#ddlist.name#</option></CFOUTPUT>
> </select></td>
> </tr>
> <tr>
> <td>Text:</td>
> <td><textarea name="dummy" rows="10" cols="40" wrap="physical"
>
> ><CFOUTPUT>#HTMLEditFormat(variables.data)#</CFOUTPUT></textarea></td>
> </tr>
> </table>
> </form>
> </body>
> </html>
>
> -- end get_chunk.cfm
>
> -- begin blob_to_string.cfm
>
> <CFOBJECT TYPE="COM" ACTION="create" CLASS="ADODB.Connection" NAME="conn">
> <CFOBJECT TYPE="COM" ACTION="create" CLASS="ADODB.Recordset" NAME="rs">
> <cfscript>
> variables.chunkSize = 8192;
> variables.adOpenForwardOnly = 0;
> variables.adLockReadOnly = 1;
> variables.adCmdText = 1;
> variables.adConnectUnspecified = -1;
> variables.data = "";
> conn.Open(variables.connectionString
> , "#variables.username#"
> , "#variables.password#"
> , variables.adConnectUnspecified);
> rs.Open(sql, conn, adOpenForwardOnly, adLockReadOnly, adCmdText);
> if (rs.EOF IS FALSE) {
> flds = rs.Fields;
> fld = flds.Item(fieldName);
> bytesLeft = fld.ActualSize;
> while(bytesLeft GT 0) {
> bytes = bytesLeft;
> if (bytes GT chunkSize) bytes = chunkSize;
> variables.data = variables.data & fld.getChunk(bytes);
> bytesLeft = bytesLeft - bytes;
> }
> }
> rs.Close();
> conn.Close();
> </cfscript>
>
> -- end blob_to_string.cfm
>
>
>
> David L. Penton, MCP
> "Mathematics is music for the mind, and Music is Mathematics for the
> Soul." - J.S. Bach
> [EMAIL PROTECTED]
>
> "Remember - I can only show you the door. You have to walk through it."
>
> "Are you patched for CodeRed? If not, why not?"
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/
> bulletin/MS01-033.asp
>
>
> -------------------------------------------------------------------------
> This email server is running an evaluation copy of the MailShield anti-
> spam software. Please contact your email administrator if you have any
> questions about this message. MailShield product info: www.mailshield.com
>
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
>
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
