[JScript] Scatter (like the FoxPro function)

On a roll tonight!

Scatter and Gather are a pair of functions in FoxPro and related languages which would take a record and copy it into the memvar space, naming the memvar the same as the field name and storing the field's value. Gather did the opposite.

Now I've got that working in JScript. I hand it a recordset and it creates an object with the field names as property names and the values of each as a list attached to the property. This makes things like this possible:

var X = Scatter( oRS );
 for (var i = 0; i < X.AdvertiserParentFK.length; i++ ) {
  WScript.Echo( i, X.AdvertiserParentFK[i]);
 }

And so here's the function:

function Scatter( o ) {
  var result = {};
  var vba = new VBArray( o.GetRows() );
  var fldCount = o.Fields.Count;
  for (var i = 0; i < fldCount; i++ ) {
   result[o.Fields(i).Name] = [];
   for ( var j = vba.lbound(2); j <= vba.ubound(2); j++ ) {
    result[o.Fields(i).Name].push( vba.getItem(i,j) );
   }
  }
  return result;
 }

More to enjoy! 


© Copyright Bruce M. Axtens, 2012.

[JScript] ADODB GetRows to JScript

StackOverflow to the rescue again. Where would I be without them?

Anyway, I was looking up how to return a GetRows() call on an ADODB recordset to JScript and worked out the following from this posting about returning SafeArrays to JScript

function getRowsToMatrix( o ) {
  var vba = new VBArray( o );
  var dims = vba.dimensions();
  var result = [];
  for ( var i = vba.lbound(2); i <= vba.ubound(2); i++ ) {
   result[i] = [];
   for ( var j = vba.lbound(1); j <= vba.ubound(1); j++ ) {
    result[i].push(vba.getItem(j,i));
   }
  }
  return result;
 }

What I end up with is an array of arrays, which I can address as follows:

var X = getRowsToMatrix( oRS.GetRows() );
var recs = X.length;
var flds = X[0].length;
var lastFldLastRec = X[recs-1][flds-1]; // X is zero-based

Enjoy!


© Copyright Bruce M. Axtens, 2012

[VBScript] Please, please, no more sorting!!

After so many tries, I had thought I was done with it. Well the virtual memory limit on my machine still stands so when the shellSort encountered an array of 1.5 million records, it failed. I could have gone ahead with getting the VM size increased but decided it was better to deal with the limitation as it stood. Who knows, the next bloke may not have much virtual memory either (though, I suppose, he's more likely to have lots more.)

I handed the job over to SORT.EXE and used the Exec method to call it. Like this: Dim sSamplesFilename, sSamplesData, aSamplesData, sSampleItem sSamplesFilename = Join( Array( sReportPath, sTableName & "_DataStream.txt" ), "\" ) If isFile2( sSamplesFilename ) Then oSamples.ReadFile sSamplesFilename End If WScript.Echo "Sorting " & FormatNumber( oSamples.ByteLength / 2, 0,,,-1) & " Unicode bytes to " & BaseName( sSamplesFilename ) oSamples.WriteFile "TemporaryFile.$$$", adSaveCreateOverWrite SortFile "TemporaryFile.$$$", sSamplesFilename The actual call to SortFile is what were talking about but the oSamples stuff needs some explaining. Dim oSamples Set oSamples = New SuperContainer SuperContainer is a class based on the ADODB.Streams object. Class SuperContainer private oStream Private Sub Class_Initialize() Set oStream = CreateObject( "ADODB.Stream" ) oStream.Open End Sub Public Sub Store( sText ) oStream.WriteText sText End Sub Public Function Recall() Dim sResult oStream.Position = 0 sResult = oStream.Read( adReadAll ) Recall = sResult End Function Public Sub ReadFile( sFilename ) Dim sText Dim aText Dim sItem Dim nStart Dim nFinish If IsFile2( sFilename ) = True Then sText = Chomp( GetFile( sFilename ) ) aText = Split( sText, VbNewLine ) nStart = oStream.Position For Each sItem in aText oStream.WriteText sItem & VbNewLine Next nFinish = oStream.Position WScript.Echo FormatNumber( nFinish - nStart / 2, 0,,,-1) & " ubytes read from " & sFilename Else WScript.Echo "Cannot find " & sFilename End If End Sub Public Sub WriteFile( sFilename, nMode ) oStream.SaveToFile sFilename, nMode End Sub Private Sub Class_Terminate() oStream.Close Set oStream = Nothing End Sub Public Function ByteLength() ByteLength = oStream.Size End Function End Class Interestingly I had a lot of trouble with the ReadFromFile method in ADODB.Stream. I think this had to do with it expecting Unicode but getting ASCII. When you hand a Unicode file over to SORT.EXE, it sorts it okay but changes it into ASCII. ReadFromFile tries to interpret the file as Unicode and you end up with a mess. Thus the GetFile()/Split/For Each thing in my ReadFile method.

The code for the sorting routine is: Sub SortFile( sInputFilename, sOutputFilename ) Const sCommandline = "SORT ""%IN%"" /O ""%OUT%""" Dim oExec Dim sCommand sCommand = Subst3( sCommandline, Array( Array( "IN", sInputFilename ), Array( "OUT", sOutputFilename ) ) ) If bDebugging = True Then Info "SortFile: Exec> " & sCommand End If Set oExec = oShell.Exec( sCommand ) Do While oExec.Status <> 1 WScript.Sleep 100 Loop If bDebugging = True Then If Not oExec.StdOut.AtEndOfStream Then Info "SortFile: StdOut> " & oExec.StdOut.ReadAll End If End If End Sub I have a global in my stdFunctions.vbs called bDebugging. It's normally False but I can change it and get some useful feedback thereby.

The upshot of all this is that the sorting seems to be working ... but still not perfectly -- when I gave SORT.EXE a 63 Gigabyte file it didn't like it. But the likelihood of generating datasets that large is low.