Problems passing binary data via Odbc PrepareStatement

I have a MaxDb database with a table, that contains binary data as Varchar/Byte ( equivalent to Ado type adVarBinary). Unfortunately I can not find the right way to pass this data in a OdbcPreparedStatement. If I bind as ODBC_TYPE_STRING, the field will contain all 0-Bytes, if a bind as ODBC_TYPE_BINARY the app will hang when the statement gets executed.

What does work, is using the BlobColumn property of DatabaseRecord, however I can not find an equivalent for ODBC_TYPEs.
I pasted some sample code below.

This is Xojo 2014R3.2 on Windows7 64-Bit.

Thanks in advance for any help in this matter,

[code] // MyTable:
// SomeField Varchar(20), Ascii, not null, primary key,
// BinField Varchar(480), Byte, not null
dim conDb as new ODBCDatabase
conDb.ScrollableCursor = True
if conDb.Connect then
if not conDb.Error then conDb.SetConnectionAttribute ODBCConstant.SQL_AUTOCOMMIT, ODBCConstant.SQL_AUTOCOMMIT_OFF
end if
if conDb.Error then
msgbox "DB Error: " + conDb.ErrorMessage
end if

dim aMem as new MemoryBlock( 480)
aMem.DoubleValue( 0 ) = 1.0
for idx as integer = 1 to 59
aMem.DoubleValue( 8 * idx ) = 2.0 ^ 0.5
next idx

dim strSql as string = "Insert into MyTable( SomeField, BinField) Values(?, ?) "
dim Stmt as ODBCPreparedStatement = conDb.Prepare( strSql )
if not conDb.Error then Stmt.BindType(0, ODBCPreparedStatement.ODBC_TYPE_STRING)
if not conDb.Error then Stmt.BindType(1, ODBCPreparedStatement.ODBC_TYPE_STRING)
if not conDb.Error then Stmt.SQLExecute( “Hello”, aMem.StringValue( 0, aMem.Size) )
if not conDb.Error then
msgbox "DB Error: " + condb.ErrorMessage
end if

strSql = “Select * from MyTable where SomeField=‘Hello’”
dim rs as recordset = conDb.SQLSelect( strSql )
if not conDb.Error and not rs.EOF then
aMem.StringValue(0, aMem.Size) = rs.Field(“BinField”).StringValue
Msgbox “Value#1=” + str(aMem.DoubleValue(0)) + “, Value#2=” + str(aMem.DoubleValue(8))
msgbox "Either no Data or DB Error: " + condb.ErrorMessage
end if


Well, maybe this post gets no answers because nobody runs MaxDb ( which by the way is a very common dbms for SAP installations). But the datatype I’m having trouble with is a common one - MySql eg. calls it VarBinary or TinyBlob (for lengths > 255/ shorter ones). Oracle has a corresponding datatype too.

From what I know, ODBC_TYPE_BINARY should be the right binding, however I can’t get it to work -the app just freezes. So I wonder whether this is a bug or whether I’m doing something wrong. In case of the latter, any working Odbc sample passing this datatype would be helpful.

Thanks, Silke

you could try MBS SQL Plugin and see if it works there.

Thanks for the reply but currently I do not need the extra functionality provided by the MBS plugin ( no GB sized blobs, none of the extra dbms). The MaxDb Odbc driver works rather well and I can work around my problem by using DatabaseRecord or Recordset.Edit.
It is just that I can not get Odbc_Type_Binary to work in a prepared statement. It could be a bug or it could be I am doing the binding wrong. This is why I was asking whether someone has done this sucessfully with any Odbc dbms. If so, I could look at the corresponding binding, if not I would file a Feedback case.

You are unlikely to get a series of “I don’t know” answers. File a feedback case and we’ll see if we can reproduce it.