SQL logic error: near "RETURNING": syntax error (1) Go and SQLite

Question

I'm creating a crud application and I'm testing the database with an in-memory DB. The only test and functions that are breaking are the toggleTask func and test.

function:

func (r *Repository) ToggleTask(task Task) (Task, error) {
    query := "UPDATE Tasks SET completed = NOT completed WHERE id = (?)"
    _, err := r.db.Exec(query, task.ID)
    if err != nil {
        return task, err
    }

    query = "SELECT id, txt, completed FROM Tasks WHERE id = (?) RETURNING *"
    err = r.db.QueryRow(query, task.ID).Scan(&task.ID, &task.Text, &task.ListID, &task.Completed)
    if err != nil {
        return task, err
    }

    return task, nil
}

test:

const (
    ToggleTask = "SELECT id, txt, completed FROM Tasks WHERE id = (?) RETURNING *"
)

func TestToggleTask(t *testing.T) {
    repo := mockDbRepo()
    list := List{Name: "Test List"}
    repo.db.Exec(CreateList, list.Name)

    task := Task{Text: "Test Task", ListID: list.ID}
    repo.db.Exec(CreateTask, task.Text, task.ListID)

    completedTask,err := repo.ToggleTask(task)
    if err != nil {
        t.Error(err)
    }

    if !reflect.DeepEqual(completedTask, task) {
        t.Errorf("Expected %v, got %v", task, completedTask)
    }
}

The test returns to me:

SQL logic error: near "RETURNING": syntax error (1)

It does not make sense to use RETURNING in a SELECT statement and SQLite does not support it. Remove RETURNING and everything after that from your statements. — forpas
– forpas, Commented May 15, 2022 at 14:52
If I don’t use a returning how can I scan the results from the select query. Now I’m getting “sql:no rows in result set” — Атанас Чолаков
– Атанас Чолаков, Commented May 15, 2022 at 15:07
A SELECT statement is by design returning rows. I can't say if your go code is correct, but if it is, then it is possible there are no rows in the table with that id. — forpas
– forpas, Commented May 15, 2022 at 15:10
Yeah thank you a lot it’s now working. I have an issue with vsCode and its not saving and compiling at moments and I need to restart it several times to get it working — Атанас Чолаков
– Атанас Чолаков, Commented May 15, 2022 at 15:32

michmich112 · Accepted Answer · 2022-05-15 23:09:58Z

1

Your SQL query is incorrect. As stated in the SQL Lite docs, SELECT does not accept a RETURNING clause. The SELECT clause by itself returns data. Remove your RETURNING clauses in your queries and you should be good.

SQL Lite Ref: https://www.sqlite.org/lang_select.html

answered May 15, 2022 at 23:09

michmich112

7744 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL logic error: near "RETURNING": syntax error (1) Go and SQLite

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related