Problem Adding Binary data with binding with CursorLocation=adUseC

[Navin Kaushik]

Adding binary data to sql server with CursorLocation=adUseClient using
CADORecordBinding does't not reflect in database. If I change
CursorLocation=adUseServer then it works fine. Don't forget this problem
comes when you bind your class.

Even I have created a smalll sample that you can use to test this problem

Just create a sample table with only two fields 1. Int Type 2 . Image Type.

Now bind the class

class CEmployeeRs : public CADORecordBinding
{
BEGIN_ADO_BINDING(CEmployeeRs)

//Column empid is the 1st field in the recordset

ADO_VARIABLE_LENGTH_ENTRY2(1, adInteger, m_id,
sizeof(m_id), l_idStatus, TRUE)

ADO_VARIABLE_LENGTH_ENTRY2(2, adLongVarBinary, m_nav,
sizeof(m_nav), l_navStatus, TRUE)

END_ADO_BINDING()

public:

int m_id;
ULONG l_idStatus;
BYTE m_nav[50];
ULONG l_navStatus;
BOOL TestFields();
void SetFldStatus();

};

_RecordsetPtr pRs=NULL;
IADORecordBinding *picRs = NULL;
CEmployeeRs emprs; //C++ class object
_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
pRs.CreateInstance(__uuidof(Recordset));
pRs->PutRefActiveConnection(m_ConnectionPtr);
pRs->CursorLocation=adUseClient;
CString strQuery="select id,nav from Table1";
pRs->Open((LPCTSTR)strQuery, vNull, adOpenKeyset, adLockOptimistic,
adCmdText);
TESTHR(pRs->QueryInterface(__uuidof(IADORecordBinding),(LPVOID*)&picRs));
//Bind the Recordset to a C++ Class here
TESTHR(picRs->BindToRecordset(&emprs));
emprs.m_id=17;
memset(emprs.m_nav,0,50);
memcpy(emprs.m_nav,"navin",5);
TESTHR(picRs->AddNew(&emprs));

You have to create the connection.

Now fire the query in query analyser as you can't see binary data directly
in sql server, you will find that id exists but binary data does't exists at
all.

Please tell me how can I overcome from this problem.

[Navin Kaushik]

Just wondering nobody is able to solve my query

[Stephen Howe]

A few things:

> CString strQuery="select id,nav from Table1";

1) Why not

"select id,nav from Table1 where 0=1";

as you are not interested in a recordset pre-populated with records you
don't intend to alter.
It is overhead.

2) Some time ago I found a bug in RecordBinding. It seems that the
combination of no-records in the Recordset and non-zero bound data in the
struct confuses the bound data and no records gets added. So I make sure
that in the constructor, all member variables are zero'ed out. Then AddNew()
works. This bug is well-known. I have no idea if this is fixed in MDAC 2.8.
It was not in 2.1, 2.5, 2.6.
That could be your problem.

3) Your code would be simpler using Microsoft's smart pointers.
See here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscexampleadowithextensions.asp
for RecordBinding and Smart Pointers. Gets rid of QueryInterface() tests.

4) In terms of your problem:
I use RecordBinding a lot but always server-sided and I have never used
adLongVarBinary.
But I know how I would investigate your problem. I would do it in stages.

Do a basic client-sided Recordset with SELECT * FROM table. Now go and dump
the Field information for the nav (type, length etc). See what ADO type has
really been picked.

Two things on the field:

If the field has a maximum of 50, then should not

BYTE m_nav[50];

be 51?

Instead of ADO_VARIABLE_LENGTH_ENTRY2(), why are you not using
ADO_VARIABLE_LENGTH_ENTRY1() or ADO_VARIABLE_LENGTH_ENTRY3()
since you need actual length stored?

These are just my thoughts. I don't know either but given some experimental
time, I could probably get it to work (and yes I wish Microsofts
documentation was better)

Stephen Howe

[Navin Kaushik]

Hi Stephen,
First of many thanks to you for replying to my query. I tried
my best on google but did't find any help. This is the first time that I
did't find something useful on google. It's 12:30 mid-night here . I read
your reply and will try on my sample application tommorrow. I have tried
various permutation-combination on my sample application. The things you have
mentioned regarding optimisation ( one of them ) are known as it is just a
basic sample.

My problem comes only when I use CursorLocation=adUseClient for
binary data using binding method.

I can send you fully functional code so that you can play with
it . I can not use Server Side cursor because of various factors , If I won't
be able to use client side cursor then response time will be increased by
manifold.

Tomorrow I will give feedback on various techniques suggested by
you.
It will be great if you can provide me your e-mail id , so that
I can send you the code if need arises but I am sure , in that case problem
will hardly take your 30 mins I know its very tough to take some times from
our busy schedules but I can assure you that it will be more than anything
else for me . I am very much curious to know why this is happening , Even How
can I send this problem to micrsoft people.

Thanks Stephen
Keep it up

[Stephen Howe]

> This is the first time that I
> did't find something useful on google. It's 12:30 mid-night here.

Hrrmmm, reverse DNS lookup makes that New Delhi, India in terms of host

> It will be great if you can provide me your e-mail id,

It is in my message headers. You have to do some translation (meant to
confuse spam engines).

> I am very much curious to know why this is happening , Even How
> can I send this problem to micrsoft people.

I think ADO classic is now considered "trailing edge technology" by
Microsoft (replaced by ADO.NET). The documentation is unlikely to be
improved.
But it does not hurt to try.

Stephen Howe

[Navin Kaushik]

Hi Stephen,
Today I tested my sample application with the suggenstion
given by you yesterday. But still it is not working. I could implemented dump
method to know type length etc If you can tell me more about this then I can
try that. Since I don't you email id so some modified code of my sample
application is given below.

void CBinaryDataTestDlg::OnButton1()
{
// TODO: Add your control notification handler code here

if (! CreateDatabaseConnection() )
{
return ;
}
try
{
_RecordsetPtr pRs=NULL;
/*IADORecordBinding *picRs = NULL; //Interface Pointer declared.(VC++
Extensions) */

CEmployeeRs emprs; //C++ class object
_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
pRs.CreateInstance(__uuidof(Recordset));
pRs->PutRefActiveConnection(m_ConnectionPtr);
pRs->CursorLocation=adUseClient;

IADORecordBindingPtr picRs(pRs);
CString strQuery="select id,nav from Table1 where 0=1";

pRs->Open((LPCTSTR)strQuery, vNull, adOpenKeyset, adLockOptimistic,
adCmdText);

// TESTHR(pRs->QueryInterface(__uuidof(IADORecordBinding),(LPVOID*)&picRs));

//Bind the Recordset to a C++ Class here
TESTHR(picRs->BindToRecordset(&emprs));

emprs.m_id=2;
memset(emprs.m_nav,0,17);
memcpy(emprs.m_nav,"111111",6);
emprs.SetFldStatus();
emprs.TestFields();
TESTHR(picRs->AddNew(&emprs));

}
catch(_com_error &e)
{
MessageBox(e.Description());
MessageBox(e.ErrorMessage());
return;
}
catch(...)
{
return;
}

}

class CEmployeeRs : public CADORecordBinding
{
BEGIN_ADO_BINDING(CEmployeeRs)

//Column empid is the 1st field in the recordset

ADO_VARIABLE_LENGTH_ENTRY2(1, adInteger, m_id,
sizeof(m_id), l_idStatus, TRUE)

ADO_VARIABLE_LENGTH_ENTRY3(2, adBinary, m_nav,
sizeof(m_nav), l_navStatus, TRUE)

END_ADO_BINDING()

public:
CEmployeeRs();
int m_id;
ULONG l_idStatus;
BYTE m_nav[17];

ULONG l_navStatus;
BOOL TestFields();
void SetFldStatus();

};