"Multiple-step OLE DB ... errors" on ADO PutValue

[joleary]

[VC++ 6.0, SQL Server] Cross-posted from Visual C++ Programming

After using Triple-DES encryption, I end up with a BYTE* array of encrypted binary data that I need to store in my SQL Server database table. But I cannot seem to find the right way to get it into the table.

(The code to convert the BYTE* array to BSTR below was provided by the VC++ forum. The structure of the resulting BSTR seems correct when I view it in the debugger. But, if you have any other suggestions for how to get the BYTE* array into the database other than BSTR, I'd gladly entertain them as well.)

In my example below, I have conjured an ordinary BYTE* array similar to what I might get from my encryption routine.

I get the this error when I execute the assignment on the last line of the following code:

"Multiple-step OLE DB operation generated errors. Check each OLE DB value, if available. No work was done."

CString csState = "MA";
BYTE* bt = new BYTE [256]
	for (int i = 0; i < 256; i++)  //my initialization for testing
	{
		bt[i] = i + 1;
	}
BSTR bstrBuff = SysAllocStringLen((LPOLESTR)bt,256);

pRst->Fields->GetItem("state")->Value = (_bstr_t) csState;
pRst->Fields->GetItem("data")->Value = bstrBuff;

The assignment of 'state' [varchar(2)] is OK. When csState has 3 characters, I get the same error.

A recurring theme in the forum(s) and on the web for this error around this kind of operation is:
the value is not suitable for the field to store.

The database field 'data' is declared as varchar(600). I also tried varbinary(600). I'm not sure what is not suitable about these typedefs.

So, I am at a loss for an explanation.

Once, this data is in the database, I will need to get it out and back into a BYTE* array for decryption. If you have any thoughts about that, they would also be greatly appreciated.

Thanks for any thoughts,

jim

[Krzemo]

BYTE* array to BSTR

There is some kind of missunderstanding
BYTE * array can be placed in BSTR if it is a string in it (not a binary data)

If U want to fallow BYTE* => to BSTR => "TEXT" pattern than U should encode that string to supported code base (alter it to HEX string for example). U cannot store binary data in TEXT column since database validates if all character from BSTR are valid in selected character encoding scheme.

If U want to store binary data than U should use IMAGE type database column (or parameter).Than using BSTR is not supported. U should pass variant of type Array to ADO (SAFEARRAY).

ANd to process large data (TEXT and IMAGE) in VS6 u have to access them in the special way.
See example from microsoft:

 
_bstr_t varChunk;
_bstr_t varNotes;
long lngTotalsize,
lngOffSet = 0,
lngChunkSize = 100; 
lngTotalsize = g_pRS->Fields->Item["Notes"]->ActualSize / 2 ; //Becuase of being WChar
if (lngTotalsize <= 0) return;

while (lngOffSet < lngTotalsize)
{
 varChunk = g_pRS->Fields->Item["Notes"]->GetChunk(lngChunkSize);
 varNotes = varNotes + varChunk;
 lngOffSet = lngOffSet + lngChunkSize;
}
SetDlgItemText (g_hDlg2Wnd, IDC_NOTES, varNotes);

HTH,
Krzemo.

[joleary]

Krzemo, thank you for the most help I've received on this issue.

I want to be I understand what you are saying beacuse the IMAGE type and SAFEARRAY are unfamiliar to me at present.

If I can try to restate what I think you are saying:

1. I cannot use BSTRs for binary data even if my database table field is varbinary and even though the data in BSTR looks OK in the debugger.

2. My database table field type for the binary data in my BYTE* array should be IMAGE. (Is this true even if there are no more than, say, 30 bytes of encrypted binary data?)

3. There is some way for me to convert my BYTE* array to (and from) an ADO (SAFEARRAY) and it is the SAFEARRAY that I can "put to" and "get from" the database table field.

Do I understand you correctly?

dziękuję

jim

[Krzemo]

I cannot use BSTRs for binary data even if my database table field is varbinary ...

Yes U shouldn't use it. BSTR is for strings only. It consist of string length and string data (without trailing 0x00) and U can store there anything (any byte values) as long U are not trying to use that BSTR for storing it to database. BSTR is string for ADO even when U store there a binary data.

and even though the data in BSTR looks OK in the debugger

Yes. As I said before BSTR is a simple structure that can hold any values, and it is OK until U use it for library that expects strings there.

My database table field type for the binary data in my BYTE* array should be IMAGE.

Sorry , my mistake. Yes U can use VARBINARY (or BINARY) too. Than U are free from "GetChunk" problems .But still U have to use variant variable with SAFEARRAY type (VECTOR).

There is some way for me to convert my BYTE* array to (and from) an ADO (SAFEARRAY) and it is the SAFEARRAY that I can "put to" and "get from" the database table field

Yes, U are correct.

U can also use ADO for C++ extensions to hide that conversion:
http://msdn.microsoft.com/library/de...extensions.asp

Use something like that:

 BYTE mytab[50];
ULONG Status;
...
ADO_VARIABLE_LENGTH_ENTRY2(2, adVarBinary, mytab,
sizeof(mytab), Status, TRUE)

or use SafeArray.. :
http://www.codeproject.com/database/...select=1608797

dziękuję

nie ma za co!

HTH,
Krzemo.

[joleary]

Well, Krzemo, I could not have done it without you!

I decided to go with the SAFEARRAY approach that you suggested. It was easy to adapt the sample code for my encrypted BYTE* array.

Thank you so much for your help, patience, and insight.

jim

[Krzemo]

I decided to go with the SAFEARRAY approach that you suggested.

So U can use some helper functions (if available in your compiler). See below:

SAFEARRAY* pSA = SafeArrayCreateVector(VT_UI1, 0, dwSize);
HRESULT hr;
LPVOID pBuf = NULL;
if(FAILED(SafeArrayAccessData(pSA,&pBuf))) 
{.... do something with error}
..after SafeArrayAccessData U can access data directly using pointers
... now do something with pBuf
...for example:
MyFun((BYTE *)pBuf,dwSize); 
... and U have to unlock array before sending it to COM 
SafeArrayUnaccessData(pSA); 
..and dont forget to destroy that array after all work done
if(pSA) SafeArrayDestroy(pSA);

Thank you so much for your help, patience, and insight.

You welcome.

Best regards,
Krzemo.