column media to xml data

[rogel]

hi!

i have a media field that will be stored as XML data.

I tried to append it in my xml?

Code: Select all

      var tableDataSet = databaseManager.getDataSetByQuery(dbServerName, tableSQL, arguments,-1)
      if (tableDataSet.getMaxRowIndex() > 0) {
         written.println("<"+tableNamesArray[indexRow]+"list>")
         for (var indexDS = 1; indexDS <= tableDataSet.getMaxRowIndex(); indexDS++) {
            written.println("<"+tableNamesArray[indexRow]+">")
            var insertSQLString = createXMLData(tableNamesArray[indexRow], tableDataSet.getRowAsArray(indexDS))
            written.println(insertSQLString)
            written.println("</"+tableNamesArray[indexRow]+">")
            written.flush();
         }
         written.println("</"+tableNamesArray[indexRow]+"list>")
      }


Code: Select all

function createXMLData(tableName, resultArray) {
   var tableObject = databaseManager.getTable(dbServerName, tableName)
   var tableFields = tableObject.getColumnNames();
   var dataString = ""
   for (var resultRowIndex = 0; resultRowIndex < resultArray.length; resultRowIndex++) {
      var rowColumnName = tableFields[resultRowIndex]
      var formattedValue = resultArray[resultRowIndex]
      if (resultArray[resultRowIndex] == null ) {
         continue;
      } else {
         var column = tableObject.getColumn(rowColumnName);
         dataString += "<" +rowColumnName+ ">" + resultArray[resultRowIndex] + "</" + rowColumnName + ">"
      }
   }
   return dataString;
}


Code: Select all

<product>
<id>1</id>
<photo>[-1,-40,-1,-32,0,16,74,70,73,70,0,1,1,1,0,96,0,96,0,0,-1,-37]</photo>
</product>


when i tried to create the sql statement from the XML data it says syntax error

Code: Select all

INSERT INTO PRODUCT(id, photo) VALUES (1,[-1,-40,-1,-32,0,16,74,70,73,70,0,1,1,1,0,96,0,96,0,0,-1,-37])

What is the proper way to place it in the xml?

[mboegem]

Hi Rogel,

you'll need to 'base64-encode' the mediadata and append it to your XML as CDATA.
I used the IT2Be cryptor plugin to do the encoding and put together a small method which I can easily call for every mediadata I need in XML.

Code: Select all

if(!(_data instanceof Array)) _data = null;
var _base64 = plugins.it2be_cryptor.Base64encode(_data);
return new XML("<![CDATA[" + _base64 + "]]>");


In the above code _data is passed as argument.

Hope this helps

[patrick]

with our XML plugin you can simply provide a foundset and it will create a XML for you. You can provide relations that will be included, calculations if needed etc. All these encoding issues are then also solved for you...

[rogel]

Hi Rogel,

you'll need to 'base64-encode' the mediadata and append it to your XML as CDATA.
I used the IT2Be cryptor plugin to do the encoding and put together a small method which I can easily call for every mediadata I need in XML.

Code: Select all

if(!(_data instanceof Array)) _data = null;
var _base64 = plugins.it2be_cryptor.Base64encode(_data);
return new XML("<![CDATA[" + _base64 + "]]>");


In the above code _data is passed as argument.

Hope this helps

thank you for the information.

How would I place in my SQL statement? It did not work when I just placed it inside single quotes.