Able to initially retrieve string from varbinary but not able to retrieve it again after setting it from ascii file?

43 Views Asked by At

I have a varbinary column which I am able to retrieve with bcp to a file.

I then wish to update the varbinary column with changes I have made to said file.

I attempt to update this using T-SQL with UPDATE, OPENROWSET and BULK to import the file.

The initial retrieval with bcp gives me a file that is ASCII with CR LF.

But when I retrieve the column again after updating it (even if I don't change the file) I end up with jibberish (lot of questions marks and what not) and it's a ISO-8859 text, with very long lines, with CRLF line terminators.

Initially I do the following:

Create a test table:

sqlcmd -S .\SQLEXPRESS -d da_dbname -U SomeDatabaseUsr -P SomePassVerd  -Q 'CREATE TABLE myTable(FileName nvarchar(60), FileType nvarchar(60), Document varbinary(max))'

Store the original in myTable so we can set it back and test again:

sqlcmd -S .\SQLEXPRESS -d datastore  -U SomeDatabaseUsr -P SomePassVerd  -Q 'INSERT INTO myTable (Filename, FileType, Document) VALUES (''ME_TBL_WITH_VARBIN'', ''DataBlob'', (SELECT varbincol FROM CONTENT_DATA WHERE ContentDataID=575757))'

Initial and 2nd retrieval after update is done with:

bcp  "SELECT CONVERT(NVARCHAR(MAX), varbincol) FROM ME_TBL_WITH_VARBIN WHERE ID=57575" queryout C:\Users\me\Documents\test2.txt -USomeDatabaseUsr -PSomePassVerd -dda_dbname -S .\SQLINST -c"

Starting copy...

1 rows copied.
Network packet size (bytes): 4096

Which results in my file as expected.

And then using the same content pulled into

C:\Users\me\Documents\test2.txt 

I try to update the same column to get the same file; but I do not.

How I update the record for the test:

sqlcmd -S .\SQLEXPRESS -d da_dbname -U SomeDatabaseUsr -P SomePassVerd  -Q 'UPDATE ME_TBL_WITH_VARBIN SET varbincol = ( SELECT a.*  FROM OPENROWSET (BULK ''C:\Users\me\Documents\test2.txt'', SINGLE_BLOB) AS a) WHERE ME_TBL_WITH_VARBIN.ID=57575'

How I test it (and so far changing nothing in the above statement has worked)?

If this results in 2 records it failed; if it results in 1 it succeeded:

TEST make sure they are both the same...if the same return 1.

sqlcmd -S .\SQLEXPRESS -d da_dbname  -U SomeDatabaseUsr -P SomePassVerd  -Q 'SELECT count(*) FROM ((SELECT Document FROM myTable WHERE Filename = ''ME_TBL_WITH_VARBIN'') UNION (SELECT varbincol As Document FROM ME_TBL_WITH_VARBIN WHERE ID=575757)) AS Worked'"

I have tried several variations on "How I update the record" above such as using SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB (link) and CONVERT but nothing seems to import the item as the same binary value.

0

There are 0 best solutions below