Re: [sqlite] API not responding correctly for vc++

[a1rex]

I hope that you know that 
sprintf(sql, "SELECT count(*) FROM table1;");

is not a going to give you  count(*)  FROM table1.

Where is your real query?



From: Prajeed chathuar 
To: sqlite-users@sqlite.org
Sent: Thu, May 13, 2010 4:10:01 AM
Subject: [sqlite] API not responding correctly for vc++

Hi
I am a sotware trainee of Bangalore,India base company...i am working for
the first time in sqlite data baase..
I am trying sqlite and vc++ for last one week iam not getting correct
output.there is no linking and compiling error but not getting correct
output: here is the code


int  sqlresult = 1;
char sql[1024]="";
int rc;

sprintf(sql, "SELECT count(*) FROM table1;");

rc = sqlite3_open("sqlite3pp.db", );
if ( rc )
   {
MessageBox("Unable to open database for record count","Database
Result",MB_OK | MB_ICONERROR);
   }


In the above code my program is always returning 0X000(some hexadecimal
value)from sqlite3_open() . The data base is in the folder where the
solution file is residing..i have included the sqlite3.lib file also
which was extracted from sqlite3.def.

Can any help me for the above problem

Thanks
Prajeed.c
Software trainee
Dwise solutions,Bangalore,India


___
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] Behaviour of tables with same name

[a1rex]

Since we are at this topic let me ask the question: Are the table names case 
insensitive?



From: Andy Gibbs 
To: sqlite-users@sqlite.org
Sent: Fri, May 7, 2010 8:00:44 AM
Subject: Re: [sqlite] Behaviour of tables with same name

>  I think the bigger issue is that you probably shouldn't rely on
>  automatic resolution of names.  ...  If you're using multiple
> databases-- even just temp and main-- the best solution is to
> just qualify as much as you can.

This is sound advice.

Thank you, also, for the clarification of the search order.

Andy

___
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] Question about binding

[a1rex]

* :VVV 
* @VVV 
* $VVV 
Are above bindings the same? (Just different prefix to VVV)?  
Thank you,
Samuel

From: Pavel Ivanov 
To: General Discussion of SQLite Database 
Sent: Wed, March 24, 2010 2:16:34 PM
Subject: Re: [sqlite] Question about binding

> Is there documentation that talks about about the various binding place
> holders or is this a standard SQL construct?

Probably this will help you: http://www.sqlite.org/c3ref/bind_blob.html.


Pavel

On Tue, Mar 23, 2010 at 3:48 PM, Vance E. Neff  wrote:
> Thanks to all those who responded!  It was quite educational.
> I'm using the zentus java jdbc wrapper.  It seems to only support an
> index # for the binding index so I'm stuck with being careful as to how
> I count ?s.
> Is there documentation that talks about about the various binding place
> holders or is this a standard SQL construct?
>
> Vance
>
> D. Richard Hipp wrote:
>> On Mar 19, 2010, at 3:29 PM, David Bicking wrote:
>>
>>>
>>> --- On Fri, 3/19/10, Vance E. Neff  wrote:
>>>
>>> 
 UPDATE table1 set (?, ?, ?) WHERE col1=? and col2=?;

 I've never used binding before but have known it is a good
 idea in order
 to avoid injection of bad stuff.

 Vance

>>> You count the question marks from left to right.
>>>
 UPDATE table1 set (<1>, <2>, <3>) WHERE col1=<4> and col2=<5>;
>>> You can also put the index number you want to use after the ? so
>>> they can be in any order you want.
>>
>> Better still is to use a symbolic name for the parameters.  The
>> symbolic names can be any identifier that begins with $, :, or @.
>> Examples:
>>
>> UPDATE table1 SET col1=$c1val, co...@c2val, col3=:c3val
>>   WHERE co...@c2val AND col3=:c3val;
>>
>> You still have to translate the symbolic name into a "parameter index"
>> before you bind it.  The sqlite3_bind_parameter_index() routine will
>> do that for you.
>>
>> In the programs I write, I always try to use symbolic names for
>> parameters and I rig the infrastructure to handle the mapping from
>> symbolic name to parameter index.  For example, if you are using the
>> TCL interface to SQLite, you just specify TCL variables embedded in
>> the SQL:
>>
>>   db eval {UPDATE table1 SET col1=$c1val WHERE col2=$c2val}
>>
>> In the statement above, the TCL interface automatically looks up the
>> values of TCL variables $c1val and $c2val and binds them appropriately
>> before running the statement.  It doesn't get any cleaner than this.
>> Unfortunately, other programming languages require more complex
>> syntax.  In the implementation of "Fossil" I do this:
>>
>>   db_prepare(, "UPDATE table1 SET col1=$c1val WHERE col2=
>> $c2val");
>>   db_bind_int(, "$c1val", 123);
>>   db_bind_double(, "$c2val, 456.78);
>>   db_step();
>>   db_finalize();
>>
>> The db_bind_int() and db_bind_double() and similar routines wrap the
>> sqlite3_bind_x() and sqlite3_bind_parameter_index() calls.
>>
>> If we've learned one thing over the history of computing it is that
>> programmers are notoriously bad at counting parameters and that
>> symbolic names tend to be much better at avoiding bugs.
>>
>> D. Richard Hipp
>> d...@hwaci.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  __
Connect with friends from any web browser - no download required. Try the new 
Yahoo! Canada Messenger for the Web BETA at 
http://ca.messenger.yahoo.com/webmessengerpromo.php
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why SQLITE_BUSY when read the database

[a1rex]

>Do The words mean that while one process is writing the database other

>processes could not read the database at the same time?

The reader fails since it cannot obtain SHARED lock required for 
reading.
It cannot obtain that lock since the writer already 
managed to obtain PENDING or EXCLUSIVE lock.



  __
The new Internet Explorer® 8 - Faster, safer, easier.  Optimized for Yahoo!  
Get it Now for Free! at http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why SQLITE_BUSY when read the database

[a1rex]

>Do The words mean that while one process is writing the database other
>processes could not read the database at the same time?

In short:: YES


  __
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] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?

[a1rex]

>- Original Message 
>From: P Kishor 

>To the veterans on the list, it is very clear that no one had a "how
>stupid are you" attitude

I totally agree, and I am new here. 
I prefer to get any answer - since I will learn from it - than no answer at 
all. 

>Even if a particular reply might seem brusque, in reality itis only a
>gentle, non-malicious attempt to veer the OP into the right direction.
>In all likelihood, in fact, from all evidence from the postings, all
>the folks responding are probably some of the nicest people we
>SQL-wise lesser mortals will have the privilege of learning from.

 I whole heartily agree. 

Reagrds,
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] Problem with SQLite in BCB 4

[a1rex]

Thank you very much for your clarification! This is what I suspected.

Regards,
Samuel


- Original Message 
From: Igor Tandetnik <itandet...@mvps.org>

a1rex wrote:
> What about void *p  =  sqlite3_column_blob()?
> From my tests it looks that pointer p survives sqlite3_finalize().
> Is it just a coincidence?

It "survives" in the same sense as in this example:

char* p = (char*)malloc(10);
strcpy(p, "Hello");
free(p);
printf(p);

Chances are high the last line will print "Hello", simply because the now-free 
memory referred to by now-dangling pointer p didn't have the chance to be 
overwritten with something else yet



  __
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] Problem with SQLite in BCB 4

[a1rex]

- Original Message 
>From: Simon Davies 

>The return from sqlite3_column_text is not valid after subsequent calls to any 
>of
>sqlite3_step/sqlite3_reset/sqlite3_finalize.
 
Yes.
 
What about void *p  =  sqlite3_column_blob()? 
>From my tests it looks that pointer p survives sqlite3_finalize().
Is it just a coincidence?
 
On the other hand I experienced invalidations of blob handles obtained by 
sqlite3_blob_open() after
updating records in an UNRELATED  table.
I could not find description of this behavior in the SQLite documentation. It 
may be nature of the beats,  a bug or a "feature".
Unfortunately this behavior may keep me away from using incremental read/write. 
   
 
Regards,
Samuel 


  __
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
Optimized for Yahoo! Get it Now for Free! at 
http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SQLite in BCB 4

[a1rex]

>> 2010/3/6 Chimerian :
>> values.push_back((char*)sqlite3_column_text(statement,
col));
>> // HERE IS ERROR !


>From: Simon Davies

>What error?
>I can not see why THIS line should provoke any error;
 
This line will
produce a runtime error when sqlite3_column_text(statement, col); returns NULL
pointer (and NULL value is a valid value for any affinity if not restricted by 
NO NULL clause)  
 
>   but be aware that you are
creating a vector of INVALID pointers. 
 
No. values.push_back
is not  creating vector of invalid pointers.
It is creating vector of strings.  The
problem is no valid string can be created from NULL pointer. This function will
fail trying to obtain the length of the string from NULL pointer. 
 
>The return
from sqlite3_column_text is not valid after subsequent calls to any of
>sqlite3_step/sqlite3_reset/sqlite3_finalize.
 
VERY TRUE! It is
easy to forget about it!

>You need to make a copy of the string and store that, not the pointer
>returned from sqlite3_column_text.
 
True. But this
is done automatically by values.push_back()
The vector, like
all other Standard Library containers, stores copies - these copies are made
with the copy constructor. 
 
Regards,
Samuel



  __
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
Optimized for Yahoo! Get it Now for Free! at 
http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SQLite in BCB 4

[a1rex]

 
Chimerian,
I compiled and tested the tutorial example (which you
pointed out) with VC++ on XP Pro. Example is fine, except with the problem of
not checking for NULL pointer, which I fixed for you. 
DB is created and values are stored and retrieved.  No problem with SQLite or 
STL. 
 
I do not have Borland C++ Builder  6, but I assume it has debugger and you can 
put a break point to
see where char *ptr points.
You can also instead of using sqlite3.dll compile sqlite3.c
source code and step through the qlite3_column_text(statement, col) function
to see how it goes.   
 
I strongly suggest to test this example “AS IS” without any
Borland GUI interference. Just pure C++ and STL. (There is always a remote
possibility for memory overrun or bad linking job). 
So try the example alone without the pollution first.
Good luck. 


- Original Message 
>From: Chimerian 
>To: sqlite-users@sqlite.org
>Sent: Mon, March 8, 2010 12:18:53 PM
>Subject: Re: [sqlite] Problem with SQLite in BCB 4

>Unfortunately I cant print the value of val before it's pushed to the
>values vector because when do that I got this same error - I think
>the problem is with pointer:
>char * ptr = (char*)sqlite3_column_text(statement, col);
>Maybe it hold wrong address ?


  __
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] Problem with SQLite in BCB 4

[a1rex]

Well, sorry to hear that.


I tested the function:


vector  CSGDb::query(char* query, sqlite3 *database)
{
sqlite3_stmt *statement;
vector results;

if(sqlite3_prepare_v2(database, query, -1, , 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{
std::string  val;
char * ptr = 
(char*)sqlite3_column_text(statement, col);
if(ptr)
{
val = ptr;
}
else val = "";

values.push_back(val); 
}
results.push_back(values);
}
else
{
break;   
}
}

sqlite3_finalize(statement);
}
  
return results;
}

  
and it works for me fine.

Could you print the value of val before it is pushed to the values vector?  Of 
course the problem is not Sqlite problem but the proper usage of STL.


- Original Message 
From: Chimerian 
To: sqlite-users@sqlite.org
Sent: Sun, March 7, 2010 4:23:30 PM
Subject: Re: [sqlite] Problem with SQLite in BCB 4

Unfortunately it still doesn't work. I have error in line values.push_back(val);
I tried to run program on Windows XP - I have this same error.

Links to error screens:
http://chimerian.net/error1.jpg
http://chimerian.net/error2.jpg


  __
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] Crash after add column

[a1rex]

- Original Message 

>From: VasiliyF4 
>After I try to ADD a column by run the querry "ALTER TABLE x
>ADD y NUMERIC" from my application, I can't use the data base any more. If I
>try to get or save any data at my DB it cause crash of the application.

Of course your old INSERT statements will not work anymore, since INSERT 
statement requires knowledge of all columns in the table.


  __
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] Crash after add column

[a1rex]

>I try to ADD a column by run the querry "ALTER TABLE x
>ADD y NUMERIC" from my application, I can't use the data base any more.

Try this: "ALTER TABLE main.x ADD y NUMERIC" and verify by external tool that 
column x has been added.


  __
Connect with friends from any web browser - no download required. Try the new 
Yahoo! Canada Messenger for the Web BETA at 
http://ca.messenger.yahoo.com/webmessengerpromo.php
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SQLite in BCB 4

[a1rex]

You cannot push_back(NULL) null pointer. 
Fix is below:

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{
std::string  val;
char * ptr = 
(char*)sqlite3_column_text(statement, col);
if(ptr)
{
val = ptr;
}
else val = "";

values.push_back(val);   // JUZ NIE 
WYWALA
}
results.push_back(values);
}
else
{
break;   
}

Enjoy,
Samuel

BTW nice usage of STL + SQlite.



- Original Message 
From: Chimerian 
To: sqlite-users@sqlite.org
Sent: Sat, March 6, 2010 12:05:51 PM
Subject: [sqlite] Problem with SQLite in BCB 4

I have a problem with SQLite in BCB 4.0. I wrote my own application -
I found very useful information on 
http://www.dreamincode.net/forums/index.php?showtopic=122300

On form (Form1) I have: Memo1 and Button3.

File Unit1.cpp:


//---
#include 
#pragma hdrstop
#include 
#include "Unit1.h"
//---
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;

// DLL handle
HANDLE DLLHandle = LoadLibrary("sqlite3.dll");

// SQLite class
Database::Database(char* filename)
{
sqlite3_open = (int (__cdecl *)(const char *,
sqlite3**))GetProcAddress(DLLHandle,"sqlite3_open");

sqlite3_close = (int (__cdecl 
*)(sqlite3*))GetProcAddress(DLLHandle,"sqlite3_close");

sqlite3_prepare_v2 = (int (__cdecl *)(sqlite3*, const char*, int, 
sqlite3_stmt**, const
char**))GetProcAddress(DLLHandle,"sqlite3_prepare_v2");

sqlite3_column_count = (int (__cdecl
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_column_count");

sqlite3_step = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_step");

sqlite3_column_text = (const unsigned char (__cdecl *)(sqlite3_stmt*, 
int
iCol))GetProcAddress(DLLHandle,"sqlite3_column_text");

sqlite3_finalize = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_finalize");

database = NULL;
open(filename);
}

Database::~Database()
{
}

bool Database::open(char* filename)
{
if(sqlite3_open(filename, ) == SQLITE_OK)
{
Form1->Memo1->Lines->Add("You have access to base.");
return true;
}

return false;  
}

vector Database::query(char* query)
{
sqlite3_stmt *statement;
vector results;

if(sqlite3_prepare_v2(database, query, -1, , 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{

values.push_back((char*)sqlite3_column_text(statement, col));  
// HERE IS ERROR !
}
results.push_back(values);
}
else
{
break;  
}
}

sqlite3_finalize(statement);
}
  
return results;
}

void Database::close()
{
sqlite3_close(database);
}

//---
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{

}
//---

void __fastcall TForm1::Button3Click(TObject *Sender)
{
Database *db;
db = new Database("Database.sqlite");
db->query("CREATE TABLE a (a INTEGER, b INTEGER);");
db->query("INSERT INTO a VALUES(1, 2);");
db->query("INSERT INTO a VALUES(5, 4);");
vector result = db->query("SELECT a, b FROM a;");
for(vector::iterator it = result.begin(); it < result.end(); 
++it)
{
vector row = *it;
cout << "Values: (A=" << row.at(0) << ", B=" << row.at(1) << ")" << 
endl;
}
db->close();
}

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[a1rex]

Simon,
 
I am very surprise that your SQLite operation can take so
long. Is it a very complicated search? Multiple writes? 
 
>I have a text field that launches a full text search query at every key press.
 
Can you rearrange your algorithm? How many records do you
search? Do you have to search on every key press?  Do you use proper index keys?
 
If this takes so long no wonder that you are trying
desperate measures like aborting via qlite3_interrupt. But qlite3_interrupt may 
not be your savior. 
 
I had similar problem when I was trying to write every
keyboard stroke to the hard drive. It was painfully slow! I was forced to
rethink my approach, buffer characters and save them on the slow timer tick.
 
>Do you think there would be any proper places to add
CHECK_FOR_INTERRUPT >calls to the FTS3 code to improve the issue?
 
Sorry! I do not know. My knowledge of FTS3 code is very
limited.
 
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

Re: [sqlite] Interrupt first sqlite3_step in FTS3 query

[a1rex]

 
- Original Message 
From: Simon dbern...@noos.fr
 
>However, it seems that some process (that can take
>several tens of seconds) in the first >sqlite3_step does not test for
>interrupt (resulting in simultaneous uninterrupted 
>concurrent threads...)
 
According to http://www.sqlite.org/c3ref/interrupt.html
process but will not be aborted if it is quite advanced: 

“If an SQL operation is very nearly finished at the time
when sqlite3_interrupt() is called, then it might not have an opportunity to be
interrupted and might continue to completion.”
 
Also:
”A call to sqlite3_interrupt(D) that occurs when there are no running SQL
statements is a no-op and has no effect on SQL statements that are started
after the sqlite3_interrupt() call returns.”
 
The safest solution is to have only ONE threat to deal with
the database.
 
Let me quote famous words: “Threads are evil. Avoid them.” 
http://www.sqlite.org/faq.html#q6
 
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

Re: [sqlite] One data base versus two smaller ones

[a1rex]

Thank you for putting me on the right track!  Now I know how to attack the 
issue.

Also this pragmas may help me:
PRAGMA default_cache_size = Number-of-pages;
PRAGMA cache_size = Number-of-pages;
PRAGMA page_size = bytes;
PRAGMA max_page_count = N;
 
Regards,
Samuel

- Original Message 
From: Israel Lins Albuquerque 

[]

>The size of cache is used to reduce the disk access . If
the record is less than 100 bytes >on 1024 will contain aprox. 10 records if
you table has no more than 10.000 you will >need 976 pages
 
>In my case I take the amount of memory, split 30% of it
to sqlite using a proportionality >to the size of the database for each
attach
 
- Original Message 

From: Jay A. Kreibich  
[]
> A smaller page size will often lower I/O performance. 

> It depends quite a bit on the OS and filesystem you're using. 
> you need to know your data and your environment.


  __
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] One data base versus two smaller ones

[a1rex]

Thank you very much for your help!
 
Since my typical record is less than 100 bytes I guess that I can use Page Size 
= 512 bytes
 without degradation of database performance.This would conserve memory.
 
How vital is default number of pages for database performance? 
Can I drastically drop the number of pages to number of records accessed by 
user per his typical database session? 
 
Regards, 
Samuel  


- Original Message 
From: Jay A. Kreibich 

On Fri, Feb 19, 2010 at 09:39:08AM -0300, Israel Lins Albuquerque scratched on 
the wall:
> Samuel, 
> 
> Each one attached database has its own page cache with 2000 
> (default number of pages in cache) * 1024 (default size in
> bytes of a page),

  On many Windows systems it will default to 4096.  It tries to match the
  cluster size on NTFS volumes.

> totaling 2 Mb of ram. 

  Actually, it is closer to 3MB (or 9MB) of used memory, as each entry
  in the page cache has some overhead.

   -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



  __
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
Optimized for Yahoo! Get it Now for Free! at 
http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Memory usage – one data base versus tw o smaller ones

[a1rex]

For some reasons it is more convenient for the project to
have a few smaller databases with unrelated data than one containing
everything.  My only concern is RAM memory. 
How much burden/memory overhead an additional database would introduce?
 
Thank you for your input,

Samuel


  __
Ask a question on any topic and get answers from real people. Go to Yahoo! 
Answers and share what you know at http://ca.answers.yahoo.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1 reader 1 writer but sqlite3_step fails wit h “database is locked” error in both processes

[a1rex]

Closing the thread:
>Then why sqlite3_step() fails for the reader and for the 
writer?


The reader fails since
it cannot obtain SHARED lock required for reading.
It cannot obtain that
lock since the writer already managed to obtain PENDING or EXCLUSIVE lock.
 
The writer fails since
there is pending SHARED lock acquired by reader which did not finished reading.
The writer needs to obtain EXCLUSIVE lock and no other
locks of any kind are allowed to coexist with an EXCLUSIVE lock.


- Original Message 
From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Sun, February 14, 2010 2:54:35 PM
Subject: Re: [sqlite] 1 reader 1 writer but sqlite3_step fails with “database 
is locked”  error in both processes

a1rex wrote:
>  I thought that I can have 1 writer and many readers 

You thought incorrectly. You can have one writer OR many readers.

Igor Tandetnik


  __
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
Optimized for Yahoo! Get it Now for Free! at 
http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1 reader 1 writer but sqlite3_step fails wit h “database is locked” error in both processes

[a1rex]

Thank you Igor! Now I understand your OR!



- Original Message 
From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Sun, February 14, 2010 2:54:35 PM
Subject: Re: [sqlite] 1 reader 1 writer but sqlite3_step fails with “database 
is locked”  error in both processes

a1rex wrote:
>  I thought that I can have 1 writer and many readers 

You thought incorrectly. You can have one writer OR many readers.

Igor Tandetnik

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



  __
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] 1 reader 1 writer but sqlite3_step fails wit h “database is locked” error in both processes

[a1rex]

Then why sqlite3_step() fails for the reader and for the writer?



- Original Message 
From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Sun, February 14, 2010 2:54:35 PM
Subject: Re: [sqlite] 1 reader 1 writer but sqlite3_step fails with “database 
is locked”  error in both processes

a1rex wrote:
>  I thought that I can have 1 writer and many readers 

You thought incorrectly. You can have one writer OR many readers.

Igor Tandetnik

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



  __
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
Optimized for Yahoo! Get it Now for Free! at 
http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 1 reader 1 writer but sqlite3_step fails wit h “database is locked” error in both processes

[a1rex]

Process A updates data base table in the tight loop. Nothing special:

loop
 sql = "UPDATE table SET blob=:blob WHERE id=?"; 
 rc = sqlite3_prepare_v2(…)
 rc = sqlite3_bind_int(…)
 sqlite3_bind_blob(…)
 rc = sqlite3_step(…) 
 rc = sqlite3_reset(…) 
rc = sqlite3_finalize(…);

Process B just reads from
the same table. 
 
Loop
{
sql = "SELECT * from table";
sqlite3_prepare_v2(…);
loop with 
rc = sqlite3_step(…);
 
rc = sqlite3_finalize(…);
}
 
The problem which I encounter is as follows:
 
== In process A ==
sqlite3_step() and sqlite3_reset(…) fail frequently with the error nr 5 = 
“database is locked”.

== In process B ==
sqlite3_step(…) and sqlite3_finalize(…) fail with the same error “database is 
locked”.
 
  I thought that I can have 1 writer and many readers 
 
Unfortunately it appears that even 1 reader can interfere with the writer. And 
the reader fails too.
The OS is Win XP Pro.
 
What don’t I understand here?  
 
Thank you,
Samuel 


  __
Ask a question on any topic and get answers from real people. Go to Yahoo! 
Answers and share what you know at http://ca.answers.yahoo.com
___
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

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?

[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

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

[a1rex]

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

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

[a1rex]

Thank you kindly for all your suggestions!
 
>If you want SQLite to support all ACID properties you cannot change
>anything to speed up updates.
 
Making sure that I do not loose a character was my primary objective.
 
>  If you are doing bulk updates, and are in a position to re-run the
>  data in case of an error, wrap batches of 100 or more in a transaction.
>  Just be sure to handle any error case that trips an automatic rollback.
 
>Make all your updates within a single transaction.
 
I will try to do something to that extent using timer and character counter. 
I hoped that I could update the text stored in the database character by 
character as fast as  they
come from the keyboard driver. Unfortunately updates noticeably slow down the 
display of typed characters.  
 
>As a test, have you tried wrapping your updates in a transaction? 
> That would isolate if the slow down is the actual writing of the data to
>disk. 
 
It appears that single transaction is slow and I have to
make less transaction with more data.

>Where is the file sitting: A local drive, or something across a network
>connection?
 
File is sitting on a local drive.  It is IDE Seagate 120MB drive – ST3120026A
 
  Capacity: 120.9 GB
  Speed: 7200 rpm
  Average Read Time:8.5 ms
  Cylinders:1023
  Heads:256
  Sectors: 63   

·  I/O data transfer rate - up to 100 Mbps 
·  Sustained data transfer rate - Up to 58 Mbps 
·  Average seek time - 8.5ms 
·  Average latency - 4.16ms
 
From this data nothing justifies the 120ms update of the record! 

Thank you again.

Regards,
Samuel


  __
The new Internet Explorer® 8 - Faster, safer, easier.  Optimized for Yahoo!  
Get it Now for Free! at http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

[a1rex]

>- Original Message 
>From: Pavel Ivanov 
>To: General Discussion of SQLite Database 
>Sent: Wed, February 3, 2010 11:37:17 AM

>Just first thought came to my mind: are you sure that 2 versions of
>code mentioned do the same thing? In particular I'm asserting that
>second version (under #if 1) doesn't do any actual updating and
>doesn't change your database because you have wrong parameter indexes.

You are right! 
Retraction. Mea Culpa. Back to square one… 
The modified code did not write anything to the drive! But there was no error 
message from the SQLITE.
After proper indexing the writing time is about the same!

>And one more question: why don't you store your prepared statement
>somewhere and do not prepare it for each row again and again?

I will. But the problem highlighted by my mistake with indexes is not with the 
prepared statement,
but extremely slow write to the drive.

int UpdateNotesRecord(sqlite3 *handle,
 int idArg,
 CString note)
{
sqlite3_stmt *stmt;
int rc;

#if 1 // 100 updates with 5 character string take 12 seconds
 
char *sql = "UPDATE notes SET note=? WHERE id=?";
rc = sqlite3_prepare_v2(handle, sql, (int) strlen(sql), , 
NULL/**/);
if(rc) PrintError("UPDATE prepare_v2",rc,handle);

// idArg
   rc = sqlite3_bind_int(stmt, 1, idArg); 
// was: rc = sqlite3_bind_int(stmt, 2, idArg);
if(rc) PrintError("bind idArg error",rc,handle);

// note
int byteCount = note.GetLength();
char *p = note.GetBuffer();

  rc = sqlite3_bind_text(stmt, 1, p, byteCount, SQLITE_STATIC); 
// was rc = sqlite3_bind_text(stmt, 2, p, byteCount, SQLITE_STATIC);
if(rc) PrintError("bind note text error",rc,handle);
#endif

rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE)
PrintError("update step error",rc,handle);

rc = sqlite3_finalize(stmt);
if(rc) PrintError("finalize update error",rc,handle);
return rc;
}

Is there any way to configure database for efficient updates of small amount of 
text?

Thank you for reading. Any comment greatly appreciated.
Samuel 


  __
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
Optimized for Yahoo! Get it Now for Free! at 
http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF-8 and UTF-16

[a1rex]

>From: Jens Miltner 

>Sent: Wed, February 3, 2010 9:46:06 AM

>Just another thought to consider: depending on the amount of non-ASCII  
>(or non-roman) string data stored in your database, in may be more  
>efficient to use UTF-8 encoding rather than UTF-16 encoding:
>UTF-8 takes up less space for ASCII or roman text, whereas it may take  
>up more space for other characters. This really depends on the mix.

Thank you for your suggestions!. I will go with UTF-8 encoding.
Regards,
Samuel


  __
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
Optimized for Yahoo! Get it Now for Free! at 
http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

[a1rex]

I just encountered very curious case in Sqlite. 
I have very simple data base with only one table and one index:

"CREATE TABLE IF NOT EXISTS notes(id INTEGER PRIMARY KEY, note TEXT)";

My updates to the simple text database were very slow. Extremely slow!

I changed my code and achieved 1000 speed improvement.
Nevertheless, something must be wrong with sqlite3_bind_parameter_index 
functions !?

My Update function looks as follows:

int UpdateNotesRecord(sqlite3 *handle,
 int idArg,
 CString note)
{
sqlite3_stmt *stmt;
int rc;

#if 0 // 100 updates with 5 charcter string takes 12 000 ms

char *sql = "UPDATE notes SET note=:cNote WHERE id=:idArg";

rc = sqlite3_prepare_v2(handle, sql, (int) strlen(sql), , 
NULL/**/);
if(rc) PrintError("UPDATE prepare_v2",rc,handle);

// idArg
rc = sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(stmt,":idArg"), 
idArg);
if(rc) PrintError("bind idArg error",rc,handle);

int byteCount = note.GetLength();
char *p = note.GetBuffer();

rc = sqlite3_bind_text(stmt, sqlite3_bind_parameter_index(stmt,":cNote"), 
p, byteCount, SQLITE_STATIC);
if(rc) PrintError("bind note text error",rc,handle);
#endif

#if 1 // 100 updates with 5 character string takes 90 ms
  // magnitude of 1000 less than with code above!

char *sql = "UPDATE notes SET note=? WHERE id=?";
rc = sqlite3_prepare_v2(handle, sql, (int) strlen(sql), , 
NULL/**/);
if(rc) PrintError("UPDATE prepare_v2",rc,handle);

// idArg
rc = sqlite3_bind_int(stmt, 1, idArg);
if(rc) PrintError("bind idArg error",rc,handle);

// note
int byteCount = note.GetLength();
char *p = note.GetBuffer();

rc = sqlite3_bind_text(stmt, 2, p, byteCount, SQLITE_STATIC);
if(rc) PrintError("bind note text error",rc,handle);
#endif

// UPDATE STEP
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE)
{
PrintError("update step error",rc,handle);
}

rc = sqlite3_finalize(stmt);
if(rc) PrintError("finalize update error",rc,handle);
return rc;
}

Thank you for reading. Any comment greatly appreciated.
Samuel 



  __
Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your 
favourite sites. Download it now
http://ca.toolbar.yahoo.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF-8 and UTF-16

[a1rex]

Thank you for your help!

This call:
sqlite3_exec(handle,"PRAGMA encoding = \"UTF-16\"",NULL,NULL,);
works.


Samuel


- Original Message 
From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Sun, January 31, 2010 6:12:27 PM
Subject: Re: [sqlite] UTF-8 and UTF-16

a1rex wrote:
> I am planning to store text in a database which has to accommodate a few 
> international languages.
> In this case I have to use UTF-16LE encoding for my TEXT fields.

Perhaps there are some external reasons, but there's nothing in SQLite that 
would force this. For example, UTF-8 is just as capable of representing any 
Unicode string as UTF-16.

> I know that once an encoding has been set for a database, it cannot be 
> changed.
> Do BLOBS are effected?

No.

> I guess I cannot mix TEXT  UTF-8 and UTF-16 columns in the same data base, 
> can I?

Why would you want to?

> Can I get away with a database encoded in UTF-8  by using 
> sqlite3_bind_text16() function for TEXT fields when I need UTF-16 text?

Yes. SQLite automatically converts between UTF-8 and UTF-16 as necessary.

> After opening empty database, I tried to execute:
> 
> sqlite3_exec(handle,"PRAGMA encoding = UTF-16",NULL,NULL,);
> 
> But I got the following error:
> PRAMA error: near "-": syntax error.  What did I do wrong?

It's PRAGMA encoding = "UTF-16". The parameter needs to be quoted.

Igor Tandetnik

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



  __
Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your 
favourite sites. Download it now
http://ca.toolbar.yahoo.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UTF-8 and UTF-16

[a1rex]

I am planning to store text in a database which has to accommodate a few 
international languages. 
In this case I have to use UTF-16LE encoding for my TEXT fields.   
I know that once an encoding has been set for a database, it cannot be changed. 
 
Do BLOBS are effected?

I guess I cannot mix TEXT  UTF-8 and UTF-16 columns in the same data base, can 
I?
 
Can I get away with a database encoded in UTF-8  by using sqlite3_bind_text16() 
function for TEXT fields when I need UTF-16 text?
 
After opening empty database, I tried to execute: 

sqlite3_exec(handle,"PRAGMA encoding = UTF-16",NULL,NULL,);
 
But I got the following error:
PRAMA error: near "-": syntax error.  What did I do wrong?
 
Thank you for reading. Any comment appreciated!
Samuel


  __
The new Internet Explorer® 8 - Faster, safer, easier.  Optimized for Yahoo!  
Get it Now for Free! at http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incremental i/o - Is anyone using ???sqlite3_blob_write????

[a1rex]

  >Look at the source for sqlite3_blob_open().


 
From stepping through the code I
have learned that  blob read/write fails and returns SQLITE_ABORT because  in 
the blob handle structure
 
structIncrblob {
  int flags;  /*
Copy of "flags" passed to qlite3_blob_open() */
  int nByte;  /*
Size of open blob, in bytes */
  int iOffset;/* Byte
offset of blob in cursor data */
  BtCursor *pCsr; /* Cursor pointing at blob row */
  sqlite3_stmt *pStmt;/* Statement holding cursor open */
  sqlite3 *db;/* The associated database */
};
 
 
pCstr->estate is turned to 0
(CURSOR_INVALID) by some previous Sqlite operation.


This forces invalidation of the statement ‘pStmt’  variable and subsequent 
return of the
SQLITE_ABORT code when first sqlite3BtreeData
is called by the blob_read:
 
SQLITE_PRIVATE int sqlite3BtreeData(BtCursor *pCur, u32 offset, u32
amt, void *pBuf){
  int rc;
 
#ifndefSQLITE_OMIT_INCRBLOB
  if (
pCur->eState==CURSOR_INVALID ){ //   
<-|

return SQLITE_ABORT; // 
<|
  }
#endif
 
//…
}
 
Now, if only I could guess what can force cursor eState to
CURSOR_INVALID value!? 
 
Thank you,
Samuel


  __
The new Internet Explorer® 8 - Faster, safer, easier.  Optimized for Yahoo!  
Get it Now for Free! at http://downloads.yahoo.com/ca/internetexplorer/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incremental i/o - Is anyone using ???sqlite3_blob_write????

[a1rex]

Thank you very much for your response! I
appreciate you input.

>  Incremental BLOB I/O handles depend on an internal statement.

Sorry, which statement is involved?

 
int sqlite3_blob_open(
  sqlite3*,
  const char *zDb,
  const char *zTable,
  const char
*zColumn,
  sqlite3_int64 iRow,
  int flags,
  sqlite3_blob
**ppBlob
);does not have a statement.  To get 'ppBlob' we have to specify only  iRow.


>  I'd turn on tracing so that you have an exact record of all the
>  statements that are executed.

 
I do not do any DROPS, CREATS nothing.
 My table ‘A’ has a BLOB.  What I do is only 1 UPDATE in the table ‘B’. I 
verified using
sqlite3_total_changes(sqlite3*) – that only 1 changed happened – change to the
row in the table ‘B’ . Yet, to my surprise the handle to the BLOB in the table 
‘A’
got invalidated! 

 
I do not do any DROPS, CREATS nothing special at all!

 
My table ‘A’ has a BLOB.  What I do is 1 UPDATE in the table ‘B’. I verified 
using
sqlite3_total_changes(sqlite3*) – that only 1 changed happened – change to the
row in the table ‘B’ . Yet, to my surprise the handle to the BLOB in the table
‘A’ got invalidated!
 
(All what I do in my code is as follows:
In a loop, I find  iRow for my blob in the table ‘A’,  open blob in the iRow. 
Than I update table
‘B’ with some information from table ‘A’.  Than I  blob_write to the blob in
the table A. This write fails frequently with code SQLITE_ABORT. I do not see 
any reason why it fails!)

Thank you again for your comments.

Best Regards,
Samuel





From: Jay A. Kreibich <j...@kreibi.ch>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tue, January 19, 2010 11:00:54 AM
Subject: Re: [sqlite] Incremental i/o - Is anyone using 
???sqlite3_blob_write

On Tue, Jan 19, 2010 at 07:14:11AM -0800, a1rex scratched on the wall:


> So, I coded accordingly.  I thought that I could keep the same handle to the 
> blob forever as long
> as I do not touch the row where my blob is located. At least that is my
> understanding of the documentation. 
>  
> Soon, I discovered that after some updates to other table my
> blob handle is no longer valid.  The
> problem was not consistent; some updates would invalidate the handle some 
> would
> not.  

  Incremental BLOB I/O handles depend on an internal statement.  As I
  understand it, any operation that invalidates statements will also
  invalidate all the current BLOB handles.  For example, I'm fairly
  sure changing the schema (any CREATE..., DROP..., etc.) will
  invalidate all BLOB I/O handles.  I haven't verified this, but my
  (small) understanding of the code makes me believe this is true.

  As you're aware, there are other reasons a handle can become invalid.

  I'd turn on tracing so that you have an exact record of all the
  statements that are executed.  When you hit a handle error, start
  looking through the trace log to see what might be causing it.

   -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



  __
Ask a question on any topic and get answers from real people. Go to Yahoo! 
Answers and share what you know at http://ca.answers.yahoo.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help understanding the basic of C++/sqlite

[a1rex]

1. Complete c program for beginners  is here:

http://manishtech.wordpress.com/2009/03/30/sqlite-with-c/


2. sqlite3_exec with callback is an obsolete concept from sqlite2

Use sqlite3_prepare_v2 with sqlite3_step  as it is linear,  more effective and 
giving more control approach.
 
I hope it helps,
Samuel




From: noel frankinet 
To: General Discussion of SQLite Database 
Sent: Tue, January 19, 2010 9:18:06 AM
Subject: Re: [sqlite] Need help understanding the basic of C++/sqlite

Fabrice NA a écrit :

Hi,

In sqlite3_exec, you pass a function pointer (callback).
Sqlite call that function with each row of data

Best wishes

Noël
> Hi all,
>
>
>
> I am trying to understand Sqlite and to make thing worse I am also learning
> C++. You will not be surprised that I find it really hard to understand the
> C++ example on the web. Can someone guide me to digest this? If you can
> explain please do so by extrapolating since I am a total newbie.
>
>
>
> I have managed to compile the code example into a file called testdb.exe and
> have created a database named Cars.db containing 7 rows. (notice that I have
> removed some part of that code that I don't need help for)
>
>
>
> #include 
>
> #include 
>
>
>
> static int callback(void *NotUsed, int argc, char **argv, char **azColName){
>
>   int i;
>
>   for(i=0; i
> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
>
>   }
>
>   printf("\n");
>
>   return 0;
>
> }
>
>
>
> int main(int argc, char **argv){
>
>   sqlite3 *db;
>
>   char *zErrMsg = 0;
>
>   int rc;
>
>
>
>   if( argc!=3 ){
>
> fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
>
> exit(1);
>
>   }
>
>   rc = sqlite3_open(argv[1], );
>
>
>
>   rc = sqlite3_exec(db, argv[2], callback, 0, );
>
>
>
>   sqlite3_close(db);
>
>
>
>   return 0;
>
> }
>
>
>
> Now in command line when I execute testdb.exe cars.db "select * from cars"
> (the table is called cars too) everything works fine and I get an output
> with all my seven rows. But how come this work? Is this some kind of magic?
>
>
>
> Even after reading the doc again and again I don't understand (probably
> because I am still learning some basic C++ concepts). Here my questions;
>
>
>
> 1)   What is the purpose of doing "sqlite3  *db"  are we just creating a
> pointer of type sqlite3 named db here?
>
>
>
> 2)   At first I though that for the main function the first
> parameter  "cars.db"
> was represented by the variable argc and the second "select * from cars" by
> argv. Well at the end, it looks like that "cars.db" is argv[1] and that the
> select statement is argv[2]. What is argc then?  Seems like it's the number
> of rows returned by the query (when looking at function callback) but how
> the program find this out? How come we have a line "  if( argc!=3 )" and see
> this same argc variable in the callback function?
>
>
>
> 3)   I don't understand the third argument from the query " rc =
> sqlite3_exec(db, argv[2], callback, 0, );" my problem is
> understanding the callback function inside the sqlite3_exec function.
> Reading this link http://www.sqlite.org/c3ref/exec.html didn't help too much
> even though it has been written in plain English.
>
>
>
> 4)   I am using VC++ Express from Microsoft  on WinXP and would like to
> know if it's possible to pass parameter when debugging i.e. tell the
> debugger to use cars.db for file and use "select * from cars" as a statement
> (this would allow me to see what's hapening witout replacing variables by
> their real values).
>
>
>
> 5)   It's really hard to find some simple example on internet about C++
> working with sqlite. Can any of you provide with simple sample codes that
> shows how you can do and what you can do with sqlite and C++?
>
>
>
> I hope I didn't offended anyone with my lack of knowledge and I thank in
> advance the courageous ones who managed to read this email until the end and
> probably got answers to my questions.
>
>
>
> Fabou
> ___
> 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



  __
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] Incremental i/o - Is anyone using ‘sq lite3_blob_write’?

[a1rex]

I have to admit my confusion with incremental i/o functions.
 
I am not sure that problem, which I encountered, is due to
internal bug in my program, I hit a Sqlite bug or Sqlite behaves properly. I
would greatly appreciate any comments! 
 
First, the incremental i/o looked as a God’s sent gift to me.
Being able to just read or write a portion of the blob looked like a huge time
saving and great advantage over the traditional approach.
 
So, I coded accordingly.  I thought that I could keep the same handle to the 
blob forever as long
as I do not touch the row where my blob is located. At least that is my
understanding of the documentation. 
 
Soon, I discovered that after some updates to other table my
blob handle is no longer valid.  The
problem was not consistent; some updates would invalidate the handle some would
not.  
 
My solution to the problem seems (to me) a crude one. Before
any update I close all blob handles do an update on reopen the blobs. 

 
If anyone using incremental i/o could confirm that this
is general practice I would be thankful! 

 
Thank you kindly,
Samuel
 (Since I can split my blob in parts and do a 'traditional' update only
to the required part the need to frequently close and open  blobs seems
like an overhead.) 


  __
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] BLOB handle expires when unrelated table is UPDATED!?

[a1rex]

 According to the documentation athttp://www.sqlite.org/c3ref/blob_open.html
“
If the row that a BLOB handle points to is
modified by an UPDATE, DELETE, or by ON CONFLICT side-effects
then the BLOB handle is marked as "expired". 
This is true if any column of the row is
changed, even a column other than the one the BLOB handle is open on.
 Calls to sqlite3_blob_read() and sqlite3_blob_write() for
a expired BLOB handle fail with an return code of SQLITE_ABORT.
”
 
However, it looks that handle can expire
when UPDATE is done on the column in the other unrelated to the blob table.
Read of the blob would return SQLITE_ABORT “not an error”(?!) and no data is 
read.
 
 
Is it a bug, a feature or the expected
behaviour? 
 
 
Thanks for your
attention.
 
 P.S
 
Searching the web I
found the following reference:
blob incremental i/o constraints
by Roger Binns Dec 02, 2007; 02:15am
Open a blob for reading.  Insert into the same table (not affecting the 
blob or its row).  On calling sqlite3_blob_read, I get SQLITE_ABORT 
returned and the errmsg is "not an error".  This also
happens if I do a 
few reads, and then the insert, on the next read.  (This also happens 
with blobs open for writing and doing read or write after the table is 
changed).


  __
Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your 
favourite sites. Download it now
http://ca.toolbar.yahoo.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users