Storing An Image In SQL Server

Question

I need to create a procedure in SQL server that takes a web URL of an image and converts it to VARBINARY, and after that: store in a column called "personqr_Image" in table "tblPersons".

I created a procedure "getPersonQrCode" that returns a URL of a unique QR code (450x450 image), and using that URL I need to convert it to VARBINARY data type in order to store it in my SQL DB.

Unfortunately I haven't really found a solution online, maybe because I am not very familiar with the subject.

why would you want to store a URL as an image? Presumably you actually want to download the image from the URL and store it in the database? — iainc, Jun 13 '19 at 13:17
Although there exists a number of methods to invoke external tools from sqlserver https://stackoverflow.com/questions/17407338/how-can-i-make-http-request-from-sql-server most reliable method would be do it in app code. — Serg, Jun 13 '19 at 13:17
In other way, you want an SP to download the image from web and store it? Also MS-Access is not related here so please don't tag it. — Ilyes, Jun 13 '19 at 13:17

Tab Alleman · Answer 1 · 2019-06-13T13:25:52.143

1

You can't do this purely in TSQL, as it doesn't have any functions for browsing the web and handling http requests and responses. If you have to do this IN SQL Server, you'll need to write a CLR procedure.

edited Jun 13 '19 at 13:25

answered Jun 13 '19 at 13:12

Tab Alleman

31,483
7
36
52

score 0 · Answer 2 · answered Jun 13 '19 at 13:46

Here is a CLR function that will allow you to submit HTTP requests

 public class RestClient
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static string Submit(string url, string data, string contentType, string 
    method = "POST",
        string httpHeaderCredentials = "")
    {
        try
        {
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls | 
            SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

            var request = (HttpWebRequest) WebRequest.Create(url);

            //Add header credentials if required
            if (!string.IsNullOrEmpty(httpHeaderCredentials))
            {
                request.Headers.Add("Authorization: " + httpHeaderCredentials);
            }

            request.ContentType = contentType;
            request.Method = method;

            if (request.Method == "PATCH")
            {
                //http://stackoverflow.com/questions/31043195/rest-api-patch-request
                request.ServicePoint.Expect100Continue = false;
            }


            if (method == "POST" || method == "PATCH")
            {
                using (var streamWriter = new StreamWriter(request.GetRequestStream()))
                {
                    streamWriter.Write(data);
                    streamWriter.Flush();
                    streamWriter.Close();
                }
            }
            var httpResponse = request.GetResponse();


            using (var responseStream = httpResponse.GetResponseStream())
            {
                if (responseStream != null)
                {
                    using (var reader = new StreamReader(responseStream))
                    {
                        return reader.ReadToEnd().Replace("\n", string.Empty);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            if (SqlContext.Pipe != null)
            {
                SqlContext.Pipe.Send(ex.Message);
            }
        }

        return "";
    }

Is there a way to do this through MS Access instead? @Matthew Evans — Tomer Barzilay, Jun 13 '19 at 14:18

Storing An Image In SQL Server

2 Answers2