Re: [sqlite] 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  wrote:

>
> On 28 May 2017, at 12:14pm, Gwendal Roué  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 
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

[Simon Slavin]

On 28 May 2017, at 12:14pm, Gwendal Roué  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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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é 
wrote:

>
> > Le 28 mai 2017 à 13:24, Bart Smissaert  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

[Gwendal Roué]

> Le 28 mai 2017 à 13:24, Bart Smissaert  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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é 
wrote:

> Hello Bart,
>
> > Le 28 mai 2017 à 13:03, Bart Smissaert  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

[Gwendal Roué]

Hello Bart,

> Le 28 mai 2017 à 13:03, Bart Smissaert  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_reset nor sqlite3_clear_bindings are clearing the auxdata when using sqlite3_set_auxdata

[Igor Tandetnik]

Philip Van Hoof  wrote:
> When we use our custom SQLite function function_sparql_regex (lower in
> this E-mail) together with bound values for the argvs of the function,
> then sqlite3_reset nor sqlite3_clear_bindings are clearning the
> auxdata. 
> 
> This makes it impossible to pass the regex as a sqlite3_bind_text (the
> same regex would be used even if you'd pass a new regex string).

You are supposed to handle cache invalidation yourself in the function. Store 
the original regex string together with its compiled form, compare the incoming 
regex with the cached one to see whether you can reuse the cached value or need 
to recompile.

Igor Tandetnik

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

[sqlite] sqlite3_reset nor sqlite3_clear_bindings are clearing the auxdata when using sqlite3_set_auxdata

[Philip Van Hoof]

Hi there,

When we use our custom SQLite function function_sparql_regex (lower in
this E-mail) together with bound values for the argvs of the function,
then sqlite3_reset nor sqlite3_clear_bindings are clearning the auxdata.

This makes it impossible to pass the regex as a sqlite3_bind_text (the
same regex would be used even if you'd pass a new regex string).

The sqlite3_set_auxdata's destroy function ptr is called when you'd
sqlite3_finalize the stmt each time, of course. But then it's not
possible to cache our statements as efficiently as we are doing right
now.

Here's an example that reproduces it:

static sqlite *db = NULL;
static const *filename = "test.db";
static sqlite3_stmt *stmt = NULL;

static void
open_db (void)
{
  sqlite3_open (filename, ) != SQLITE_OK);
  sqlite3_create_function (db, "SparqlRegex", 3, SQLITE_ANY,
   priv, _sparql_regex, 
   NULL, NULL);
}

static void
exec_regex (char *regex, char *mod)
{
  if (!stmt) {
sqlite3_prepare_v2 (db, "SELECT field FROM Table"
"WHERE ... AND ... "
"SparqlRegex (field, ?, ?)",
   -1, , NULL);
  } else {
sqlite3_reset (stmt);
sqlite3_clear_bindings (stmt);
  }
  sqlite3_bind_text (stmt, 0, regex, -1, SQLITE_TRANSIENT);
  sqlite3_bind_text (stmt, 1, mod, -1, SQLITE_TRANSIENT);
  ...
  sqlite3_step () ...
  ...
}

static void
app (void)
{
  open_db ();
  exec_regex (".*", "i");
  exec_regex ("foo", "i");
}

-

static void
function_sparql_regex (sqlite3_context *context,
   int  argc,
   sqlite3_value   *argv[])
{
  gboolean ret;
  const gchar *text, *pattern, *flags;
  GRegexCompileFlags regex_flags;
  GRegex *regex;

  if (argc != 3) {
sqlite3_result_error (context, “Invalid argument count”, -1);
return;
  }

  regex = sqlite3_get_auxdata (context, 1);
  text = sqlite3_value_text (argv[0]);
  flags = sqlite3_value_text (argv[2]);
  if (regex == NULL) {
gchar *err_str;
GError *error = NULL;
pattern = sqlite3_value_text (argv[1]);
regex_flags = 0;
while (*flags) {
  switch (*flags) {
  case ’s’: regex_flags |= G_REGEX_DOTALL; break;
  case ‘m’: regex_flags |= G_REGEX_MULTILINE; break;
  case ‘i’: regex_flags |= G_REGEX_CASELESS; break;
  case ‘x’: regex_flags |= G_REGEX_EXTENDED; break;
  default:
err_str = g_strdup_printf (”Invalid SPARQL regex flag ‘%c’”, *flags);
sqlite3_result_error (context, err_str, -1);
g_free (err_str);
return;
  }
  flags++;
}
regex = g_regex_new (pattern, regex_flags, 0, );
if (error) {
  sqlite3_result_error (context, error->message, error->code);
  g_clear_error ();
  return;
}
sqlite3_set_auxdata (context, 1, regex, (void (*) (void*)) g_regex_unref);
  }
  ret = g_regex_match (regex, text, 0, NULL);
  sqlite3_result_int (context, ret);
  return;
}

-- 


Philip Van Hoof
freelance software developer
Codeminded BVBA - http://codeminded.be

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