look up the insert or replace statement, http://www.sqlite.org/lang_insert.html
--- On Wed, 6/3/09, liubin liu <7101...@sina.com> wrote:
From: liubin liu <7101...@sina.com>
Subject: [sqlite] how to compose the sql sentence?
To: sqlite-users@sqlite.org
Date: Wednesday, June 3, 2009, 2:05 AM
th
the first step is to tell if there is the data in the table.
if the answer is not, I want to insert a row of data into the table
if the answer is yes, I need to update the row of data acccording to the
data inputting from me.
--
View this message in context:
http://www.nabble.com/how-to-compose
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Sambasivarao Vemula wrote:
> I want to encrypt a perticular field in sqlite3 database.
You can use the SQLite Encryption Extension which will encrypt the whole
database. This is by far the easiest approach and you won't have to
worry about accide
Hi,
I want to encrypt a perticular field in sqlite3 database.
For Example I want encrypt a password field in a table .
Is there any special commands or technics for this encryption.
Please let me know is there any solution for this .
Thanks and Regards
Samba
DISCLAIMER
==
This e-mail
Nikolaus Rath wrote:
> "Igor Tandetnik" writes:
>> Nikolaus Rath wrote:
>>> How can I determine the rowid of the last insert if I am accessing
>>> the db from different threads? If I understand correctly,
>>> last_insert_rowid() won't work reliably in this case.
>>
>> Last inserted rowid is maint
Nuno Lucas writes:
> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote:
>> Hello,
>>
>> How can I determine the rowid of the last insert if I am accessing the
>> db from different threads? If I understand correctly,
>> last_insert_rowid() won't work reliably in this case.
>
> It should work if
Hello,
I am pretty sure that at some point I have read if the following code
might bring me into trouble:
cur1 = conn.get_cursor()
cur2 = conn.get_cursor()
res = cur1.execute("SELECT id FROM data WHERE enabled == 1")
for name in res:
cur2.execute("UPDATE data SET enabled = 0 WHERE id=?", nam
"Igor Tandetnik" writes:
> Nikolaus Rath wrote:
>> How can I determine the rowid of the last insert if I am accessing the
>> db from different threads? If I understand correctly,
>> last_insert_rowid() won't work reliably in this case.
>
> Last inserted rowid is maintained per connection. Do your
Am Montag, den 01.06.2009, 07:38 -0400 schrieb Igor Tandetnik:
> Oliver Peters wrote:
> > After an UPDATE in a record I want the update time stored in a column
> > of this record - the problem is that the trigger I use doesn't work
> > only in this record but in all others
> >
> > CREATE TRIGGER IF
Hi,
thanks. I'll investigate this possibility.
John Stanton schrieb:
> Something to investigate is to use an AVL tree structure with rowids as
> the pointers. It would stay balanced and you could present family trees
> quite simply as well as use SQL to extract data on individuals and sets
>
Hi,
> If you don't want to update, but you do want to query for entire
> subtrees, do give nested sets more consideration.
But as Jay pointed out: Nested sets only work with one parent. Do they?
>
> The best encoding for intervals I've yet seen is here:
> http://arxiv.org/pdf/0806.3115v1
>
> Bes
Something to investigate is to use an AVL tree structure with rowids as
the pointers. It would stay balanced and you could present family trees
quite simply as well as use SQL to extract data on individuals and sets
of individuals.
Jay A. Kreibich wrote:
> On Tue, Jun 02, 2009 at 11:16:20PM +0
On Tue, Jun 2, 2009 at 10:45 PM, Jan wrote:
> Sounds good. I think I try that. Although updating is usually not
> necessary (once you have a mother/father its usually difficult to get
> rid of/update them .-) I read that there is problem with queries that go
> deeper in grand-grand-.. parents str
A good tool ltries to be "bette"r, not "easier". Libraries compiled
from C are compatible with C++. In general optimization is better with
C compilers and the support libraries are more compact and effcient.
Producing Sqlite in well written and documented ANSI C was a very sound
decision.
I
>> - adjacency list (not very difficult to understand)
>
> Also easy to work with two parents, just have a "father" column and a
> "mother" column.
>
> Adjacency lists are quick to update, but many queries can't be done
> in (standard) SQL by itself. That usually isn't a problem, and it
On Tue, Jun 02, 2009 at 11:16:20PM +0200, Jan scratched on the wall:
> Hi,
>
> I am planning a database for animal breeding. I need to store the
> relations between individuals and therefore I have to build something
> like a tree structure. But of course with two parents (There wont be
> clone
Hi,
I am planning a database for animal breeding. I need to store the
relations between individuals and therefore I have to build something
like a tree structure. But of course with two parents (There wont be
cloned animals in the database .-) afaik)
I read a little bit about
- adjacency list
I think you have to factor the age of SQLite into that explanation
as well. I think the first versions of SQLite were released about 10
years ago, at which point C++ compilers were even more non-standard
than they are today. Then, once it's functional and stable in C, why
rewrite it?
On Tue,
On Tue, 2 Jun 2009 12:28:31 -0700 (PDT), Boris Ioffe
wrote:
>
> Kees,
> Thank you very much for quick prototype. I will use single
> quotes from now on. It turns out Igor was right.
> I had another trigger.
Yes, always read Igor's replies first ;)
Triggers are very powerful, yet tricky. Neve
On Tue, 2 Jun 2009 21:30:51 +0200, Sylvain Pointeau
wrote:
>... because I experienced C++ to be easier with the classes and resource
>management via the destructor.I was just wondering why C++ is not used?
>
>was it for a performance issue?
>or a compiler issue?
What Virgilio said:
: Because th
... because I experienced C++ to be easier with the classes and resource
management via the destructor.I was just wondering why C++ is not used?
was it for a performance issue?
or a compiler issue?
or anything else?
I just read the Linus Torvalds comment on the C++ for Git
What do you think?
Thanks for asking your crystal ball. it works rather well.
--- On Tue, 6/2/09, Igor Tandetnik wrote:
> From: Igor Tandetnik
> Subject: Re: [sqlite] Before Update trigger question
> To: sqlite-users@sqlite.org
> Date: Tuesday, June 2, 2009, 2:48 PM
> Boris Ioffe
> wrote:
> > --- On Tue, 6/2/0
Kees,
Thank you very much for quick prototype. I will use single quotes from now on.
It turns out Igor was right.
I had another trigger
CREATE TRIGGER insert_players_timeStamp AFTER INSERT ON players
BEGIN
UPDATE players SET create_ts = DATETIME('NOW', 'localtime')
WHERE rowid = new.rowi
Boris Ioffe wrote:
> --- On Tue, 6/2/09, Igor Tandetnik
> wrote:
>
>> From: Igor Tandetnik
>> Subject: Re: [sqlite] Before Update trigger question
>> To: sqlite-users@sqlite.org
>> Date: Tuesday, June 2, 2009, 2:11 PM
>> Boris Ioffe
>> wrote:
>>> This is my first question on this mail list. I n
Very intrigued. You ruined some sql foundational priciples I lived upon. How
is it possible for insert statement to update? Even replace is (delete on
constraint then insert). Can you elaborate your answer a bit ?
THanks,
Boris
--- On Tue, 6/2/09, Igor Tandetnik wrote:
> From: Igor Tand
On Tue, 2 Jun 2009 09:38:18 -0700 (PDT), Boris Ioffe
wrote:
>
>Hello Gang,
>This is my first question on this mail list. I noticed that BEFORE UPDATE
>trigger goes off even for insert statements.
>
>My example:
>CREATE TRIGGER validate_players_update BEFORE UPDATE ON players
>WHEN (ne
Boris Ioffe wrote:
> This is my first question on this mail list. I noticed that BEFORE
> UPDATE trigger goes off even for insert statements.
My crystal ball shows you have an INSERT trigger that performs an
UPDATE.
Igor Tandetnik
___
sqlite-users
Some further question regarding FTS3.
Am I correct that a doclist of a certain term is never split onto two blocks
(BLOBs)?
Can we somehow limit the size of such BLOBs?
I did some tests where I inserted Millions of addresses into FTS3 and all
contained a certain term.
I ended up with some Blobs
Hello Rogrigo,
I'll take a shot at a response to this, bearing in mind there are quite a
few salient details you haven't
yet provided in this post.
--As a principle, I'd recommend keeping your solutions architecture as
simple as possible,
and use either PostgreSQL or SQLite, but not both, barri
On 2 Jun 2009, at 5:33pm, Rodrigo Faccioli wrote:
> mpi
Which of the many things called 'mpi' are you talking about ? A URL
will be good.
> Is it possible to employ sqlite in mpi application? Example: I have a
> computer which is a dual-core and my program works with mpi. Its
> goal is to
Hello Gang,
This is my first question on this mail list. I noticed that BEFORE UPDATE
trigger goes off even for insert statements.
My example:
CREATE TRIGGER validate_players_update BEFORE UPDATE ON players
WHEN (new.role in (1,2) and
(select count(*) from players where ta
Hello,
I'm very new user about SQLite. I'm working with Structural Bioinformatics
and everybody knows that this area requires a lot of computational
resources. So, I'm developing an Evolutionary Algorithms (EA) for some
protein analysis (I don't know what will do exactly, because I've just
started
Why wuld you want to do such a thing?
Sylvain Pointeau wrote:
> Hello,
> I would like to know if someone already though about to introduce C++ in
> SQLite?
> I just think about a minimal subset of C++ that will not make any
> performance penalty
> (like C with classes)
>
> is it a performance issu
On Mon, Jun 01, 2009 at 08:56:57PM -0700, Roger Binns wrote:
> Dennis Cote wrote:
> > Do you have a list of such changes that should be implemented in the
> > next breaking release of SQLite?
>
> I assume you are talking about a major release (ie SQLite v4 not 3.7).
>
> > I'm thinking of things
Because there are many platforms that sqlite runs (and can run at some time)
that doesn't have a C++ compiler available, but they always have a C
compiler.
[]'s
On Tue, Jun 2, 2009 at 12:33, Sylvain Pointeau
wrote:
> I didn't mean to program with sqlite in C++ ...
> I just meant to introduce C+
2009/6/2 Kees Nuyt :
> On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt
> wrote:
>>
>>Let me explain the complete picture so someone can help me.
>>
>>I develop a wrapper around sqlite that tracks the changed records and
>>than save the changes to the database by building and executing a SQL
>>query
I figure the only reasons it is written in C is for portability and
Assembler programming is a bitch!
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of P Kishor
Sent: Tuesday, June 02, 2009 10:36 AM
To: General Discussion of SQLi
I have used sqlite on a similar platform. I use the GHS Integrity operating
system. With a few tweaks I was able to get it to run well. The flash will
keep things on the slower side, but I see my inserts (with idexes) taking a
little as 0.05 seconds. Queries of 250 out of 10K sorted taking less
On Tue, Jun 2, 2009 at 10:33 AM, Sylvain Pointeau
wrote:
> I didn't mean to program with sqlite in C++ ...
> I just meant to introduce C++ inside SQLite instead of C...
> I would like to understand why SQLite is programmed only in C
Definitively only DRH can answer. My speculation is -- beca
I didn't mean to program with sqlite in C++ ...
I just meant to introduce C++ inside SQLite instead of C...
I would like to understand why SQLite is programmed only in C
Cheers,
Sylvain
On Tue, Jun 2, 2009 at 5:15 PM, wrote:
> One of the best and worst things about C++ is its ability to us
On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt
wrote:
>2009/6/2 J. King
>>
>> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt
>> wrote:
>>
>> > I'm trying to set the conflict resolution of an entire transaction by
>> > using the ON CONFLICT clause without success.
>> >
>> > [...]
>> >
>> > Is t
One of the best and worst things about C++ is its ability to use C code
(almost) transparently. Best because it allows you to use things like
SQLite seamlessly in your C++ code. Worst because it allows some
programming idioms that are in opposition to good object oriented
design. But let's concen
2009/6/2 Sylvain Pointeau :
> Hello,
> I would like to know if someone already though about to introduce C++ in
> SQLite?
> I just think about a minimal subset of C++ that will not make any
> performance penalty
> (like C with classes)
Prob good idea to look through these:
http://www.sqlite.org/cv
I have used sqlite for an embedded application using c++. The sqlite
library is all in c and compiles on its own. I then created several classes
that use the sqlite api. The first is a DbHandler class. This opens the
database and provides the public accessors to the rest of application. I
then
Hello,
I would like to know if someone already though about to introduce C++ in
SQLite?
I just think about a minimal subset of C++ that will not make any
performance penalty
(like C with classes)
is it a performance issue?
is it a deployment/compiler issue?
or any issue?
Please don't make any agg
thanx a lot dude
Pavel Ivanov-2 wrote:
>
> If you have unique index on stock_tab.prod_batch_code then you can
> re-write your trigger as this:
>
> INSERT OR REPLACE INTO stock_tab
> (stock_id, prod_batch_code, stock_qty, stock_date)
> SELECT new.purchase_id+2, new.prod_batch_c
If prod_batch_code is not a unique key (which is surprising as you may be
updating more than one row), we can still write a pseudo INSERT OR REPLACE in
the form of both an update and an insert statement. The update can go
unchanged. The insert should not use values () but a query that only yield
2009/6/2 J. King
>
> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt
> wrote:
>
> > I'm trying to set the conflict resolution of an entire transaction by
> > using the ON CONFLICT clause without success.
> >
> > [...]
> >
> > Is there a way to set the conflict resolution for an entire transaction
I use the following sql
INSERT INTO malware (file, location, md5, size, sig, sig_name, cnt,
clam_result, date_found, date_removed, ref) VALUES
('Setup.exe-IRAD0n', '/Users/tshaw/malware/Setup.exe-IRAD0n',
'1186b3a97de73f924dcfb12cba0bb1bf', 15360, '', '', 1,
'/Users/tshaw/virus_archive/Setup.e
If you have unique index on stock_tab.prod_batch_code then you can
re-write your trigger as this:
INSERT OR REPLACE INTO stock_tab
(stock_id, prod_batch_code, stock_qty, stock_date)
SELECT new.purchase_id+2, new.prod_batch_code,
new.purchase_qty + ifnull(b.stock_qty
On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt
wrote:
> I'm trying to set the conflict resolution of an entire transaction by
> using the ON CONFLICT clause without success.
>
> [...]
>
> Is there a way to set the conflict resolution for an entire transaction?
Such a thing is not possible. Y
On Tue, 2 Jun 2009 08:40:01 -0300, Karl Brandt
wrote:
>I'm trying to set the conflict resolution of an entire transaction by
>using the ON CONFLICT clause without success.
>
>I'm using the following syntax:
>
>BEGIN ON CONFLICT ROLLBACK;
>INSERT INTO TableX (Id) Values (1);
>INSERT INTO TableX (I
guys i ll clarify the problem
this is the purchase table here purchase id is PK
purchase_id prod_batch_code vendor_type_code purchase_qty purchase_date
--- ---
---
11000 1 100
I'm trying to set the conflict resolution of an entire transaction by
using the ON CONFLICT clause without success.
I'm using the following syntax:
BEGIN ON CONFLICT ROLLBACK;
INSERT INTO TableX (Id) Values (1);
INSERT INTO TableX (Id) Values (2);
INSERT INTO TableX (Id) Values (3);
COMMIT;
But
thanx for ur reply dude.. but its showing an error " no such function:
NULL_IF"
Edzard Pasma wrote:
>
> Hello, you are cleverer than you think. Your initial idea to use INSERT OR
> REPLACE might look like:
>
> INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date)
Sorry, this was written down without testing. I see now that prod_batch_code
must be the primary key, instead of stock_id, for the REPLACE to work as
expected. Then some other expression must be used to fill stock_id, e.g.
IF_NULL (s.stock_id, 29). I also see that this message crosses Kees N
On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew
wrote:
>
>hey thanx for the reply... u leave the things happening inside.. wat i jus
>wanna do is i wanna insert a new row to a table
>the table will be like this
>stock_id PKproduct_id FK quantitystock_date
>1
Hello, you are cleverer than you think. Your initial idea to use INSERT OR
REPLACE might look like:
INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty,
stock_date)
SELECT
s.stock_id,
p.prod_batch_code,
IF_NULL (s.stock_qty, 0) + p.purchase_qty
DATETIME('NOW
Hi,
as far as I know, you cannot do what you want to do in pure SQL.
However, perhaps someone cleverer can contradict me.
You could first execute the update statement, check if there was a row
which was updated using sqlite3_changes() (see
http://www.sqlite.org/capi3ref.html#sqlite3_changes),
hey thanx for the reply... u leave the things happening inside.. wat i jus
wanna do is i wanna insert a new row to a table
the table will be like this
stock_id PKproduct_id FK quantitystock_date
1 10001028-05-2009
10001
Hi,
what language is this? it certainly is not SQL or a "query".
I suspect that you can not use "insert or replace" (see
http://www.sqlite.org/lang_insert.html), because you look first for a
record with prod_batch_code=1000, and if you do not find it you insert
one with prod_batch_code = 1003.
hi am new to SQLite can anybody please tell me how this query can be solved
in SQLite?
IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE prod_batch_code=1000)
UPDATE stock_tab
SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab WHERE
oduct_batch_code=1000 )
62 matches
Mail list logo