AdoSBin.exe Stores and Retrieves Short Binary Data with ADO in Visual C++ (282932)

MORE INFORMATION

The AdoSBin.exe Visual C++ sample demonstrates how to retrieve and store short binary data from a Microsoft SQL Server 7.0 database. The SQL Server datatypes that are used are:

• timestamp
• binary(8)
• binary(16)

The timestamp and binary(8) datatypes are retrieved into a Visual C++ large integer type (ULONGLONG) and the binary(16) datatype is retrieved into a byte array.

To run the sample, use these steps:

1. Extract the project files.
2. Make the necessary changes to the connection string to reflect your SQL Server database (the one you want to use for this sample, and the user/password information). By default, the program attempts to connect to a SQL Server server on the local computer and use the pubs database. The program then attempts to connect with the sa login account, and then creates a table named ShortBinaryTable in the specified database.
3. If you do not want to drop the table that is created prior to the termination of the program, comment out the line that drops the table towards the end of the main() function:

   	pCon->Execute(btDrop, NULL, -1);
   					

4. Compile the program.
5. If you keep the statement to drop the table, place a breakpoint before that line so that you can observe the new data in the SQL Server Query Analyzer before the table is dropped.
6. Run the program.
   
   The program creates the table, inserts a single record with some binary data by using an INSERT statement, retrieves the data with ADO, and then inserts another record with ADO. Lastly, the program drops the table.

Programming Notes

• All three fields that are to be retrieved from the table are fetched by ADO as binary data. The code checks whether the data is indeed binary by checking the type of the VARIANT. Binary data is represented in a VARIANT as a safearray of bytes, hence VT_ARRAY | VT_UI1:

  	if (val.vt == (VT_ARRAY | VT_UI1)) //only process binary data
  	...
  						

• Both the timestamp field and the binary(8) field can be interpreted as 64-bit integers. Visual C++ provides two datatypes for 64-bit integers, LONGLONG (__int64) and ULONGLONG (unsigned __int64). This sample interprets the data as ULONGLONG.
  
  When the binary data is to be converted to an integer, the order of the bytes have to be reversed because the bytes in the array are ordered with the most significant byte first. Personal computers store numbers in the big-endian format with the most significant byte last, so a function is provided to convert the data from an array of bytes to an integer called VarToULongLong(). The VarToULongLong() function uses the SafeArrayAccessData() and SafeArrayUnaccessData() functions to obtain a pointer to the data contained in the array and then loops through the bytes in the array to fill the bytes of the ULONGLONG integer.
  
  

  	for (int i=0; i < 8; i++)
  	{
  		((BYTE*) (&ullVal)) [i] = ((BYTE*) (pvData)) [7-i];
  	}
  						

• The binary(16) field is interpreted as a byte array. The VarToByteArray() function again uses the SafeArrayAccessData() and SafeArrayUnaccessData() functions to obtain a pointer to the data contained in the array but does not reverse the order of the bytes:

  	for (int i=0; i < v.parray->rgsabound[0].cElements; i++)
  	{
  		pBytes[i] = ((BYTE*) (pvData)) [i];
  	}
  						

• When inserting new data, the ULongLongToVar() and ByteArrayToVar() functions are used to create the variants that contain the safearray. A helper function named CreateSafeArrayU1() is provided to simplify the creation of the C++ SAFEARRAY object.
• The PrintULongLong() and PrintByteArray() functions use the cout stream to output the data in hexadecimal format.

The following file is available for download from the Microsoft Download Center:
Release Date: FEB-01-2001

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The AdoSBin.exe file contains the following files:

File name	Size
ShortBinary.cpp	6.49KB
ShortBinary.dsp	4.50KB
ShortBinary.dsw	545 bytes
ShortBinary.ncb	113KB
ShortBinary.opt	48.5KB
StdAfx.cpp	298 bytes
StdAfx.h	667 bytes
ShortBinary.exe	556KB

Use the adVarBinary datatype with the ADO Command object when you pass the short binary data types as parameters to a stored procedure. For additional information%1, click the article number%2 below to view the article%2 in the Microsoft Knowledge Base: