Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
2

I'm working on a Titanium app and have build a function to run sqlite database queries and return a result set (array).

I'll not bother going too far into Titaniums details as it shouldnt make too much difference to the issue I'm facing.

Basically any query can be executed using this function, so naturally once its executed you need an array of data which corresponds to certain database column names. This is where I'm having an issue. With the way the titanium system works, I need to push the results into a results array, like so:

var resultSet = conn.execute('SELECT * FROM some_table WHERE parent = ?', parent);
    if (resultSet.isValidRow()) {
        result = {
            id: resultSet.fieldByName('id'),
            name: resultSet.fieldByName('name'),
            parent: resultSet.fieldByName('parent'),
            order: resultSet.fieldByName('order')
        };
    }

Ok, so thats all ok. But its not dynamic enough. What happens when you want to select from a different table, with different fields. id, name, parent and order are obviously not going to work.

What I'm trying to do is take an array (which I've already got) and use the values as their names, like so:

var resultSet = conn.execute(<!-- SQL IS IN HERE -->);
var fieldCount = resultSet.fieldCount();
var fields = [];
for(i=0;i<fieldCount;i++) {
    fields.push(resultSet.fieldName(i));
};
// At this point we have an array 'fields' which contains all the field names.

var results = [];
    while (resultSet.isValidRow()) {
    results.push({
// START OF PROBLEM
        for(i=0; i < fields.length; i++)
        {
            fields[i]: resultSet.fieldByName(fields[i]);
        }
// END OF PROBLEM
    });
    resultSet.next();
};
resultSet.close();

The commented start/end above shows the problem code. I realise its wrong, however cant figure out how it should be. Lets say the field array contains 'id' and 'name', I need variables to cover the two of them, but they must be dynamic - I want to avoid having to create a new javascript function for every table.

I should point out that I'm a PHP guy and my experience in JS is very limited. I've been using this as an opportunity to learn but just cant get my head around this one! Really sorry if its not explained clearly enough and hope that a JS guru can lend a hand here!

Cheers

share|improve this question

2 Answers

up vote 4 down vote accepted

You almost got it, except you were trying to iterate through the fields array within an object literal, which is completely invalid - you can only have key-value pairs within an object literal.

var resultSet = conn.execute(<!-- SQL IS IN HERE -->);
var fieldCount = resultSet.fieldCount;
var fields = [];
for(var i = 0; i < fieldCount;i++) {
    fields.push(resultSet.fieldName(i));
};

var results = [];
while (resultSet.isValidRow()) {
    for(var i=0; i < fields.length; i++) {
       results[i] = {};
       results[i][fields[i]] = resultSet.fieldByName(fields[i]);
    }
    resultSet.next();
};
resultSet.close();
share|improve this answer
Thanks for your response Jacob - this does make much more sense than what I had! I do seem to be hitting a problem still though. The line "fields[i] : resultSet.fieldByName(fields[i])" is showing up with one of those annoying errors: "[ERROR] Script Error = Result of expression 'results[i]' [undefined] is not an object. at builder.js (line 45).". The DB is definitely populated as if I replace that line with a simple alert showing the field name, id and result it works. Any ideas what could be wrong? I did clear caches/rebuild/etc but still no luck :( – RickM Apr 16 '11 at 9:37
@RickM, Hmm, how about now? – Jacob Relkin Apr 18 '11 at 7:45
up vote 0 down vote

Try This

Ti.UI.backgroundColor= 'white';
var win= Titanium.UI.createWindow();


var db= Titanium.Database.open('MY_DB');

db.execute('CREATE TABLE IF NOT EXISTS USERS1 (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, PHONE_NUMBER, IL TEXT)');
db.execute('DELETE FROM USERS1');
db.execute('INSERT INTO USERS1(NAME, PHONE_NUMBER, IL) VALUES(?, ?, ?)', 'SALIH-1','1-SALIH', 'RIZE-1');
db.execute('INSERT INTO USERS1(NAME, PHONE_NUMBER, IL) VALUES(?, ?, ?)', 'AHMET-2','2-AHMET', 'RIZE-2');
db.execute('INSERT INTO USERS1(NAME, PHONE_NUMBER, IL) VALUES(?, ?, ?)', 'HAKAN-3','3-HAKAN', 'RIZE-3');
db.execute('INSERT INTO USERS1(NAME, PHONE_NUMBER, IL) VALUES(?, ?, ?)', 'GALIP-4','4-GALIP', 'RIZE-4');



var resultSet = db.execute('SELECT * FROM USERS1'); 
var fieldCount = resultSet.fieldCount();
var fields = [];
for(var i = 0; i < fieldCount;i++) {
    fields.push(resultSet.fieldName(i));
};

var i=0;
var results = [];
while (resultSet.isValidRow()) {
    results[i] = {};

    for (var j=0; j < fieldCount; j++) {
        results[i][fields[j]] = resultSet.fieldByName(fields[j]);
    };

    resultSet.next();
    i++;
};

// Get ALL DATA
alert(results);

// Get row 0
alert(results[0]);

// get row 0 - column Name
alert(results[0]['NAME']);

resultSet.close();

win.open();
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.