Thanks Robert, Robert and Ben-
Here's a little more info, and some results.
In the MS docs for SQL Server 2005 it seems they recommend using
varbinary(max) rather than image or other datatypes for file storage.
I think this was a move to create a more universal datatype for all
files, and there are various filters that can be applied when
creating a full-text index of the contents of that field type. This
way you can have a single datatype used to create your own db-based
file storage system, and have a useful, searchable index maintained
that is many times faster than 'LIKE' based searches within SQL
Server itself. So that's why I'm trying to work out how to do this.
That said, I tried modifying the column and setting it to TEXT and
was then able to upload and store files no problem. So it's a step
closer, but still not quite what I was hoping for. I'll let you know
how it goes. In theory this will yield the best solution so far for
an intranet file system that will be a big step forward from what I'm
doing now. However, the file-system based approach (uploading the
files and then writing them to a directory, and then serving them
back with a file read (from a secure directory) and modified HTTP
headers has been in place working well for a number of years, and
houses over 100,000 files). The main reason for changing over is to
create a more searchable intranet, and also move away from a more
fixed physical directory structure for the files, and create a more
'virtual' one with the ability to easily have pointers to the same
files from more than one place, or move them from one directory to
another without physically having to move the file itself.
As far as efficiency goes, given that it's a lot of listing but much
lower volume serving and writing, I'm hopeful that the DB will handle
that just fine. It will be on a pretty robust setup with a dedicated
dual-processor DB server separate from the web/app server.
Cheers guys-
Jason
-----
Jason Pamental
Director of Web Services
North Sails
Office: 401.643.1415
Fax: 401.643.1420
Mobile: 401.743.4406
Email: [EMAIL PROTECTED]
On Dec 2, 2006, at 5:29 PM, Robert Garcia wrote:
One more point, I have a couple systems using mssql, and blob
storage and retrieval on MSSQL 2000 has been much less efficient,
and slower. I have had to rewrite code on mssql to help not show as
many images per page as I would would with mysql.
Just a note. Maybe 2005 is better.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Dec 2, 2006, at 10:30 AM, Jason Pamental wrote:
Thanks Robert - I'm wondering if there is some sort of issue with
the varbinary(max) datatype in SQL Server 2005. That's supposed to
allow the largest files to be inserted, but in the dev studio it
shows as varbin(0). I'll have to try some different datatypes -
but is that the right approach - take the postarg containing the
file and insert it via an insert action, no conversion or anything?
Cheers,
Jason
-----
Jason Pamental
Director of Web Services
North Sails
Office: 401.643.1415
Fax: 401.643.1420
Mobile: 401.743.4406
Email: [EMAIL PROTECTED]
On Dec 2, 2006, at 1:15 PM, Robert Garcia wrote:
I am not familiar with SQL Server 2005, but I know 2000 will
throw errors like that when the length of what you are inserting
is greater than the field length you are inserting into.
Either, change the length of the field in the table schema, or
use <@left > to limit the <@arg> on the insert to make sure it is
not more than the length of the table field.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Dec 2, 2006, at 9:35 AM, Jason Pamental wrote:
Hi all-
I've read through lots of posts about inserting BLOBs, but am
hitting snags trying it out for the first time.
I'd like to use the varbinary(max) data type in SQL Server 2005
so I can create a full-text index on the files in the system,
but when I try to insert the file (an @arg from a form) I get an
error (String truncation right). Am I missing a step - do I need
to do a conversion on the postarg to binary prior to insert?
Any ideas would be most welcome. My goal is to convert from a
filesystem based storage method for an intranet to a DB-based one.
Thanks-
Jason
-----
Jason Pamental
Director of Web Services
North Sails
Office: 401.643.1415
Fax: 401.643.1420
Mobile: 401.743.4406
Email: [EMAIL PROTECTED]
___________________________________________________________________
_____
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
____________________________________________________________________
____
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
_____________________________________________________________________
___
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
______________________________________________________________________
__
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
