Parametrized queries using cached prepared statements
A parametrized query is implemented using prepared statements. This is a parametrized query:
1> mysql:query(Pid, "SELECT * FROM foo WHERE id=?", [Id]).
{ok,[<<"id">>,<<"bar">>],[[1,<<"baz">>],[2,<<"bah">>]]}
In SQL, you need to call "CREATE STATEMENT" to create the prepared statement before you can execute it using "EXECUTE" and passing values for the ?
place holders. This is two calls. When you are done, the prepared statement should be deleted using "DROP STATEMENT" to free resources, thus three calls in total.
The MySQL/OTP connection process is saving the prepared statement so when the same query is used again within a certain time (the cache TTL) the same prepared statement can be used. The query itself is used as a cache key. Thus, if the same code is run frequently, the prepared statement will already exist and there will be only one call to the server.
Use the {query_cache_time, Timeout}
option to mysql:start_link/1 to set the cache time in milleseconds or 'infinity'
.