Insert and Retrieve binary objects with MS-SQL 2000

[HeloWorld]

Alrighty then,

I have searched the web abd CF forums for the past 2 months trying to find a
code snippet for inserting a binary object into a MS-SQL 2000 db.

I am trying to insert an MS-Office document (whether it be
word/excel/powerpoint) into a DB.
I DO NOT want to store the file in a directory structure of sorts and just
POINT to it from the DB.
(which seems to be the suggestion when anyone else has asked a similar
question.)

My process steps will be along the lines of...
get filename from user.
store filename in appropriate variable
create UID
store UID in appropriate variable.
insert uid + filename + binary object into DB.

if anyone has some time I would be much appreciative for the assistance.
I am certain that with a working INSERT snippet I will be able create
retrieval code myself.

[PaulH]

something like this doesn't work for you?

<cffile action="READBINARY" file="e:\temp\trendyBlueCast.jpg"
variable="imageFile">

<cfquery name="testBinary" datasource="lab">
INSERT testBinary(docType,theFile)
VALUES('jpeg',<cfqueryparam cfsqltype="CF_SQL_BLOB" value="#imageFile#"
null="No">)
</cfquery>

[HeloWorld]

Hi Paul,

thanks for the reply...
TYou suggested I use the following :
VALUES('jpeg',<cfqueryparam cfsqltype="CF_SQL_BLOB" value="#imageFile#"
null="No">)
</cfquery>

I take I am replacing the VALUES('JPEG' with
VALUES('doc'... if it is a word document and 'x's' if its an excel document?

And, while I have your attention... what the heck does cfqueryparam do?
I haven''t seen/used this tag before.

[PaulH]

that was just an example, though if you want to use full-text indexing i think
you'll need a columns to hold doc type, etc. as for cfqueryparam see
[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=0 0000317.htm, to quote the
docs: "Verifies the data type of a query parameter and, for DBMSs that support
bind variables, enables ColdFusion to use bind variables in the SQL statement.
Bind variable usage enhances performance when executing a cfquery statement
multiple times." further for public facing websites, "Macromedia recommends
that you use the cfqueryparam tag within every cfquery tag, to help secure your
databases from unauthorized users."

its a good practice.

[HeloWorld]

Just in case any other not-so-nimble minded person ends up searching the MM
forums for help with inserting MS-Office docs into a DB I have copied in an
email that finally made sense to me.

Mind you saying that... know that I actually understand what is going on - the
other dozen or so threads I have read previously all make sense to me now
too... - better late than never I suppose!


Gavin Baumanis wrote:

> 1) Can I get an example of inserting an office document into a DB.

i don't think your files being ms office is going to make any
difference. that example i posted should work ok for your office docs.
did you try?

> 2) Will I need to alter the SQL for Document types?

no you shouldn't. binary is binary. i'm not following your concerns.
what seems to be the problem?

> document, therefore I'll need some conditional statements testing/asking
> for the file type, then using that file type in the VALUES arguments?

that only matters if you're using full text (i think) and of course when
you want to use these files then you'd need original file names & types.
this snippet seems to work ok for me.

<!--- get a word doc --->
<cfset theFile="e:\users\paul\cfbooks\6\peachpitpress\cfa dv6 toc 01.doc">
<cfset fileName=getFileFromPath(theFile)> <!--- file name --->
<cfset fileExt=listLast(filename,".")> <!--- doc type --->
<cffile action="READBINARY" file="#theFile#" variable="imageFile">
<!--- stick in the db --->
<cfquery name="i" datasource="lab">
INSERT testBinary(docType,docName,original,theBLOB)
VALUES('#fileExt#','#fileName#',getDate(),<cfquery param
cfsqltype="CF_SQL_BLOB" value="#imageFile#" null="No">)
</cfquery>
<!--- get it back --->
<cfquery name="i" datasource="lab">
SELECT TOP 1 docName,theBLOB
FROM testBinary
WHERE docName='#fileName#'
</cfquery>
<!--- write it out --->
<cffile action="WRITE" file="e:\temp\#i.docName#" output="#i.theBlob#"
addnewline="No" fixnewline="No">
<!--- did it get there ok? --->
<cfdirectory action="LIST" filter="#i.docName#" name="docList"
directory="e:\temp\">
<cfif docList.recordCount>
<cfdump var="#docList#">
<cfelse>
Something bad happened.
</cfif>

and this is the DDL for that test table:
CREATE TABLE [dbo].[testBinary] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[docType] [varchar] (3) NULL ,
[docName] [varchar] (50) NULL ,
[original] [smalldatetime] NULL ,
[modified] [smalldatetime] NULL ,
[theBLOB] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

A special thanks to PaulH of the MM team....