sqlite3_reset and sqlite3_clear_bindings

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_reset and sqlite3_clear_bindings

Bart Smissaert
Using SQLite3 3.19.0 on a Windows machine.
I have some general questions about sqlite3_reset and
sqlite3_clear_bindings:
I am processing data from a 2D variant array (this is VB6).

1. I understand that after processing a row (binding all the values in a
row of that variant array)
I need to do either sqlite3_clear_bindings or make sure the next row has
all the values bound
either with a value (eg with sqlite3_bind_int) or with sqlite3_bind_null.
Now if I am sure that there are always values to be bound (so I will never
need sqlite3_bind_null) then I don't need sqlite3_clear_bindings. If there
are empty array elements however then I could either do sqlite3_bind_null
or always do sqlite3_clear_bindings after processing a row.
In general what would be the most efficient approach? I suppose the only
way to find out is testing, but maybe somebody can shred some light on this.

2. Is there any difference in the order of doing sqlite3_reset and
sqlite3_clear_bindings?

3. Is there any point in checking the return value of
sqlite3_clear_bindings, especially if it was
already preceded by a successful sqlite3_reset?

4. Is there any point in checking the return value of sqlite3_reset if
there was a successful
sqlite3_bind_XXX preceding it?

Thanks for any advice.


RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_reset and sqlite3_clear_bindings

Gwendal Roué-2
Hello Bart,

> Le 28 mai 2017 à 13:03, Bart Smissaert <[hidden email]> a écrit :
>
> Using SQLite3 3.19.0 on a Windows machine.
> I have some general questions about sqlite3_reset and
> sqlite3_clear_bindings:
> I am processing data from a 2D variant array (this is VB6).
>
> 1. I understand that after processing a row (binding all the values in a
> row of that variant array)
> I need to do either sqlite3_clear_bindings or make sure the next row has
> all the values bound
> either with a value (eg with sqlite3_bind_int) or with sqlite3_bind_null.
> Now if I am sure that there are always values to be bound (so I will never
> need sqlite3_bind_null) then I don't need sqlite3_clear_bindings. If there
> are empty array elements however then I could either do sqlite3_bind_null
> or always do sqlite3_clear_bindings after processing a row.
> In general what would be the most efficient approach? I suppose the only
> way to find out is testing, but maybe somebody can shred some light on this.

You are responsible for binding correct values before executing any statement. Calling sqlite3_clear_bindings does the same thing as calling sqlite3_bind_null for all arguments.

> 2. Is there any difference in the order of doing sqlite3_reset and
> sqlite3_clear_bindings?

I personnally call sqlite3_reset before sqlite3_clear_bingings with great success, but I don't know if the order is relevant or not.

> 3. Is there any point in checking the return value of
> sqlite3_clear_bindings, especially if it was
> already preceded by a successful sqlite3_reset?
>
> 4. Is there any point in checking the return value of sqlite3_reset if
> there was a successful
> sqlite3_bind_XXX preceding it?

3, 4: is there any point *not* checking a result code whenever you are given the opportunity to?

Of course you have to check it. The two functions perform a different job, and may fail for different reasons. For example, sqlite3_reset() will return an error if a previous execution of the statement has returned an error. I'm almost sure sqlite3_clear_bindings does not.

Happy SQLite :-)
Gwendal

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_reset and sqlite3_clear_bindings

Bart Smissaert
> Calling sqlite3_clear_bindings does the same thing as calling
sqlite3_bind_null for all arguments.

Yes, I understand that, just thinking about efficiency.

> I personnally call sqlite3_reset before sqlite3_clear_bingings with great
success

I am doing the same now. Probably no difference there

> is there any point *not* checking a result code whenever you are given
the opportunity to?

Yes, there is if there is no possible way in that particular situation that
the result could be other than success.
If there was a successful sqlite3_step just preceding it could a
sqlite3_reset possibly be unsuccessful?
If there was a successful sqlite3_reset just preceding it could a
sqlite3_clear_bindings possibly be unsuccessful?


RBS




On Sun, May 28, 2017 at 12:14 PM, Gwendal Roué <[hidden email]>
wrote:

> Hello Bart,
>
> > Le 28 mai 2017 à 13:03, Bart Smissaert <[hidden email]> a
> écrit :
> >
> > Using SQLite3 3.19.0 on a Windows machine.
> > I have some general questions about sqlite3_reset and
> > sqlite3_clear_bindings:
> > I am processing data from a 2D variant array (this is VB6).
> >
> > 1. I understand that after processing a row (binding all the values in a
> > row of that variant array)
> > I need to do either sqlite3_clear_bindings or make sure the next row has
> > all the values bound
> > either with a value (eg with sqlite3_bind_int) or with sqlite3_bind_null.
> > Now if I am sure that there are always values to be bound (so I will
> never
> > need sqlite3_bind_null) then I don't need sqlite3_clear_bindings. If
> there
> > are empty array elements however then I could either do sqlite3_bind_null
> > or always do sqlite3_clear_bindings after processing a row.
> > In general what would be the most efficient approach? I suppose the only
> > way to find out is testing, but maybe somebody can shred some light on
> this.
>
> You are responsible for binding correct values before executing any
> statement. Calling sqlite3_clear_bindings does the same thing as calling
> sqlite3_bind_null for all arguments.
>
> > 2. Is there any difference in the order of doing sqlite3_reset and
> > sqlite3_clear_bindings?
>
> I personnally call sqlite3_reset before sqlite3_clear_bingings with great
> success, but I don't know if the order is relevant or not.
>
> > 3. Is there any point in checking the return value of
> > sqlite3_clear_bindings, especially if it was
> > already preceded by a successful sqlite3_reset?
> >
> > 4. Is there any point in checking the return value of sqlite3_reset if
> > there was a successful
> > sqlite3_bind_XXX preceding it?
>
> 3, 4: is there any point *not* checking a result code whenever you are
> given the opportunity to?
>
> Of course you have to check it. The two functions perform a different job,
> and may fail for different reasons. For example, sqlite3_reset() will
> return an error if a previous execution of the statement has returned an
> error. I'm almost sure sqlite3_clear_bindings does not.
>
> Happy SQLite :-)
> Gwendal
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_reset and sqlite3_clear_bindings

Gwendal Roué-2

> Le 28 mai 2017 à 13:24, Bart Smissaert <[hidden email]> a écrit :
>
>> Calling sqlite3_clear_bindings does the same thing as calling
> sqlite3_bind_null for all arguments.
>
> Yes, I understand that, just thinking about efficiency.

Then I don't know. Your experience will tell.

>> I personnally call sqlite3_reset before sqlite3_clear_bingings with great
> success
>
> I am doing the same now. Probably no difference there

I suppose so.

>> is there any point *not* checking a result code whenever you are given
> the opportunity to?
>
> Yes, there is if there is no possible way in that particular situation that
> the result could be other than success.
> If there was a successful sqlite3_step just preceding it could a
> sqlite3_reset possibly be unsuccessful?
> If there was a successful sqlite3_reset just preceding it could a
> sqlite3_clear_bindings possibly be unsuccessful?

The documentation is your reference. If the documentation does not answer your questions, then you shouldn't assume anything, and take the only reasonable decision: check for errors whenever you can.

Gwendal

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_reset and sqlite3_clear_bindings

Bart Smissaert
The documentation (https://www.sqlite.org/c3ref/reset.html) seems to
suggest that sqlite3_reset can only be unsuccessful if sqlite3_step
returned an error.
Not sure about sqlite3_clear_bindings.
I will do some testing and see if there is any performance gain in leaving
these checks out. Very likely there is no relevant gain.

RBS

On Sun, May 28, 2017 at 12:28 PM, Gwendal Roué <[hidden email]>
wrote:

>
> > Le 28 mai 2017 à 13:24, Bart Smissaert <[hidden email]> a
> écrit :
> >
> >> Calling sqlite3_clear_bindings does the same thing as calling
> > sqlite3_bind_null for all arguments.
> >
> > Yes, I understand that, just thinking about efficiency.
>
> Then I don't know. Your experience will tell.
>
> >> I personnally call sqlite3_reset before sqlite3_clear_bingings with
> great
> > success
> >
> > I am doing the same now. Probably no difference there
>
> I suppose so.
>
> >> is there any point *not* checking a result code whenever you are given
> > the opportunity to?
> >
> > Yes, there is if there is no possible way in that particular situation
> that
> > the result could be other than success.
> > If there was a successful sqlite3_step just preceding it could a
> > sqlite3_reset possibly be unsuccessful?
> > If there was a successful sqlite3_reset just preceding it could a
> > sqlite3_clear_bindings possibly be unsuccessful?
>
> The documentation is your reference. If the documentation does not answer
> your questions, then you shouldn't assume anything, and take the only
> reasonable decision: check for errors whenever you can.
>
> Gwendal
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_reset and sqlite3_clear_bindings

Simon Slavin-3
In reply to this post by Bart Smissaert

On 28 May 2017, at 12:14pm, Gwendal Roué <[hidden email]> wrote:

> I personnally call sqlite3_reset before sqlite3_clear_bingings with great success, but I don't know if the order is relevant or not.

It makes more sense to move sqlite3_clear_bindings() to before you set parameters individually, including before the first time you call sqlite3_step().  There’s no need to call it just before sqlite3_finalize().  In fact, if you’re sure your code sets all the parameters in the statement there’s no need to call it at all.

On 28 May 2017, at 12:24pm, Bart Smissaert <[hidden email]> wrote:

> If there was a successful sqlite3_step just preceding it could a
> sqlite3_reset possibly be unsuccessful?
> If there was a successful sqlite3_reset just preceding it could a
> sqlite3_clear_bindings possibly be unsuccessful?

I can’t think of any cases where these would happen if everything was working as planned.  They might happen if something was wrong with your setup.  For instance, some buggy part of your program or OS might write over SQLite’s statement record.

But if you have code which has been thoroughly tested, and if timing or program space is so critical to you that checking an int to see if it’s SQLITE_OK takes too long, then yes, you might leave the check out.  But a single check for an integer’s exact value doesn’t take long.

From what I can see, you’re executing a long series of queries, each of which you expect to return zero or one row.  After each result you don’t need any other values which might result, so you execute sqlite3_reset().  There’s no problem with this, and it’s a standard way of operating.

sqlite3_clear_bindings() is just a quick way of making sure all parameters have legal (if not useful) values.  If you’re immediately going to set all the parameters yourself, then there’s no need to do it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_reset and sqlite3_clear_bindings

Bart Smissaert
> I can’t think of any cases where these would happen if everything was
working as planned.

I can't think of anything either and this is all fully tested, so I think I
can leave these checks out.

> From what I can see, you’re executing a long series of queries, each of
which you expect to return zero or one row.

No, this is moving data from a 2D variant array to a SQLite table, so these
are inserts with parameters.


RBS

On Sun, May 28, 2017 at 4:37 PM, Simon Slavin <[hidden email]> wrote:

>
> On 28 May 2017, at 12:14pm, Gwendal Roué <[hidden email]> wrote:
>
> > I personnally call sqlite3_reset before sqlite3_clear_bingings with
> great success, but I don't know if the order is relevant or not.
>
> It makes more sense to move sqlite3_clear_bindings() to before you set
> parameters individually, including before the first time you call
> sqlite3_step().  There’s no need to call it just before
> sqlite3_finalize().  In fact, if you’re sure your code sets all the
> parameters in the statement there’s no need to call it at all.
>
> On 28 May 2017, at 12:24pm, Bart Smissaert <[hidden email]>
> wrote:
>
> > If there was a successful sqlite3_step just preceding it could a
> > sqlite3_reset possibly be unsuccessful?
> > If there was a successful sqlite3_reset just preceding it could a
> > sqlite3_clear_bindings possibly be unsuccessful?
>
> I can’t think of any cases where these would happen if everything was
> working as planned.  They might happen if something was wrong with your
> setup.  For instance, some buggy part of your program or OS might write
> over SQLite’s statement record.
>
> But if you have code which has been thoroughly tested, and if timing or
> program space is so critical to you that checking an int to see if it’s
> SQLITE_OK takes too long, then yes, you might leave the check out.  But a
> single check for an integer’s exact value doesn’t take long.
>
> From what I can see, you’re executing a long series of queries, each of
> which you expect to return zero or one row.  After each result you don’t
> need any other values which might result, so you execute sqlite3_reset().
> There’s no problem with this, and it’s a standard way of operating.
>
> sqlite3_clear_bindings() is just a quick way of making sure all parameters
> have legal (if not useful) values.  If you’re immediately going to set all
> the parameters yourself, then there’s no need to do it.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users