How to save a Safe Array into SQL CE

Question

Hello:

I'm now facing a problem saving an image which is contained in a safe array into "image" datatype field in SQL CE 3.5.

Previously, this MFC program is dealing with SQL express using ADO

#import "c:\Program Files\Common Files\system\ado\msadox.dll"
#import "c:\Program Files\Common Files\system\ado\msado15.dll" no_namespace rename("EOF", "EndOfFile")

The code was like: appendchunk(safearray), and then use update() method. But I found out that ADOX doesn't support update() method, so the previous code failed.

I tried to find a way to work around it: save the content in safe array into a char *, and then save it to a CString, and then use query "update table set image=convert(varbinary, '"+CString+"') where **", since char* contains 0 in it and CString considers it as terminator, so I added "1" to char * before save it into CString. So basically, storing problem is solved. However, when I need to retrieve image from "image" field, it's the contaminated data. after image decode, I couldn't get my original image.

Therefore, I'm looking for a solution that can store a safe array in SQL CE 3.5.

Anyone has any idea? Thank you 

Answer 1

Hi JensKitchen,

Base on my research, maybe there’s no such easy solution to store a safe array in SQL CE 3.5.

Could you please try to save images to SQL CE directly? Here is a similar thread that you can take a look at it as your reference.

 

Best Regards,
Ray Chen

Answer 2

Thank you for your reply Chen.

The image in my application is from the scanner, it is coded into JPEG, and stored in a safe array. Elements in this safe array are numbers from 0-255. 

Answer 3

I worked out the problem, here is the solution:

In my situation, ADO failed so I went back to basic SQL query, now problem becomes how to convert safe array (with content 0-255 representing image) to CString. 

1. pass safe array content to unsigned char * using "SafeArrayGetElement()"

2. pass each element in unsigned char* to integer

3. append all integers together and store in a CString 

4. "UPDATE table SET column=convert(ntext,'"+CString+"' WHERE ***". Yes, I use ntext datatype to store image. 

To retrieve information from this column, reverse all the above steps. 

I guess this is really a case-to-case problem, I did find some information helping me decide why update() method doesn't work, but to solve the problem, it took me almost two weeks. 

Hope this helps.