Re: [sqlite] library routine called out of sequence

[Michael Stephenson]

Assuming that you are calling reset after each use of a prepared statement
and/or that the error occurs on the very first bind...

This sounds like in the working case the statements are prepared, bound, and
then reset by the same thread each time these steps are done, but perhaps in
the non-working case the statements are prepared in one thread and then
bound in different threads (perhaps even on the first call to bind).  I
think that might be your issue.

Which version of SQLite are you using, and what OS?  Is thread support
compiled in to your SQLite instance?

Also, could two threads be attempting to bind the same statement at the same
time?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP
Sent: Friday, December 23, 2011 9:06 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] library routine called out of sequence

Hi,

yes, I know. I have a multithreaded app. One db conection.I will try to
reproduce the problem with a simple app.

However, the difference between the working and non working case is that in
the working case statements are prepared each time. In the non working case,
its reused.

The very first call to bind throws this error.

Sreekumar
On Dec 23, 2011 2:51 PM, "Pavel Ivanov"  wrote:
>
> Devil is in the details. What you described in general is okay. But I 
> guess the exact code you have has some problems. To find them we 
> should see the code.
>
> An just some first check: do you have a single-threaded application 
> accessing database?
>
>
> Pavel
>
>
> On Fri, Dec 23, 2011 at 3:32 AM, Sreekumar TP 
wrote:
> > Hi ,
> >
> > I have a strange problem. I do the following in sequence -
> >
> > 1. open database
> > 2. set pragmas
> > 3.create table
> > 4. prepare a paremetrised statement ( SELECT ..) 5. bind values to 
> > the statement 6. step
> >
> > At 5, I get the error  "library routine called out of sequence".
> >
> > If I replace the parameterised statement  with a 'non-parameterised'
> > statement, I have no issues.
> >
> > If I use a parameterized INSERT statement , I do not get the error.
> >
> > Any suggestions?
> >
> > -Sreekumar
> > ___
> > 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

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

Re: [sqlite] Parameters are not allowed in views

[Petite Abeille]

On Dec 23, 2011, at 2:31 PM, Chris Mets wrote:

> A parameterized view allows me to do that just fine in other SQL engines,

Well, MSSQL sports so-called parameterized views, but that's about it.

> but apparently not SQLite. 

If you insist on that approach, you could rewrite your view in term of a 
parameter table, as pointed out by Kees Nuyt somewhere else in this thread.

So:

(1) create temporary table parameter( value )

(2) create view foo as select bar from baz where bar = (select value from 
parameter)

(3) insert into parameter( value ) values( 'fubar' )

(4) select * from foo

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

Re: [sqlite] : about add sqlite database cloumn

[Kit]

2011/12/23 YAN HONG YE :
> I have a question about C binding for sqlite, I have a table like this:
>
> Name    Price1  Price2  Sum
> A1      23      231
> A2      22      12
> A3      21      223
>
> how to use functin
>  int  myfunc() {
> int tt=0;
> if (price1>2) tt++;
> if (price2>1) tt++;
> if (price2>12) tt++;
> return tt
> }
>
> to put function result into  my table last added cloumn use sqlite in c code?

SELECT (price1>2)+(price2>1)+(price2>12) AS tt FROM table;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Peter Aronson]

Another possibility might be to create a parameters table, say:

CREATE TABLE tabparams (p1,p2,p2,p4,p5);
INSERT INTO tabparams VALUES (null,null,null,null,null);

And when creating the view, access tabparams.p1, tabparams.p2, etc. instead
of variables (with an appropriate join clause).  Then, before accessing the
view, update the tabparams table with the values you want.

This may be more expensive, depending on the optimizer, since SQLite will no
longer be dealing with a constant but rather a join.

A more elaborate method would be to program a pair of parameter_set and
parameter_get functions in C.

Integer parameter_set (integer parameter_no, value pvalue)
Value parameter_get (integer parameter_no)

The parameter values would actually be stored during the session in the
application data for the sqlite3_create_function_v2 function, and deleted at
the session end by the destroy function you pass to it.

This has the possible advantage that I think functions with constant
arguments are treated themselves as constants.

This has the disadvantage that software that didn't load your functions
would not be able to access the views at all.

Best regards,

Peter

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

Re: [sqlite] Parameters are not allowed in views

[Chris Mets]

Hi Kees, Thank you for the input.  I had your first workaround in mind if I 
cannot get the real parameterized views to work. It still achieves the goal of 
keeping the sql logic in the database, and only make the logic in the code a 
little less elegant (string substitution instead of sql parameter 
substitution). Thanks & Best regards, Chris > From: k.n...@zonnet.nl
> To: sqlite-users@sqlite.org
> Date: Fri, 23 Dec 2011 18:03:16 +0100
> Subject: Re: [sqlite] Parameters are not allowed in views
> 
> On Fri, 23 Dec 2011 06:31:33 -0700, Chris Mets 
> wrote:
> 
> >
> > Thanks for the response.  In the solution you propose,
> > the view is no longer a parameterized view. I assume
> > you suggest putting the select statement with the
> > paramterized where clause in the code. In my question,
> > I simplified the query. In reality, it is a huge query
> > (view with left join to two sub-views). I prefer to
> > avoid embedded that SQL complexity into my code.
> > A parameterized view allows me to do that just fine
> > in other SQL engines, but apparently not SQLite.
> 
> I can think of a few workarounds:
> 
> In an application, it might be an option to create the view with fancy
> values as placeholders like par1,par2 instead of ? in the WHERE clause.
> When you want to use it, retrieve the view from sqlite_master(sql),
> strip 'CREATE VIEW viewname AS' from the sql string, replace the fancy
> values by the correct placeholders and then run it.
> 
> 
> Some SQLite browsers allow a user to right click on a text cell in a
> result set grid and select 'execute as sql' from the contextmenu. It
> will then prompt for actual values for any placeholders.
> The text can be anything, even a select statement with placeholders.
> 
> I know sqlite3explorer (by Mike Cariotoglou) allows this.
> 
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
> 
> In some use cases I offered a "Search" table with end user descriptions
> and sql parameter statements which could easily be used as described
> above.
> 
> 
> -- 
> Regards,
> 
> Kees Nuyt
> 
> ___
> 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] : about add sqlite database cloumn

[Kees Nuyt]

On Fri, 23 Dec 2011 04:40:53 +, YAN HONG YE 
wrote:

>I have a question about C binding for sqlite, I have a table like this:
>
>Name   Price1  Price2  Sum
>A1 23  231  
>A2 22  12   
>A3 21  223  
>
>how to use functin
> int  myfunc()
>{
>int tt=0;
>if (price1 >2)
>tt++;
>if (price2>1)
>tt++;
>if (price2>12)
>tt++;
>
>...
>return tt
>
>}
>
> to put function result into  my table last added
> column use sqlite in c code?

To use SQLite from C, you have to use the C API.

http://www.sqlite.org/c3ref/funclist.html

Here are a few examples:

http://www.sqlite.org/cvstrac/wiki?p=SimpleCode

http://icculus.org/~chunky/stuff/sqlite3_example/sqlite3_example_bind.c

Assuming tt is what you would want to see in the sum column, there is no
need to use C:

CREATE TABLE t1 (
NameTEXT,
Price1  INTEGER,
Price2  INTEGER
);
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A1',23,231); -- 3
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A2',22,12);  -- 2
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A3',21,223); -- 3
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A4',1,1);-- 0
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A5',1,2);-- 1
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A6',1,13);   -- 2
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A7',3,1);-- 1
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A8',3,2);-- 2
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A9',3,13);   -- 3

CREATE VIEW t1_with_sum AS
  SELECT Name,
Price1,
Price2,
(Price1 > 2) + (Price2 > 1) + (Price2 > 12) AS Sum
   FROM t1
ORDER BY Name;
.headers on
SELECT * FROM t1_with_sum;

outputs:

Name|Price1|Price2|Sum
A1|23|231|3
A2|22|12|2
A3|21|223|3
A4|1|1|0
A5|1|2|1
A6|1|13|2
A7|3|1|1
A8|3|2|2
A9|3|13|3


Note: the sum column should not be part of the table, as it is a derived
value. If you really want to add that column, you can populate it with: 

UPDATE t1 set Sum=(Price1 > 2) + (Price2 > 1) + (Price2 > 12);

SELECT * FROM t1 ORDER BY Name;
(same output)

Hope this helps.

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Parameters are not allowed in views

[Kees Nuyt]

On Fri, 23 Dec 2011 06:31:33 -0700, Chris Mets 
wrote:

>
> Thanks for the response.  In the solution you propose,
> the view is no longer a parameterized view. I assume
> you suggest putting the select statement with the
> paramterized where clause in the code. In my question,
> I simplified the query. In reality, it is a huge query
> (view with left join to two sub-views). I prefer to
> avoid embedded that SQL complexity into my code.
> A parameterized view allows me to do that just fine
> in other SQL engines, but apparently not SQLite.

I can think of a few workarounds:

In an application, it might be an option to create the view with fancy
values as placeholders like par1,par2 instead of ? in the WHERE clause.
When you want to use it, retrieve the view from sqlite_master(sql),
strip 'CREATE VIEW viewname AS' from the sql string, replace the fancy
values by the correct placeholders and then run it.


Some SQLite browsers allow a user to right click on a text cell in a
result set grid and select 'execute as sql' from the contextmenu. It
will then prompt for actual values for any placeholders.
The text can be anything, even a select statement with placeholders.

I know sqlite3explorer (by Mike Cariotoglou) allows this.

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

In some use cases I offered a "Search" table with end user descriptions
and sql parameter statements which could easily be used as described
above.


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] library routine called out of sequence

[Sreekumar TP]

I am 99% certain that it is the very first call. unless the step and reset
executed once before.(the bind , step, reset is in a loop). I will double
check this.

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

Re: [sqlite] library routine called out of sequence

[Dan Kennedy]

On 12/23/2011 09:06 PM, Sreekumar TP wrote:

Hi,

yes, I know. I have a multithreaded app. One db conection.I will try to
reproduce the problem with a simple app.

However, the difference between the working and non working case is that in
the working case statements are prepared each time. In the non working
case, its reused.

The very first call to bind throws this error.


Usually indicates the statement has not been reset. Calls
to bind functions return an error if sqlite3_step() has been
called on the statement more recently than sqlite3_reset().

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

Re: [sqlite] library routine called out of sequence

[Pavel Ivanov]

On Fri, Dec 23, 2011 at 9:06 AM, Sreekumar TP  wrote:
> Hi,
>
> yes, I know. I have a multithreaded app. One db conection.I will try to
> reproduce the problem with a simple app.
>
> However, the difference between the working and non working case is that in
> the working case statements are prepared each time. In the non working
> case, its reused.
>
> The very first call to bind throws this error.

Are you sure it's the very first call to bind? If difference between
working and non-working is reusing statements then it's probably not
the first bind, but first after statement was already executed. Are
you sure that you call sqlite3_reset on reused statement?


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

Re: [sqlite] Parameters are not allowed in views

[Simon Slavin]

On 23 Dec 2011, at 1:31pm, Chris Mets wrote:

> Thanks for the response.  In the solution you propose, the view is no longer 
> a parameterized view. I asume you suggest putting the select statement with 
> the paramterized where clause in the code. In my question, I simplified the 
> query. In reality, it is a huge query (view with left join to two sub-views). 
> I prefer to avoid embedded that SQL complexity into my code. A parameterized 
> view allows me to do that just fine in other SQL engines, but apparently not 
> SQLite. 

I know the syntax diagrams aren't definitive, but the syntax diagram on



doesn't suggest that any values are forbidden.  I have no trouble running 
_exec() on this statement:

CREATE VIEW theThrees AS SELECT second FROM myTable WHERE first=3

so I don't see why it shouldn't work.  Perhaps the problem is with binding.

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

Re: [sqlite] library routine called out of sequence

[Sreekumar TP]

Hi,

yes, I know. I have a multithreaded app. One db conection.I will try to
reproduce the problem with a simple app.

However, the difference between the working and non working case is that in
the working case statements are prepared each time. In the non working
case, its reused.

The very first call to bind throws this error.

Sreekumar
On Dec 23, 2011 2:51 PM, "Pavel Ivanov"  wrote:
>
> Devil is in the details. What you described in general is okay. But I
> guess the exact code you have has some problems. To find them we
> should see the code.
>
> An just some first check: do you have a single-threaded application
> accessing database?
>
>
> Pavel
>
>
> On Fri, Dec 23, 2011 at 3:32 AM, Sreekumar TP 
wrote:
> > Hi ,
> >
> > I have a strange problem. I do the following in sequence -
> >
> > 1. open database
> > 2. set pragmas
> > 3.create table
> > 4. prepare a paremetrised statement ( SELECT ..)
> > 5. bind values to the statement
> > 6. step
> >
> > At 5, I get the error  "library routine called out of sequence".
> >
> > If I replace the parameterised statement  with a 'non-parameterised'
> > statement, I have no issues.
> >
> > If I use a parameterized INSERT statement , I do not get the error.
> >
> > Any suggestions?
> >
> > -Sreekumar
> > ___
> > 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

[sqlite] canonical new u1db and sqlite

[sqlite-us...@h-rd.org]

Hi,

Canonical ha released a first prototype of u1db, a sqlite based  
document db which is syncable.  Somehow this reminds me of a  
combination of the fossil db model and unql and unqlspec.  Is there a  
relation between the projects?


thanks,



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

Re: [sqlite] library routine called out of sequence

[Pavel Ivanov]

Devil is in the details. What you described in general is okay. But I
guess the exact code you have has some problems. To find them we
should see the code.

An just some first check: do you have a single-threaded application
accessing database?


Pavel


On Fri, Dec 23, 2011 at 3:32 AM, Sreekumar TP  wrote:
> Hi ,
>
> I have a strange problem. I do the following in sequence -
>
> 1. open database
> 2. set pragmas
> 3.create table
> 4. prepare a paremetrised statement ( SELECT ..)
> 5. bind values to the statement
> 6. step
>
> At 5, I get the error  "library routine called out of sequence".
>
> If I replace the parameterised statement  with a 'non-parameterised'
> statement, I have no issues.
>
> If I use a parameterized INSERT statement , I do not get the error.
>
> Any suggestions?
>
> -Sreekumar
> ___
> 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] Parameters are not allowed in views

[Chris Mets]

Thanks for the response.  In the solution you propose, the view is no longer a 
parameterized view. I asume you suggest putting the select statement with the 
paramterized where clause in the code. In my question, I simplified the query. 
In reality, it is a huge query (view with left join to two sub-views). I prefer 
to avoid embedded that SQL complexity into my code. A parameterized view allows 
me to do that just fine in other SQL engines, but apparently not SQLite. 
> From: petite.abei...@gmail.com
> Date: Thu, 22 Dec 2011 00:00:30 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Parameters are not allowed in views
> 
> 
> On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:
> 
> > Is this truly a limitation of SQLite or am I doing something wrong?
> 
> The later. Simply create your view. Then restrict it.
> 
> In other words:
> 
> create view foo as select bar from baz
> 
> select * from foo where bar = ?
> 
> ___
> 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] SQL help

[Paul Sanderson]

Thanks all, dates are stored internally as integers

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

[sqlite] library routine called out of sequence

[Sreekumar TP]

Hi ,

I have a strange problem. I do the following in sequence -

1. open database
2. set pragmas
3.create table
4. prepare a paremetrised statement ( SELECT ..)
5. bind values to the statement
6. step

At 5, I get the error  "library routine called out of sequence".

If I replace the parameterised statement  with a 'non-parameterised'
statement, I have no issues.

If I use a parameterized INSERT statement , I do not get the error.

Any suggestions?

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