how to Query into sqlite3 in golang?

Question

My table function

func createTransactionTable(db *sql.DB) {
    transaction := `CREATE TABLE IF NOT EXISTS Transaction_history(
        "rollno" INTEGER UNSIGNED NOT NULL,
        "isReward" INTEGER UNSIGNED NOT NULL,
        "transfered_to" INTEGER UNSIGNED NOT NULL,
        "transfered_amount" INTEGER UNSIGNED NOT NULL,
        "redeems" INTEGER UNSIGNED NOT NULL,
        "date" TEXT NOT NULL
        );`
    statement, err := db.Prepare(transaction)
    if err != nil {
        panic(err)
    }
    statement.Exec()
    fmt.Println("trasaction  table created")
}

query function

count, err := db.Exec(`SELECT count(isReward) from Transaction_history WHERE rollno = ? AND isReward = ?`, rollno, 1)
if err != nil {
    panic(err)
}
if count != 0 {
    return true
}
return false

I am trying to find the no of rows of in my table where isReward = 1 and rollno is as specified of our choice but is giving and i dont know how to achieve, i know it is a very basic but literally searched but didn't get anything that will fit my need, so need help

I don't understand that this question is pretty clear what i am asking, still people instead of helping dislike your question — Dhruv singhal, Jun 28 '21 at 16:05

Clark McCauley · Accepted Answer · 2021-06-28T16:49:25.260

1

From the docs:

Exec executes a query that doesn't return rows. For example: an INSERT and UPDATE.

Try Query instead. I didn't test this against your dataset (obviously) but hopefully it gives you some good direction. Also you should properly handler errors.

type row struct {
    Count int `json:"count"`
}
response, _ := db.Query("SELECT count(isReward) as count from Transaction_history WHERE rollno = ? AND isReward = ?", rollno, 1)
var rows []row
_ = response.Scan(&rows)
count := rows[0].Count

If you're getting a database lock error, make sure you're not trying to query SQLite concurrently. You can create a global mutex, and make sure that every query against the database acquires the lock.

var m sync.Mutex

func createTransactionTable(...) {
    m.Lock()
    defer m.Unlock()
    // Perform your query here
}

edited Jun 28 '21 at 16:49

answered Jun 28 '21 at 16:14

Clark McCauley

1,342
5
16

Also i am facing database lock error , i had searched that mutex can solve this can you like how can i apply mutexes – Dhruv singhal Jun 28 '21 at 16:43
Check out [this](https://github.com/mattn/go-sqlite3/issues/274) and [this](https://stackoverflow.com/questions/32479071/sqlite3-error-database-is-locked-in-golang). Also see my updated answer above. – Clark McCauley Jun 28 '21 at 16:49

score 0 · Answer 2 · edited Jun 29 '21 at 10:03

I think it will be easier if you use Driver like this

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")

// and InsertStudent func like this

createStudentTableSQL := `CREATE TABLE student (
        "idStudent" integer NOT NULL PRIMARY KEY AUTOINCREMENT,     
        "code" TEXT,
        "name" TEXT,
        "program" TEXT      
      );` 

    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")

You can use methods like this

how to Query into sqlite3 in golang?

2 Answers2