Re: [sqlite] deleting a single row

[P Kishor]

On 2/20/07, P Kishor <[EMAIL PROTECTED]> wrote:

On 2/19/07, Jim Crafton <[EMAIL PROTECTED]> wrote:
> If I have a simple table without an index column, and have multiple
> rows with the same data, is it possible to *only* delete one row? In
> other words, is there anything like the LIMIT syntax that's found in
> the SELECT command for DELETEs?
>


you could select all distinct records in a temp table, drop the
original table, and recreate the table

CREATE TEMP TABLE tmp AS
SELECT DISTINCT *


ack! never mind. This does what I thought you asked, not what you
actually asked. Time to go to bed.



--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] deleting a single row

[P Kishor]

On 2/19/07, Jim Crafton <[EMAIL PROTECTED]> wrote:

If I have a simple table without an index column, and have multiple
rows with the same data, is it possible to *only* delete one row? In
other words, is there anything like the LIMIT syntax that's found in
the SELECT command for DELETEs?




you could select all distinct records in a temp table, drop the
original table, and recreate the table

CREATE TEMP TABLE tmp AS
SELECT DISTINCT *

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] deleting a single row

[Dan Kennedy]

On Tue, 2007-02-20 at 00:29 -0500, Jim Crafton wrote:
> If I have a simple table without an index column, and have multiple
> rows with the same data, is it possible to *only* delete one row? In
> other words, is there anything like the LIMIT syntax that's found in
> the SELECT command for DELETEs?

Not directly. But every row has an implicit primary key that you can
refer to using (amongst other names) "oid". You can use a SELECT to
locate a single oid value and then use the oid to delete a single
row. i.e. instead of:

  DELETE FROM xxx WHERE ;

do

  DELETE FROM xxx 
  WHERE oid = (SELECT oid FROM xxx WHERE  LIMIT 1)

or similar.

Dan.



> Thanks,
> 
> Jim
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

[sqlite] deleting a single row

[Jim Crafton]

If I have a simple table without an index column, and have multiple
rows with the same data, is it possible to *only* delete one row? In
other words, is there anything like the LIMIT syntax that's found in
the SELECT command for DELETEs?

Thanks,

Jim

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

Re: [sqlite] Restrictions on table names

[miguel manese]

There is almost no restrictions, just put it inside square brackets

create table foobar ([my $0.02 column] int, ...)

M. Manese

On 2/20/07, Pablo Santacruz <[EMAIL PROTECTED]> wrote:

I'd like to know if there is any restriction on table names.

Thanks in advance

--
Pablo



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

Re: [sqlite] query on match between application and sqlite

[P Kishor]

On 2/19/07, Eric S. Johansson <[EMAIL PROTECTED]> wrote:

Joe Wilson wrote:

>> 2) can I use it for a message store and have it run faster than a 1
>> message per file system?
>
> What does that mean? Is "file system" a unit of time?

ack! bad sentence construction...  comparing a system storing 1 message
per file (and thousands of messages total) vrs one storing messages as
blobs or text in sqlite, which is faster?


only benchmarking can tell definitively. But, if you know which file
to open, and you know where exactly it is at, probably opening the
file would be very fast. Navigating filesystem folder hierarchy does
start slowing down as the number of files increases (beyond a few
thousand), but you can circumvent that by creating a smart hierarchy
schema.

But, why bother. SQLite will store your text and allow you to get to
it plenty fast, especially if you need complicated criteria to get to
it. However, if you are getting to the message using a single, unique
criteria, a Berkeley DB hash would probably be much faster (at one
point I did some tests, and BDB beat most everything). However, you
probably have a good reason to consider a SQL db, in which case SQLite
is a great choice.

..

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] query on match between application and sqlite

[Eric S. Johansson]

Joe Wilson wrote:

2) can I use it for a message store and have it run faster than a 1 
message per file system?


What does that mean? Is "file system" a unit of time?


ack! bad sentence construction...  comparing a system storing 1 message 
per file (and thousands of messages total) vrs one storing messages as 
blobs or text in sqlite, which is faster?



3) how would one allocate the different data sets across how many dbms?


You can put all your data in a single sqlite database file,
or use several different sqlite database files and ATTACH them
at runtime - it's up to you.


don't know enough yet to judge pros and cons


If you're new to SQL, here's a good link: http://sqlzoo.net/


thanks.  I've known about it since the late 1970's but never used it.

--
Speech-recognition in use.  It makes mistakes, I correct some.

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

Re: [sqlite] query on match between application and sqlite

[Joe Wilson]

> 1) need to know if sqlite can help me cut down on the number of little db's 

Sure.

> 2) can I use it for a message store and have it run faster than a 1 
> message per file system?

What does that mean? Is "file system" a unit of time?

You can store messages in BLOBs or as TEXT in an SQLite table.

> 3) how would one allocate the different data sets across how many dbms?

You can put all your data in a single sqlite database file,
or use several different sqlite database files and ATTACH them
at runtime - it's up to you.

If you're new to SQL, here's a good link: http://sqlzoo.net/


 

Yahoo! Music Unlimited
Access over 1 million songs.
http://music.yahoo.com/unlimited

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

Re: [sqlite] query on match between application and sqlite

[Eric S. Johansson]

Eric S. Johansson wrote:
Again, I do most sincerely apologize for size of this request.  I've 
spent 30 years avoiding databases and I guess this is payback.  :-)  Any 
advice, good online tutorials on SQL, or feedback would be valued.


urk. I hate it when I forget to ask for what I need after info dumping 
what I got.


1) need to know if sqlite can help me cut down on the number of little 
db's (shat well trod path do I take)


2) can I use it for a message store and have it run faster than a 1 
message per file system?


3) how would one allocate the different data sets across how many dbms?

again, many thanks and I'm sorry about being frazzled.

--- eric
--
Speech-recognition in use.  It makes mistakes, I correct some.

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

[sqlite] Re: What query?

[Igor Tandetnik]

erw2 <[EMAIL PROTECTED]> wrote:

I have a table with a following data:
IdNo1   No2

11001   11
21002   11
31003   12
41004   12
51004   12
61005   12
71006   13
81007   13
91008   14
...     ...

Now, I would like to select only the rows when No2 change. So the
result 
of such query should look like:

IdNo1   No2

11001   11
31003   12
71006   13
91008   14
...     ...


select min(Id), min(No1), No2
from TableName
group by No2;

Igor Tandetnik

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

[sqlite] query on match between application and sqlite

[Eric S. Johansson]

I apologize if this comes across as a "do my homework for me" type of 
query but I'm in a bit of a bind and I'm trying to come up to some 
reasonable amount of knowledge in less than no time.  I've been using ad 
hoc DBM files for a variety of small databases and well, it's crumbling. 
 I'm seeing the disk light on solid for what I consider low volumes of 
traffic.


The application is an experimental anti-Spam testbed exploring the use 
of reputation through proof for puzzles and user interaction.  The list 
of things I'm tracking is fairly significant (for me) and I'm trying to 
pull things together.  the entire system has been implemented in Python 
so that puts additional constraints on the solution.


-- the players --

Reputation database is keyed by IP address and had a single integer 
(reputation).  Its visibility is exposed lan wide by a remote object call.


Rate of arrival database key is recipient address and contains 10 
timestamps for the most recently received messages.  Again, exposed via 
remote object call.


received messages database.  All messages that come in are saved in one 
of three categories green, yellow, red (a.k.a. ham, mystery meat, spam) 
on a per user basis.  Associated with each message are a series of 
additional data elements such as filter scored, any transitions between 
colors, source IP address, last filter seen.  visibility is strictly on 
a single machine although among a small number of processes.


for me the challenge with the received message database is accessing 
messages, expiration of messages and transitions between colors.


info exhaust: a list of all things to be graphed/tracked.  Probably best 
as a log file but I thought I would add it just in case it made sense as 
a database.  needs to roll over periodically and expire older data.


additionally, is a friends white list.  The key is the e-mail address of 
the "friend" and the data doesn't really matter.  It's the presence of 
the key that counts.  This is also a per user database used between a 
small number of processes.


Again, I do most sincerely apologize for size of this request.  I've 
spent 30 years avoiding databases and I guess this is payback.  :-)  Any 
advice, good online tutorials on SQL, or feedback would be valued.


---eric

--
Speech-recognition in use.  It makes mistakes, I correct some.

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

Re: [sqlite] sqlite: current_time is off

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- P Kishor <[EMAIL PROTECTED]> wrote:
> > > On 2/18/07, Neil McLeod <[EMAIL PROTECTED]> wrote:
> > > > I just started using SQLite, and it seems my timestamps are all 5 hours
> > > > ahead of time (i.e. "select current_time" gives me 0:47 when it is 
> > > > actually
> > > > 19:47 here). I guess this is GMT. Is there a way to adjust the time? 
> > > > I'm on
> > > > Windows XP, and the time is listed correctly in my taskbar.
> > > 
> > > by default, time is in UTC. Use 'localtime' modifier for your time.
> > > 
> > > select time('now', 'localtime');
> > 
> > Is there any way to force current_time to display localtime (other than
> > hacking the sqlite source code)?  Some environment setting or PRAGMA
> > or something?
> > 
> 
> CURRENT_TIME does not "display" anything.  It generates a value
> to be stored in the database.  Whether you realize it or not, 
> you want to store time and date values in UTC, not localtime.
> Convert from UTC to localtime at the point where you extract
> the information out of the database to display it to the user.
> 
> And to answer your question:  No, there is no way to get CURRENT_TIME
> to use anything other than UTC.

Thanks for your response.

I'm aware of UTC time handling and time zones. 
I just thought SQLite might support per-connection time zone settings 
to display/set datetime information as many other databases do. 

 http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
 http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

 "You can set the current time zone on a per-connection basis, 
 as described in Section 5.11.8, “MySQL Server Time Zone Support”. 
 TIMESTAMP values are stored in UTC, being converted from the 
 current time zone for storage, and converted back to the current 
 time zone upon retrieval."



 

Finding fabulous fares is fun.  
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel 
bargains.
http://farechase.yahoo.com/promo-generic-14795097

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

Re: [sqlite] SQLite db lock problem

[John Stanton]

Allan, Mark wrote:

Hi,

A little while back I submitted a query as to whether SQLite would be a good alternative to using MS Access as an internal database for a PC application. I received many repiles for which I was grateful. Mostly people thought that SQLite was a far more superior option to Access, the main concern I was warned about however was that SQLite does not work particularly well in a multiuser environment. We will need (in some instances) for the database file to be located on a network drive and there is the requirement to support up to 10 concurrent users. The actual traffic in most cases will be very light and the likelyhood of writes actually taking place at the same time actually very slim. However we do need the database engine to be able to handle this. 


Basically my questions are thus:-
1) What are peoples experiences with SQLite under the scenario I have described 
above?
2) What are peoples opinions on whether SQLite is a good choice for this 
project? Any other alternatives?
3) How severe is the database locking problem? How easy is it to unlock a 
locked database? How often on average will it occur?

It is worth noting that the project is a complete upgrade from an older 
version, the old version used access in the same environment as described above 
and we had no complaints of problems in multiuser usage. However we are finding 
access old technology and too slow and the 2Gb limit is mnow too small for some 
of our customers.

Any help/suggestions will be gratefully received.

Mark


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

Sqlite will work as well or better than Access in a networked 
environment since it uses the same networking connections.


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

[sqlite] Restrictions on table names

[Pablo Santacruz]

I'd like to know if there is any restriction on table names.

Thanks in advance

--
Pablo

[sqlite] What query?

[erw2]

Hi,
I have a table with a following data:
IdNo1   No2

11001   11
21002   11
31003   12
41004   12
51004   12
61005   12
71006   13
81007   13
91008   14
...     ...

Now, I would like to select only the rows when No2 change. So the result
of such query should look like:
IdNo1   No2

11001   11
31003   12
71006   13
91008   14
...     ... 

How should this query look like?

Regards
Wojciech


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

[sqlite] Accented characters not displayed properly

[Adriano]

I've created a .db database using sqlite maestro
http://www.sqlmaestro.com/products/sqlite/maestro/

On desktop, inside that pc tools, some fields containing text, all the
accented characters are correctly shown (for example èéàì).
When copy database on ppc device all the accented characters are
trasformed into 

I think the problem is here:
AddObject "newObjects.sqlite3.dbutf8","db"
I've to inserted somewhere the correct iso code, isn't it ?
It should be:
iso-8859-1

How to solve that ?
Thank you
Adriano

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

[sqlite] How to read next record of a table after a query 'select from where'

[Adriano]

"SELECT * FROM mytable WHERE NameField = '"" & Name & 
Set r= db.execute(cmd)
if i'd like to read next record in the same table how to do ?
I use last version of sqlite avaible.
Thanks
Adriano

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

RE: [sqlite] Re: Looking for equivalent syntax

[Anderson, James H \(IT\)]

Thanks, I'll try both and see which is faster. 

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 19, 2007 2:33 PM
To: SQLite
Subject: [sqlite] Re: Looking for equivalent syntax

Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> update C1_credDerivEvent
>set CDEvent = CDEvent || ',' ||
>(select b.CDEvent
> from C1_tmp_credDerivEvent b
> where C1_credDerivEvent.CDId = b.CDId)
> where exists (
>select * from C1_tmp_credDerivEvent b, tmp_events c
>where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
> )

This one is shorter, and requires only one subselect per row:

update C1_credDerivEvent set CDEvent = ifnull(
CDEvent || ',' || (select b.CDEvent
from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId
and b.CDEvent=c.CDEvent),
CDEvent)

If the nested select produces an empty set, it will be treated as NULL,
which will force the concatenation to be NULL, and the update will
degenerate into a no-op (SET CDEvent=CDEvent).

It's not necessarily faster though. If SQLite can use indexes to satisfy
the WHERE clause in the first statement, it doesn't need to look at
every record. If only a small portion of all records actually needs
updating, the first query may run faster even though a second lookup is
necessary for those records that do get updated after all. The second
query requires a linear scan of C1_credDerivEvent table, and a lookup
for every record.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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

[sqlite] Re: Looking for equivalent syntax

[Igor Tandetnik]

Igor Tandetnik <[EMAIL PROTECTED]> wrote:


update C1_credDerivEvent
   set CDEvent = CDEvent || ',' ||
   (select b.CDEvent
from C1_tmp_credDerivEvent b
where C1_credDerivEvent.CDId = b.CDId)
where exists (
   select * from C1_tmp_credDerivEvent b, tmp_events c
   where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)


This one is shorter, and requires only one subselect per row:

update C1_credDerivEvent set CDEvent = ifnull(
   CDEvent || ',' || (select b.CDEvent
   from C1_tmp_credDerivEvent b, tmp_events c
   where C1_credDerivEvent.CDId = b.CDId
   and b.CDEvent=c.CDEvent),
   CDEvent)

If the nested select produces an empty set, it will be treated as NULL,
which will force the concatenation to be NULL, and the update will
degenerate into a no-op (SET CDEvent=CDEvent).

It's not necessarily faster though. If SQLite can use indexes to satisfy
the WHERE clause in the first statement, it doesn't need to look at
every record. If only a small portion of all records actually needs
updating, the first query may run faster even though a second lookup is
necessary for those records that do get updated after all. The second
query requires a linear scan of C1_credDerivEvent table, and a lookup
for every record.

Igor Tandetnik 



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

RE: [sqlite] Re: Looking for equivalent syntax

[Anderson, James H \(IT\)]

Thanks! I'll give it a try. 

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 19, 2007 2:04 PM
To: SQLite
Subject: [sqlite] Re: Looking for equivalent syntax

Anderson, James H (IT)
 wrote:
> I'm trying to convert the following statement in Sybase syntax into
> the
> equivalent sqlite syntax:
>
> update C1_credDerivEvent
>set a.CDEvent = a.CDEvent || ',' || b.CDEvent
>   from C1_credDerivEvent a,
>C1_tmp_credDerivEvent b,
>tmp_eventsc
>  where a.CDId= b.CDId
>and b.CDEvent = c.CDEvent

update C1_credDerivEvent
set CDEvent = CDEvent || ',' ||
(select b.CDEvent
 from C1_tmp_credDerivEvent b
 where C1_credDerivEvent.CDId = b.CDId)
where exists (
select * from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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

Re: [sqlite] Looking for equivalent syntax

[P Kishor]

On 2/19/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

The original statement works fine in Sybase. I did not try it in Sqlite
because my previous experience is that the update statement in Sqlite
does not support the "from" clause. Am I missing something here?


no, seems like it indeed doesn't (PostGres does, but it is apparently
non-standard SQL). How about something like

UPDATE
(SELECT a.CDEvent || ',' || b.CDEvent AS CDEvent
 FROM C1_credDerivEvent a JOIN C1_tmp_credDerivEvent b ON
  a.CDId  = b.CDId JOIN tmp_events c ON b.CDEvent = c.CDEvent) AS tmp
SET CDEvent = tmp.CDEvent



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Monday, February 19, 2007 1:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Looking for equivalent syntax

On 2/19/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> I'm trying to convert the following statement in Sybase syntax into
the
> equivalent sqlite syntax:
>
> update C1_credDerivEvent
>set a.CDEvent = a.CDEvent || ',' || b.CDEvent
>   from C1_credDerivEvent a,
>C1_tmp_credDerivEvent b,
>tmp_eventsc
>  where a.CDId= b.CDId
>and b.CDEvent = c.CDEvent
>
> Here's what I came up with but it turns out not to be equivalent at
all:
>
> insert or replace into C1_credDerivEvent
>   select A.CDId as CDId,
>  A.CDEvent || ',' || B.CDEvent as CDEvent
>   from C1_credDerivEvent A,
>C1_tmp_credDerivEvent B,
>tmp_eventsC
>  where A.CDId= B.CDId
>and B.CDEvent = C.CDEvent;
>
> The Sybase statement simply updates each record for which the where
> clause is satified, yielding the same number of rows in the table
before
> the update as after.
>
> The Sqlite statement on the other hand, adds rows to the table.
>


well yes, you yourself are asking SQLite to "INSERT or REPLACE"... see
the INSERT part... that adds rows to the table.

In your Sybase version, you are only UPDATEin (equivalent to the
REPLACE part). SQLite is just doing what you are asking it to do.

What is wrong with your original statement? You never mentioned
whether that worked on not... did you try it? (listed again below)

update C1_credDerivEvent
  set a.CDEvent = a.CDEvent || ',' || b.CDEvent
 from C1_credDerivEvent a,
  C1_tmp_credDerivEvent b,
  tmp_eventsc
 where a.CDId= b.CDId
  and b.CDEvent = c.CDEvent

You could update it to the more standard-ish syntax like so

UPDATE C1_credDerivEvent
SET a.CDEvent = a.CDEvent || ',' || b.CDEvent
FROM C1_credDerivEvent a JOIN C1_tmp_credDerivEvent b ON
  a.CDId  = b.CDId JOIN tmp_events c ON b.CDEvent = c.CDEvent


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

[sqlite] Re: Looking for equivalent syntax

[Igor Tandetnik]

Anderson, James H (IT)
 wrote:

I'm trying to convert the following statement in Sybase syntax into
the
equivalent sqlite syntax:

update C1_credDerivEvent
   set a.CDEvent = a.CDEvent || ',' || b.CDEvent
  from C1_credDerivEvent a,
   C1_tmp_credDerivEvent b,
   tmp_eventsc
 where a.CDId= b.CDId
   and b.CDEvent = c.CDEvent


update C1_credDerivEvent
   set CDEvent = CDEvent || ',' ||
   (select b.CDEvent
from C1_tmp_credDerivEvent b
where C1_credDerivEvent.CDId = b.CDId)
where exists (
   select * from C1_tmp_credDerivEvent b, tmp_events c
   where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)

Igor Tandetnik 



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

[sqlite] Re: Looking for equivalent syntax

[Igor Tandetnik]

P Kishor <[EMAIL PROTECTED]> wrote:

What is wrong with your original statement? You never mentioned
whether that worked on not... did you try it? (listed again below)


Well, have _you_ tried it? SQLite doesn't support this syntax.


You could update it to the more standard-ish syntax like so

UPDATE C1_credDerivEvent
SET a.CDEvent = a.CDEvent || ',' || b.CDEvent
FROM C1_credDerivEvent a JOIN C1_tmp_credDerivEvent b ON
 a.CDId  = b.CDId JOIN tmp_events c ON b.CDEvent = c.CDEvent


SQLite doesn't support this syntax either. FROM clause is not part of 
UPDATE statement.


Igor Tandetnik 



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

RE: [sqlite] Looking for equivalent syntax

[Anderson, James H \(IT\)]

The original statement works fine in Sybase. I did not try it in Sqlite
because my previous experience is that the update statement in Sqlite
does not support the "from" clause. Am I missing something here?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Monday, February 19, 2007 1:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Looking for equivalent syntax

On 2/19/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> I'm trying to convert the following statement in Sybase syntax into
the
> equivalent sqlite syntax:
>
> update C1_credDerivEvent
>set a.CDEvent = a.CDEvent || ',' || b.CDEvent
>   from C1_credDerivEvent a,
>C1_tmp_credDerivEvent b,
>tmp_eventsc
>  where a.CDId= b.CDId
>and b.CDEvent = c.CDEvent
>
> Here's what I came up with but it turns out not to be equivalent at
all:
>
> insert or replace into C1_credDerivEvent
>   select A.CDId as CDId,
>  A.CDEvent || ',' || B.CDEvent as CDEvent
>   from C1_credDerivEvent A,
>C1_tmp_credDerivEvent B,
>tmp_eventsC
>  where A.CDId= B.CDId
>and B.CDEvent = C.CDEvent;
>
> The Sybase statement simply updates each record for which the where
> clause is satified, yielding the same number of rows in the table
before
> the update as after.
>
> The Sqlite statement on the other hand, adds rows to the table.
>


well yes, you yourself are asking SQLite to "INSERT or REPLACE"... see
the INSERT part... that adds rows to the table.

In your Sybase version, you are only UPDATEin (equivalent to the
REPLACE part). SQLite is just doing what you are asking it to do.

What is wrong with your original statement? You never mentioned
whether that worked on not... did you try it? (listed again below)

update C1_credDerivEvent
  set a.CDEvent = a.CDEvent || ',' || b.CDEvent
 from C1_credDerivEvent a,
  C1_tmp_credDerivEvent b,
  tmp_eventsc
 where a.CDId= b.CDId
  and b.CDEvent = c.CDEvent

You could update it to the more standard-ish syntax like so

UPDATE C1_credDerivEvent
SET a.CDEvent = a.CDEvent || ',' || b.CDEvent
FROM C1_credDerivEvent a JOIN C1_tmp_credDerivEvent b ON
  a.CDId  = b.CDId JOIN tmp_events c ON b.CDEvent = c.CDEvent


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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

Re: [sqlite] Looking for equivalent syntax

[P Kishor]

On 2/19/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

I'm trying to convert the following statement in Sybase syntax into the
equivalent sqlite syntax:

update C1_credDerivEvent
   set a.CDEvent = a.CDEvent || ',' || b.CDEvent
  from C1_credDerivEvent a,
   C1_tmp_credDerivEvent b,
   tmp_eventsc
 where a.CDId= b.CDId
   and b.CDEvent = c.CDEvent

Here's what I came up with but it turns out not to be equivalent at all:

insert or replace into C1_credDerivEvent
  select A.CDId as CDId,
 A.CDEvent || ',' || B.CDEvent as CDEvent
  from C1_credDerivEvent A,
   C1_tmp_credDerivEvent B,
   tmp_eventsC
 where A.CDId= B.CDId
   and B.CDEvent = C.CDEvent;

The Sybase statement simply updates each record for which the where
clause is satified, yielding the same number of rows in the table before
the update as after.

The Sqlite statement on the other hand, adds rows to the table.




well yes, you yourself are asking SQLite to "INSERT or REPLACE"... see
the INSERT part... that adds rows to the table.

In your Sybase version, you are only UPDATEin (equivalent to the
REPLACE part). SQLite is just doing what you are asking it to do.

What is wrong with your original statement? You never mentioned
whether that worked on not... did you try it? (listed again below)

update C1_credDerivEvent
 set a.CDEvent = a.CDEvent || ',' || b.CDEvent
from C1_credDerivEvent a,
 C1_tmp_credDerivEvent b,
 tmp_eventsc
where a.CDId= b.CDId
 and b.CDEvent = c.CDEvent

You could update it to the more standard-ish syntax like so

UPDATE C1_credDerivEvent
SET a.CDEvent = a.CDEvent || ',' || b.CDEvent
FROM C1_credDerivEvent a JOIN C1_tmp_credDerivEvent b ON
 a.CDId  = b.CDId JOIN tmp_events c ON b.CDEvent = c.CDEvent


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

[sqlite] Looking for equivalent syntax

[Anderson, James H \(IT\)]

I'm trying to convert the following statement in Sybase syntax into the
equivalent sqlite syntax:

update C1_credDerivEvent
   set a.CDEvent = a.CDEvent || ',' || b.CDEvent
  from C1_credDerivEvent a,
   C1_tmp_credDerivEvent b,
   tmp_eventsc
 where a.CDId= b.CDId
   and b.CDEvent = c.CDEvent

Here's what I came up with but it turns out not to be equivalent at all:

insert or replace into C1_credDerivEvent
  select A.CDId as CDId,
 A.CDEvent || ',' || B.CDEvent as CDEvent
  from C1_credDerivEvent A,
   C1_tmp_credDerivEvent B,
   tmp_eventsC
 where A.CDId= B.CDId
   and B.CDEvent = C.CDEvent;

The Sybase statement simply updates each record for which the where
clause is satified, yielding the same number of rows in the table before
the update as after.

The Sqlite statement on the other hand, adds rows to the table.

Rowcount before "insert or replace": 691,066
Rowcount after "insert or replace": 7,594,268

What am I doing wrong? Is there an equivalent Sqlite syntax or not?

Thanks,
Jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

[sqlite] SQLite db lock problem

[Allan, Mark]

Hi,

A little while back I submitted a query as to whether SQLite would be a good 
alternative to using MS Access as an internal database for a PC application. I 
received many repiles for which I was grateful. Mostly people thought that 
SQLite was a far more superior option to Access, the main concern I was warned 
about however was that SQLite does not work particularly well in a multiuser 
environment. We will need (in some instances) for the database file to be 
located on a network drive and there is the requirement to support up to 10 
concurrent users. The actual traffic in most cases will be very light and the 
likelyhood of writes actually taking place at the same time actually very slim. 
However we do need the database engine to be able to handle this. 

Basically my questions are thus:-
1) What are peoples experiences with SQLite under the scenario I have described 
above?
2) What are peoples opinions on whether SQLite is a good choice for this 
project? Any other alternatives?
3) How severe is the database locking problem? How easy is it to unlock a 
locked database? How often on average will it occur?

It is worth noting that the project is a complete upgrade from an older 
version, the old version used access in the same environment as described above 
and we had no complaints of problems in multiuser usage. However we are finding 
access old technology and too slow and the 2Gb limit is mnow too small for some 
of our customers.

Any help/suggestions will be gratefully received.

Mark


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

Re: [sqlite] Binary data in SQLite3

[jose isaias cabrera]

Thanks.

- Original Message - 
From: "Roger Binns" <[EMAIL PROTECTED]>

To: 
Sent: Monday, February 19, 2007 1:51 AM
Subject: Re: [sqlite] Binary data in SQLite3



-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

jose isaias cabrera wrote:

Can SQLite3 save binary data?


http://sqlite.org/datatype3.html

Look for blobs.


I would like to save zip files on it.  Is
this possible?


Yes.  There is a limit of 2GB per item and there is no random access api
to retrieve part of a blob - you have to get the whole thing in one go.

In general better practise is to store the zip files in the filesystem
and store the name of the file in SQLite.


If so, is there a place where I can read or get some
examples about it?


Blobs are just another SQLite datatype so you manipulate them in exactly
the same way as you do other types.

http://sqlite.org/docs.html

The topic also comes up fairly frequently on this list.  You can examine
the list archives for previous discussion.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF2Uj0mOOfHg372QQRAhPJAKDY/M/aash7JD7xlOGp0m/xwwHbjgCeJXj9
kiUUCu+a/POfb1PzP4Nf3Yw=
=Tb74
-END PGP SIGNATURE-

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




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

Re: [sqlite] Binary data in SQLite3

[jose isaias cabrera]

thanks.

- Original Message - 
From: "P Kishor" <[EMAIL PROTECTED]>

To: 
Sent: Monday, February 19, 2007 1:36 AM
Subject: Re: [sqlite] Binary data in SQLite3



On 2/19/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:


Greetings!

Can SQLite3 save binary data?  I would like to save zip files on it.  Is
this possible?  If so, is there a place where I can read or get some
examples about it?


blobs

http://www.sqlite.org/datatype3.html


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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




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

RE: [sqlite] Re: What is wrong with this SELECT CASE statement?

[RB Smissaert]

Got this worked out now and in fact it looks a big case statement is a bit
faster than the other methods. I also found that I don't need the CAST AS
INTEGER:

UPDATE A2ID965_J
SET DATE_OF_BIRTH =
CASE (DATE_OF_BIRTH / 100) % 100
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END

This is about 20% faster than a table lookup, even when the lookup table is
already present.

So, learned something useful there.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 19 February 2007 08:04
To: Bart Smissaert2
Subject: FW: [sqlite] Re: What is wrong with this SELECT CASE statement?



-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 19 February 2007 04:54
To: SQLite
Subject: [sqlite] Re: What is wrong with this SELECT CASE statement?

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> Trying to update my mmdd integers to months with a SELECT CASE
> statement:
>
> SELECT CASE
> (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS
> INTEGER) * 100)
> WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January'
> WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February'
> WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March'
> WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April'
> WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May'
> WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June'
> WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July'
> WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August'
> WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September'
> WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October'
> WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November'
> WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December'
> END
> FROM A2IDC21_J
>
> But no updates take place.

UPDATE is a statement, not an expression. It cannot appear nested in 
another statement. You want

UPDATE A2IDC21_J SET DATE_OF_BIRTH =
CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) -
CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
...
END

Also, the expression in the CASE can be simplified to

CAST(DATE_OF_BIRTH AS INTEGER) / 100 % 100

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





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

Re: [sqlite] compare open table and attached database table

[Rich Shepard]

On Sun, 18 Feb 2007, fangles wrote:


Thank you Igor, that's fantastic. I'm reading lots of SqLite tutorials but
a lot of the SQL is so far out of my brain's reach that it doesn't make
sense to me. And I love to play:):):)


  Look at Joe Celko's books on SQL. He tries to be useful for all
implementations and the reader learns a lot on how best to create SELECTs
that work.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] SQL request. Sign "-" as NOT LIKE

[Cecilia VIGNY]

[EMAIL PROTECTED] a écrit :

Cecilia VIGNY <[EMAIL PROTECTED]> wrote:
  

Hi everybody,

I'm building a SQL request in order to question my SQLite database from 
my php program. I would be happy if this request were compatible with a 
MySQL database ;o) So what I want to know is if we can use the minus 
sign as we can do with the index fulltext in MySQL. For instance, does 
this request function with SQLite ? :


SELECT iden FROM cmsa c where ( wwvw LIKE "%conseil%") -( wwvw LIKE 
"%agents%");





SQLite follows the SQL standard here, using "NOT LIKE".
See http://www.sqlite.org/lang_expr.html
--
D. Richard Hipp  <[EMAIL PROTECTED]>
  


Ok so if I want to run this request :

select * from cmsa where 
match(tsim,supp,nsup,even,emet,natu,mcex,indi,no,wwvw,iden,resu,comp,hist) 
against ("( +test1 +test2 -(test3 test4) )")


with SQLite, which means that I want all the records which contains 
(test1 AND test2) but WITHOUT (test3 OR test4), I will have to be 
malignant ...! Not funny... Yuck... :o(





Ce message est protégé par les règles relatives au secret des correspondances. 
Il est donc établi à destination exclusive de son destinataire. Celui-ci peut 
donc contenir des informations confidentielles. La divulgation de ces 
informations est à ce titre rigoureusement interdite. Si vous avez reçu ce 
message par erreur, merci de le renvoyer à l'expéditeur dont l'adresse e-mail 
figure ci-dessus et de détruire le message ainsi que toute pièce jointe.

This message is protected by the secrecy of correspondence rules. Therefore, 
this message is intended solely for the attention of the addressee. This 
message may contain privileged or confidential information, as such the 
disclosure of these informations is strictly forbidden. If, by mistake, you 
have received this message, please return this message to the addressser whose 
e-mail address is written above and destroy this message and all files attached.



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

Re: [sqlite] SQL request. Sign "-" as NOT LIKE

[drh]

Cecilia VIGNY <[EMAIL PROTECTED]> wrote:
> Hi everybody,
> 
> I'm building a SQL request in order to question my SQLite database from 
> my php program. I would be happy if this request were compatible with a 
> MySQL database ;o) So what I want to know is if we can use the minus 
> sign as we can do with the index fulltext in MySQL. For instance, does 
> this request function with SQLite ? :
> 
> SELECT iden FROM cmsa c where ( wwvw LIKE "%conseil%") -( wwvw LIKE 
> "%agents%");
> 

SQLite follows the SQL standard here, using "NOT LIKE".
See http://www.sqlite.org/lang_expr.html
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] sqlite: current_time is off

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- P Kishor <[EMAIL PROTECTED]> wrote:
> > On 2/18/07, Neil McLeod <[EMAIL PROTECTED]> wrote:
> > > I just started using SQLite, and it seems my timestamps are all 5 hours
> > > ahead of time (i.e. "select current_time" gives me 0:47 when it is 
> > > actually
> > > 19:47 here). I guess this is GMT. Is there a way to adjust the time? I'm 
> > > on
> > > Windows XP, and the time is listed correctly in my taskbar.
> > 
> > by default, time is in UTC. Use 'localtime' modifier for your time.
> > 
> > select time('now', 'localtime');
> 
> Is there any way to force current_time to display localtime (other than
> hacking the sqlite source code)?  Some environment setting or PRAGMA
> or something?
> 

CURRENT_TIME does not "display" anything.  It generates a value
to be stored in the database.  Whether you realize it or not, 
you want to store time and date values in UTC, not localtime.
Convert from UTC to localtime at the point where you extract
the information out of the database to display it to the user.

And to answer your question:  No, there is no way to get CURRENT_TIME
to use anything other than UTC.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Search all collumns with LIKE at once

[fangles]

Martin Jenkins-2 wrote:
> 
> fangles wrote:
>> PK? I'm sorry but I am not familiar with that term..
> 
> Sorry, in  this example a Primary Key is a column (eg an integer) which 
> uniquely specifies a row in a table.
> 
> In the example above you:
> 
> select all columns from the rows which have a PK in the set
>   (
> select the PK from all rows where the columns joined together 
> matches your search text
>   )
> 
> BTW, I don't know how well this performs compared to the FTS extensions.
> 
> Martin
> 
> 

Thanks Martin, the acronym threw me originally LOL. I'd have to redesign the
original database to do FTS so your way using PK seems easier for now.
-- 
View this message in context: 
http://www.nabble.com/Search-all-collumns-with-LIKE-at-once-tf3251161.html#a9040441
Sent from the SQLite mailing list archive at Nabble.com.


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

[sqlite] SQL request. Sign "-" as NOT LIKE

[Cecilia VIGNY]

Hi everybody,

I'm building a SQL request in order to question my SQLite database from 
my php program. I would be happy if this request were compatible with a 
MySQL database ;o) So what I want to know is if we can use the minus 
sign as we can do with the index fulltext in MySQL. For instance, does 
this request function with SQLite ? :


SELECT iden FROM cmsa c where ( wwvw LIKE "%conseil%") -( wwvw LIKE 
"%agents%");


Thank you !



Ce message est prot?g? par les r?gles relatives au secret des correspondances. 
Il est donc ?tabli ? destination exclusive de son destinataire. Celui-ci peut 
donc contenir des informations confidentielles. La divulgation de ces 
informations est ? ce titre rigoureusement interdite. Si vous avez re?u ce 
message par erreur, merci de le renvoyer ? l'exp?diteur dont l'adresse e-mail 
figure ci-dessus et de d?truire le message ainsi que toute pi?ce jointe.

This message is protected by the secrecy of correspondence rules. Therefore, 
this message is intended solely for the attention of the addressee. This 
message may contain privileged or confidential information, as such the 
disclosure of these informations is strictly forbidden. If, by mistake, you 
have received this message, please return this message to the addressser whose 
e-mail address is written above and destroy this message and all files attached.



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

Re: [sqlite] Search all collumns with LIKE at once

[Martin Jenkins]

fangles wrote:

PK? I'm sorry but I am not familiar with that term..


Sorry, in  this example a Primary Key is a column (eg an integer) which 
uniquely specifies a row in a table.


In the example above you:

select all columns from the rows which have a PK in the set
 (
   select the PK from all rows where the columns joined together 
matches your search text

 )

BTW, I don't know how well this performs compared to the FTS extensions.

Martin

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

Re: [sqlite] Search all collumns with LIKE at once

[fangles]

Martin Jenkins-2 wrote:
> 
> fangles wrote:
>> I'm currently searching through all columns in a table to see if any
>> match
>> the search text and the query is rather cumbersome. Is there a way to use
>> a
>> loop to go through all available columns by some means? Maybe a loop by
>> querying the schema?
>> 
>> SELECT * FROM addresses WHERE title LIKE '% + searchtext + %'
>> OR first LIKE '%  + searchtext + "%'
>> OR middle LIKE '% + searchtext + "%'
>> OR last LIKE '%   + searchtext + "%'
>> OR street LIKE '% + searchtext + "%'
>> OR suburb LIKE '% + searchtext + "%'
>> OR city LIKE '%   + searchtext + "%'
>> OR postcode LIKE '%   + searchtext + "%'
>> OR state LIKE '%  + searchtext + "%'
>> OR country LIKE '%+ searchtext + "%'
>> OR work LIKE '%   + searchtext + "%'
>> OR home LIKE '%   + searchtext + "%'
>> OR mobile LIKE '% + searchtext + "%'
>> OR company LIKE '%+ searchtext + "%'
>> OR misc1 LIKE '%  + searchtext + "%'
>> OR misc2 LIKE '%  + searchtext + "%'
>> OR email LIKE '%  + searchtext + "%'
>> OR note LIKE '%   + searchtext + "%'
>> OR category LIKE '%   + searchtext + "%'
>> OR displayas LIKE '%  + searchtext + "%'
>> OR firstentry LIKE '% + searchtext + "%'
>> OR lastedit LIKE '%   + searchtext + "%'
>> OR deleted LIKE '%+ searchtext + "%' ORDER BY displayas
>> 
>> This is a big one!!
> 
> Could you not join all the columns and search that?
> 
> select * from addresses where first||middle...deleted like searchtext;
> 
> Martin
> 
> 

Probably because I didn't know about it. Hmm, so much to learn, so little
brain
-- 
View this message in context: 
http://www.nabble.com/Search-all-collumns-with-LIKE-at-once-tf3251161.html#a9039025
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Search all collumns with LIKE at once

[fangles]

Martin Jenkins-2 wrote:
> 
> fangles wrote:
>> I'm currently searching through all columns in a table to see if any
>> match
>> the search text and the query is rather cumbersome. Is there a way to use
>> a
>> loop to go through all available columns by some means? Maybe a loop by
>> querying the schema?
> 
> If you had a PK on that table and used a view to concatenate the columns
> 
> create view v as select pk, first||...||deleted as cols from addr;
> 
> you could reduce the select to
> 
> select * from addr where pk = (select pk from v where cols like srch);
> 
> Martin
> 
> 

PK? I'm sorry but I am not familiar with that term..
-- 
View this message in context: 
http://www.nabble.com/Search-all-collumns-with-LIKE-at-once-tf3251161.html#a9038981
Sent from the SQLite mailing list archive at Nabble.com.


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