Generated SQL query not returning the same thing as the corresponding static query in sqlite3 HDBC

So... just to state the facts:

• your code does run it does not produce any syntax errors or warnings (and this is for both the haskell and the sql that is ran by thehaskell)
• the original query does run but not with op and pos added (there were already dynamic parts to it)
• you get an empty set back (meaning, the query returns no rows)...

If all these things are true, it leads me to believe that the query must be valid but wrong. Check the data? Dump the query, run it manually. Let me know.

things to try:

• Try rolling back the changes to see if it still works (so you know nothing was accidentally changed and to verify the data is the same).
• Can you try testing with a simpler query?
• Can you try dumping the query variable and running it manually in the DB (with and without changes)?
• Do you want to post a few rows of your data (some rows that will be returned, some that won't) so I can load it into a temp table test with it?
• Try adding just pos to the working query (with op hard-coded) and see if that works
• Try adding just op to the working query (with pos hard-coded) and see if that works
• Make sure you are listing your variables in the correct order everywhere

For some reason, I keep thinking it might be a datatype issue with casting or something but I have never worked with Haskell so I don't can't really guess at what else could be going on.

Other suggestions:

• format your query properly so it is easily readable (at least a little, so it isn't one huge string)
• update your question to include specifications on how your environment is set up (with versions of software/things and stuff)
• if you think the issue is tied to laziness, try forcing evaluation...? But the query did already have dynamic/variable parts to it. I would have to assume that they would have the same problem, if this was the case, and the query wouldn't have worked to begin with.
• this would be silly, but you didn't happen to change what DB you are pulling from, did you?

sqlite> select * from temp;temp_id     temp_name----------  ----------1           one2           two3           three

import Database.HDBC.Sqlite3 import Database.HDBCtestdb = "C:\\Users\\Kim!\\test.db"data UmeQuery = UmeQuery String [SqlValue] deriving ShowtRunUmeQuery :: UmeQuery -> FilePath -> IO [[SqlValue]]tRunUmeQuery (UmeQuery q args) dbFile = do    conn <- connectSqlite3 dbFile    stat <- prepare conn q    s <- execute stat args    res <- fetchAllRows' stat     disconnect conn    return $ res     selectPos temp_id op = let    q = "select temp_id, temp_name from temp where temp_id = " ++ op ++ " ?";     a = [ toSql temp_id ]    in UmeQuery q a

> let a = selectPos (1::Int) "="> let b = tRunUmeQuery a testdb > b[[SqlByteString "1",SqlByteString "one"]]> let a = selectPos (1::Int) ">"> let b = tRunUmeQuery a testdb > b[[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"]] 

Quick note: I've never touched Haskell or SQLite before today. I am running Haskell Platform 2014.2.0.0 with this SQLite3 - sqlite-dll-win64-x64-201409301904.zip on Windows 7 Professional 64bit.

edit: this also works... (query is a lil different, too)

import Data.ListselectPos temp_id op temp_name = let    q = foldl' (++)  [] [       "select temp_id, temp_name        " ++        "from   temp                      " ++       "where  temp_id " , op , " ? or   " ++       "       temp_name = ?             "]   a = [ toSql (temp_id::Int), toSql temp_name ]     in UmeQuery q a> let a = selectPos 1 ">" "one"> let b = tRunUmeQuery a testdb > b[[SqlByteString "1",SqlByteString "one"],[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"]] 

edit: and this works...

sqlite> insert into temp values (4, "Word"); sqlite> insert into temp values (5, "Utterance");selectPos targetlt parentlt op pos = let    q = " select temp_id, temp_name        \       \ from   temp                      \       \ where  temp_name = ?  or         \       \        temp_name = ?  or         \       \        temp_name = ?  or         \       \        temp_name = ?  or         \       \        temp_id "++op++" ?        "   a = [toSql targetlt, toSql parentlt,         toSql targetlt, toSql parentlt,         toSql (pos::Int) ]   in UmeQuery q a> let a = selectPos "Word" "Utterance" "=" 2> let b = tRunUmeQuery a testdb > b[[SqlByteString "2",SqlByteString "two"],[SqlByteString "4",SqlByteString "Word"],[SqlByteString "5",SqlByteString "Utterance"]]

so... in your queries that you posted in the question... there is an unexpected difference, too... that doesn't have to do with the variables. It's a single quote. Not sure if just a typo in copy and paste or what. I obviously cannot run your query as it is exactly because that's a significant amount of mock tables and data to come up with...

edit: hah... I came back to this again. I noticed you had an extra line above your last selectPos example that I wasn't using. I had to do it like this to get it to work... [[SqlValue]] or IO [[SqlValue]] as the last value did not work for me; errors (I'm just trying things, I don't know if either of those values truly make sense).

selectPos :: String -> String -> String -> Integer -> UmeQueryselectPos targetlt parentlt op pos = let    q = " select temp_id, temp_name        \       \ from   temp                      \       \ where  temp_name = ?  or         \       \        temp_name = ?  or         \       \        temp_name = ?  or         \       \        temp_name != ?  or        \       \        temp_id "++op++" ?        "   a = [toSql targetlt, toSql parentlt,         toSql targetlt, toSql parentlt,         toSql pos ]   in UmeQuery q a> let a = selectPos "Word" "Utterance" "=" 2> let b = tRunUmeQuery a testdb > b[[SqlByteString "1",SqlByteString "one"],[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"],[SqlByteString "4",SqlByteString "Word"],[SqlByteString "5",SqlByteString "Utterance"]] 

either way on this... I'm happy I got to write my first Haskell program today...!