Re: [sqlite] Nested Inner Join Help

Ed Hawke wrote: > By run-time defined fields I meant column names that SQL would not > recognise until the query was executed I don't get the distinction. Could you give an example of column names that SQL would somehow "recognize" before a query is executed? What do you mean by "recognize"

[sqlite] How to import an empty value(NULL) into database from a file?

Hello, how can I import an empty value into the database from a file. The filed may be int or string, if I write "NULL" , then I'll read a string "NULL" from it. I hope that I can get an empty value (Eg, an empty string whose size() is zero in Qt). How can I do ? Thanks. Kermit

Re: [sqlite] Nested Inner Join Help

Thank you again Igor. By run-time defined fields I meant column names that SQL would not recognise until the query was executed, and therefore are only defined when the statement is "run". I am aware that this is probably not the correct terminology. Ed Igor Tandetnik wrote: > Ed Hawke >

Re: [sqlite] Nested Inner Join Help

Ed Hawke wrote: > Out of interest, would I be able to use binding on the run-time > defined fields? What's "run-time defined fields"? I'm not familiar with the term. > If I wanted to use: > > select * from A >join B b1 on (A.Column3 = b1.ID) >join

Re: [sqlite] Nested Inner Join Help

Thank you very much for this Igor. Out of interest, would I be able to use binding on the run-time defined fields? If I wanted to use: select * from A join B b1 on (A.Column3 = b1.ID) join C c1 on (b1.Column1 = c1.ID) join D d1 on (b1.Column2 = d1.ID) join B b2 on (A.Column4 =

Re: [sqlite] Nested Inner Join Help

Ed Hawke wrote: > To clarify this (I hope) if my table set-up is: > > Table A: > IDColumn1Column2Column3Column4 > 112345678921 23 > 216321587622 21 > 3

Re: [sqlite] Nested Inner Join Help

Thank you both for your help. That works, but what happens if I want to do a more complex query. If Table B contains references to tables C and D then I can just extend your example to select information from D as well as C. However if I have multiple Columns in A that are occurrences of B.ID

Re: [sqlite] Nested Inner Join Help

why not use: SELECT A.ID , A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER JOIN B ON A.Column3 = B.ID INNER JOIN C ON B.Column2 = C.ID ? On Thu, Jul 2, 2009 at 2:53 PM, Ed Hawke < edward.ha...@hawkeyeinnovations.co.uk> wrote: > Hi all, > >

Re: [sqlite] Nested Inner Join Help

I believe you need this: SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER JOIN B ON A.Column3 = B.ID INNER JOIN C ON B.Column2 = C.ID Pavel On Thu, Jul 2, 2009 at 2:53 PM, Ed Hawke wrote: > Hi all, > > I'm having problems

[sqlite] Nested Inner Join Help

Hi all, I'm having problems getting nested inner joins to work with SQLite. As far as I can tell from various resources the correct way of joining multiple tables is this: SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER JOIN B (INNER JOIN C ON B.Column2 = C.ID) ON

Re: [sqlite] getting offending constraint

On 2 Jul 2009, at 6:35pm, James Gregurich wrote: > works as expected in the sqlite3 exe. in C code, I get > "constraint failed" from sqlite3_errmsg. [...] > CREATE TRIGGER trig BEFORE INSERT ON test1b > BEGIN > SELECT CASE > WHEN (1) > THEN RAISE(ABORT, 'no parent element') > END; The

Re: [sqlite] getting offending constraint

I dropped the constraint and added the trigger. strange. works as expected in the sqlite3 exe. in C code, I get "constraint failed" from sqlite3_errmsg. If I drop the trigger shown below, the C code has no constraint violation as would be expected which means the trigger is causing the

Re: [sqlite] Advice needed for fuzzy search

Simon, At 15:26 02/07/2009, you wrote: ´¯¯¯ >What we need is a new version of Soundex which is written to deal with >unicode instead of ASCII. `--- Umm, soundex already fails often with plain english names. It would need a whole lot of native speakers of all those languages around to come up

Re: [sqlite] Type conversion performance

On Thu, Jul 02, 2009 at 11:02:44AM -0400, Shaun Seckman (Firaxis) scratched on the wall: > Consider the following SQL Statement: > > > > create table foo(val1 integer, val2 integer, val3 integer); > > insert into foo values('1', '2', '3'); > > > > When I call sqlite3_column_int(stmt,

Re: [sqlite] Advice needed for fuzzy search

On 2 Jul 2009, at 2:01pm, Jean-Christophe Deschamps wrote: > I need to deal with codepoints that would expand to several individual > characters. Examples are ligatures or fractions. I've never seen > ligatures used in French, nor in any european language, when it comes > to user input. I

Re: [sqlite] Type conversion performance

On Thu, 2 Jul 2009 11:02:44 -0400, "Shaun Seckman (Firaxis)" wrote: >Consider the following SQL Statement: > > > >create table foo(val1 integer, val2 integer, val3 integer); > >insert into foo values('1', '2', '3'); > > >When I call sqlite3_column_int(stmt,

Re: [sqlite] 3 million rows, query speeds, and returning zero for rows that don't exist

Seems to me you might need a master and detail tables. One with the dates, timestamp and one with the data CREATE TABLE 'log_time' ( id integer primary key datetimestring VARCHAR COLLATE NOCASE, timestamp INTEGER, date INTEGER, hour INTEGER, min INTEGER, sec INTEGER ) CREATE TABLE

Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2

Tino Lange wrote: > Hi Marcus, > > I have no problem when the reading application gets a lock error because the > writing application has a lock. > > But the problem is the other way around: > -> The writing application gets a lock error because someone reads! well... since the writer blocks

Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2

This is by design. The read only transaction acquires a "Read" Lock on the database file. So if that lock has not been released your writing process will receive the SQLITE_LOCKED error code. a. Use a begin exclusive on your writing process and test for sqlite locked. Using a loop and

[sqlite] Type conversion performance

Consider the following SQL Statement: create table foo(val1 integer, val2 integer, val3 integer); insert into foo values('1', '2', '3'); When I call sqlite3_column_int(stmt, columnNum), must it convert the string to integer each time or will it perform that conversion on insertion? Is it

Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2

Hi Marcus, I have no problem when the reading application gets a lock error because the writing application has a lock. But the problem is the other way around: -> The writing application gets a lock error because someone reads! This is what I can't understand and what I didn't expect. I would

Re: [sqlite] SQLite with read-only filesystems and hot-journal

On Thu, 2 Jul 2009 14:21:44 +0200, Jean-François Smigielski wrote: >Hello, list! > >I am currently using SQLite Databases (v3.5.8) stored on NAS servers and I >have to take snapshosts of these storage devices. The snapshots must be >accessible, and the database files can

Re: [sqlite] Compatibility problems with sqlite3 and On-Time RTOS

On Wed, 1 Jul 2009 15:46:08 +0200, "Software" wrote: > Does anyone run sqlite3 in an OnTime > real time operating system? > > Massimo Dazzan It is used in QNX, for example. SQLite should work properly if you implement the Operating system-specific routines for OnTime.

Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2

>From http://www.sqlite.org/lockingv3.html: -- Eventually, the writing process will want to update the database file, either because its memory cache has filled up or because it is ready to commit its changes. Before this happens, the writer must make sure no

Re: [sqlite] INSERT INTO and Hexadecimal Literals

Igor Tandetnik wrote: > No, there's no syntax for integral hexadecimal literals. There is a blob > literal x'B4', but it doesn't behave like a number (e.g. you can't > do arithmetic on blobs). Bottom line, the only option is to use plain > vanilla decimal numbers. > Thanks for the

Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2

I'm afraid this is by design of sqlite: Sqlite will lock the database during a writing transaction, I think no matter if you open a 2nd connection using the readonly flag. the typical solutions are: a) retry your read attempt after you receive a busy error code until it proceeds. b) consider

[sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2

Hi all, I have written a program that opens a SQLIte3 database and writes in it most of the time via replace/update. If I do select like (no writes, really only reads) statements from some other process that carefully opens the database with "sqlite3_open_v2(..., SQLITE_OPEN_READONLY, ...)"

[sqlite] Advice needed for fuzzy search

Hello group, I'm writing a fuzzy search extension. The current code is getting a little messy and I'm not completely satisfied by the way it works. So I'm about to rewrite it from scratch on stronger foundations. The goal is to provide a fuzzy search on _short_ fields like names, street

Re: [sqlite] Help with SQLite Query

Igor, That has worked perfectly. Thankyou very much for your assistance. Regards Andrew -- View this message in context: http://www.nabble.com/Help-with-SQLite-Query-tp24297858p24305860.html Sent from the SQLite mailing list archive at Nabble.com.

[sqlite] SQLite with read-only filesystems and hot-journal

Hello, list! I am currently using SQLite Databases (v3.5.8) stored on NAS servers and I have to take snapshosts of these storage devices. The snapshots must be accessible, and the database files can be found in well-named directories whose content has read-only permissions. My problem is that

Re: [sqlite] getting offending constraint

On Thu, Jul 2, 2009 at 4:17 AM, Jean-Denis Muys wrote: > Wow, I have been on the internet since 1986, and I had never realized that > this could be a problem. I am often guilty of the same, for lazyness > reasons, as this is a convenient way to avoid having to reenter the

[sqlite] Compatibility problems with sqlite3 and On-Time RTOS

Does anyone run sqlite3 in an OnTime real time operating system? Massimo Dazzan www.schnell.it Le informazioni trasmesse sono destinate esclusivamente alla persona o alla societa' in indirizzo e sono da intendersi confidenziali e riservate. Ogni trasmissione, inoltro, diffusione o

Re: [sqlite] getting offending constraint

On 2 Jul 2009, at 10:17am, Jean-Denis Muys wrote: > But when I examine its raw headers, I found this one: > > In-reply-to: > > > Is this the header that made you point your finger? Yes, this header is how some mail clients

Re: [sqlite] getting offending constraint

Wow, I have been on the internet since 1986, and I had never realized that this could be a problem. I am often guilty of the same, for lazyness reasons, as this is a convenient way to avoid having to reenter the "to", "cc", and "bcc" fields. I went back to James' original message, which my mail

[sqlite] corrupt journal file - database disk image is malformed

Hello all, to increase the poor insert speed of sqlite on my system I set the PRAGMA journal_mode=PERSIST on all my databases. Now I have two problems: 1. Sometimes master journal files are not deleted. All databases are healthy, the journal files are empty and in the remaining master

Re: [sqlite] 3 million rows, query speeds, and returning zero for rows that don't exist

On 2/07/2009 11:00 AM, yaconsult wrote: > > Most of the queries I've done so far have been pretty straightforward > and it's worked very well. But, now I need to do one that's taking > too long. There's probably a better way than the one I'm using. > > The problem is that I need to produce