Can you embed blob data in a script for sql server?

Question

I'm testing the retrieval of blobs by a web application.

There are some difficulties uploading blobs programmatically from the javascript code so I decided to prepopulate the database with some data instead. However I'm running into some problems with that aswell.

We have a database versioning process that expects all the schema + data for the database to be in scripts that can be run by sqlcmd.

This post seems to show how to insert blobs. However this script requires that you specify an absolute path to a file on the server.

Is there another way? We are using source control and continuous integration and so wouldn't ever really want to refer to a file in a specific place outside a given copy of a repository on one machine.

If not it seems like there are 2 options:

Take the hit and never change or delete anything from a random directory on the database server aswell. The data will need to be split between several locations. Further more we either ban blobs from production config deployment or just have to bear in mind we have to do something crazy if we ever need them - we wont be in control of the directory structure on a remote server. This probably wont be a massive problem to be fair - I can't see us wanting to ship any config in blob form really.

or

write a program that does something crazy like remotely create a temporary directory on the server and then copy the file there at the correct version and output a script with that filename in.

It doesn't really seem like having things under source control and not wanting to hardcode paths is exactly an outlandish scenario but the poor quality of database tools stopped surprising me a while ago!

Solomon Rutzky · Accepted Answer · 2014-07-15T16:55:01.673

Assuming you are referring to a field of type BINARY / VARBINARY / IMAGE, you should be able to just specify the Hex Bytes such as:

0x0012FD...

For example:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD);

You just need to get that string of hex digits from the file. If you already have such a value in the DB already, then just select that row and field in SSMS, and copy / paste the value from the cell (in "Results to Grid" mode) into your SQL script.

You can also wrap long lines using a backslash as follows:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD\
12B36D98\
D523);

If wrapping via back-slash, be sure to start each new line at the first position as the entire thing is treated as a continuous string. Hence, indenting lines that come immediately following a back-slash would then have spaces between the hex digits, which is not valid. For example:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD\
  12B36D98\
D523);

equates to:

0x0012FD  12B36D98D523

thanks exactly what I was hoping for + nice and simple – JonnyRaa Jul 16 '14 at 08:22 — JonnyRaa, Jul 16 '14 at 08:22

score 2 · Answer 2 · answered Jul 15 '14 at 18:21

If you have access to C#, here's a function that I've used that will take a binary blob and spit out a SQL script that sets a varbinary(max) variable to the contents of the blob. It will format it nicely and take into account length restrictions on SQL statements (which can be an issue with very large blobs). So basically it will output something like:

select @varname = 0x4d5a90000300000004000000ffff0000b8000000000000 +
0x0040000000000000000000000000000000000000000000000000000000000000 +
0x0000000000800000000e1fba0e00b409cd21b8014ccd21546869732070726f67 +
...
0x007365745f4d6574686f64007365745f53656e644368756e6b65640053747265;
select @varname = @varname + 0x616d004765745265717565737453747265 +
0x616d0053797374656d2e5465787400456e636f64696e6700476574456e636f64 +
...

You just have to make sure to declare the variable at the front of the script it gives you. You could build a little utility that runs this function on a file (or wherever your blobs come from) to help in creating your scripts.

public static string EncodeBinary(string variable, byte[] binary)
{
    StringBuilder result;
    int column;
    int concats;
    bool newLine;

    if (binary.Length == 0)
    {
        return "select " + variable + " = null;";
    }

    result = new StringBuilder("select ");
    result.Append(variable);
    result.Append(" = 0x");
    column = 12 + variable.Length;
    concats = 0;

    for (int i = 0; i < binary.Length; i++)
    {
        newLine = false;

        if (column > 64)
        {
            concats++;
            newLine = true;
        }

        if (newLine)
        {
            if (concats == 64)
            {
                result.Append(";\r\nselect ");
                result.Append(variable);
                result.Append(" = ");
                result.Append(variable);
                result.Append(" + 0x");
                column = 15 + variable.Length * 2;
                concats = 1;
            }
            else
            {
                result.Append(" +\r\n0x");
                column = 2;
            }
        }

        result.Append(binary[i].ToString("x2"));
        column += 2;
    }

    result.Append(";\r\n");
    return result.ToString();
}

nice - I really like this approach. Its a bit overkill for my initial needs but looks exactly the way to go if you had to do it a lot — JonnyRaa, Jul 16 '14 at 08:22

Can you embed blob data in a script for sql server?

2 Answers2