Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Keith Medcalf]

> I/and the original implementer, am/were more familiar with PostgreSQL's
> MVCC.  So I think the issue was the assumption that the query being
> stepped through would only ever see the rows as they were at the start
> of the query and would walk through them from first to last.
> 
> Would
> PRAGMA journal_mode=WAL;
> provide that?

Yes. WAL journal mode provides "Repeatable Read" isolation within a 
transaction.  This means that if you issue:

BEGIN;
SELECT .
wait 3 hours with lots of intervening updates to all the tables in the database 
including the ones in your select above, done on a different connection.
SELECT .
COMMIT;
SELECT .

Assuming that all the selects are the same, then the first two selects will 
return identical results.  All the updates will not be seen until after the 
read transaction is committed, so the third select will see the updates done in 
the updates that occurred on different connections.

If you do not explicitly BEGIN and COMMIT transactions, then SQLite3 
effectively wraps each statement in its own transaction.  Note that without 
WAL, the above example will prohibit updates on other connections during the 3 
hour perios you are holding the transaction open since without WAL, readers 
block writers and writers block readers.

Even in WAL mode changes to the database made ON THE SAME CONNECTION are 
immediately visible to every statement on that connection.


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Reid Thompson
> Sent: Tuesday, 11 April, 2017 07:17
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] sqlite3_step and ORDER BY random() question.
> 
> On Tue, 2017-04-11 at 13:17 +0100, Simon Slavin wrote:
> > On 11 Apr 2017, at 12:33pm, Reid Thompson 
> wrote:
> >
> > > Does 'more complicated' in the below scenario mean that there is
> > > potential for records to be returned more than once if I use random()?
> >
> > The problem is not to do with random().  The problem is to do with
> modifying a field used in your SELECT command.  Suppose you have an index
> on the column "name" and you execute
> >
> > SELECT name FROM treetypes ORDER BY name
> >
> > with the following names
> >
> > apple
> > lemon
> > oak
> > orange
> > teak
> > willow
> >
> > You execute _step() twice, returning the top two, then issue
> >
> > UPDATE treetypes SET name="citrus" WHERE name="orange"
> >
> > What do you expect SQLite to do for the SELECT ?  Should it return a
> list of names which is clearly not in the order you asked for ?  Should it
> miss out one of the rows even though you asked for
> > them all ?  Should it return "orange" even though you have already
> changed that value ?
> >
> > That’s the "more complicated".  And for the sanity of people debugging
> your code it’s better to ensure it never happens.
> >
> > Simon.
> 
> Thanks again for the explanation.
> 
> > That’s the "more complicated".  And for the sanity of people debugging
> your code it’s better to ensure it never happens.
> 
> ;) yes. I'm debugging some inherited code.
> 
> ___
> 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_step and ORDER BY random() question.

[Richard Hipp]

On 4/11/17, Reid Thompson  wrote:
>
> I/and the original implementer, am/were more familiar with PostgreSQL's
> MVCC.  So I think the issue was the assumption that the query being
> stepped through would only ever see the rows as they were at the start
> of the query and would walk through them from first to last.
>

That is true, as long as the query and the workers are all using
separate database connections.  Trouble only arises when the query is
run partially - not to completion - and then the table being queried
is updated *on the same database connection*.  Changes implemented on
separate database connections are isolated and will not change the
result of the original query.

The problem scenario is impossible on PostgreSQL or any other
client-server database because the client/server databases run the
whole query all the way to the end before returning the results to
you.  It has nothing to do with MVCC.

You can emulate the PostgreSQL behavior by:

(1) Saving the query results in a TEMP table and then querying the
TEMP table separately and running the workers based on the TEMP table.

(2) Use the sqlite3_get_table() (or a similar wrapper of your own
concoction) to load all query results into memory prior to starting
the UPDATEs.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Simon Slavin]

On 11 Apr 2017, at 2:16pm, Reid Thompson  wrote:

> I/and the original implementer, am/were more familiar with PostgreSQL's
> MVCC.  So I think the issue was the assumption that the query being
> stepped through would only ever see the rows as they were at the start
> of the query and would walk through them from first to last.
> 
> Would
> PRAGMA journal_mode=WAL;
> provide that?

Your situation would be that you have a single connection to the database, and 
it’s being used by two statements at the same time ?

You’re beyond what I can promise.  I need someone else to answer that.

The answer might be different if the SELECT was using a different connection to 
the one which was modifying the table.  Again, I hope someone else can help.

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Reid Thompson]

On Tue, 2017-04-11 at 13:17 +0100, Simon Slavin wrote:
> On 11 Apr 2017, at 12:33pm, Reid Thompson  wrote:
> 
> > Does 'more complicated' in the below scenario mean that there is
> > potential for records to be returned more than once if I use random()?
> 
> The problem is not to do with random().  The problem is to do with modifying 
> a field used in your SELECT command.  Suppose you have an index on the column 
> "name" and you execute
> 
> SELECT name FROM treetypes ORDER BY name
> 
> with the following names
> 
> apple
> lemon
> oak
> orange
> teak
> willow
> 
> You execute _step() twice, returning the top two, then issue
> 
> UPDATE treetypes SET name="citrus" WHERE name="orange"
> 
> What do you expect SQLite to do for the SELECT ?  Should it return a list of 
> names which is clearly not in the order you asked for ?  Should it miss out 
> one of the rows even though you asked for
> them all ?  Should it return "orange" even though you have already changed 
> that value ?
> 
> That’s the "more complicated".  And for the sanity of people debugging your 
> code it’s better to ensure it never happens.
> 
> Simon.

Thanks again for the explanation. 

> That’s the "more complicated".  And for the sanity of people debugging your 
> code it’s better to ensure it never happens.

;) yes. I'm debugging some inherited code.

I/and the original implementer, am/were more familiar with PostgreSQL's
MVCC.  So I think the issue was the assumption that the query being
stepped through would only ever see the rows as they were at the start
of the query and would walk through them from first to last.

Would
PRAGMA journal_mode=WAL;
provide that?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Reid Thompson]

> 
> Thank you.  I'll make adjustments to my process.  One follow on
> question.  This would be a concern regardless of whether random() is
> used or not in the ORDER BY clause? 
> 
> reid

sorry - I posted this before receiving/seeing Simon's response.

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Reid Thompson]

On Tue, 2017-04-11 at 07:39 -0400, Richard Hipp wrote:
> On 4/11/17, Reid Thompson  wrote:
> > On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote:
> > > On 10 Apr 2017, at 9:28pm, Reid Thompson  wrote:
> > > 
> > > > my questions are, if I prepare and utilize a statement for a result set
> > > > in the tens of thousands (or more) using a where clause along the lines
> > > > of
> > > >    "ORDER BY batch_id, due_datetime, random()"
> > > > 
> > > > 1) As I sqlite3_step through the result set, am I guaranteed to get
> > > > each
> > > > row only once?
> > > 
> > > Yes.  _step() will return each row exactly once.  This assumes you will
> > > not make any changes to the table those rows are in until you have
> > > finished stepping.  If you make any changes to the table
> > > before the last call to _step() then things may get more complicated.
> > 
> > 
> > I have a manager process that manages the above step'ing. It gathers
> > records in batches of 30, marks them as claimed, and forwards those
> > batches to one of 50 worker processes. Each worker process performs work
> > based on each records data and when complete provides that information
> > back to the manager along with the record data.  The manager process
> > uses the returned information to update the records.  When a worker
> > completes a batche, the manager process sends them another batch. So
> > in most cases, I'm performing two updates to the record while step'ing
> > through the result set.
> 
> If you make changes to the table being queried in the middle of the
> query, then there are no guarantees.
> 
> In your case, you are *probably* ok (for now) because the ORDER BY is
> probably not being accomplished using an index.  But if batch_id and
> due_datetime are both NOT NULL and unique, then the third ORDER BY
> term will be ignored, and the UNIQUE index will be used to order the
> output.  And in that case, if any of your worker threads modify the
> batch_id or due_datetime fields, then there could be issues.
> 
> A safe work-around is to run the query into a TEMP table:
> 
>    CREATE TEMP TABLE work_to_do AS SELECT .
> 
> Then query the work_to_do table for stuff to be modified.

Thank you.  I'll make adjustments to my process.  One follow on
question.  This would be a concern regardless of whether random() is
used or not in the ORDER BY clause? 

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Simon Slavin]

On 11 Apr 2017, at 12:33pm, Reid Thompson  wrote:

> Does 'more complicated' in the below scenario mean that there is
> potential for records to be returned more than once if I use random()?

The problem is not to do with random().  The problem is to do with modifying a 
field used in your SELECT command.  Suppose you have an index on the column 
"name" and you execute

SELECT name FROM treetypes ORDER BY name

with the following names

apple
lemon
oak
orange
teak
willow

You execute _step() twice, returning the top two, then issue

UPDATE treetypes SET name="citrus" WHERE name="orange"

What do you expect SQLite to do for the SELECT ?  Should it return a list of 
names which is clearly not in the order you asked for ?  Should it miss out one 
of the rows even though you asked for them all ?  Should it return "orange" 
even though you have already changed that value ?

That’s the "more complicated".  And for the sanity of people debugging your 
code it’s better to ensure it never happens.

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Richard Hipp]

On 4/11/17, Reid Thompson  wrote:
> On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote:
>> On 10 Apr 2017, at 9:28pm, Reid Thompson  wrote:
>>
>> > my questions are, if I prepare and utilize a statement for a result set
>> > in the tens of thousands (or more) using a where clause along the lines
>> > of
>> >"ORDER BY batch_id, due_datetime, random()"
>> >
>> > 1) As I sqlite3_step through the result set, am I guaranteed to get
>> > each
>> > row only once?
>>
>> Yes.  _step() will return each row exactly once.  This assumes you will
>> not make any changes to the table those rows are in until you have
>> finished stepping.  If you make any changes to the table
>> before the last call to _step() then things may get more complicated.
>
>
> I have a manager process that manages the above step'ing. It gathers
> records in batches of 30, marks them as claimed, and forwards those
> batches to one of 50 worker processes. Each worker process performs work
> based on each records data and when complete provides that information
> back to the manager along with the record data.  The manager process
> uses the returned information to update the records.  When a worker
> completes a batche, the manager process sends them another batch. So
> in most cases, I'm performing two updates to the record while step'ing
> through the result set.

If you make changes to the table being queried in the middle of the
query, then there are no guarantees.

In your case, you are *probably* ok (for now) because the ORDER BY is
probably not being accomplished using an index.  But if batch_id and
due_datetime are both NOT NULL and unique, then the third ORDER BY
term will be ignored, and the UNIQUE index will be used to order the
output.  And in that case, if any of your worker threads modify the
batch_id or due_datetime fields, then there could be issues.

A safe work-around is to run the query into a TEMP table:

   CREATE TEMP TABLE work_to_do AS SELECT .

Then query the work_to_do table for stuff to be modified.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Reid Thompson]

On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote:
> On 10 Apr 2017, at 9:28pm, Reid Thompson  wrote:
> 
> > my questions are, if I prepare and utilize a statement for a result set
> > in the tens of thousands (or more) using a where clause along the lines
> > of
> >    "ORDER BY batch_id, due_datetime, random()"
> > 
> > 1) As I sqlite3_step through the result set, am I guaranteed to get each
> > row only once?
> 
> Yes.  _step() will return each row exactly once.  This assumes you will not 
> make any changes to the table those rows are in until you have finished 
> stepping.  If you make any changes to the table
> before the last call to _step() then things may get more complicated.

Thank you Simon.

so things may get more complicated:

Does 'more complicated' in the below scenario mean that there is
potential for records to be returned more than once if I use random()?

I have a manager process that manages the above step'ing. It gathers
records in batches of 30, marks them as claimed, and forwards those
batches to one of 50 worker processes. Each worker process performs work
based on each records data and when complete provides that information
back to the manager along with the record data.  The manager process
uses the returned information to update the records.  When a worker
completes a batche, the manager process sends them another batch. So
in most cases, I'm performing two updates to the record while step'ing
through the result set.


> > 2) is the order set only once on the first sqlite3_step, or does it
> > change with each sqlite3_step invocation?
> 
> Because no index already exists for that ORDER, you can imagine that SQLite 
> makes up a temporary index for the results when you do the first 
> sqlite3_step(), and deletes it when you execute
> sqlite3_reset() or sqlite3_finalize().
> 
> 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_step and ORDER BY random() question.

[Simon Slavin]

On 10 Apr 2017, at 9:28pm, Reid Thompson  wrote:

> my questions are, if I prepare and utilize a statement for a result set
> in the tens of thousands (or more) using a where clause along the lines
> of
>"ORDER BY batch_id, due_datetime, random()"
> 
> 1) As I sqlite3_step through the result set, am I guaranteed to get each
> row only once?

Yes.  _step() will return each row exactly once.  This assumes you will not 
make any changes to the table those rows are in until you have finished 
stepping.  If you make any changes to the table before the last call to _step() 
then things may get more complicated.

> 2) is the order set only once on the first sqlite3_step, or does it
> change with each sqlite3_step invocation?

Because no index already exists for that ORDER, you can imagine that SQLite 
makes up a temporary index for the results when you do the first 
sqlite3_step(), and deletes it when you execute sqlite3_reset() or 
sqlite3_finalize().

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

Re: [sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

[Simon Slavin]

On 5 Sep 2014, at 1:58pm, Jan Slodicka  wrote:

> 1) SQLITE_OK should not happen. (Official documentation allows /ANY/ result
> code. I hope this not true.)
> 2) If it happens, then this looks dangerous. 

You are correct.  If you use this routine only for SELECT commands or PRAGMAs 
which return results, SQLITE_OK should never happen.  You might get SQLITE_OK 
from a command that makes a change (e.g. INSERT, or a PRAGMA which changes 
things).

> I don't like a lot of things about the C# wrapper (heavy overhead,
> SQL_SCHEMA dealing, timeouts...). But those points are not crucial. What I
> ask is following:
> 
> a) If sqlite3_step() returns error code different from 100/101, should we
> call sqlite3_reset()? This looks like a bug to me. (Imagine we are reading a
> table.)

The statement is still valid, it just can't be used right now.  So you might 
want to use it later.  However, the only reason for wanting to call 
sqlite3_reset() is if you might want to execute the same command later.  Since 
this is a wrapper it seems unlikely that you would want to do this in your 
situation.

Instead, it is more likely that you would want to call sqlite3_finalize().  You 
would do this to tell SQLite that you are finished with the statement and want 
to release its memory and other resources.

> b) If we get Locked/Busy errors, is it safe and reasonable to retry
> sqlite3_step? Note that if we solve this way some multi-access conflicts,
> then this is a welcome feature.

It is probably not appropriate to retry sqlite3_step().  If the database was 
locked past whatever timeout you set, it will still be locked.  If it was busy 
it will still be busy.  The normal thing to do if you very Locked/Busy is to 
issue some kind of error message to the user.  In your situation you'd probably 
have that routine do something similar to what it would do for a badly 
formatted command.

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

Re: [sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

[Jan Slodicka]

Thanks, Simon,

perhaps I did not formulate may question clearly enough. The class of course
works as you wrote, the only problem relates to the error handling.

If you are interested, the C# wrapper (original code) is  here

 
. (UnsafeNativeMethods class contains a collection of function pointers to
sqlite3.dll.)

This is the part of the code I want to discuss (a shortened version):


internal override bool Step(SQLiteStatement stmt)
{
  Random rnd = new Random();
  uint starttick = (uint)Environment.TickCount;
  uint timeout = (uint)(stmt._command._commandTimeout * 1000);

  while (true)
  {
SQLiteErrorCode n =
UnsafeNativeMethods.sqlite3_step(stmt._sqlite_stmt);
if (n == SQLiteErrorCode.Row) return true;
if (n == SQLiteErrorCode.Done) return false;

if (n != SQLiteErrorCode.Ok)   //*) 
{
  SQLiteErrorCode r = Reset(stmt);
  if (r == SQLiteErrorCode.Ok)
throw new SQLiteException(n, GetLastError());

  // Only Locked/Busy err code can pass here
  if ((uint)Environment.TickCount - starttick > timeout)
  throw new SQLiteException(r, GetLastError());
  System.Threading.Thread.Sleep(rnd.Next(1, 150));
}
  }
}

internal override SQLiteErrorCode Reset(SQLiteStatement stmt)
{
  SQLiteErrorCode n =
UnsafeNativeMethods.sqlite3_reset(stmt._sqlite_stmt);
  if (n == SQLiteErrorCode.Locked || n == SQLiteErrorCode.Busy || n ==
SQLiteErrorCode.Ok)
return n;
  throw new SQLiteException(n, GetLastError());
}


*) STRANGE:
1) SQLITE_OK should not happen. (Official documentation allows /ANY/ result
code. I hope this not true.)
2) If it happens, then this looks dangerous. 

I don't like a lot of things about the C# wrapper (heavy overhead,
SQL_SCHEMA dealing, timeouts...). But those points are not crucial. What I
ask is following:

a) If sqlite3_step() returns error code different from 100/101, should we
call sqlite3_reset()? This looks like a bug to me. (Imagine we are reading a
table.)

b) If we get Locked/Busy errors, is it safe and reasonable to retry
sqlite3_step? Note that if we solve this way some multi-access conflicts,
then this is a welcome feature.

Meanwhile I collected more info about the problem.

First, a reduced version of the sqlite3_step() official documentation that
applies to these conditions:
- Sqlite 3.7.15, SQLITE_OMIT_AUTORESET was not defined
- The statement was prepared using sqlite3_prepare_v2()
/
sqlite3_step returns SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR,
SQLITE_MISUSE, or any other result code
SQLITE_ROW/SQLITE_DONE... (description omitted)
SQLITE_BUSY means a failure to acquire a DB lock. If the statement is a
COMMIT or occurs outside of an explicit transaction, then you can retry the
statement. Otherwise you should rollback.
SQLITE_ERROR means a run-time error. sqlite3_step() should not be called,
sqlite3_errmsg() provides additional info.
SQLITE_MISUSE means that this routine was called inappropriately, such as on
a finalized statement. 
If sqlite3_step() returns anything other than SQLITE_ROW, then the next call
to sqlite3_step() automatically calls sqlite3_reset().
/

Another SQLite document  says   
"/SQLITE_LOCKED error may only occur on the first call to sqlite3_step()/".
And  Mr. Hipp

  
seems to have confirmed the same for SQLITE_BUSY. (Although 10 years ago)

Dan Kennedy  says

  
"/After SQLITE_BUSY is returned, you can call sqlite3_step() again
immediately/". (Note that this is not the same as the official documentation
says.)

Knowing now above info, I don't see the C# wrapper code that bad as
yesterday, though I am still far from being sure that it is 100% ok.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite3-step-and-SQLITE-LOCKED-BUSY-tp77690p77707.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

[Simon Slavin]

> On 4 Sep 2014, at 4:21pm, Jan Slodicka  wrote:
> 
> Our C# wrapper (inspired by system.data.sqlite) uses this quasi-code:
> 
> bool Step(stmt)
> {
>  while(true)
>  {
>start time measurement
>rc = sqlite3_step(stmt);
>if( rc == SQLITE_ROW) return true;// ... reading column values
> possible
>if( rc == SQLITE_DONE) return false;  // ... done
> 
>rc = sqlite3_reset(stmt);// needed? looks strange to me
> 
>if( rc!=SQLITE_LOCKED && rc!=SQLITE_BUSY)
>throw an exception (message taken from sqlite3_errmsg())
>else {
>if timeout expired => throw a timeout exception
>else sleep a bit
>}
>  }
> }

Bad code.  No cookie.  A better structure would be

int stepResult = sqlite3_step(statement);
while (stepResult == SQLITE_ROW) {
// we received a legit row of data
// so handle it here then once it has been handled ...

stepResult = sqlite3_step(statement);
}
sqlite3_finalize(statement);
// now stepResult contains the reason there was no more data.
// if stepResult == SQLITE_DONE then we just finished receiving legit
// results and can carry on with the program.  But ...

if (stepResult != SQLITE_DONE) {
// handle other result codes here and halt/cancel/error
}

You don't need to sleep for timeouts yourself.  Just use this call once, after 
you've opened your connection, to set a timeout of a minute or so ...



and SQLite will handle timeouts and sleeping and exponential backoff itself 
without you having to write your own code to do it.  If the database is still 
locked after the amount of timeout you specified, /then/ you get SQLITE_BUSY.

> If I understand sqlite3_reset correctly (i.e. when it is called after some
> table rows are read, then it returns to the first row), then this code looks
> buggy.

A statement still exists until it has been sqlite3_reset() or 
sqlite3_finalized() and you should act as if it's taking up memory, pointers, 
or some other annoying stuff.  You reset it if you think you're going to start 
it again later, which will restart a SELECT from the first row again.  You 
finalize it if you're done with it and don't intend to execute it again later.  
Both of these calls release resources and set status so that when you 
eventually close the connection to the database SQLite knows you've tidied up 
and can release everything associated with it.

Theoretically you should not execute things like sqlite3_finalize() or 
sqlite3_close() without checking the values they returned to make sure they're 
SQLITE_OK.  I ommitted this check from my above example to make it more 
understandable.

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

Re: [sqlite] sqlite3_step getting core dumbed.

[Tim Streater]

On 10 Feb 2012 at 07:55, bhaskarReddy  wrote: 

> PRAGMA table_info(yourtablename); will display  colNumber, colName, colType,
> 
> ex: 0|slotId|INTEGER|0||0
> 1|ponChannelId|INTEGER|0||0
> 2|onuType|INTEGER|0||0
> 3|onuSerialNumber|TEXT|0||0
> 4|onuId|INTEGER|0||0
> 5|plannedSwVersion|TEXT|0||0
> 6|adminStatus|INTEGER|0||0
>
> In that how can we extract INTEGER.

I expect that in the result set, third column (index 2) says INTEGER.

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

Re: [sqlite] sqlite3_step getting core dumbed.

[bhaskarReddy]

PRAGMA table_info(yourtablename); will display  colNumber, colName, colType,

ex: 0|slotId|INTEGER|0||0
1|ponChannelId|INTEGER|0||0
2|onuType|INTEGER|0||0
3|onuSerialNumber|TEXT|0||0
4|onuId|INTEGER|0||0
5|plannedSwVersion|TEXT|0||0
6|adminStatus|INTEGER|0||0


In that how can we extract INTEGER.

Regards,
Bhaskar.

Kees Nuyt wrote:
> 
> On Thu, 9 Feb 2012 05:07:05 -0800 (PST), bhaskarReddy 
> wrote:
> 
>>
>>Is it possible to find the data type, even if the table doesn't have data.  
>>That means, my requirement is, 
>>
>>1. I want to find the number of columns in the table. ( I already done.)
>>2. I want to find the names of the columns. (I already done.)
>>3. I want to find the data types. Because, if it is an integer i'll use
>>s***_bind_int(), else if it is test i'll use s***_bind_text().
>>
>>Now if the table is empty, i want to find the data types and according to
>>that i want to fill the row.
>>
>>So is there any way to find the data types even if the  table is empty.
>>(i.e., Table is there, columns and their names are there but no data in
that
>>table.)
> 
> http://www.sqlite.org/c3ref/table_column_metadata.html
> or
> http://www.sqlite.org/c3ref/column_decltype.html 
> or execute the
>   PRAGMA table_info(yourtablename);
> statement and process the resultset.
> 
>>Regards,
>>Bhaskar.
> 
> -- 
> Regards,
> 
> Kees Nuyt
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-getting-core-dumbed.-tp33292180p33298627.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3_step getting core dumbed.

[Kees Nuyt]

On Thu, 9 Feb 2012 05:07:05 -0800 (PST), bhaskarReddy 
wrote:

>
>Is it possible to find the data type, even if the table doesn't have data.  
>That means, my requirement is, 
>
>1. I want to find the number of columns in the table. ( I already done.)
>2. I want to find the names of the columns. (I already done.)
>3. I want to find the data types. Because, if it is an integer i'll use
>s***_bind_int(), else if it is test i'll use s***_bind_text().
>
>Now if the table is empty, i want to find the data types and according to
>that i want to fill the row.
>
>So is there any way to find the data types even if the  table is empty.
>(i.e., Table is there, columns and their names are there but no data in that
>table.)

http://www.sqlite.org/c3ref/table_column_metadata.html
or
http://www.sqlite.org/c3ref/column_decltype.html 
or execute the
PRAGMA table_info(yourtablename);
statement and process the resultset.

>Regards,
>Bhaskar.

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] sqlite3_step getting core dumbed.

[bhaskarReddy]

Is it possible to find the data type, even if the table doesn't have data.  
That means, my requirement is, 

1. I want to find the number of columns in the table. ( I already done.)
2. I want to find the names of the columns. (I already done.)
3. I want to find the data types. Because, if it is an integer i'll use
s***_bind_int(), else if it is test i'll use s***_bind_text().

Now if the table is empty, i want to find the data types and according to
that i want to fill the row.

So is there any way to find the data types even if the  table is empty.
(i.e., Table is there, columns and their names are there but no data in that
table.)


Regards,
Bhaskar.


Marcus Grimm wrote:
> 
> 
> On 09.02.2012 13:12, bhaskarReddy wrote:
>>
>> Thank you. Its working.
>>
>> But why i am getting   sqlite3_column_type returning 5.
>>
> 
> I can only guess:
> 
> a) because you have no data in that table.
> b) probably you have no known type defined in table
> definition and sqlite does a conversion anyhow.
> c) is sqlite3_step(stmt); really required here ?
> what is the result of step ? is it SQLITE_ROW ?
> If not, probably sqlite3_column_type doesn't make much sense.
> 
> 
> Marcus
> 
> 
>>
>>
>>
>> Marcus Grimm wrote:
>>>
>>> try changing this:
>>>
>>> colNames = (char **)malloc(sizeof(char));
>>>
>>> into something like
>>>
>>> colNames = (char **)malloc(sizeof(char * ) * noOfColumns);
>>>
>>>
>>> On 09.02.2012 11:21, bhaskarReddy wrote:

 HI Friends,

 I dont know why the sqlite3_step getting core dumped.

#include
#include
#include "sqlitedb1.h"
#include
#include
#include
#include
#include

int db_retrieve_row(char *db_name, char *table_name, int
 num_of_keys,
 char
 * col_names[],column_value_t * col_values);

 int main(void)
 {
 column_type_t enm[2];
  //   int retVal;
 char *col_name[3];
 char *db_name = "LR6002.db";
 char *table_name = "ONTTable";
 column_value_t col_val[3];

 enm[0] = COLUMN_TYPE_INT;   // slotId
 enm[1] = COLUMN_TYPE_STRING;

 col_val[0].number = 1;
 col_val[1].number = 2;
 col_val[2].number = 3;
   /* Array of Column Names. */
col_name[0] = "slotId";
col_name[1] = "ponChannelId";
col_name[2] = "onuType";

  db_retrieve_row(db_name, table_name, 3,  col_name,col_val);
  return 0;
}
int db_retrieve_row(char *db_name, char *table_name, int
 num_of_keys,
 char
 * col_names[],column_value_t * col_values)
{
sqlite3 *db;
sqlite3_stmt *stmt;
int status = 0,loopVar,noOfColumns;
char *query = NULL,**colNames,*str;
int  retVal,*colType;

retVal = sqlite3_open(db_name,&db);
if(retVal) {
fprintf(stderr,"Can't  open database:
 %s\n",sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
   /* A Query to find the number  of columns in the table. */
   query = (char *)malloc(sizeof(char) * 255);
   memset(query,0,255);
   strcat(query, "SELECT * FROM ");
   strcat(query, table_name);
   strcat(query, ";");

   status = sqlite3_prepare_v2(db,query,strlen(query) + 1,&stmt,
 NULL);
   if(status != SQLITE_OK) {
   printf("Prepare error: %s\n", sqlite3_errmsg(db));
   exit(1);
   }
   noOfColumns = sqlite3_column_count(stmt);
   if(SQLITE_OK != sqlite3_finalize(stmt))
   {
printf("The prepared statement is Not deleted.\n");
   }
   free(query);
   query = NULL;

 /* A Query to find the Names of each column. */
 query = (char *)malloc(sizeof(char) * 255);
memset(query,0,255);
 strcat(query, "SELECT * FROM ");
 strcat(query, table_name);
 strcat(query, ";");

 //stmt = NULL;
 status = sqlite3_prepare_v2(db,query,strlen(query) + 1,&stmt,
 NULL);
if(status != SQLITE_OK) {
printf("Prepare error: %s\n", sqlite3_errmsg(db));
 exit(1);
 }

 colNames = (char **)malloc(sizeof(char));
 memset(colNames,0,1);

  for(loopVar = 0; loopVar<   noOfColumns; loopVar++)
  {
  colNames[loopVar] = (char *)malloc(sizeof(char) * 20);
  memset(colNames[loopVar], 0, 20);
   strcat(colNames[loopVar],sqlite3_column_name(stmt,
 loopVar));
  }
  if(SQLITE_OK != sqlite3_finalize(stmt))
  {
 printf("The prepared statement is Not deleted.\n");
 

Re: [sqlite] sqlite3_step getting core dumbed.

[Marcus Grimm]

On 09.02.2012 13:12, bhaskarReddy wrote:


Thank you. Its working.

But why i am getting   sqlite3_column_type returning 5.



I can only guess:

a) because you have no data in that table.
b) probably you have no known type defined in table
   definition and sqlite does a conversion anyhow.
c) is sqlite3_step(stmt); really required here ?
   what is the result of step ? is it SQLITE_ROW ?
   If not, probably sqlite3_column_type doesn't make much sense.


Marcus






Marcus Grimm wrote:


try changing this:

colNames = (char **)malloc(sizeof(char));

into something like

colNames = (char **)malloc(sizeof(char * ) * noOfColumns);


On 09.02.2012 11:21, bhaskarReddy wrote:


HI Friends,

I dont know why the sqlite3_step getting core dumped.

   #include
   #include
   #include "sqlitedb1.h"
   #include
   #include
   #include
   #include
   #include

   int db_retrieve_row(char *db_name, char *table_name, int num_of_keys,
char
* col_names[],column_value_t * col_values);

int main(void)
{
column_type_t enm[2];
 //   int retVal;
char *col_name[3];
char *db_name = "LR6002.db";
char *table_name = "ONTTable";
column_value_t col_val[3];

enm[0] = COLUMN_TYPE_INT;   // slotId
enm[1] = COLUMN_TYPE_STRING;

col_val[0].number = 1;
col_val[1].number = 2;
col_val[2].number = 3;
  /* Array of Column Names. */
   col_name[0] = "slotId";
   col_name[1] = "ponChannelId";
   col_name[2] = "onuType";

 db_retrieve_row(db_name, table_name, 3,  col_name,col_val);
 return 0;
   }
   int db_retrieve_row(char *db_name, char *table_name, int num_of_keys,
char
* col_names[],column_value_t * col_values)
   {
   sqlite3 *db;
   sqlite3_stmt *stmt;
   int status = 0,loopVar,noOfColumns;
   char *query = NULL,**colNames,*str;
   int  retVal,*colType;

   retVal = sqlite3_open(db_name,&db);
   if(retVal) {
   fprintf(stderr,"Can't  open database:
%s\n",sqlite3_errmsg(db));
   sqlite3_close(db);
   exit(1);
   }
  /* A Query to find the number  of columns in the table. */
  query = (char *)malloc(sizeof(char) * 255);
  memset(query,0,255);
  strcat(query, "SELECT * FROM ");
  strcat(query, table_name);
  strcat(query, ";");

  status = sqlite3_prepare_v2(db,query,strlen(query) + 1,&stmt, NULL);
  if(status != SQLITE_OK) {
  printf("Prepare error: %s\n", sqlite3_errmsg(db));
  exit(1);
  }
  noOfColumns = sqlite3_column_count(stmt);
  if(SQLITE_OK != sqlite3_finalize(stmt))
  {
   printf("The prepared statement is Not deleted.\n");
  }
  free(query);
  query = NULL;

/* A Query to find the Names of each column. */
query = (char *)malloc(sizeof(char) * 255);
   memset(query,0,255);
strcat(query, "SELECT * FROM ");
strcat(query, table_name);
strcat(query, ";");

//stmt = NULL;
status = sqlite3_prepare_v2(db,query,strlen(query) + 1,&stmt,
NULL);
   if(status != SQLITE_OK) {
   printf("Prepare error: %s\n", sqlite3_errmsg(db));
exit(1);
}

colNames = (char **)malloc(sizeof(char));
memset(colNames,0,1);

 for(loopVar = 0; loopVar<   noOfColumns; loopVar++)
 {
 colNames[loopVar] = (char *)malloc(sizeof(char) * 20);
 memset(colNames[loopVar], 0, 20);
  strcat(colNames[loopVar],sqlite3_column_name(stmt,
loopVar));
 }
 if(SQLITE_OK != sqlite3_finalize(stmt))
 {
printf("The prepared statement is Not deleted.\n");
 }
 free(query);
query = NULL;

/*  A Query to find types of each column. */
str = (char *)malloc(sizeof(char) * 255);
 memset(str, 0, 255);
 strcat(str, "SELECT ");
 for(loopVar = 0; loopVar<   noOfColumns; loopVar++)
 {
   strcat(str, colNames[loopVar]);
   if(loopVar<   (noOfColumns -1))
 strcat(str,",");
 }
 strcat(str, " FROM ");
 strcat(str, table_name);
 strcat(str, ";");

 status=sqlite3_prepare_v2(db,str,strlen(str)+1,&stmt, NULL);
 if (status != SQLITE_OK) {
printf("prepare error:%s\n",sqlite3_errmsg(db));
exit(1);
}
sqlite3_step(stmt);   //Causing Segmentation Fault. //
colType = (int *)malloc(sizeof(int) * noOfColumns);
for(loopVar = 0; loopVar<   noOfColumns; loopVar++){
colType[loopVar] = sqlite3_column_type(stmt, loopVar);
printf("Column Types = %d\n",colType[loopVar]);
 }
if(SQLITE_OK != sqlite3_finalize(stmt))
{
   printf("The prepared statement is Not deleted.\n");
}
free(query);
query = NULL;

}

And the API 'sqlite3_column_type" always returning 5, i.e., NULL.


Can any one tell me what wa

Re: [sqlite] sqlite3_step getting core dumbed.

[bhaskarReddy]

Thank you. Its working.

But why i am getting   sqlite3_column_type returning 5.




Marcus Grimm wrote:
> 
> try changing this:
> 
> colNames = (char **)malloc(sizeof(char));
> 
> into something like
> 
> colNames = (char **)malloc(sizeof(char * ) * noOfColumns);
> 
> 
> On 09.02.2012 11:21, bhaskarReddy wrote:
>>
>> HI Friends,
>>
>>I dont know why the sqlite3_step getting core dumped.
>>
>>   #include
>>   #include
>>   #include "sqlitedb1.h"
>>   #include
>>   #include
>>   #include
>>   #include
>>   #include
>>
>>   int db_retrieve_row(char *db_name, char *table_name, int num_of_keys,
>> char
>> * col_names[],column_value_t * col_values);
>>
>> int main(void)
>> {
>>column_type_t enm[2];
>> //   int retVal;
>>char *col_name[3];
>>char *db_name = "LR6002.db";
>>char *table_name = "ONTTable";
>>column_value_t col_val[3];
>>
>>enm[0] = COLUMN_TYPE_INT;   // slotId
>>enm[1] = COLUMN_TYPE_STRING;
>>
>>col_val[0].number = 1;
>>col_val[1].number = 2;
>>col_val[2].number = 3;
>>  /* Array of Column Names. */
>>   col_name[0] = "slotId";
>>   col_name[1] = "ponChannelId";
>>   col_name[2] = "onuType";
>>
>> db_retrieve_row(db_name, table_name, 3,  col_name,col_val);
>> return 0;
>>   }
>>   int db_retrieve_row(char *db_name, char *table_name, int num_of_keys,
>> char
>> * col_names[],column_value_t * col_values)
>>   {
>>   sqlite3 *db;
>>   sqlite3_stmt *stmt;
>>   int status = 0,loopVar,noOfColumns;
>>   char *query = NULL,**colNames,*str;
>>   int  retVal,*colType;
>>
>>   retVal = sqlite3_open(db_name,&db);
>>   if(retVal) {
>>   fprintf(stderr,"Can't  open database:
>> %s\n",sqlite3_errmsg(db));
>>   sqlite3_close(db);
>>   exit(1);
>>   }
>>  /* A Query to find the number  of columns in the table. */
>>  query = (char *)malloc(sizeof(char) * 255);
>>  memset(query,0,255);
>>  strcat(query, "SELECT * FROM ");
>>  strcat(query, table_name);
>>  strcat(query, ";");
>>
>>  status = sqlite3_prepare_v2(db,query,strlen(query) + 1,&stmt, NULL);
>>  if(status != SQLITE_OK) {
>>  printf("Prepare error: %s\n", sqlite3_errmsg(db));
>>  exit(1);
>>  }
>>  noOfColumns = sqlite3_column_count(stmt);
>>  if(SQLITE_OK != sqlite3_finalize(stmt))
>>  {
>>   printf("The prepared statement is Not deleted.\n");
>>  }
>>  free(query);
>>  query = NULL;
>>
>>/* A Query to find the Names of each column. */
>>query = (char *)malloc(sizeof(char) * 255);
>>   memset(query,0,255);
>>strcat(query, "SELECT * FROM ");
>>strcat(query, table_name);
>>strcat(query, ";");
>>
>>//stmt = NULL;
>>status = sqlite3_prepare_v2(db,query,strlen(query) + 1,&stmt,
>> NULL);
>>   if(status != SQLITE_OK) {
>>   printf("Prepare error: %s\n", sqlite3_errmsg(db));
>>exit(1);
>>}
>>
>>colNames = (char **)malloc(sizeof(char));
>>memset(colNames,0,1);
>>
>> for(loopVar = 0; loopVar<  noOfColumns; loopVar++)
>> {
>> colNames[loopVar] = (char *)malloc(sizeof(char) * 20);
>> memset(colNames[loopVar], 0, 20);
>>  strcat(colNames[loopVar],sqlite3_column_name(stmt,
>> loopVar));
>> }
>> if(SQLITE_OK != sqlite3_finalize(stmt))
>> {
>>printf("The prepared statement is Not deleted.\n");
>> }
>> free(query);
>>query = NULL;
>>
>>/*  A Query to find types of each column. */
>>str = (char *)malloc(sizeof(char) * 255);
>> memset(str, 0, 255);
>> strcat(str, "SELECT ");
>> for(loopVar = 0; loopVar<  noOfColumns; loopVar++)
>> {
>>   strcat(str, colNames[loopVar]);
>>   if(loopVar<  (noOfColumns -1))
>> strcat(str,",");
>> }
>> strcat(str, " FROM ");
>> strcat(str, table_name);
>> strcat(str, ";");
>>
>> status=sqlite3_prepare_v2(db,str,strlen(str)+1,&stmt, NULL);
>> if (status != SQLITE_OK) {
>>printf("prepare error:%s\n",sqlite3_errmsg(db));
>>exit(1);
>>}
>>sqlite3_step(stmt);   //Causing Segmentation Fault. //
>>colType = (int *)malloc(sizeof(int) * noOfColumns);
>>for(loopVar = 0; loopVar<  noOfColumns; loopVar++){
>>colType[loopVar] = sqlite3_column_type(stmt, loopVar);
>>printf("Column Types = %d\n",colType[loopVar]);
>> }
>>if(SQLITE_OK != sqlite3_finalize(stmt))
>>{
>>   printf("The prepared statement is Not deleted.\n");
>>}
>>free(query);
>>query = NULL;
>>
>> }
>>
>> And the API 'sqlite3_column_type" always returning 5, i.e., NULL.
>>
>>
>> Can any one tell me what was the problem that i did.
>>
>>
>> Regards,
>> Bhaskar.
>>
> __

Re: [sqlite] sqlite3_step getting core dumbed.

[Marcus Grimm]

try changing this:

colNames = (char **)malloc(sizeof(char));

into something like

colNames = (char **)malloc(sizeof(char * ) * noOfColumns);


On 09.02.2012 11:21, bhaskarReddy wrote:


HI Friends,

   I dont know why the sqlite3_step getting core dumped.

  #include
  #include
  #include "sqlitedb1.h"
  #include
  #include
  #include
  #include
  #include

  int db_retrieve_row(char *db_name, char *table_name, int num_of_keys, char
* col_names[],column_value_t * col_values);

int main(void)
{
   column_type_t enm[2];
//   int retVal;
   char *col_name[3];
   char *db_name = "LR6002.db";
   char *table_name = "ONTTable";
   column_value_t col_val[3];

   enm[0] = COLUMN_TYPE_INT;   // slotId
   enm[1] = COLUMN_TYPE_STRING;

   col_val[0].number = 1;
   col_val[1].number = 2;
   col_val[2].number = 3;
 /* Array of Column Names. */
  col_name[0] = "slotId";
  col_name[1] = "ponChannelId";
  col_name[2] = "onuType";

db_retrieve_row(db_name, table_name, 3,  col_name,col_val);
return 0;
  }
  int db_retrieve_row(char *db_name, char *table_name, int num_of_keys, char
* col_names[],column_value_t * col_values)
  {
  sqlite3 *db;
  sqlite3_stmt *stmt;
  int status = 0,loopVar,noOfColumns;
  char *query = NULL,**colNames,*str;
  int  retVal,*colType;

  retVal = sqlite3_open(db_name,&db);
  if(retVal) {
  fprintf(stderr,"Can't  open database: %s\n",sqlite3_errmsg(db));
  sqlite3_close(db);
  exit(1);
  }
 /* A Query to find the number  of columns in the table. */
 query = (char *)malloc(sizeof(char) * 255);
 memset(query,0,255);
 strcat(query, "SELECT * FROM ");
 strcat(query, table_name);
 strcat(query, ";");

 status = sqlite3_prepare_v2(db,query,strlen(query) + 1,&stmt, NULL);
 if(status != SQLITE_OK) {
 printf("Prepare error: %s\n", sqlite3_errmsg(db));
 exit(1);
 }
 noOfColumns = sqlite3_column_count(stmt);
 if(SQLITE_OK != sqlite3_finalize(stmt))
 {
  printf("The prepared statement is Not deleted.\n");
 }
 free(query);
 query = NULL;

   /* A Query to find the Names of each column. */
   query = (char *)malloc(sizeof(char) * 255);
  memset(query,0,255);
   strcat(query, "SELECT * FROM ");
   strcat(query, table_name);
   strcat(query, ";");

   //stmt = NULL;
   status = sqlite3_prepare_v2(db,query,strlen(query) + 1,&stmt, NULL);
  if(status != SQLITE_OK) {
  printf("Prepare error: %s\n", sqlite3_errmsg(db));
   exit(1);
   }

   colNames = (char **)malloc(sizeof(char));
   memset(colNames,0,1);

for(loopVar = 0; loopVar<  noOfColumns; loopVar++)
{
colNames[loopVar] = (char *)malloc(sizeof(char) * 20);
memset(colNames[loopVar], 0, 20);
 strcat(colNames[loopVar],sqlite3_column_name(stmt, loopVar));
}
if(SQLITE_OK != sqlite3_finalize(stmt))
{
   printf("The prepared statement is Not deleted.\n");
}
free(query);
   query = NULL;

   /*  A Query to find types of each column. */
   str = (char *)malloc(sizeof(char) * 255);
memset(str, 0, 255);
strcat(str, "SELECT ");
for(loopVar = 0; loopVar<  noOfColumns; loopVar++)
{
  strcat(str, colNames[loopVar]);
  if(loopVar<  (noOfColumns -1))
strcat(str,",");
}
strcat(str, " FROM ");
strcat(str, table_name);
strcat(str, ";");

status=sqlite3_prepare_v2(db,str,strlen(str)+1,&stmt, NULL);
if (status != SQLITE_OK) {
   printf("prepare error:%s\n",sqlite3_errmsg(db));
   exit(1);
   }
   sqlite3_step(stmt);   //Causing Segmentation Fault. //
   colType = (int *)malloc(sizeof(int) * noOfColumns);
   for(loopVar = 0; loopVar<  noOfColumns; loopVar++){
   colType[loopVar] = sqlite3_column_type(stmt, loopVar);
   printf("Column Types = %d\n",colType[loopVar]);
}
   if(SQLITE_OK != sqlite3_finalize(stmt))
   {
  printf("The prepared statement is Not deleted.\n");
   }
   free(query);
   query = NULL;

}

And the API 'sqlite3_column_type" always returning 5, i.e., NULL.


Can any one tell me what was the problem that i did.


Regards,
Bhaskar.


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

Re: [sqlite] sqlite3_step getting core dumbed.

[bhaskarReddy]

No, that is not problem.


Taleeb Anwar wrote:
> 
> /*  A Query to find types of each column. */
>  str = (char *)malloc(sizeof(char) * 255);
>   memset(str, 0, 255);
>   strcat(str, "SELECT ");
>   for(loopVar = 0; loopVar < noOfColumns; loopVar++)
>   {
> strcat(str, colNames[loopVar]);
> if(loopVar < (noOfColumns -1))
>   strcat(str,",");
>   }
> 
> The problem is, perhaps, in the query created through this loop. An extra
> ',' will get appended after the last column name. that is if you have two
> columns called column1 and column2 so your query will be
> SELECT column1,column2, FROM... //(note ',' after column2)
> 
> I may be wrong for more or less I've forgotten "C"; but personally I think
> the sqlite should be throwing exception...
> 
> Thanks & Regards
> Taleeb bin Waquar
> 
> *Hum Mashriq Ke "Miskeenon" Ka Dil Maghrib men Ja Atka Hai!!*
> 
> 
> 
> 
> On Thu, Feb 9, 2012 at 3:51 PM, bhaskarReddy  wrote:
> 
>>
>> HI Friends,
>>
>>  I dont know why the sqlite3_step getting core dumped.
>>
>>  #include 
>>  #include 
>>  #include "sqlitedb1.h"
>>  #include 
>>  #include 
>>  #include 
>>  #include 
>>  #include 
>>
>>  int db_retrieve_row(char *db_name, char *table_name, int num_of_keys,
>> char
>> * col_names[],column_value_t * col_values);
>>
>> int main(void)
>> {
>>  column_type_t enm[2];
>>   //   int retVal;
>>  char *col_name[3];
>>  char *db_name = "LR6002.db";
>>  char *table_name = "ONTTable";
>>  column_value_t col_val[3];
>>
>>  enm[0] = COLUMN_TYPE_INT;   // slotId
>>  enm[1] = COLUMN_TYPE_STRING;
>>
>>  col_val[0].number = 1;
>>  col_val[1].number = 2;
>>  col_val[2].number = 3;
>>/* Array of Column Names. */
>> col_name[0] = "slotId";
>> col_name[1] = "ponChannelId";
>> col_name[2] = "onuType";
>>
>>   db_retrieve_row(db_name, table_name, 3,  col_name,col_val);
>>   return 0;
>>  }
>>  int db_retrieve_row(char *db_name, char *table_name, int num_of_keys,
>> char
>> * col_names[],column_value_t * col_values)
>>  {
>> sqlite3 *db;
>> sqlite3_stmt *stmt;
>> int status = 0,loopVar,noOfColumns;
>> char *query = NULL,**colNames,*str;
>> int  retVal,*colType;
>>
>> retVal = sqlite3_open(db_name, &db);
>> if(retVal) {
>> fprintf(stderr,"Can't  open database: %s\n",sqlite3_errmsg(db));
>> sqlite3_close(db);
>> exit(1);
>> }
>>/* A Query to find the number  of columns in the table. */
>>query = (char *)malloc(sizeof(char) * 255);
>>memset(query,0,255);
>>strcat(query, "SELECT * FROM ");
>>strcat(query, table_name);
>>strcat(query, ";");
>>
>>status = sqlite3_prepare_v2(db,query,strlen(query) + 1, &stmt, NULL);
>>if(status != SQLITE_OK) {
>>printf("Prepare error: %s\n", sqlite3_errmsg(db));
>>exit(1);
>>}
>>noOfColumns = sqlite3_column_count(stmt);
>>if(SQLITE_OK != sqlite3_finalize(stmt))
>>{
>> printf("The prepared statement is Not deleted.\n");
>>}
>>free(query);
>>query = NULL;
>>
>>  /* A Query to find the Names of each column. */
>>  query = (char *)malloc(sizeof(char) * 255);
>> memset(query,0,255);
>>  strcat(query, "SELECT * FROM ");
>>  strcat(query, table_name);
>>  strcat(query, ";");
>>
>>  //stmt = NULL;
>>  status = sqlite3_prepare_v2(db,query,strlen(query) + 1, &stmt,
>> NULL);
>> if(status != SQLITE_OK) {
>> printf("Prepare error: %s\n", sqlite3_errmsg(db));
>>  exit(1);
>>  }
>>
>>  colNames = (char **)malloc(sizeof(char));
>>  memset(colNames,0,1);
>>
>>   for(loopVar = 0; loopVar < noOfColumns; loopVar++)
>>   {
>>   colNames[loopVar] = (char *)malloc(sizeof(char) * 20);
>>   memset(colNames[loopVar], 0, 20);
>>strcat(colNames[loopVar],sqlite3_column_name(stmt, loopVar));
>>   }
>>   if(SQLITE_OK != sqlite3_finalize(stmt))
>>   {
>>  printf("The prepared statement is Not deleted.\n");
>>   }
>>   free(query);
>>  query = NULL;
>>
>>  /*  A Query to find types of each column. */
>>  str = (char *)malloc(sizeof(char) * 255);
>>   memset(str, 0, 255);
>>   strcat(str, "SELECT ");
>>   for(loopVar = 0; loopVar < noOfColumns; loopVar++)
>>   {
>> strcat(str, colNames[loopVar]);
>> if(loopVar < (noOfColumns -1))
>>   strcat(str,",");
>>   }
>>   strcat(str, " FROM ");
>>   strcat(str, table_name);
>>   strcat(str, ";");
>>
>>   status=sqlite3_prepare_v2(db,str,strlen(str)+1, &stmt, NULL);
>>   if (status != SQLITE_OK) {
>>  printf("prepare error:%s\n",sqlite3_errmsg(db));
>>  exit(1);
>>  }
>>  sqlite3_step(stmt);   //Causing Segmentation Fault. //
>>  colType = (int *)malloc(sizeof(int) * noOfColumns);
>>  for(loopVar = 0; loopVar < noOfColumns; loopVar++){
>>  colType[loopVar] = sqlite

Re: [sqlite] sqlite3_step getting core dumbed.

[Taleeb Anwar]

/*  A Query to find types of each column. */
 str = (char *)malloc(sizeof(char) * 255);
  memset(str, 0, 255);
  strcat(str, "SELECT ");
  for(loopVar = 0; loopVar < noOfColumns; loopVar++)
  {
strcat(str, colNames[loopVar]);
if(loopVar < (noOfColumns -1))
  strcat(str,",");
  }

The problem is, perhaps, in the query created through this loop. An extra
',' will get appended after the last column name. that is if you have two
columns called column1 and column2 so your query will be
SELECT column1,column2, FROM... //(note ',' after column2)

I may be wrong for more or less I've forgotten "C"; but personally I think
the sqlite should be throwing exception...

Thanks & Regards
Taleeb bin Waquar

*Hum Mashriq Ke "Miskeenon" Ka Dil Maghrib men Ja Atka Hai!!*




On Thu, Feb 9, 2012 at 3:51 PM, bhaskarReddy  wrote:

>
> HI Friends,
>
>  I dont know why the sqlite3_step getting core dumped.
>
>  #include 
>  #include 
>  #include "sqlitedb1.h"
>  #include 
>  #include 
>  #include 
>  #include 
>  #include 
>
>  int db_retrieve_row(char *db_name, char *table_name, int num_of_keys, char
> * col_names[],column_value_t * col_values);
>
> int main(void)
> {
>  column_type_t enm[2];
>   //   int retVal;
>  char *col_name[3];
>  char *db_name = "LR6002.db";
>  char *table_name = "ONTTable";
>  column_value_t col_val[3];
>
>  enm[0] = COLUMN_TYPE_INT;   // slotId
>  enm[1] = COLUMN_TYPE_STRING;
>
>  col_val[0].number = 1;
>  col_val[1].number = 2;
>  col_val[2].number = 3;
>/* Array of Column Names. */
> col_name[0] = "slotId";
> col_name[1] = "ponChannelId";
> col_name[2] = "onuType";
>
>   db_retrieve_row(db_name, table_name, 3,  col_name,col_val);
>   return 0;
>  }
>  int db_retrieve_row(char *db_name, char *table_name, int num_of_keys, char
> * col_names[],column_value_t * col_values)
>  {
> sqlite3 *db;
> sqlite3_stmt *stmt;
> int status = 0,loopVar,noOfColumns;
> char *query = NULL,**colNames,*str;
> int  retVal,*colType;
>
> retVal = sqlite3_open(db_name, &db);
> if(retVal) {
> fprintf(stderr,"Can't  open database: %s\n",sqlite3_errmsg(db));
> sqlite3_close(db);
> exit(1);
> }
>/* A Query to find the number  of columns in the table. */
>query = (char *)malloc(sizeof(char) * 255);
>memset(query,0,255);
>strcat(query, "SELECT * FROM ");
>strcat(query, table_name);
>strcat(query, ";");
>
>status = sqlite3_prepare_v2(db,query,strlen(query) + 1, &stmt, NULL);
>if(status != SQLITE_OK) {
>printf("Prepare error: %s\n", sqlite3_errmsg(db));
>exit(1);
>}
>noOfColumns = sqlite3_column_count(stmt);
>if(SQLITE_OK != sqlite3_finalize(stmt))
>{
> printf("The prepared statement is Not deleted.\n");
>}
>free(query);
>query = NULL;
>
>  /* A Query to find the Names of each column. */
>  query = (char *)malloc(sizeof(char) * 255);
> memset(query,0,255);
>  strcat(query, "SELECT * FROM ");
>  strcat(query, table_name);
>  strcat(query, ";");
>
>  //stmt = NULL;
>  status = sqlite3_prepare_v2(db,query,strlen(query) + 1, &stmt, NULL);
> if(status != SQLITE_OK) {
> printf("Prepare error: %s\n", sqlite3_errmsg(db));
>  exit(1);
>  }
>
>  colNames = (char **)malloc(sizeof(char));
>  memset(colNames,0,1);
>
>   for(loopVar = 0; loopVar < noOfColumns; loopVar++)
>   {
>   colNames[loopVar] = (char *)malloc(sizeof(char) * 20);
>   memset(colNames[loopVar], 0, 20);
>strcat(colNames[loopVar],sqlite3_column_name(stmt, loopVar));
>   }
>   if(SQLITE_OK != sqlite3_finalize(stmt))
>   {
>  printf("The prepared statement is Not deleted.\n");
>   }
>   free(query);
>  query = NULL;
>
>  /*  A Query to find types of each column. */
>  str = (char *)malloc(sizeof(char) * 255);
>   memset(str, 0, 255);
>   strcat(str, "SELECT ");
>   for(loopVar = 0; loopVar < noOfColumns; loopVar++)
>   {
> strcat(str, colNames[loopVar]);
> if(loopVar < (noOfColumns -1))
>   strcat(str,",");
>   }
>   strcat(str, " FROM ");
>   strcat(str, table_name);
>   strcat(str, ";");
>
>   status=sqlite3_prepare_v2(db,str,strlen(str)+1, &stmt, NULL);
>   if (status != SQLITE_OK) {
>  printf("prepare error:%s\n",sqlite3_errmsg(db));
>  exit(1);
>  }
>  sqlite3_step(stmt);   //Causing Segmentation Fault. //
>  colType = (int *)malloc(sizeof(int) * noOfColumns);
>  for(loopVar = 0; loopVar < noOfColumns; loopVar++){
>  colType[loopVar] = sqlite3_column_type(stmt, loopVar);
>  printf("Column Types = %d\n",colType[loopVar]);
>   }
>  if(SQLITE_OK != sqlite3_finalize(stmt))
>  {
> printf("The prepared statement is Not deleted.\n");
>  }
>  free(query);

Re: [sqlite] Sqlite3_step

[Igor Tandetnik]

Sreekumar TP  wrote:
> I do not have a ORDER BY , only  a WHERE  clause. So sorting should not be
> the cause for the overhead.

Show your query, and the output of EXPLAIN QUERY PLAN on it.
-- 
Igor Tandetnik

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

Re: [sqlite] Sqlite3_step

[Igor Tandetnik]

Simon Slavin  wrote:
> The first step has to make a lot of decisions about what plan to follow in 
> retrieving the rows: Which index is the best to use ? 
> Is it going to be necessary to sort the rows even after that index ?  These 
> things do take some extra time. 

These decisions are made by sqlite3_prepare, before the first sqlite3_step.
-- 
Igor Tandetnik

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

Re: [sqlite] Sqlite3_step

[Sreekumar TP]

Hi,

I do not have a ORDER BY , only  a WHERE  clause. So sorting should not be
the cause for the overhead.

-Sreekumar



On Mon, Oct 17, 2011 at 9:08 AM, Simon Slavin  wrote:

>
> On 17 Oct 2011, at 4:22am, Sreekumar TP wrote:
>
> > In case of a prepared statement that uses SELECT
> > , the first sqlite3_step statement consumes a lot of time (order of
> > magnitude can be 100 or more)
> > when compared to the subsequent executions of step used to iterate
> through
> > the results. Does the first execution of step cache the entire result set
> > and tje subsequent steps get the results from the cache?
>
> The first step has to make a lot of decisions about what plan to follow in
> retrieving the rows: Which index is the best to use ?  Is it going to be
> necessary to sort the rows even after that index ?  These things do take
> some extra time.
>
> If it turns out that one of the available indexes will produce the rows in
> exactly the order wanted, then it will not be necessary to store all the
> rows which should be returned.  SQLite just stores the query plan: it
> remembers how to find each next row, rather than remembering the data in
> those rows.
>
> On the other hand, if it is necessary for SQLite to sort the rows itself,
> because no good index has been created, then yes, it will have to sort the
> rows in order to figure out which one to return first, and having sorted
> them it will store them so that subsequent _step()s can just return the next
> row.
>
> Simon.
>  ___
> 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] Sqlite3_step

[Simon Slavin]

On 17 Oct 2011, at 4:22am, Sreekumar TP wrote:

> In case of a prepared statement that uses SELECT
> , the first sqlite3_step statement consumes a lot of time (order of
> magnitude can be 100 or more)
> when compared to the subsequent executions of step used to iterate through
> the results. Does the first execution of step cache the entire result set
> and tje subsequent steps get the results from the cache?

The first step has to make a lot of decisions about what plan to follow in 
retrieving the rows: Which index is the best to use ?  Is it going to be 
necessary to sort the rows even after that index ?  These things do take some 
extra time.

If it turns out that one of the available indexes will produce the rows in 
exactly the order wanted, then it will not be necessary to store all the rows 
which should be returned.  SQLite just stores the query plan: it remembers how 
to find each next row, rather than remembering the data in those rows.

On the other hand, if it is necessary for SQLite to sort the rows itself, 
because no good index has been created, then yes, it will have to sort the rows 
in order to figure out which one to return first, and having sorted them it 
will store them so that subsequent _step()s can just return the next row.

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

Re: [sqlite] Sqlite3_step

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 16/10/11 20:22, Sreekumar TP wrote:
> Does the first execution of step cache the entire result set and tje
> subsequent steps get the results from the cache?

No, the first execution does the minimum amount of work necessary in order
to get you the first result row.  Subsequent steps do only the work
necessary to get the next row.

However if your query needs the rows to be sorted then all of the result
rows will have to be found first in order to do the sorting.  (Some sorts
can be satisfied from an index.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6botAACgkQmOOfHg372QS+1gCeNPAie5rgEcUH+2gFJAT8iZVq
n5UAoMl0HkipR2qN5Vr9dLg+jc+3dYhT
=rHPf
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step causing Segmentation Fault

[Black, Michael (IS)]

I'm guessing you want "&(user.getPhoto()[0])" or just "user.getPhoto()"





A pointer...not the first value of your photo.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Rafael Toledo [rafaeldtol...@gmail.com]
Sent: Wednesday, August 31, 2011 1:31 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] sqlite3_step causing Segmentation Fault

Hello! I'm coding a simple method in C++ to insert a tuple in a small
database. The database has a table called 'user', with 3 fields (id -
the primary key, name, and a image).
I mapped these fields in a class - id is an int, name is a std::string
and image is a std::vector of unsigned char. In this point, I'm ok. To
insert this in the database, I write the following method:

bool DatabaseClass::insertAdminUser(User user) {

int rc;
sqlite3_stmt *statement;

// mydb is defined in the private area of the class
rc = sqlite3_open_v2("database.db", &mydb, SQLITE_OPEN_READWRITE, NULL);
if (rc != SQLITE_OK) {
sqlite3_close(mydb);
return false;
}

rc = sqlite3_prepare_v2(mydb, "INSERT INTO users (name, isadmin,
photo) VALUES (?, 1, ?)", -1, &statement, NULL);
if (rc != SQLITE_OK) {
sqlite3_finalize(statement);
sqlite3_close(banco);
return false;
}

int i = 0;
rc = sqlite3_bind_text(statement, ++i, user.getName().c_str(), -1,
SQLITE_TRANSIENT);
if (rc != SQLITE_OK) {
sqlite3_finalize(statement);
sqlite3_close(banco);
return false;
}

rc = sqlite3_bind_blob(statement, ++i, (void*) user.getPhoto()[0],
user.getPhoto().size(), SQLITE_STATIC);
if (rc != SQLITE_OK) {
sqlite3_finalize(statement);
sqlite3_close(banco);
return false;
}

rc = sqlite3_step(statement); // The app crashes here!
if (rc != SQLITE_DONE && rc != SQLITE_ROW) {
sqlite3_finalize(statement);
sqlite3_close(mydb);
return false;
}

sqlite3_finalize(statement);
sqlite3_close(banco);

return true;
}
___
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] sqlite3_step causing Segmentation Fault

[Igor Tandetnik]

Rafael Toledo  wrote:
> rc = sqlite3_prepare_v2(mydb, "INSERT INTO users (name, isadmin,
> photo) VALUES (?, 1, ?)", -1, &statement, NULL);
> if (rc != SQLITE_OK) {
> sqlite3_finalize(statement);

If prepare fails, statement is never updated. So you are passing garbage to 
sqlite3_finalize. You shouldn't call it at all.

> sqlite3_close(banco);

What's the relationship between variables named banco and mydb? Why are you 
using one in open, but the other in close?

> rc = sqlite3_bind_blob(statement, ++i, (void*) user.getPhoto()[0],

I suspect that should be &user.getPhoto()[0]. Rule of thumb: if you can't get 
it to compile without a cast, you are likely doing something wrong. My psychic 
powers tell me that user.getPhoto() returns vector& or similar. You then 
take the first byte of that vector, and interpret it as a pointer - which of 
course is a complete nonsense.

> rc = sqlite3_step(statement); // The app crashes here!

This is where SQLite tries to dereference that bogus pointer for the first time.
-- 
Igor Tandetnik

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

Re: [sqlite] Sqlite3_step problem

[Stephan Beal]

On Tue, Aug 2, 2011 at 12:49 PM, Prasanth R Kosigi Shroff <
prasanth@globaledgesoft.com> wrote:

> i am trying to run a compiled query and i am passing it through
> sqlite3_step API the return value is SQLITE_DONE. But the same query
> works fine when i execute the same query using command line tool.
> ...what could be the reason
>

Can you show us the C code? Without that we can only guess.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step hold about 0.5sec in iphone.

[Simon Slavin]

On 20 May 2011, at 12:33pm, Hyoseok Kim wrote:

> I develop a app on iphone. I use SQLite for about 120,000 data. In my code,
> sqlite3_step hold 0.5~1.0 sec.
> Please give me a hint to escape sqlite3_step method rapidly.
> 
[snip]
> 
>// 0.5~1.0 sec hold after read last row data.

Do you get the long pause 0.5~1.0 sec after each row, or only after the last 
row of data ?

Are you using 'SELECT * FROM ...' or are you naming the columns you want ?

Can you supply us with your 'SELECT' command, or an example of it ?

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

Re: [sqlite] sqlite3_step hold about 0.5sec in iphone.

[Kees Nuyt]

On Fri, 20 May 2011 20:33:04 +0900, Hyoseok Kim
 wrote:

>Hi, I'm Hyoseok Kim from Korea.
>
>I develop a app on iphone. I use SQLite for about 120,000 data. In my code,
>sqlite3_step hold 0.5~1.0 sec.
>Please give me a hint to escape sqlite3_step method rapidly.
>
>Please help me.
>Thank you so much.
>
>
>My code is below...
>
>   if (sqlite3_prepare_v2(db, query_stmt, -1, &statement, NULL) ==
>SQLITE_OK) {
>
>int rowCount = 10;
>while (sqlite3_step(statement) != SQLITE_DONE) {
>// Some code to retrieve values
>// Looping...
>
>// 0.5~1.0 sec hold after read last row data.
>}
> }

It all depends on the schema, the SELECT statement you use and the
exact working of the loop. With the information you gave it is
hard to tell what is happening.
If you for example SELECT all rows, but only use the first 10 and
step through all other rows without processing them, you force the
sqlite engine to finish the query. You could stop stepping after
the last row you want to process by leaving the while loop and
calling sqlite3_reset().

This papge might be of interest for you:
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step() error and constraints

[Simon Slavin]

On 31 Mar 2011, at 7:08pm, Igor Tandetnik wrote:

> For reasons described in the documentation: 
> http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section 
> at the bottom.



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

Re: [sqlite] sqlite3_step() error and constraints

[Igor Tandetnik]

On 3/31/2011 2:12 PM, Pavel Ivanov wrote:
>> http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section
>> at the bottom.
>
> I believe it was changed with recent versions of SQLite. Is call to
> sqlite3_extended_result_codes
> (http://www.sqlite.org/c3ref/extended_result_codes.html) not needed
> anymore?

It is needed if you are interested in extended result codes. 
SQLITE_CONSTRAINT isn't one of them.
-- 
Igor Tandetnik

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

Re: [sqlite] sqlite3_step() error and constraints

[Pavel Ivanov]

> http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section
> at the bottom.

I believe it was changed with recent versions of SQLite. Is call to
sqlite3_extended_result_codes
(http://www.sqlite.org/c3ref/extended_result_codes.html) not needed
anymore?


Pavel


On Thu, Mar 31, 2011 at 2:08 PM, Igor Tandetnik  wrote:
> On 3/31/2011 2:02 PM, Julien Laffaye wrote:
>> When sqlite3_step() fail because a UNIQUE constraint is not satisfied,
>> it returns SQLITE_ERROR.
>> I see that there is an error code named SQLITE_CONSTRAINT. Why
>> sqlite3_step() does not return this one?
>
> For reasons described in the documentation:
> http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section
> at the bottom.
> --
> Igor Tandetnik
>
> ___
> 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] sqlite3_step() error and constraints

[Julien Laffaye]

On 03/31/2011 19:08, Igor Tandetnik wrote:
> On 3/31/2011 2:02 PM, Julien Laffaye wrote:
>
>> When sqlite3_step() fail because a UNIQUE constraint is not satisfied,
>> it returns SQLITE_ERROR.
>> I see that there is an error code named SQLITE_CONSTRAINT. Why
>> sqlite3_step() does not return this one?
>>  
> For reasons described in the documentation:
> http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section
> at the bottom.
>
Oh, I feel stupid now... Let's use the v2 function then.

Anyway, thank you very much for your quick reply.

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

Re: [sqlite] sqlite3_step() error and constraints

[Igor Tandetnik]

On 3/31/2011 2:02 PM, Julien Laffaye wrote:
> When sqlite3_step() fail because a UNIQUE constraint is not satisfied,
> it returns SQLITE_ERROR.
> I see that there is an error code named SQLITE_CONSTRAINT. Why
> sqlite3_step() does not return this one?

For reasons described in the documentation: 
http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section 
at the bottom.
-- 
Igor Tandetnik

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

Re: [sqlite] sqlite3_step behavior on empty set

[Black, Michael (IS)]

Wouldn't the addition of "Empty result sets will return SQLITE_DONE on the 
first call to sqlite3_step." add some clarity?

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Sunday, March 27, 2011 8:11 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] sqlite3_step behavior on empty set

Sam Carleton  wrote:
> Is my impression correct that when calling sqlite3_step() on a query
> that returns no rows, the result will be [SQLITE_DONE]?

Yes.

> If that is the case, might that be added to the documentation?

"SQLITE_DONE means that the statement has finished executing successfully."
"If the SQL statement being executed returns any data, then SQLITE_ROW is 
returned each time a new row of data is ready for processing by the caller."

The implication is that if SQL statement doesn't return any data, then 
sqlite3_step doesn't return SQLITE_ROW. Since it nevertheless finishes 
executing successfully, it returns SQLITE_DONE.
--
Igor Tandetnik

___
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] sqlite3_step behavior on empty set

[Igor Tandetnik]

Sam Carleton  wrote:
> Is my impression correct that when calling sqlite3_step() on a query
> that returns no rows, the result will be [SQLITE_DONE]?

Yes.

> If that is the case, might that be added to the documentation?

"SQLITE_DONE means that the statement has finished executing successfully."
"If the SQL statement being executed returns any data, then SQLITE_ROW is 
returned each time a new row of data is ready for processing by the caller."

The implication is that if SQL statement doesn't return any data, then 
sqlite3_step doesn't return SQLITE_ROW. Since it nevertheless finishes 
executing successfully, it returns SQLITE_DONE.
-- 
Igor Tandetnik

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

Re: [sqlite] sqlite3_step behavior on empty set

[Simon Slavin]

On 27 Mar 2011, at 9:24pm, Sam Carleton wrote:

> Is my impression correct that when calling sqlite3_step() on a query
> that returns no rows, the result will be [SQLITE_DONE]?  If that is
> the case, might that be added to the documentation?

The SQLite documentation would profit greatly from a list of result values each 
function can generate and what they mean (when it's not obvious).

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

Re: [sqlite] sqlite3_step to select and update the same table

[Stephen Chrzanowski]

Well, with your 5 gig table, the select statement needs to still compile a
set of results somehow, be it data or pointers to the data, and remember
which row has been read.  As I said, I've never looked or traced the code,
but something has be be created somewhere that says "This is the next
record".  If I update the CURRENT record, the order in which the records are
retrieved/stored shouldn't change.  Maybe if just doing a [select * from
MyTable] will yeild a simple pseudo thought of "Start from the first
physical record, then go to the next physical record when needed" but if I
change the statement to [select * from MyTable order by SortOrder] it can't
go by the first physical record in that table, of course, but some sort of
compiled list stating "The first record is physical row B, second is
physical row Z..." etc.

I'm not look'n to piss anyone off, of course, I'm just trying to figure out
how it works in the background.

On Mon, Nov 8, 2010 at 5:25 PM, Simon Slavin  wrote:

> >
> > In other words, when I make a SELECT statement, the results that come
> back
> > would point to the physical locations of where the raw data exists, or,
> > return the data and stores it in memory, or however the mechanism works.
> > Updating should not affect what rows have been called up.
>
> So you require two copies of the data: one which is the data as it was when
> you started the SELECT command and another will all the updates that have
> taken place since then, until _finalize() is called.  This means that the
> SELECT command must reserve enough memory for the entire results of the
> SELECT command, and copy all the data into it to process the initial SELECT
> command.
>
> So if I had a table which took 5 Gig of disk space and did a "SELECT * FROM
> myTable" my application would suddenly need to reserve 5 Gig of memory to
> store the results.  This would make every SELECT very slow and use a lot of
> memory, useless on platforms which need to respond to button-presses in
> realtime or have limited memory.  So I can understand why SQLite doesn't
> allow it.
>
> You can do this yourself, of course: perform the entire SELECT and store
> the results in one or more variables, then use the contents of those
> variables to decide which UPDATE commands to do.  Or you can just use the
> UPDATE command with a WHERE clause, which does both commands in one go and
> is far more efficient.
>
> Simon.
> ___
> 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] sqlite3_step to select and update the same table

[Woody & Yuni Ho]

Sql can do it. It just takes a properly designed query.


Woody wizard at large(I'm in shape. Round is a shape)
Connected by MOTOBLUR™ on T-Mobile

-Original message-
From: cricketfan 
To: sqlite-users@sqlite.org
Sent: Mon, Nov 8, 2010 19:24:54 PST
Subject: Re: [sqlite] sqlite3_step to select and update the same table


SQL will not do the trick for me because I based on the select I have to
perform other operations(queries on other tables) and only then can I update
the table in question.

Kees Nuyt wrote:
> 
> There is no need to do this in a loop with a cursor.
> Whenever possible, use the power of SQL set operations.
> As far as I can tell, an alternative solution to
> your problem could be (pseudocode):
> 
> delSql = "UPDATE table1
>SET ghi = ?
>  WHERE def IN (
>   SELECT ref 
> FROM table1
>WHERE abc = ?
>   );";
> prepare(db, delSql, ..., stmt, ...);
> bind_int(stmt, ..., ghivalue);
> bind_text(stmt, ..., abcvalue);
> step(stmt);
> reset(stmt);
> finalize(stmt);
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30166069.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] sqlite3_step to select and update the same table

[cricketfan]

SQL will not do the trick for me because I based on the select I have to
perform other operations(queries on other tables) and only then can I update
the table in question.

Kees Nuyt wrote:
> 
> There is no need to do this in a loop with a cursor.
> Whenever possible, use the power of SQL set operations.
> As far as I can tell, an alternative solution to
> your problem could be (pseudocode):
> 
> delSql = "UPDATE table1
>SET ghi = ?
>  WHERE def IN (
>   SELECT ref 
> FROM table1
>WHERE abc = ?
>   );";
> prepare(db, delSql, ..., stmt, ...);
> bind_int(stmt, ..., ghivalue);
> bind_text(stmt, ..., abcvalue);
> step(stmt);
> reset(stmt);
> finalize(stmt);
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30166069.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 8 Nov 2010, at 9:27pm, Stephen Chrzanowski wrote:

> I don't know how the code works, but logically speaking, if I'm at row B,
> and I update row B to E, row B physically remains B but has its data content
> changed to E.  From there on in, it should go on to C then D then F, etc.

And naturally, since you are reading the row and it was retrieved in 'B' order, 
it is vital that the contents of the field is 'B'.  Otherwise your code will 
not find the data in the order it has asked for it.

> Since the full rowset results already exist somewhere, it shouldn't show up
> anywhere else down the line, simply because the updated or even new
> recordset isn't part of the compiled result set list.
> 
> In other words, when I make a SELECT statement, the results that come back
> would point to the physical locations of where the raw data exists, or,
> return the data and stores it in memory, or however the mechanism works.
> Updating should not affect what rows have been called up.

So you require two copies of the data: one which is the data as it was when you 
started the SELECT command and another will all the updates that have taken 
place since then, until _finalize() is called.  This means that the SELECT 
command must reserve enough memory for the entire results of the SELECT 
command, and copy all the data into it to process the initial SELECT command.

So if I had a table which took 5 Gig of disk space and did a "SELECT * FROM 
myTable" my application would suddenly need to reserve 5 Gig of memory to store 
the results.  This would make every SELECT very slow and use a lot of memory, 
useless on platforms which need to respond to button-presses in realtime or 
have limited memory.  So I can understand why SQLite doesn't allow it.

You can do this yourself, of course: perform the entire SELECT and store the 
results in one or more variables, then use the contents of those variables to 
decide which UPDATE commands to do.  Or you can just use the UPDATE command 
with a WHERE clause, which does both commands in one go and is far more 
efficient.

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

Re: [sqlite] sqlite3_step to select and update the same table

[Kees Nuyt]

On Sun, 7 Nov 2010 10:14:29 -0800 (PST), cricketfan
 wrote:

>Just to make things clearer
>the value being fetched into ref from the database, is also the value being
>changed(ghi) in the update statement. When I change my query (just to debug)
>to update some other column in the table the whole thing runs fine and runs
>only once!
>Can someone throw some light on this?

It is still unclear to me why you insist using a cursor.
A single UPDATE statement with a proper WHERE clause should
do the job. I already suggested code for that.

Since your first example wasn't exact, and the second is not
complete I'm not really tempted to look into it.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Stephen Chrzanowski]

I don't know how the code works, but logically speaking, if I'm at row B,
and I update row B to E, row B physically remains B but has its data content
changed to E.  From there on in, it should go on to C then D then F, etc.

Since the full rowset results already exist somewhere, it shouldn't show up
anywhere else down the line, simply because the updated or even new
recordset isn't part of the compiled result set list.

In other words, when I make a SELECT statement, the results that come back
would point to the physical locations of where the raw data exists, or,
return the data and stores it in memory, or however the mechanism works.
Updating should not affect what rows have been called up.

On Sun, Nov 7, 2010 at 9:16 PM, Simon Slavin  wrote:

>
> On 8 Nov 2010, at 2:02am, cricketfan wrote:
>
> > Simon, As per my understanding I am getting the result set and trying to
> > change values in the table based on what I read from the result set up to
> > that point. I see no reason why I should be stopped from updating the row
> I
> > have already read in the result set.
>
> Your update might change whether the row is in the result set.  Or it might
> change /where/ the row falls in the result set.  For instance, suppose you
> have the following rows:
>
> A
> B
> C
> D
> F
>
> You read as far as B, then decide to change the B to an E.  Now what should
> happen ?  Should that record appear again when you get past the D ?  But it
> might be a problem to include the same record twice.  How should SQL know
> what it should do ?  So SQL prevents you from making changes which effect an
> open SELECT.
>
> Simon.
> ___
> 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] sqlite3_step to select and update the same table

[Simon Slavin]

On 8 Nov 2010, at 2:02am, cricketfan wrote:

> Simon, As per my understanding I am getting the result set and trying to
> change values in the table based on what I read from the result set up to
> that point. I see no reason why I should be stopped from updating the row I
> have already read in the result set.

Your update might change whether the row is in the result set.  Or it might 
change /where/ the row falls in the result set.  For instance, suppose you have 
the following rows:

A
B
C
D
F

You read as far as B, then decide to change the B to an E.  Now what should 
happen ?  Should that record appear again when you get past the D ?  But it 
might be a problem to include the same record twice.  How should SQL know what 
it should do ?  So SQL prevents you from making changes which effect an open 
SELECT.

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

Re: [sqlite] sqlite3_step to select and update the same table

[cricketfan]

Simon, As per my understanding I am getting the result set and trying to
change values in the table based on what I read from the result set up to
that point. I see no reason why I should be stopped from updating the row I
have already read in the result set. 

Simon Slavin-3 wrote:
> 
> 
> On 7 Nov 2010, at 6:14pm, cricketfan wrote:
> I don't understand why you find this surprising.  You have two pieces of
> program: one is trying to look at something while the other is in the
> middle of changing it.  They are going to argue about which one can access
> those values.
> 
> You can complete the scan first, then make the changes you want.  Or if
> the changes depend on the scan, use an UPDATE ... WHERE command that does
> all the work for you.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30157400.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 7 Nov 2010, at 6:14pm, cricketfan wrote:

> Just to make things clearer
> the value being fetched into ref from the database, is also the value being
> changed(ghi) in the update statement. When I change my query (just to debug)
> to update some other column in the table the whole thing runs fine and runs
> only once!
> Can someone throw some light on this?

I don't understand why you find this surprising.  You have two pieces of 
program: one is trying to look at something while the other is in the middle of 
changing it.  They are going to argue about which one can access those values.

You can complete the scan first, then make the changes you want.  Or if the 
changes depend on the scan, use an UPDATE ... WHERE command that does all the 
work for you.

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

Re: [sqlite] sqlite3_step to select and update the same table

[cricketfan]

Just to make things clearer
the value being fetched into ref from the database, is also the value being
changed(ghi) in the update statement. When I change my query (just to debug)
to update some other column in the table the whole thing runs fine and runs
only once!
Can someone throw some light on this?

cricketfan wrote:
> 
> rc = sqlite3_bind_text(stmt, 1, temp, (int)strlen(temp), NULL);
> while(sqlite3_step(stmt) == SQLITE_ROW) { 
>   ref = sqlite3_column_int(stmt,3);
>   delSql = sqlite3_mprintf("UPDATE table1 SET ghi = %d WHERE def = %d
> ;",1,ref);
>   if( (rc = sqlite3_exec(db, delSql, NULL, NULL, &zErrMsg)) != SQLITE_OK )
> { sqlite3_free(zErrMsg); goto error; }
>   }
> Thanks
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30153073.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3_step to select and update the same table

[Kees Nuyt]

On Sat, 6 Nov 2010 21:29:58 -0700 (PDT), cricketfan
 wrote:

>I am trying to select some columns from a table and use that to
>update another column in the same table using prepare/step/reset/finalize
>methods. However, when I use the update statement while stepping it is
>executing the flow 2 times. 

There is no need to do this in a loop with a cursor.
Whenever possible, use the power of SQL set operations.
As far as I can tell, an alternative solution to
your problem could be (pseudocode):

delSql = "UPDATE table1
   SET ghi = ?
 WHERE def IN (
SELECT ref 
  FROM table1
 WHERE abc = ?
);";
prepare(db, delSql, ..., stmt, ...);
bind_int(stmt, ..., ghivalue);
bind_text(stmt, ..., abcvalue);
step(stmt);
reset(stmt);
finalize(stmt);

Hope this helps.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 7 Nov 2010, at 4:29am, cricketfan wrote:

>I am trying to select some columns from a table and use that to
> update another column in the same table using prepare/step/reset/finalize
> methods. However, when I use the update statement while stepping it is
> executing the flow 2 times.

You can't UPDATE a table while you're in the middle of stepping through a 
SELECT.  Because the UPDATE might change one of the things that governs the 
results of the SELECT command and cause it to repeat or skip records.  The 
neatest way to do what you want is to first perform the SELECT, read all the 
rows into an array, and finalize it.  Then perform any UPDATE you need.

An alternative would be to address the database as if you're using two 
different processes with two different database handles.  But depending on how 
you're accessing the database this may merely delay your UDPATE thread until 
the SELECT thread is finished.

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

Re: [sqlite] sqlite3_step returns sqlite_busy

[Pavel Ivanov]

> So it means we can have mor than one valid db handle?

Yes.

Pavel

On Wed, Jul 7, 2010 at 9:29 AM, Lloyd  wrote:
> So it means we can have mor than one valid db handle?
>
> Thanks,
>  Lloyd
>
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Sent: Wednesday, July 07, 2010 6:40 PM
> Subject: Re: [sqlite] sqlite3_step returns sqlite_busy
>
>
>>> one thread is preparing an "INSERT" statement (It returns SQLITE_OK),
>>> then it is executed using sqlite3_step. sqlite3_step returns an
>>> SQLITE_BUSY! Is there any possibility for this?
>>
>> Sure. Preparing INSERT statement doesn't acquire any "write" locks on
>> the database. It's executing the INSERT statement that acquires locks.
>> So if while executing statement finds that it can't get the locks then
>> sqlite3_step returns SQLITE_BUSY. And thread-safety is irrelevant
>> here.
>>
>>
>> Pavel
>>
>
>
> __
> Scanned and protected by Email scanner
> ___
> 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] sqlite3_step returns sqlite_busy

[Lloyd]

So it means we can have mor than one valid db handle?

Thanks,
  Lloyd

- Original Message - 
From: "Pavel Ivanov" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, July 07, 2010 6:40 PM
Subject: Re: [sqlite] sqlite3_step returns sqlite_busy


>> one thread is preparing an "INSERT" statement (It returns SQLITE_OK),
>> then it is executed using sqlite3_step. sqlite3_step returns an
>> SQLITE_BUSY! Is there any possibility for this?
> 
> Sure. Preparing INSERT statement doesn't acquire any "write" locks on
> the database. It's executing the INSERT statement that acquires locks.
> So if while executing statement finds that it can't get the locks then
> sqlite3_step returns SQLITE_BUSY. And thread-safety is irrelevant
> here.
> 
> 
> Pavel
> 


__
Scanned and protected by Email scanner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step returns sqlite_busy

[Pavel Ivanov]

> one thread is preparing an "INSERT" statement (It returns SQLITE_OK),
> then it is executed using sqlite3_step. sqlite3_step returns an
> SQLITE_BUSY! Is there any possibility for this?

Sure. Preparing INSERT statement doesn't acquire any "write" locks on
the database. It's executing the INSERT statement that acquires locks.
So if while executing statement finds that it can't get the locks then
sqlite3_step returns SQLITE_BUSY. And thread-safety is irrelevant
here.


Pavel

On Wed, Jul 7, 2010 at 7:56 AM, Lloyd  wrote:
> Hi,
>
> We have a multi-threaded application (I know "threads are evil", but this is 
> a small server application). Each thread tries to access the SQLite database. 
> When trying to get a DB handle (sqlite3_open), if it returns SQLITE_BUSY, 
> then the thread will wait for some time and try to open it again (until it 
> gets the handle). In our case, two threads are running, one thread is 
> preparing an "INSERT" statement (It returns SQLITE_OK), then it is executed 
> using sqlite3_step. sqlite3_step returns an SQLITE_BUSY! Is there any 
> possibility for this? ( I think these two threads are obtaining the DB handle 
> at the same time using sqlite_open - with wrtie permission- is it possible?) 
> I have tried compiling the application with both  -DSQLITE_THREADSAFE=2 and 
> -DSQLITE_THREADSAFE=1.
>
> Any hint would be very helpful...
>
> Thanks a lot,
>  Lloyd
>
> __
> Scanned and protected by Email scanner
> ___
> 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] sqlite3_step() transaction

[Simon Slavin]

On 9 Apr 2010, at 11:57am, Igor Tandetnik wrote:

> Christoph Walser, ETH Zurich wrote:
>> Is there a way to do all the 5000 rows in one transaction to minimize
>> database locking time? I know I can do a sqlite3_exec with "BEGIN
>> TRANSACTION", then do the inserts and then "COMMIT TRANSACTIONS" but how
>> does this work when using the bind, step and reset functions?
> 
> Yes it does.

Just to make it clear, Igor is recommending that you do explicitly call

BEGIN TRANSACTION
do the 5000 inserts
END TRANSACTION

as this will speed up your calls and reduce the amount of disk activity quite a 
lot.

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

Re: [sqlite] sqlite3_step() transaction

[Igor Tandetnik]

Christoph Walser, ETH Zurich wrote:
> I have a C application which generates around 1000 rows of data per
> second to insert in an SQLite database.
> What I now do is to collect 5000 rows in a buffer and then for each row,
> execute sqlite3_bind_*, sqlite3_step, sqlite3_reset.
> 
> I wonder now how sqlite does this internaly: with the above mentioned
> technique, is every row capsulated into a single transaction or are they
> one large transaction alltogether?

If you don't issue explicit BEGIN and COMMIT statements, then each row is a 
separate transaction.

> Is there a way to do all the 5000 rows in one transaction to minimize
> database locking time? I know I can do a sqlite3_exec with "BEGIN
> TRANSACTION", then do the inserts and then "COMMIT TRANSACTIONS" but how
> does this work when using the bind, step and reset functions?

Yes it does.
-- 
Igor Tandetnik

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

Re: [sqlite] sqlite3_step()

[Max Vlasov]

Mike,
as the docs on the page http://www.sqlite.org/c3ref/stmt.html says:

1. sqlite3_prepare_v2()
2. sqlite3_bind_*()
3. sqlite3_step() one or more times
4. sqlite3_reset()
5. goto 2 or sqlite3_finalize().

As you may know sqlite3_prepare_* compiles your SQL query into a VDBE
micro-program with interpreted instructions how to execute the query. I see
reasons why sometimes bind_* can just affect the contents of one register in
this program and nothing more, but sometimes your new bindings can affect
the way the micro-program is executed.

I see some interesting variant of optimization in your question that is
theoretically might be possible. For example your query contains some
expresion about rowid evaluation (like rowid > ?) and on some step, the row
you just read can contain a new information to bypass some of your following
steps, for example initially you wanted to scan rows with rowid > 50 and now
you're ready to bypass some of them and go to > 5000. Changing the binded
value in this case looks like some kind of optimization, but speed advangage
from my point of view is not very big since after _reset sqlite will jump to
that new value almost as fast. But if your new value is lower than 50 (for
example > 20), you will get logic error since the microcode has already
passed values bigger than 20 and there's no way for the microcode to detect
your new requirements and make conditional reset.

So just always follow the pattern of calls described earlier

Max





On Sat, Jan 16, 2010 at 4:10 PM, Mike Johnston wrote:

> Using sqlite 3.6.22.  Here's my sql
> SELECT sno, lname, fname FROM addressbook where sno>?
> Suppose your stepping through a 100 row result set.  I'm calling
> sqlite3_step(), getting my values all good.  If I were to call
> sqlite3_bind_int() function with a new value for my where clause at row 50,
> does that cause the step to re-execute the entire SQL?  Do I have to do a
> reset, bind, step sequence ?
> When does sqlite3_step() evaluate the bound values in the where clause?
> Thank you,Mike
>
>
>
> ___
> 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] sqlite3_step()

[Simon Davies]

2010/1/16 Mike Johnston :
> Using sqlite 3.6.22.  Here's my sql
> SELECT sno, lname, fname FROM addressbook where sno>?
> Suppose your stepping through a 100 row result set.  I'm calling 
> sqlite3_step(), getting my values all good.  If I were to call 
> sqlite3_bind_int() function with a new value for my where clause at row 50, 
> does that cause the step to re-execute the entire SQL?  Do I have to do a 
> reset, bind, step sequence ?

>From http://www.sqlite.org/c3ref/bind_blob.html:
"If any of the sqlite3_bind_*() routines are called with a NULL
pointer for the prepared statement or with a prepared statement for
which sqlite3_step() has been called more recently than
sqlite3_reset(), then the call will return SQLITE_MISUSE. If any
sqlite3_bind_() routine is passed a prepared statement that has been
finalized, the result is undefined and probably harmful."

In your scenario above you should get a SQLITE_MISUSE error.

> When does sqlite3_step() evaluate the bound values in the where clause?
> Thank you,Mike
>

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

Re: [sqlite] sqlite3_step performance degredation

[Jim Wilcoxson]

There was a recent SQLite bug that caused the size of the SQLite cache
to shrink in some circumstances, and the longer a program ran, the
smaller the cache became.  Maybe you are running into this bug.  IIRC,
you had to do an update in the select loop to trigger the bug, so if
you're not doing that, maybe this doesn't apply.

Jim

On 6/15/09, Mike Borland  wrote:
> Nuno, unfortunately your psychic skills are a bit off on this one.  Sorry I
> wasn't more explicit.  I am not using any LIMIT or OFFSET to do any virtual
> scrolling.  Basically I have table A which has 900 rows.  Table B has
> 180,000 rows (900 * 200) which has a foreign key relationship back to table
> A.  So for each row in table A, there are 200 rows in table B.  My query is
> basically a "SELECT * FROM Table B WHERE ID = TableA.ID".  I'm executing
> this query 900 times, once for each row in table A.
>
> When I start the 900 read iterations (always in the same order), the first
> one generally reads in about 50ms and by the last read, it's taking roughly
> 1000ms.  Sometimes it slows down immediately, sometimes after the 100th
> iteration.  The only absolutely reproducible aspect is that it always slows
> down eventually and once it slows down, it never speeds back up.  I don't
> believe it's a locking issue since my timer doesn't start until the query is
> successfully executed.
>
> Any ideas?  Would the occasional write operation in the midst of these reads
> cause any permanent slow down to the read time?  Thanks.
>
> Mike Borland
>
> -Original Message-
> From: Nuno Lucas [mailto:ntlu...@gmail.com]
> Sent: Friday, June 12, 2009 7:16 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3_step performance degredation
>
> On Sat, Jun 13, 2009 at 1:52 AM, Mike
> Borland wrote:
>> I have a fairly complex program where I am seeing a performance
>> degradation of the sqlite3_step() function.  Basically I'm iterating
>> roughly 200 rows at a time, over and over.  The only work happening
>> during the iteration is I'm copying the record into an array.  At first,
>> sqlite3_step() takes less than a millisecond to run.  After 0-50
>> iterations, it's taking anywhere from 10-100ms.
>>
>> Does anybody have any insight into what's happening behind the scenes
>> with this function to help me track down the cause?  I appreciate it!
>
> You should explicitly say what your SQL query is. Without that we can
> only guess.
>
> My current "psychic" guess is that you are using LIMIT to obtain those
> 200 rows, one "page" at a time, and as you go advancing "pages" it
> becomes slower and slower.
> If this is true, then you should re-think your design as LIMIT just
> skips the rows, but it will  "generate" them before, meaning it
> becomes slower as you advance on the offset given.
> Look at http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor (and
> notice the "What not to do" at the end, talking about "LIMIT" and
> "OFFSET").
>
> If my my psychic abilities are becoming weak, then please supply your
> exact query that is getting slower  (and maybe your database schema)
> and then someone can give you an exact answer.
>
>
> Regards,
> ~Nuno Lucas
>
>>
>> Mike Borland
>>
>> ___
>> 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
>


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

Re: [sqlite] sqlite3_step performance degredation

[Simon Slavin]

On 15 Jun 2009, at 10:11pm, Mike Borland wrote:

>
> When I start the 900 read iterations (always in the same order), the  
> first one generally reads in about 50ms and by the last read, it's  
> taking roughly 1000ms.  Sometimes it slows down immediately,  
> sometimes after the 100th iteration.

Put some code in your application that closes and reopens the database  
after iteration 500 (or every 100).  See if it speeds up when you've  
just done that.  If it does, the bug is somewhere in your code or  
SQLite.  If it doesn't, the bug is somewhere in memory management or  
disk access.

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

Re: [sqlite] sqlite3_step performance degredation

[Ken]

Also is there an index on the table B.ID field?

--- On Mon, 6/15/09, Mike Borland  wrote:

> From: Mike Borland 
> Subject: Re: [sqlite] sqlite3_step performance degredation
> To: "General Discussion of SQLite Database" 
> Date: Monday, June 15, 2009, 4:11 PM
> Nuno, unfortunately your psychic
> skills are a bit off on this one.  Sorry I wasn't more
> explicit.  I am not using any LIMIT or OFFSET to do any
> virtual scrolling.  Basically I have table A which has
> 900 rows.  Table B has 180,000 rows (900 * 200) which
> has a foreign key relationship back to table A.  So for
> each row in table A, there are 200 rows in table B.  My
> query is basically a "SELECT * FROM Table B WHERE ID =
> TableA.ID".  I'm executing this query 900 times, once
> for each row in table A.  
> 
> When I start the 900 read iterations (always in the same
> order), the first one generally reads in about 50ms and by
> the last read, it's taking roughly 1000ms.  Sometimes
> it slows down immediately, sometimes after the 100th
> iteration.  The only absolutely reproducible aspect is
> that it always slows down eventually and once it slows down,
> it never speeds back up.  I don't believe it's a
> locking issue since my timer doesn't start until the query
> is successfully executed.
> 
> Any ideas?  Would the occasional write operation in
> the midst of these reads cause any permanent slow down to
> the read time?  Thanks.
> 
> Mike Borland
> 
> -Original Message-----
> From: Nuno Lucas [mailto:ntlu...@gmail.com]
> 
> Sent: Friday, June 12, 2009 7:16 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3_step performance degredation
> 
> On Sat, Jun 13, 2009 at 1:52 AM, Mike
> Borland
> wrote:
> > I have a fairly complex program where I am seeing a
> performance
> > degradation of the sqlite3_step() function.
>  Basically I'm iterating
> > roughly 200 rows at a time, over and over.  The only
> work happening
> > during the iteration is I'm copying the record into an
> array.  At first,
> > sqlite3_step() takes less than a millisecond to run.
>  After 0-50
> > iterations, it's taking anywhere from 10-100ms.
> >
> > Does anybody have any insight into what's happening
> behind the scenes
> > with this function to help me track down the cause?
>  I appreciate it!
> 
> You should explicitly say what your SQL query is. Without
> that we can
> only guess.
> 
> My current "psychic" guess is that you are using LIMIT to
> obtain those
> 200 rows, one "page" at a time, and as you go advancing
> "pages" it
> becomes slower and slower.
> If this is true, then you should re-think your design as
> LIMIT just
> skips the rows, but it will  "generate" them before,
> meaning it
> becomes slower as you advance on the offset given.
> Look at http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> (and
> notice the "What not to do" at the end, talking about
> "LIMIT" and
> "OFFSET").
> 
> If my my psychic abilities are becoming weak, then please
> supply your
> exact query that is getting slower  (and maybe your
> database schema)
> and then someone can give you an exact answer.
> 
> 
> Regards,
> ~Nuno Lucas
> 
> >
> > Mike Borland
> >
> > ___
> > 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] sqlite3_step performance degredation

[Ken]

Mike, 

Not 100% sure of the prior information but a write could slow things down. It 
would basically gain the lock to the DB preventing the reads from happening. 
But it should not be permanent.

If you are reading all of the data. Could you just execute one query instead of 
iterating  over all of table A?

I think that would be faster overall and prevent any locking issues.

--- On Mon, 6/15/09, Mike Borland  wrote:

> From: Mike Borland 
> Subject: Re: [sqlite] sqlite3_step performance degredation
> To: "General Discussion of SQLite Database" 
> Date: Monday, June 15, 2009, 4:11 PM
> Nuno, unfortunately your psychic
> skills are a bit off on this one.  Sorry I wasn't more
> explicit.  I am not using any LIMIT or OFFSET to do any
> virtual scrolling.  Basically I have table A which has
> 900 rows.  Table B has 180,000 rows (900 * 200) which
> has a foreign key relationship back to table A.  So for
> each row in table A, there are 200 rows in table B.  My
> query is basically a "SELECT * FROM Table B WHERE ID =
> TableA.ID".  I'm executing this query 900 times, once
> for each row in table A.  
> 
> When I start the 900 read iterations (always in the same
> order), the first one generally reads in about 50ms and by
> the last read, it's taking roughly 1000ms.  Sometimes
> it slows down immediately, sometimes after the 100th
> iteration.  The only absolutely reproducible aspect is
> that it always slows down eventually and once it slows down,
> it never speeds back up.  I don't believe it's a
> locking issue since my timer doesn't start until the query
> is successfully executed.
> 
> Any ideas?  Would the occasional write operation in
> the midst of these reads cause any permanent slow down to
> the read time?  Thanks.
> 
> Mike Borland
> 
> -Original Message-----
> From: Nuno Lucas [mailto:ntlu...@gmail.com]
> 
> Sent: Friday, June 12, 2009 7:16 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3_step performance degredation
> 
> On Sat, Jun 13, 2009 at 1:52 AM, Mike
> Borland
> wrote:
> > I have a fairly complex program where I am seeing a
> performance
> > degradation of the sqlite3_step() function.
>  Basically I'm iterating
> > roughly 200 rows at a time, over and over.  The only
> work happening
> > during the iteration is I'm copying the record into an
> array.  At first,
> > sqlite3_step() takes less than a millisecond to run.
>  After 0-50
> > iterations, it's taking anywhere from 10-100ms.
> >
> > Does anybody have any insight into what's happening
> behind the scenes
> > with this function to help me track down the cause?
>  I appreciate it!
> 
> You should explicitly say what your SQL query is. Without
> that we can
> only guess.
> 
> My current "psychic" guess is that you are using LIMIT to
> obtain those
> 200 rows, one "page" at a time, and as you go advancing
> "pages" it
> becomes slower and slower.
> If this is true, then you should re-think your design as
> LIMIT just
> skips the rows, but it will  "generate" them before,
> meaning it
> becomes slower as you advance on the offset given.
> Look at http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> (and
> notice the "What not to do" at the end, talking about
> "LIMIT" and
> "OFFSET").
> 
> If my my psychic abilities are becoming weak, then please
> supply your
> exact query that is getting slower  (and maybe your
> database schema)
> and then someone can give you an exact answer.
> 
> 
> Regards,
> ~Nuno Lucas
> 
> >
> > Mike Borland
> >
> > ___
> > 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] sqlite3_step performance degredation

[Mike Borland]

Nuno, unfortunately your psychic skills are a bit off on this one.  Sorry I 
wasn't more explicit.  I am not using any LIMIT or OFFSET to do any virtual 
scrolling.  Basically I have table A which has 900 rows.  Table B has 180,000 
rows (900 * 200) which has a foreign key relationship back to table A.  So for 
each row in table A, there are 200 rows in table B.  My query is basically a 
"SELECT * FROM Table B WHERE ID = TableA.ID".  I'm executing this query 900 
times, once for each row in table A.  

When I start the 900 read iterations (always in the same order), the first one 
generally reads in about 50ms and by the last read, it's taking roughly 1000ms. 
 Sometimes it slows down immediately, sometimes after the 100th iteration.  The 
only absolutely reproducible aspect is that it always slows down eventually and 
once it slows down, it never speeds back up.  I don't believe it's a locking 
issue since my timer doesn't start until the query is successfully executed.

Any ideas?  Would the occasional write operation in the midst of these reads 
cause any permanent slow down to the read time?  Thanks.

Mike Borland

-Original Message-
From: Nuno Lucas [mailto:ntlu...@gmail.com] 
Sent: Friday, June 12, 2009 7:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite3_step performance degredation

On Sat, Jun 13, 2009 at 1:52 AM, Mike
Borland wrote:
> I have a fairly complex program where I am seeing a performance
> degradation of the sqlite3_step() function.  Basically I'm iterating
> roughly 200 rows at a time, over and over.  The only work happening
> during the iteration is I'm copying the record into an array.  At first,
> sqlite3_step() takes less than a millisecond to run.  After 0-50
> iterations, it's taking anywhere from 10-100ms.
>
> Does anybody have any insight into what's happening behind the scenes
> with this function to help me track down the cause?  I appreciate it!

You should explicitly say what your SQL query is. Without that we can
only guess.

My current "psychic" guess is that you are using LIMIT to obtain those
200 rows, one "page" at a time, and as you go advancing "pages" it
becomes slower and slower.
If this is true, then you should re-think your design as LIMIT just
skips the rows, but it will  "generate" them before, meaning it
becomes slower as you advance on the offset given.
Look at http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor (and
notice the "What not to do" at the end, talking about "LIMIT" and
"OFFSET").

If my my psychic abilities are becoming weak, then please supply your
exact query that is getting slower  (and maybe your database schema)
and then someone can give you an exact answer.


Regards,
~Nuno Lucas

>
> Mike Borland
>
> ___
> 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] sqlite3_step performance degredation

[Nuno Lucas]

On Sat, Jun 13, 2009 at 1:52 AM, Mike
Borland wrote:
> I have a fairly complex program where I am seeing a performance
> degradation of the sqlite3_step() function.  Basically I'm iterating
> roughly 200 rows at a time, over and over.  The only work happening
> during the iteration is I'm copying the record into an array.  At first,
> sqlite3_step() takes less than a millisecond to run.  After 0-50
> iterations, it's taking anywhere from 10-100ms.
>
> Does anybody have any insight into what's happening behind the scenes
> with this function to help me track down the cause?  I appreciate it!

You should explicitly say what your SQL query is. Without that we can
only guess.

My current "psychic" guess is that you are using LIMIT to obtain those
200 rows, one "page" at a time, and as you go advancing "pages" it
becomes slower and slower.
If this is true, then you should re-think your design as LIMIT just
skips the rows, but it will  "generate" them before, meaning it
becomes slower as you advance on the offset given.
Look at http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor (and
notice the "What not to do" at the end, talking about "LIMIT" and
"OFFSET").

If my my psychic abilities are becoming weak, then please supply your
exact query that is getting slower  (and maybe your database schema)
and then someone can give you an exact answer.


Regards,
~Nuno Lucas

>
> Mike Borland
>
> ___
> 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] sqlite3_step performance degredation

[Mike Borland]

Simon,

Thanks for the reply!  I have read that documentation and everything
else that google search would bring up on the site.  Memory is constant.
When you ask about "disposing of the result of one step before
proceeding to the next", what exactly do you mean?  Can't I
theoretically call sqlite3_step() multiple times just to iterate rows?
I happen to be binding the data in each row, but I thought that is
optional.

Mike Borland

-Original Message-
From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] 
Sent: Friday, June 12, 2009 6:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite3_step performance degredation


On 13 Jun 2009, at 1:52am, Mike Borland wrote:

> I have a fairly complex program where I am seeing a performance
> degradation of the sqlite3_step() function.  Basically I'm iterating
> roughly 200 rows at a time, over and over.  The only work happening
> during the iteration is I'm copying the record into an array.  At  
> first,
> sqlite3_step() takes less than a millisecond to run.  After 0-50
> iterations, it's taking anywhere from 10-100ms.

Read <http://www.sqlite.org/c3ref/step.html>, if you haven't already  
done so.

Check your memory usage as you get the slow-down.  Do you have a  
resource leak of some kind ?  Are you, for example, correctly  
disposing of the result of one step before proceeding to the next ?

Simon.
___
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] sqlite3_step performance degredation

[Simon Slavin]

On 13 Jun 2009, at 1:52am, Mike Borland wrote:

> I have a fairly complex program where I am seeing a performance
> degradation of the sqlite3_step() function.  Basically I'm iterating
> roughly 200 rows at a time, over and over.  The only work happening
> during the iteration is I'm copying the record into an array.  At  
> first,
> sqlite3_step() takes less than a millisecond to run.  After 0-50
> iterations, it's taking anywhere from 10-100ms.

Read , if you haven't already  
done so.

Check your memory usage as you get the slow-down.  Do you have a  
resource leak of some kind ?  Are you, for example, correctly  
disposing of the result of one step before proceeding to the next ?

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

Re: [sqlite] sqlite3_step() returning SQLITE_OK

[Dan]

On Apr 3, 2009, at 8:03 PM, Radcon Entec wrote:

> According to the documentation, assuming I'm reading it correctly,  
> and assuming there are no locks on the database, and assuming the  
> query sent to sqlite3_prepare_v2() was valid, sqlite3_step() should  
> return either SQLITE_ROW or SQLITE_DONE.  I am seeing a different  
> result after executing a DELETE query: it is returning SQLITE_OK.   
> This makes sense for a query that returns no rows, such as a DELETE  
> query, but it doesn't match the documentation.  Is this normal  
> behavior?

Doesn't sound right to me. What SQLite version? Can you post code that
reproduces this?

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

Re: [sqlite] sqlite3_step() returning SQLITE_OK

[Lukhnos D. Liu]

On Apr 3, 2009, at 9:03 PM, Radcon Entec wrote:
> According to the documentation, assuming I'm reading it correctly,  
> and assuming there are no locks on the database, and assuming the  
> query sent to sqlite3_prepare_v2() was valid, sqlite3_step() should  
> return either SQLITE_ROW or SQLITE_DONE.  I am seeing a different  
> result after executing a DELETE query: it is returning SQLITE_OK.   
> This makes sense for a query that returns no rows, such as a DELETE  
> query, but it doesn't match the documentation.  Is this normal  
> behavior?
> Thank you very much.


On this page (http://www.sqlite.org/c3ref/step.html), it says:

 > In the legacy interface, the return value will be either  
SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE.  
With the "v2" interface, any of the other result codes or extended  
result codes might be returned as well.

That's how one gets SQLITE_OK, rather than SQLITE_DONE.

d.

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

RE: [sqlite] sqlite3_step() returning SQLITE_DONE for query that should return results

[Joe Cosby]

OK, I'm an idiot.

I apologize if anybody wasted their time on this, it turned out to be something 
stupid I was doing.  (An unrelated bug was changing the index value I was 
looking up, and though I thought I turned transactions off for this operation, 
it was turned off for the earlier but not the later part where the problem is, 
so when I went to verify that the value was actually in the column it had been 
rolled back.)

I hate it when I do that.


> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Fri, 18 Jan 2008 17:01:56 -0800
> Subject: [sqlite] sqlite3_step() returning SQLITE_DONE for query that should 
> return results
>
>

-- 
Joe Cosby
http://joecosby.com/ 
I've been on aol for something like ten years.  But I still hate it.

:: Currently listening to The Last Supper, 1973, by Andrew Lloyd Webber/Tim 
Rice, from "Jesus Christ Superstar"
_
Shed those extra pounds with MSN and The Biggest Loser!
http://biggestloser.msn.com/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3_step() crash if didn't use sqlite3_bind_int before it

[Ken]

You Need to bind for each insert, Sqlite does not bind by address it does a 
copy.
   
  You'll need to populate your variables inside the loop then bind then step:
   
 prepare_v2
   
 for(i = ...) {
assign Variables to be bound.
sqlite bind for each variable/column
  step
  }
  
 finalize/reset
   
 HTH
  
 
So I am thinking to have the bindColumns the tcpAcclRec before the for loop 
so no need to bind the column every single insert.
So I took out the bindColumns for tcpAcclRec in the insert function and 
move right after the begin transaction as below

   

Re: [sqlite] sqlite3_step

[Dan Kennedy]

On Thu, 2007-07-05 at 18:26 -0300, Bruno S. Oliveira wrote:
> Hi all,
> 
> I'm having problems with the sqlite3_step function.
> I have a table with only one record and then I execute the
> sqlite3_step twice (with some processing in between). In the first run
> of the function everything goes fine, but, in the second one, it
> returns SQLITE_ROW even with only one record in the table and with no
> modifications in the SQL statement.
> Is this the correct behaviour of that function? Does anyone knows why
> this happens?
> 
> I'm using the sqlite3_prepare before executing the sqlite3_step.

Could be a few things. If you post the problematic code somebody 
will be able to help you.

Dan.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3_step() vs. get_tables() - CPU-Usage

[John Stanton]

A. Klitzing wrote:

Hello,

I need some information about the sqlite3_step() function.
How much CPU-usage does it need in comparison with sqlite3_get_tables()?

I have to implement functions like next(), prev(), last(), first(), 
relative(int) and absolute(int).


#1 Way - sqlite3_step()
I can only step forward with sqlite3_step() and use sqlite3_reset() to get 
first() and iterate through the result to the position for relative() and so on.

#2 Way - sqlite3_get_tables()
I can use get_tables() instead and so I can set my cursor without problems to 
every relative position but I loose nice features that I have with sqlite3_stmt.


So the question is - needs sqlite3_step() a few performance so it is "ok" that I will 
iterate "dirty" through the result everytime?

Regards,
André Klitzing

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Sqlite3_step is how sqlite3_get_tables functions.  If you use step 
directly you will get more eficient processing, but if you intend to 
move all your selected rows into memory before processing them, then 
get_table is appropriate.  Just be wary when you use get_table that you 
could be consuming a large chunk of memory and choking your application 
unless you control it with a LIMIT or similar mechanism.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] sqlite3_step crash?

[Boris Popov]

Thanks, guys. I knew this wasn't going to be enough, I was mostly looking
for suggestions as to how I should pursue the problem and it looks like I'll
have to resort to writing a rusty C sample given that I haven't touched
C/C++ in years. I guess I was still hoping for an "oh yea, we have a bug 123
logged and its been solved post 3.3.4 release" :)

Cheers!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Christian Smith [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 29, 2006 6:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_step crash?

On Wed, 29 Mar 2006 [EMAIL PROTECTED] wrote:

>"Boris Popov" <[EMAIL PROTECTED]> wrote:
>>
>> Does this help at all?
>>
>
>No.  A stack trace, especially one without line number
>information, is of no help whatsoever.


I beg to differ. Any stack trace that includes malloc or free is almost
certainly heap corruption. What the OP should do is:
- Check for buffer overflows.
- Check for multiple free()s of the same memory block.
- Run the software under some memory checker. Not sure what to recommend
  on Windows.

The problem is almost certainly in the OP's code.


>D. Richard Hipp   <[EMAIL PROTECTED]>


Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \


smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] sqlite3_step crash?

[Christian Smith]

On Wed, 29 Mar 2006 [EMAIL PROTECTED] wrote:

>"Boris Popov" <[EMAIL PROTECTED]> wrote:
>>
>> Does this help at all?
>>
>
>No.  A stack trace, especially one without line number
>information, is of no help whatsoever.


I beg to differ. Any stack trace that includes malloc or free is almost
certainly heap corruption. What the OP should do is:
- Check for buffer overflows.
- Check for multiple free()s of the same memory block.
- Run the software under some memory checker. Not sure what to recommend
  on Windows.

The problem is almost certainly in the OP's code.


>D. Richard Hipp   <[EMAIL PROTECTED]>


Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] sqlite3_step crash?

[drh]

"Boris Popov" <[EMAIL PROTECTED]> wrote:
> 
> Does this help at all?
> 

No.  A stack trace, especially one without line number
information, is of no help whatsoever.

If you think the problem is in SQLite and not in your
own code, then you should do as much of the following
as you can:

  (1)  Explain why you think the problem is in SQLite.
  (2)  Tell us exactly what version of SQLite you are
   using and how you obtained it (did you compile it
   yourself or use a precompiled download.)
  (3)  Provide us with your database schema and the
   query you were executing at the time of failure.
  (4)  Provide a sample database.
  (5)  Provide copies of your source code
  (6)  Come up with a reproducible test case that
   demonstrations the problem using the command-line
   shell, sqlite3.exe

--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] sqlite3_step crash?

[Boris Popov]

Here's a stack from VS2005 (comes with SQL2005, so I install that for now)

Unhandled exception at 0x7c91103c (ntdll.dll) in vwnt.exe: 0xC005:
Access violation writing location 0x6c61766e.

>   ntdll.dll!wcsncpy()  + 0x5ad bytes  
[Frames below may be incorrect and/or missing, no symbols loaded for
ntdll.dll]  
msvcrt.dll!free()  + 0xc3 bytes 
sqlite3.dll!sqlite3_thread_cleanup()  + 0x8bd bytes 
sqlite3.dll!sqlite3_free_table()  + 0x3c2a bytes
sqlite3.dll!sqlite3_db_handle()  + 0x2573 bytes 
sqlite3.dll!sqlite3_db_handle()  + 0x3074 bytes 
sqlite3.dll!sqlite3_db_handle()  + 0x347d bytes 
sqlite3.dll!sqlite3_step()  + 0x17d bytes   
vwntoe.dll!NBAPIInnerCall(threadInfoS * tinfo=0x010561c0)  Line 679
+ 0x6 bytes C
vwntoe.dll!IOStateMachine(threadInfoS * t=0x010561c0, int
stateToEnter=0x0001)  Line 1794 + 0x9 bytes C
vwntoe.dll!InitialIOStateMachine(threadInfoS * t=0x010561c0)  Line
1896 + 0x12 bytes   C
msvcrt.dll!_endthreadex()  + 0xa9 bytes 
kernel32.dll!GetModuleFileNameA()  + 0x1b4 bytes

Does this help at all?

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Boris Popov [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 28, 2006 10:32 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite3_step crash?

As a matter of fact sqlite3_reset and sqlite3_finalize also segfault if
invoked on that prepared sqlite3_stmt, so there's something seriously
screwed up, but not immediately obvious what exactly since the previous
sqlite3_column_* and sqlite3_step calls worked just fine to get all 9 rows
that are supposed to be returned... 

Thanks!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Boris Popov [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 28, 2006 10:16 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite3_step crash?

Hmm, I have a fairly specific scenario here where after fetching values from
all the rows in the result set calling sqlite3_step segfaults instead of
returning SQLITE_DONE. I'm at a loss as to how I could extract a little more
info to help figure this out, so any pointers would be greatly appreciated.

Thanks!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.


smime.p7s
Description: S/MIME cryptographic signature

RE: [sqlite] sqlite3_step crash?

[Boris Popov]

As a matter of fact sqlite3_reset and sqlite3_finalize also segfault if
invoked on that prepared sqlite3_stmt, so there's something seriously
screwed up, but not immediately obvious what exactly since the previous
sqlite3_column_* and sqlite3_step calls worked just fine to get all 9 rows
that are supposed to be returned... 

Thanks!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Boris Popov [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 28, 2006 10:16 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite3_step crash?

Hmm, I have a fairly specific scenario here where after fetching values from
all the rows in the result set calling sqlite3_step segfaults instead of
returning SQLITE_DONE. I'm at a loss as to how I could extract a little more
info to help figure this out, so any pointers would be greatly appreciated.

Thanks!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.


smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] sqlite3_step() question

[Kiel W.]

> There's the possibility of make a function called
> sqlite3_step_at_position()?!


Is it not feasable to do this in your wrapper function?  For instance,
in the wrappers I've written an use, I return a set of "rows" and the
user can manipulate them however they wish.  In your application, this
seems to me it would be a solution..

Make the general selection (or a set of 1000 queries) and "step"
through those in memory.  When you need more, get the next set..

Will that work for you?


-- 
Kiel W.
[EMAIL PROTECTED]
--
>> time is swift <<

Re: [sqlite] sqlite3_step() and sqlite3_reset() return values

[Roger Binns]

I tried inserting two rows the same unique index.
sqlite3_step() failed, as expected, but returned 1 (SQLITE_ERROR).
Even sqlite3_errcode() returned 1 (SQLITE_ERROR). I would have
expected error 19 (SQLITE_CONSTRAINT) to be returned.
I got bit by this as well.  However go and look at the docs where
it clearly states what happens.
http://www.sqlite.org/capi3ref.html#sqlite3_step
  The return value will be either SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, 
  SQLITE_ERROR, or SQLITE_MISUSE. 

You have to use finalize or reset to get the actual error code.  That
isn't a burden since you always have to call finalize (or reset) anyway.
Roger