Sqlite_fetching


#1

Here is my code
< / >
this.sqlite.create({
name: ‘data.db’,
location: ‘default’
})
.then((db: SQLiteObject) => {

db.executeSql('create table if not exists user(name VARCHAR(32),email  VARCHAR(32),number  VARCHAR(32),password  VARCHAR(32))', {})
  .then(() => {
    console.log("created");
    db.executeSql('insert into user(name ,email,number,password) values ("'+this.name+'","'+this.email+'","'+this.number+'","'+this.password+'")', {})
    .then((data) => {
      console.log("inserted"+JSON.stringify(data));
      db.executeSql('select * from user',[])
      .then((data)=>{
        this.people=[];
        console.log("fetched"+JSON.stringify(data));
        if(data.rows.length > 0) {
            for(var i = 0; i < data.rows.length; i++) {
                // this.people.push({firstname: data.rows.item(i).firstname, lastname: data.rows.item(i).lastname});
                console.log(data.rows.item[i]);
            }
        }
      })
    })
    .catch(e => console.log("failed to insert "+JSON.stringify(e)));
  })
  .catch(e => console.log("failed to create table"+JSON.stringify(e)));

})
.catch(e => console.log(“failed to open db”+JSON.stringify(e)));
</>
select * query (data) is showing fetched{“rows”:{“length”:4},“rowsAffected”:0}
< / >
but when I console data it is showing null


#2

Please edit your post and use the </> button above the post input field to format your code or error message or wrap it in ``` (“code fences”) manually. This will make sure your text is readable and if it recognizes the programming language it also automatically adds code syntax highlighting. Thanks.


#3

Try

console.log(data.rows.item(i));

instead of

item[i]

Also, consider using Ionic Native Storage instead of SQLite native if your data has a simple structure.


#4

Hi thanks for reply if u check above code where fetched is written in that there is length and rows mentioned but not the data That I want from database


#5

if you do just console.log(data) and inspect the values it will return the rows with the object containing the extra data.

The line

console.log(data.rows.item[i]);

returns null, because the .item is a function in the object prototype, and not a array. Thats why you need to call:
console.log(data.rows.items(i));

and in theory it should return the correct data.

Some more tips:

to provide better readability you could structure your queries like this:

let insertQuery = "INSERT INTO user( name, email, number, password ) values (?, ?, ?, ?)";
let insertParams = [this.name, this.email, this.number, this.password];

db.executeSql(insertQuery, insertParams);

Always supply the params, because its cleaner and much more safer. And when you get the returned data create a class Person to store the data for further use in your app. Unfortunately dealing with SQLite is messy, and the code gets hard to maintain, would be wonderful if we had a nice ORM for it.

So if possible use Native Storage because it uses SQLite and has a much simpler and cleaner API.


#6

Hey thanks to clear my doubt will check today


#7

by the way if fetching in http, you should use something like data.map.json {
fetch the data at some point…
]

so that you get something usable in terms of array

like this.data(console.log);

and compare it with speed to some ips,

hope this helps :slight_smile:


#8

thanks francoislonic for this will work with this also