Re: [sqlite] Question about Auto Idexes generated by SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Harish Vishwanath wrote:
> I would like to understand when sqlite generates an autoindex on primary keys 
> of tables. 

An autoindex is created when a column is unique since that is the only way
to know if a value being inserted is a duplicate of an existing one.

One does not need be created if the primary key is an integer since the
existing rowid can be used.  See
http://www.sqlite.org/lang_createtable.html#rowid

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAktrzuoACgkQmOOfHg372QTyfwCgg+jir+ncdod0HdAgDgP0H0Sa
Mh4AmwYEIns0xQwo8yy43F6erVcBXBwO
=gAud
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about Auto Idexes generated by SQLite

[Harish Vishwanath]

Hello,

I am using SQLite with Python (via SQLAlchemy). I would like to understand
when sqlite generates an autoindex on primary keys of tables. I am seeing
inconsistent behaviour in my case, where sqlite is automatically generating
indexes for some tables on their primary keys, but not all tables in the
schema.

I am referring to indexes named as sqlite_autoindex_*. Appreciate your help!

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

Re: [sqlite] how to get the meta type of a column

[gujx]

>If all you have is a void*, you're going to have a very hard time calling
any sqlite3_bind_*() function. 
yes, I just have all values with the type void*, so I can not choose which
sqlite3_bind_*() functions to use.





Jay A. Kreibich-2 wrote:
> 
> On Thu, Feb 04, 2010 at 06:41:46PM -0800, gujx scratched on the wall:
>> 
>> Maybe I didn't express the problem clearly.
>> > e.g. if you have a text value, you use _text(), if you have an int, you
>> > use _int()
> 
>> I just don't know what type I have,
> 
>   If all you have is a void*, you're going to have a very hard time
>   calling any sqlite3_bind_*() function.  I sure hope you know what YOU
>   have, you're just not sure about the place you want to stick it.
> 
>> so I want to get the meta type of the column somebody defined.
> 
>   One more time: columns don't have types.  There is no way to get the
>   affinity (that I'm aware of) other than replicating code internal to
>   SQLite.  Asking again won't change this.
> 
>> e.g. I'd like to use a bind routine to bind a variable t to a "?", but I
>> don't konw this t is a string or a number or it is a object,
> 
>   It doesn't matter.
> 
>> so I want to
>> know that the table is defined like "id(varchar)" ,then I will use
>> sqlite3_bind_text; or it is defined like "id(integer)", then I will
>> choose
>> sqlite3_bind_int.
> 
>   I understand that, but you need to put something in the third
>   parameter.  If you're going to call sqlite3_bind_text(), you need a
>   const char * for the third parameter.  If you're going to call 
>   sqlite3_bind_int(), you need an int for the third parameter.  What
>   you have, in your C code, will best dictate what bind function to
>   use.  SQLite will then do any conversions required, given the column
>   affinity, and it is generally going to do a better and smarter job
>   than your code can because it has more information about what is
>   going on.
> 
>> >  http://sqlite.org/datatype3.html
>> >  
>> >   Read this whole page.  Several times.  Make sure you understand it in
>> >   great detail.
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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/how-to-get-the-meta-type-of-a-column-tp27451326p27463630.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] Restricting fast no-result query yields slow no-result query

[Kelly Jones]

I have a query that runs very quickly and returns no results:

SELECT * FROM filebackup WHERE sha1='x';

However, the more restrictive query below runs very slowly, although
it obviously can't have any results either:

SELECT * FROM filebackup WHERE sha1='x' AND refid=0;

I have indexes on both sha1 (string) and refid (int). Only issue I
see: this is a large table and refid=0 for 90%+ of the rows.

The explains are below. Any reason why sqlite3 chooses the 'wrong'
index and/or how to fix this?

sqlite> EXPLAIN SELECT * FROM filebackup WHERE sha1='x';

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 String80 1 0 x  00
2 Goto   0 26000
3 OpenRead   0 2 0 10 00
4 OpenRead   1 8 0 keyinfo(1,BINARY)  00
5 IsNull 1 23000
6 Affinity   1 1 0 ab 00
7 SeekGe 1 231 1  00
8 IdxGE  1 231 1  01
9 IdxRowid   1 2 000
10Seek   0 2 000
11Column 0 0 300
12Column 0 1 400
13Column 0 2 500
14Column 0 3 600
15Column 0 4 700
16Column 1 0 800
17Column 0 6 9 0  00
18Column 0 7 10   00
19Column 0 8 11   00
20Column 0 9 12   00
21ResultRow  3 10000
22Next   1 8 000
23Close  0 0 000
24Close  1 0 000
25Halt   0 0 000
26Transaction0 0 000
27VerifyCookie   0 12000
28TableLock  0 2 0 filebackup 00
29Goto   0 3 000

sqlite> EXPLAIN SELECT * FROM filebackup WHERE sha1='x' AND refid=0;

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 String80 1 0 x  00
2 Integer0 2 000
3 Goto   0 29000
4 OpenRead   0 2 0 10 00
5 OpenRead   1 9 0 keyinfo(1,BINARY)  00
6 IsNull 2 26000
7 Affinity   2 1 0 db 00
8 SeekGe 1 262 1  00
9 IdxGE  1 262 1  01
10IdxRowid   1 3 000
11Seek   0 3 000
12Column 0 5 300
13Ne 1 253 collseq(BINARY)  69
14Column 0 0 500
15Column 0 1 600
16Column 0 2 700
17Column 0 3 800
18Column 0 4 900
19Column 0 5 10   00
20Column 1 0 110  00
21Column 0 7 12   00
22Column 0 8 13   00
23Column 0 9 14   00
24ResultRow  5 10000
25Next   1 9 000
26Close  0 0 000
27Close  1 0 000
28Halt   0 0 000
29Transaction0 0 000
30VerifyCookie   0 12000
31TableLock  0 2 0 filebackup 00
32Goto   0 4 000
-- 
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the meta type of a column

[Jay A. Kreibich]

On Fri, Feb 05, 2010 at 12:54:30PM +1000, Mark Hessling scratched on the wall:
> I think the OP wanted to know how to find this programatically.

  The .schema command runs a pretty simple query against sqlite_master.
  Just look it up in shell.c... search for \"schema\".

  Similar info (that is much easier to parse) is available from
  PRAGMA table_info().

  One would still have to prepare an SQL statement and extract the
  info, but it is pretty easy to do.

  Unfortunately, it won't tell the OP what they're looking for, since
  those sources only return the literal (as defined) type, not the
  column affinity SQLite derived from the definition type.
 
> In that case see sqlite3_column_type() function call.

  Not very useful for this situation.
  
  That API call returns the *type* of a *value* in a specific
  column of a specific row.  As you step through the results of a
  query, for a given column the type information returned by this
  function can change from row to row.  It doesn't provide information
  on the column affinity, or anything having to do with the table
  definition.
  
  Plus, it only works for an active query.

   -j

> On Fri, 2010-02-05 at 02:47 +, Simon Slavin wrote:
> > On 5 Feb 2010, at 2:41am, gujx wrote:
> > 
> > > Maybe I didn't express the problem clearly.
> > >> e.g. if you have a text value, you use _text(), if you have an int, you
> > >> use _int()
> > > I just don't know what type I have, so I want to get the meta type of the
> > > column somebody defined.
> > > e.g. I'd like to use a bind routine to bind a variable t to a "?", but I
> > > don't konw this t is a string or a number or it is a object, so I want to
> > > know that the table is defined like "id(varchar)" ,then I will use
> > > sqlite3_bind_text; or it is defined like "id(integer)", then I will choose
> > > sqlite3_bind_int.
> > 
> > Open the database in the command-line tool, and use the .schema command:
> > 
> > 
> > 
> > Simon.
> > 
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> -- 
> 
> * Mark Hessling, m...@rexx.org http://www.rexx.org/ 
> * Author of THE, a Free XEDIT/KEDIT editor, Rexx/SQL, Rexx/CURL,  etc. 
> * Maintainer of Regina Rexx interpreter and Rexx/Tk 
> * Use Rexx? join the Rexx Language Association: http://www.rexxla.org/ 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the meta type of a column

[Jay A. Kreibich]

On Thu, Feb 04, 2010 at 06:41:46PM -0800, gujx scratched on the wall:
> 
> Maybe I didn't express the problem clearly.
> > e.g. if you have a text value, you use _text(), if you have an int, you
> > use _int()

> I just don't know what type I have,

  If all you have is a void*, you're going to have a very hard time
  calling any sqlite3_bind_*() function.  I sure hope you know what YOU
  have, you're just not sure about the place you want to stick it.

> so I want to get the meta type of the column somebody defined.

  One more time: columns don't have types.  There is no way to get the
  affinity (that I'm aware of) other than replicating code internal to
  SQLite.  Asking again won't change this.

> e.g. I'd like to use a bind routine to bind a variable t to a "?", but I
> don't konw this t is a string or a number or it is a object,

  It doesn't matter.

> so I want to
> know that the table is defined like "id(varchar)" ,then I will use
> sqlite3_bind_text; or it is defined like "id(integer)", then I will choose
> sqlite3_bind_int.

  I understand that, but you need to put something in the third
  parameter.  If you're going to call sqlite3_bind_text(), you need a
  const char * for the third parameter.  If you're going to call 
  sqlite3_bind_int(), you need an int for the third parameter.  What
  you have, in your C code, will best dictate what bind function to
  use.  SQLite will then do any conversions required, given the column
  affinity, and it is generally going to do a better and smarter job
  than your code can because it has more information about what is
  going on.

> >  http://sqlite.org/datatype3.html
> >  
> >   Read this whole page.  Several times.  Make sure you understand it in
> >   great detail.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the meta type of a column

[Mark Hessling]

I think the OP wanted to know how to find this programatically.

In that case see sqlite3_column_type() function call.

Cheers, Mark

On Fri, 2010-02-05 at 02:47 +, Simon Slavin wrote:
> On 5 Feb 2010, at 2:41am, gujx wrote:
> 
> > Maybe I didn't express the problem clearly.
> >> e.g. if you have a text value, you use _text(), if you have an int, you
> >> use _int()
> > I just don't know what type I have, so I want to get the meta type of the
> > column somebody defined.
> > e.g. I'd like to use a bind routine to bind a variable t to a "?", but I
> > don't konw this t is a string or a number or it is a object, so I want to
> > know that the table is defined like "id(varchar)" ,then I will use
> > sqlite3_bind_text; or it is defined like "id(integer)", then I will choose
> > sqlite3_bind_int.
> 
> Open the database in the command-line tool, and use the .schema command:
> 
> 
> 
> Simon.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 

* Mark Hessling, m...@rexx.org http://www.rexx.org/ 
* Author of THE, a Free XEDIT/KEDIT editor, Rexx/SQL, Rexx/CURL,  etc. 
* Maintainer of Regina Rexx interpreter and Rexx/Tk 
* Use Rexx? join the Rexx Language Association: http://www.rexxla.org/ 

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

Re: [sqlite] how to get the meta type of a column

[Simon Slavin]

On 5 Feb 2010, at 2:41am, gujx wrote:

> Maybe I didn't express the problem clearly.
>> e.g. if you have a text value, you use _text(), if you have an int, you
>> use _int()
> I just don't know what type I have, so I want to get the meta type of the
> column somebody defined.
> e.g. I'd like to use a bind routine to bind a variable t to a "?", but I
> don't konw this t is a string or a number or it is a object, so I want to
> know that the table is defined like "id(varchar)" ,then I will use
> sqlite3_bind_text; or it is defined like "id(integer)", then I will choose
> sqlite3_bind_int.

Open the database in the command-line tool, and use the .schema command:



Simon.


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

Re: [sqlite] how to get the meta type of a column

[gujx]

Maybe I didn't express the problem clearly.
> e.g. if you have a text value, you use _text(), if you have an int, you
> use _int()
I just don't know what type I have, so I want to get the meta type of the
column somebody defined.
e.g. I'd like to use a bind routine to bind a variable t to a "?", but I
don't konw this t is a string or a number or it is a object, so I want to
know that the table is defined like "id(varchar)" ,then I will use
sqlite3_bind_text; or it is defined like "id(integer)", then I will choose
sqlite3_bind_int.


Jay A. Kreibich-2 wrote:
> 
> On Thu, Feb 04, 2010 at 03:59:14PM +0800, gujx scratched on the wall:
>> There is a table like this:
> 
> 
>> Is there any way to resolve this?
> 
>   First off, you need to understand that columns do not have "types",
>   they have affinities.
> 
>  http://sqlite.org/datatype3.html
>  
>   Read this whole page.  Several times.  Make sure you understand it in
>   great detail.
> 
> 
> 
>   Second, you can put any type into (almost) any column, so it doesn't
>   matter.  If the affinity is setup right, SQLite will usually do the
>   right thing.  For example, if you have a column with numeric affinity
>   and you insert the text value '12', it will converted to an integer
>   before it is stored.
> 
>   Which bind function you use is usually dictated by what you have
>   (e.g. if you have a text value, you use _text(), if you have an int,
>   you use _int()), rather than the column you are placing it in. 
>   SQLite will try its best to convert what you've given it into a
>   format that makes sense for the column, but if it can't, it will store
>   it in whatever format you sent.  Which is more or less exactly the
>   position you'd be in if you wanted to do the conversion "by-hand."
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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/how-to-get-the-meta-type-of-a-column-tp27451326p27462879.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 question

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

lizhe wrote:
> I am writing to enquire about a bug we found. 

http://www.beiww.com/doc/oss/smart-questions.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAktrfUMACgkQmOOfHg372QRpngCeMF5O+xBQcA2FyMItjIrC3T+W
QQcAn2ArgAeksiAn0rwD4CY5+k7Bdj06
=mEdA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 question

[lizhe]

您好:
我们使用的SQLITE3 
数据库版本为3.6版本,在红帽编译器下运行,运行的硬件系统为LINUX(2.6版本),现在出现了一个问题,对有些板子,发现如果连续插入数据库会造成LINUX系统的崩溃,有的时候会造成FLASH的丢失,使用调试口测试,发现是堆栈益出,但是在有些板子上却很正常,可以连续插入几万条记录,请问这是什么原因造成的.


Dear Sir:
 
I am writing to enquire about a bug we found. Now the SQLITE3 database we 
use is version 3.6, which running in the red hat compiler and the hardware 
system is Linux(version 2.6).We have a problem that for some broads, if 
inserting the records into database continuously will make the Linux system 
breakdown, and sometimes lose Flash too. Using debugcom to test, it comes out 
"stack overflow ". But for some broads are not, we can insert tens of thousands 
of records into it. Do you have any idea about this? Wish you happiness !


Yours sincerely,

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

[sqlite] Change in index optimizer bug with FTS3 between 3.6.21 and 3.6.22?

[Nasron Cheong]

Not sure if this is intentional, but it looks like the wrong index is being
selected on fts tables when a rowid is involved.

Given a table:

CREATE VIRTUAL TABLE MessagesFts USING fts3(Message);

Explain query plan using sqlite 3.6.21:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM MessagesFts WHERE docid=1 AND
Message MATCH 'ABC*';
0|0|TABLE MessagesFts VIRTUAL TABLE INDEX 1:

However doing the same in 3.6.22:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM MessagesFts WHERE docid=1 AND
Message MATCH 'ABC*';
0|0|TABLE MessagesFts VIRTUAL TABLE INDEX 2:

Note that different indexes are now picked.

This seems to mesh with behaviour I've found...

Using 3.6.22, when MessagesFts contains a lot of entries that would match
ABC*, the query takes exceedingly long time to run, since it seems to be
doing the fts before narrowing down by docid. I imagine that it's supposed
to narrow down by docid first.

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

Re: [sqlite] When incremental write is committed to the hard drive?

[a1rex]

Pavel,
 
Thank you so much for your help. Your knowledge is worth
more than gold. 
You were absolutely right regarding not closed blob handle!
(for prepare I use only sqlite3_prepare_v2)

I found out that I had one blob handle opened in the unrelated table in the 
same database.
This handle was never used for read or write yet it was holding up
all the database incremental writes in the other tables.
 
That was preventing all incremental writes to be committed to the hard drive!
This was also locking the whole database preventing any updates by external 
programs.
Very interesting…
 
Thank you very much again,
Best regards,
Samuel  

- Original Message 
From: Pavel Ivanov 
To: General Discussion of SQLite Database 
Sent: Thu, February 4, 2010 3:54:33 PM
Subject: Re: [sqlite] When incremental write is committed to the hard drive?

> commit = 1;
> pStmt  != NULL
> but
> sql = NULL;   //under debugger: pStmt->isPrepareV2 = 0; pStmt->zSql = NULL;

So, I was right then that in case of not finished SELECT statement
autocommit will still be 1. But as you correctly noticed above
isPrepareV2 = 0 and it means that this statement wasn't prepared with
sqlite3_prepare_v2 and sql text is never stored in this case. Do you
prepare all your statements with sqlite3_prepare_v2? If yes then
probably it's statement created by one of sqlite3_blob_open calls
which wasn't matched with sqlite3_blob_close then. Check carefully
that you close all blob handles.



  __
Be smarter than spam. See how smart SpamGuard is at giving junk email the boot 
with the All-new Yahoo! Mail.  Click on Options in Mail and switch to New Mail 
today or register for free at http://mail.yahoo.ca
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS3 module locking behaviour

[Nasron Cheong]

I'm using FTS3 in a single table configuration:

CREATE VIRTUAL TABLE MessagesFts USING fts3(Message);

I have a writer thread that is writing messages to this table, as well as
several other tables, in batched transactions.

There are reader threads that may execute fts queries against the
MessagesFts table.

I'm finding that when we are doing a long FTS query (such as a MATCH using
prefixes that will match many entries, e.g SELECT from MessagesFts WHERE
Message MATCH 'MyToken*' ), the writer thread is blocked. This is expected,
since we are doing a read.

However, I find other reader threads are also blocked! These include reader
threads that are not accessing the fts table at all, but other unrelated
tables!

Can't seem to find any documentation relating FTS locking behaviour.

Any suggestions?

Thanks.

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

Re: [sqlite] When incremental write is committed to the hard drive?

[Pavel Ivanov]

> commit = 1;
> pStmt  != NULL
> but
> sql = NULL;   //under debugger: pStmt->isPrepareV2 = 0; pStmt->zSql = NULL;

So, I was right then that in case of not finished SELECT statement
autocommit will still be 1. But as you correctly noticed above
isPrepareV2 = 0 and it means that this statement wasn't prepared with
sqlite3_prepare_v2 and sql text is never stored in this case. Do you
prepare all your statements with sqlite3_prepare_v2? If yes then
probably it's statement created by one of sqlite3_blob_open calls
which wasn't matched with sqlite3_blob_close then. Check carefully
that you close all blob handles.


Pavel

On Thu, Feb 4, 2010 at 3:36 PM, a1rex  wrote:
>
> Thank you very much for your advice!
>
>>to check that transaction
>>wasn't committed yet you can connect to the database with external
>>command while application is working and try to update or insert
>>something. If it fails with message "The database file is locked" then
>>application didn't commit transaction
>
> Yes, you are right. Update fails with message "The database file is locked".
>
> I inserted following statements:
>
> const char *sql;
> int commit = sqlite3_get_autocommit(db);
>
> sqlite3_stmt *pStmt = sqlite3_next_stmt(db, NULL);
>
> if(pStmt != NULL)
> {
>      sql = sqlite3_sql(pStmt);
> }
>
> right after :
>
> sqlite3_blob_close();
>
> And received:
> commit = 1;
> pStmt  != NULL
> but
> sql = NULL;   //under debugger: pStmt->isPrepareV2 = 0; pStmt->zSql = NULL;
>
> The results surprised me. I think that I do not have any
> statement open yet sqlite3_next_stmt tells me that I have statement prepared
> and pending. At the same time sqlite3_sql(pStmt) says that that statement is 
> a NULL statement.
>
> I just got more confused.
>
> Best regards,
> Samuel
>
>
> - Original Message 
> From: Pavel Ivanov 
> To: General Discussion of SQLite Database 
> Sent: Thu, February 4, 2010 2:07:12 PM
> Subject: Re: [sqlite] When incremental write is committed to the hard drive?
>
>> 1) What else can prevent incremental data to be written to the hard drive?
>
> Besides all that I mentioned only explicit BEGIN statement can open
> transaction and thus prevent anything after that from being written to
> disk immediately until COMMIT is executed. What you can do now is
> first of all use sqlite3_get_autocommit function
> (http://www.sqlite.org/c3ref/get_autocommit.html) after closing blob
> handle to check that transaction should be automatically committed.
> But I'm not sure that it will return 0 if some SELECT statement is in
> progress. To check that you can call sqlite3_next_stmt(db, NULL)
> (http://www.sqlite.org/c3ref/next_stmt.html) to obtain pointer to the
> statement that is still open (if you finalize all your statements then
> this function should return NULL). If function returns some statement
> you can use sqlite3_sql (http://www.sqlite.org/c3ref/sql.html) to see
> what statement is at fault.
>
>> 2) Is there a way to force a write to the hard drive?
>
> Nothing but COMMIT statement (or auto-commit) can force new and
> changed data to be written on disk. BTW, to check that transaction
> wasn't committed yet you can connect to the database with external
> command while application is working and try to update or insert
> something. If it fails with message "The database file is locked" then
> application didn't commit transaction. If update succeeds and you
> still cannot see changes made by application then you have some
> problems with file system, but I hope you have not.
>
>
>      __
> Looking for the perfect gift? Give the gift of Flickr!
>
> http://www.flickr.com/gift/
> ___
> 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] When incremental write is committed to the hard drive?

[a1rex]

Thank you very much for your advice!  
 
>to check that transaction
>wasn't committed yet you can connect to the database with external
>command while application is working and try to update or insert
>something. If it fails with message "The database file is locked" then
>application didn't commit transaction
 
Yes, you are right. Update fails with message "The database file is locked".
 
I inserted following statements:

const char *sql;
int commit = sqlite3_get_autocommit(db);
 
sqlite3_stmt *pStmt = sqlite3_next_stmt(db, NULL);
 
if(pStmt != NULL)
{
  sql = sqlite3_sql(pStmt);
}

right after :

sqlite3_blob_close();
 
And received:
commit = 1;
pStmt  != NULL
but 
sql = NULL;   //under debugger: pStmt->isPrepareV2 = 0; pStmt->zSql = NULL;
 
The results surprised me. I think that I do not have any
statement open yet sqlite3_next_stmt tells me that I have statement prepared
and pending. At the same time sqlite3_sql(pStmt) says that that statement is a 
NULL statement.
 
I just got more confused.
 
Best regards,
Samuel   


- Original Message 
From: Pavel Ivanov 
To: General Discussion of SQLite Database 
Sent: Thu, February 4, 2010 2:07:12 PM
Subject: Re: [sqlite] When incremental write is committed to the hard drive?

> 1) What else can prevent incremental data to be written to the hard drive?

Besides all that I mentioned only explicit BEGIN statement can open
transaction and thus prevent anything after that from being written to
disk immediately until COMMIT is executed. What you can do now is
first of all use sqlite3_get_autocommit function
(http://www.sqlite.org/c3ref/get_autocommit.html) after closing blob
handle to check that transaction should be automatically committed.
But I'm not sure that it will return 0 if some SELECT statement is in
progress. To check that you can call sqlite3_next_stmt(db, NULL)
(http://www.sqlite.org/c3ref/next_stmt.html) to obtain pointer to the
statement that is still open (if you finalize all your statements then
this function should return NULL). If function returns some statement
you can use sqlite3_sql (http://www.sqlite.org/c3ref/sql.html) to see
what statement is at fault.

> 2) Is there a way to force a write to the hard drive?

Nothing but COMMIT statement (or auto-commit) can force new and
changed data to be written on disk. BTW, to check that transaction
wasn't committed yet you can connect to the database with external
command while application is working and try to update or insert
something. If it fails with message "The database file is locked" then
application didn't commit transaction. If update succeeds and you
still cannot see changes made by application then you have some
problems with file system, but I hope you have not.


  __
Looking for the perfect gift? Give the gift of Flickr! 

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

Re: [sqlite] When incremental write is committed to the hard drive?

[Pavel Ivanov]

> 1) What else can prevent incremental data to be written to the hard drive?

Besides all that I mentioned only explicit BEGIN statement can open
transaction and thus prevent anything after that from being written to
disk immediately until COMMIT is executed. What you can do now is
first of all use sqlite3_get_autocommit function
(http://www.sqlite.org/c3ref/get_autocommit.html) after closing blob
handle to check that transaction should be automatically committed.
But I'm not sure that it will return 0 if some SELECT statement is in
progress. To check that you can call sqlite3_next_stmt(db, NULL)
(http://www.sqlite.org/c3ref/next_stmt.html) to obtain pointer to the
statement that is still open (if you finalize all your statements then
this function should return NULL). If function returns some statement
you can use sqlite3_sql (http://www.sqlite.org/c3ref/sql.html) to see
what statement is at fault.

> 2) Is there a way to force a write to the hard drive?

Nothing but COMMIT statement (or auto-commit) can force new and
changed data to be written on disk. BTW, to check that transaction
wasn't committed yet you can connect to the database with external
command while application is working and try to update or insert
something. If it fails with message "The database file is locked" then
application didn't commit transaction. If update succeeds and you
still cannot see changes made by application then you have some
problems with file system, but I hope you have not.


Pavel

On Thu, Feb 4, 2010 at 1:49 PM, a1rex  wrote:
> Pavel,
> Thank you very much for your email. I greatly appreciate
> your knowledge on the internal workings of Sqlite and your kindness to share 
> it.
>
>>All incremental writing is committed (and thus is written to disk)
>>when blob handle is closed. And even when you close the handle
>>transaction is committed only when there's no more blob handles or
>>SELECT statements open at the moment on the same connection.
>
> I would never guess that SELECT dependency, never!
>
> I checked my code. But as far as I can tell I have all SELECT statements are 
> finalized
> by sqlite3_finalize() and not held by sqlite3_reset(). I have only one blob 
> handle and I am opening and
> closing it when I SELECT another record from the table.
>
> Incremental changes are done for sure, I can come back to the
> modified record read it within program and new data is in. But when an 
> external
> program reads the same database it does not see the changes till my program 
> exits.
>
> 1) What else can prevent incremental data to be written to the hard drive?
>
> 2) Is there a way to force a write to the hard drive?
>
> Thank you for reading. Any comment greatly appreciated!
>
> Regards,
> Samuel
>
>
>      __
> Be smarter than spam. See how smart SpamGuard is at giving junk email the 
> boot with the All-new Yahoo! Mail.  Click on Options in Mail and switch to 
> New Mail today or register for free at http://mail.yahoo.ca
> ___
> 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] When incremental write is committed to the hard drive?

[a1rex]

Pavel,
Thank you very much for your email. I greatly appreciate
your knowledge on the internal workings of Sqlite and your kindness to share 
it. 

>All incremental writing is committed (and thus is written to disk)
>when blob handle is closed. And even when you close the handle
>transaction is committed only when there's no more blob handles or
>SELECT statements open at the moment on the same connection.
 
I would never guess that SELECT dependency, never!
 
I checked my code. But as far as I can tell I have all SELECT statements are 
finalized 
by sqlite3_finalize() and not held by sqlite3_reset(). I have only one blob 
handle and I am opening and
closing it when I SELECT another record from the table. 
 
Incremental changes are done for sure, I can come back to the
modified record read it within program and new data is in. But when an external
program reads the same database it does not see the changes till my program 
exits. 
 
1) What else can prevent incremental data to be written to the hard drive?

2) Is there a way to force a write to the hard drive?
 
Thank you for reading. Any comment greatly appreciated!

Regards,
Samuel


  __
Be smarter than spam. See how smart SpamGuard is at giving junk email the boot 
with the All-new Yahoo! Mail.  Click on Options in Mail and switch to New Mail 
today or register for free at http://mail.yahoo.ca
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When incremental write is committed to the hard drive?

[Pavel Ivanov]

All incremental writing is committed (and thus is written to disk)
when blob handle is closed. And even when you close the handle
transaction is committed only when there's no more blob handles or
SELECT statements open at the moment on the same connection.

Pavel

On Wed, Feb 3, 2010 at 7:41 PM, a1rex  wrote:
>
> I use UPDATE for text columns in the table and any changes are committed to 
> the hard drive right away (well,
> after about 120 ms).
>
> This can be verified by using external tool.  I use SQLite Manager to 'see' 
> the changes.
>
> I use also sqlite3_blob_write(ppBlob,dataToWrite,dataCount,offsetInTheBlob);
> for the BLOB columns.
>
> The changes to the blob are registered “somewhere” and
> subsequent SELECT statement is aware of them but I do not see any physical
> changes to the database data on the hard drive.
> Also looking at the time stamps I do not see that ‘sqlite3_blob_write’ does 
> any physical i/o!
>
> Closing the blob also seems to do nothing.
>
> Nevertheless, the data seems to be written (flashed?) to the hard drive when 
> data base is closed.
>
> I wonder if ACID
> properties of the data base are assured for incremental write and if yes when 
> physical
> write really happens?
>
> Thank you for reading. Any comment greatly appreciated!
> Regards,
> Samuel
>
>
>      __
> Looking for the perfect gift? Give the gift of Flickr!
>
> http://www.flickr.com/gift/
> ___
> 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] how to get the meta type of a column

[Jay A. Kreibich]

On Thu, Feb 04, 2010 at 03:59:14PM +0800, gujx scratched on the wall:
> There is a table like this:


> Is there any way to resolve this?

  First off, you need to understand that columns do not have "types",
  they have affinities.

 http://sqlite.org/datatype3.html
 
  Read this whole page.  Several times.  Make sure you understand it in
  great detail.



  Second, you can put any type into (almost) any column, so it doesn't
  matter.  If the affinity is setup right, SQLite will usually do the
  right thing.  For example, if you have a column with numeric affinity
  and you insert the text value '12', it will converted to an integer
  before it is stored.

  Which bind function you use is usually dictated by what you have
  (e.g. if you have a text value, you use _text(), if you have an int,
  you use _int()), rather than the column you are placing it in. 
  SQLite will try its best to convert what you've given it into a
  format that makes sense for the column, but if it can't, it will store
  it in whatever format you sent.  Which is more or less exactly the
  position you'd be in if you wanted to do the conversion "by-hand."

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to get the meta type of a column

[gujx]

There is a table like this:


id(varchar)

name(text)

age(integer)

ss(text)

Its name is “test_for_cpp”, now I get the pointer of sqlite successfully. 

 

There are codes below:

 

sqlite3_stmt *ppStmt;

const char *pzTail; 

int nVal = sqlite3_prepare_v2(

   conn,   

   "INSERT INTO [test_for_cpp] ([id], [name], [age], [ss]) VALUES (?, ?,
?, ’exa’) ; ", 

   -1,

   ,  

   

);

 

now, I should use routines such as
 sqlite3_bind_text to bind data
with the “?”, but I don’t know the exact meta type of the column. 

I don’t know that the meta type of the column id is “integer”, and I
don’t know the meta type of the column name is “text”.…… So, I can’t
choose the exact bind routine.

Is there any way to resolve this?

 

 

 

Gu Jinxiang

 

以上、よろしくお��いします。

 

-- 

 

 

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