Thanks Siward. Can you confirm what version of mysql server you used, and what options it was using (like ansi mode)?
Can anyone else confirm the results with old or new mysql versions, and perhaps offer an example stored procedure. Tim. On Sun, Aug 10, 2008 at 01:13:10AM +0200, Siward de Groot wrote: > Hello dbi-users, > > I am reading perldoc DBI, > and there is a section there that describes how to handle > placeholders in WHERE clauses on columns that can contain NULL. > > That section has a table listing which database brands support which clause > styles. > That table did not contain entries for (my favorite) MySQL > (i did not check whether i had very latest version of that perldoc, > just installed it from Debian unstable). > > So i downloaded script referred to in that section, and executed it > (after making a few changes, as indicated in script, > and filling in user/pass as i had seen in man DBD::MySQL). > > Result i got is : > ------------------------------------------------------------------------ > 2 styles are supported: > Style 4: WHERE mycol = ? OR (mycol IS NULL AND ? IS NULL) > Style 6: WHERE mycol = ? OR (mycol IS NULL AND ? = 1) > ------------------------------------------------------------------------ > > As to support for style 5, please note that i did not create a stored > procedure, > since i do not yet know how to use these > (and also think that since query depends on a value in client, > procedure to execute would best be executed in client). > I expect that with current MySQL version stored procedure would work, > but older versions of MySQL do not support stored procedures. > > Error messages of failures of other styles were : > style 0 : WHERE clause style 0 returned incorrect results. > style 1 : FUNCTION test.NVL does not exist > style 2 : syntax error near second argument of first ISNULL > style 3 : syntax error near third argument of DECODE > > ------- > I hope that this info is usefull to you, and that it be included in docs. > Thanks for providing perl, DBI, DBD, perldoc, etc... > > Siward de Groot > (www.gransi.com/siward) >
