How to store and read binary data in the SQLite Database

1. store binary data

 

The BIND binary parameter interface function provided by SQLite is:

Int sqlite3_bind_blob (sqlite3_stmt *, Int, const void *, int N, void (*) (void *));

We hope to use a set of encapsulated com interfaces to encapsulate the above function as a form of COM interface.

Bindparabyindex (long index, variant Val );

You can use a safearray pointer to transmit binary data using the variant variable. It stores the address of binary data and the length of binary data in bytes.

In our COM interface, you can call the original interface as follows:

Sqlite3_bind_blob (m_pstmt, Val. parray, Val. parray-> rsground-> celement, sqlite_transient );

Construct an example to test our interface:

Byte data [] = {0x01,0x02,0x03,0x04,0x05 };

Ccomsafearray <byte> * pcsfa;

Ccomsafearraybound bound [1];

Bound [0]. setcount (5 );

Bound [0]. setlowerbound (0 );

Pcsfa = new ccomsafearray <byte> (bound, 1 );

For (long I = 0; I <(long) 5; I ++)

{

Hresult hR = pcsfa-> setat (I, data [I]);

}

_ Variant_t variant;

Variant. Vt = vt_array | vt_ui1;

Variant. parray = pcsfa-> m_psa;

Encapsulate the five-byte data into the variant variable, call the corresponding interfaces, store them in the database, and then

Call the following binary read interface to read the data to see if the read data is consistent with the stored data.

 

2. Read Binary data

 

The following two SQLite APIs are required to read binary parameters:

Const void * sqlite3_column_blob (sqlite3_stmt *, int icol );

Int sqlite3_column_bytes (sqlite3_stmt *, int icol );

Access is also implemented through the COM interface:

Getblobdata (long index, variant * pval );

How can we encapsulate the data read from the original interface into the Variant Structure? There are few references on the Internet and a lot of information is missing. We found that there is no safearray implementation solution on the Internet, however, I tried to read no binary number into the safearray structure one by one. The mentor recommended a ccomsafearray class, which successfully implements data storage.

Ccomvariant cval;

Int nlen = sqlite3_column_bytes (m_pstmt, nindex );

Const void * pcvdata = (const void *) sqlite3_column_blob (m_pstmt, nindex );

Byte * pdata = new byte [nlen];

Memcpy (pdata, pcvdata, nlen );

Ccomsafearray <byte> * pcsfa;

Ccomsafearraybound bound [1];

Bound [0]. setcount (nlen );

Bound [0]. setlowerbound (0 );

Pcsfa = new ccomsafearray <byte> (bound, 1 );

For (long I = 0; I <(long) nlen; I ++)

{

Hresult hR = pcsfa-> setat (I, pdata [I]);

}

Cval = pcsfa-> m_psa;

Cval. Vt = vt_array | vt_ui1;

Delete pdata;

Cval. Detach (pval );

OK, now you can use the followingCodeTo test whether all binary data is read successfully. The test code is as follows:

_ Variant_t val;

Val = getblobdata (nindex); // nindex indicates the index value of BLOB data.

Byte Buf [5];

If (Val. Vt = (vt_ui1 | vt_array ))

{

For (long Index = 0; index <5; index ++)

{

: Safearraygetelement (Val. parray, & Index, BUF + index );

}

}

For (Int J = 0; j <5; j ++)

{

Cout <"0x"

}