MySQL connection throws "Unknown prepared statement handler (0) given to mysql_stmt_precheck" error

Question

I've created a macOS application to learn and test the local MySQL connections.

I installed MySQL via Homebrew.

I can connect to localhost from my application.

I tried to connect the server while using a non-exist database name, MySQL throws Unknown database 'test'

When I use an existing database name, I get this error:

Unknown prepared statement handler (0) given to mysql_stmt_precheck

My connection code:

import PerfectHTTP
import PerfectHTTPServer
import PerfectMySQL
import Foundation

public class DB {

    let host = "127.0.0.1"
    let user = "root"
    let password = "12345678"
    let database = "pets"
   
    func databaseConnect(host: String, user: String, password: String, db: String) -> MySQL {
        
        let mysql = MySQL() // Create an instance of MySQL to work with
        
        let connected = mysql.connect(host: host, user: user, password: password, db: db)
        
        guard connected else {
            // verify that we have connected successfully
            print(mysql.errorMessage())
            return mysql
        }
        
        return mysql
    }
    
    public func insertGame(title: String, description: String, createdDate: String){
    
        // Connect to our database
        var db = databaseConnect(host: host, user: user, password: password, db: database)
        
        defer {
            db.close() //This defer block makes sure we terminate the connection once finished, regardless of the result
        }
        
        // Create the statement we wish to execute
        let statement = MySQLStmt(db)
        let insert = "INSERT INTO game(id, title, description, release_date) VALUES (\(statement.insertId()), '\(title)', '\(description)', '\(createdDate)');"
        _ = statement.prepare(statement: insert)
        
        // Run the query
        let querySuccess = statement.execute()
        
        // Check that our query was successfuly, otherwise return out
        guard querySuccess else {
            print(db.errorMessage())
            return
        }
        
        print("Insert successful!");
    }
    
}

Rob · Answer 1 · 2020-12-07T01:39:51.447

You should not use insertId (which is just calling mysql_insert_id) in the INSERT statement, itself. That function gets the id for the previously performed INSERT statement, and as there was no previous statement, it will fail.

You should simply omit the AUTO_INCREMENT column from the INSERT statement, letting MySQL set the auto-incrementing value itself. That obviously assumes that you defined it as an auto incrementing column, e.g.

Now, if after the INSERT, if you need what value was used for the auto incrementing key, then might you then call insertId. E.g.,

func databaseConnect(host: String, user: String, password: String, db: String) -> MySQL? {
    let mysql = MySQL() // Create an instance of MySQL to work with

    let connected = mysql.connect(host: host, user: user, password: password, db: db)

    // verify that we have connected successfully
    guard connected else {
        print(mysql.errorCode(), mysql.errorMessage())
        return nil
    }

    return mysql
}

@discardableResult
public func insertGame(title: String, description: String, createdDate: String) -> UInt? {
    // Connect to our database
    guard let db = databaseConnect(host: host, user: user, password: password, db: database) else {
        return nil
    }

    // Create the statement we wish to execute
    let statement = MySQLStmt(db)
    let insert = "INSERT INTO game (title, description, release_date) VALUES (?, ?, ?);"
    guard statement.prepare(statement: insert) else {
        print(db.errorMessage())
        return nil
    }

    // bind values to the ? in the prepared SQL
    statement.bindParam(title)
    statement.bindParam(description)
    statement.bindParam(createdDate)

    // Check that our query was successfuly, otherwise return out
    guard statement.execute() else {
        print(db.errorMessage())
        return nil
    }

    // if you really need the ID, this is where you'd call `insertId`, perhaps returning it in case the caller would need it
    let id = statement.insertId()
    return id
}

A few other unrelated issues included in the above code snippet:

I would advise against using string interpolation to build the SQL. What if the title was “Finnegan's Wake”, where the apostrophe in the name will mess up the SQL, prematurely terminating that string value. Ideally you would bind values to placeholders like above, or at the very least, employ some escaping logic. The SQL in your question will be susceptible to SQL injection attacks and/or syntax errors with strings that need to be escaped.
I would not advise the _ = statement.prepare(...) pattern. The whole point of the return value is to tell you whether it succeeded or not. If prepare failed, that is where you would check the error messages and exit the function.
I would suggest only proceeding if the connecting with the server was successful. In your prior question, you reported a secondary “MySQL server has gone away” message. That was simply because you were trying to interact with MySQL when the connection failed, and you were proceeding regardless.

MySQL connection throws "Unknown prepared statement handler (0) given to mysql_stmt_precheck" error

1 Answers1