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

21 Jun 2011

      ...
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@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.

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

David Virebayre