Dear café (CC John and Chris), I'm having some trouble with getting positional parameters in HDBC-mysql to work. Most of the time they work fine, but sometimes (and often enough that it's a serious bother) the parameters don't reach the server correctly. Let me first describe my setup: * Ubuntu 8.10 intrepid (inside VMWare Fusion on Mac OS X Snow Leopard) * mysql Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2 * ghc 6.10.3 * HDBC-2.2.4 * HDBC-mysql-0.6.1 I have a database with a single, empty table:
CREATE TABLE `foo` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1031 DEFAULT CHARSET=latin1
And I have the following Haskell program:
import Database.HDBC import Database.HDBC.MySQL import Data.List import Data.Traversable import Control.Monad
main :: IO () main = do conn <- connectMySQL defaultMySQLConnectInfo { mysqlUnixSocket = "/var/run/mysqld/mysqld.sock" , mysqlUser = "root" , mysqlDatabase = "db" } for [1..1000] $ \n -> do let sql = "select id from foo where id in (" ++ intercalate "," (replicate (fromInteger n) "?") ++ ")" stmt <- prepare conn sql execute stmt (map SqlInteger [1..n]) finish stmt return ()
The program produces no output. All 1000 queries that are sent to the database are expected to return 0 results, since table foo is empty. This is all fine. The problem is in the queries that the server receives. If I turn on full logging, I expect all queries to have a "where id in (...)" where there is an increasing list of integers within the parentheses. But when examining the logs, some are small negative numbers, NULLs or just other numbers:
506 Execute [980] select id from foo where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26, 27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,824,825,826,827,828,829, 830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847, 848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865, 866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883, 884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901, 902,903,904,905,906,907,908,0,0,34359738368,-5210566582833489576,NULL, NULL,0,34359738368,-5210565758199768552,NULL,NULL,0,34359738368, -5210564933566047528,NULL,NULL,0,34359738368,-5210564108932326504,NULL, NULL,0,34359738368,-5210563284298605480,NULL,NULL,0,34359738368, -5210562459664884456,NULL,NULL,158,159,160,161,162,(snip)
The server then faithfully executes the statement and returns the results expected for the query it reports to have received, which (in a table with actual data) is not what the Haskell program was expecting. If I compile the program once, the results vary per run. If I don't use positional parameters but instead render the full SQL query myself, there is no problem. Is this a problem in HDBC or in HDBC-mysql? What's going on here? How do I fix or work around this? Many thanks, Martijn.