none
newbie - how to define a VarBinary parameter for SQL stored procedure from C++ client app. RRS feed

  • Question

  • In the following code snippet, all seems well except for the "acDataBuf" parameter assingment.

    Is is an array of bytes (VarBinary) of a calculatable length.

     

    My problem is in representing the array of bytes stating at variable address " &pt_ripper->flags" in a form that the sql parameter accepts.

     

     

    Code Snippet

    String ^sType = gcnew String(acType );

    String ^sPtName = gcnew String(pt_ripper->id.label );

    String ^sVECName = gcnew String(sy_rec->id.label );

    //Binary ^binData = gcnew Binary();

    myC->Parameters->AddWithValue( "@acPointType", sType );

    myC->Parameters->AddWithValue( "@acPointName", sPtName );

    myC->Parameters->AddWithValue( "@acVECName", sVECName );

    myC->Parameters->Add("@acDataBuf", SqlDbType::VarBinary, itypesize - sizeof( RECID ));

    myC->Parameters["@data_params"]->Value = &pt_ripper->flags;

    myC->Parameters->AddWithValue( "@iLen", itypesize - sizeof( RECID ) );

    myC->Parameters->AddWithValue( "@uiOffset", sizeof( RECID ) );

     

     

    Thursday, August 16, 2007 9:19 PM

Answers

  • I have narrowed the problem down a little

     

     

     

    String ^sType = gcnew String(acType );

    String ^sPtName = gcnew String(pt_ripper->id.label );

    String ^sVECName = gcnew String(sy_rec->id.label );

     

    int iBinaryLength = itypesize - sizeof( RECID );

    Byte ^binarydata = gcnew Byte[iBinaryLength];

     

    myC->Parameters->AddWithValue( "@acPointType", sType );

    myC->Parameters->AddWithValue( "@acPointName", sPtName );

    myC->Parameters->AddWithValue( "@acVECName", sVECName );

    myC->Parameters->Add("@acDataBuf", SqlDbType::VarBinary, iBinaryLength);

    myC->Parameters["@acDataBuf"]->Value = binarydata;

    myC->Parameters->AddWithValue( "@iLen", iBinaryLength );

    myC->Parameters->AddWithValue( "@uiOffset", sizeof( RECID ) );

     

     

     

    Here, "Byte ^binarydata" is declared and passed to the stored procedure properly.

     

    The problem remains that I have the address of the block of data "&pt_ripper->flags", length " iBinaryLength"  but how do i copy the bytes into "binarydata" ????????

     

     

     

    Ok - have to create a MemoryStream - assign the array of binary to the stream during initialization, then access the memorystream using a ReadStream.

     

    nothing like adding lots of overhead to do something that should be simple!!!!!

     

     

    Friday, August 17, 2007 2:08 PM

All replies

  •  

    Without fully understanding your code, I think the parameter value should be set to a byte buffer and my read of your code implies that's not what is happening. Also, it's not clear what RECID is. Could you please provide more information on what stored procedure (i.e. parameters of the stored proc) and what RECID is?

     

    Regards,

    Uwa.

    Thursday, August 16, 2007 9:42 PM
  • no problem.

     

    The stored procedure is a c# procedure defined as:

    Code Snippet

    public partial class StoredProcedures

    {

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static int vciWriteDataCS

    (

    String acPointType,

    String acPointName,

    String acVECName,

    byte[] acDataBuf,

    int iLen,

    int uiOffset

    )

     

     

     

     

     

    From the original post, a binary file is read into a byte array allocated to be the size of the file.  This file contains a series of structures one after another.

    ANYTYPE is a "union" structure such that the first x bytes of every structure in the binay file defines, amongst other things, the length of the current structure.  RECID is a common structure at the beginning of all structures in the binary file that is not written to the stored procedure.

     

     

    "buffer" is the entire contents of the binary file.

    byte *ripper = buffer;

    ANYTYPE *pt_ripper = (ANYTYPE *)ripper; // change types

    int itypesize = DB_type_size( pt_ripper );  // given a pointer to a structure, extract the length of the structure

     

     

    myC->Parameters->Add("@acDataBuf", SqlDbType::VarBinary, itypesize - sizeof( RECID ));

    myC->Parameters["@data_params"]->Value = &pt_ripper->flags;

     

    The "&pt_ripper->flags" is the address of the binary structure that is to be passed to the stored procedure as a block of binary data.

     

    The code as written does not work.  Being CRT it does not allow "Parameters['x']->Value" to be assigned to the address of a byte.  Therein lies the issue.

     

    Any thoughts?

    Friday, August 17, 2007 12:53 PM
  • I have narrowed the problem down a little

     

     

     

    String ^sType = gcnew String(acType );

    String ^sPtName = gcnew String(pt_ripper->id.label );

    String ^sVECName = gcnew String(sy_rec->id.label );

     

    int iBinaryLength = itypesize - sizeof( RECID );

    Byte ^binarydata = gcnew Byte[iBinaryLength];

     

    myC->Parameters->AddWithValue( "@acPointType", sType );

    myC->Parameters->AddWithValue( "@acPointName", sPtName );

    myC->Parameters->AddWithValue( "@acVECName", sVECName );

    myC->Parameters->Add("@acDataBuf", SqlDbType::VarBinary, iBinaryLength);

    myC->Parameters["@acDataBuf"]->Value = binarydata;

    myC->Parameters->AddWithValue( "@iLen", iBinaryLength );

    myC->Parameters->AddWithValue( "@uiOffset", sizeof( RECID ) );

     

     

     

    Here, "Byte ^binarydata" is declared and passed to the stored procedure properly.

     

    The problem remains that I have the address of the block of data "&pt_ripper->flags", length " iBinaryLength"  but how do i copy the bytes into "binarydata" ????????

     

     

     

    Ok - have to create a MemoryStream - assign the array of binary to the stream during initialization, then access the memorystream using a ReadStream.

     

    nothing like adding lots of overhead to do something that should be simple!!!!!

     

     

    Friday, August 17, 2007 2:08 PM