[sqlite] transaction in one thread and other thread also trying to write data
Hi all, My application is multithreaded. It maintains only connection per application. Database accessed by single process only. ThreadA will do database write operations(bulk) in a transaction. ThreadB will do single write operation without transaction but same connection pointer. Here, application needs to synchronize the calls among threads by using synchronization technique(critical_section/mutex)? (OR) begin transaction and commit will synchronize the calls between threads? Please suggest. Thanks, av. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite suddenly thrown error
I tried to get error code 21 by using below code snippet. But didn't
get. Skipped sqlite3_reset() for 2 to 10,000 records.
code snippet:
int main()
{
//create db connection and statement
for(int i = 0; i < 1; ++ i)
{
MyFunc(i, "abc", "def");
}
//finalize m_insert_stmt
//close
return 1;
}
bool MyFunc( const int col1, const char * const col2, const char * const col3)
{
static bool reset_once = false;
bool r = false;
int nr = sqlite3_bind_int( m_insert_stmt, 1, col1 );
if(SQLITE_OK != nr) return r;
nr = sqlite3_bind_text( m_insert_stmt, 2, col2, strlen(col2),
SQLITE_STATIC );
if(SQLITE_OK != nr) return r;
nr = sqlite3_bind_text( m_insert_stmt, 3, col3, strlen( col3
), SQLITE_STATIC );
if(SQLITE_OK != nr) return r;
nr = sqlite3_step( m_insert_stmt );
if(!reset_once)
{
reset_once = true;
sqlite3_reset( m_insert_stmt );
}
return r;
}
On Mon, Mar 31, 2014 at 3:52 PM, d b <va230...@gmail.com> wrote:
> >>You might have failed to call sqlite3_reset()
>
> Here is the point. If my application gets any error(other than SQLITE_OK)
> during binding parameters for 11th record, function may return without
> sqlite3_reset. So, subsequent insertions may result same error. Am I right?
>
>
> On Mon, Mar 31, 2014 at 3:33 PM, d b <va230...@gmail.com> wrote:
>
>> My application calls sqlite3_finalize() after insertion of 10,000
>> record(loop). Is there any other reason?
>>
>>
>> On Mon, Mar 31, 2014 at 3:23 PM, d b <va230...@gmail.com> wrote:
>>
>>> sqlite3_bind_text() returned 21 error. It is throwing for all insertions
>>> once error introduced.
>>>
>>> For ex: Here, Inserted first 10 records successfully. then it was
>>> started throwing error 21 for 11th record on-wards till last record(10,000
>>> record). I was able to insert the records successfully after restart the
>>> application only.
>>>
>>>
>>> On Mon, Mar 31, 2014 at 2:23 PM, d b <va230...@gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> I am getting sqlite error 21.
>>>>
>>>>
>>>> There is only one sqlite prepared statement for insertion. It's not
>>>> multi threaded app. The below function repeatedly calling for every record
>>>> insertion. This app was running from last 4 days. On third, it was started
>>>> throwing error 21. What could be the reason?
>>>>
>>>> steps from application:
>>>>
>>>> bool stmt::execute(const char* const insertquery, listofinputparams)
>>>> {
>>>>
>>>>insertquery is insert or replace into mystuff(id, name, value)
>>>> values(?,?,?);
>>>>
>>>>bool r = false;
>>>>
>>>> sqlite3_stmt s = find(insertquery);
>>>>
>>>>while(enumerate listofinputparams)
>>>>{
>>>>bind each argument for insert statement s
>>>>}
>>>>
>>>>sqlite3_step(s);
>>>>
>>>>sqlite3_reset(s);
>>>>
>>>>return r;
>>>> }
>>>>
>>>> Thanks,
>>>> a v
>>>>
>>>
>>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite suddenly thrown error
>>You might have failed to call sqlite3_reset()
Here is the point. If my application gets any error(other than SQLITE_OK)
during binding parameters for 11th record, function may return without
sqlite3_reset. So, subsequent insertions may result same error. Am I right?
On Mon, Mar 31, 2014 at 3:33 PM, d b <va230...@gmail.com> wrote:
> My application calls sqlite3_finalize() after insertion of 10,000
> record(loop). Is there any other reason?
>
>
> On Mon, Mar 31, 2014 at 3:23 PM, d b <va230...@gmail.com> wrote:
>
>> sqlite3_bind_text() returned 21 error. It is throwing for all insertions
>> once error introduced.
>>
>> For ex: Here, Inserted first 10 records successfully. then it was started
>> throwing error 21 for 11th record on-wards till last record(10,000 record).
>> I was able to insert the records successfully after restart the application
>> only.
>>
>>
>> On Mon, Mar 31, 2014 at 2:23 PM, d b <va230...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I am getting sqlite error 21.
>>>
>>>
>>> There is only one sqlite prepared statement for insertion. It's not
>>> multi threaded app. The below function repeatedly calling for every record
>>> insertion. This app was running from last 4 days. On third, it was started
>>> throwing error 21. What could be the reason?
>>>
>>> steps from application:
>>>
>>> bool stmt::execute(const char* const insertquery, listofinputparams)
>>> {
>>>
>>>insertquery is insert or replace into mystuff(id, name, value)
>>> values(?,?,?);
>>>
>>>bool r = false;
>>>
>>> sqlite3_stmt s = find(insertquery);
>>>
>>>while(enumerate listofinputparams)
>>>{
>>>bind each argument for insert statement s
>>>}
>>>
>>>sqlite3_step(s);
>>>
>>>sqlite3_reset(s);
>>>
>>>return r;
>>> }
>>>
>>> Thanks,
>>> a v
>>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite suddenly thrown error
My application calls sqlite3_finalize() after insertion of 10,000
record(loop). Is there any other reason?
On Mon, Mar 31, 2014 at 3:23 PM, d b <va230...@gmail.com> wrote:
> sqlite3_bind_text() returned 21 error. It is throwing for all insertions
> once error introduced.
>
> For ex: Here, Inserted first 10 records successfully. then it was started
> throwing error 21 for 11th record on-wards till last record(10,000 record).
> I was able to insert the records successfully after restart the application
> only.
>
>
> On Mon, Mar 31, 2014 at 2:23 PM, d b <va230...@gmail.com> wrote:
>
>> Hi,
>>
>> I am getting sqlite error 21.
>>
>>
>> There is only one sqlite prepared statement for insertion. It's not
>> multi threaded app. The below function repeatedly calling for every record
>> insertion. This app was running from last 4 days. On third, it was started
>> throwing error 21. What could be the reason?
>>
>> steps from application:
>>
>> bool stmt::execute(const char* const insertquery, listofinputparams)
>> {
>>
>>insertquery is insert or replace into mystuff(id, name, value)
>> values(?,?,?);
>>
>>bool r = false;
>>
>> sqlite3_stmt s = find(insertquery);
>>
>>while(enumerate listofinputparams)
>>{
>>bind each argument for insert statement s
>>}
>>
>>sqlite3_step(s);
>>
>>sqlite3_reset(s);
>>
>>return r;
>> }
>>
>> Thanks,
>> a v
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite suddenly thrown error
sqlite3_bind_text() returned 21 error. It is throwing for all insertions
once error introduced.
For ex: Here, Inserted first 10 records successfully. then it was started
throwing error 21 for 11th record on-wards till last record(10,000 record).
I was able to insert the records successfully after restart the application
only.
On Mon, Mar 31, 2014 at 2:23 PM, d b <va230...@gmail.com> wrote:
> Hi,
>
> I am getting sqlite error 21.
>
>
> There is only one sqlite prepared statement for insertion. It's not
> multi threaded app. The below function repeatedly calling for every record
> insertion. This app was running from last 4 days. On third, it was started
> throwing error 21. What could be the reason?
>
> steps from application:
>
> bool stmt::execute(const char* const insertquery, listofinputparams)
> {
>
>insertquery is insert or replace into mystuff(id, name, value)
> values(?,?,?);
>
>bool r = false;
>
> sqlite3_stmt s = find(insertquery);
>
>while(enumerate listofinputparams)
>{
>bind each argument for insert statement s
>}
>
>sqlite3_step(s);
>
>sqlite3_reset(s);
>
>return r;
> }
>
> Thanks,
> a v
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite suddenly thrown error
Hi,
I am getting sqlite error 21.
There is only one sqlite prepared statement for insertion. It's not multi
threaded app. The below function repeatedly calling for every record
insertion. This app was running from last 4 days. On third, it was started
throwing error 21. What could be the reason?
steps from application:
bool stmt::execute(const char* const insertquery, listofinputparams)
{
insertquery is insert or replace into mystuff(id, name, value)
values(?,?,?);
bool r = false;
sqlite3_stmt s = find(insertquery);
while(enumerate listofinputparams)
{
bind each argument for insert statement s
}
sqlite3_step(s);
sqlite3_reset(s);
return r;
}
Thanks,
a v
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] check constraint
Hi,
I would like to add check constraint for existing database. Is it
possible?
for ex:
create table emp(id integer primary key autoincrement,
fullname,fathername,mothername as text);
insert into emp(fullname,fathername,mothername) values("a","b","c");
insert into emp(fullname,fathername,mothername) values("b","d","e");
I want to add check constraint for fathername and mothername columns for
existing database.
What if I add check constraints for above schema. I tried, but it didnt
throw any errors as well as check constraints didn't apply.
Is there anyway to add check constraints for existing database?
Thanks,
va
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] crashed
Hi all, crashed at winShmBarrier from sqlite free/sqlite mutex leave/sqlite page apis. application using database extensively on windows that time. any idea? Thanks, d b ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite error
I didn't set any timeout value. It was fine with delete/insert/update/select queries. Suddenly, started giving sqlite error with error code 1. On Tue, Jan 21, 2014 at 9:03 AM, d b <va230...@gmail.com> wrote: > Hi all, > > sqlite throws error code 1 while sqlite busy with read/write operations. > what could be the reason. But, I could not reproduce this with test > program. what is the best way to handle sqlite error. Is it like sqlite > abort case? > > Thanks, > d b > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite error
Hi all, sqlite throws error code 1 while sqlite busy with read/write operations. what could be the reason. But, I could not reproduce this with test program. what is the best way to handle sqlite error. Is it like sqlite abort case? Thanks, d b ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite abort
Thank you. What is the default time? what is the ideal time for sqlite? On Fri, Jan 17, 2014 at 6:53 PM, d b <va230...@gmail.com> wrote: > Hi all, > > When database busy with read/write operations, it's thrown sqlite abort > error while delete a record. > > what does it mean sqlite abort(Callback routine requested an abort)? > > Shouldn't application delete when database busy with read/write > operations? > > Thanks, > a v > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite abort
Hi all, When database busy with read/write operations, it's thrown sqlite abort error while delete a record. what does it mean sqlite abort(Callback routine requested an abort)? Shouldn't application delete when database busy with read/write operations? Thanks, a v ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] prepared statemnt for column names and sorting preference
Hi, select * from emp order by empid desc; //here empid is column name among clolumns. Now, I want to write prepared statement for above query. select * from emp order by ? ?; //I want to substitute column name and sorting preference. is it possible with sqlite? thanks, a ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization
Thanks alot RSmith.
On Mon, Nov 18, 2013 at 6:04 PM, d b <va230...@gmail.com> wrote:
> Hi Igor/Keith,
>
> I tried with both queries. I expect to delete all rows belongs to key 1.
> But not deleted. Am I missing something while writing queries?
>
> delete from emp where key = 1 and (name='' or name='f');
> DELETE FROM emp WHERE key = 1 AND (name IS NULL OR name = 'f');
>
>
>
> -
>
> create table if not exists emp(key integer not null, name text not null ,
> personaldata text not null, unique(key, name));
> insert into emp (key, name, personaldata) values(1, 'a', 'z');
> insert into emp (key, name, personaldata) values(1, 'b', 'zz');
> insert into emp (key, name, personaldata) values(2, 'c', 'y');
> insert into emp (key, name, personaldata) values(3, 'd', 'yy');
> insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
>
> ------
>
>
> On Mon, Nov 18, 2013 at 5:20 PM, d b <va230...@gmail.com> wrote:
>
>> Thanks RSmith.
>>
>> It works.
>>
>> But, I am looking for single query for prepared statements. That's the
>> actual struggle for me.
>>
>>
>> On Mon, Nov 18, 2013 at 4:24 PM, d b <va230...@gmail.com> wrote:
>>
>>> Hi RSmith,
>>>
>>> Thanks. Still, I could not delete with single query.
>>>
>>>
>>> create table if not exists emp(key integer not null, name text not null
>>> , personaldata text not null, unique(key, name));
>>> insert into emp (key, name, personaldata) values(1, 'a', 'z');
>>> insert into emp (key, name, personaldata) values(1, 'b', 'zz');
>>> insert into emp (key, name, personaldata) values(2, 'c', 'y');
>>> insert into emp (key, name, personaldata) values(3, 'd', 'yy');
>>> insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
>>>
>>> bool delete_emp(int key, string name = "")
>>> {
>>> string query = ???;
>>>
>>> if(name.length() > 0)
>>> {
>>> //needs to delete specific row. by unique key.
>>> }
>>> else
>>> {
>>> //needs to delete rows belongs to key
>>> }
>>> }
>>>
>>>
>>> On Mon, Nov 18, 2013 at 2:13 PM, d b <va230...@gmail.com> wrote:
>>>
>>>> Hi Luis,
>>>>
>>>> Those are parameters.
>>>>
>>>> This is the query after replacing with ?1 and ?2.
>>>>
>>>> delete from emp where key = '123' and (case when name = 'abc' is null
>>>> THEN 1 else name = 'abc' end);
>>>>
>>>> It covered "delete from emp where key = '123' and name = 'abc';" but
>>>> not other query.
>>>>
>>>> I tried with "select (case when name = 'abc' is null THEN 1 else name
>>>> = 'abc' end) from emp;" query. It's always going to else portion when
>>>> 'abc' doesn't exist in table. Any suggestions?
>>>>
>>>
>>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization
Hi Igor/Keith,
I tried with both queries. I expect to delete all rows belongs to key 1.
But not deleted. Am I missing something while writing queries?
delete from emp where key = 1 and (name='' or name='f');
DELETE FROM emp WHERE key = 1 AND (name IS NULL OR name = 'f');
-
create table if not exists emp(key integer not null, name text not null ,
personaldata text not null, unique(key, name));
insert into emp (key, name, personaldata) values(1, 'a', 'z');
insert into emp (key, name, personaldata) values(1, 'b', 'zz');
insert into emp (key, name, personaldata) values(2, 'c', 'y');
insert into emp (key, name, personaldata) values(3, 'd', 'yy');
insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
--
On Mon, Nov 18, 2013 at 5:20 PM, d b <va230...@gmail.com> wrote:
> Thanks RSmith.
>
> It works.
>
> But, I am looking for single query for prepared statements. That's the
> actual struggle for me.
>
>
> On Mon, Nov 18, 2013 at 4:24 PM, d b <va230...@gmail.com> wrote:
>
>> Hi RSmith,
>>
>> Thanks. Still, I could not delete with single query.
>>
>>
>> create table if not exists emp(key integer not null, name text not null ,
>> personaldata text not null, unique(key, name));
>> insert into emp (key, name, personaldata) values(1, 'a', 'z');
>> insert into emp (key, name, personaldata) values(1, 'b', 'zz');
>> insert into emp (key, name, personaldata) values(2, 'c', 'y');
>> insert into emp (key, name, personaldata) values(3, 'd', 'yy');
>> insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
>>
>> bool delete_emp(int key, string name = "")
>> {
>> string query = ???;
>>
>> if(name.length() > 0)
>> {
>> //needs to delete specific row. by unique key.
>> }
>> else
>> {
>> //needs to delete rows belongs to key
>> }
>> }
>>
>>
>> On Mon, Nov 18, 2013 at 2:13 PM, d b <va230...@gmail.com> wrote:
>>
>>> Hi Luis,
>>>
>>> Those are parameters.
>>>
>>> This is the query after replacing with ?1 and ?2.
>>>
>>> delete from emp where key = '123' and (case when name = 'abc' is null
>>> THEN 1 else name = 'abc' end);
>>>
>>> It covered "delete from emp where key = '123' and name = 'abc';" but
>>> not other query.
>>>
>>> I tried with "select (case when name = 'abc' is null THEN 1 else name =
>>> 'abc' end) from emp;" query. It's always going to else portion when 'abc'
>>> doesn't exist in table. Any suggestions?
>>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization
Thanks RSmith.
It works.
But, I am looking for single query for prepared statements. That's the
actual struggle for me.
On Mon, Nov 18, 2013 at 4:24 PM, d b <va230...@gmail.com> wrote:
> Hi RSmith,
>
> Thanks. Still, I could not delete with single query.
>
>
> create table if not exists emp(key integer not null, name text not null ,
> personaldata text not null, unique(key, name));
> insert into emp (key, name, personaldata) values(1, 'a', 'z');
> insert into emp (key, name, personaldata) values(1, 'b', 'zz');
> insert into emp (key, name, personaldata) values(2, 'c', 'y');
> insert into emp (key, name, personaldata) values(3, 'd', 'yy');
> insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
>
> bool delete_emp(int key, string name = "")
> {
> string query = ???;
>
> if(name.length() > 0)
> {
> //needs to delete specific row. by unique key.
> }
> else
> {
> //needs to delete rows belongs to key
> }
> }
>
>
> On Mon, Nov 18, 2013 at 2:13 PM, d b <va230...@gmail.com> wrote:
>
>> Hi Luis,
>>
>> Those are parameters.
>>
>> This is the query after replacing with ?1 and ?2.
>>
>> delete from emp where key = '123' and (case when name = 'abc' is null
>> THEN 1 else name = 'abc' end);
>>
>> It covered "delete from emp where key = '123' and name = 'abc';" but not
>> other query.
>>
>> I tried with "select (case when name = 'abc' is null THEN 1 else name =
>> 'abc' end) from emp;" query. It's always going to else portion when 'abc'
>> doesn't exist in table. Any suggestions?
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization
Hi RSmith,
Thanks. Still, I could not delete with single query.
create table if not exists emp(key integer not null, name text not null ,
personaldata text not null, unique(key, name));
insert into emp (key, name, personaldata) values(1, 'a', 'z');
insert into emp (key, name, personaldata) values(1, 'b', 'zz');
insert into emp (key, name, personaldata) values(2, 'c', 'y');
insert into emp (key, name, personaldata) values(3, 'd', 'yy');
insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
bool delete_emp(int key, string name = "")
{
string query = ???;
if(name.length() > 0)
{
//needs to delete specific row. by unique key.
}
else
{
//needs to delete rows belongs to key
}
}
On Mon, Nov 18, 2013 at 2:13 PM, d b <va230...@gmail.com> wrote:
> Hi Luis,
>
> Those are parameters.
>
> This is the query after replacing with ?1 and ?2.
>
> delete from emp where key = '123' and (case when name = 'abc' is null THEN
> 1 else name = 'abc' end);
>
> It covered "delete from emp where key = '123' and name = 'abc';" but not
> other query.
>
> I tried with "select (case when name = 'abc' is null THEN 1 else name =
> 'abc' end) from emp;" query. It's always going to else portion when 'abc'
> doesn't exist in table. Any suggestions?
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization
Hi Luis, Those are parameters. This is the query after replacing with ?1 and ?2. delete from emp where key = '123' and (case when name = 'abc' is null THEN 1 else name = 'abc' end); It covered "delete from emp where key = '123' and name = 'abc';" but not other query. I tried with "select (case when name = 'abc' is null THEN 1 else name = 'abc' end) from emp;" query. It's always going to else portion when 'abc' doesn't exist in table. Any suggestions? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] query optimization
Hi, I am trying to make single query instead of below two queries. Can somebody help? 1. delete from emp where key = '123'; 2. delete from emp where key = '123' and name = 'abc'; if Key available, execute 1st query. if key and name available, execute 2nd query. Is it possible to write in single query? Regards, va ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] store image file as blob
Hi, My sqlite database module has to store images (max. 100 KB) on client machine. 1. store images on file system and have reference in database 2. store image as blob in database. Which is the best way to store these images? Any suggestions are welcome. Best Regards, va. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
