小贝子编程

如何从离子2中的SQLite选择语句处理返回



我正在使用带有SQLite存储的离子2（2.0.0-rc.2）应用程序，我遇到了一些问题，因此我在这里寻求帮助。

第一次启动应用程序时，我创建数据库（表）插入一些值。到目前为止，一切顺利。但是，当我尝试检索数据并从数据库中读取它时，我会遇到错误。

我在说： 

import {Component} from '@angular/core';
import {Platform} from 'ionic-angular';
import {StatusBar, Splashscreen, SQLite} from 'ionic-native';
import {HomePage} from '../pages/home/home';

@Component({
    template: `<ion-nav [root]="rootPage"></ion-nav>`
})
export class MyApp {
    users = [];
    rootPage = HomePage;
    constructor(platform: Platform) {
        platform.ready().then(() => {
            StatusBar.styleDefault();
            let db = new SQLite();
            console.log("open database app.compontent")
            db.openDatabase({
                name: "data.db",
                location: "default"
            }).then(() => {
                db.executeSql("CREATE TABLE IF NOT EXISTS user (iduser INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT, castleType TEXT, castleHealth INTEGER); ", {}).then((data) => {
                    console.log("USER TABLE CREATED: ", data);
                }, (error) => {
                    console.error("USER Unable to execute sql", error);
                });
                db.executeSql("CREATE TABLE IF NOT EXISTS melding (idmelding INTEGER PRIMARY KEY AUTOINCREMENT, meldingType TEXT, meldingTitel TEXT, meldingOmschrijving TEXT, ruimte INTEGER, user_iduser INTEGER, FOREIGN KEY (user_iduser) REFERENCES user(iduser)); ", {}).then((data) => {
                    console.log("MELDING TABLE CREATED: ", data);
                }, (error) => {
                    console.error("MELDING Unable to execute sql", error);
                });
                db.executeSql("CREATE TABLE IF NOT EXISTS kasteelOnderdelen (idkasteelOnderdelen INTEGER PRIMARY KEY AUTOINCREMENT, onderdeelNaam TEXT, onderdeelShort TEXT, onderdeelHealth TEXT); ", {}).then((data) => {
                    console.log("KASTEELONDERDELEN TABLE CREATED: ", data);
                }, (error) => {
                    console.error("KASTEELONDERDELEN Unable to execute sql", error);
                });
                db.executeSql("CREATE TABLE IF NOT EXISTS user_has_kasteelOnderdelen (id INTEGER PRIMARY KEY AUTOINCREMENT, user_iduser INTEGER, kasteelOnderdelen_idkasteelOnderdelen INTEGER, FOREIGN KEY (user_iduser) REFERENCES user(iduser), FOREIGN KEY (kasteelOnderdelen_idkasteelOnderdelen) REFERENCES kasteelOnderdelen(idkasteelOnderdelen));", {}).then((data) => {
                    console.log("USER_HAS_KASTEELONDERDELEN TABLES CREATED: ", data);
                }, (error) => {
                    console.error("USER_HAS_KASTEELONDERDELEN Unable to execute sql", error);
                });
                console.log('Creating database complete!');
                db.executeSql("INSERT INTO user (firstname, lastname, castletype, castleHealth) VALUES (?, ?, ?, ?)", ['Jan', 'Klaassen', 'mannelijk', '10']).then((data) => {
                    console.log("Inserted row 1: ", data);
                }, (error) => {
                    console.error("Unable to insert row 1: ", error);
                });
                db.executeSql("INSERT INTO user (firstname, lastname, castletype, castleHealth) VALUES (?, ?, ?, ?)", ['Klaas', 'Janssen', 'mannelijk', '5']).then((data) => {
                    console.log("Inserted row 2: ", data);
                }, (error) => {
                    console.error("Unable to insert row 2: ", error);
                });
                db.executeSql('SELECT * FROM user', []).then((resultSet) => {
                    if(resultSet.res.rows.length > 0) {
                        this.users = [];
                        for(let i = 0; i < resultSet.res.rows.length; i++) {
                            this.users.push({
                                "iduser": resultSet.res.rows.item(i).iduser,
                                "firstname": resultSet.res.rows.item(i).firstname,
                                "lastname": resultSet.res.rows.item(i).lastname,
                            });
                        }
                    }
                }, (error) => {
                    console.log(error);
                });
            }, (error) => {
                console.error("Unable to open database", error);
            });
            Splashscreen.hide();
        });
    }
}

错误（当我在设备上运行它时（Nexus 5）：

D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 12 : Error: Uncaught (in promise): TypeError: Cannot read property 'rows' of undefined
                                                                                         at s (file:///android_asset/www/build/polyfills.js:3:8568)
                                                                                         at file:///android_asset/www/build/polyfills.js:3:8940
                                                                                         at t.invokeTask (file:///android_asset/www/build/polyfills.js:3:14051)
                                                                                         at Object.inner.inner.fork.onInvokeTask (file:///android_asset/www/build/main.js:3:22982)
                                                                                         at t.invokeTask (file:///android_asset/www/build/polyfills.js:3:13987)
                                                                                         at e.runTask (file:///android_asset/www/build/polyfills.js:3:11411)
                                                                                         at i (file:///android_asset/www/build/polyfills.js:3:8028)
                                                                                         at u (file:///android_asset/www/build/polyfills.js:2:16275)
                                                                                         at file:///android_asset/www/build/polyfills.js:2:16399
                                                                                             at MutationObserver.r (file:///android_asset/www/build/polyfills.js:2:14841)

其他发现/注：

如果我写console.log(resultSet);退货将为[object object]就目前而言，这一切都在同一班级中，而在长距离上，它将在其自己的StorageProvider/Service中使用，但我将其全部移动到这里进行调试。（到目前为止没有成功）

有人知道什么导致错误？

预先感谢。

poerkie

更新20-12（实施技巧和建议后）：

当前代码：

import {Injectable} from '@angular/core';
import 'rxjs/add/operator/map';
import {SQLite} from 'ionic-native';
import {Platform} from 'ionic-angular';

@Injectable()
export class StorageService {
public database: SQLite;
public users;
constructor(private platform: Platform) {
    console.log(' StorageService Initialised');
    this.platform.ready().then(() => {
        this.database = new SQLite();
        this.database.openDatabase({name: "data.db", location: "default"}).then(() => {
            console.log("SUCCES Opening DB storageService");
            let s1 = this.createUserTable()
            let s2 = this.database.executeSql("CREATE TABLE IF NOT EXISTS melding (idmelding INTEGER PRIMARY KEY AUTOINCREMENT, meldingType TEXT, meldingTitel TEXT, meldingOmschrijving TEXT, ruimte INTEGER, user_iduser INTEGER, FOREIGN KEY (user_iduser) REFERENCES user(iduser)); ", {});
            return Promise.all([s1, s2]).then(() => {
                console.log('creating database complete!');
                let i1 = this.insertUser();
                let i2 = this.database.executeSql("INSERT INTO user (firstname, lastname, castletype, castleHealth) VALUES (?, ?, ?, ?)", ['Klaas', 'Janssen', 'mannelijk', '5']);
                return Promise.all([i1, i2]).then((res) => {
                    console.log('all insert querys executed', res);
                }).then(() => {
                    console.log('executing sql statement');
                    return this.database.executeSql('SELECT * FROM user', []);
                }).then((resultSet) => {
                    console.log('after then((resultset): ' + resultSet);
                    console.log(JSON.stringify(resultSet, undefined, 2));
                    if (resultSet.res.rows.length > 0) {
                        console.log('if statement true');
                        this.users = [];
                        for (let i = 0; i < resultSet.res.rows.length; i++) {
                            console.log('forloop');
                            this.users.push({
                                "iduser": resultSet.res.rows.item(i).iduser,
                                "firstname": resultSet.res.rows.item(i).firstname,
                                "lastname": resultSet.res.rows.item(i).lastname,
                            });
                        }
                        console.log('after forloop');
                    }
                }).catch((error) => {
                    console.error("db error", error);
                })
            })
        }, (error) => {
            console.log("ERROR oepening DB storageService: ", error);
        });
    });
}
public createUserTable() {
    this.database.executeSql("CREATE TABLE IF NOT EXISTS user (iduser INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT, castleType TEXT, castleHealth INTEGER); ", {}).then((data) => {
        console.log("USER TABLE CREATED: ", data);
    }, (error) => {
        console.error("USER Unable to execute sql", error);
    });
}
public insertUser() {
        this.database.executeSql("INSERT INTO user (firstname, lastname, castletype, castleHealth) VALUES (?, ?, ?, ?)", ['Jan', 'Klaassen', 'mannelijk', '10']).then((data) => {
            console.log('Insert Succesful');
        }, (error) => {
            console.error('user not inserted: ' + error);
        });
    }
}

nexus 5 outputlog：

D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 :  StorageService Initialised
I/chromium: [INFO:CONSOLE(6)] " StorageService Initialised", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : CastleService initialized
I/chromium: [INFO:CONSOLE(6)] "CastleService initialized", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 5 : SlimLabsService initialized
I/chromium: [INFO:CONSOLE(5)] "SlimLabsService initialized", source: file:///android_asset/www/build/main.js (5)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 5 : Logging in!
I/chromium: [INFO:CONSOLE(5)] "Logging in!", source: file:///android_asset/www/build/main.js (5)
12-20 07:48:04.042 796-18483/? W/InputMethodManagerService: Window already focused, ignoring focus gain of: com.android.internal.view.IInputMethodClient$Stub$Proxy@fd827af attribute=null, token = android.os.BinderProxy@7d616de
D/CordovaWebViewImpl: onPageFinished(file:///android_asset/www/index.html)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 19 : DEVICE READY FIRED AFTER
I/chromium: [INFO:CONSOLE(19)] "DEVICE READY FIRED AFTER", source: file:///android_asset/www/build/main.js (19)
I/App: WARNING: Back Button Default Behavior will be overridden.  The backbutton event will be fired!
D/SystemWebChromeClient: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js: Line 175 : OPEN database: data.db
I/chromium: [INFO:CONSOLE(175)] "OPEN database: data.db", source: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js (175)
V/SQLitePlugin: Android db implementation: built-in android.database.sqlite package
V/info: Open sqlite db: /data/user/0/com.ionicframework.cwistlev1105374/databases/data.db
D/SystemWebChromeClient: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js: Line 179 : OPEN database: data.db - OK
I/chromium: [INFO:CONSOLE(179)] "OPEN database: data.db - OK", source: file:///android_asset/www/plugins/cordova-sqlite-storage/www/SQLitePlugin.js (179)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : SUCCES Opening DB storageService
I/chromium: [INFO:CONSOLE(6)] "SUCCES Opening DB storageService", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : USER TABLE CREATED: 
I/chromium: [INFO:CONSOLE(6)] "USER TABLE CREATED: ", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : creating database complete!
I/chromium: [INFO:CONSOLE(6)] "creating database complete!", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : Insert Succesful
I/chromium: [INFO:CONSOLE(6)] "Insert Succesful", source: file:///android_asset/www/build/main.js (6)
12-20 07:48:04.629 1596-1596/? I/Keyboard.Facilitator: onFinishInput()
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : all insert querys executed
I/chromium: [INFO:CONSOLE(6)] "all insert querys executed", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : executing sql statement
I/chromium: [INFO:CONSOLE(6)] "executing sql statement", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : after then((resultset): [object Object]
I/chromium: [INFO:CONSOLE(6)] "after then((resultset): [object Object]", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : {
                                                                                             "rows": {
                                                                                               "length": 42
                                                                                             },
                                                                                             "rowsAffected": 0
                                                                                           }
I/chromium: [INFO:CONSOLE(6)] "{
                                                                                "rows": {
                                                                                  "length": 42
                                                                                },
                                                                                "rowsAffected": 0
                                                                              }", source: file:///android_asset/www/build/main.js (6)
D/SystemWebChromeClient: file:///android_asset/www/build/main.js: Line 6 : db error
I/chromium: [INFO:CONSOLE(6)] "db error", source: file:///android_asset/www/build/main.js (6)

其他信息：没有更多有关该错误的信息，或者我不知道如何检索该错误。（没有堆栈跟踪或任何东西）；为什么不想传递结果！进入用户[]？

我同意suraj rao关于异步结果。我会补充说，您可以在Promise.all（）中完成所有创建表。在"然后"语句之间，将"返回"放在查询中，这是在下一个"然后"中预期的结果。例如：

    query.then( (res) => { return query2 } 
).then( (res2) => { do something }
).catch( (err) => {...});

在您的情况下：

let db = new SQLite();
console.log("open database app.compontent")
db.openDatabase({
  name: "data.db",
  location: "default"
}).then(() => {
  let s1 = db.executeSql("CREATE TABLE IF NOT EXISTS user (iduser INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT, castleType TEXT, castleHealth INTEGER); ", {});
  let s2 = db.executeSql("CREATE TABLE IF NOT EXISTS melding (idmelding INTEGER PRIMARY KEY AUTOINCREMENT, meldingType TEXT, meldingTitel TEXT, meldingOmschrijving TEXT, ruimte INTEGER, user_iduser INTEGER, FOREIGN KEY (user_iduser) REFERENCES user(iduser)); ", {})
  let sN = db.executeSql("CREATE TABLE ....); 
   return Promise.all([s1,s2,....,sN]);
).then(
   () => {
      console.log('Creating database complete!');
      let i1 =   db.executeSql("INSERT INTO user (firstname, lastname, castletype, castleHealth) VALUES (?, ?, ?, ?)", ['Jan', 'Klaassen', 'mannelijk', '10']);
      let i2 =   db.executeSql("INSERT INTO user (firstname, lastname, castletype, castleHealth) VALUES (?, ?, ?, ?)", ['Klaas', 'Janssen', 'mannelijk', '5']);
      let iN = db.executeSql("INSERT INTO ....");
      return Promise.all([i1,i2,....,iN]);
).then(
   (res) => {
       console.log('all insert over ' , res)
    }
).then(
   () => {return  db.executeSql('SELECT * FROM user', []);}
).then(
  (resultSet) => {
        if(resultSet.res.rows.length > 0) {
          this.users = [];
          for(let i = 0; i < resultSet.res.rows.length; i++) {
            this.users.push({
               "iduser": resultSet.res.rows.item(i).iduser,
               "firstname": resultSet.res.rows.item(i).firstname,
                "lastname": resultSet.res.rows.item(i).lastname,
            });
          }
        }
   }
).catch(
   (error) => {
       console.error("db error", error);
   }
);

您使用的返回承诺的DB相关功能。then（）是异步的，含义您从创建，插入，选择可以执行的所有DB函数可能在调用一个或多个then方法之前执行。

假设您只有用户表，您需要

db.executeSql(`
    CREATE TABLE IF NOT EXISTS user (
        iduser INTEGER PRIMARY KEY AUTOINCREMENT, 
        firstname TEXT, 
        lastname TEXT, 
        castleType TEXT, 
        castleHealth INTEGER
    ); `, {})
    .then((data) => {
        console.log("USER TABLE CREATED: ", data);
        db.executeSql(`
        INSERT INTO user (
            firstname, 
            lastname, 
            castletype, 
            castleHealth
        ) VALUES (?, ?, ?, ?)`, 
        ['Jan', 'Klaassen', 'mannelijk', '10'])
        .then((data) => {
            //select here
            console.log("Inserted row 1: ", data);
        }, (error) => {
            console.error("Unable to insert row 1: ", error);
        });
    }, (error) => {
        console.error("USER Unable to execute sql", error);
    });

等等。您的ResultSet.res可能是无效的，因为您的表尚未创建。也要打印对象：console.log(JSON.stringify(resultSet,undefined,2));

希望它对您有用，请参阅以下简单实现。

import { Component } from '@angular/core';
import { NavController } from 'ionic-angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
@Component({
  selector: 'page-about',
  templateUrl: 'about.html',
  providers:[SQLite]
})
export class AboutPage {
 
  rows: any;
  data: any;
  public people: Array<Object>;
  getdata: string;
   
  constructor(public navCtrl: NavController, private sqlite: SQLite) {
  this.sqlite.create({
  name: 'data.db',
  location: 'default'
}).then((db: SQLiteObject) => {
      db.executeSql('CREATE TABLE IF NOT EXISTS supriya(name VARCHAR(32))', {})
      .then(() => console.log('Executed SQL'))
      .catch(e => console.log(e));
	  db.executeSql('INSERT INTO supriya VALUES (?)', ['Supriya']).then(() => console.log('value inserted in SQL'))
      .catch(e => console.log(e));
	 
	 db.executeSql('select * from supriya', []).then(data => {
	         console.log("select statment executed"+ data);
			 
             this.people = [];
			    if(data.rows.length > 0) {
                for(var i = 0; i < data.rows.length; i++) {
				
				this.people.push({name: data.rows.item(i).name});
				console.log(data.rows.item(i).name);
                }
            }
        }, (error) => {
            console.log("ERROR: " + JSON.stringify(error));
             
			 
           			
			})
	  })
}
}
<ion-list>
         <ion-item *ngFor="let person of people">
             {{person.name}}
         </ion-item>
     </ion-list>

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium