Changing the precision of fields for xml export with ado object with the 'save' method

Question

Hello,

I am working on a VB6 application that uses an ADODB.Recordset object to dump data to an .xml file with the 'save' method, like the following:

adoRecordset.Save strDst, adPersistXML

where strDst is a string describing the destination.

Now, there are some rows in the database that are floats/doubles, for some table. For example, the table TABLE contains a column MEASURE of type float.

Question: Is it possible to control the presision of MEASURE that is written to the .xml-file?

For example, if the measure is 1.23456789 is it possible to have measure="1.234" in the output file?

Idea: The ADO record set has a collection 'Fields' of type 'Field' for each record of the database. The property 'Precision' for the fields exists but is 'mostly' read-only. It is pretty unclear when it can be written by looking at the help from MSDN. Can it be used somehow?

Restriction: I do want to use the 'Save' method. I want to know if it is possible with it in order to avoid writing my own method.

Background: The aim is to transfer a huge amount of data extracted from the database over a long distance channel to another system. In that system, the data is loaded into a database for preliminary analysis. The bottlenecks are the channel and the existing application for data extraction which selects the tables to be transfers via some algorithm. The .xml files are zipped with a high compression rate but the amount is still too much so that we decided to drop some not so important data.

Side problems (referring to onedaywhen's solution) Thanks onedaywhen for you answer up to now. I use it slitly differently, but it already helped a lot (+1). The problem that appear is that most of the values are well rounded at the desired position, but when it is written to the .xml, there is a kind of binary to decimal conversion error. e.g. 3.123 becomes "3.1230000000000002".

Answer 1

The simple solution would be to round the value using SQL and return this as an additional column for the recordset. So let's assume you have a complex rounding algorithm that requires client-side code.

You can use the MSDataShape provider to 'append' a new column to your recordset, for example of type numeric (i.e. fixed precision decimal), then walk the recordset to fill the values before saving:

  Dim rs As ADODB.Recordset
  Set rs = CreateObject("ADODB.Recordset")
  With rs
    .ActiveConnection = _
    "Provider=MSDataShape;" & _
    "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Tempo\New_Jet_DB.mdb"
    .Source = _
    "SHAPE {" & _
    " SELECT ExistingField" & _
    " FROM ExistingTable" & _
    " ORDER BY ExistingField" & _
    "} APPEND NEW adNumeric(5, 4) AS NewField"
    .LockType = 4  ' adLockBatchOptimistic

    .Open

    Dim i As Long
    For i = 0 To .RecordCount - 1
      .Fields("NewField").value = Round(.Fields("ExistingField").value, 4)
      .MoveNext
    Next

    rs.Save "C:\rs.xml", adPersistXML

  End With

The rows will show as updated in the XML, of course.

Answer 2

The 'Save' method is not quite flexible. Its purpose is to save data persistently, that's all. Changing the format of values or influencing the way the data is stored is not possible (only the PersistFormat can be set).

The solution I have adopted is to post-pocess the resulting XML-file rounding all the floating point values that I am interrested in. That way I have the flexibility I need.