[Updates John Goerzen **20071106092527] { hunk ./en/book-shortcuts.xml 148 - - +prepare"> +Statement"> hunk ./en/ch21-databases.xml 182 + + The &run; function returns the number of rows each query + modified. For the first query, which created a table, no rows + were modified. The second query inserted a single row, so + &run; returned 1. + hunk ./en/ch21-databases.xml 217 - FIXME + HDBC, like most databases, supports a notion of replacable + paramaters in queries. There are two primary benefits of + using replacable parameters: they improve performance when + executing similar queries repeatedly, and they permit easy and + portable insertion of data into queries. + + + Let's say you wanted to add thousands of rows into our new + table test. You could issue thousands of + queries looking like INSERT INTO test VALUES (0, + 'zero') and INSERT INTO test VALUES (1, + 'one'). This forces the database server to parse + each SQL statement individually. If you could replace the two + values with a placeholder, the server could parse the SQL + query once, and just execute it multiple times with the + different data. + + + A second problem involves escaping. What if you wanted to + insert the string I don't like 1? SQL uses + the single quote character to show the end of the field. Most + SQL databases would require you to write this as I + don''t like 1. But rules for other special + charcters such as backslashes differ between databases. + Rather than trying to code this yourself, HDBC can handle it + all for you. Let's look at an example. + + &query.ghci:runargs; + + The question marks in the INSERT query in this example are the + placeholders. We then passed the parameters that are going to + go there. &run; takes a list of &SqlValue;, so we used + &toSql; to convert each item into an &SqlValue;. HDBC + automatically handled conversion of the &String; + "zero" into the appropriate representation + for the database in use. + + + This approach won't actually achieve any performance benefits + when inserting large amounts of data. For that, we need more + control over the process of creating the SQL query. We'll + discuss that in the next section. + + + + + Prepared Statements + + HDBC defines a function &prepare; that will prepare a SQL + query, but it does not yet bind + the parameters to the query. &prepare; returns a + &Statement; representing the compiled query. + + + Once you have a &Statement;, you can do a number of things + with it. You can call &execute; on it one or more times. + After calling &execute; on a query that returns data, you can + use one of the fetch functions to retrieve that data. + Functions like &run; and &quickQueryp; use statements and + &execute; internally; they are simply shortcuts to let you + perform common tasks quickly. When you need more control over + what's happening, you can use a &Statement; instead of a + function like &run;. + + + Let's look at using statements to insert multiple values with + a single query. Here's an example: + + &query.ghci:mult; + + In this example, we created a prepared statement and called it + stmt. We then executed that statement four + times, and passed different parameters each time. These + parameters are used, in order, to replace the question marks + in the original query string. Finally, we commit the changes + and disconnect the database. + + + HDBC also provides a function &executeMany; that can be useful + in situations such as this. &executeMany; simply takes a list + rows of data to call the statement with. Here's an example: hunk ./en/ch21-databases.xml 299 + &query.ghci:executeMany; + + + On the server, most databases will have an optimization that + they can apply to executeMany so that they only have to compile + this query string once, rather than twice.HDBC + emulates this behavior for databases that do not provide it, + providing programmers a unified API for running queries + repeatedly. This can lead to a dramatic + performance gain when inserting large amounts of data at + once. Some databases can also apply this optimization to + execute, but not all. + + addfile ./examples/ch21/connect.ghci hunk ./examples/ch21/connect.ghci 1 +--# conn +:m Database.HDBC Database.HDBC.Sqlite3 +conn <- connectSqlite3 "test1.db" +:t conn +disconnect conn addfile ./examples/ch21/query.ghci hunk ./examples/ch21/query.ghci 1 +--# setup +:m Database.HDBC Database.HDBC.Sqlite3 +conn <- connectSqlite3 "test1.db" +run conn "CREATE TABLE test (id INTEGER NOT NULL, desc VARCHAR(80))" [] +run conn "INSERT INTO test (id) VALUES (0)" [] +commit conn +disconnect conn + +--# runargs +conn <- connectSqlite3 "test1.db" +run conn "INSERT INTO test VALUES (?, ?)" [toSql 0, toSql "zero"] +commit conn +disconnect conn + +--# mult +conn <- connectSqlite3 "test1.db" +stmt <- prepare conn "INSERT INTO test VALUES (?, ?)" +execute stmt [toSql 1, toSql "one"] +execute stmt [toSql 2, toSql "two"] +execute stmt [toSql 3, toSql "three"] +execute stmt [toSql 4, SqlNull] +commit conn +disconnect conn + +--# executeMany +conn <- connectSqlite3 "test1.db" +stmt <- prepare conn "INSERT INTO test VALUES (?, ?)" +executeMany stmt [[toSql 5, toSql "five's nice"], [toSql 6, SqlNull]] +commit conn +disconnect conn + }