INSERT file to column

[HugoSchmidt]

I have done some due diligence looking for an answer, but I have not found it
so here is my post:

I have a table t_CustomerFiles, with two columns, CustomerID (INT) and
FileData (NTEXT)

On a web page the customer is uploading a file and I want to insert the entire
file (it is TEXT, not binary) into the table. The following does not work 100%
of the time. Sometimes the preservesinglequote fails and the INSERT statement
gets terminated.

<cffile action="READ" file="D:\customerFile.txt" variable="ReadFile">

<cfquery datasource="#CurDS#" dbtype="#CurDSType#">
INSERT INTO CustomerFiles (CustomerID , FileData )
VALUES(12342, '#preservesinglequotes(ReadFile)#')
</cfquery>

Is there a way that I can simply tell Microsoft SQL Server 2000 to "go get
that file and put it in the database with the CustomerID?"

Somethig like this:
<cfquery datasource="#CurDS#" dbtype="#CurDSType#">
INSERT INTO CustomerFiles (CustomerID , FileData )
VALUES(12342, GetFile('D:\customerFile.txt'))
</cfquery>

(I know that is bogus syntax, but one can only wish upon a star)

[Sojovi]

Using a DTS, but you can't call it from CF, you have to use dtsrun (from
command line).

With BULK INSERT you have to get all the values to insert in the table inside
the txt file.

WRITETEXT, but you have to use preservesinglequotes anyway.

Regards

[PaulH *ACE*]

Sojovi wrote:

> Using a DTS, but you can't call it from CF, you have to use dtsrun (from
> command line).

what makes you say that? we do it all the time. you can easily call any DTS job
from within a sp or even call it via COM.

[Sojovi]

OK, don't get angry, thanks for let me know that. Take it easy, it's a forum, not a class room.

Regards

[Sojovi]

And would be a good idea that you write how we can do it.... if not, why are you posting messages here ?

Best regards

[HugoSchmidt]

Thanks all, I just did a rereplace() instead of the PreserveSingleQuotes() and that worked fine. It may be a patch, but it did the job.