[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[R.Smith]

On 2015-09-19 06:41 PM, gwenn wrote:
> SQLITE_SCHEMA is returned only on failure.
> But, here, there is not failure.
>
> Maybe I should not store the column count ?
> Maybe I should look at schema version
> (http://sqlite.org/pragma.html#pragma_schema_version):
> "The schema version is used by SQLite each time a query is executed to
> ensure that the internal cache of the schema used when compiling the
> SQL query matches the schema of the database against which the
> compiled query is actually executed."
> Is there a better solution ?

I too may be reading this thread wrong, I am not sure we're on the same 
page, but I /THINK/ maybe you are worrying about an unnecessary thing.
The only way the column count can change for a prepared statement 
object, is if you changed it.
If you have a prepared statement like "SELECT * FROM t..." then the 
definition might change once the SCHEMA has changed - but that will only 
have effect outside of the implicit transaction you are in, as in you 
will only notice when you reset the prepared statement - and you should 
always check column counts then.

The bit I am not sure about is whether, after a schema change and reset, 
whether that select statement will give an error or simply recompile 
with a new column count - but that can be checked rather easily and then 
respond accordingly.

Hope this makes some sense,
Ryan

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[Simon Slavin]

On 19 Sep 2015, at 5:41pm, gwenn  wrote:

> Is there a better solution ?

Why do you want to know when the statement has been recompiled while you're in 
the middle of processing a SELECT command ?  Are you doing something that might 
make you pause for a long time before executing your last sqlite3_step() ?

I'm not being funny.  I just think that if we knew what you were doing we'd 
find a solution for your situation which didn't involve answering your original 
question.

Simon.

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[Simon Slavin]

On 19 Sep 2015, at 5:41pm, gwenn  wrote:

> Is there a better solution ?

Why do you want to know when the statement has been recompiled while you're in 
the middle of processing a SELECT command ?  Are you doing something that might 
make you pause for a long time before executing your last sqlite3_step() ?  
Something which might plausibly last so long another process might have changed 
the schema ?

I'm not being funny.  I just think that if we knew what you were doing we'd 
find a solution for your situation which didn't involve answering your original 
question.

Simon.

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[gwenn]

SQLITE_SCHEMA is returned only on failure.
But, here, there is not failure.

Maybe I should not store the column count ?
Maybe I should look at schema version
(http://sqlite.org/pragma.html#pragma_schema_version):
"The schema version is used by SQLite each time a query is executed to
ensure that the internal cache of the schema used when compiling the
SQL query matches the schema of the database against which the
compiled query is actually executed."
Is there a better solution ?

Thanks.

On Sat, Sep 19, 2015 at 3:21 PM, Kees Nuyt  wrote:
> On Sat, 19 Sep 2015 11:29:37 +0200, gwenn 
> wrote:
>
>>Hello,
>>Is there any way to know when a prepared statement is recompiled ?
>>For example:
>>
>>rc = sqlite3_prepare_v2(db, "SELECT * FROM test", -1, &stmt, NULL);
>>...
>>cc = sqlite3_column_count(stmt);
>>...
>>rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data DEFAULT
>>'missing'", NULL, NULL, NULL);
>>...
>>rc = sqlite3_step(stmt);
>>// how to know that the column count is not good anymore ?
>
> http://www.sqlite.org/rescode.html#schema
>
> --
> Regards,
>
> Kees Nuyt
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[Kees Nuyt]

On Sat, 19 Sep 2015 11:29:37 +0200, gwenn 
wrote:

>Hello,
>Is there any way to know when a prepared statement is recompiled ?
>For example:
>
>rc = sqlite3_prepare_v2(db, "SELECT * FROM test", -1, &stmt, NULL);
>...
>cc = sqlite3_column_count(stmt);
>...
>rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data DEFAULT
>'missing'", NULL, NULL, NULL);
>...
>rc = sqlite3_step(stmt);
>// how to know that the column count is not good anymore ?

http://www.sqlite.org/rescode.html#schema

-- 
Regards,

Kees Nuyt

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[Nicolas Jäger]

Hi gwenn,
Maybe you are talking about something I don't understand, or I don't understand 
what you are
talking about...

I would like somebody tells if I'm wrong or right,
first in your email you have both :

> >>rc = sqlite3_prepare_v2(db, "SELECT * FROM test", -1, &stmt, NULL);
> >>...
> >>...
> >>rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data DEFAULT

I do not understand why you are doing that...

when you do :

>>cc = sqlite3_column_count(stmt);
in the documentation (https://www.sqlite.org/c3ref/column_count.html): 

  Return the number of columns in the result set returned by the prepared 
statement. This routine
  returns 0 if pStmt is an SQL statement that does not return data

so for me you have at least to do this :

  int i = sqlite3_step( stmt );

then calling `sqlite3_column_count()`

BUT you'll get something not null only if there is a result. Usually, what I'm 
doing when I call
`sqlite3_prepare()` is directly having a loop who inspects the statements :

  int i;

  while ( i!=SQLITE_DONE )
  {
i = sqlite3_step( stmt );

switch( i )
{

  case SQLITE_DONE:
  {
   ...
  }
  case SQLITE_ROW:
  {
// here I guess you have to call `qlite3_column_count()`
  }
  case SQLITE_SCHEMA:
  {
// here I guess the database schema has changed
  }
  ...
   }


> SQLITE_SCHEMA is returned only on failure.
I don't think this make sense. SQLITE_SCHEMA can't be a "failure" if is 
actually telling you it
succeded to change the schema...

or, it's a possibility, there is something I don't understand.


I don't know if I deleted some mail about this discussion, but more code would 
maybe helps to found
the heck...

regards,
nicolas

[sqlite] Handling the whole select query from an index

[Luiz Américo]

Em 19/09/2015 11:13, "Rob Willett" <
mail.robertwillett.com at postfix.robertwillett.com> escreveu:
>
> Ryan,
>
> Thanks. We?ve got 100 requests a second which may be enough. We?ll keep
looking though for any time.
>

Have you considered to implement the service using a compiled language,
configured to run through FastCGI?

I'm sure it can beat your current performance and such simple service can
be easily done even if you don't master the language.

There are lot of resources on net how to do with C, C++ or even Go.

In my side, i build web services using FreePascal with sqlite as db. It
runs in apache through CGI with Fastcgi as an option. Service like yours
can be implemented in a few minutes.

Luiz

> Rob.
>
> > On 18 Sep 2015, at 18:26, R.Smith  wrote:
> >
> > >>>Rob: "We want to do postal code ==> GPS..."
> >
> > >>Me: "You can use google apis..."
> >
> > >Rob: "Our business is GPS and GIS traffic data"
> >
> > Oops yes, that's a whole nother kettle of fish then. To return to some
of the older parts of the conversation, I think the SQL route is best
(whichever engine is chosen) - I am sure you will need to expand in future.
I hope you get the PERL latency sorted out.
> >
> > Best of luck!
> > Ryan
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Handling the whole select query from an index

[Rob Willett]

Ryan,

Thanks. We?ve got 100 requests a second which may be enough. We?ll keep looking 
though for any time.

Rob.

> On 18 Sep 2015, at 18:26, R.Smith  wrote:
> 
> >>>Rob: "We want to do postal code ==> GPS..."
> 
> >>Me: "You can use google apis..."
> 
> >Rob: "Our business is GPS and GIS traffic data"
> 
> Oops yes, that's a whole nother kettle of fish then. To return to some of the 
> older parts of the conversation, I think the SQL route is best (whichever 
> engine is chosen) - I am sure you will need to expand in future. I hope you 
> get the PERL latency sorted out.
> 
> Best of luck!
> Ryan
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[gwenn]

Hello,
Is there any way to know when a prepared statement is recompiled ?
For example:

rc = sqlite3_prepare_v2(db, "SELECT * FROM test", -1, &stmt, NULL);
...
cc = sqlite3_column_count(stmt);
...
rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data DEFAULT
'missing'", NULL, NULL, NULL);
...
rc = sqlite3_step(stmt);
// how to know that the column count is not good anymore ?

Regards.

[sqlite] Redundant link in documentation

[gwenn]

Maybe I should have sent a patch:

diff src/sqlite.h.in.bak src/sqlite.h.in
3629c3629
< ** [sqlite3_bind_parameter_index()].
---
> ** [sqlite3_bind_parameter_name()].

Regards.

On Tue, Aug 4, 2015 at 8:14 PM, gwenn  wrote:
> Hi,
> In the following page:
> http://sqlite.org/c3ref/bind_parameter_index.html
> there is a link to itself: sqlite3_bind_parameter_index().
>
> Maybe it should have been a link to:
> http://sqlite.org/c3ref/bind_parameter_name.html
> ?
> Regards.

[sqlite] Redundant link in documentation

[Simon Slavin]

On 19 Sep 2015, at 9:46am, gwenn  wrote:

> Maybe I should have sent a patch:
> 
> diff src/sqlite.h.in.bak src/sqlite.h.in
> 3629c3629
> < ** [sqlite3_bind_parameter_index()].
> ---
>> ** [sqlite3_bind_parameter_name()].

Actually the linktext is wrong too.  So, to summarise ...

In the 'See also' section of this page



, there's a link to that same page.  It should instead be instead a link to



Simon.

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[Nicolas Jäger]

Le Sat, 19 Sep 2015 15:21:44 +0200,
Kees Nuyt  a ?crit :

> On Sat, 19 Sep 2015 11:29:37 +0200, gwenn 
> wrote:
> 
> >Hello,
> >Is there any way to know when a prepared statement is recompiled ?
> >For example:
> >
> >rc = sqlite3_prepare_v2(db, "SELECT * FROM test", -1, &stmt, NULL);
> >...
> >cc = sqlite3_column_count(stmt);
> >...
> >rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data DEFAULT
> >'missing'", NULL, NULL, NULL);
> >...
> >rc = sqlite3_step(stmt);
> >// how to know that the column count is not good anymore ?
> 
> http://www.sqlite.org/rescode.html#schema
> 


not sure if I understand, but If you just add a column, 

  int i;

  while ( i!=SQLITE_DONE )
  {
i = sqlite3_step( stmt );

switch( i )
{

  case SQLITE_DONE :
  {
/* then the column was added so you implicitly know
   the count has changed : */
cc = sqlite3_column_count(stmt);
break;

  }


  default :
  {
std::cout << "(EE) " << i << " not expected!" << std::endl;
/*...*/
  }

}

  }

also I may have wrong but for me you are mixing `sqlite3_exec` and 
`sqlite3_prepare_v2`. For my
your strategy is wrong.

regards,
nicolas

[sqlite] Querying nextchar extension

[Charles Leifer]

I was thinking of dynamically building up the strings using the `nextchar`
extension but your example works just fine.

On Fri, Sep 18, 2015 at 9:52 AM, Igor Tandetnik  wrote:

> On 9/18/2015 9:05 AM, Charles Leifer wrote:
>
>> As a challenge I was curious how one would write a recursive CTE to take a
>> substring and recursively calculate all possible matches. I realize I
>> could
>> just use 'LIKE xxx%' to accomplish the same, but if anyone has any
>> thoughts
>> on writing such a query I'd love to hear them!
>>
>
> Something along these lines (untested):
>
> select group_concat(substr(word, length(:prefix)+1, 1), '')
> from words where substr(word, 1, length(:prefix)) = :prefix
>
> Not sure what recursive CTE has to do with it - what is there to recurse
> over?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>