title: How to SQL insert a raw/Binary field value from bytes/integer array using VBScript (or VB6)?
body: <p>Does anyone know how to pass a several bytes into a Binary (or varbinary) field using SQL and ideally in ADO (classic) &amp; VBScript (or Visual Basic 6)?</p>
  
  <p>I wish to encode 10-20 (maybe more) bytes of data and have this data stored in a SQL db field. (Its not MS-SQLSVR, but I'm hoping that a standard SQL supported format will work!)</p>
  
  <p>The bytes are available as either a string of bytes obtained via AscB/ChrB, OR, an array of 'bytes' (actually variants of type byte obtained via 'cbyte') and stored in the array.</p>
  
  <p><strong>First option: using the string format</strong> 
  I have had some (limited) success creating the SQL insert as:</p>
  
  <pre><code>x = ".&gt;?hD-&amp;91k[="    '&lt;psuedo string of chars, some unprintable
  Insert Into rawtest (byt) Values (CAST('" &amp; x &amp; "' as SQL_BINARY(12)))
  </code></pre>
  
  <p>But I am concerned that string nulls will truncate the data in the field and other non-printing control characters will get in the way of handling the data. Is there a way to avoid this?</p>
  
  <p><strong>Second Option: Byte Array</strong>
  I can put the data in a array easily enough, but cannot see how to pass to the SQL Insert statement. If I attempt to pass in 12 bytes, the insert fails due to the CAST attempting to store the data into a Integer (4bytes). If I pass in a single byte, it works, eg: </p>
  
  <pre><code>x = a(0)
  </code></pre>
  
  <p>And continues to work for 4 bytes, but fails when the Integer overflows. Also, it reorders the data </p>
  
  <p>I've attempted to use various workarounds:</p>
  
  <pre><code>Insert Into rawtest (byt) Values (CAST('12,34,45' as SQL_BINARY(12)))
  Insert Into rawtest (byt) Values (CAST(&amp;h12&amp;h34 as SQL_BINARY(12)))
  Insert Into rawtest (byt) Values (CAST(0x123456789012 as SQL_BINARY(12)))
  </code></pre>
  
  <p>I've also tried similar combinations with:</p>
  
  <pre><code>Insert Into rawtest (byt) Values (CONVERT('" &amp; x &amp; "', SQL_BINARY)
  </code></pre>
  
  <p>But these all fail!</p>
  
  <p>Ideally, I want a method, any method, that takes a small binary-array of upto 20 bytes(ideally full byte range 0-255, but could take less) and passes them thru to a plain, raw, binary SQL field. </p>
  
  <p>Ideally I need to do this in VBScript/ADO, but can take a VB6 based solution if available. I want this as 'raw' binary, I don't want to use an ascii-encoding, like Base64.</p>
  
  <p>I've googled till I'm numb and havn't not found much atall relevant to binary fields in SQL. </p>
  
  <p>Can you help? Any answers appreciated. Many thx.</p>