Reading VarBinary(max) from MS SQL Server

Databases
1

I have a MS SQL Server database with a varbinary(max) field that contains a PDF file. I can’t seem to read the binary data. Does anyone know what type of variable I need to read the data into? I tried putting it in a MemoryBlock but I get a “IllegalCastException” error.

I’m running Windows 7-64 bit and Xojo 2015 v2.1

– Rob

2

do you use the built in MS SQL Server Plugin coming with Xojo?
Or the MBS SQL Plugin?

3

I’m using the built in MS SQL Server Plugin.

4

Two possibilities:

  1. There’s a bug in one of the versions of the SQL SERVER drivers which causes this problem. Make sure you are using the latest one available.
  2. You may need to use the SQL command CAST to change it into a type that Xojo’s plugin can handle.
CAST(fieldname AS VARCHAR)
5

Greg -

  1. I’m using SQL Client 11.0.
  2. A VarChar field won’t work as the PDF files I’m working with are over the 8K field size limit

I’m I correct that I can just read the VarBinary field into a MemoryBlock?
This is the code I’m using:

  Dim db As New ODBCDatabase
  Dim rs As RecordSet
  Dim strSQL As String
  Dim mb as MemoryBlock
  
  db.DataSource = "Driver={SQL Server Native Client 11.0};SERVER=myServer;Database=myDatabase;Uid=myID;Pwd=myPassword;"
  If Not db.Connect Then
    Msgbox "Error connecting to MS SQL Server: " +db.ErrorMessage
  End If
  
  strSQL = "Select InvID, InvPDF From tblInvoicePDF Where InvID = 2;"
  rs = db.SQLSelect(strSQL)
  
  If db.Error Then
    MsgBox("DB Error: " + db.ErrorMessage)
    Return
  End If
  
  if rs <>Nil then
    txtInvID.Text = rs.Field("InvID").value
    mb = rs.Field("InvPDF").Value
  else
    Msgbox "No Recordset"
  end if
6

Yeah, that won’t work. When you assign a memory block like that, it’s expecting a pointer. If you know the data length ahead of time, you could use StringValue to set it:

mb.StringValue(0,length) = rs.field("in odd").StringValue

If not, you could use a binarystream and write the data to the memoryblock.