[sqlite] Unicode support for Sqlite?

[Sreedhar.a]

Hi,
 
Does Sqlite support unicode?
I have seen that it supports utf-8 and utf-16.
I want to know whether it supports unicode character formats.
 
Thanks and Best Regards,
A.Sreedhar.
 
 

[sqlite] SQLite gives Access Violation in Windows

[CAVALO SCHMIDT]

Salutations,

I'm programing in C, new to SQLite, in Windows, with the Pelles C Compiler
for Windows.
When I create a Console project and put, for example, the following commands
(an SQLite connection with callback function):

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  // Show "column 1 -> column 2" in a MessageBox
  char output[256];
  sprintf(output, "%s -> %s", argv[0], argv[1]);
  MessageBox(0, output, "", 0);
  return 0;
}

sqlite3 *db;
sqlite3_open("dict.db", );
char *zErrMsg;
int rc;
rc = sqlite3_exec(db, "select * from a where a = 'key1'", callback, 0,
);
rc = sqlite3_exec(db, "select * from a where a = 'key2'", callback, 0,
);

It works well. BUT, when I try to put this code in a Win32 application, like
inside a WinMain procedure, for example, a strange behavior happens:
everytime sqlite3_exec finds exactly more than 3 records in the database, it
gives an "Access Violation" error. Pelles C's debugger gives "Exception:
Access Violation", apparently in NTDLL.DLL. It happens with any database of
any size, as long as SELECT returns more than 3 results. We tested it in 2
computers Windows XP Pro (one of them 2.4 GHz and 1GB RAM, and the
other 1.1GHz and 384 MB RAM), and, in both of them, the error happens
every time
SELECT finds more than 3 records (so, I could never have noticed it if I had
tested it only with SELECTs that return 3 or less results); we tried to
increase Stack Reserve size, but no difference. Important to notice is the
fact that it works fine in a Console application, and also in sqlite3.exe it
works fine.
Any ideas? Thank you in advance.

[sqlite] SQLite Consortium Launches

[drh]

   SQLite Consortium Launches With Mozilla And
   Symbian As Charter Members

Charlotte, North Carolina - December 12, 2007 - The SQLite
Consortium, a new membership association dedicated to maintaining
SQLite as a fully open and independent product, was formally
announced today. Mozilla and Symbian Ltd. have joined the SQLite
Consortium as charter members.

SQLite is a compact, high efficiency, high reliability, embeddable
SQL database engine. The source code to SQLite is in the public
domain and is available with no associated fees. SQLite is the
most deployed SQL database engine in the world and is currently
used in a wide range of commercial software products and
electronic devices from leading manufacturers. SQLite is found
today in many mobile phones, MP3 players, set-top boxes, and PCs.

The mission of the SQLite Consortium is to continue developing and
enhancing SQLite as a product that anyone may use without paying
royalties or licensing fees. Members of the SQLite Consortium
provide funding to enable this mission and in return receive
enterprise-level technical support. Technical control and
direction of SQLite remains entirely with the SQLite developers.

Mozilla, developer of the popular open-source Firefox web browser,
and Symbian, the market-leading open operating system for advanced
data-enabled smartphones, both deploy the SQLite database engine
in their products. As charter members of the Consortium, Mozilla
and Symbian are ensuring the development and support of SQLite as
a freely accessible and public domain software asset.

"SQLite has become a popular embedded database because it is
lightweight, fast, and open source," said Michael Schroepfer, Vice
President of Engineering, Mozilla. "As a charter member of the
SQLite Consortium, Mozilla is excited to help ensure SQLite
remains a vibrant and open technology, in line with our mission to
promote choice and innovation on the Internet."

"The SQLite Consortium will help set the standards for database
management which are essential in smartphone functionality and
will also help create a pool of developers, highly-skilled in
SQLite for future mobile phone development," said Bruce Carney,
Director, Developer Programmes & Services, Symbian. "Our
involvement with the SQL Consortium not only demonstrates
Symbian's commitment to open standards in the industry, but as
mobile phones become increasingly powerful and smartphones become
increasingly popular, we are focused on ensuring that desktop
developers, who move to the mobile space, have the easiest and
most productive experience possible."

Additional information is available at the SQLite website, 
http://www.sqlite.org/.



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

RE: [sqlite] Unicode support for Sqlite?

[Sreedhar.a]

 
Hi,

I am using the sqlite to store the metadata of audio files.
Is it possible to store the metadata in unicode character format in sqlite.

Best Regards,
A.Sreedhar.
 

-Original Message-
From: Trevor Talbot [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 12, 2007 4:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unicode support for Sqlite?

On 12/12/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:

> Does Sqlite support unicode?
> I have seen that it supports utf-8 and utf-16.
> I want to know whether it supports unicode character formats.

Unicode is a very large and complex topic, so that question is way too vague
to answer. Can you provide an example of what you're looking for?


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] SQLite gives Access Violation in Windows

[Trevor Talbot]

On 12/12/07, CAVALO SCHMIDT <[EMAIL PROTECTED]> wrote:

> I'm programing in C, new to SQLite, in Windows, with the Pelles C Compiler
> for Windows.

> It works well. BUT, when I try to put this code in a Win32 application, like
> inside a WinMain procedure, for example, a strange behavior happens:
> everytime sqlite3_exec finds exactly more than 3 records in the database, it
> gives an "Access Violation" error. Pelles C's debugger gives "Exception:
> Access Violation", apparently in NTDLL.DLL. It happens with any database of
> any size, as long as SELECT returns more than 3 results. We tested it in 2
> computers Windows XP Pro (one of them 2.4 GHz and 1GB RAM, and the
> other 1.1GHz and 384 MB RAM), and, in both of them, the error happens
> every time
> SELECT finds more than 3 records (so, I could never have noticed it if I had
> tested it only with SELECTs that return 3 or less results); we tried to
> increase Stack Reserve size, but no difference. Important to notice is the
> fact that it works fine in a Console application, and also in sqlite3.exe it
> works fine.

You'll need to get more information out of the debugger, like a stack
trace. Alternatively, scatter MessageBox() calls through your code to
find out where it is when it runs into trouble.

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

Re: [sqlite] Unicode support for Sqlite?

[Trevor Talbot]

On 12/12/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:

> Does Sqlite support unicode?
> I have seen that it supports utf-8 and utf-16.
> I want to know whether it supports unicode character formats.

Unicode is a very large and complex topic, so that question is way too
vague to answer. Can you provide an example of what you're looking
for?

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

Re: [sqlite] Unicode support for Sqlite?

[Trevor Talbot]

On 12/12/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:

> I am using the sqlite to store the metadata of audio files.
> Is it possible to store the metadata in unicode character format in sqlite.

Yes; SQLite assumes all TEXT type data in the database is Unicode. You
can work with it in UTF-8 with the *_text() APIs, or UTF-16 using the
*_text16() calls. SQLite will convert between the two encodings as
necessary.

The sqlite3 shell assumes UTF-8, but it depends on the platform's
console to actually use UTF-8 when talking to it, so it may be
difficult to properly test with it.

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

Re: [sqlite] select date using variables in Python

[Mag. Wilhelm Braun]

hi C M,

I'm by no means an expert but what I do in similar cases is: I prepare 
the variables beforehand - actually I prepare the whole SQL statement 
beforehand and do normally not use ?.  except by executemany.


date=date("now","+1 day")
sqlcu.execute ("SELECT string FROM test WHERE d >=?",(date,) )



or something like that should also work:

sqlcu.execute ("SELECT string FROM test WHERE d >=?", (date("now", "+1 
day"),) )


I actually use never the date but convert everything to utc timestamps 
in an extra column.


regards W.Braun

C M wrote:

I'm new to SQLite and can't figure out the right way to write this. I want
to select a range of dates, let's say anything beyond tomorrow So in my
table called test I want to select the column called string based on the
date being tomorrow or later...

This statement (from the sql wiki about dates) in my Python code works:

cur.execute('SELECT string FROM test WHERE d >= date("now","+1 day")')

However, I'd like to make it flexible, so that a user can put in an amount
of days forward or backward and the query will use that--basically I want
the user to be able to select the date range over the data in the table.  I
tried something like:

amount = "1"  #just to try it, later this will refer to a user-chosen
variable
cur.execute ('SELECT string FROM test WHERE d >= date("now", "+",?,"
day")',amount)

But of course that's not right and it doesn't work.  What is the right
syntax in this case to use the ? to stand for the 1 in the original "+1 day"
portion?

Or am I barking up the wrong tree with this approach?  Ultimately I want to
make it totally generalizable, so that users can select whatever range of
dates they want, and so I thought I needed a way to sub in the variable of
#of days--just not sure how.

Any help is appreciated.

  



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

Re: [sqlite] SQLite Consortium Launches

[Trevor Talbot]

Very cool. Congratulations on getting this organized.

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

RE: [sqlite] Unicode support for Sqlite?

[Sreedhar.a]

 
Thankyou all for the quick replies.

Best Regards,
A.Sreedhar.
 

-Original Message-
From: Trevor Talbot [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 12, 2007 5:08 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unicode support for Sqlite?

On 12/12/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:

> I am using the sqlite to store the metadata of audio files.
> Is it possible to store the metadata in unicode character format in
sqlite.

Yes; SQLite assumes all TEXT type data in the database is Unicode. You can
work with it in UTF-8 with the *_text() APIs, or UTF-16 using the
*_text16() calls. SQLite will convert between the two encodings as
necessary.

The sqlite3 shell assumes UTF-8, but it depends on the platform's console to
actually use UTF-8 when talking to it, so it may be difficult to properly
test with it.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] Unicode support for Sqlite?

[Daniel Önnerby]

utf-8 and utf-16 ARE unicode formats. But there are some things that 
sqlite does not handle without the ICU extension.

The ICU extension extends SQLite with the following functionallity:
   1.1  SQL Scalars upper() and lower()
   1.2  Unicode Aware LIKE Operator
   1.3  ICU Collation Sequences
   1.4  SQL REGEXP Operator

Download the SQLite source and have a look in the ext/icu directory

Sreedhar.a wrote:

Hi,
 
Does Sqlite support unicode?

I have seen that it supports utf-8 and utf-16.
I want to know whether it supports unicode character formats.
 
Thanks and Best Regards,

A.Sreedhar.
 
 

  


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

Re: [sqlite] how to cast utf16 text to int?

[Trevor Talbot]

On 12/11/07, Maxim V. Shiyanovsky <[EMAIL PROTECTED]> wrote:

> One more question - is there any way to insert utf-16 string from
> sqlite3.exe?
> As I understand I can't use syntax like this:
> insert into t values (X'31003700', 1);

No; the sqlite3 shell deals only in UTF-8, which is hard to make work
on Windows because the console doesn't have proper support for it.

However, it does perform casts from BLOB to TEXT by treating the blob
as a set of bytes in UTF-8 form, so that could be used as an escape
syntax:

insert into t values(X'3137', 1);

But since these characters are in the ASCII range anyway, you might as
well just enter them as plain text:

insert into t values('17', 1);

(That also explains the "first digit" trouble you were having: null
bytes are not allowed in strings, so the final TEXT form only
contained one character. It was a happy coincidence that you were
using characters from the ASCII range, and UTF-16 in little endian
form as the input bytes.)

> As I wrote in the first letter I need cast from utf-16 text to int.
> In other words I need select like this:
> Select * from t, d where cast(t.value as int) = d.id

With the above in mind, this should work now.

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

Re: [sqlite] SQLite gives Access Violation in Windows

[CAVALO SCHMIDT]

Thank you for the answer. Actually, I've already scattered MessageBox()
calls through the code, and I've found out exactly where the error happens.
For example, in a database where searching for text "key1" (column a)
returns "value1", "value2", "value3", and "value4" in column b, the callback
fnuction of that code would be called THREE times, returning thus three
message boxes:
 - key1 -> value1
 - key1 -> value2
 - key1 -> value3
And, in the fourth call to the callback function in this same sqlite3_exec
call, it gives "Access Violation", and never gets to reach the callback
function for the fourth time. But, if there were only value1, value2 and
value3, it would work fine. Actually, if we called, several times,
sqlite3_exec() for an SELECT that return 3 or less calls to the callback
function, it would work fine. Thus, it runs into trouble apparently when it
is about to call the callback function for the fourth time in a same
sqlite3_exec.
Call stack just after the error happens (I'm not very sure of what it means,
because I'm a beginner):
KERNEL32.dll: GetCurrentDirectoryW( ) + 44
test.exe: 00401265
test.exe: WinMain( ) + 71
sqlite3.dll: sqlite3_exec( ) + 154
sqlite3.dll: sqlite3_column_text( ) + 1A
sqlite3.dll: sqlite3_data_count( ) + AC
ntdll.dll: RtlEnterCriticalSection( ) + B
I'm not very sure if it is all the information that you need.
Thank you in advance.
2007/12/12, Trevor Talbot <[EMAIL PROTECTED]>:

> On 12/12/07, CAVALO SCHMIDT <[EMAIL PROTECTED]> wrote:
>
> > I'm programing in C, new to SQLite, in Windows, with the Pelles C
> Compiler
> > for Windows.
>
> > It works well. BUT, when I try to put this code in a Win32 application,
> like
> > inside a WinMain procedure, for example, a strange behavior happens:
> > everytime sqlite3_exec finds exactly more than 3 records in the
> database, it
> > gives an "Access Violation" error. Pelles C's debugger gives "Exception:
> > Access Violation", apparently in NTDLL.DLL. It happens with any database
> of
> > any size, as long as SELECT returns more than 3 results. We tested it in
> 2
> > computers Windows XP Pro (one of them 2.4 GHz and 1GB RAM, and the
> > other 1.1GHz and 384 MB RAM), and, in both of them, the error happens
> > every time
> > SELECT finds more than 3 records (so, I could never have noticed it if I
> had
> > tested it only with SELECTs that return 3 or less results); we tried to
> > increase Stack Reserve size, but no difference. Important to notice is
> the
> > fact that it works fine in a Console application, and also in
> sqlite3.exe it
> > works fine.
>
> You'll need to get more information out of the debugger, like a stack
> trace. Alternatively, scatter MessageBox() calls through your code to
> find out where it is when it runs into trouble.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] SQLite gives Access Violation in Windows

[Trevor Talbot]

On 12/12/07, CAVALO SCHMIDT <[EMAIL PROTECTED]> wrote:

> For example, in a database where searching for text "key1" (column a)
> returns "value1", "value2", "value3", and "value4" in column b, the callback
> fnuction of that code would be called THREE times, returning thus three
> message boxes:
>  - key1 -> value1
>  - key1 -> value2
>  - key1 -> value3
> And, in the fourth call to the callback function in this same sqlite3_exec
> call, it gives "Access Violation", and never gets to reach the callback
> function for the fourth time. But, if there were only value1, value2 and
> value3, it would work fine. Actually, if we called, several times,
> sqlite3_exec() for an SELECT that return 3 or less calls to the callback
> function, it would work fine. Thus, it runs into trouble apparently when it
> is about to call the callback function for the fourth time in a same
> sqlite3_exec.

> test.exe: WinMain( ) + 71
> sqlite3.dll: sqlite3_exec( ) + 154
> sqlite3.dll: sqlite3_column_text( ) + 1A
> sqlite3.dll: sqlite3_data_count( ) + AC
> ntdll.dll: RtlEnterCriticalSection( ) + B

Hmm, looks like a fault within SQLite's internal mutex handling. What
version of sqlite is this, and did you compile it yourself? I'm
wondering if it's not a compiler-related bug.

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

Re: [sqlite] SQLite gives Access Violation in Windows

[drh]

"Trevor Talbot" <[EMAIL PROTECTED]> wrote:
> 
> > test.exe: WinMain( ) + 71
> > sqlite3.dll: sqlite3_exec( ) + 154
> > sqlite3.dll: sqlite3_column_text( ) + 1A
> > sqlite3.dll: sqlite3_data_count( ) + AC
> > ntdll.dll: RtlEnterCriticalSection( ) + B
> 
> Hmm, looks like a fault within SQLite's internal mutex handling. What
> version of sqlite is this, and did you compile it yourself? I'm
> wondering if it's not a compiler-related bug.
> 

sqlite3_data_count() never touches a mutex.  sqlite3_data_count()
consists of 3 lines of code that extracts a value from the structure 
that is passed in as its only parameter.

Furthermore, sqlite3_column_text() does not call sqlite3_data_count(),
either directly or through intermediate subroutines.

So I would be very suspicious about drawing conclusions from the
stack trace above.

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


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

[sqlite] Re: select date using variables in Python

[Igor Tandetnik]

C M <[EMAIL PROTECTED]> wrote:

cur.execute('SELECT string FROM test WHERE d >= date("now","+1 day")')

However, I'd like to make it flexible, so that a user can put in an
amount of days forward or backward and the query will use
that--basically I want the user to be able to select the date range
over the data in the table.  I tried something like:

amount = "1"  #just to try it, later this will refer to a user-chosen
variable
cur.execute ('SELECT string FROM test WHERE d >= date("now", "+",?,"
day")',amount)

But of course that's not right and it doesn't work.  What is the right
syntax in this case to use the ? to stand for the 1 in the original
"+1 day" portion?


I don't know Python well enough, but the SQL statement you want would 
look like this:


SELECT string FROM test WHERE d >= date('now', ? || ' days');

|| is the string concatenation operator in SQL.

Igor Tandetnik 



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

Re: [sqlite] SQLite gives Access Violation in Windows

[CAVALO SCHMIDT]

It is SQLite 3.4.2, and I downloaded the ready binary, the DLL and sqlite3.h;
I didn't compile anything. But the sqlite3.lib didn't come ready; it came as
sqlite3.def together with the DLL, then I made it into a sqlite3.lib by
using the LIB.EXE utility of Visual Studio 6.0 -> LIB.EXE /DEF:sqlite3.def.
Thank you in advance.


2007/12/12, Trevor Talbot <[EMAIL PROTECTED]>:
>
> On 12/12/07, CAVALO SCHMIDT <[EMAIL PROTECTED]> wrote:
>
> > For example, in a database where searching for text "key1" (column a)
> > returns "value1", "value2", "value3", and "value4" in column b, the
> callback
> > fnuction of that code would be called THREE times, returning thus three
> > message boxes:
> >  - key1 -> value1
> >  - key1 -> value2
> >  - key1 -> value3
> > And, in the fourth call to the callback function in this same
> sqlite3_exec
> > call, it gives "Access Violation", and never gets to reach the callback
> > function for the fourth time. But, if there were only value1, value2 and
> > value3, it would work fine. Actually, if we called, several times,
> > sqlite3_exec() for an SELECT that return 3 or less calls to the callback
> > function, it would work fine. Thus, it runs into trouble apparently when
> it
> > is about to call the callback function for the fourth time in a same
> > sqlite3_exec.
>
> > test.exe: WinMain( ) + 71
> > sqlite3.dll: sqlite3_exec( ) + 154
> > sqlite3.dll: sqlite3_column_text( ) + 1A
> > sqlite3.dll: sqlite3_data_count( ) + AC
> > ntdll.dll: RtlEnterCriticalSection( ) + B
>
> Hmm, looks like a fault within SQLite's internal mutex handling. What
> version of sqlite is this, and did you compile it yourself? I'm
> wondering if it's not a compiler-related bug.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] SQLite gives Access Violation in Windows

[Trevor Talbot]

On 12/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Trevor Talbot" <[EMAIL PROTECTED]> wrote:

> > > test.exe: WinMain( ) + 71
> > > sqlite3.dll: sqlite3_exec( ) + 154
> > > sqlite3.dll: sqlite3_column_text( ) + 1A
> > > sqlite3.dll: sqlite3_data_count( ) + AC
> > > ntdll.dll: RtlEnterCriticalSection( ) + B

> > Hmm, looks like a fault within SQLite's internal mutex handling. What
> > version of sqlite is this, and did you compile it yourself? I'm
> > wondering if it's not a compiler-related bug.

> sqlite3_data_count() never touches a mutex.  sqlite3_data_count()
> consists of 3 lines of code that extracts a value from the structure
> that is passed in as its only parameter.
>
> Furthermore, sqlite3_column_text() does not call sqlite3_data_count(),
> either directly or through intermediate subroutines.
>
> So I would be very suspicious about drawing conclusions from the
> stack trace above.

Most of the Windows debugging tools will, in the absence of full
symbols, choose the closest public/exported symbol and print an
instruction offset from it. Not many optimizers are aggressive about
reordering functions, so usually code gets laid out in the order it
was written. That puts the mutex-related call not very far after
sqlite3_data_count(), like perhaps columnMem().

Still quite a bit of guesswork involved, but that's pretty much the
only way it's going to get from sqlite into RtlEnterCriticalSection(),
and it'll do as a starting point.

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

Re: [sqlite] SQLite gives Access Violation in Windows

[CAVALO SCHMIDT]

Thank you for the response. So, how can I fix it? Can it have anything to do
with sqlite3.lib?
Is it a bug? Thank you in advance.


2007/12/12, Trevor Talbot <[EMAIL PROTECTED]>:
>
> On 12/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > "Trevor Talbot" <[EMAIL PROTECTED]> wrote:
>
> > > > test.exe: WinMain( ) + 71
> > > > sqlite3.dll: sqlite3_exec( ) + 154
> > > > sqlite3.dll: sqlite3_column_text( ) + 1A
> > > > sqlite3.dll: sqlite3_data_count( ) + AC
> > > > ntdll.dll: RtlEnterCriticalSection( ) + B
>
> > > Hmm, looks like a fault within SQLite's internal mutex handling. What
> > > version of sqlite is this, and did you compile it yourself? I'm
> > > wondering if it's not a compiler-related bug.
>
> > sqlite3_data_count() never touches a mutex.  sqlite3_data_count()
> > consists of 3 lines of code that extracts a value from the structure
> > that is passed in as its only parameter.
> >
> > Furthermore, sqlite3_column_text() does not call sqlite3_data_count(),
> > either directly or through intermediate subroutines.
> >
> > So I would be very suspicious about drawing conclusions from the
> > stack trace above.
>
> Most of the Windows debugging tools will, in the absence of full
> symbols, choose the closest public/exported symbol and print an
> instruction offset from it. Not many optimizers are aggressive about
> reordering functions, so usually code gets laid out in the order it
> was written. That puts the mutex-related call not very far after
> sqlite3_data_count(), like perhaps columnMem().
>
> Still quite a bit of guesswork involved, but that's pretty much the
> only way it's going to get from sqlite into RtlEnterCriticalSection(),
> and it'll do as a starting point.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] SQLite gives Access Violation in Windows

[Trevor Talbot]

On 12/12/07, CAVALO SCHMIDT <[EMAIL PROTECTED]> wrote:

> It is SQLite 3.4.2, and I downloaded the ready binary, the DLL and sqlite3.h;
> I didn't compile anything. But the sqlite3.lib didn't come ready; it came as
> sqlite3.def together with the DLL, then I made it into a sqlite3.lib by
> using the LIB.EXE utility of Visual Studio 6.0 -> LIB.EXE /DEF:sqlite3.def.
> Thank you in advance.

Ok. It sounds like there's something weird about the environment in
the GUI version of your app. Pelles C is new to me, so it's a bit of a
wild card too.

If you're able to provide the source to the Win32 app, and the
database you're testing with, I can dig into it myself, although it'll
take me a little while.

One thing you can try now is getting the 3.5.3 binary, and replace the
DLL your app uses with it. It should just be a drop-in replacement,
without needing to build the import library and link your app again. I
still want to find out what the problem is, but it would be good to
know if this changes things.

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

[sqlite] How to get record count

[Tom Parke]

How can I get a count of the number of records in a table?
Sqlite3_get_table() might work, but I only need the count, not the
record set.

Thanks,

Tom

[sqlite] Another novice question

[Tom Parke]

I created a table with column 1 as primary key.  So where are the index
records?  I thought I would find them in the sqlite_stat1 table but
after inserting records in my table, there are no records in the
sqlite_stat1 table.

 

And, do primary keys need to be the first consecutive n fields?  I
couldn't have a table with only column 3 as primary key could I?

 

Tom

Re: [sqlite] How to get record count

[Marco Bambini]

SELECT count(*) FROM myTable;

---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



On Dec 12, 2007, at 2:55 PM, Tom Parke wrote:


How can I get a count of the number of records in a table?
Sqlite3_get_table() might work, but I only need the count, not the
record set.

Thanks,

Tom




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

[sqlite] Re: Another novice question

[Igor Tandetnik]

Tom Parke <[EMAIL PROTECTED]> wrote:

I created a table with column 1 as primary key.  So where are the
index records?


In the database, of course. 


I thought I would find them in the sqlite_stat1 table
but after inserting records in my table, there are no records in the
sqlite_stat1 table.


This table stores statistics, not indexes. See ANALYZE statement.


And, do primary keys need to be the first consecutive n fields?


No.


 I
couldn't have a table with only column 3 as primary key could I?


You could.

Igor Tandetnik

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

[sqlite] Re: How to get record count

[Igor Tandetnik]

Tom Parke <[EMAIL PROTECTED]> wrote:

How can I get a count of the number of records in a table?


select count(*) from tableName;

Igor Tandetnik

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

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

[Joe Wilson]

It appears that Postgres, DB2 and SQL Server CE have issues with 
certain types of alias expresssions in GROUP BY, while MySQL does 
not.

Postgres will allow column aliases in GROUP BY - even aliases overriding
table column names - as long as every table column component of that 
alias' expression is also explicitly individually specified in the 
GROUP BY clause (of course this would change the result of the query). 
DB2 and SQL Server CE had similar GROUP BY errors, so I'm speculating 
that they may the same GROUP BY resolution behavior as postgres.

I guess the superset behavior that would allow the running of queries
from any of the databases mentioned would be to allow aliases in 
GROUP BY, with aliases having precedence over table column names.

 create table t1(a INT, b INT, c INT);
 insert into t1 values(1, 2, 4);
 insert into t1 values(2, -1000, 5);

postgres=> select a, a+b AS c from t1 group by c, a order by c, a;
ERROR:  column "t1.b" must appear in the GROUP BY clause or be used in an
aggregate function

"b" added to SELECT list and GROUP BY (changing the meaning of the query):

postgres=> select a, a+b AS c, b from t1 group by c, a, b order by c, a, b;
 a |  c   |   b
---+--+---
 2 | -998 | -1000
 1 |3 | 2

Verify that an unambiguous alias "g" returns the same results:

postgres=> select a, a+b AS g, b from t1 group by g, a, b order by g, a, b;
 a |  g   |   b
---+--+---
 2 | -998 | -1000
 1 |3 | 2

Remove mention of column b from the SELECT list:

postgres=> select a, a+b AS c from t1 group by c, a, b order by c, a, b;
 a |  c
---+--
 2 | -998
 1 |3

Do not overload c, use unambiguous alias g:

postgres=> select a, a+b AS g from t1 group by g, a, b order by g, a, b;
 a |  g
---+--
 2 | -998
 1 |3

> mysql>   select a, a+b AS c from t1 group by c, a order by c, a;
> +--+--+
> | a| c|
> +--+--+
> |2 | -998 |
> |1 |3 |
> +--+--+

DB2:
> select a, a+b AS c from t1 group by c, a order by c, a
> SQL0119N  An expression starting with "B" specified in a SELECT clause, HAVING
> clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in
> a SELECT clause, HAVING clause, or ORDER BY clause with a column function and
> no GROUP BY clause is specified.  SQLSTATE=42803

SQL Server CE:
> select a, a+b AS c from t1 group by c, a order by c, a;
> SQL Server Compact Edition Version 3.5 returns
> Unhandled Exception: In aggregate and grouping expressions, the SELECT
> clause can contain only aggregates and grouping expressions. [ Select
> clause = ,b ]



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


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

[sqlite] Re: .dump/.load not workin in 3.4.0 or later for "large" rows?

[A. Pagaltzis]

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-12-11 20:15]:
> But, as has been pointed out, you can work around it using
> a compile-time switch:
>
>gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o sqlite3
>
> I should probably modify the makefile to do this
> automatically...

Maybe it would be worthwhile to ifdef the checks so that one
can set SQLITE_MAX_SQL_LENGTH to 0 to get the old behaviour
back, and then make that the default? Then people like the
Google Gears folks can compile with an appropriate limit and
no one else is affected.

Regards,
-- 
Aristotle Pagaltzis // 

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

[sqlite] DeviceSQL

[John Stanton]

I received an email promoting a DeviceSQL web presentation.  It 
specifically targets Sqlite and promises 5X performance.


For those interested -

DeviceSQL vs. SQLite: Which Gets You the Most Efficient Embedded Database?

DATE: Thursday, December 13th, 2007
TIME:  Noon PST, 3:00 PM EST
DURATION: 50 minutes + Q & A

Register:  
http://seminar2.techonline.com/registration/distrib.cgi?s=1191=1700

Who Should Attend: Software Engineers, Software Architects, Software 
Engineering Managers

Webinar Overview

Reliably processing, searching and managing growing amounts of data is driving 
many embedded developers to use third-party data management software such as 
DeviceSQL or SQLite. However, a critical issue for these technologies is their 
efficiency in terms of performance and memory usage, especially when they’re 
being used to replace hand-coded databases in resource-constrained systems.

While SQLite is widely-known, many users are frustrated by not being able to 
meet stringent performance and/or memory size goals with SQLite, particularly 
in applications with a sub-2 GHz CPU. In this webinar you’ll learn how 
DeviceSQL, a next-generation technology for managing data, provides 5x the 
performance of SQLite while yielding a smaller memory footprint. Also, a 
DeviceSQL user, TV Guide, will share how they were able to meet their 
aggressive design goals for managing electronic program guide (EPG) data. This 
seminar is for engineers who are currently using SQLite or anyone considering 
an embedded database technology.


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

Re: [sqlite] DeviceSQL

[drh]

John Stanton <[EMAIL PROTECTED]> wrote:
> I received an email promoting a DeviceSQL web presentation.  It 
> specifically targets Sqlite and promises 5X performance.
> 

If you view their web presentation and/or try out Encirq's
products, I would be very interested to hear your impressions.
Even better would be if you could blog about it.

Encirq has for years been running Google Adsense ads claiming
to be 20x faster than SQLite.  (Dunno why they have now reduced
that claim to 5x faster.)  But I have never yet seen an
independent confirmation of this.  Nor even have I been able
to find anybody who is actually using DeviceSQL in a product.
Web searches turn up nothing but marketing literature coming
directly or indirectly from Encirq.  Some independent analysis
(regardless of whether it is favorable or unfavorable to SQLite)
would be appreciated.

My understanding of DeviceSQL is:

   *  It is NOT transactional.  There is no such thing as ROLLBACK.
   *  If you lose power during a write, your database is toast.
   *  If your database schema changes, you have to recompile
  your application.
   *  The database file format changes depending on the schema.
   *  DeviceSQL is not a general-purpose database engine.  You
  compile SQL statements into C code on a development
  workstation, then compile the C code for your embedded
  device.

I can imagine circumstances where the DeviceSQL approach,
while much less flexible and forgiving than SQLite, might
be a better way to go, depending on what you are trying to
do.  But I have not gotten good vibes from Encirq as a 
company.  And I have no idea how reliable the DeviceSQL 
product is.  I would really appreciate your thoughts on 
that subject.

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


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

Re: [sqlite] DeviceSQL

[John Stanton]

[EMAIL PROTECTED] wrote:

John Stanton <[EMAIL PROTECTED]> wrote:
I received an email promoting a DeviceSQL web presentation.  It 
specifically targets Sqlite and promises 5X performance.




If you view their web presentation and/or try out Encirq's
products, I would be very interested to hear your impressions.
Even better would be if you could blog about it.

Encirq has for years been running Google Adsense ads claiming
to be 20x faster than SQLite.  (Dunno why they have now reduced
that claim to 5x faster.)  But I have never yet seen an
independent confirmation of this.  Nor even have I been able
to find anybody who is actually using DeviceSQL in a product.
Web searches turn up nothing but marketing literature coming
directly or indirectly from Encirq.  Some independent analysis
(regardless of whether it is favorable or unfavorable to SQLite)
would be appreciated.

My understanding of DeviceSQL is:

   *  It is NOT transactional.  There is no such thing as ROLLBACK.
   *  If you lose power during a write, your database is toast.
   *  If your database schema changes, you have to recompile
  your application.
   *  The database file format changes depending on the schema.
   *  DeviceSQL is not a general-purpose database engine.  You
  compile SQL statements into C code on a development
  workstation, then compile the C code for your embedded
  device.

I can imagine circumstances where the DeviceSQL approach,
while much less flexible and forgiving than SQLite, might
be a better way to go, depending on what you are trying to
do.  But I have not gotten good vibes from Encirq as a 
company.  And I have no idea how reliable the DeviceSQL 
product is.  I would really appreciate your thoughts on 
that subject.


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

Your earlier description of DeviceSQL intrigued me.  In general claims 
of "20x" or even "5x" imply either serious deficiencies in the compared 
product or a generous dose of snake oil in the challenger.  Since we 
know the Sqlite code and use it without encountering serious 
deficiencies, I smell snake, but shall look at the presentation and ask 
some questions if possible and report back.


Like you, I have been unable to uncover any concrete documentation or 
reviews of this product.


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

Re: [sqlite] SQLite Consortium Launches

[Kees Nuyt]

On Wed, 12 Dec 2007 11:28:40 +, [EMAIL PROTECTED] wrote:

>   SQLite Consortium Launches With Mozilla And
>   Symbian As Charter Members
>

Congratulations!
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] DeviceSQL

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> Be careful about speculative comments.
> 
> For all anyone knows, said product could use SQLite internally with 
> a couple of proprietary optimizations here and there that may make it
> faster in specific cases. 
> 
> The sqlite public domain license would allow that sort of thing.
> 

Because of the radically different architectures of SQLite
and DeviceSQL, it seems unlikely that they share a common
core.  Though, I suppose anything is possible...

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


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

Re: [sqlite] DeviceSQL

[Joe Wilson]

Be careful about speculative comments.

For all anyone knows, said product could use SQLite internally with 
a couple of proprietary optimizations here and there that may make it
faster in specific cases. 

The sqlite public domain license would allow that sort of thing.


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

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

[sqlite] Re: DeviceSQL

[A. Pagaltzis]

* John Stanton <[EMAIL PROTECTED]> [2007-12-12 17:55]:
> In general claims of "20x" or even "5x" imply either serious
> deficiencies in the compared product or a generous dose of
> snake oil in the challenger.

Depends. The outline given by Dr. Hipp about the product’s
features may the claim quite plausible, because you pay a hefty
cut in features and reliability in exchange for a very large
increase in speed; a price that many may well find unacceptable.
(It is, after all, easy, as they say, to compute the wrong answer
in constant time.)

Regards,
-- 
Aristotle Pagaltzis // 

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

RE: [sqlite] DeviceSQL

[RB Smissaert]

Couldn't find anywhere how much this costs.
Newsgroup search shows nil.
Has anybody downloaded and tried the demo?

RBS

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 12 December 2007 17:10
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL

Be careful about speculative comments.

For all anyone knows, said product could use SQLite internally with 
a couple of proprietary optimizations here and there that may make it
faster in specific cases. 

The sqlite public domain license would allow that sort of thing.


 


Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] SQLite gives Access Violation in Windows

[Uwe Sander]

Hi,
Am Mittwoch, 12. Dezember 2007 schrieb CAVALO SCHMIDT:
> Ok, I'm sending to you a zip file with the Pelles C project, together
> with the database (a small test database, with a key1 with 2 values
> [which doesn't throw errors] and a key2 with six values [which throws the
> error]); note that, in my installation, I put sqlite3.lib and sqlite3.h,
> respectively, in the Lib and in the Include folders inside Pelles C
> installation folder; I included the LIB I created from the DEF in the zip
> file, but it wasn't possible, due to size limitations, to include neither
> the DLL nor the H.

you have to tell the compiler that the calling convention for the callback 
is the same as sqlite expect it to be. The default for windows applications 
is stdcall, but I think it should be cdecl for the lib you use.

HIH
Uwe

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

[sqlite] Occasional Database is Locked Error

[Mark Riehl]

I'm running SQLite 3.4.1 under Linux.  I have a C++ application that
inserts records into the database.

During testing, I lauch the C++ app and I start sqlite3 from a console
and open the database that is being written to by the C++ application.
 I perform selects using sqlite3 (no inserts) to look at the collected
data and occasionally get "Error: database is locked".  Sometimes get
the error a few times in a row, other times, it doesn't happen at all.

I'm not inserting a lot of records - worst case, there are ~25 records
per second.

Am I just getting lucky and issuing selects at the same time I'm
writing from the other process?  I'm planning on using a web-based app
to periodically poll the database (to provide summary information)
while the C++ app performs the inserts.  What is the best way to
reduce/minimize the lock issue?

Thanks for the help,
Mark

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

Re: [sqlite] Re: select date using variables in Python

[C M]

On Dec 12, 2007 8:20 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> C M <[EMAIL PROTECTED]> wrote:
> > cur.execute('SELECT string FROM test WHERE d >= date("now","+1 day")')
> >
> > However, I'd like to make it flexible, so that a user can put in an
> > amount of days forward or backward and the query will use
> > that--basically I want the user to be able to select the date range
> > over the data in the table.  I tried something like:
> >
> > amount = "1"  #just to try it, later this will refer to a user-chosen
> > variable
> > cur.execute ('SELECT string FROM test WHERE d >= date("now", "+",?,"
> > day")',amount)
> >
> > But of course that's not right and it doesn't work.  What is the right
> > syntax in this case to use the ? to stand for the 1 in the original
> > "+1 day" portion?
>
> I don't know Python well enough, but the SQL statement you want would
> look like this:
>
> SELECT string FROM test WHERE d >= date('now', ? || ' days');
>
> || is the string concatenation operator in SQL.
>
> Igor Tandetnik
>

Thanks to you both--using the || did the trick, and I can try the other
approaches mentioned as well.  In Python Igor's suggestion was just:

amount = "+1"
cur.execute('SELECT string, d FROM test WHERE d >= date("now", ? || ? || "
days")',amount)

Re: [sqlite] Re: select date using variables in Python

[Dennis Cote]

C M wrote:


Thanks to you both--using the || did the trick, and I can try the other
approaches mentioned as well.  In Python Igor's suggestion was just:

amount = "+1"
cur.execute('SELECT string, d FROM test WHERE d >= date("now", ? || ? || "
days")',amount)

  
You will might be better off building the entire argument string in 
Python and then binding that string to a single argument to the date 
function in the SQL. You can then use the same query to do offsets in 
hours, days, months, or years for example. It will probably be slightly 
faster as well since SQLite will be parsing an executing a simpler SQL 
statement.


You should also invert your string quoting in Python. SQL uses single 
quotes for literal values, and double quotes for identifiers (such as 
table and column names). SQLite accepts double quotes around literals as 
an extension, but it can get you into trouble if you have a column with 
the same name as your literal value. In that case it will be interpreted 
as the column name. You might even want to use Python's triple quotes to 
allow both single and double quotes to be used in the SQL string itself.


Note, the plus character in '+1 days' is not needed. It is simply a sign 
character, it does not signify addition. You only need the sign if you 
want a negative offset.


amount = 1
unit = "days"
offset = str(amount) + " " + unit
cur.execute("SELECT string, d FROM test WHERE d >= date('now', ?)", offset)


HTH
Dennis Cote



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

Re: [sqlite] SQLite gives Access Violation in Windows

[CAVALO SCHMIDT]

Thank you, it worked.

2007/12/12, Uwe Sander <[EMAIL PROTECTED]>:
>
> Hi,
> Am Mittwoch, 12. Dezember 2007 schrieb CAVALO SCHMIDT:
> > Ok, I'm sending to you a zip file with the Pelles C project, together
> > with the database (a small test database, with a key1 with 2 values
> > [which doesn't throw errors] and a key2 with six values [which throws
> the
> > error]); note that, in my installation, I put sqlite3.lib and sqlite3.h,
> > respectively, in the Lib and in the Include folders inside Pelles C
> > installation folder; I included the LIB I created from the DEF in the
> zip
> > file, but it wasn't possible, due to size limitations, to include
> neither
> > the DLL nor the H.
>
> you have to tell the compiler that the calling convention for the callback
> is the same as sqlite expect it to be. The default for windows
> applications
> is stdcall, but I think it should be cdecl for the lib you use.
>
> HIH
> Uwe
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] Occasional Database is Locked Error

[John Stanton]

Your application needs to handle the synchronization logic since there 
is no DB server to do it for you.  When you get a busy check you can 
pause for a short time and relaunch the query.


Mark Riehl wrote:

I'm running SQLite 3.4.1 under Linux.  I have a C++ application that
inserts records into the database.

During testing, I lauch the C++ app and I start sqlite3 from a console
and open the database that is being written to by the C++ application.
 I perform selects using sqlite3 (no inserts) to look at the collected
data and occasionally get "Error: database is locked".  Sometimes get
the error a few times in a row, other times, it doesn't happen at all.

I'm not inserting a lot of records - worst case, there are ~25 records
per second.

Am I just getting lucky and issuing selects at the same time I'm
writing from the other process?  I'm planning on using a web-based app
to periodically poll the database (to provide summary information)
while the C++ app performs the inserts.  What is the best way to
reduce/minimize the lock issue?

Thanks for the help,
Mark

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




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

Re: [sqlite] Occasional Database is Locked Error

[Dennis Cote]

John Stanton wrote:
Your application needs to handle the synchronization logic since there 
is no DB server to do it for you.  When you get a busy check you can 
pause for a short time and relaunch the query.


Mark Riehl wrote:

I'm running SQLite 3.4.1 under Linux.  I have a C++ application that
inserts records into the database.

During testing, I lauch the C++ app and I start sqlite3 from a console
and open the database that is being written to by the C++ application.
 I perform selects using sqlite3 (no inserts) to look at the collected
data and occasionally get "Error: database is locked".  Sometimes get
the error a few times in a row, other times, it doesn't happen at all.







I think he is saying that he is getting the errors in the sqlite3 
command shell, not in his application.


To fix that you should issue a .timeout 1000 command in the shell when 
you open it. That will cause a query to wait up to one second (1000 ms) 
if the database is locked by your application when it is writing.


HTH
Dennis Cote


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

Re: [sqlite] SQLite Consortium Launches

[Richard Klein]

[EMAIL PROTECTED] wrote:

   SQLite Consortium Launches With Mozilla And
   Symbian As Charter Members
...
Additional information is available at the SQLite website, 
http://www.sqlite.org/.


Congratulations!

You might want to fix one small typo I noticed:

"The SQLite Consortium is a membership association dedicated to
insuring the continuing vitality and independent of SQLite."

should be:

"... independence of SQLite."
   ^^


Richard Klein


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

Re: [sqlite] Re: select date using variables in Python

[C M]

On Dec 12, 2007 1:51 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> C M wrote:
> >
> > Thanks to you both--using the || did the trick, and I can try the other
> > approaches mentioned as well.  In Python Igor's suggestion was just:
> >
> > amount = "+1"
> > cur.execute('SELECT string, d FROM test WHERE d >= date("now", ? || ? ||
> "
> > days")',amount)
> >
> >
> You will might be better off building the entire argument string in
> Python and then binding that string to a single argument to the date
> function in the SQL. You can then use the same query to do offsets in
> hours, days, months, or years for example. It will probably be slightly
> faster as well since SQLite will be parsing an executing a simpler SQL
> statement.
>
> You should also invert your string quoting in Python. SQL uses single
> quotes for literal values, and double quotes for identifiers (such as
> table and column names). SQLite accepts double quotes around literals as
> an extension, but it can get you into trouble if you have a column with
> the same name as your literal value. In that case it will be interpreted
> as the column name. You might even want to use Python's triple quotes to
> allow both single and double quotes to be used in the SQL string itself.
>
> Note, the plus character in '+1 days' is not needed. It is simply a sign
> character, it does not signify addition. You only need the sign if you
> want a negative offset.
>
> amount = 1
> unit = "days"
> offset = str(amount) + " " + unit
> cur.execute("SELECT string, d FROM test WHERE d >= date('now', ?)",
> offset)
>
>
> HTH
> Dennis Cote
>

Thank you--very useful to know.

Re: [sqlite] Occasional Database is Locked Error

[Richard Klein]

Mark Riehl wrote:


Am I just getting [un]lucky and issuing selects at the same time I'm
writing from the other process?


Yes.


I'm planning on using a web-based app
to periodically poll the database (to provide summary information)
while the C++ app performs the inserts.  What is the best way to
reduce/minimize the lock issue?


There is no way to reduce/minimize the lock issue.  "Database is locked"
is a "normal" error return that your polling app must be prepared to deal
with.  As other folks have noted, the way to deal with it is to sleep for
awhile and then retry the SELECT.

- Richard Klein


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

[sqlite] RSS feed

[Francois Botha]

Hi,

Sorry if this has been asked previously. I searched the archives and 
couldn't find anything.


Is there perhaps an RSS feed version of new releases of SQLITE?  I'm not 
talking about the detailed feed about every little bug that has been 
fixed.  I'm basically looking for an RSS version of 
http://www.sqlite.org/changes.html.


If it doesn't exist, I'll slap one together.

regards,
Francois

Re: [sqlite] Occasional Database is Locked Error

[Joanne Pham]

Hi All,
This is the exaclty approach that I used in my code.
No way to reduce/minimize the lock issue regardless about the reading or the 
writting.
We need to have to code to sleep and re-try the command again.
JP



- Original Message 
From: Richard Klein <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, December 12, 2007 11:59:04 AM
Subject: Re: [sqlite] Occasional Database is Locked Error

Mark Riehl wrote:

> Am I just getting [un]lucky and issuing selects at the same time I'm
> writing from the other process?

Yes.

> I'm planning on using a web-based app
> to periodically poll the database (to provide summary information)
> while the C++ app performs the inserts.  What is the best way to
> reduce/minimize the lock issue?

There is no way to reduce/minimize the lock issue.  "Database is locked"
is a "normal" error return that your polling app must be prepared to deal
with.  As other folks have noted, the way to deal with it is to sleep for
awhile and then retry the SELECT.

- Richard Klein


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


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

[sqlite] SQLite and Columnar Databases

[Yuvaraj Athur Raghuvir]

Hello,

There seems to be a high interest in columnar databases recently.

Is there any plan of supporting data organization as a columnar database in
SQLite? What are the challenges here?

Regards,
Yuva

[sqlite] How to check if the table has some specific values

[Joanne Pham]

Hi All,
I have been working on MSSQL server and SQLite is new to me. I usually did the 
following in MSQL server to check of the specific row is existed in the table 
and have different action depending on the result of the check. For example:
Create table versionTable (dbVersion varchar(20)
   insert into versionTable values('6, 0, 0, 1');
Now the table is created and it has one row(6, 0, 0, 1).
I usually do the following to check the content of the table

If NOT EXISTS ( select 1 from versionTable where dbVersion = '6, 0, 0, 1')
  insert into versionTable values('6, 0, 0, 2');
ELSE
 update versionTable set dbVersion = '6, 0, 0, 2';

I really don't know how to convert these syntax from MSSQL server to SQLite.
Your help is appreciated.
Thanks,
JP


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

[sqlite] Re: How to check if the table has some specific values

[Igor Tandetnik]

Joanne Pham  wrote:

I have been working on MSSQL server and SQLite is new to me. I
usually did the following in MSQL server to check of the specific row
  is existed in the table and have different action depending on the
result of the check. For example: Create table versionTable
(dbVersion varchar(20) insert into versionTable values('6, 0, 0, 1');
Now the table is created and it has one row(6, 0, 0, 1).
I usually do the following to check the content of the table

If NOT EXISTS ( select 1 from versionTable where dbVersion = '6, 0,
 0, 1') insert into versionTable values('6, 0, 0, 2');
ELSE
update versionTable set dbVersion = '6, 0, 0, 2';

I really don't know how to convert these syntax from MSSQL server to
SQLite.


delete from versionTable;
insert into versionTable values ('6, 0, 0, 2');


Specifically for maintaining a version number in the database file, see 
also


PRAGMA user_version

at http://sqlite.org/pragma.html

Igor Tandetnik 



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

Re: [sqlite] Re: How to check if the table has some specific values

[Joanne Pham]

Hi Igor,
Thanks for the response.
Basiclly there is no SQL logic in SQLite.
I would like to check if the database version is xyz then I will have different 
action and if then database version is abc then I will have different action. 
So SQLite doesn't allow this luxury.
Thanks,
JP


- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: SQLite 
Sent: Wednesday, December 12, 2007 4:14:08 PM
Subject: [sqlite] Re: How to check if the table has some specific values

Joanne Pham  wrote:
> I have been working on MSSQL server and SQLite is new to me. I
> usually did the following in MSQL server to check of the specific row
>  is existed in the table and have different action depending on the
> result of the check. For example: Create table versionTable
> (dbVersion varchar(20) insert into versionTable values('6, 0, 0, 1');
> Now the table is created and it has one row(6, 0, 0, 1).
> I usually do the following to check the content of the table
>
> If NOT EXISTS ( select 1 from versionTable where dbVersion = '6, 0,
>  0, 1') insert into versionTable values('6, 0, 0, 2');
> ELSE
>update versionTable set dbVersion = '6, 0, 0, 2';
>
> I really don't know how to convert these syntax from MSSQL server to
> SQLite.

delete from versionTable;
insert into versionTable values ('6, 0, 0, 2');


Specifically for maintaining a version number in the database file, see 
also

PRAGMA user_version

at http://sqlite.org/pragma.html

Igor Tandetnik 


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


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

Re: [sqlite] How to check if the table has some specific values

[T]

Hi Joanne,

I have been working on MSSQL server and SQLite is new to me. I  
usually did the following in MSQL server to check of the specific  
row is existed in the table and have different action depending on  
the result of the check. For example:

Create table versionTable (dbVersion varchar(20)
  insert into versionTable values('6, 0, 0, 1');
Now the table is created and it has one row(6, 0, 0, 1).
I usually do the following to check the content of the table

If NOT EXISTS ( select 1 from versionTable where dbVersion = '6, 0,  
0, 1')

 insert into versionTable values('6, 0, 0, 2');
ELSE
update versionTable set dbVersion = '6, 0, 0, 2';

I really don't know how to convert these syntax from MSSQL server to  
SQLite.


I suggest that you avoid thinking about procedural steps in an SQL  
database. SQL is based in the concept of sets so you apply a single  
action to a whole set or subset. Rather than check if something exists  
and then choose what to do about it, I think a better approach is to  
specify the subset that you want to affect, and run the action on that  
subset. If the subset is empty, then nothing will happen. This also  
has the advantage of fewer connections to the database, so potentially  
twice as fast.


In your particular example, you seem to just want one row always in  
the table, and insert if it doesn't exist, and update if it does  
exist. SQLite has a built in variant of insert that will handle this,  
called "insert or replace", which will replace if it would violate a  
constraint such as a primary key. So, a rewrite would be something  
like this:


-- Set up:
create table versionTable (ID integer primary key, dbVersion text);
insert into versionTable values(1, '6, 0, 0, 1');

-- Insert or replace:
insert or replace into versionTable values(1, '6, 0, 0, 2' );

-- Check:
select * from versionTable;

which gives:

ID  dbVersion
--  --
1   6, 0, 0, 2

Does this suit your purpose?

Tom


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

Re: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

[steveweick]

This is Steve Weick, CTO & VP Engineering at Encirq Corp., developers and IP
owners of DeviceSQL. I would like to address D. Richard Hipp’s statements.

RDH:"If you view their web presentation and/or try out Encirq's products, I
would be very interested to hear your impressions. Even better would be if
you could blog about it.Encirq has for years been running Google Adsense ads
claiming to be 20x faster than SQLite. (Dunno why they have now reduced that
claim to 5x faster.) But I have never yet seen an independent confirmation
of this. Nor even have I been able
to find anybody who is actually using DeviceSQL in a product. Web searches
turn up nothing but marketing literature coming directly or indirectly from
Encirq. Some independent analysis (regardless of whether it is favorable or
unfavorable to SQLite) would be appreciated."



SW: The DeviceSQL  performance advantage over SQLite has been demonstrated
by running a series of benchmarks with a variety of operations using  Linux
on PCs,  ARM, Freescale, and other processor platforms that are commonly
used in embedded applications. 

In all our benchmarking we attempt to present SQLite capabilities at their
best. So we "tweak" SQLite to use indexes, not scans, in all cases. We also
opt (for fairness) to compare the products using only paged storage with
B-trees like those of SQLite. In many cases our other indexing techniques
are far superior to this approach. We provide detailed benchmark reports as
well as the benchmark code to prospective customers.

 We have seen that SQLite performance, while poor on larger PCs, degrades
significantly on small processors compared with DeviceSQL. We believe that
this is due to the fact that SQLite uses a number of techniques that consume
large amounts of the available CPU capacity, and it is therefore unable to
operate at the flash or disk speed. While SQLite performance has improved in
some areas in the last few releases, we can still show that DeviceSQL is
2-10X faster in all of the interesting cases and 50X faster in one odd case. 

You do not see client listings on our site because our clients believe that
DeviceSQL is part of their competitive advantage and they do not like to
advertise to their competition what they are using. You will however see
outspoken users of DeviceSQL explain why they chose DeviceSQL over SQLite,
if they were not able to make SQLite satisfy their requirements, like
Gemstar-TV Guide.


RDH: "My understanding of DeviceSQL is:


* It is NOT transactional. There is no such thing as ROLLBACK."


SW: This is false. DeviceSQL DOES support transactions and ROLLBACK, just
not in the traditional, resource intensive manner of maintaining a
journaling log. Rather, we use a simple approach which maintains data
integrity, high performance, and small footprint without introducing the
possibility of corrupting the journal.



RDH:"* If you lose power during a write, your database is toast."


SW: Again, not true. DeviceSQL has supported transactions and rollback since
its very first release in 2003 and continues to do so today. Contrary to Mr.
Hipp's assertions, DeviceSQL ensures that writes complete successfully
(ensuring no power outage can cause corruption) before continuing after a
commit. In fact, because of DeviceSQL's novel (and very simple) commit
approach, it is possible to prove that application data is recoverable (this
is quite difficult to do with the logging approach used by SQLite and
important for devices that must handle critically important data). In
addition to fast updates, the DeviceSQL approach yields substantially
shorter boot times after failures. This is often important to consumer
devices where the end user will not tolerate long boot times.



RDH:"* If your database schema changes, you have to recompile your
application."


SW: This is true. DeviceSQL is targeted for embedded applications where
executables change rarely, so schema changes are a big deal, and the clients
do not want to make changes to the schema after production begins. We do
however, offer migration utilities and approaches for doing this if needed.


RDH:"* The database file format changes depending on the schema."


SW: Not sure what this statement is about, although all databases have this
to some extent.


RDH:"* DeviceSQL is not a general-purpose database engine. You compile SQL
statements into C code on a development workstation, then compile the C code
for your embedded device."


SW: Neither is SQLite by this standard. Both products are
application-resident database engines that live in the application's address
space. The question is whether the main use model is compiled SQL versus
interpreted SQL or C APIs. DeviceSQL also supports C query interfaces. This
is rarely an issue in small devices where the database manager is embedded
in the application, and where our compiled language can be used to implement 
application database logic.


RDH:"I can imagine circumstances where the DeviceSQL approach, 

Re: [sqlite] SQLite and Columnar Databases

[Darren Duncan]

At 4:42 AM +0530 12/13/07, Yuvaraj Athur Raghuvir wrote:

Hello,
There seems to be a high interest in columnar databases recently.
Is there any plan of supporting data organization as a columnar database in
SQLite? What are the challenges here?
Regards,
Yuva


Something I will say about this, for people who don't know, is that 
this columnar thing is strictly an implementation detail.  While 
adopting it would be probably backwards-incompatible file formats, 
the programmatic API and SQL et al wouldn't have to change a bit and 
would remain compatible. -- Darren Duncan


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

Re[2]: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

[Ion Silvestru]

>SW: Richard,  We have written to you directly before to ask you to stop the
>FUD and incorrect statements, and you have chosen to continue. I suggest you
>not waste everyone's time by circulating deliberately misleading
>information.
I think you are very aggressive and I think you must apologise to, not
only Richard, but to us (just see previous messages about DeviceSQL,
full of suppositions).

These were no "FUD and incorrect statements", nor "misleading
information", these were only suppositions, and this is because it's
hard to find real technical information or specifications on DeviceSQL, only
marketing information. Maybe DeviceSQL is a good product, but absence
of real info and abundance of marketing make us think and suppose
various things (just see previous messages).

All of us are waiting for what Richard stated:
"If you view their web presentation and/or try out Encirq's
products, I would be very interested to hear your impressions.
Even better would be if you could blog about it."

Even better if all of us can have access to this web presentation, to
find out maybe more technical info about DeviceSQL.

Any way, thank you.


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