Re: [sqlite] A couple of questions about prepared statements
On 19 Oct 2018, at 17:33, Simon Slavin wrote: > On 19 Oct 2018, at 3:43pm, Tim Streater wrote: > >> is it OK to generate the myid and goodtext parts using the usual string >> methods from my host language, leaving only badtext as a bound variable, so >> that my prepared statement looks like this: >> >> select somecol from mytable where myid=3 and badtext=? and >> goodtext="somegoodtext" > > That will work fine. Good. > Just a note that your string quoting characters are wrong (this > may have been done by your email client). They should both be > apostrophes: > > select somecol from mytable > where myid=3 and badtext=? and goodtext='somegoodtext' No, that was my fault. Not awake enough yet. > Presumably you will > CREATE INDEX t_tgb ON mytable (myid,goodtext,badtext) > >> My other question relates to when the database is actually touched. Is it >> the case that statement preparation and variable binding do not affect the >> database itself and it's only when the prepared statement is actually >> executed that the database is touched and might generate an SQLITE_BUSY >> response? > > The database needs to be read, but not written, during _prepare_v2(). It > won't be written until _step(). Depending on your journal mode, it's possible > that _prepare() will be blocked because another connection is making a change. > > However, proper use of _timeout() will mean you will not care about either of > the above. OK - I'll bear all that in mind - thanks. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A couple of questions about prepared statements
On 19 Oct 2018, at 3:43pm, Tim Streater wrote: > is it OK to generate the myid and goodtext parts using the usual string > methods from my host language, leaving only badtext as a bound variable, so > that my prepared statement looks like this: > > select somecol from mytable where myid=3 and badtext=? and > goodtext="somegoodtext" That will work fine. Just a note that your string quoting characters are wrong (this may have been done by your email client). They should both be apostrophes: select somecol from mytable where myid=3 and badtext=? and goodtext='somegoodtext' Presumably you will CREATE INDEX t_tgb ON mytable (myid,goodtext,badtext) > My other question relates to when the database is actually touched. Is it the > case that statement preparation and variable binding do not affect the > database itself and it's only when the prepared statement is actually > executed that the database is touched and might generate an SQLITE_BUSY > response? The database needs to be read, but not written, during _prepare_v2(). It won't be written until _step(). Depending on your journal mode, it's possible that _prepare() will be blocked because another connection is making a change. However, proper use of _timeout() will mean you will not care about either of the above. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A couple of questions about prepared statements
On 2018-10-19 10:43:21, "Tim Streater" wrote: Suppose I have an SQL statement like this: select somecol from mytable where myid=3 and badtext="usertext" and goodtext="somegoodtext" Suppose further that the values of myid and goodtext are generated by reliably me whereas badtext is supplied externally. If I want to use a prepared statement here, is it OK to generate the myid and goodtext parts using the usual string methods from my host language, leaving only badtext as a bound variable, so that my prepared statement looks like this: select somecol from mytable where myid=3 and badtext=? and goodtext="somegoodtext" That would simplify my life. It might complicate your life later. If your "safe" data gets mixed with unsafe data somewhere that you're not aware of (or it changes in a later version of your application), you're opening yourself up to stealth bugs. Binding everything reduces guesswork and long-term maintenance. Also, statements have a maximum size: if your embedded information is large (or later becomes large), you can run into problems there, too. Note, too, that the correct way to quote string is with single-quote characters. SQLite will accept double-quotes when it's unambiguous, but it's a good habit to get out of, for the ambiguous cases. My other question relates to when the database is actually touched. Is it the case that statement preparation and variable binding do not affect the database itself and it's only when the prepared statement is actually executed that the database is touched and might generate an SQLITE_BUSY response? When the transaction is committed. If you've not explicitly begun a transaction, then when the statement is executed, yes. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A couple of questions about prepared statements
Suppose I have an SQL statement like this: select somecol from mytable where myid=3 and badtext="usertext" and goodtext="somegoodtext" Suppose further that the values of myid and goodtext are generated by reliably me whereas badtext is supplied externally. If I want to use a prepared statement here, is it OK to generate the myid and goodtext parts using the usual string methods from my host language, leaving only badtext as a bound variable, so that my prepared statement looks like this: select somecol from mytable where myid=3 and badtext=? and goodtext="somegoodtext" That would simplify my life. My other question relates to when the database is actually touched. Is it the case that statement preparation and variable binding do not affect the database itself and it's only when the prepared statement is actually executed that the database is touched and might generate an SQLITE_BUSY response? Thanks. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
