Re: [sqlite] WHERE CLAUSE in UNICODE

On 30 Nov 2009, at 1:58am, Igor Tandetnik wrote: > Note that Unicode collation is not as simple as you might think. Did you know > that in Estonian, 'y' sorts between 'i' and 'j'? Or that in German phonebook > sort, 'oe' sorts as if it were a single letter between 'o' and 'p'? > Basically,

[sqlite] Trigger-based PostgreSQL to SQLite online replication

Hello! For some reasons may be useful online replication from PostgreSQL database to SQLite database or databases. I write this as set of pltclu procedures for my PostgreSQL database. The code of example procedure and results see here:

[sqlite] Error message from sqlite3_tokenizer_module.xCreate?

I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case they are invalid, I would like to return an explaining error message in addition to SQLITE_ERROR. I did not find a way to do this. Is it at all possible? Thanks, Ralf ___

Re: [sqlite] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

> Because there are several process who use the database. I have another > question:Could I close the database of other process in main process? Just use your favorite IPC mechanism and write your application so that main process sends message to other process and when other process receives it

[sqlite] Error message from sqlite3_tokenizer_module.xCreate?

I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case they are invalid, I would like to return an explaining error message in addition to SQLITE_ERROR. I did not find a way to do this. Is it at all possible? Thanks, Ralf ___

[sqlite] sqlite3 bind and insert double values

Hej, I am using sqlite_bind_double on a prepared statement (C API). The insert completes, however, the value stored in the sqlite table is different from the output of a casual printf("%f",..) Am I wrong when assuming that they should be the same. (double values are gathered by a gps and are in

Re: [sqlite] sqlite3 bind and insert double values

How different are they? Could this be [unavoidable] binary floating point storage limitations? Nick. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Lippautz Sent: 30 November 2009 13:50 To: sqlite-users@sqlite.org

Re: [sqlite] sqlite3 bind and insert double values

On 30 Nov 2009, at 1:50pm, Michael Lippautz wrote: > I am using sqlite_bind_double on a prepared statement (C API). The > insert completes, however, the value stored in the sqlite table is > different from the output of a casual printf("%f",..) By 'different' do you mean that it is obviously a

Re: [sqlite] sqlite3 bind and insert double values

Michael Lippautz wrote: > I am using sqlite_bind_double on a prepared statement (C API). The > insert completes, however, the value stored in the sqlite table is > different from the output of a casual printf("%f",..) How do you determine this? Have you retrieved the value back from the database

Re: [sqlite] sqlite3 bind and insert double values

Thanks for helping (all)! Well, I compared a casual printf("%f\n",val) with the entry stored in the database (as REAL). The entry is inserted into the db via sqlite3_bind_double (prepare/reset/step) Some examples: fprintf / database (looked up via .dump on the table) 47.824669 / 47.824669167

[sqlite] Getting up and running

Hi - I downloaded sqlite-amalgamation-3_6_20.zip and now I have a folder with three files: sqlite3.c, sqlite3.h, and sqlite3ext.h. I have no idea what to do next. I have a Mac 2.4 GHz Intel Core 2 Duo running Mac OS X 10.6.1. Can anyone guide me to a page that steps me through an installation

Re: [sqlite] sqlite3 bind and insert double values

Michael Lippautz wrote: > Well, I compared a casual printf("%f\n",val) with the entry stored in > the database (as REAL). The entry is inserted into the db via > sqlite3_bind_double (prepare/reset/step) > > Some examples: > fprintf / database (looked up via .dump on the table) > 47.824669 /

Re: [sqlite] Getting up and running

On 30 Nov 2009, at 2:09pm, FrankLane wrote: > Hi - I downloaded sqlite-amalgamation-3_6_20.zip and now I have a folder with > three files: sqlite3.c, sqlite3.h, and sqlite3ext.h. I have no idea what to > do next. I have a Mac 2.4 GHz Intel Core 2 Duo running Mac OS X 10.6.1. Can > anyone guide

Re: [sqlite] sqlite3 bind and insert double values

On 30 Nov 2009, at 2:05pm, Michael Lippautz wrote: > 47.824669 / 47.824669167 Same number. If you need better precision than that, declare the column type as TEXT and bind your data as text. But since you're using GPS coordinates I can tell you it's not necessary. That seventh digit in a

Re: [sqlite] sqlite3 bind and insert double values

Agreed - the difference in coordinates between the two values amounts to 3/10,000's of a second, which is about 9 millimeters. Most GPS devices can't give accuracy to more than 5 meters! It's also probably nicer storing GPS coordinates as numeric instead of text, as then you can use some useful

Re: [sqlite] sqlite3 bind and insert double values

Indeed, I was mistaken by the fact the printf cuts a float/double after 6 digits (iso c99) if no precision is given. SQLite seems to store the whole value, despite the situations where the last digits would be 0's. (I think I've copied some wrong values in the examples; sorry for that) And thx

[sqlite] Views across attached databases

Hello, Playing around with attached databases in sqlite 3.6.17, I notice that it is possible to create triggers that operate across attached databases, but not views. So something along the lines of: attach database "other.db" as other; create table other.a(a integer); create table b(a

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 08:15:58AM +, Simon Slavin wrote: > On 30 Nov 2009, at 1:58am, Igor Tandetnik wrote: > > Note that Unicode collation is not as simple as you might think. Did > > you know that in Estonian, 'y' sorts between 'i' and 'j'? Or that in > > German phonebook sort, 'oe' sorts

Re: [sqlite] WHERE CLAUSE in UNICODE

Nicolas Williams wrote: > IMO you'll have two types of text to sort: a) generic text (e.g., > proper names), b) localized text (e.g., message catalogs). For (a) > you'll want > to pick a collation, _any_ collation. Actually, you may want to choose a collation familiar

Re: [sqlite] WHERE CLAUSE in UNICODE

On 30 Nov 2009, at 5:51pm, Nicolas Williams wrote: > Consider a column that contains a person's last name. Q: do proper > names have a language? A: No, since people can be from all over and > even within a single country may have last names of various radically > different origins. But what

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 01:21:08PM -0500, Igor Tandetnik wrote: > Nicolas Williams wrote: > > IMO you'll have two types of text to sort: a) generic text (e.g., > > proper names), b) localized text (e.g., message catalogs). For (a) > > you'll want > > to pick a

Re: [sqlite] Getting up and running

On Mon, Nov 30, 2009 at 8:09 AM, FrankLane wrote: > > Hi - I downloaded sqlite-amalgamation-3_6_20.zip and now I have a folder with > three files: sqlite3.c, sqlite3.h, and sqlite3ext.h. I have no idea what to > do next. I have a Mac 2.4 GHz Intel Core 2 Duo running Mac OS

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 06:37:11PM +, Simon Slavin wrote: > > On 30 Nov 2009, at 5:51pm, Nicolas Williams wrote: > > > Consider a column that contains a person's last name. Q: do proper > > names have a language? A: No, since people can be from all over and > > even within a single country

Re: [sqlite] WHERE CLAUSE in UNICODE

Simon Slavin wrote: > But what is the purpose of collating a column ? Why, to allow it to be > indexed, of course. And for it to be indexed every value in the column must > be comparable to every other value. So it might be sufficient to simply > declare the column as having a language: > >

Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?

Monday 30 of November 2009 12:29:10 Ralf Junker napisał(a): > I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case > they are invalid, I would like to return an explaining error message in > addition to SQLITE_ERROR. I did not find a way to do this. Is it at all > possible?

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 06:37:11PM +, Simon Slavin scratched on the wall: > > On 30 Nov 2009, at 5:51pm, Nicolas Williams wrote: > > > Consider a column that contains a person's last name. Q: do proper > > names have a language? A: No, since people can be from all over and > > even within

Re: [sqlite] Views across attached databases

Hello! On Monday 30 November 2009 20:22:43 wcl...@gfs-hofheim.de wrote: > Is there a technical reason why triggers work, but views don't, or is this > just a feature that's not currently supported in views? I know that I can > create a temporary view and this works. Is this something I can

Re: [sqlite] Getting up and running

On Mon, Nov 30, 2009 at 2:18 PM, wrote: > > Hi - thanks for the help. Eventually I will want to get more involved, but > this lets me learn sql on my own database which is really helpful. One > problem I am having is that I have a tab-delimited text file which I load >

Re: [sqlite] WHERE CLAUSE in UNICODE

On 30 Nov 2009, at 6:49pm, Nicolas Williams wrote: > On Mon, Nov 30, 2009 at 06:37:11PM +, Simon Slavin wrote: >> > >> The column 'language' >> could perhaps be absolute, or perhaps be used as a default if the >> individual values did not declare a language. On the other hand, it >> might

Re: [sqlite] WHERE CLAUSE in UNICODE

Why does the optimizer not attempt to use an index when the WHERE clause uses the LIKE operator with a text column, as in: ... where myTextColumn like 'M%' My question ultimately concerns Unicode and indexing, and since these subjects are being discussed *passim* in this thread, I hope you

Re: [sqlite] WHERE CLAUSE in UNICODE

Tim Romano wrote: > Why does the optimizer not attempt to use an index when the WHERE > clause uses the LIKE operator with a text column, as in: > > ... where myTextColumn like 'M%' It does, when certain conditions are met. See http://sqlite.org/optoverview.html ,

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 08:56:48PM +, Simon Slavin wrote: > On 30 Nov 2009, at 6:49pm, Nicolas Williams wrote: > > See my previous message: it would make no sense to have a column with > > data-dependent collations. But perhaps I'm missing something. Can you > > describe the semantics of

[sqlite] Feature Request: More descriptive error message to replace "Constraint Failed."

Hello everyone, I've recently stumbled across a plethora of "Constraint Failed" errors in my code due to a schema change that modified various columns of tables to be unique. It would be extremely useful if this error message could contain more information such as which

Re: [sqlite] Feature Request: More descriptive error message to replace "Constraint Failed."

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Shaun Seckman (Firaxis) wrote: > It would be extremely useful if this > error message could contain more information such as which constraint > failed and even perhaps the data which caused the constraint to fail. > Even just knowing the column names

Re: [sqlite] WHERE CLAUSE in UNICODE

A revised question (sorry for leaving out an important fact on the first go): When the default binary collation is being used, why does the optimizer not attempt to use an index when the WHERE clause uses the LIKE operator with a text column, as in: ... where myTextColumn like 'M%' On my

Re: [sqlite] WHERE CLAUSE in UNICODE

Tim Romano wrote: > A revised question (sorry for leaving out an important fact on the > first go): > > When the default binary collation is being used, why does the > optimizer not attempt to use an index when the WHERE > clause uses the LIKE operator with a text column,

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 08:28:12PM -0500, Tim Romano scratched on the wall: > On my system, an index is used with the query above only when collation > is "collation nocase". In other words, when LIKE and collation agree on what "equal" is. > I /believe/ case_sensitive_like is OFF. I've

Re: [sqlite] WHERE CLAUSE in UNICODE

Aha. Got it. After PRAGMA case_sensitive_like =1 the optimizer might use the index with BINARY collation. I was under the mistaken impression that LIKE() adapted itself to the column's collation. But I see that it is not possible to define one column to use BINARY collation and another

Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?

On 30.11.2009 20:33, Grzegorz Wierzchowski wrote: > Monday 30 of November 2009 12:29:10 Ralf Junker napisał(a): >> I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case >> they are invalid, I would like to return an explaining error message in >> addition to SQLITE_ERROR. I

Re: [sqlite] Views across attached databases

Alexey Pechnikov wrote on 30/11/2009 19:58:15: > This feature was disabled becouse can produce inconsistent database schema. > If you want to enable this feature then the patch is simple: > > --- sqlite3-3.6.20.orig/src/attach.c > +++ sqlite3-3.6.20/src/attach.c > @@ -447,10 +447,11 @@ > if(

[sqlite] .read command

Can I pass parameters to the .read command? Like "select * from test where data=whatever" and then pass the value of whatever to the .read file somehow? Thanks - FL -- View this message in context: http://old.nabble.com/.read-command-tp26587826p26587826.html Sent from the SQLite mailing list