0
saveX :: [String] -> Int->IO ()
saveX [] y= return ()
sav xs y=
     do conn <- connectSqlite3 "cw.db"
        stmt <- prepare conn "INSERT INTO pic (src,urlId) VALUES (?,?)"
        executeMany stmt <what to add here>
        commit conn

I have a table with two columns src and urlId and [String] contains list of src and Int is the urlID. I want to insert all the srcs to table with the urlId. I have tried few ways with converting tuples but toSql does not work for it. Please help me with this

Gihan
  • 2,476
  • 2
  • 27
  • 33
  • Are you using HDBC or HDBI? I can't easily tell since `connectSqlite3`, `prepare`, and `executeMany` are defined in both of them. It looks like, in either case, it's a list of what you would have passed to `execute` for the same SQL. – Cirdec Jan 06 '14 at 23:42
  • i use HDBC. My imports are import Database.HDBC import Database.HDBC.Sqlite3 – Gihan Jan 06 '14 at 23:46
  • yes i do understand i have to add the list. If it was the case of list it is possible for me to do so. Issue comes since there are two insert values. One is a list and other is a one value. That is why I tried tupling them so I can have a list of tuples to map the toSql method but it turned out that it does not support tuples. – Gihan Jan 06 '14 at 23:50

1 Answers1

2

There's an almost identical example in Real World Haskell:

ghci> conn <- connectSqlite3 "test1.db"
ghci> stmt <- prepare conn "INSERT INTO test VALUES (?, ?)"
ghci> executeMany stmt [[toSql 5, toSql "five's nice"], [toSql 6, SqlNull]]
ghci> commit conn
ghci> disconnect conn

Edit

For your specific case, this isn't a question about how to use HDBC, but about how to combine one piece of ordinary Haskell data, y, with a list of data, xs. The map function takes a single function and applies it to every element in a list, returning a list of the results. If we put the single y in a function, we can map it onto the list, and get a result for every item in the list. For example:

map (\x -> (x, "One Thing")) [1, 2, 3]

Will result in:

[(1, "One Thing"), (2, "One Thing"), (3, "One Thing")]

To combine your one y that is a urlId with your xs that contain sources, you could write

map (\x -> [toSql x, toSql y]) xs

This would give you the following entire piece of code:

saveX :: [String] -> Int->IO ()
saveX [] y= return ()
sav xs y=
     do conn <- connectSqlite3 "cw.db"
        stmt <- prepare conn "INSERT INTO pic (src,urlId) VALUES (?,?)"
        executeMany stmt (map (\x -> [toSql x, toSql y]) xs)
        commit conn
Cirdec
  • 24,019
  • 2
  • 50
  • 100
  • this much I do understand. But I want to know how to map the list and the single int value to two insert columns. Few things I tried would be executeMany stmt (map (\x -> [toSql x]) (cnvertP xs y)) here convertP returns a list of tupel with signature [(String,int)] does not work and I tried with my own mapTuple method as well.. I am confused but there should be a simple way to this I guess – Gihan Jan 06 '14 at 23:52
  • What list? Do you mean you have a list, `sources`, of what will go in `src` but only have one `urlId` which will go in `urlId`? Then use `map (\src -> [toSql src, toSql urlId]) sources`. If not, what are the Haskell types of the data you want to store in `src` and `urlId`. What are the database types of the `src` and `urlId` columns? – Cirdec Jan 06 '14 at 23:56
  • thanks alot.... for the answer... I will try it out.. I see the issue with the things I tried.. It was me not using the proper mapping here.. Stupid me I guess.. Thank you !! I will accept this as answer ! – Gihan Jan 07 '14 at 00:21