Does anyone have thoughts or know of resources that explain the pro's and con's of storing images in a database versus keeping the image in the file system and just storing the path to the image in the database?
View 1 RepliesHi, can someone help me with this problem. I have recently downloaded VWD and have been playing around with it a bit. I was windering how do I add an image file to a SQL database. I have used SQL in the past but I have never store an image in it. I can add text data and so forth, but I need some pointers into adding images to the sql database. I want to be able to store the images and then retrieve them to a GridView. I have the images in an image folder within the website, but I do not know how to input the reference or link to the images in an sql table.
A quick example, say I have a table called Images, with the following info:imgID (int)imgDesc (varchar(max)imgFile (img)imgTitle (varchar(20))
To input this in sql I would write Insert Into Images (imgDesc,imgFile,imgTitle) Values ("Large fishy", "", "The Big Fish") what would I need to put for imgFile?
I would really appreciate if someone can point me in the right direction.
Hi whats the best way to store an image in SQL, for instance the image of a product, bearing in mind the website will be hosted by a hosting company.
View 4 Replies View RelatedHello All!
Can some one give SQL Statement on storing images from disk to the database columns.
Once I store the image how do I test it out that the image is stored correctly?
For example this is what I did
I have a table called table1., field called name
insert into table1(name) values(C:123.bmp)
Is the above statement correct? will it store the file or not?
if it does store them then how do I view them?
Can some one please help me...
Thanks in advance for your help
Venkat
Hi
We are building a portal internal to our org. Here, For one of the option on the portal, we have given each user in our organization an option to upload three images. Iam storing these images in Sql server table columns with datatype image.So, the question is:
1. Is it ok to store the images in sqlserver? Will it add any performance degradation?
2. Is it ok to store the images in folder and store the respective urls in the DB?
Please let us know.
Thanks!
Santhosh
I am storing images within and image field in sql, however when I go to get the data I have two issues.
the first issue is that my image is linked to information on another table but the only way that i have seen getting the image back is to do a:
response.binarywrite(mydatareader.item("image"))
but i am hoping to be able to find a way to use the template functionality with datagrid to align the picture with the information on the table. i am storing the image type and size with the image as well.
the second problem might be related to the load, i am not sure. sometimes, for some images and it just shows the frame of the image with the red x in the middle. The load appears to work (no errors) but since we cannot view them properly we dont know.
Our application wants to store all data in sql so that we can secure it and relate it to projects. in addition, we hope to be able to use this function to upload all sorts of binary documents from jpg, gif, doc, xls, visio, and others as we can. any issues with that process???
we would like to be able to display them directly in the browser where we can but all users have standard office.
any suggestions on either and hopefully both of these situations? we have sort of hit a brick wall.
I'm fiddling around with my sql server in my home computer. I would like to store a different image in each row of the product table (for example). All the pics are now in my hard disk. I gather from various discussion boards that there are several ways of saving images -
1. INTERNALLY
a)Use VB. Run in ISQL/w
b) Use insert statement. INSERT INTO EMP(empno,empname ,Picture_Id)
VALUES('E1235','Anthony','c:PhotosAnthony.jpg')
c) Use isql/w & TSQL statement
d) Use sqlgetdata and sqlputdatn
e) Use textcopy in binn directory.
f) Use VB & ADO
2. EXTERNALLY
a)store the image externally and simply store the path to it in a plain
varchar variable.
My questions are :-
(i) Can I save images simply using 1b)?
(ii)Is 2a) correct ? Do I just type the name of the image file in my harddisk as the value in the table ?
(iii) If I have the database linked to the internet and have it hosted by a commercial server in the future, will I have a nightmare reentering all the path names when I upload the database into the commercial server?
(iv) What are the pros and cons of the various methods listed in 1.
If you use the file upload HTML form field to insert an image (jpeg, etc.) into an IMAGE datatype column (which is not the problem), how can you pull that data back out and insert it into an HTML document back to an image (jpeg, etc.) format? Is that possible? I simply need to know if there is a way to use cast or convert or whatever so that the binary/hexidecimal data stored in the table can be reassembled as a real picture again.
Thanks in advance!
I wanted to store image files in my Db.So can i use image data type?If i can use the image data type how big images can i store.My image are arounf 10 mb in size.
Thanks.
hello ... i have a project this semester and i had to study is storing images in a database ( using Microsoft Access ) is good or bad ? so any one could tell me the advantages and the disadvantages please ...
thanks
bye
An odd question from me, I know, but this time, I assure you a twist.
I have a group that wants to store images in either a database or a file share, in order to make a certain website able to run on a load balanced web farm. These images are around 1KB each in size, and have a lifespan of exactly one use (think of graphs). I went a-googling, and found no shortage of articles that say "don't do it, but here's how you can do it", but I did not find any real hard statements as to why to not do it. Needless to say, this is hurting the case for not putting these images in the database. I have found an article that says images over 8KB will have worse performance, but I can not use that factoid here. For the moment, I have the developers leaning toward the fileshare, because they will be writing transaction logs all day, which will be more work than the fileshare needs to do....I think. The best I can do, is cut the text/image datatype overhead, and have them create the table as varbinary(2000), but even then, I don't like the look of the idea.
Anyone out there have good articles/whitepapers that detail the differences between writing single-use images to a fileshare vs. a SQL database? <baiting=blatant>Failing that, do any of the Microsoft development team have an opinion?</baiting>
Alternatively, what is the general opinion of keeping session state information in a database (because I bet that will be my next battle). I see .NET includes a session state server, but not being a programmer, I can not create an opbjective test.
So what's the secret to getting images in and out of a SQL database? There's lots out there about this, but I cant seem to get any of it to work...
I have a table, named Attachments and among other columns I have one called Attachment. I've tried using both image and varbinary(max) data types, both with the same results.
I have an sproc with the following code:
INSERT INTO Attachments (Attachment) SELECT * FROM OPENROWSET(BULK 'c: est.bmp', SINGLE_BLOB) AS BulkColumn
That seems to read the file and create a record. If I move the file, yeah it complains it doesnt exist... If I "show data" on the table, there is a new record and under Attachment, it says "<Binary Data>. t seems to be there, but who knows for sure...
So in an aspx file, I have the following:
myConnection.Open();string SQL = "SELECT Attachment FROM Attachments";SqlCommand myCommand = new SqlCommand(SQL, myConnection);byte[] bytePic = (byte[])myCommand.ExecuteScalar();
When I run it, it errors out on that last line with "Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.
With the use of "DBNull", it sure does seem the SQL is coming up emtpy handed, eh? If I change the SELECT to AttachmentId (an identity field), it says it cant cast a "system32" - which makes sense... Any ideas whats going on? Many thanks!! -- Curt
Hi,
I need to know if storing images in the MSSQL database is a viable option when compared to having them in a different directory and only storing their location or address in the DB.
I ask this with reference to the performance of the DB. Will having many images of huge size on the databse make it slow for retrieval as the MDX or database file size would eventually increase?
Does the size of the database file has to do anything with MSSQL query performance?
Comments on this will be appreciated.
Thanks
Hi There
I have not had much luck finding info in BOL.
I have a directory with many images, i need to load these images into sql server.We want to staore them in the database instead of the file server.
What is the best way to do this? TSQL (i am hoping), .NET code or maybe even SSIS ?
I have been reading what i can find in BOL basically all i know so far is that image data type will be no more in the future and i should use a varbinary(max) data type.
Basically i need a good link or something that can demonstrate how one can go about loading images into Sql Server 2005.
Thanx
Hi,I was wondering what anybodies feelings on storing images and files in SQL server express 2005?I am guessing it is a No No...If this is the case what are the best practices for achieving this?Would something like a table which held the "real name of the file", "a unique identifier" do the trick...hence in code (c#) i could create a GUID for the unique identifier maybe??? and then upload the pictue or file to a common directory and name it with the unique identifier rather than its real name.This means that i could potentially hold a different file with the same name i.e. no conflictsMy app is a sort of HTML editor in asp.net (all html is stored in the db) so if i do upload a picture using a GUID or uniquee identifier then if somebody saves teh image from a webpage then it would be called uniqueIdentifier.jpg which is probably what i don't want.So the only other idea i had was to create a directroy within my site which I would name with the unique identifier and upload the pics and files with thier real names into this directory. Hence each document would have its own directory so there would be no conflicts with filenamesAny advice or comments really appreciatedThanks in advanceIan
View 3 Replies View RelatedHi There,Being quite new to MS-SQL I would like to ask if there is a general opinionof what approach should be taken to storing things like external documentsand images in databases.Should the actual files be stored within the database, or instead shouldlinks to the files on a file server or something similar be stored instead.For the end user I imagine it is easier to have everything stored within thedatabase, because doing it the other way in effect gives another level ofmanagement because there is the need to perhaps manually look after the fileserver with all the image files or document files on.In my particular case, I am building a database where the users use a greatmany Word documents. For example, I particular record might have a number ofdifferent Word documents associated with it.I was going to create a "Documents" table that all documents were stored in(including meta data about each document because it will be really useful tobe able to search for documents so that they can be reused).In this table I was in a dilemma as to whether to actually store thedocuments in the table. Other then the performance hit and memoryrequirements this will require, are there any other disadvantages?What are the general thoughts when a database needs to manage a lot of pdfand word documents?Thanks in advance.Dave.
View 3 Replies View RelatedI am looking for some microsoft recommendation on storing images in SQL Server Vs File System. Here is what our requirement is.
Currently we are using SQL Server 2000 with a VB6(COM+) & ASP front end. Our architecture involves Log Shipping & Replication. We use log shipping for our DR site and Replication to replicate to Datawarehouse & DB2 enviornment. I have been trying to research this topic and have come up with opinion favoring both sides mostly file system but nobody seems to point any Microsoft recommendation.
With our application we are looking for Transactions, backups, Log shipping & replication with these images. I have read MS implementation of TerraServer and there they recommend storing them in SQL Server if above is required. (https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true)
Can somebody please point me to some Microsoft recommendation and what does SQL Server 2005 offers in this aspect.
Thanks
Hello --
I'm building an app that will allow users to create their own photo galleries. At this point, I'm planning on storing all photos as byte arrays in SQL server image fields.
Besides the organizational benefit, is there a space benefit to doing this? That is, if I have 1MB of .jpg's, will those same images take up less than 1MB of file space within the database?
One of the reasons I ask is that most hosting plans out there seem to offer more "normal" disk space than is allocated for the database, so I'm trying to make a best plan to accommodate what will probably end up being the biggest disk space consumer in my app (the photos, that is).
Any other recommendations re: this scenario (hosting, best practices) are appreciated.
TIA,
Eric
I want to store Images as binary data in SQL table and compare it each time with a image file I am getting. I've tried below approach but getting error:
DROP TABLE #BLOBTest
CREATE TABLE #BLOBTest
(
TestID int IDENTITY(1,1),
BLOBName varChar(50),
BLOBData varBinary(MAX)
);
[Code] ....
Error: Msg 4861, Level 16, State 1, Line 10
Cannot bulk load because the file "C:Files12656.jpg" could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105).
I am having a problem with MMSQL BLOB with VB, Sorry to say I am new in Programming using VB 6 and MSSQL and I have never touch BLOB in my live.
I just wish anyone could give me any ideal, like, white pages, or manual on how do I insert BLOB data (Images) to MSSQL 2000 database using VB 6. I need to know exspecially the VB Code and the SQL Portion if you have a store procedure code for that it will be nice.
:confused:
The URL address is for example http://backup/pics/19980.jpg
On the report when I right click on an image the URL is http://localhost/reportserver
I do not understand what is going on. Please help.
Many thanks,
Nic
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
View 6 Replies View RelatedHi all,
is it good practice to store product images in a sql db?
thanks
I have a table structure like this
EmpID LastName FirstName Emp_Picture
100| x |T |<BINARY>
200| W |W |<BINARY>
..
..
ETC
This table has 935 rows in it with Emp_Picture Blank.
How to insert the jpeg files into the Emp_Picture Column??
Do we have run the update statement for each and every employee or is there a
way to get around this problem..
Thanks in Advance
VENU
Hi,
Maybe someone knows how to get images in a SQL table?
I know that there is an image datatype, but how to use it?
Any suggestions are welcome.
Thanks!!
I want to create a catalogue such thing with stores items in a shop, some with pictures. How do I store images in sql server 2000???
Thank You
When you have a column property set to image, does it link to or does it embed the image in the table?
Davids Learning
I have a report that i've created that has a picture of the image followed by details relating to the image.
What i'm trying to do if possible is for the user to click on the image and for it to open a new window with the full sized image and then close that image and click on a image relating to another image so they could see that full sized.
Is this possible in Reporting Services? or is there a way in which i could do a similar thing?
Many Thanks
I am trying export images from my SQL server but keep getting the same error:
Error 0xc0208030: Data Flow Task: The data type for "input column "image_full" (105)" is DT_IMAGE, which is not supported. Use DT_TEXT or DT_NTEXT instead and convert the data from, or to, DT_IMAGE using the data conversion component. (SQL Server Import and Export Wizard)
I want to be able to save them locally. The data type is IMAGE. What is the best and easiest way to do this? What format should I be exporting to? Has anyone ever had this problem? Please let me know if you have any help or can point me in the right direction.
Thanks,
Josh
I am trying to create a sql database that will have a table with the following parameters. ID, Name, Picture. So i create the table and set the ID to an int the Name to an nvchar and the Picture to an image. My question is now how to i add a picture to this database. When i rightclick on the table and click show table data and attempt to add an image all i can type in is text. Thank you for your help
View 8 Replies View Relatedcan anyone tell me how to store and retrieve images from sql server....
View 6 Replies View Related Hi everyone. This is my
first post here. I did a quick search and didn't find what I as
looking for, so I'm hoping you all can help me.
I'm working on a project that will allow my company to upload images to
a database and have the website pull the images from the DB. We
are planning to incorporate some type of cacheing, however I'm
skeptical as to if it is really necessary. I imagine that I'm
certianly not the first to do this and I'm wondering if someone can let
me know if I truly should expect a performance hit, or if I shouldn't
really worry about it b/c this is a very common mantra.
Thanks all. I've been pretty vague and haven't explained all of
the details. I can write more, but I'm just looking for
generalizations right now.
Thanks,
HD
Hi,I really appreciate any help from somebody that could help me on how I can upload images in a GridView or DetailsView using ASP.NET 2.0 - VB.NETI have looked on the internet, but could not find any example....Here are my pages :ASPX-Page :
<asp:SqlDataSource ID="SqlDataSource2"
runat="server" ConnectionString="<%$ ConnectionStrings:IMMOASPNETDBConnectionString %>"
DeleteCommand="DELETE FROM aspnet_BuyGroundImages WHERE (BuyGroundID = @Original_BuyGroundID)"
InsertCommand="AddImage" SelectCommand="SELECT BuyGroundID, BuyGroundDescription, Image1, Image2, Image3, Image4, Image5, OptionOn, Sold, Price FROM aspnet_BuyGroundImages" UpdateCommand="UPDATE aspnet_BuyGroundImages SET BuyGroundDescription = @BuyGroundDescription, Image1 = @Image1, Image2 = @Image2, Image3 = @Image3, Image4 = @Image4, Image5 = @Image5, OptionOn = @OptionOn, Sold = @Sold, Price = @Price WHERE (BuyGroundID = @original_BuyGroundID)" InsertCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="Original_BuyGroundID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="BuyGroundDescription" />
<asp:Parameter Name="Image1" Type="Byte" />
<asp:Parameter Name="Image2" Type="Byte" />
<asp:Parameter Name="Image3" Type="Byte" />
<asp:Parameter Name="Image4" Type="Byte" />
<asp:Parameter Name="Image5" Type="Byte" />
<asp:Parameter Name="OptionOn" />
<asp:Parameter Name="Sold" />
<asp:Parameter Name="Price" />
<asp:Parameter Name="original_BuyGroundID" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="BuyGroundID" Type="Int32" />
<asp:Parameter Name="BuyGroundDescription" />
<asp:Parameter Name="Image1" />
<asp:Parameter Name="Image2" />
<asp:Parameter Name="Image3" />
<asp:Parameter Name="Image4" />
<asp:Parameter Name="Image5" />
<asp:Parameter Name="OptionOn" />
<asp:Parameter Name="Sold" />
<asp:Parameter Name="Price" />
<asp:Parameter Name="LastUpdated" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource>
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"
DataKeyNames="BuyGroundID" DataSourceID="SqlDataSource2" Height="50px" Width="125px" BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px" CellPadding="4" GridLines="Horizontal">
<Fields>
<asp:BoundField DataField="BuyGroundID" HeaderText="BuyGroundID" InsertVisible="False"
ReadOnly="True" SortExpression="BuyGroundID" />
<asp:BoundField DataField="BuyGroundDescription" HeaderText="BuyGroundDescription"
SortExpression="BuyGroundDescription" />
<asp:TemplateField HeaderText="Image1" SortExpression="Image1">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Image1") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:FileUpload ID="FileUpload1" runat="server" />
<!-- <input id="File1" type="file" language="javascript" runat="server" />
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Image1") %>'></asp:TextBox> -->
</EditItemTemplate>
<InsertItemTemplate>
<input id="File2" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Image1") %>'></asp:TextBox> -->
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image2" SortExpression="Image2">
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Image2") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<input id="File3" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Image2") %>'></asp:TextBox> -->
</EditItemTemplate>
<InsertItemTemplate>
<input id="File4" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Image2") %>'></asp:TextBox> -->
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image3" SortExpression="Image3">
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Image3") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<input id="File5" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Image3") %>'></asp:TextBox> -->
</EditItemTemplate>
<InsertItemTemplate>
<input id="File6" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Image3") %>'></asp:TextBox> -->
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image4" SortExpression="Image4">
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Image4") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<input id="File7" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Image4") %>'></asp:TextBox> -->
</EditItemTemplate>
<InsertItemTemplate>
<input id="File8" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Image4") %>'></asp:TextBox> -->
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image5" SortExpression="Image5">
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("Image5") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<input id="File9" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("Image5") %>'></asp:TextBox> -->
</EditItemTemplate>
<InsertItemTemplate>
<input id="File10" type="file" language="javascript" runat="server" />
<!-- <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("Image5") %>'></asp:TextBox> -->
</InsertItemTemplate>
</asp:TemplateField>
<asp:CheckBoxField DataField="OptionOn" HeaderText="OptionOn" SortExpression="OptionOn" />
<asp:CheckBoxField DataField="Sold" HeaderText="Sold" SortExpression="Sold" />
<asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" ButtonType="Button" />
</Fields>
<FooterStyle BackColor="White" ForeColor="#333333" />
<EditRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="White" ForeColor="#333333" />
<PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
</asp:DetailsView>Code behind page:
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Partial Class gronden
Inherits System.Web.UI.Page
Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdating
Try
'copy file data into record?
Dim upload As FileUpload = CType(Me.DetailsView1.FindControl("FileUpload1"), FileUpload)
If Not upload Is Nothing Then
If upload.PostedFile.FileName <> "" Then
'copy file to a byte array in memory
Dim data(upload.PostedFile.ContentLength) As Byte
upload.PostedFile.InputStream.Read(data, 0, upload.PostedFile.ContentLength)
'get filename part only
Dim filename As String
Dim tmp As Integer = upload.PostedFile.FileName.LastIndexOf("")
If tmp >= 0 Then
'strip directory part
filename = upload.PostedFile.FileName.Substring(tmp + 1, _
upload.PostedFile.FileName.Length - tmp - 1)
Else
filename = upload.PostedFile.FileName
End If
'save this to the fields
e.NewValues("Image1") = data
e.NewValues("FileName") = filename
e.NewValues("Contenttype") = upload.PostedFile.ContentType
End If
End If
Catch Ex As Exception
Response.Write(Ex.StackTrace)
End Try
End Sub
End Class