英文:
Using prepared statement for SQL queries in Go errors
问题
我正在尝试使用预处理语句查询一个sqlite文件。这在之前是正常工作的，但是我改变了代码以使其更易于进行单元测试。为了做到这一点，我开始使用预处理语句。然而，我得到了一个"结果集中没有行"的错误，但是当我手动在文件上键入这个查询时，它返回了一些结果。
我真的不明白出了什么问题。我觉得可能是我传入的参数有问题，但是我已经尝试了stackoverflow上的所有方法，但没有一个起作用。
这些查询是针对CentOS和RHEL机器上的默认事务yum db文件运行的。上述代码的输出如下：
包名是：telnet
数据库是：&{0 {/var/lib/yum/history/history-2022-10-14.sqlite 0xc0000ae140} 0 {0 0} [] map[] 0 0 0xc00008e1e0 false map[] map[] 0 0 0 0
错误：sql: no rows in result set
ID是：0
没有返回行：sql: no rows in result set
查询事务ID时出错：sql: no rows in result set
"WHERE LIKE"的参数应该匹配包含"INSTALL {PACKAGE_HERE}"的任何行，但似乎没有找到任何行（尽管它们肯定存在）。
我已经尝试了stackoverflow上的大多数方法，阅读了GO SQL的官方文档。到目前为止，没有任何方法对我起作用。
英文:
I am trying to use a prepared statement for querying a sqlite file. This worked fine before, but I change the code to make it more unit testable. To do this, I started using prepared statements. However, I am getting an error of "No rows in result set", yet when I type this query out manually on the file, it returns something.
I'm not really understanding whats going wrong here. I feel like its something to do with the argument I am passing in but I've tried everything on stackoverflow and nothing has worked
import (
"database/sql"
"database/sql/driver"
_ "github.com/mattn/go-sqlite3"
)
...
type Driver struct {
driver *driver.Driver
}
...
func getTransactionId(pkg string, db *sql.DB) (int, error) {
var id int
fmt.Println("Package is:", pkg)
fmt.Println("Db is this:", db)
sqlStatement := `SELECT tid FROM trans_cmdline WHERE cmdline LIKE '%install ?%' ORDER BY tid DESC LIMIT 1`
err := db.QueryRow(sqlStatement, pkg).Scan(&id)
if err != nil {
fmt.Println("Err:", err)
}
switch err {
case sql.ErrNoRows:
fmt.Println("ID is this:", id)
fmt.Println("No rows were returned:", err)
return 0, err
case nil:
return id, nil
default:
return 0, err
}
}
These queries are being ran against the default transaction yum db file on CentOS & RHEL machines. The output to the above code is like so:
Package is: telnet
Db is this: &{0 {/var/lib/yum/history/history-2022-10-14.sqlite 0xc0000ae140} 0 {0 0} [] map[] 0 0 0xc00008e1e0 false map[] map[] 0 0 0 0 <nil> 0 0 0 0 0x52cc80}
Err: sql: no rows in result set
ID is this: 0
No rows were returned: sql: no rows in result set
Error querying for transaction ID: sql: no rows in result set
The argument "WHERE LIKE" is supposed to match any row that contains "INSTALL {PACKAGE_HERE}", yet it doesn't seem to be finding any rows (when they certainly exist).
I've tried most things on Stackoverflow, read over the official docs for GO SQL. Nothing has worked for me so far.
答案1
得分: 2
replace函数在like语句的%...%内部不起作用，请尝试构建SQL的like语句。
SELECT * FROM analysis WHERE notes like CONCAT('%',?,'%');
英文:
The replace doesn't work inside the like's %...% try building the sql like statement
SELECT * FROM analysis WHERE notes like CONCAT( '%',?,'%');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论