英文:
How to correctly execute this promise based SQL Query?
问题
抱歉，我只会翻译文本，不会执行代码。以下是您提供的文本的翻译：
我正在使用MySQL2将我的Node.js应用程序与MySQL数据库连接。不幸的是，尝试执行一些基于Promise的预处理语句时，我无法设置一个合适的函数，无论在记录输入后是否成功返回，或在出现问题时抛出错误。
如何修复下面的代码？
// 连接设置
const connection = mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_DATABASE,
port: process.env.DB_PORT
})
// 基于Promise的SQL预处理语句
db.pps = ({ query, variables, error }) => {
return new Promise((resolve, reject) => {
connection.execute(query, variables, (err, results) => {
if (err) {
console.log(`错误：${error} \n ${err.sqlMessage}`)
return reject(err)
}
return resolve(results)
})
})
}
// 注册
auth.signup = (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
db.promise({ query, variables }, (err, result) => {
if (err) {
res.status(400)
}
res.status(200)
})
}
英文:
I'm using MySQL2 to connect my Node.js app with a MySQL Database.
Unfortunately trying to perform some promise based prepared statements I just can't get a proper function setup that either returns successfully after entering the record or to throw an error whenever something goes wrong.
Any ideas on how to fix the code below?
// Connection Settings
const connection = mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_DATABASE,
port: process.env.DB_PORT
})
// Promise based SQL Prepared Statement
db.pps = ({ query, variables, error }) => {
return new Promise((resolve, reject) => {
connection.execute(query, variables, (err, results) => {
if (err) {
console.log(`Error: ${error} \n ${err.sqlMessage}`)
return reject(err)
}
return resolve(results)
})
})
}
// Sign Up
auth.signup = (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
db.promise({ query, variables }, (err, result) => {
if (err) {
res.status(400)
}
res.status(200)
})
}
</details>
# 答案1
**得分**: 1
你可以像下面这样使用预编译语句查询函数。
如果你不是在函数内部使用这个函数：
```javascript
auth.signup = (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
db.pps({ query, variables })
.then(result => {
res.status(200)
})
.catch( error => {
res.status(400)
});
}
```
或者使用async/await：
```javascript
auth.signup = async (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
try {
await db.pps({ query, variables });
res.status(200)
} catch (err) {
res.status(400)
}
}
```
<details>
<summary>英文:</summary>
you can use the prepared statement query function like below.
If you are not using this inside a function
```
auth.signup = (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
db.pps({ query, variables })
.then(result => {
res.status(200)
})
.catch( error => {
res.status(400)
});
}
```
Or use async await
```
auth.signup = async (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
try {
await db.pps({ query, variables });
res.status(200)
} catch (err) {
res.status(400)
}
}
```
</details>
# 答案2
**得分**: 1
1. 移除错误参数
```javascript
db.pps = ({ query, variables }) => {
```
2. 更改注册代码如下
```javascript
auth.signup = (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
db.pps({ query, variables }).then(response => {
// 处理用户信息
})
.catch(err => {
// 处理错误
})
}
```
<details>
<summary>英文:</summary>
You have to do 2 changes:
**1.Remove error param**
db.pps = ({ query, variables}) => {
**2. Change the SignUp code as below**
auth.signup = (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
db.pps({ query, variables }).then(response => {
// Do stuff with users
})
.catch(err => {
// handle errors
})
</details>
# 答案3
**得分**: 1
```javascript
function pps(查询, 变量){
return new Promise((解决, 拒绝) => {
连接.execute(查询, 变量, (错误, 结果) => {
if (错误) {
console.log(`错误: ${错误} \n ${错误.sqlMessage}`)
拒绝(错误)
}
解决(结果)
})
})
}
auth.signup = (req, res) => {
const 查询 = `
INSERT INTO 用户 (Id, 邮箱, 密码)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const 变量 = [req.query.email, req.query.password]
pps(查询, 变量)
.then(结果 => {
res.status(200)
})
.catch(错误 => {
res.status(400)
});
}
```
<details>
<summary>英文:</summary>
function pps( query, variables){
return new Promise((resolve, reject) => {
connection.execute(query, variables, (err, results) => {
if (err) {
console.log(`Error: ${error} \n ${err.sqlMessage}`)
reject(err)
}
resolve(results)
})
})
}
auth.signup = (req, res) => {
const query = `
INSERT INTO User (Id, Email, Password)
VALUES (UUID_TO_BIN(UUID()), ?, ?)
`
const variables = [req.query.email, req.query.password]
pps(query, variables)
.then(result => {
res.status(200)
})
.catch( error => {
res.status(400)
});
}
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论