SQL Server 2008 convert varchar to varbinary

Question

I try to import image data into a sql server 2008 db with code like this:

INSERT INTO [TAB] (ID_PHOTO,PHOTO)
VALUES(
    CAST('333EFB54-7062-E043-F088-FE0A916C0297' as uniqueidentifier),
    CONVERT(varbinary(max),'0xFFD8FFE000')
)

The string is just a dummy but when I make the insert I found something like this in the database

0x307846464438464645303030

which isn't exactly what I expected. Does anybody know what I have done wrong?

`varbinary` has a maximum size of `8000` which may not contain the whole image, you should use `image` data type instead (which can hold up to `2,147,483,647` bytes. Also saving large data of image in database is not recommended, we can save the URL instead (and make sure the URLs are some kind of permanent links). — King King, Aug 18 '14 at 12:55
@glaeran; the one who answers your question must get the +rep — Felipe Pereira, Aug 18 '14 at 13:17

score 6 · Answer 1 · answered Nov 20 '16 at 03:00

The issue here is simply that a string -- '0xFFD8FFE000' -- is being converted to VARBINARY, and so each character -- first 0, then x, then F, and so on -- is "converted" to its hex representation:

Character   --  Hex value
  0               30
  x               78
  F               46
  F               46
  D               44
  8               38
  F               46
  F               46
  E               45
  0               30
  0               30
  0               30

So, the 0xFFD8FFE000 was seen as just a string of characters, just like "this is a test", instead of as a sequence of bytes.

Fortunately, the fix is quite simple: just add a "style" value of 1 for the optional 3rd parameter to CONVERT:

SELECT CONVERT(VARBINARY(MAX), '0xFFD8FFE000', 1);
-- 0xFFD8FFE000

score 3 · Answer 2 · answered Aug 18 '14 at 12:48

3

What you are seeing is correct. You should be able to run the following to see that they conversion is happening correctly (basically, convert the varbinary value in your DB back to a VARCHAR):

SELECT CONVERT(varbinary(max),'0xFFD8FFE000')
SELECT CONVERT(varchar(100), 0x307846464438464645303030)

OR

SELECT CONVERT(varchar(100), CONVERT(varbinary(max),'0xFFD8FFE000'))

Note the lack of single quotes around the varbinary value - not needed in SQL Server

answered Aug 18 '14 at 12:48

How 'bout a Fresca

2,267
1
15
26

Hmm, funny. The thing is that I am hacking into an existing application and all other Images are starting with – Thomas Dorloff Aug 18 '14 at 13:14
what does the data stored look like and what is the datatype? Do you have an example? – How 'bout a Fresca Aug 18 '14 at 13:20

score 0 · Answer 3 · edited Mar 18 '19 at 12:51

I want to insert data into sql DB with a pic, for which I have set datatype in sql as varbinary, but following error

System.Data.SqlClient.SqlException: 'Implicit conversion from data type varchar to varbinary(max) is not allowed.

Use the CONVERT function to run this query.' Consider my code:

 private void InsertButton_Click(object sender, EventArgs e)
    {
        string grno = GRNoTextBox.Text;
        string rollno = RollNoTextBox.Text;
        string name = NameTextBox.Text;
        string fname = FatherNameTextBox.Text;
        string cno = ContactNoTextBox.Text;
        string clas = ClassComboBox.SelectedItem.ToString();
        string sec = SectionComboBox.SelectedItem.ToString();
        string picadd = ofg.FileName;

        conn.Open();
        string query = "insert into stdinfo values ('"+grno+ "','" + rollno + "','" + name + "','" + fname + "','" + clas + "','" + sec + "','" + cno + "','" + picadd + "')";
        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.ExecuteNonQuery();
        conn.Close();

        MessageBox.Show("Student info inserted into database successfully");
    }

SQL Server 2008 convert varchar to varbinary

3 Answers3