[sqlite] SQLite on a Windows Network

I am interested in using SQLite for my business management program which is currently using text files to store all data. I have Windows XP and 3 PC's access my data files through a shared drive (Windows file sharing). I have heard problems with SQLite and NFS but I have no idea if a standard

Re: [sqlite] How to input a double num?

Thanks a lot for every reply. The num is just random num. The project is on power-measurement. So there are some big nums with high precision. May I use sqlite3_bind_double() and sqlite3_prepare_v2() to solve the problem. liubin liu wrote: > > Now I use the sqlite3_mprintf() and the "%f"

Re: [sqlite] querys for analysis for performace measures

On 30 Oct 2009, at 1:12am, oryann9 wrote: > Other than setting up typical cron jobs such as iostat, vmstat and > the like, will you provide me with a query(s) to test DB performance > time it takes to return data such as table joins and or any other > relevant system tables? The

[sqlite] querys for analysis for performace measures

HiI Other than setting up typical cron jobs such as iostat, vmstat and the like, will you provide me with a query(s) to test DB performance time it takes to return data such as table joins and or any other relevant system tables? Thank you

Re: [sqlite] Some clarification needed about Unicode

> > No, I mean which encoding. You can't give a UTF-16 string to an API > > that only knows how to handle UCS-2 encoded data > > Well, most of the time, you can. Only in rare cases do you need to treat > surrogate pairs in special way. One such case, relevant to this discussion, > is converting

Re: [sqlite] Some clarification needed about Unicode

John Crenshaw wrote: > No, I mean which encoding. You can't give a UTF-16 string to an API > that only knows how to handle UCS-2 encoded data Well, most of the time, you can. Only in rare cases do you need to treat surrogate pairs in special way. One such case,

[sqlite] memory management under iphone

I was having a lot of trouble matching up the observed behavior of sqlite 3.6.12, as it comes pre-installed on Snow Leopard and on the iphone, with the sqlite documentation, as it pertains to dynamic memory management. Specifically, it appeared that sqlite3_soft_heap_limit() was having no effect.

Re: [sqlite] Some clarification needed about Unicode

>Thanks for the link. That clarifies things a lot. So, for the OP, if you >are targeting Win2k, it would be a good idea to use UCS-2, not UTF-16, >with any wide API calls. XP and above should (according to Kaplan and >Chen) support UTF-16 for API calls. W2k is clearly something of the past.

Re: [sqlite] How to input a double num?

> He's probably measuring the number of atoms in a city or something. LOL. Actually my bet is that field of the project has more to do with number theory. That's the only likely way to get a number that large with such high precision. John ___

Re: [sqlite] Late data typing. Am I missing something?

> Um...how do I go to the page that describes the date-time information > without just 'knowing' it's there? For example, there seems to be no > path to http://www.sqlite.org/lang_datefunc.html from > http://.sqlite.org. I used Google personally. "sqlite date functions" John

Re: [sqlite] Some clarification needed about Unicode

> Don't worry: we're all confused with MS wording! For what I understand > having also myself tried to sort out the question, is that there is a > line drawn: before XP unicode support included was nothing else than > UCS-2 (W2K). Xp and post-XP system include Unicode 5.1 and use UTF-16 >

Re: [sqlite] feature proposal - strong but dynamic typing

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Darren Duncan wrote: > I should point out, for people reading this thread, that it is very possible > to > have both the amount of type flexibility that SQLite provides and have strict > typing, at the same time. What I fail to understand with all

Re: [sqlite] Some clarification needed about Unicode

> MB_COMPOSITE has nothing to do with surrogate pairs You're right. I was trying to determine whether the output was UTF-16 or UCS-2 based on whether the output might use multiple bytes to represent a character, which is where I got tripped up. > Do you believe _that's_ what differentiates

Re: [sqlite] Some clarification needed about Unicode

Hi John, >Microsoft never seems to clearly identify whether the wide APIs should >be given UTF-16 or UCS-2. Their guide on internationalization would seem >to suggest that UCS-2 must be used, however, there is some reason to >believe that perhaps UTF-16 is handled correctly as well. Couldn't

Re: [sqlite] Late data typing. Am I missing something?

On Thu, 29 Oct 2009 17:25:34 -0400 Pavel Ivanov wrote: > > For example, there seems to be no > > path to http://www.sqlite.org/lang_datefunc.html from > > http://.sqlite.org. > > Go to Documentation -> SQL Syntax -> date and time functions. > > Pavel > Uh...RTFM?

Re: [sqlite] Late data typing. Am I missing something?

On Thu, Oct 29, 2009 at 05:21:29PM -0400, Ted Rolle scratched on the wall: > I'm learning so much from this discussion! > I've come to believe that late (lazy) typing is a Good Thing(tm). > > Um...how do I go to the page that describes the date-time information > without just 'knowing' it's

[sqlite] feature proposal - strong but dynamic typing

This email is a feature proposal for SQLite; I can rewrite it if desired. Ted Rolle wrote (in the "Late data typing ..." thread): > Doesn't dynamic data typing lead to bad data? > And proliferation of home-grown editing routines? > It seems that a strict data typing at column definition time

Re: [sqlite] Late data typing. Am I missing something?

> For example, there seems to be no > path to http://www.sqlite.org/lang_datefunc.html from > http://.sqlite.org. Go to Documentation -> SQL Syntax -> date and time functions. Pavel On Thu, Oct 29, 2009 at 5:21 PM, Ted Rolle wrote: > I'm learning so much from this

Re: [sqlite] Late data typing. Am I missing something?

I'm learning so much from this discussion! I've come to believe that late (lazy) typing is a Good Thing(tm). Um...how do I go to the page that describes the date-time information without just 'knowing' it's there? For example, there seems to be no path to http://www.sqlite.org/lang_datefunc.html

Re: [sqlite] Some clarification needed about Unicode

John Crenshaw wrote: > 2. MultiByteToWideChar supports a "MB_COMPOSITE" flag, which appears > to > give UTF-16 output. MB_COMPOSITE has nothing to do with surrogate pairs, and everything to do with whether, say, Latin-1 character Á (A with accute) is converted to a

Re: [sqlite] Late data typing. Am I missing something?

On Thu, Oct 29, 2009 at 03:21:13PM -0500, Nicolas Williams scratched on the wall: > On Wed, Oct 28, 2009 at 07:11:29PM +, O'Neill, Owen wrote: > > I must agree with other posters that the lack of an exposed timestamp > > type does feel like something of a gap. > > Given the rather large

Re: [sqlite] Late data typing. Am I missing something?

> > I must agree with other posters that the lack of an exposed timestamp > > type does feel like something of a gap. > > Given the rather large number of reasonable ways to represent > timestamps, I have to disagree. I'd rather have the freedom to use > whichever representation is best for my

Re: [sqlite] Some clarification needed about Unicode

> Certainly not! > > Win32 supports full Unicode 5.1 in UTF-16 for a long time. I double checked this just to be sure, and now I'm not. Here are the facts: 1. When Microsoft describes "Unicode" and "wide characters" the description always matches UCS-2 (they continually mention that "Unicode"

Re: [sqlite] Some clarification needed about Unicode

Hi, ´¯¯¯ >Despite of that, I'm aware that I have some more that pure US-ASCII in >the >blob objects, in fact I'm near your situation because used the Spanish >languaje and have 8-bit extended ASCII with some special >characters -accented characters and so-. > >So the question is Yes, I have

Re: [sqlite] Late data typing. Am I missing something?

On Wed, Oct 28, 2009 at 07:11:29PM +, O'Neill, Owen wrote: > You can get close if you put some check constraints on the columns. This is key: you can get the benefits of static and dynamic data typing. > I must agree with other posters that the lack of an exposed timestamp > type does feel

Re: [sqlite] SELECT * vs SELECT columns ?

On Thu, Oct 29, 2009 at 3:19 PM, Gerry Snyder wrote: > Beau Wilkinson wrote: >> >>  If the schema changes, a listing of every column can be invalidated, but >> the asterisk cannot > > OTOH, a listing of every column may catch a query which is no longer > valid,

Re: [sqlite] SELECT * vs SELECT columns ?

On Thu, Oct 29, 2009 at 2:59 PM, John Crenshaw wrote: >> If the schema changes, a listing of every column can be >> invalidated, but the asterisk cannot. > > This is only partly true. At some point, the code is going to need to > grab the individual fields, and that is

Re: [sqlite] Some clarification needed about Unicode

> there must exist zillions [working] wrappers to VC++. You would think. In fact, there are only a few, and most are not very good. I used the wrapper at Code Project as a base, then added handling for SQLITE_LOCKED, a date class, better blob handling, transaction support, and other useful

Re: [sqlite] SELECT * vs SELECT columns ?

> If the schema changes, a listing of every column can be > invalidated, but the asterisk cannot. This is only partly true. At some point, the code is going to need to grab the individual fields, and that is the point where the asterisk fails to serve you well. If new fields are added to the

Re: [sqlite] Some clarification needed about Unicode

Jean-Christophe: Thanks for your advice -very helpful and ilustrative for my-. Fortunately I'm not in the same horror history thank to the people in this list, and because I use the old and simple way: say plain SQLite C interface; plain Windows32 C API and plain Visual Cpp (Multibyte build in

Re: [sqlite] problem with inserting non NULL values into sqlite table

Hi How do I catch a error raised by a trigger from a programming language like C++ or freepascal? Do we have a call back to do the same? regards Nataraj On Thu, Oct 29, 2009 at 8:36 PM, T wrote: > > hello everyone ! > > I`m trying to use sqlite database with

Re: [sqlite] problem with inserting non NULL values into sqlite table

T wrote: > The problem I`m dealing here is that i cant insert other datas into > the table i`ve created except NULL, because i get following error > message: > > Error in select statement : INSERT INTO a (a,b,c,d) VALUES (NULL, > NULL, something_else, NULL) [no such

Re: [sqlite] problem with inserting non NULL values into sqlite table

> Error in select statement : INSERT INTO a (a,b,c,d) VALUES (NULL, NULL, > something_else, NULL) [no such column: something_else]. So what are you trying to insert by this statement? What is "something_else"? If you suppose it to be a string then you have to enclose it in single quotes like

Re: [sqlite] how to get the Trigger's Raise err in Application

sqlite3.h greensparker schrieb: > what are the headers i need to add in QT , to get sqlite3 *handle syntax and > sqlite3_errmsg working ?? > > > jan-118 wrote: >> greensparker schrieb: >>> i want the trigger's raise error in QT call. >> Try this in Qt: >> >> QString msg; >> QVariant v =

[sqlite] problem with inserting non NULL values into sqlite table

hello everyone ! I`m trying to use sqlite database with c++, but something isnt working right... The problem I`m dealing here is that i cant insert other datas into the table i`ve created except NULL, because i get following error message: Error in select statement : INSERT INTO a (a,b,c,d)

Re: [sqlite] has used anyone sqlite for production web sites ??

Your writing transactions most probably will not block readers, but each writer will block others. So whether SQLite is applicable for you depends on how much load your server will have. If it's 60/80 per second (I wouldn't call such site small :) ) then you can try it but it's already a boundary

Re: [sqlite] SELECT * vs SELECT columns ?

>You could use EXPLAIN to see if there is a different query plan, but I'd >bet there isn't. * will generally be slower, just because you usually >won't need EVERY column. If you can specify only certain columns, that >will save you some time. This will "save you some time" in the sense that the

Re: [sqlite] has used anyone sqlite for production web sites ??

Hi Sebastian, The reading should be ok, but you'll have to be *really* careful how you handle that many concurrent writers. See the recent thread titled "shared cache mode and 'LOCKED'" which has a discussion of topics with the same problem. Cheers. Owen -Original Message- From:

Re: [sqlite] Late data typing. Am I missing something?

Am 29.10.2009 um 14:31 schrieb Simon Slavin: > > On 29 Oct 2009, at 9:36am, John Crenshaw wrote: > >>> Consider the case of an application using an SQLite database to >>> store >>> its settings (like the Windows registry, but portable). The dynamic >>> type system is great for this. >>> >>>

Re: [sqlite] Some clarification needed about Unicode

Hi, Please, follow Igor advices, he is right. >[1] Read the actual textual data with sqlite3_column_blob() Which you can directly convert to TEXT if, as you say, you entered only 7-bit ASCII or UTF-8 compliant data. >[2] Assuming the system code page matches the one used when the data was

Re: [sqlite] Late data typing. Am I missing something?

On 29 Oct 2009, at 9:36am, John Crenshaw wrote: >> Consider the case of an application using an SQLite database to store >> its settings (like the Windows registry, but portable). The dynamic >> type system is great for this. >> >> CREATE TABLE Settings (Name TEXT PRIMARY KEY, Value BLOB); >>

[sqlite] has used anyone sqlite for production web sites ??

i have an very small web site ( 60/80 Concurrent users )... running with mysql.. but i need transactions ( and my web hosting does not support innodb ) but have support for SQLITE 2.x & 3.x)... The web site has built with PHP using PDO.. the max..db-load for a

Re: [sqlite] Some clarification needed about Unicode

>My main point is that you can't take the UTF-16 string and safely supply >it to APIs which want UCS-2 encoded text, such as Win32 APIs (including >things like SetWindowText()). Odds are that the only library you are >using which supports UTF-16 is SQLite. You should always be converting >the

Re: [sqlite] How to input a double num?

On 29 Oct 2009, at 12:38pm, Griggs, Donald wrote: > Regarding: How to input the num > "212345678901234567890123456.988290112"? > > > Purely curious, if it's not confidential -- how are you acquiring > numbers of such precision? > > If these are measurements, I'm just awed by the precision the

Re: [sqlite] Some clarification needed about Unicode

- Original Message - From: "Igor Tandetnik" To: Sent: Thursday, October 29, 2009 1:45 PM Subject: Re: [sqlite] Some clarification needed about Unicode > > The only Win32 API function that can handle UTF-8 strings is > MultiByteToWideChar

Re: [sqlite] how to get the Trigger's Raise err in Application

what are the headers i need to add in QT , to get sqlite3 *handle syntax and sqlite3_errmsg working ?? jan-118 wrote: > > greensparker schrieb: >> i want the trigger's raise error in QT call. > > Try this in Qt: > > QString msg; > QVariant v = QSqlDatabase::database().driver()->handle(); >

Re: [sqlite] Some clarification needed about Unicode

A.J.Millan wrote: > Thanks for your answer; let me see if I understood correctly the process: > > [1] Read the actual textual data with sqlite3_column_blob() > > [2] Assuming the system code page matches the one used when the data was > originally inserted, convert with mbstowcs() > > [3]

Re: [sqlite] Some clarification needed about Unicode

On 10/29/09 12:55 , "A.J.Millan" wrote: > Now, do you know about some library to conver to and from UTF-8 or UTF-16 to > UCS-2? > [4-1b] convert with WideCharToMultiByte(CP_UTF8) On 10/29/09 12:51 , "Igor Tandetnik" wrote: > You > can use

Re: [sqlite] How to input a double num?

Regarding: How to input the num "212345678901234567890123456.988290112"? Purely curious, if it's not confidential -- how are you acquiring numbers of such precision? If these are measurements, I'm just awed by the precision the device is achieving. If instead these numbers represent

Re: [sqlite] Some clarification needed about Unicode

Igor: Thanks for your answer; let me see if I understood correctly the process: [1] Read the actual textual data with sqlite3_column_blob() [2] Assuming the system code page matches the one used when the data was originally inserted, convert with mbstowcs() [3] (Doubt) The result can be

Re: [sqlite] Some clarification needed about Unicode

A.J.Millan wrote: > Now, do you know about some library to conver to and from UTF-8 or UTF-16 to > UCS-2? John's claims notwithstanding, you don't want or need UCS-2. It's a strict subset of UTF-16. Every valid UCS-2 string is also a UTF-16 string, but the converse is not true. UCS-2 is of

Re: [sqlite] Some clarification needed about Unicode

John Crenshaw wrote: > Similarly, UTF-16 is NOT the same as UCS-2 (the wide "Unicode" chars > used by MS APIs) Win32 API does too support UTF-16. What makes you believe otherwise? > though it looks the same at low values. UTF-16 is a > multibyte character set, while UCS-2 is always 2 bytes per

Re: [sqlite] Some clarification needed about Unicode

John: From: "John Crenshaw" To: "General Discussion of SQLite Database" Sent: Thursday, October 29, 2009 11:46 AM Subject: Re: [sqlite] Some clarification needed about Unicode > > My main point is that you can't take the UTF-16 string and

Re: [sqlite] Some clarification needed about Unicode

A.J.Millan wrote: > Really, here you touched tangentially the core of my question. Besides all > those great theories, at last I have UTF-8 encoded data in a dBase, and the > UCS-2 encoded data of the MS Win32 API (w_chars in muy Cpp app). The > question is: What is the concrete way to and from

Re: [sqlite] Some clarification needed about Unicode

John Crenshaw wrote: > My main point is that you can't take the UTF-16 string and safely supply > it to APIs which want UCS-2 encoded text, such as Win32 APIs (including > things like SetWindowText()). What makes you believe Win32 API, and SetWindowText in particular, does not support surrogate

Re: [sqlite] using EXCEPT with UNION

mchulet wrote: > Hi, >Please need your help in figuring out why this Oracle query does not > work in SQLite : > (select * from A minus select * from B) union all (select * from B minus > select * from B) > > I tried the same in SQLite: > select * from A EXCEPT select * from B union all select

[sqlite] using EXCEPT with UNION

Hi, Please need your help in figuring out why this Oracle query does not work in SQLite : (select * from A minus select * from B) union all (select * from B minus select * from B) I tried the same in SQLite: select * from A EXCEPT select * from B union all select * from B EXCEPT select *

Re: [sqlite] Some clarification needed about Unicode

John: > 2. UTF-8 is NOT the same as ASCII for values greater than 127. > Similarly, UTF-16 is NOT the same as UCS-2 (the wide "Unicode" chars > used by MS APIs), though it looks the same at low values. UTF-16 is a > multibyte character set, while UCS-2 is always 2 bytes per character. > You have

[sqlite] Regarding NATURAL JOIN (implicit cartesian product)

I'm not sure if the bug-fix for "natural self-join" solves this, but here we go. This query yields a cartesian product: SELECT COUNT(X) FROM A NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D); However, if I rearrange the tables I get the desired result: SELECT COUNT(X) FROM (B NATURAL

Re: [sqlite] Idea for improving page cache

Just for the sake of discussion I've attached a performance graph for various C++ data structures plus the Unrolled LL. The tests where run on a dell vostro 1400 laptop. As you can see the graphs show the ULL to be quite efficient for insert/delete from the front/back of the list. I beleive

Re: [sqlite] Some clarification needed about Unicode

Yes, they are obscure, but that is the whole point of supporting Unicode. Because users want to enter obscure characters into your application ;) My main point is that you can't take the UTF-16 string and safely supply it to APIs which want UCS-2 encoded text, such as Win32 APIs (including things

Re: [sqlite] shared cache mode and 'LOCKED'

Thankyou John and Tom ! I feel more enlightened now ! So I think I could summarise it by saying It is assumed that every thread (within every process) has it's own instance of a database connection object. When multiple processes share a *database* file contention will be indicated by

Re: [sqlite] Some clarification needed about Unicode

On Oct 29, 2009, at 4:41 PM, Jean-Christophe Deschamps wrote: > >> [1] Supposing some textual data already inserted as UTF-8 (default >> mode) in >> a dBase, and a connection opened with sqlite3_open(): Does a >> sqlite3_column_text16 retrieves a correct UTF-16 content? Is to >> say, do >>

Re: [sqlite] Some clarification needed about Unicode

From: "Jean-Denis Muys" Sent: Thursday, October 29, 2009 11:10 AM Subject: Re: [sqlite] Some clarification needed about Unicode > This may be seen as nit picking, but when discussing character encodings > and > representations, the issues can become so subtil and

Re: [sqlite] Some clarification needed about Unicode

On 10/29/09 10:51 , "John Crenshaw" wrote: > 2. UTF-8 is NOT the same as ASCII for values greater than 127. ASCII only uses 7 bits values, so no larger representation can be "the same as ASCII for values greater than 127". This may be seen as nit picking, but when

Re: [sqlite] Some clarification needed about Unicode

1. My understanding is that it will convert between UTF-8 and UTF-16 when you use these functions. I haven't tested this though, so you might try a simple test app just to make sure. 2. UTF-8 is NOT the same as ASCII for values greater than 127. Similarly, UTF-16 is NOT the same as UCS-2 (the

Re: [sqlite] Some clarification needed about Unicode

>[1] Supposing some textual data already inserted as UTF-8 (default >mode) in >a dBase, and a connection opened with sqlite3_open(): Does a >sqlite3_column_text16 retrieves a correct UTF-16 content? Is to say, do >SQLite the convertion internally? > >[2] Assuming the previous -or a UTF-16

Re: [sqlite] Late data typing. Am I missing something?

>>> *if* you know that the number *is* a date. >>> >> >> If the column has a type of timestamp, it should be safe to always >> assume that it IS a date. >sqlite> CREATE TABLE t1 (StartTime TIMESTAMP, Duration REAL); >sqlite> CREATE TABLE t2 AS SELECT StartTime, StartTime + Duration /

[sqlite] Some clarification needed about Unicode

Hi list: After some years using this wonderful tool, I embraced the internationalization of a application, and despite some readings in this list, and muy own test -not conclusive-, I still have some obscure corners. [1] Supposing some textual data already inserted as UTF-8 (default mode) in

Re: [sqlite] Late data typing. Am I missing something?

John Crenshaw wrote: >> *if* you know that the number *is* a date. >> > > If the column has a type of timestamp, it should be safe to always > assume that it IS a date. sqlite> CREATE TABLE t1 (StartTime TIMESTAMP, Duration REAL); sqlite> CREATE TABLE t2 AS SELECT StartTime, StartTime +

Re: [sqlite] How to input a double num?

You cannot store 212345678901234567890123456.988290112 using IEEE a 8 byte double precision. You cannot do this even with a 16 byte (long double - which for x86 architectures is actually 80-bit extended precision - only 64 bit for mantissa; is not the quadruple precision). If you care about

Re: [sqlite] Late data typing. Am I missing something?

> *if* you know that the number *is* a date. If the column has a type of timestamp, it should be safe to always assume that it IS a date. Don't put non-dates in it, and there isn't a problem. I can't imagine a case where you would not know whether the value is a date. Am I missing something here?

Re: [sqlite] Late data typing. Am I missing something?

John Crenshaw wrote: > Strings have a number of other disadvantages in this case. They take > more computations to compare, they take time to parse when you read > them, and they take longer to build when you insert them. Generally, > storing dates as a number of some sort is ideal. > I do