Storing docs with SQL2000

[GUKLLY]

:evil;
hi to all....

i need help: i'm new to SQL 2000 and i want to create a DB to store my docs
to put it on my site accessible to every body...

i have this structures:

CREATE TABLE . (
IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
(500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
NOT NULL
) ON TEXTIMAGE_ON
GO

ALTER TABLE . WITH NOCHECK ADD
CONSTRAINT PRIMARY KEY CLUSTERED
(

) ON
GO

ALTER TABLE . WITH NOCHECK ADD
CONSTRAINT DEFAULT ('''C\:') FOR ,
CONSTRAINT DEFAULT ('') FOR ,
CONSTRAINT DEFAULT (6) FOR
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.sp_mt_gen_file_insert

@CONOP_ID INT,
@ADJ_REFER VARCHAR(25),
@ADJ_ID INT,
@ADJ_NAME VARCHAR(50),
@ADJ_PATH VARCHAR(100),
@ADJ_ALIAS VARCHAR(50)


AS
INSERT INTO DBO.DOCS
(DOC_ID,DOC_REFERENCE, DOC_PATH,DOC_NAME, DOC_FILE, DOC_TYPE)
VALUES
(@CONOP_ID,@ADJ_REFER,@ADJ_PATH,@ADJ_NAME,@ADJ_ALI AS,@ADJ_ID)


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

and on my page i use <cfstoredproc> to send it ; the problema is that i just
send the full path to the doc_file column, and i need to send the file( .xls;
..doc; .pdf; .jpg; .txt...)

i was trying with FTP connections, but i think the real problem is on SQL
structures....


Any help will be great!..

[philh]

Hi GUKLLY,

In the SQL database, your file is just a pile of bits. You have to trick the
browser into thinking that the pile is an actual file.

To do this, we use CFCONTENT. An example that would bring back a Word doc
from your database:
<CFQUERY name="getdoc" datasource="mysqldatasource">
SELECT doc_file FROM Docs
WHERE doc_name = '#whateveryoupasstogetthefile#' --put the correct variable
reference here
</CFQUERY>

<CFCONTENT type="file" filename="#yourfilename#">
<CFOUTPUT>#getdoc.doc_file#</CFOUTPUT>

HTH,

[philh]

Well, that was helpful.

Now that I've REALLY read your post, try using CFFILE action="read" and inserting the variable contents into the table.

HTH,

[GUKLLY]

Hi philh ; i understand, but which data type would be the column on my DB??, cause i put it as TEXT, and as NTEXT, but, it dosn't work...

[MikerRoo]

You would use datatype "varbinary" but take it from experience, it is a HUGE
mistake to store files this way in SQL server!

Store them seperately in disk (you can even use a compressed drive) and merely
store the particulars in SQL server.

For server storage, rename the file to a globally unique name. In the DB,
record the storage path and storage name, the original name and file extension,
plus anything else you want to track about the file.

[PaulH *TMM*]

GUKLLY wrote:

> Hi philh ; i understand, but which data type would be the column on
> my DB??, cause i put it as TEXT, and as NTEXT, but, it dosn't work...

if the data is text, then text, if it contains unicode data use nText.
if it's "binary" data (word docs, PD, etc.) use image.

[PaulH *TMM*]

MikerRoo wrote:

> You would use datatype "varbinary" but take it from experience, it is a HUGE
> mistake to store files this way in SQL server!

varbinary is probably a bad idea to store a lot of data but storing
BLOBs in a database isn't anything like a HUGE mistake, at least not
w/modern databases. that's my experience anyways.

[MikerRoo]

Yeah image is better.
We don't store such data in the DB because we've learned from experience that
it's bad. This theorem is backed up by the SMART people that we've read and
conversed with.

Anyway, not using these types often, I used the object browser -- which says
that varbinary has a max length of 2^31-1 bytes.

This is, of course, wrong and the correct size is stated elsewhere in BOL.

So, add ANOTHER flaw (documentation/tools) to Microsoft's large-data handling.

[PaulH *TMM*]

MikerRoo wrote:

> We don't store such data in the DB because we've learned from experience that

our experience differs. we store huge amounts of binary spatial data in
databases including raster imagery (aerial/satellite photos, etc).
besides sql server we also use postGIS (on top of postgreSQL) for this.
it's nothing for modern db & s/w.

> it's bad. This theorem is backed up by the SMART people that we've read and
> conversed with.

such as who?

> So, add ANOTHER flaw (documentation/tools) to Microsoft's large-data handling.

minor.