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
