[Haskell-cafe] [ANN] mysql-simple - your go-to package for talking to MySQL

[David Virebayre]

> You can access the docs on a slightly earlier version:
> http://hackage.haskell.org/package/mysql-simple-0.2.2.0

That's what I did.

> The doc specifies it here:

>> convertError :: [Field] -> [Maybe ByteString] -> Int -> a
>> Throw a ConversionFailed exception, indicating a mismatch between the number of columns in the Field and row, and the number in the collection to be converted to.

> So if you're making an instance for a type that takes ten items from
> the collection, then put 10. Could always make this clearer.

That wasn't clear to me, but English isn't my first language, so maybe
that's why.


>> To try, I put 0, and the test compiled. However, I had a connection
>> error number 1312, saying my procedure "can't return a result set in
>> the given context". (The query I used works from the mysql
>> command-line interface)
> Ah, I wouldn't know about that, I haven't used the mysql version.

I tried again with that code:


data PlateauSel = PS
  { psPlat :: Int
  , psArm :: Maybe Int
  , psTaille :: Int
  , psType :: Int
  , psEtat :: Int
  , psLoc :: Int
  , psDest :: Int
  , psCol :: Int
  , psEtg :: Int
  , psNiv :: Int
  , psPos :: Int
  , psRes :: Int
  }

instance QueryResults PlateauSel where
  convertResults [fa,fb,fc,fd,fe,ff,fg,fh,fi,fj,fk,fl]
                 [va,vb,vc,vd,ve,vf,vg,vh,vi,vj,vk,vl]
                 = PS a b c d e f g h i j k l
    where !a = convert fa va
          !b = convert fb vb
          !c = convert fc vc
          !d = convert fd vd
          !e = convert fe ve
          !f = convert ff vf
          !g = convert fg vg
          !h = convert fh vh
          !i = convert fi vi
          !j = convert fj vj
          !k = convert fk vk
          !l = convert fl vl
  convertResults fs vs = convertError fs vs 12
hello :: IO [PlateauSel]
hello = do
  conn <- connect myConnectInfo
  query_ conn "call Plateau_Select(1)"


But there's no improvement :

*Main> hello
*** Exception: ConnectionError {errFunction = "query", errNumber =
1312, errMessage = "PROCEDURE robot.Plateau_Select can't return a
result set in the given context"}
*Main>

The problem isn't with the stored procedure, it works if I call it
from the mysql client.

(xxxx at x.x.x.x) [robot] (;)> call Plateau_Select(1);
+-----------+-----------+--------+------+------+--------------+-------------+---------+-------+--------+----------+---------+
| IdPlateau | IdArmoire | Taille | Type | Etat | Localisation |
Destination | Colonne | Etage | Niveau | Position | Reserve |
+-----------+-----------+--------+------+------+--------------+-------------+---------+-------+--------+----------+---------+
|         1 |      NULL |      1 |    2 |    1 |            1 |
   1 |       0 |     0 |      0 |        0 |       1 |
+-----------+-----------+--------+------+------+--------------+-------------+---------+-------+--------+----------+---------+
1 row in set (0.03 sec)

Another information: it doesn't work either with HDBC-mysql, but it
does work with HDBC-odbc.





Another unrelated thing : the documentation states that the Query type
is designed to make it difficult to create queries by concatenating
strings.
I found out there are situations where you don't have a choice.

For example, how to write a function that returns the columns of a
table using show columns ?

type Champ = (String,String,String,String,String,String)
getColumns :: Connection -> String -> IO [Champ]
getColumns conn table = do
  query_ conn (fromString $ "show columns from " ++ table)

if you try query conn "show columns from " ( Only table), the query built is
show columns from 'xxxxx'
which fails.