{"id":1377,"date":"2021-06-11T13:08:19","date_gmt":"2021-06-11T05:08:19","guid":{"rendered":"https:\/\/www.yohz.com\/blogs\/?p=1377"},"modified":"2022-07-07T10:27:19","modified_gmt":"2022-07-07T02:27:19","slug":"extracting-binary-data-from-a-nvarcharmax-column","status":"publish","type":"post","link":"https:\/\/www.yohz.com\/blogs\/2021\/06\/11\/extracting-binary-data-from-a-nvarcharmax-column\/","title":{"rendered":"Extracting binary data from a nvarchar(max) column"},"content":{"rendered":"<p>Recently we had a user that needed to extract PDF files stored in a SQL Server nvarchar(max) column.\u00a0 Now, you wouldn&#8217;t normally be able to store binary files correctly in a text column, but the application they used (Deacom) first encoded the PDF file to UTF-8 text, then stored the UTF-8 text into the text column.<\/p>\n<p>While <a href=\"https:\/\/www.yohz.com\/siv8_details.htm\">SQL Image Viewer<\/a> (and <a href=\"https:\/\/www.yohz.com\/sbe_details.htm\">SQL Blob Export<\/a>\/<a href=\"https:\/\/www.yohz.com\/aole_details.htm\">Access OLE Export<\/a>) could extract the UTF-8 encoded data from the table and store it in a file, the resulting PDF file cannot be opened because the data contained therein is invalid.\u00a0 So we had to provide a small utility to convert the UTF-8 encoded file back to its original contents.\u00a0 You can read more about the utility <a href=\"https:\/\/www.yohz.com\/blogs\/2021\/06\/11\/using-utf8tool\/\">here<\/a>.<\/p>\n<p>After converting the PDF files, the user then compressed the PDF files and now wanted to upload the PDF files back into the database.\u00a0 They used <a href=\"https:\/\/www.yohz.com\/sfi_overview.htm\">SQL File Import<\/a> to perform the upload, but first the PDF files had be to UTF-8 encoded again, otherwise the application (Deacom) could not open the PDF files.\u00a0 That can also be one using the above <a href=\"https:\/\/www.yohz.com\/blogs\/2021\/06\/11\/using-utf8tool\/\">utility<\/a>.<\/p>\n<p>Finally, we had one last issue where only 8 Kb of the encoded PDF file was being uploaded.\u00a0 The cause was traced to the use of the MDAC\/ODBC driver to connect to SQL Server.<\/p>\n<p>In our database products, when you connect to a SQL Server instance, the default is to let the application select the best available drivers.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1378\" src=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2021\/06\/odbc01.png\" alt=\"\" width=\"456\" height=\"205\" srcset=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2021\/06\/odbc01.png 456w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2021\/06\/odbc01-300x135.png 300w\" sizes=\"(max-width: 456px) 100vw, 456px\" \/><\/p>\n<p>By design, our products will use the SQL Server Native Client drivers if available, and only use MDAC\/ODBC drivers when the Native Client drivers are not available.\u00a0 However, MDAC\/ODBC drivers limit text fields to only 8000 bytes.<\/p>\n<p>When the user attempted to use the Native Client driver, the connection failed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1379\" src=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2021\/06\/odbc02.png\" alt=\"\" width=\"453\" height=\"211\" srcset=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2021\/06\/odbc02.png 453w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2021\/06\/odbc02-300x140.png 300w\" sizes=\"(max-width: 453px) 100vw, 453px\" \/><\/p>\n<p>Installing and using the SQL Server Native Client drivers then resolved the connection and upload issue.<\/p>\n<div class=\"fcbkbttn_buttons_block\" id=\"fcbkbttn_left\"><div class=\"fcbkbttn_like \"><fb:like href=\"https:\/\/www.yohz.com\/blogs\/2021\/06\/11\/extracting-binary-data-from-a-nvarcharmax-column\/\" action=\"like\" colorscheme=\"light\" layout=\"button\"  size=\"small\"><\/fb:like><\/div><div class=\"fb-share-button  \" data-href=\"https:\/\/www.yohz.com\/blogs\/2021\/06\/11\/extracting-binary-data-from-a-nvarcharmax-column\/\" data-type=\"button\" data-size=\"small\"><\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>Recently we had a user that needed to extract PDF files stored in a SQL Server nvarchar(max) column.\u00a0 Now, you wouldn&#8217;t normally be able to store binary files correctly in a text column, but the application they used (Deacom) first encoded the PDF file to UTF-8 text, then stored the UTF-8 text into the text [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[17,44,40,11],"tags":[18,141,48,41,12],"_links":{"self":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1377"}],"collection":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/comments?post=1377"}],"version-history":[{"count":3,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1377\/revisions"}],"predecessor-version":[{"id":1748,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1377\/revisions\/1748"}],"wp:attachment":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/media?parent=1377"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/categories?post=1377"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/tags?post=1377"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}