This is also why it can be important to ensure that your test suites use the same version of the JDBC driver and the same version of your database (even when it is convenient and tempting to run tests against, say, an in-memory DB like H2 or a local DB like Derby or SQLite). But it’s worth checking what types actually come back from a given SQL operation (in the REPL) – and it’s also worth being a bit more careful (or at least forgiving) in terms of comparisons you perform on data coming back from the database (e.g., as John noted above, using = for numeric equality instead of = unless you know exactly what types you are dealing with).Ĭomputed values – as in your CASE expression – are particularly “vulnerable” since the database may use a “larger” type than you expect to avoid potential truncation/overflow. I wouldn’t say you can’t trust the types – for a given database and JDBC driver version, they are consistent. So I’m starting here with the hope that someone smarter than me has a clue This may not be/is probably not a Clojure-specific problem but I don’t know very much about databases or any Java so I cannot recreate the problem outside of Clojure using jdbc. Query where `int_field` is returned from a CASE statement Create table with one integer field `int_field` Using the MySQL connection, the value returned is 1 and using the MariaDB connection, the value returned is 1M. In this article, well introduce you to the syntax, formats, and uses of the CASE expression. It takes a db connection, creates a table with one integer field, inserts one row with the value 1 and then queries the table using a CASE statement that returns the row. The simple way to achieve this goal is to add a CASE expression to your SELECT statement. The following function demonstrates the problem. And now queries with CASE statements that should return a Long instead return a BigDecimal. I need to change my database driver from MySQL 5.7.26 to the latest MariaDB driver 10.5.8.
0 Comments
Leave a Reply. |