Storing array of doubles as a blob using ADO
Marek
I have an array of doubles (40000 of them) that I want to store as a blob
(varbinarymax) inside a SQL Server 2005 database. My code is listed below,
but my main problem is that the call to CreateParameter fails with the error
message "Application uses a value of the wrong type for the current
operation". I have tried both adVarBinary and adLongVarBinary data type.
I've done a number of searches, but none have helped me resolve this issue.
Any help would be much appreciated.
Best regards
Marek
double blobData[RECORD_COUNT];
// Set the first element so that we know something has happened.
blobData[0] = 3.1415926539;
ADODB::_CommandPtr command;
ADODB::_ParameterPtr param;
HRESULT hr = command.CreateInstance(__uuidof(ADODB::Command));
TCHAR* sql = TEXT("INSERT INTO [BlobTable] ([ObjectKey], [Blob]) VALUES
('CFD6DC2F-52E2-4406-A4E3-B663F457EAF5', ?)");
command->CommandText = sql;
command->CommandType = ADODB::adCmdText;
_variant_t var;
// Create a SAFEARRAY.
SAFEARRAY *pSA = SafeArrayCreateVector(VT_R8, 0, sizeof(blobData));
// Copy the data to the SAFEARRAY.
double *pData;
hr = SafeArrayAccessData(pSA, (void **)&pData);
memcpy(pData, blobData, sizeof(blobData));
SafeArrayUnaccessData(pSA);
// Assign the Safe array to a variant.
var.vt = VT_ARRAY|VT_R8;
var.parray = pSA;
try
{
param = command->CreateParameter(_bstr_t("Blob"), ADODB::adLongVarBinary,
ADODB::adParamInput, sizeof(blobData), var);
}
catch (_com_error &e)
{
printf("Error:\n");
printf("Code = %08lx\n", e.Error());
printf("Code meaning = %s\n", (char*)e.ErrorMessage());
printf("Source = %s\n", (char*)e.Source());
printf("Description = %s\n", (char*)e.Description());
return 6;
}
command->Parameters->Append(param);
command->Execute(NULL, NULL, ADODB::adCmdText);
SafeArrayDestroy(pSA);
return 0;
Marek
Jialiang Ge [MSFT]
I have tested your code snippet on my side and can reproduce the error.
Although I'm currently not 100% sure about cause of the error, I figure out
a solution after a couple of hours' tests and researches:
The solution is to change all the appearances of double and VT_R8 to byte
and VT_I1. Then the code works well in my test. I doubt that
command->CreateParameter always expects a *byte* array (BLOB), instead of a
double array, when the parameter type is specified as adLongVarBinary. If
you indeed need to initialize the array as double, we can convert it to be
an byte array when we create the safearray:
===================================
double blobData[40000];
// init the blog data
int size = sizeof(blobData);
// the safe array's var type is specified as byte
SAFEARRAY *pSA = SafeArrayCreateVector(VT_I1, 0, size);
byte *pData = (byte*)blobData;
hr = SafeArrayAccessData(pSA, (void **)&pData);
memcpy(pData, blobData,size);
SafeArrayUnaccessData(pSA);
_variant_t var;
// Assign the Safe array to a variant.
var.vt = VT_ARRAY|VT_I1;
var.parray = pSA;
ADODB::_ParameterPtr spParam;
spParam = spCommand->CreateParameter(_bstr_t("Blob"),
ADODB::adLongVarBinary,
ADODB::adParamInput, size , var);
spCommand->Parameters->Append(spParam);
====================================
By the way, I notice several other problems in your code while I was
testing it. They are not directly related to the "Application uses a value
of the wrong type for the current operation" error, but they may deserve
your notice:
1.
TCHAR* sql = TEXT("INSERT INTO [BlobTable] ([ObjectKey], [Blob]) VALUES
('CFD6DC2F-52E2-4406-A4E3-B663F457EAF5', ?)");
command->CommandText = sql;
command->CommandText expects a bstr, but you are passing a LPCTSTR to it.
This may cause potential problems:
http://blogs.msdn.com/ericlippert/archive/2003/09/12/52976.aspx
2.
SAFEARRAY *pSA = SafeArrayCreateVector(VT_R8, 0, sizeof(blobData));
If I understand it rightly, you are creating a double array sized
RECORD_COUNT. Because blobData is a double array, sizeof(blobData) returns
the byte count that equals RECORD_COUNT * 8. Thus, it results in a double
array sized RECORD_COUNT * 8. This may not be what you wanted.
3.
// Copy the data to the SAFEARRAY.
double *pData;
hr = SafeArrayAccessData(pSA, (void **)&pData);
It appears that you forgot to point pData to the double array?
Please try my solution and let me know whether it works for you. If you
have any additional questions or concerns, feel free to tell me.
P.S. some additional readings:
http://support.microsoft.com/kb/190450
The problem in the KB aritcle looks the same as this one, but they are
actually different. That article results from the way that *VB* calculates
the size of an array.
Have a nice day!
Regards,
Jialiang Ge (jia...@online.microsoft.com, remove 'online.')
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msd...@microsoft.com.
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Marek
Thank you very much for that - it certainly solved the CreateParameter issue
and the other points you raised are much appreciated too (and now fixed). As
usual it has just moved the problem further on. My latest code is shown
below. The problem now is that the data stored does not match that created
using a C# application, which also needs to get this blob out. The two
records written by the C# and C++ codes repectively are:
0x0001000000FFFFFFFF01000000000000000F01000000409C000006B4D54E54FB21094...
and
0xB4D54E54FB21094...
Where the ellipses indicate a start of a large number of zeros. I assume
one is the PI value (first elelment of the array) and the second is a pointer
to it?
I hope you can see what the issue might be.
Best regards
Marek
************************************************************
The C# code that does the reading is:
BinaryFormatter binaryFormatter = null;
SqlDataReader sqlDataReader = null;
SqlCommand sqlCommand = null;
MemoryStream memoryStream = null;
double[] blobData = new double[RecordCount];
try
{
sqlCommand = new SqlCommand("SELECT [Blob] FROM
[BlobTable]", _sqlConnection);
sqlDataReader = sqlCommand.ExecuteReader();
memoryStream = new MemoryStream();
BinaryWriter binaryWriter = new BinaryWriter(memoryStream);
if (sqlDataReader.HasRows)
{
if (sqlDataReader.Read())
{
long startIndex = 0;
byte[] buffer = new byte[BufferSize];
long bytesRead = 0;
bytesRead = sqlDataReader.GetBytes(0, startIndex,
buffer, 0, (int)BufferSize);
while (bytesRead == BufferSize)
{
binaryWriter.Write(buffer);
binaryWriter.Flush();
startIndex += BufferSize;
bytesRead = sqlDataReader.GetBytes(0,
startIndex, buffer, 0, (int)BufferSize);
}
if (bytesRead > 0)
{
binaryWriter.Write(buffer, 0, (int)bytesRead - 1);
binaryWriter.Flush();
}
memoryStream.Position = 0;
blobData =
(double[])binaryFormatter.Deserialize(memoryStream);
MessageBox.Show(String.Format("First element of blob
data = {0}", blobData[0]), "", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
}
************************************************************
And the C++ code now looks like this:
DOUBLE blobData[RECORD_COUNT];
for (long loop=0;loop<RECORD_COUNT;loop++)
blobData[loop] = 0.0;
blobData[0] = 3.1415926539;
ADODB::_CommandPtr command;
ADODB::_ParameterPtr param;
HRESULT hr = command.CreateInstance(__uuidof(ADODB::Command));
command->CommandText = _bstr_t("INSERT INTO [BlobTable] ([ObjectKey],
[Blob]) VALUES ('CFD6DC2F-52E2-4406-A4E3-B663F457EAF5', ?)");
command->CommandType = ADODB::adCmdText;
command->ActiveConnection = connection.GetInterfacePtr();
_variant_t var;
// Create a SAFEARRAY and copy the data to it.
int size = sizeof(blobData);
SAFEARRAY *pSA = SafeArrayCreateVector(VT_I1, 0, size);
byte *pData = (byte*)blobData;
hr = SafeArrayAccessData(pSA, (void **)&pData);
memcpy(pData, blobData,size);
SafeArrayUnaccessData(pSA);
// Assign the Safe array to a variant.
var.vt = VT_ARRAY|VT_I1;
var.parray = pSA;
try
{
param = command->CreateParameter(_bstr_t("Blob"), ADODB::adLongVarBinary,
ADODB::adParamInput, sizeof(blobData), var);
command->Parameters->Append(param);
command->Execute(NULL, NULL, ADODB::adCmdText);
}
catch (_com_error &e)
{
printf("Error:\n");
printf("Code = %08lx\n", e.Error());
printf("Code meaning = %s\n", (char*)e.ErrorMessage());
printf("Source = %s\n", (char*)e.Source());
printf("Description = %s\n", (char*)e.Description());
return 6;
}
SafeArrayDestroy(pSA);
return 0;
Marek
I should have said that the exception:
"Binary stream '0' does not contain a valid BinaryHeader. Possible causes
are invalid stream or object version change between serialization and
deserialization."
on the line:
blobData = (double[])binaryFormatter.Deserialize(memoryStream);
brought this to my attention.
Also the binary formatter needs to be initialised at the top of the C# code:
BinaryFormatter binaryFormatter = new BinaryFormatter();
Best regards
Marek
Jialiang Ge [MSFT]
I am glad that my last reply can help you. Now the new problem is that the
records written by C# and C++ do not match, right?
I am not sure how you write the blob with C#, as the code you provided is
about reading the blob. I performed a test on my side, and I can't
reproduce the problem. The two records are the same. The following is my
testing code. It uses the Buffer.BlockCopy Method to convert double array
to byte array, and uses ADO.NET to write the blob to database.
SqlConnection con = new SqlConnection("connect string");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "INSERT INTO BlobTable (ObjectKey, Blob) VALUES
(@ObjectKey, @Blob)";
cmd.Parameters.Add(new SqlParameter("@ObjectKey", SqlDbType.NVarChar,
50)).Value = "CFD6DC2F-52E2-4406-A4E3-B663F457EAF5";
double[] blobdata = new double[4000];
for (int i = 0; i < 4000; i++)
blobdata[i] = 0.0;
blobdata[0] = 3.1415926539;
//convert double array to byte array
int length = Buffer.ByteLength(blobdata);
byte[] bytes = new byte[length];
Buffer.BlockCopy(blobdata, 0, bytes, 0, length);
cmd.Parameters.Add(new SqlParameter("@Blob", SqlDbType.VarBinary)).Value =
bytes;
cmd.ExecuteNonQuery();
con.Close();
Please try my code and let me know whether it works for you. If you have
any additional questions or concerns, feel free to tell me.
Regards,
Jialiang Ge (jia...@online.microsoft.com, remove 'online.')
Microsoft Online Community Support
=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msd...@microsoft.com.
This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
Marek
Sorry for not giving you the C# writing code - please see below. I will
take a look at your code to see if there is anything significantly different
to what I am trying to do.
I'm feeling guilty that we are moving off C++ on the C++ discussion group!
Thanks again.
Best regards
Marek
double[] blobData = new double[RecordCount];
blobData[0] = 3.1415926539;
BinaryFormatter binaryFormatter = new
BinaryFormatter();
MemoryStream memoryStream = new MemoryStream();
binaryFormatter.Serialize(memoryStream, blobData);
SqlCommand sqlCommand = new SqlCommand("INSERT INTO
[BlobTable] ([ObjectKey],[Blob]) VALUES
('CFD6DC2F-52E2-4406-A4E3-B663F457EAF5', @blob)", _sqlConnection);
sqlCommand.Parameters.AddWithValue("@blob",
memoryStream.GetBuffer());
sqlCommand.ExecuteNonQuery();
memoryStream.Close();
sqlCommand.Dispose();
// Read the data
SqlDataReader sqlDataReader = null;
try
{
sqlCommand = new SqlCommand("SELECT TOP 1 [Blob]
FROM [RiskAsBlob]", _sqlConnection);
sqlDataReader = sqlCommand.ExecuteReader();
memoryStream = new MemoryStream();
BinaryWriter binaryWriter = new
BinaryWriter(memoryStream);
if (sqlDataReader.HasRows)
{
if (sqlDataReader.Read())
{
long startIndex = 0;
byte[] buffer = new byte[BufferSize];
long bytesRead = 0;
bytesRead = sqlDataReader.GetBytes(0,
startIndex, buffer, 0, (int)BufferSize);
while (bytesRead == BufferSize)
{
binaryWriter.Write(buffer);
binaryWriter.Flush();
startIndex += BufferSize;
bytesRead =
sqlDataReader.GetBytes(0, startIndex, buffer, 0, (int)BufferSize);
}
if (bytesRead > 0)
{
binaryWriter.Write(buffer, 0,
(int)bytesRead - 1);
binaryWriter.Flush();
}
memoryStream.Position = 0;
blobData =
(double[])binaryFormatter.Deserialize(memoryStream);
}
}
}
finally
{
if (sqlDataReader != null)
{
sqlDataReader.Close();
sqlDataReader.Dispose();
}
}
Jialiang Ge [MSFT]
I can reproduce the extra bytes with your C# code. The first thought in my
mind is that BinaryFormatter adds some headers in the serialization. I
checked MSDN documentation. It says, BinaryFormatter.Serialize Method
serializes the object, or graph of objects with the specified top (root),
to the given stream. [http://msdn.microsoft.com/en-us/library/a3833y0d.aspx]
However, the document doesn't detail the specified top (root) . So I enable
.NET Framework Source Code debugging in Visual Studio 2008 (see
http://blogs.msdn.com/sburke/archive/2008/01/16/configuring-visual-studio-to
-debug-net-framework-source-code.aspx) , and found the prefix is written by
an internal method in the internal class BinaryCommonClass:
public void Write(__BinaryWriter sout)
{
majorVersion = binaryFormatterMajorVersion;
minorVersion = binaryFormatterMinorVersion;
sout.WriteByte((Byte)binaryHeaderEnum);
sout.WriteInt32(topId);
sout.WriteInt32(headerId);
sout.WriteInt32(binaryFormatterMajorVersion);
sout.WriteInt32(binaryFormatterMinorVersion);
}
From the code, we can see that it writes some additional information (like
versions) that exactly matches the extra bytes to the memory stream, and
this behavior is by design. I think that the solution is to not use
BinraryFormatter when you serialize the double array, but use
Buffer.BlockCopy to convert double array to byte array. If you use
BinaryFormatter.Serialize, please make sure that the deserialization is
always done by BinaryFormatter.Deserialize.
If you have any additional questions or concerns, please feel free to tell
me.
Have a great day!