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?

Phobe

Question is: what are you expecting? – Lighterman

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). – Watchman

@glaeran; the one who answers your question must get the +rep – Abbevillian

Answer 1

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

Triquetrous

Answer 2

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

Leaflet

Hmm, funny. The thing is that I am hacking into an existing application and all other Images are starting with – Phobe

what does the data stored look like and what is the datatype? Do you have an example? – Cureall

Answer 3

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");
    }

Get