Re: [sqlite] how to reuse a prepared statement

2011-04-23 Thread Teg 

I got bit by this the other day. I typically just used "reset" but, if
you have a case where you're looping through an insert and you don't
always set all values (say a string is empty sometimes so, you forgo
the bind), the previous value set will get inserted where you expected
blanks to go.

Clearly, it was my fault because I assumed reset cleared the old
values. I've since changed my wrapper to clear with each reset.


>> If
>> I understand things correctly, you call sqlite3_reset() to reuse a
>> prepaired statement, why do you call sqlite3_clear_bindings()?

IT> You don't have to. Personally, I've never once found a use for it. 


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

Re: [sqlite] how to reuse a prepared statement

2011-04-23 Thread Mihai Militaru 
On Sat, 23 Apr 2011 09:39:40 -0400
Sam Carleton  wrote:

> Can someone then explain the purpose of  sqlite3_clear_bindings()?  If
> I understand things correctly, you call sqlite3_reset() to reuse a
> prepaired statement, why do you call sqlite3_clear_bindings()?  Is it
> because sqlite3_reset() does not actually clear the binding values,
> just resets the preparied statement?  I have a feeling this is the
> case, so...
Yes,
take for example a SELECT * FROM table WHERE name='John' statement, for each 
step it gets one 'John'
row. At step n, it will give you the n-th 'John' row, but if you reset it, 
you'll get the first
'John' row again, though it's still 'John' that was bound to the statement, it 
won't change (to
the default NULL) unless you call clear bindings.

> > I understand things correctly, you call sqlite3_reset() to reuse a
> > prepaired statement, why do you call sqlite3_clear_bindings()?
> 
> You don't have to. Personally, I've never once found a use for it.
I now realize that I used it several times for no good reason. :) However, I 
think it's safer to
use it, it's easier to debug a problem involving an unwelcome NULL than an 
arbitrary incorrect
entry, in case something goes wrong.

-- 
Mihai Militaru 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to reuse a prepared statement

2011-04-23 Thread Igor Tandetnik 
Sam Carleton  wrote:
> On Fri, Apr 22, 2011 at 11:44 PM, Drake Wilson  wrote:
> 
>> You probably need to sqlite3_reset the statement after stepping it.
> 
> Can someone then explain the purpose of  sqlite3_clear_bindings()?

sqlite3_reset preserves previous bindings. sqlite3_clear_bindings resets all 
bound parameters to their default value of NULL, if for some reason you want to 
do that.

> If
> I understand things correctly, you call sqlite3_reset() to reuse a
> prepaired statement, why do you call sqlite3_clear_bindings()?

You don't have to. Personally, I've never once found a use for it. 

> Is it
> because sqlite3_reset() does not actually clear the binding values,
> just resets the preparied statement?

That's correct.

> Why doesn't reset also clear the binding?

In case you want to run the statement several times varying some, but not all, 
parameters.
-- 
Igor Tandetnik

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

Re: [sqlite] how to reuse a prepared statement

2011-04-23 Thread Sam Carleton 
On Fri, Apr 22, 2011 at 11:44 PM, Drake Wilson  wrote:

> You probably need to sqlite3_reset the statement after stepping it.

Can someone then explain the purpose of  sqlite3_clear_bindings()?  If
I understand things correctly, you call sqlite3_reset() to reuse a
prepaired statement, why do you call sqlite3_clear_bindings()?  Is it
because sqlite3_reset() does not actually clear the binding values,
just resets the preparied statement?  I have a feeling this is the
case, so...

One thing I have learned (especially on this list) is that when
something appears strange, there is ALWAYS a good reason for it, so...

Why doesn't reset also clear the binding?

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to reuse a prepared statement

2011-04-22 Thread Drake Wilson 
Quoth Sam Carleton , on 2011-04-22 23:36:04 -0400:
> I am implementing a dataset update process.  There is a for loop going
> through the dataset either doing an update on the row or deleting the
> row.  So there are two statements that are preparied, currently I am
> using the same basic logic for both.  The problem is the second time
> around I get a SQLITE_MISUSE.
>
> What am I doing wrong?

You probably need to sqlite3_reset the statement after stepping it.

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users