How to insert value using textfield to get data in sqlite using appcelerator

Question

I beginner in using appcelerator to develop android apps. I tried to insert data using textfield to retrieve data in sqlite. but if I use example "doc_num.value" with query. I will get error "[ERROR] : V8Exception: Exception occurred at test/csv.js:61: Uncaught TypeError: Cannot read property 'value' of undefined"

var currentWin = Ti.UI.currentWindow;

var label_export = Ti.UI.createLabel({
color:'#fffdfd',
text: 'Please insert document number to export :',
textAlign: Ti.UI.TEXT_ALIGNMENT_CENTER,
top: 20, //200
width: 300,
font:{fontFamily:'Arial',fontSize:14}
});

currentWin.add(label_export);

var doc_num = Ti.UI.createTextField({
color:'#fffdfd',
top:60,
left:10,
width:'80%',
height:40,
hintText:'Document Number',
keyboardType:Ti.UI.KEYBOARD_DEFAULT,
borderStyle:Ti.UI.INPUT_BORDERSTYLE_ROUNDED
});
currentWin.add(doc_num);


// btn search
var btn_search = Ti.UI.createButton({
title:'Export CSV',
top:55, //355
right:5,
width:'15%',
height:50,
color:'green',
borderRadius:1,
font:{fontFamily:'Arial',fontWeight:'bold',fontSize:8}

});

btn_search.addEventListener('click',function(e) { 

var db = Ti.Database.install('../products.sqlite','products');
var dbName = db.execute('SELECT doc_num,barcode,    product_name,oum,rack_loc,qty FROM products WHERE doc_num = ?',doc_num.value);
while (dbName.isValidRow()) {

var doc_num = dbName.fieldByName('doc_num');    
var barcode = dbName.fieldByName('barcode');
var product_name = dbName.fieldByName('product_name');
var rack_loc = dbName.fieldByName('rack_loc');
var oum = dbName.fieldByName('oum');
var qty = dbName.fieldByName('qty');
dbName.next();
Ti.API.info(doc_num + ' ' + barcode + ' ' + product_name+' ' + rack_loc +' '+ oum +' '+qty);

}

dbName.close();    
});


currentWin.add(label_warning);

currentWin.add(btn_search);

And this my table structure that I use for my database.Database name products and tape name also products

CREATE TABLE products (id INTEGER PRIMARY KEY, doc_num VARCHAR, product_name VARCHAR, barcode VARCHAR, rack_loc VARCHAR, oum VARCHAR, qty NUMERIC)

Answer 1

You just need to use this line of code instead of yours:

var dbName = db.execute('SELECT doc_num,barcode, product_name,oum,rack_loc,qty FROM products WHERE doc_num = ?', doc_num.value);

You cannot concatenate strings in execute method, you will need to standard approach of passing parameters in SQL query using '?' sign.

Also note that you cannot do this something like this also:

var query = 'select * from products where doc_num = ' + doc_num.value;

• This query will fail in case where there are spaces in doc_num.value

So the recommended and safest way of passing values to queries is this:

var query = 'select * from products where doc_num = ? and someother_condition = ?';
db.execute(query, doc_num.value, someother_value);

db.execute method can take any number of parameters starting from 2nd parameter.