January 21, 2005 at 2:04 am
Dear All,
I have an eForm that would insert records into sql server. And the eForm can have attachment, is it possible for sql server to insert the attachement into the tables? If yes, what kind of datatype should I use; otherwise, what other approach would you suggest?
THx a lot
Victor
January 21, 2005 at 2:38 am
Yes, the IMAGE data type should work. This is similar to the MySQL LONGBLOB type.
I use the ADO command object in a similar manner to that below
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.RecordSet")
cmd.ActiveConnection = connString
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandText = "Usp_SetData"
cmd.CommandType = 4
cmd.CommandTimeout = 0
cmd.Prepared = True
cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", 3, 4)
cmd.Parameters.Append cmd.CreateParameter("MyImage", 205, 1)
205 is the equivalent of adLongVarBinary.
The last number on each parameter line is the direction in which the parameter is flowing. 4 = Return value, 1 = input.
If you try and achieve the input using dynamic SQL you may run into situations where the attachment contains characters that break the SQL.
January 24, 2005 at 1:39 am
Thx David,
Can I use this datatype to store word or pdf documents? Is there any size limit for it?
Thx.
Victor
January 24, 2005 at 1:49 am
The size limit is 2Gb.
You can store any binary document you like in it, PDF, MS Office, Graphics, you name it.
If you are going to use replication you may want to check a setting called "Max Text Repl Size" because the default maximum size is 64K. You can up this all the way to 2Gb but I would put a more sensible limit on this.
If you are submitting the attachment from a web page then I would have your server side script check the size before writing it to the database.
January 24, 2005 at 1:27 pm
See if this helps:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply