Wednesday, March 7, 2012

Problem Using XML BASE64 encoding & SQL Server

Here is what I am trying to do.

I allow users to upload images from client side. This is the code I m using to load image into a client side xml document element

var node1 = xmlData.createElement("PHOTO");
node1.dataType = "bin.base64";
// Open stream object and read source file
adoStream.Type = 1; // 1=adTypeBinary
adoStream.Open();
adoStream.LoadFromFile(filename);

// Store file content and filename into XML nodes
node1.nodeTypedValue = adoStream.Read(-1); // -1=adReadAll
document.all("INVST_PHOTO").src = adoStream.Read(-1);
node2.nodeTypedValue = filename;

Now after that I extract this Image and insert it into Sql server using a stored procedure.

Dim ImgBuff() As Byte

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").Text

' Add null termination:
ReDim Preserve ImgBuff(0 To UBound(ImgBuff) + 2) As Byte

' Get the pointer to the string:
Dim lPtrString As Long
lPtrString = VarPtr(ImgBuff(0))

objCmd.Parameters("@.pIMG_PHOTO").AppendChunk ImgBuff().

I am Sucessfully able to store it in the database fileld type of Image. Now I am using XML: to retrieve images from the database. Here is how the SQL looks like

SELECT
1 AS TAG, NULL AS PARENT,
IMG_PHOT AS [PHOTO!1!PHOTO!ELEMENT]
FROM PHOTO
FOR XML EXPLICIT , BINARY BASE64
The problem I am runing into is that content of the IMG_PHOTO are not the same after saving and retreival.

Here is how the contents are prior to inserting into database

/9j/4AAQSkZJRgABAgAAZABkAAD/7AARRHVja3kAAQAEAAAAPAAA/+4ADkFkb2JlAGTAAAAAAf/bAIQABgQEBAUEBgUFBgkGBQYJCwgGBggLDAoKCwoKDBand here is how they look after retrieval

LwA5AGoALwA0AEEAQQBRAFMAawBaAEoAUgBnAEEAQgBBAGcAQQBBAFoAQQBCAGsAQQBBAEQALwA3AEEAQQBSAFIASABWAGoAYQAzAGsAQQB

I expected them to look same. I guess what I am doing is encoding the contents twice once I load it and once I am retreiving it from xml. Can please somebody help me out with this so that I can have the same content on both ocassion.

I am using Javascript/VB6/SQLServer 2000

I resolved it Instead of using

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").Tex

I shoudl have used it Instead of using

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").nodeTypeValue

which gives me base64 encoding data. VB string gives us Unicode data.

No comments:

Post a Comment