SQLite“SELECT”包装函数返回空对象
我正在使用带有Node.js和SQLite 3的Electron制作一个简单的应用程序。我的目标是用我的函数包装一个基本的SELECT
查询。但是,它无法从数据库中获取内容。
我知道我可以像这样选择一行的firstname
列:
storage.all('select firstname from users where id = 1', [], ( err, rows ) => {
if ( err ) {
throw `Error while retrieving ${q}`;
};
rows.forEach(( row ) => {
n.webContents.send( 'userName', row.firstname );
console.log( 'query output sent: ' + row.firstname );
});
});
storage
在这里是我的设置数据库。以上工作正常,并允许我将我的用户名发送到我的前端视图。
但是如果我将它包装成如下函数:
let select = ( items, db, where, sign, whereNew ) => {
// preset sql query
let q = `select ${items} from ${db}`;
if ( where && sign && whereNew ) { q += ` where ${where} ${sign} ${whereNew}`};
let arr;
console.log(q);
// perform query on the database
return storage.each(q, [], ( err, row ) => {
if ( err ) {
throw `Error while retrieving ${q}`;
};
return row[items];
});
};
然后我的控制台上的输出将是一个空的Database
对象(见下文)。
这就是我调用此函数并将其记录在控制台中的方法:
let u = select( 'firstname', 'users', 'id', '=', 1 );
console.log( 'this log ' + u );
这是控制台输出:
> [email protected] dev /Users/middleman/Documents/Programowanie/MyApps/szwaczki-react-sqlite
> concurrently "BROWSER=none npm run start" "wait-on http://localhost:3000 && electron ."
[0]
[0] > [email protected] start /Users/middleman/Documents/Programowanie/MyApps/szwaczki-react-sqlite
[0] > react-scripts start
[0]
[0] Starting the development server...
[0]
[1] Connected to the database.sqlite
[0] Compiled with warnings.
[0]
[0] ./src/app.js
[0] Line 2: 'ReactDOM' is defined but never used no-unused-vars
[0]
[0] ./src/view/button.js
[0] Line 2: 'ReactDOM' is defined but never used no-unused-vars
[0]
[0] Search for the keywords to learn more about each warning.
[0] To ignore, add // eslint-disable-next-line to the line before.
[0]
[1] select firstname from users where id = 1
[1] this log [object Database]
[1] query output sent: Mateusz
^C[0] BROWSER=none npm run start exited with code 0
[1] wait-on http://localhost:3000 && electron . exited with code 0
我究竟做错了什么?
回答如下:问题是each
函数返回一个Database
对象以允许链接,而不是回调函数的return
值(as per the documentation)。
要解决这个问题,并且由于SQLite的异步行为,您可以将SQLite API函数包装到Promise
中,等待它,然后返回要检索的值:
async function select (items, db, where, sign, whereNew) {
// preset sql query
let q = `select ${items} from ${db}`;
if (where && sign && whereNew) {q += ` where ${where} ${sign} ${whereNew}`};
console.log(q);
// perform query on the database
function fetch () {
return new Promise ((resolve, reject) => {
storage.each(q, [], ( err, row ) => {
if (err) {
reject (`Error while retrieving ${q}`);
} else {
resolve (row [items]);
}
});
});
}
try {
const result = await fetch ();
return result;
} catch (error) {
throw error;
}
}
顺便说一句,构建这样的SQL查询并不是一个好主意,因为它对SQL injection开放。您应该“准备”您的查询,例如使用Database#prepare (...)
function,它基本上确保不会执行除查询之外的任何SQL代码(通过转义任何特殊字符,命令等)。
SQLite“SELECT”包装函数返回空对象
我正在使用带有Node.js和SQLite 3的Electron制作一个简单的应用程序。我的目标是用我的函数包装一个基本的SELECT
查询。但是,它无法从数据库中获取内容。
我知道我可以像这样选择一行的firstname
列:
storage.all('select firstname from users where id = 1', [], ( err, rows ) => {
if ( err ) {
throw `Error while retrieving ${q}`;
};
rows.forEach(( row ) => {
n.webContents.send( 'userName', row.firstname );
console.log( 'query output sent: ' + row.firstname );
});
});
storage
在这里是我的设置数据库。以上工作正常,并允许我将我的用户名发送到我的前端视图。
但是如果我将它包装成如下函数:
let select = ( items, db, where, sign, whereNew ) => {
// preset sql query
let q = `select ${items} from ${db}`;
if ( where && sign && whereNew ) { q += ` where ${where} ${sign} ${whereNew}`};
let arr;
console.log(q);
// perform query on the database
return storage.each(q, [], ( err, row ) => {
if ( err ) {
throw `Error while retrieving ${q}`;
};
return row[items];
});
};
然后我的控制台上的输出将是一个空的Database
对象(见下文)。
这就是我调用此函数并将其记录在控制台中的方法:
let u = select( 'firstname', 'users', 'id', '=', 1 );
console.log( 'this log ' + u );
这是控制台输出:
> [email protected] dev /Users/middleman/Documents/Programowanie/MyApps/szwaczki-react-sqlite
> concurrently "BROWSER=none npm run start" "wait-on http://localhost:3000 && electron ."
[0]
[0] > [email protected] start /Users/middleman/Documents/Programowanie/MyApps/szwaczki-react-sqlite
[0] > react-scripts start
[0]
[0] Starting the development server...
[0]
[1] Connected to the database.sqlite
[0] Compiled with warnings.
[0]
[0] ./src/app.js
[0] Line 2: 'ReactDOM' is defined but never used no-unused-vars
[0]
[0] ./src/view/button.js
[0] Line 2: 'ReactDOM' is defined but never used no-unused-vars
[0]
[0] Search for the keywords to learn more about each warning.
[0] To ignore, add // eslint-disable-next-line to the line before.
[0]
[1] select firstname from users where id = 1
[1] this log [object Database]
[1] query output sent: Mateusz
^C[0] BROWSER=none npm run start exited with code 0
[1] wait-on http://localhost:3000 && electron . exited with code 0
我究竟做错了什么?
回答如下:问题是each
函数返回一个Database
对象以允许链接,而不是回调函数的return
值(as per the documentation)。
要解决这个问题,并且由于SQLite的异步行为,您可以将SQLite API函数包装到Promise
中,等待它,然后返回要检索的值:
async function select (items, db, where, sign, whereNew) {
// preset sql query
let q = `select ${items} from ${db}`;
if (where && sign && whereNew) {q += ` where ${where} ${sign} ${whereNew}`};
console.log(q);
// perform query on the database
function fetch () {
return new Promise ((resolve, reject) => {
storage.each(q, [], ( err, row ) => {
if (err) {
reject (`Error while retrieving ${q}`);
} else {
resolve (row [items]);
}
});
});
}
try {
const result = await fetch ();
return result;
} catch (error) {
throw error;
}
}
顺便说一句,构建这样的SQL查询并不是一个好主意,因为它对SQL injection开放。您应该“准备”您的查询,例如使用Database#prepare (...)
function,它基本上确保不会执行除查询之外的任何SQL代码(通过转义任何特殊字符,命令等)。