Re: Cold Fusion and select/update large text objects

From: <craig_comstock_at_my-deja.com>
Date: Wed, 29 Nov 2000 21:03:40 GMT
Message-ID: <903qv7$ins$1@nnrp1.deja.com>

Problem: manipulate Oracle CLOB object with Cold Fusion.

Ok. I solved my own problem. I ended up using the DBMS_LOB package in conjunction with seperating the CLOB into chunks on the Cold Fusion side and in a stored procedure.

The code:

cctest.cfm

---

<CFQUERY NAME="getlength" DATASOURCE="" USERNAME="" PASSWORD="">
SELECT DBMS_LOB.GETLENGTH(doctext) as length FROM documents
WHERE DocID = 2
</CFQUERY>

<CFSET length = #getlength.length#>
<CFIF length IS ''>

  <CFSET length=1>
</CFIF>
<CFSET document = "">
<CFLOOP INDEX="i" FROM="1" TO="#length#" STEP="2000">

<CFQUERY NAME="get" DATASOURCE="" USERNAME="" PASSWORD="">
SELECT DBMS_LOB.SUBSTR(doctext,2000,#i#) as text FROM documents
WHERE docid = 2
</CFQUERY>
<CFSET document = document & get.text>
</CFLOOP>

<p>
<form method="post" action="cctest2.cfm">
<textarea name="document" rows="20" cols="80">
<CFOUTPUT QUERY="get">#document#</CFOUTPUT>
</textarea>
<input type="submit" value="Update">
</form>

---

cctest2.cfm

---

<CFQUERY NAME="delete" DATASOURCE="" USERNAME="" PASSWORD="">
UPDATE documents
SET DocText = ' '
WHERE DocId = 2
</CFQUERY>

<CFLOOP INDEX="i" FROM="1" TO="#len(document)#" STEP=2000>
<CFQUERY NAME="append" DATASOURCE="" USERNAME="" PASSWORD="">
call appendDocument('#mid(document,i,2000)#',2)
</CFQUERY>
</CFLOOP>

---

appendDocument.sql

---

CREATE OR REPLACE PROCEDURE appendDocument (chunk IN VARCHAR,id IN NUMBER) AS newdoc CLOB;
chunk_size BINARY_INTEGER;

BEGIN   DBMS_LOB.CREATETEMPORARY(newdoc,TRUE);

  SELECT doctext INTO newdoc FROM documents WHERE docid = id FOR UPDATE;

  DBMS_LOB.WRITEAPPEND(newdoc,LENGTH(chunk),chunk);

COMMIT; END;
/
SHO ERR;

---

documents table

---

CREATE TABLE documents (docid INT,doctext CLOB);

---

In article <903d6r$5vj$1_at_nnrp1.deja.com>,   craig_comstock_at_my-deja.com wrote:

> Does anyone have experience with selecting/updateing/insterting large
> text objects with Cold Fusion and Oracle?
>
> Currently I have a stored procedure that returns a CLOB that I would
> like to use. If that won't work, then I at least need to be able to
> manipulate a ~30k character object.
>
> Thanks,
> Craig Comstock
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 29 2000 - 15:03:40 CST