This is Interesting: Free Magazines for Graphics designers and webmasters
Home > Archive > Microsoft XML > February 2005 > ADO GetRows array to XML?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
ADO GetRows array to XML?
|
|
| rgutter@bctf.ca 2005-02-20, 6:34 pm |
| I need to pass a few dozen sequential records at a time from a 50K row
recordset to another process (which expects an XML file), and thought I
had the solution: just iterating through this VB-ish pseudocode
avar(0)=Rs.Bookmark
Rs.Move n
avar(1)=Rs.Bookmark
Rs.Filter = Array(avar(0),avar(1))
Rs.Save filename.xml,adPersist
Rs.MoveNext
But it turns out that the filtered Rs gets saved only if the filter is
constructed via a criteria string; bookmarks won't do.
So I'm considering Rs.GetArray(x) instead. But - is there a simple way
to create an XML file from the resultant array?
Thanks.
| |
| rgutter@bctf.ca 2005-02-21, 7:20 am |
| If anyone's interested - I took a cheap approach, constructing a new
Recordset from the x-rowed array and then persisting that Recordset as
an XML file.
| |
|
| Could you please publish it here?
Thanks
<rgutter@bctf.ca> wrote in message
news:1108981650.958855.132130@z14g2000cwz.googlegroups.com...
> If anyone's interested - I took a cheap approach, constructing a new
> Recordset from the x-rowed array and then persisting that Recordset as
> an XML file.
>
| |
| rgutter@bctf.ca 2005-02-21, 11:18 pm |
| Sure. This sub accepts the original Recordset, extracts the required
number of records and creates the XML file. The sub is meant to be
called iteratively, with varBookmark set to Null for the inital call.
Private Sub CreateXMLForPage(Rs As Recordset, _
intCurrentPage As Integer, _
intRecordsPerPage As Integer, _
varBookmark As Variant, _
strTargetXML As String)
Dim varRecord As Variant
Dim RsForXML As ADODB.Recordset
Dim strName As String
Dim intCols As Integer
Dim intRows As Integer
Dim i As Integer
Dim j As Integer
On Error Goto 0 'replace w/ error handler
If Rs.RecordCount = 0 Or Rs.EOF Then Exit Sub
If IsNull(varBookmark) Then Rs.MoveFirst 'first pass
On Error Resume Next 'to catch eof for final page if not full
varRecord = Rs.GetRows(intRecordsPerPage)
Err.Clear
On Error Goto 0 'replace w/ error handler
intCols = UBound(varRecord, 1) + 1
intRows = UBound(varRecord, 2) + 1
'Set the bookmark for the next pass
'not sure if Rs.Bookmark is valid at EOF, so do it explicitly:
If Rs.EOF Then varBookmark = Rs.EOF Else varBookmark = Rs.Bookmark
'Now construct the new recordset
Set RsForXML = New ADODB.Recordset
For j = 0 To intCols - 1 'create ecah column
'For some reason, copying Attributes isn't working,
'so just set it as updatable:
RsForXML.Fields.Append Rs.Fields(j).Name, Rs.Fields(j).Type, _
Rs.Fields(j).DefinedSize, adFldUpdatable
'Remember to set scale & precision for numeric fields!
If RsForXML.Fields(j).Type = adNumeric Then
RsForXML.Fields(j).NumericScale = Rs.Fields(j).NumericScale
RsForXML.Fields(j).Precision = Rs.Fields(j).Precision
End If
'Again, because just copying the attributes isn't working,
'explicitly set the nullable attribute
If (Rs.Fields(j).Attributes And adFldIsNullable) = _
adFldIsNullable Then
RsForXML.Fields(j).Attributes = _
RsForXML.Fields(j).Attributes Or adFldIsNullable
End If
If (Rs.Fields(j).Attributes And adFldMayBeNull) = _
adFldMayBeNull Then
RsForXML.Fields(j).Attributes = _
RsForXML.Fields(j).Attributes Or adFldMayBeNull
End If
Next j
'Now fill the recordset from the array
'Two catches here:
'(1) We should be able to fill 2 arrays and just do a
'single RsForXML.AddNew but it's failing
'(maybe related to the 2nd catch), so we build it field by field
'(2) For some reason, as soon as we open the Recordset the fields'
'Attributes are reset! This means we can't assign null values.
'Still researching...
RsForXML.Open
For i = 0 To intRows - 1
'since we cannot get AddNew via array to work:
RsForXML.AddNew
For j = 0 To intCols - 1
RsForXML.Fields(j) = varRecord(j, i)
Next j
Next i
RsForXML.UpdateBatch 'not really needed?
'Now create the XML file
On Error Resume Next
Kill strTargetXML
Err.Clear
On Error Goto 0 'replace w/ error handler
RsForXML.Save strTargetXML, adPersistXML
RsForXML.Close
Set RsForXML = Nothing
On Error GoTo 0
Exit Sub
| |
| rgutter@bctf.ca 2005-02-22, 4:20 am |
| Sure. This sub accepts the original Recordset, extracts the required
number of records and creates the XML file. The sub is meant to be
called iteratively, with varBookmark set to Null for the initial call.
Private Sub CreateXMLForPage(Rs As Recordset, _
intCurrentPage As Integer, _
intRecordsPerPage As Integer, _
varBookmark As Variant, _
strTargetXML As String)
Dim varRecord As Variant
Dim RsForXML As ADODB.Recordset
Dim strName As String
Dim intCols As Integer
Dim intRows As Integer
Dim i As Integer
Dim j As Integer
On Error Goto 0 'replace w/ error handler
If Rs.RecordCount = 0 Or Rs.EOF Then Exit Sub
If IsNull(varBookmark) Then Rs.MoveFirst 'first pass
On Error Resume Next 'to catch eof for final page if not full
varRecord = Rs.GetRows(intRecordsPerPage)
Err.Clear
On Error Goto 0 'replace w/ error handler
intCols = UBound(varRecord, 1) + 1
intRows = UBound(varRecord, 2) + 1
'Set the bookmark for the next pass
'not sure if Rs.Bookmark is valid at EOF, so do it explicitly:
If Rs.EOF Then varBookmark = Rs.EOF Else varBookmark = Rs.Bookmark
'Now construct the new recordset
Set RsForXML = New ADODB.Recordset
For j = 0 To intCols - 1 'create ecah column
'For some reason, copying Attributes isn't working,
'so just set it as updatable:
RsForXML.Fields.Append Rs.Fields(j).Name, Rs.Fields(j).Type, _
Rs.Fields(j).DefinedSize, adFldUpdatable
'Remember to set scale & precision for numeric fields!
If RsForXML.Fields(j).Type = adNumeric Then
RsForXML.Fields(j).NumericScale = Rs.Fields(j).NumericScale
RsForXML.Fields(j).Precision = Rs.Fields(j).Precision
End If
'Again, because just copying the attributes isn't working,
'explicitly set the nullable attribute
If (Rs.Fields(j).Attributes And adFldIsNullable) = _
adFldIsNullable Then
RsForXML.Fields(j).Attributes = _
RsForXML.Fields(j).Attributes Or adFldIsNullable
End If
If (Rs.Fields(j).Attributes And adFldMayBeNull) = _
adFldMayBeNull Then
RsForXML.Fields(j).Attributes = _
RsForXML.Fields(j).Attributes Or adFldMayBeNull
End If
Next j
'Now fill the recordset from the array
'We should be able to fill 2 arrays and just do a
'single RsForXML.AddNew(avarNamesOrPositions, avarValues) but
'it was failing earlier so we just build it field by field
RsForXML.Open
For i = 0 To intRows - 1
'since we couldn't get AddNew via array to work:
RsForXML.AddNew
For j = 0 To intCols - 1
RsForXML.Fields(j) = varRecord(j, i)
Next j
Next i
RsForXML.UpdateBatch 'not really needed?
'Now create the XML file
On Error Resume Next
Kill strTargetXML
Err.Clear
On Error Goto 0 'replace w/ error handler
RsForXML.Save strTargetXML, adPersistXML
RsForXML.Close
Set RsForXML = Nothing
On Error GoTo 0
Exit Sub
| |
| rgutter@bctf.ca 2005-02-22, 4:20 am |
| Sure. This sub accepts the original Recordset, extracts the required
number of records and creates the XML file. The sub is meant to be
called iteratively, with varBookmark set to Null for the initial call.
Private Sub CreateXMLForPage(Rs As Recordset, _
intCurrentPage As Integer, _
intRecordsPerPage As Integer, _
varBookmark As Variant, _
strTargetXML As String)
Dim varRecord As Variant
Dim RsForXML As ADODB.Recordset
Dim strName As String
Dim intCols As Integer
Dim intRows As Integer
Dim i As Integer
Dim j As Integer
On Error Goto 0 'replace w/ error handler
If Rs.RecordCount = 0 Or Rs.EOF Then Exit Sub
If IsNull(varBookmark) Then Rs.MoveFirst 'first pass
On Error Resume Next 'to catch eof for final page if not full
varRecord = Rs.GetRows(intRecordsPerPage)
Err.Clear
On Error Goto 0 'replace w/ error handler
intCols = UBound(varRecord, 1) + 1
intRows = UBound(varRecord, 2) + 1
'Set the bookmark for the next pass
'not sure if Rs.Bookmark is valid at EOF, so do it explicitly:
If Rs.EOF Then varBookmark = Rs.EOF Else varBookmark = Rs.Bookmark
'Now construct the new recordset
Set RsForXML = New ADODB.Recordset
For j = 0 To intCols - 1 'create each column
RsForXML.Fields.Append Rs.Fields(j).Name, Rs.Fields(j).Type, _
Rs.Fields(j).DefinedSize, Rs.Fields(j).Attributes
'Remember to set scale & precision for numeric fields!
If RsForXML.Fields(j).Type = adNumeric Then
RsForXML.Fields(j).NumericScale = Rs.Fields(j).NumericScale
RsForXML.Fields(j).Precision = Rs.Fields(j).Precision
End If
Next j
'Now fill the recordset from the array
'We should be able to fill 2 arrays and just do a
'single RsForXML.AddNew(avarNamesOrPositions, avarValues) but
'it was failing earlier so we just build it field by field
RsForXML.Open
For i = 0 To intRows - 1
'since we couldn't get AddNew via array to work:
RsForXML.AddNew
For j = 0 To intCols - 1
RsForXML.Fields(j) = varRecord(j, i)
Next j
Next i
RsForXML.UpdateBatch 'not really needed?
'Now create the XML file
On Error Resume Next
Kill strTargetXML
Err.Clear
On Error Goto 0 'replace w/ error handler
RsForXML.Save strTargetXML, adPersistXML
RsForXML.Close
Set RsForXML = Nothing
On Error GoTo 0
Exit Sub
|
|
|
| | Copyright 2003 - 2008 forum4designers.com Software forum Computer Hardware reviews |
|