Re: [sqlite] A couple of questions about prepared statements

[Tim Streater]

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

[Simon Slavin]

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

[J. King]

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

[Tim Streater]

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

Re: [sqlite] a couple of questions

[Baruch Burstein]

I rebind them on every iteration.

On Mon, May 14, 2012 at 6:46 PM, Pavel Ivanov <paiva...@gmail.com> wrote:

> > One the data is commited surely it doesn't need to be retained, does it?
>
> If you called sqlite3_reset() on a statement and then didn't call
> sqlite3_clear_bindings() then all bindings will surely be needed on
> the next statement execution in some subsequent transaction.
>
>
> Pavel
>
>
> On Mon, May 14, 2012 at 11:35 AM, Black, Michael (IS)
> <michael.bla...@ngc.com> wrote:
> > If you do a periodic commit and use SQLITE_TRANSIENT wouldn't that work?
> >
> >
> >
> > One the data is commited surely it doesn't need to be retained, does it?
> >
> >
> >
> > Michael D. Black
> >
> > Senior Scientist
> >
> > Advanced Analytics Directorate
> >
> > Advanced GEOINT Solutions Operating Unit
> >
> > Northrop Grumman Information Systems
> >
> > 
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Baruch Burstein [bmburst...@gmail.com]
> > Sent: Monday, May 14, 2012 10:20 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] a couple of questions
> >
> > On Mon, May 14, 2012 at 5:45 PM, Richard Hipp <d...@sqlite.org> wrote:
> >
> >> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein <bmburst...@gmail.com
> >> >wrote:
> >>
> >> >
> >> > 2. How "static" does data have to be to be bound with SQLITE_STATIC?
> If
> >> it
> >> > won't change until the call to sqlite3_step, is that enough? How about
> >> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> >> > enough, I assume that if it doesn't change before the
> sqlite3_finalize,
> >> it
> >> > must be safe to use SQLITE_STATIC?
> >> >
> >>
> >> SQLITE_STATIC means that the content must not change until after SQLite
> has
> >> read it for the last time.  You are guaranteed to be safe if you hold
> the
> >> content unchanged until sqlite3_finalize().  You can probably get away
> with
> >> changing the content sooner than that, but it depends on your
> >> circumstances.  Why push your luck?
> >>
> >
> > For speed. I will be binding - stepping (it is an update statement) -
> > resetting in a tight loop, possibly up to about a million times.
> >
> > --
> > Programming today is a race between software engineers striving to build
> > bigger and better idiot-proof programs, and the Universe trying to
> produce
> > bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of questions

[Pavel Ivanov]

> One the data is commited surely it doesn't need to be retained, does it?

If you called sqlite3_reset() on a statement and then didn't call
sqlite3_clear_bindings() then all bindings will surely be needed on
the next statement execution in some subsequent transaction.


Pavel


On Mon, May 14, 2012 at 11:35 AM, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
> If you do a periodic commit and use SQLITE_TRANSIENT wouldn't that work?
>
>
>
> One the data is commited surely it doesn't need to be retained, does it?
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Baruch Burstein [bmburst...@gmail.com]
> Sent: Monday, May 14, 2012 10:20 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] a couple of questions
>
> On Mon, May 14, 2012 at 5:45 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein <bmburst...@gmail.com
>> >wrote:
>>
>> >
>> > 2. How "static" does data have to be to be bound with SQLITE_STATIC? If
>> it
>> > won't change until the call to sqlite3_step, is that enough? How about
>> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
>> > enough, I assume that if it doesn't change before the sqlite3_finalize,
>> it
>> > must be safe to use SQLITE_STATIC?
>> >
>>
>> SQLITE_STATIC means that the content must not change until after SQLite has
>> read it for the last time.  You are guaranteed to be safe if you hold the
>> content unchanged until sqlite3_finalize().  You can probably get away with
>> changing the content sooner than that, but it depends on your
>> circumstances.  Why push your luck?
>>
>
> For speed. I will be binding - stepping (it is an update statement) -
> resetting in a tight loop, possibly up to about a million times.
>
> --
> Programming today is a race between software engineers striving to build
> bigger and better idiot-proof programs, and the Universe trying to produce
> bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of questions

[Black, Michael (IS)]

If you do a periodic commit and use SQLITE_TRANSIENT wouldn't that work?



One the data is commited surely it doesn't need to be retained, does it?



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Baruch Burstein [bmburst...@gmail.com]
Sent: Monday, May 14, 2012 10:20 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] a couple of questions

On Mon, May 14, 2012 at 5:45 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein <bmburst...@gmail.com
> >wrote:
>
> >
> > 2. How "static" does data have to be to be bound with SQLITE_STATIC? If
> it
> > won't change until the call to sqlite3_step, is that enough? How about
> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> > enough, I assume that if it doesn't change before the sqlite3_finalize,
> it
> > must be safe to use SQLITE_STATIC?
> >
>
> SQLITE_STATIC means that the content must not change until after SQLite has
> read it for the last time.  You are guaranteed to be safe if you hold the
> content unchanged until sqlite3_finalize().  You can probably get away with
> changing the content sooner than that, but it depends on your
> circumstances.  Why push your luck?
>

For speed. I will be binding - stepping (it is an update statement) -
resetting in a tight loop, possibly up to about a million times.

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of questions

[Baruch Burstein]

On Mon, May 14, 2012 at 5:45 PM, Richard Hipp  wrote:

> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein  >wrote:
>
> >
> > 2. How "static" does data have to be to be bound with SQLITE_STATIC? If
> it
> > won't change until the call to sqlite3_step, is that enough? How about
> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> > enough, I assume that if it doesn't change before the sqlite3_finalize,
> it
> > must be safe to use SQLITE_STATIC?
> >
>
> SQLITE_STATIC means that the content must not change until after SQLite has
> read it for the last time.  You are guaranteed to be safe if you hold the
> content unchanged until sqlite3_finalize().  You can probably get away with
> changing the content sooner than that, but it depends on your
> circumstances.  Why push your luck?
>

For speed. I will be binding - stepping (it is an update statement) -
resetting in a tight loop, possibly up to about a million times.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of questions

[Richard Hipp]

On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein wrote:

> 1. Can a use the expression 'WHERE b IN (SELECT ...)' if b is a blob
> column? Does the 'IN' comparison work with blobs?
>

IN works with blobs.


>
> 2. How "static" does data have to be to be bound with SQLITE_STATIC? If it
> won't change until the call to sqlite3_step, is that enough? How about
> until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> enough, I assume that if it doesn't change before the sqlite3_finalize, it
> must be safe to use SQLITE_STATIC?
>

SQLITE_STATIC means that the content must not change until after SQLite has
read it for the last time.  You are guaranteed to be safe if you hold the
content unchanged until sqlite3_finalize().  You can probably get away with
changing the content sooner than that, but it depends on your
circumstances.  Why push your luck?


>
> --
> Programming today is a race between software engineers striving to build
> bigger and better idiot-proof programs, and the Universe trying to produce
> bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] a couple of questions

[Baruch Burstein]

1. Can a use the expression 'WHERE b IN (SELECT ...)' if b is a blob
column? Does the 'IN' comparison work with blobs?

2. How "static" does data have to be to be bound with SQLITE_STATIC? If it
won't change until the call to sqlite3_step, is that enough? How about
until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
enough, I assume that if it doesn't change before the sqlite3_finalize, it
must be safe to use SQLITE_STATIC?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users