Further progress...
After a lot more digging and trial and error, I think I've found the
issue: SQL Native Client vs. SQL Server ODBC driver
It seems that it fails miserably with the string truncation error
when I set up the datasource using SQL Native Client, but works just
fine if I create one using the SQL Server ODBC driver (version
2000.85.1117.00).
Further to the question about using TEXT as a datatype, or Image - it
seems that support for TEXT and IMAGE may be getting dropped in the
next version of SQL Server, so this seems to be the way they are
pushing people to go.
For all other development I've been doing in almost a year, this is
the first time I've seen an issue with SQL Native Client - it seems
that this might be compatibility issue for Witango users though. I
haven't done any testing to see if one is faster than the other for
access - it'd be interesting to see though.
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 4, 2006, at 9:58 AM, Jason Pamental wrote:
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
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
