Re: [sqlite] "SQLite" Pronunciation

[Simon Slavin]

On 27 Feb 2013, at 11:39pm, Kevin Benson  wrote:

> http://www.youtube.com/watch?v=jN_YdMdjVpU

Heh.  I can't think of a more 'cannonical' source.  I've been doing it wrong, 
but I can't remember the last time I spoke the word out loud so it doesn't 
matter.

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

Re: [sqlite] "SQLite" Pronunciation

[Kevin Benson]

http://www.youtube.com/watch?v=jN_YdMdjVpU
--
   --
  --
 --Ô¿Ô--
K e V i N


On Wed, Feb 27, 2013 at 6:28 PM,  wrote:

> OK, how does one pronounce "SQLite"? "see-kwel-lite"? "ess-kyoo-lite"?
> "ess-kyoo-ell-lite"? "see-kwel-ite"? "ess-kyoo-ell-ite"? Or...?
>
> I guess, to some extent, it may depend on whether one pronounces (or
> mispronounces) "SQL" as "see-kwel" or as "ess-kyoo-ell".
>
> I think I read someplace that the author of SQLite pronounced it as
> "ess-kyoo-ell-ite". (?) I couldn't find any pronunciation mentioned on
> sqlite.org.
>
> Is there a consensus here? (Yes, I know that pronunciation matters little
> on a mailing list.)
>
> Gee, "see-plus-plus", "emm-eff-see", and "boost" (etc.) are all a lot
> easier (to pronounce, anyway - ).
>
> Fred
>
> ___
> 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] "SQLite" Pronunciation

[fred]

OK, how does one pronounce "SQLite"? "see-kwel-lite"? "ess-kyoo-lite"?
"ess-kyoo-ell-lite"? "see-kwel-ite"? "ess-kyoo-ell-ite"? Or...?

I guess, to some extent, it may depend on whether one pronounces (or
mispronounces) "SQL" as "see-kwel" or as "ess-kyoo-ell".

I think I read someplace that the author of SQLite pronounced it as
"ess-kyoo-ell-ite". (?) I couldn't find any pronunciation mentioned on
sqlite.org.

Is there a consensus here? (Yes, I know that pronunciation matters little
on a mailing list.)

Gee, "see-plus-plus", "emm-eff-see", and "boost" (etc.) are all a lot
easier (to pronounce, anyway - ).

Fred

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

Re: [sqlite] Sqlite on windows ce 5.0 Emulator

[Joe Mistachkin]

Anand Shah wrote:
>
> I am able to run application successfully developed with vs2005 .net CF
2.0
> on my local windows embedded ce 6.0 machine. I am trying to deploy this
> application to windows emulator ce 5.0 as a device application and it also
> gets deployed successfully but when any event calls sqlite dll it is
raising
> errors. 
>

What errors does it raise?

> 
> I am using SQLite-1.0.66.0-binaries.
> 

Have you tried using the latest version?  It is available from:

http://system.data.sqlite.org/downloads/1.0.84.0/sqlite-netFx35-binary-Pocke
tPC-2008-1.0.84.0.zip

--
Joe Mistachkin

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

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

[Petite Abeille]

On Feb 27, 2013, at 2:53 PM, James K. Lowden  wrote:

> On Mon, 25 Feb 2013 23:54:23 +0100
> anydacdev anydacdev  wrote:
> 
>> I was wondering what is SQLite's equivalent to:
>> 
>> MERGE INTO x TGT
>> USING (SELECT NAME, KEY FROM y) SRC
>> ON (TGT.key = SRC.key)
>> WHEN MATCHED THEN
>>  UPDATE SET TGT.NAME = NAME
>> WHEN NOT MATCHED THEN
>>  INSERT (TGT.NAME) VALUES (SRC.NAME)
> 
> begin transaction;
> update tgt
> set name = (select name from src where tgt.key = src.key)
> where exists (
>   select 1 from src
>   where src.key = tgt.key
> );
> -- check for error
> insert into tgt (name)
> select name from src
> where not exists (
>   select 1 from tgt
>   where tgt.key = src.key
> );
> -- check for error
> commit transaction;
> 
> It's only close, not equivalent, because MERGE is atomic: here src and
> tgt could change between UPDATE and INSERT.  That you'll have to deal
> with using timestamps or some form of advisory locking.  


As SQLite can only have one writer at the time, perhaps a 'begin immediate 
transaction;' will deal with that.

Regarding the 'update' part, I'm not sure how unwieldy  this will grow when 
introducing more columns, and more feature, such as the ability to update only 
rows which have effective changes.


So, all in all, one may end up with the following:

--8<--

begin immediate transaction;

-- assume 'source' is populated somehow, somewhere, before hand… 

update  target
set value1 = ( select value1 from source where source.key = target.key ),
value2 = ( select value2 from source where source.key = target.key ),
value3 = ( select value3 from source where source.key = target.key ),
value4 = ( select value4 from source where source.key = target.key )
where   exists
(
  select  1
  fromsource
  where   source.key = target.key
  and (
coalesce( source.value1, '-' ) != coalesce( target.value1, 
, '-' ) or
coalesce( source.value2, '-' ) != coalesce( target.value2, 
, '-' ) or
coalesce( source.value3, '-' ) != coalesce( target.value3, 
, '-' )  or
coalesce( source.value4, '-' ) != coalesce( target.value4, 
, '-' ) 
  )
);

insert  
intotarget
(
  key,
  value1,
  value2,
  value3,
  value4
)
select  key,
value1,
value2,
value3,
value4
fromsource

where   not exists
(
  select  1
  fromtarget
  where   target.key = source.key
);

commit transaction;

-->8--

Quite a mouthful. Not to even mention all these scalar queries and multiple 
passes over both source and target.


Compare that to an hypothetical  merge statement:

merge
intotarget
using 
(
  select  key,
  value1,
  value2,
  value3,
  value4
  fromsource
) source
on
(
  target.key = source.key
)
whenmatched then update
set target.value1 = source.value1,
target.value2 = source.value2,
target.value3 = source.value3,
target.value4 = source.value4
where   coalesce( target.value1, '-' ) != coalesce( source.value1, , '-' ) 
or  coalesce( target.value2, '-' ) != coalesce( source.value2, , '-' ) 
or  coalesce( target.value3, '-' ) != coalesce( source.value3, , '-' )  
or  coalesce( target.value4, '-' ) != coalesce( source.value4, , '-' )
whennot matched then insert
(
  key,
  value1,
  value2,
  value3,
  value4
)
values  (
  source.key,
  source.value1,
  source.value2,
  source.value3,
  source.value4
);

Another major benefit of merge is that the 'using' clause can be any query. No 
need for a preexisting source as for the update/insert scenario above.


Alternatively, this could be all turned inside out, and dealt with 
programmatically. Pseudo code:


for row in
(
  selectsource.key as skey,
source.value1 as svalue1,
source.value2 as svalue2,
source.value3 as svalue3,
source.value4 as svalue4,
target.key as tkey,
target.value1 as tvalue1,
target.value2 as tvalue2,
target.value3 as tvalue3,
target.value4 as tvalue4
  from  source

  left join target
  ontarget.key = source.key
)
loop
  if row.target is null then
insert
intotarget
(
  key,
  value1,
  value2,
  value3,
  value4
)
values  (
  row.skey,
  row.svalue1,
  row.svalue2,
  row.svalue3,
  row.svalue4
);
  elseif coalesce( row.tvalue1 ) != coalesce( row.svalue1, , '-' ) 
  or coalesce( row.tvalue2, '-' ) != 

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

[Nico Williams]

On Wed, Feb 27, 2013 at 7:53 AM, James K. Lowden
 wrote:
> begin transaction;
> update tgt
> set name = (select name from src where tgt.key = src.key)
> where exists (
> select 1 from src
> where src.key = tgt.key
> );
> -- check for error

Yes, this is what I'd call the canonical way to implement the missing
OR IGNORE (both in SQLite3 apps and in general), and combined with the
subsequent INSERT this is the canonical way to implement INSERT OR
UPDATE.

> insert into tgt (name)
> select name from src
> where not exists (
> select 1 from tgt
> where tgt.key = src.key
> );
> -- check for error
> commit transaction;
>
> It's only close, not equivalent, because MERGE is atomic: here src and
> tgt could change between UPDATE and INSERT.  That you'll have to deal
> with using timestamps or some form of advisory locking.

Who might change it?  We're in an exclusive transaction at this point
and SQLite3 allows only one writer at a time.  Another thread with the
same db handle?

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

Re: [sqlite] Tool to import tables and its data from sqlserver to sqlite

[Simon Slavin]

On 27 Feb 2013, at 3:10pm, levi  wrote:

> More precisely, I want to create a sqlite database based on a few tables and
> data from the Sqlserver database.

Good solution:

Have your SQLServer database software generate a set of SQL instructions for 
making the database and inserting data into it.  Then use the SQLite shell tool 
to create a new database using the '.read' command and this file as input:


  (look for 'shell' for your platform)

Not quite as good:

Have your SQLServer database software generate either Tab-Delimited or 
Comma-Delimited data, and import that into a SQLite database which has tables 
already existing.  You can use the same shell tool yourself to create the 
SQLite database with empty tables in it.

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

Re: [sqlite] Memory DB - Query does not return all records after Update

[Igor Tandetnik]

On 2/26/2013 9:27 AM, mike.akers wrote:

Yes I believe so. For my UPDATES, currently (things have moved around quite a
bit for me trying to solve this)

sqlite3_exec(this->hDBC_, "BEGIN EXCLUSIVE TRANSACTION;", NULL, NULL, 0);

sqlite3_prepare_v2(this->hDBC_, updateStatement, -1, >hStmt_, 0);

sqlite3_bind_blob(this->hStmt_, 1, blob, blobSize, SQLITE_STATIC);

sqlite3_step(this->hStmt_);

sqlite3_exec(this->hDBC_, "END TRANSACTION;", NULL, NULL, 0);

sqlite3_finalize(this->hStmt_);


The last two lines should go the other way round. Finalize (or at least 
reset) the statement first, then commit the transaction. The statement 
is considered "active" after step until it's reset or finalized. Your 
END TRANSACTION statement actually fails, leaving the transaction open.

--
Igor Tandetnik

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

Re: [sqlite] random, infrequent disk I/O errors

[Greg Janée]

For the record, this problem was due to a locking problem between two  
processes trying to access the same SQLite database, in which a locked  
database was being mis-reported by SQLite as a disk I/O error.   
Furthermore, the problem went away when I upgraded to 3.7.15.2.


On Feb 27, 2013, at 9:01 AM, Greg Janee wrote:




From: sqlite-users-boun...@sqlite.org [sqlite-users- 
boun...@sqlite.org] on behalf of Patrik Nilsson [nipatriknils...@gmail.com 
]

Sent: Sunday, February 10, 2013 11:11 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] random, infrequent disk I/O errors

Hello,

This letter is a help getting you started to finding your error.

There are many SQLITE_IOERR-errors, for example SQLITE_IOERR_NOMEM  
which

means out of memory.

I have noticed in my application that I sometimes get out of memory  
when

calling g_string_new() after modifying the whole table, i.e. adding a
new column.

After thinking about what it is I compiled the sqlite-code with
SQLITE_ENABLE_MEMORY_MANAGEMENT and called sqlite3_release_memory()
after doing much change to the database. I haven't got that error  
since.


Even though this change is relatively recently I have made test
stresses, but as for now it seems to work.

"The sqlite3_release_memory() interface attempts to free N bytes of  
heap

memory by deallocating non-essential memory allocations held by the
database library."

Line 23566 in sqlite.c v3.7.15.1:
/*
** This routine translates a standard POSIX errno code into something
** useful to the clients of the sqlite3 functions.  Specifically, it  
is
** intended to translate a variety of "try again" errors into  
SQLITE_BUSY

** and a variety of "please close the file descriptor NOW" errors into
** SQLITE_IOERR
**
** Errors during initialization of locks, or file system support for  
locks,

** should handle ENOLCK, ENOTSUP, EOPNOTSUPP separately.
*/

Line 1021 in sqlite.c v3.7.15.1:
#define SQLITE_IOERR_READ  (SQLITE_IOERR | (1<<8))
#define SQLITE_IOERR_SHORT_READ(SQLITE_IOERR | (2<<8))
#define SQLITE_IOERR_WRITE (SQLITE_IOERR | (3<<8))
#define SQLITE_IOERR_FSYNC (SQLITE_IOERR | (4<<8))
#define SQLITE_IOERR_DIR_FSYNC (SQLITE_IOERR | (5<<8))
#define SQLITE_IOERR_TRUNCATE  (SQLITE_IOERR | (6<<8))
#define SQLITE_IOERR_FSTAT (SQLITE_IOERR | (7<<8))
#define SQLITE_IOERR_UNLOCK(SQLITE_IOERR | (8<<8))
#define SQLITE_IOERR_RDLOCK(SQLITE_IOERR | (9<<8))
#define SQLITE_IOERR_DELETE(SQLITE_IOERR | (10<<8))
#define SQLITE_IOERR_BLOCKED   (SQLITE_IOERR | (11<<8))
#define SQLITE_IOERR_NOMEM (SQLITE_IOERR | (12<<8))
#define SQLITE_IOERR_ACCESS(SQLITE_IOERR | (13<<8))
#define SQLITE_IOERR_CHECKRESERVEDLOCK (SQLITE_IOERR | (14<<8))
#define SQLITE_IOERR_LOCK  (SQLITE_IOERR | (15<<8))
#define SQLITE_IOERR_CLOSE (SQLITE_IOERR | (16<<8))
#define SQLITE_IOERR_DIR_CLOSE (SQLITE_IOERR | (17<<8))
#define SQLITE_IOERR_SHMOPEN   (SQLITE_IOERR | (18<<8))
#define SQLITE_IOERR_SHMSIZE   (SQLITE_IOERR | (19<<8))
#define SQLITE_IOERR_SHMLOCK   (SQLITE_IOERR | (20<<8))
#define SQLITE_IOERR_SHMMAP(SQLITE_IOERR | (21<<8))
#define SQLITE_IOERR_SEEK  (SQLITE_IOERR | (22<<8))
#define SQLITE_IOERR_DELETE_NOENT  (SQLITE_IOERR | (23<<8))

Best Regards,
Patrik


On 02/10/2013 05:28 PM, Greg Janée wrote:
Hello, I'm running a web service that uses SQLite that throws a  
disk I/O

exception every once in a while, meaning once every few weeks.

Details: SQLite 3.7.0.1, being called from an Apache/Django/Python
multi-threaded application running on Solaris 10.  The database  
file is

on a local filesystem, and is <200MB.  Disk space is not an issue on
that filesystem.

Everything appears fine, and then one transaction out of the blue  
gets a
disk I/O error when doing a "BEGIN IMMEDIATE".  The database passes  
an

integrity check.  These disk I/O errors don't seem to correlate with
anything.  When one happens, there isn't a lot of activity on the  
system

(individual transactions occurring once every few seconds, say, so
threading/locking/contention wouldn't seem to be an issue), and the
transactions immediately before and after the failed transaction
succeeded just fine.  If there is any correlation, they all seem to
happen in the middle of the night.  I've checked with my sysadmin,  
and

he can't think of anything (backups, virus scans, etc.) that might be
happening at that time.

I've searched for help on this topic and have come up with  
nothing.  It

should be clear from the above that directory permissions are not the
problem.

Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm
using doesn't provide access to any more information (if there is  
any to

be had).

Thanks in advance,
-Greg

___

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Greg Janée]

For the record, this problem went away when I upgraded to the latest &  
greatest (3.7.15.2).


On Feb 27, 2013, at 8:57 AM, Greg Janee wrote:




From: sqlite-users-boun...@sqlite.org [sqlite-users- 
boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com]

Sent: Tuesday, February 26, 2013 10:32 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] locked database returning SQLITE_IOERR, not  
SQLITE_BUSY


On 02/27/2013 12:49 AM, Greg Janée wrote:

I've instrumented the SQLite source and have found that the error is
occurring at the fcntl call near the end of function unixLock (in  
SQLite

version 3.7.0.1, this is line 23592 of sqlite3.c).  The relevant code
snippet is below.  fnctl is returning -1, and errno=2 (ENOENT).   
From my
reading of the fcntl man page, it wouldn't seem to be possible for  
fcntl

to even return ENOENT.

SQLite is being used from a multi-threaded application in my case,  
and I
don't know if it's a possibility that some other thread is  
overwriting

errno.  But then, if that's even a possibility, wouldn't that seem to
preclude using SQLite in a multithreaded application at all?


I think errno is thread specific on any unix that isn't ancient.
On Solaris you have to get the compiler flags right - "-D_REENTRANT"
or something. Maybe "-mt" too.

I thought you might have been reading errno fter the sqlite3_step()
call returned.

It is weird that it is setting errno to ENOENT...

___
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] SQLite4 UPDATE performance

[Rob Turpin]

Richard,

I commented out SQLITE4_DEBUG and SQLITE4_MEMDEBUG and added -DNDEBUG=1.

That did the trick!  More like 224,000 updates per second.  Thanks for your
help

Rob

On Wed, Feb 27, 2013 at 5:47 AM, Richard Hipp  wrote:

> On Wed, Feb 27, 2013 at 3:24 AM, Rob Turpin  wrote:
>
> > I wrote up a test case to do some performance tests for the update
> > statement, and I'd thought I'd ask before probing around the code first.
> >
> > For SQLite3: 280,000 updates per second
> > For SQLite4: 290 updates per second
> >
> > So why the abysmal drop for the in memory SQLite4?
> >
>
> I ran this in the profiler and saw that the SQLite4 is spending 97.53% of
> its time (literally) in a single debugging check inside of the in-memory KV
> storage engine: assertUpPointer().  This routine is entirely option, of
> course, and is there merely to verify the integrity of the binary tree used
> for storage.  If you disable that one routine, the in-memory database is
> very fast, it seems.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Tool to import tables and its data from sqlserver to sqlite

[levi]

Hello everyone,

I have one Android App that will use the sqlite database, I would to do the
following:

I want to create the sqlite database, from sqlserver database, and download
it sqlite database that I created from the SqlServer to my Android App
Are there any tool to import the tables and its data from sqlserver database
to the sqlite database?

More precisely, I want to create a sqlite database based on a few tables and
data from the Sqlserver database.

Thanks in advanced.






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Tool-to-import-tables-and-its-data-from-sqlserver-to-sqlite-tp67325.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] Memory DB - Query does not return all records after Update

[mike.akers]

COT_EVENT_TABLE = "CREATE TABLE MAIN_TABLE(   pk char(41) primary key,\
item1 real, 
   
\
item2
char(64),   \
item3
timestamp,  \
item4
integer,\
item5
TIMESTAMP NOT NULL DEFAULT current_timestamp, \
Blob
char(1024))";

Lets assume I have inserted 10 records. pk = 1 - 10

sqlite3_exec(this->hDBC_, "BEGIN EXCLUSIVE TRANSACTION;", NULL, NULL, 0); 
sqlite3_prepare_v2(this->hDBC_, "SELECT * FROM MAIN_TABLE", -1,
>hStmt_, 0); 
sqlite3_step(this->hStmt_); 
sqlite3_exec(this->hDBC_, "END TRANSACTION;", NULL, NULL, 0); 
sqlite3_finalize(this->hStmt_);
Expect 10 Results -- Returns 10 Results
This Select statement is on a loop of roughly .5s
...

Then on an external event, another thread will aquire my mutex and attempt
an update
sqlite3_exec(this->hDBC_, "BEGIN EXCLUSIVE TRANSACTION;", NULL, NULL, 0);
sqlite3_prepare_v2(this->hDBC_, "UPDATE EVENT_TABLE SET item1 = value1,
item2 = value2, blob = ?, WHERE pk = 5", -1, >hStmt_, 0); 
sqlite3_bind_blob(this->hStmt_, 1, blob, blobSize, SQLITE_STATIC);
sqlite3_step(this->hStmt_); 
sqlite3_exec(this->hDBC_, "END TRANSACTION;", NULL, NULL, 0);
sqlite3_finalize(this->hStmt_); 
  Expect SQLITE_OK -- Returns SQLITE_OK

Now, if a select statments timer is up immediately after this update,
meaning the next thing the thread does it the first set of statements. Then
sqlite3_exec returns SQLITE_OK and there are 9 results (pk = 5 is missing).
I expect either 10 results, or for sqlite3_exec to return a SQLITE_LOCKED,
BUSY, or something other than _OK.

Additional Info
SQLITE_VERSION "3.7.14.1"
To open my connection:
sqlite3_config(SQLITE_CONFIG_URI);
sqlite3_enable_shared_cache(true);
sqlite3_open("file::memory:?cache=shared", _);
sqlite3_extended_result_codes(hDBC_, true);
I did not change SQLITE_THREADSAFE so I assuming it is still the default "1"
 




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267p67320.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] Sqlite on windows ce 5.0 Emulator

[Anand Shah]

Hello,
I am able to run application successfully developed with vs2005 .net CF 2.0
on my local windows embedded ce 6.0 machine. I am trying to deploy this
application to windows emulator ce 5.0 as a device application and it also
gets deployed successfully but when any event calls sqlite dll it is raising
errors. 

I am using SQLite-1.0.66.0-binaries.

Please advice.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Sqlite-on-windows-ce-5-0-Emulator-tp67313.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] Memory DB - Query does not return all records after Update

[mike.akers]

Yes I believe so. For my UPDATES, currently (things have moved around quite a
bit for me trying to solve this)

sqlite3_exec(this->hDBC_, "BEGIN EXCLUSIVE TRANSACTION;", NULL, NULL, 0);

sqlite3_prepare_v2(this->hDBC_, updateStatement, -1, >hStmt_, 0);

sqlite3_bind_blob(this->hStmt_, 1, blob, blobSize, SQLITE_STATIC);

sqlite3_step(this->hStmt_);

sqlite3_exec(this->hDBC_, "END TRANSACTION;", NULL, NULL, 0);

sqlite3_finalize(this->hStmt_);



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267p67292.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] like query

[Dominique Devienne]

On Wed, Feb 27, 2013 at 3:16 PM, Igor Tandetnik  wrote:

> On 2/27/2013 4:35 AM, Dominique Devienne wrote:
>
>> PS: Something else that should also be part of SQLite built-in is the
>> optimization that col LIKE 'prefix%' queries should implicitly try to use
>> an index on col.
>>
>
> http://www.sqlite.org/**optoverview.html#like_opt


Thanks for the reminder. Note though that last time I checked [1], this
didn't work for a multi-column index, even if the column involved in a
prefix-based like-where-clause is first in the index. --DD

[1]
http://stackoverflow.com/questions/11152371/how-to-improve-the-performance-of-query-with-where-and-group-by-in-sqlite/11736532#11736532
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite2010 Pro ODBC Driver

[Paul Mathieu]

Good Morning, 

I am trying the SQLite2010 Pro ODBC Driver in a connection string in
PowerBuilder 12.5. In the database painter and in a compiled app some
selects from tables are returning more than what is in the table. The county
table has 4280 records. Selecting specific columns or the whole table
returns 831,911 records. The zip table has zip_code char(5) and county
char(80) along with other columns. 

Select zip code from zip where state = 'AZ' returns  605 records which is
correct.

Select zip_code, county from zip where state = 'AZ' returns 82,011records.

Select county from zip where state = 'AZ' returns 82,011records.

Select Distinct does not change the result.

 

The SQLite3 ODBC Driver does not have this issue.

Thank you,

Paul

 

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

Re: [sqlite] [SQLite.ADO.Net] Upgrading XP to SQLite version?

[Gilles Ganault]

On Tue, 26 Feb 2013 19:45:27 -0500, Kevin Benson
 wrote:
>> Right, but while the first DLL will be found since it's now part of
>> the project (Project > Add Reference), the wiki doesn't say that this
>> doesn't take care of the other DLL.

>http://www.mail-archive.com/sqlite-users@sqlite.org/msg73039.html

Good to know, although creating an .\x86 or .\x64 doesn't seem to add
anything as compared to simply putting the two DLLs in the
application's directory.

>> Speaking of which, why is the no-bundle version (two DLLs) to be
>> prefered to the bundle version (single DLL)? It solves this issue.

I still didn't find any info on the web about what the difference is.

The wiki says: "All the "bundle" packages contain the
"System.Data.SQLite.dll" mixed-mode assembly. These packages should
only be used in cases where the assembly binary must be deployed to
the Global Assembly Cache for some reason (e.g. to support some legacy
application on customer machines). "

Anyone knows more?

Thank you.

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

Re: [sqlite] like query

[Igor Tandetnik]

On 2/27/2013 4:35 AM, Dominique Devienne wrote:

PS: Something else that should also be part of SQLite built-in is the
optimization that col LIKE 'prefix%' queries should implicitly try to use
an index on col.


http://www.sqlite.org/optoverview.html#like_opt

--
Igor Tandetnik

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

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

[James K. Lowden]

On Mon, 25 Feb 2013 23:54:23 +0100
anydacdev anydacdev  wrote:

> I was wondering what is SQLite's equivalent to:
> 
> MERGE INTO x TGT
> USING (SELECT NAME, KEY FROM y) SRC
> ON (TGT.key = SRC.key)
> WHEN MATCHED THEN
>   UPDATE SET TGT.NAME = NAME
> WHEN NOT MATCHED THEN
>   INSERT (TGT.NAME) VALUES (SRC.NAME)

begin transaction;
update tgt
set name = (select name from src where tgt.key = src.key)
where exists (
select 1 from src
where src.key = tgt.key
);
-- check for error
insert into tgt (name)
select name from src
where not exists (
select 1 from tgt
where tgt.key = src.key
);
-- check for error
commit transaction;

It's only close, not equivalent, because MERGE is atomic: here src and
tgt could change between UPDATE and INSERT.  That you'll have to deal
with using timestamps or some form of advisory locking.  

HTH.  

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

Re: [sqlite] SQLite4 UPDATE performance

[Richard Hipp]

On Wed, Feb 27, 2013 at 3:24 AM, Rob Turpin  wrote:

> I wrote up a test case to do some performance tests for the update
> statement, and I'd thought I'd ask before probing around the code first.
>
> For SQLite3: 280,000 updates per second
> For SQLite4: 290 updates per second
>
> So why the abysmal drop for the in memory SQLite4?
>

I ran this in the profiler and saw that the SQLite4 is spending 97.53% of
its time (literally) in a single debugging check inside of the in-memory KV
storage engine: assertUpPointer().  This routine is entirely option, of
course, and is there merely to verify the integrity of the binary tree used
for storage.  If you disable that one routine, the in-memory database is
very fast, it seems.


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

Re: [sqlite] like query

[Dominique Devienne]

On Wed, Feb 27, 2013 at 11:23 AM, Clemens Ladisch wrote:

> Dominique Devienne wrote:
> > My $0.02 is that such a chr() function could/should be built-in to
> SQLite.
>
> Apparently, drh has a time machine:
> http://www.sqlite.org/cgi/src/info/209b21085b
>

Indeed! Spooky :) --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Clemens Ladisch]

Dominique Devienne wrote:
> My $0.02 is that such a chr() function could/should be built-in to SQLite.

Apparently, drh has a time machine:
http://www.sqlite.org/cgi/src/info/209b21085b


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

Re: [sqlite] like query

[Dominique Devienne]

On Tue, Feb 26, 2013 at 2:31 PM, Clemens Ladisch  wrote:

>   ... 'somedata/' || CAST(x'F48FBFBF' AS TEXT)
>

Great trick! But it hardly qualifies as user friendly though, no?

For our app, I added a chr() SQL function that take an arbitrary number of
integers and UTF-8 encodes them:

register_function(-1, "chr", codepoint_to_utf8);

so the above becomes

... 'somedata/' || chr(1114111)

Of course, the fact that it's a decimal code-point number is not ideal
since less expressive than

... 'somedata/' || chr(0x10)

but hexa-literals are not supported in SQL it seems (I tried just SQLite
and Oracle).

My $0.02 is that such a chr() function could/should be built-in to SQLite.
--DD

PS: Something else that should also be part of SQLite built-in is the
optimization that col LIKE 'prefix%' queries should implicitly try to use
an index on col. I suspect it may be more difficult than I expect because
of collation, but absent custom collations, I wish that optimization was
available.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite4 UPDATE performance

[Rob Turpin]

I wrote up a test case to do some performance tests for the update
statement, and I'd thought I'd ask before probing around the code first.

I did some comparisons with SQLite3.

The update statement is like this:

update pk_sk set sk=? where pk=?

pk being the primary key.

Using the command line interpreter and running an explain query plan I get:

SQLite3:
0|0|0|SEARCH TABLE pk_sk USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

SQLite4:
0|0|0|SEARCH TABLE pk_sk USING PRIMARY KEY (pk=?) (~1 rows)

Don't know if that provides anything, thought I'd send it.

So a general synopsis of the tests.  I'm doing random updates when there
are 100,000 rows in a table.

For SQLite3 when using a DB on disk I'm getting around 41,000 updates per
second
For SQLite4 when using a DB on disk I'm getting around 9,200 updates per
second

For the moment I'm not too worried about this, but it's really weird when I
run tests with an in memory DB

For SQLite3: 280,000 updates per second
For SQLite4: 290 updates per second

So why the abysmal drop for the in memory SQLite4?  I'm assuming maybe some
tracer/debug code I know nothing about.  Thought I'd ask about this before
I started getting lost in rooting around code and debugging my two versions
of tests looking for something.  If someone can give a little info. to give
me a better focus on where to look, that would be great.

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