String to varbinary blob

[Steve Workings]

I have a non-Phonegap application, am storing user input in localstorage when there's no web connection. It's worked well for a couple of years, just a text string in XML format.

Now I need to add an image (.jpg) to that offline storage and then retrieval and insert to MS SQL.

Along with this, I do have an an immediate submit for those occasions when there is a connection. The photo, the photo_thumb and everything else get inserted to SQL just fine. So my basic setup is sound. In the data-bound functionality, I just bind the Image type control to the Photo (B) field, and all is well.

While I can store, retrieve, and insert the image from the localstorage string, I'm not getting the right data format for the image into the varbinary field.

Generally:

vPhoto = extract_string(rowdata, "<_PHOTO>", "</_PHOTO>")
vblob = char_to_blob(vPhoto)
args.set("Photo", vblob)

is inserting data to the varbinary field, but it's not quite right to be retrieved as an image.

I've tried everything I can think of:

vblob = base64decode(vblob)
vblob = base64decode(substr(vblob,23))
as well as simply inserting the vPhoto string from localstorage.

Nothing is storing/producing the right data to correctly store the image.

Anyone have the magic keys?

[Steve Workings]

Re: String to varbinary blob

Bumping this up. Surely there's way to do this:

1. Store the mobile device's picture (jpg image) to local storage along with other data in the XML string.

2. Include the image in the string when picked up in a callback

3. Convert the string (I presume) for proper image storage in the MS SQL varbinary field.

Thanks

[Steve Workings]

Re: String to varbinary blob

It seems that I'm so close to a solution, I think it's worth the details of what I'm doing.

To add the image data to the XML string put in localstorage, I'm doing this:

	var vele = document.getElementById('{dialog.componentname}.V.R1.PHOTO').src;
	var params = '<_PHOTO>' + vele + '</_PHOTO>'
	txt = txt.concat(params);

This appears to work well, presents data in the XML string with a bit of a header as I'd expect:

data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEASABIAAD/4gx. . .

So, when I retrieve it with a callback:

		dim vblob as b
		vblob = char_to_blob(extract_string(rowdata, "<_PHOTO>", "</_PHOTO>"))
		vblob = base64decode(substr(vblob,23))
		args.set("Photo", vblob)

this produces a value in the SQL Photo field that appears to be very consistent with the values of images recorded through the regular data binding.

Yet, the UX image field control doesn't display this as a photo - just shows a broken image icon.

I'm missing some detail, but sure don't know what that might be.

Thanks.

[Selwyn Rabins]

Re: String to varbinary blob

your code is wrong. you are not stripping off the data uri prefix correctly.

you have substr(vblob,23)

but if the data is as shown below, you can see that 23 is the wrong value.

txt = "data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEASABIAAD/4gx"
?substr(txt,23)
= ",/9j/4AAQSkZJRgABAQEASABIAAD/4gx"

you need to use 24

?substr(txt,24)
= "/9j/4AAQSkZJRgABAQEASABIAAD/4gx"

[Steve Workings]

Re: String to varbinary blob

Despite Selwyn's help, verifying that I seem to be doing things correctly, this still doesn't work. I cannot seem to store the character string that comprises the image in the XML, saved to local storage, then get it back out and create a usable image.

So in trying to make this work, I have this oddity:

Executive summary: If I copy/paste the string from the localstorage value into a text file, and allow the Callback to process that, I get an image. If I process the string within the callback - whether saved to file or just as a variable, I do not get a result that produces a useful image.
----------

To find this out:

I copied/pasted the image string from the value in localstorage, and put it into PHOTO1.TXT.

Then, I let the Xbasic process the XML string and stored the result of this value in PHOTO2.TXT, with this:

		photo = extract_string(rowdata, "<_PHOTO>", "</_PHOTO>")
		save_to_file(photo, "c:\temp\photo2.txt")

A visual comparison shows that at least the beginning and ending characters are the same.

I've picked them up with file.to_string() and compared their lengths - perfect match.

But, comparing the strings shows me they do not match. The below says they are not equal:

' Copy/pasted from localstorage console:
photo1 = file.to_string("C:\Temp\Photo1.txt")
photo1 = word(photo,2,",")

' generated from callback:
Photo2 = file.to_string("C:\Temp\Photo2.txt") 
photo2 = word(photo2,2,",")

result = "false"
if Photo1 = Photo2
	result = "true"
end if

So, how is it that the string I get from copy/paste of the localstorage value is different from that string I get by pulling it out of some XML with extract_string(rowdata, "<_PHOTO>", "</_PHOTO>") ? And, again, beginning and ending values appear identical, lengths match exactly, but something else is different and I don't know what.

[Selwyn Rabins]

Re: String to varbinary blob

i don't see how you can expect to get an answer without including a test case.

i have already sent you a test case of my own which works. so once we get your test case we can compare it with the working test case.