1

Recently I have started working with sqlite3 in haskell and I was wondering how I can modify a variable in a table with a parameter as offset?

I would like to do something like this:

execute_ conn "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, v INTEGER)"

execute conn "INSERT INTO test (v) VALUES (?)" (Only ("1" :: String))

execute conn "UPDATE test SET v = v + ?" (Only modifyingValue) <--- this is not working

Thank you.

Matthew Verstraete
  • 6,335
  • 22
  • 67
  • 123
Relu
  • 13
  • 3
  • What is the error message? Is it a compiler error or a run-time error? – ErikR May 16 '16 at 18:03
  • It is a run-time error : SQLite3 returned ErrorMisuse while attempting to perform prepare "UPDATE test SET v = ?": library routine called out of sequence – Relu May 16 '16 at 18:26

1 Answers1

0

Here is the info on the ErrorMisuse error:

https://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence

Perhaps the connection has already been closed? Or are you using the same connection in two different threads?

Here is some sample code which works:

{-# LANGUAGE OverloadedStrings #-}

import           Control.Applicative
import qualified Data.Text as T
import           Database.SQLite.Simple
import           Database.SQLite.Simple.FromRow

dumpTable conn table = do
  r <- query_ conn "SELECT * from test" :: IO [(Int,String,Int)]
  mapM_ print r

main :: IO ()
main = do
  conn <- open "test.db"
  execute_ conn "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, firstname TEXT, pets INTEGER)"
  execute conn "INSERT INTO test (firstname, pets) VALUES (?,?)"
               ("Alice" :: String, 2 :: Int)
  execute conn "INSERT INTO test (firstname, pets) VALUES (?,?)"
               ("Bob" :: String, 3 :: Int) 
  putStrLn "before:"
  dumpTable conn "test"
  execute conn "UPDATE TEST SET pets = pets + ?" (Only (4 :: Int))
  putStrLn "after:"
  dumpTable conn "test"
  close conn
ErikR
  • 51,541
  • 9
  • 73
  • 124
  • Yes, i was using the connection in 3 threads since the purpose of my program was to test whether sqlite is single-threaded or not. If this was the cause of the error, then my test seems to prove that sqlite is not multi-threaded. Thank you :D – Relu May 16 '16 at 18:41
  • Each connection is single threaded, but sqlite allows concurrent access and updates, so just open up a new connection for each thread. – ErikR May 16 '16 at 18:48
  • For instance - see this SO answer: http://stackoverflow.com/questions/4060772/sqlite3-concurrent-access – ErikR May 16 '16 at 18:48