none
Data Type for saving Base64 image string into SQL database

    Question

  • Hi Team! I have image base64 string to be save into sql database. I use Varchar(MAX) data type. The problem is there are certain image string that cannot be enter (paste) into the table (due to the size) then i need to resize the image then i can paste the string into the table. How to make all image string size be able to be save in the table? Or there is limit on the image size to be save into the table? I'm using mssql 2008. Thanks a lot.
    Thursday, April 03, 2014 3:53 AM

Answers

  • Hi,
    1. Define the column using data type [varbinary](max).
    2. Here is one example to save one image into the table

    update dbo.user_tab
    set photo_file=(SELECT BulkColumn
    FROM OPENROWSET(BULK N'C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\DATA\t93.jpg', SINGLE_BLOB) AS SRC)
    where user_name='pc1';



    Many Thanks & Best Regards, Hua Min


    Thursday, April 03, 2014 4:28 AM
  • There is a limit of 2GB. Which means that the actual image size must be less, since you encode the image in Base64 for some reason.

    Now, "pasting" strings is not the normal way to store data in an SQL Server database, so the question is a little funny. Normally data is stored in a database through a program of some sort. One way is to use OPENROWSET(BULK) as HuaMin suggested. Here is a link to a simple example of how to do it from .NET:
    http://www.sommarskog.se/blobload.txt


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 03, 2014 7:26 AM

All replies