Best way to convert a ResultSet to an Array

Greetings,

I am developing for Android.

My app uses a database, and of course I query it to get resultsets. At the moment, I convert the resultset to an array which is returned from the database accessor function and this is working fine in general.

The reason for asking this question is because I'm using a query now that JOINs a bunch of tables together and produces quite a large number of fields. I'm wondering if there is a better way (especially from a lines of code point of view) of handling resultsets. I like arrays as they are versatile objects but converting a resultset to an array is tedious when doing it field by field.

Is there a quicker way of converting a resultset to an array (like typecasting)? Or a better way overall?

Cheers, DJ

For iOs:

var rows = db.execute(statement);
var result = [];
var cont = 0;
while (rows.isValidRow()){
    result[cont] = {};
    for (var i=0, len=rows.fieldCount(); i<len; i++){
        result[cont][rows.fieldName(i)] = rows.field(i);
    }
rows.next();
cont++;
}
rows.close();

Output will be an array of records (depending of the field names):

...
[
  {'id': 1, 'name': 'Koldo'},
  {'id': 2, 'name': 'Xarai'}
]
or
[
  {'pos': 1, 'posName': 'First', 'teamCode':'001', 'teamName':'A'},
  {'pos': 2, 'posName': 'Second', 'teamCode':'002', 'teamName':'B'}
]
...

For Android (I haven't tested, but I think it could be something like this):

var rows = db.execute(statement);
var result = [];
var numRow = 0;
while (rows.isValidRow()){
    result[contRows] = {};
    var numField = 0;
    while (rows.field(numField)) {
        result[numRow][rows.fieldName(numField)] = rows.field(numField);
        numField++;
    }
    rows.next();
    numRow++;
}
rows.close();

You can create an array and push all the elements of result set to that array, and use that where ever you need that.

Ashutosh, yes, that is the method I'm using at the moment. I'm wondering if there is a more direct way of doing it than iterating through the resultset (which is a form of an array already).

For example, a toArray() function in the resultset object would be nice. Or, even nicer, it should be possible to close the database cursor without loosing the data contained in the resultset.

It just seems that an unnecessary and inefficient step is currently required. So I'm looking for the best method.

Wrote this, tested and works well both on Android & iOS, with error handling (at least to console) for any bad queries.

function getResultsetAsArray( sqlQueryAsStr ) 
{
    var resultsetAsArray = [];
 
    try {
        var dbConnection = Ti.Database.open( dbName );
        var resultsetAsObj = dbConnection.execute( sqlQueryAsStr );
 
        var resultsetRowCountAsInt = (platform == 'android') ? resultsetAsObj.fieldCount : resultsetAsObj.fieldCount(); //known issue, fieldCount is a property on android and a method on ios
 
        while ( resultsetAsObj.isValidRow() ) 
        {               
            var rowAsObj = {};
            var rowIndexAsInt = resultsetRowCountAsInt;
            var rowCountAsInt = 0;
 
            while (rowIndexAsInt > 0) 
            {
                var fieldNameAsStr = resultsetAsObj.fieldName(rowCountAsInt);
                var fieldValueAsStr = resultsetAsObj.field(rowCountAsInt);
 
                rowAsObj[ fieldNameAsStr ] = fieldValueAsStr;   
 
                rowCountAsInt++;
                rowIndexAsInt--;
            }
 
            resultsetAsArray[ resultsetAsArray.length ] = rowAsObj;
            resultsetAsObj.next();  
        }
    }
    catch(e) 
    {
        Ti.API.info('error: ' + e.message);
    }
    finally 
    {
        resultsetAsObj.close();
        dbConnection.close();
 
        resultsetRowCountAsInt = null;
        rowAsObj = null;
        rowIndexAsInt = null;
        rowCountAsInt = null;
 
        return resultsetAsArray;
    }
 
};