时间戳

首页 > 解决方案 > 如何处理非准备好的语句占位符

go - 如何处理非准备好的语句占位符

问题描述

在下面的代码中，数据是使用非准备好的语句插入的。但是，查询使用占位符。

我想知道语句是否代表我 JIT 准备好，或者驱动程序是否实现了一个特殊的逻辑来适当地应用这些值，或者它是否是在协议实现中发生的。

你能告诉我引擎盖下发生了什么吗？

我有兴趣了解专业 sql 驱动程序 (pg/mysql/sqlite/oracle/mssql) 的行为

package main

import (
    "database/sql"
    "log"
    "os"

    _ "github.com/mattn/go-sqlite3" // Import go-sqlite3 library
)

func main() {
    os.Remove("sqlite-database.db") // I delete the file to avoid duplicated records.
    // SQLite is a file based database.

    log.Println("Creating sqlite-database.db...")
    file, err := os.Create("sqlite-database.db") // Create SQLite file
    if err != nil {
        log.Fatal(err.Error())
    }
    file.Close()
    log.Println("sqlite-database.db created")

    sqliteDatabase, _ := sql.Open("sqlite3", "./sqlite-database.db") // Open the created SQLite File
    defer sqliteDatabase.Close()                                     // Defer Closing the database
    createTable(sqliteDatabase)                                      // Create Database Tables

    // INSERT RECORDS
    insertStudent(sqliteDatabase, "0001", "Liana Kim", "Bachelor")
    insertStudent(sqliteDatabase, "0002", "Glen Rangel", "Bachelor")
    insertStudent(sqliteDatabase, "0003", "Martin Martins", "Master")
    insertStudent(sqliteDatabase, "0004", "Alayna Armitage", "PHD")
    insertStudent(sqliteDatabase, "0005", "Marni Benson", "Bachelor")
    insertStudent(sqliteDatabase, "0006", "Derrick Griffiths", "Master")
    insertStudent(sqliteDatabase, "0007", "Leigh Daly", "Bachelor")
    insertStudent(sqliteDatabase, "0008", "Marni Benson", "PHD")
    insertStudent(sqliteDatabase, "0009", "Klay Correa", "Bachelor")

    // DISPLAY INSERTED RECORDS
    displayStudents(sqliteDatabase)
}

func createTable(db *sql.DB) {
    createStudentTableSQL := `CREATE TABLE student (
        "idStudent" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
        "code" TEXT,
        "name" TEXT,
        "program" TEXT
      );` // SQL Statement for Create Table

    log.Println("Create student table...")
    statement, err := db.Prepare(createStudentTableSQL) // Prepare SQL Statement
    if err != nil {
        log.Fatal(err.Error())
    }
    statement.Exec() // Execute SQL Statements
    log.Println("student table created")
}

// We are passing db reference connection from main to our method with other parameters
func insertStudent(db *sql.DB, code string, name string, program string) {
    log.Println("Inserting student record ...")
    insertStudentSQL := `INSERT INTO student(code, name, program) VALUES (?, ?, ?)`
    // statement, err := db.Prepare(insertStudentSQL) // Prepare statement.
    // This is good to avoid SQL injections
    // if err != nil {
    //  log.Fatalln(err.Error())
    // }
    _, err := db.Exec(insertStudentSQL, code, name, program)
    if err != nil {
        log.Fatalln(err.Error())
    }
}

func displayStudents(db *sql.DB) {
    row, err := db.Query("SELECT * FROM student ORDER BY name")
    if err != nil {
        log.Fatal(err)
    }
    defer row.Close()
    for row.Next() { // Iterate and fetch the records from result cursor
        var id int
        var code string
        var name string
        var program string
        row.Scan(&id, &code, &name, &program)
        log.Println("Student: ", code, " ", name, " ", program)
    }
}

标签： go

解决方案

对于pq驱动程序 v1.9.0，服务器始终准备具有参数的查询。

见https://github.com/lib/pq/blob/master/conn.go#L850，我在下面添加了一些**评论

func (cn *conn) query(query string, args []driver.Value) (_ *rows, err error) {
    // ** ....

    // Check to see if we can use the "simpleQuery" interface, which is
    // *much* faster than going through prepare/exec
    if len(args) == 0 {
        return cn.simpleQuery(query)
    }
    // **below blocks also prepares the query, 
    // might just be a fast path
    if cn.binaryParameters {
        cn.sendBinaryModeQuery(query, args)

        cn.readParseResponse()
        cn.readBindResponse()
        rows := &rows{cn: cn}
        rows.rowsHeader = cn.readPortalDescribeResponse()
        cn.postExecuteWorkaround()
        return rows, nil
    }
    // **below calls prepares the query
    st := cn.prepareTo(query, "")
    st.exec(args)
    return &rows{
        cn:         cn,
        rowsHeader: st.rowsHeader,
    }, nil
}

相关Query方法在https://github.com/lib/pq/blob/master/conn.go#L846

// Implement the "Queryer" interface
func (cn *conn) Query(query string, args []driver.Value) (driver.Rows, error) {
    return cn.query(query, args)
}

Exec方法类似query，https: //github.com/lib/pq/blob/master/conn.go#L884

推荐阅读