Re: [sqlite] How to configure any parameter to set max number of records to be added in SQLite DB

[Simon Slavin]

On 31 Aug 2011, at 4:55am, Tarun wrote:

> Is there any configurable parameter which allows us to set maximum
> number of records that is allowed to be inserted. Once this limit is
> reached, insert SQL query should fail inserting records telling max
> records have been inserted in DB table.

sqlite> CREATE TABLE testTable (id INTEGER PRIMARY KEY CHECK (id <= 5), name 
TEXT);
sqlite> INSERT INTO testTable (name) VALUES ('Frederick');
sqlite> INSERT INTO testTable (name) VALUES ('Mabel');
sqlite> INSERT INTO testTable (name) VALUES ('Cuthbert');
sqlite> INSERT INTO testTable (name) VALUES ('Maud');
sqlite> INSERT INTO testTable (name) VALUES ('Reginald');
sqlite> INSERT INTO testTable (name) VALUES ('Flora');
Error: constraint failed

You should, of course, be handling errors in your own code and since you can 
placed only one constraint on this table, you can assume that a constraint 
failure means you've reached the maximum id.

You do need to consider quite carefully what happens if a record is DELETEd, or 
whether your understanding of the data would ever allow this to happen in the 
first place.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Ivan Shmakov]

> Pete Helgren writes:

 > I may end up going this direction, at the moment I am not having much
 > luck with the conditional copy in Busybox.  Your suggestion:

 > cp -n newdatabase.db /data/newdatabase.db

 > Isn't supported in the version of Busybox that I am running.  Also
 > the script example I tried:

 > if  [ -f /data/newdatabase.db];
 > then
 > echo "Nothing to do, database exists"
 > else
 > cp newdatabase.db /data/newdatabase.db
 > fi

 > delivers the error  [:missing]

The primary token delimiter in POSIX Shell is space.  Hence, the
following line:

   if  [ -f /data/newdatabase.db];

Is understood as: “check if the file ‘/data/newdatabase.db]’
exists” (note the closing bracket), and it certainly lacks a
closing bracket for the ‘test’ (AKA ‘[’) command.

The solution would be as follows:

 - if  [ -f /data/newdatabase.db];
 + if  [ -f /data/newdatabase.db ];

OTOH, the when the ‘test’ form of the command is used, closing
bracket is not necessary, thus:

   if  test -f /data/newdatabase.db ;

Please also consider joining the news:comp.unix.shell newsgroup
(e. g., on Aioe, nntp://aioe.org/comp.unix.shell/), as there're
quite a few folks familiar with the arcane art of Unix Shell
programming.  (AFAIK, Thunderbird has the support for the
Internet News service.)

[…]

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to configure any parameter to set max number of records to be added in SQLite DB

[Igor Tandetnik]

Tarun  wrote:
> Is there any configurable parameter which allows us to set maximum
> number of records that is allowed to be inserted. Once this limit is
> reached, insert SQL query should fail inserting records telling max
> records have been inserted in DB table.

No such parameter that I know of. PRAGMA max_page_count comes close, but it 
works in terms of bytes, not records. If you really want to limit the number of 
records, you should be able to achieve this with triggers.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to configure any parameter to set max number of records to be added in SQLite DB

[Tarun]

Hi All,

Is there any configurable parameter which allows us to set maximum
number of records that is allowed to be inserted. Once this limit is
reached, insert SQL query should fail inserting records telling max
records have been inserted in DB table.

Please let me know. Waiting for response.

Thank you all.

-- 
Regards,
- Tarun Thakur
Module Lead
NEC HCL System Technologies, Noida
www.nechclst.in
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Simon Slavin]

On 31 Aug 2011, at 3:53am, Pete Helgren wrote:

> I may end up going this direction, at the moment I am not having much luck 
> with the conditional copy in Busybox.  Your suggestion:
> 
> cp -n newdatabase.db /data/newdatabase.db
> 
> Isn't supported in the version of Busybox that I am running. 

Oh, you're running BusyBox, not a standard shell.  okay, well here's a doc for 
BusyBox:



see whether cp will overwrite without any options set.  Try it out.  See what 
happens.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Jay A. Kreibich]

On Tue, Aug 30, 2011 at 08:29:06PM -0600, Pete Helgren scratched on the wall:
> The only issue I had was finding an example of how I could do all of
> what you describe below in bash script.  For example, if I put this
> in a script:
> 
> sqlite3 newdatabase.db
> 
> and save that as createdb.sh and execute it then the script never
> completes because SQLite is at the sqlite> prompt, waiting for
> commands.  Hence that option is a non-starter.

  You need to give sqlite3 a command, or it will go into interactive
  mode.  That's how the shell is designed to work.
  
  You can do this, however:

$ sqlite3 newdatabase.db .exit

  The existence of the command will cause sqlite3 to execute the
  command and quit, without going into interactive mode.  As I 
  explained before, this specific example won't actually create a
  database file, however.

  I suppose you could do something this:

sqlite3 newdatabase.db "CREATE TABLE IF NOT EXISTS ..."
sqlite3 newdatabase.db "CREATE TABLE IF NOT EXISTS ..."
...

  But that seems a bit wasteful.  If you want to do all your
  initialization in one pass, I would do something like this:

sqlite3 newdatabase.db << EOF
CREATE TABLE IF NOT EXISTS t1 ( a, b, c );
CREATE TABLE IF NOT EXISTS t2 ( d, e, f );
EOF

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Pete Helgren]

I may end up going this direction, at the moment I am not having much 
luck with the conditional copy in Busybox.  Your suggestion:


cp -n newdatabase.db /data/newdatabase.db

Isn't supported in the version of Busybox that I am running.  Also the 
script example I tried:


if  [ -f /data/newdatabase.db];
then
echo "Nothing to do, database exists"
else
cp newdatabase.db /data/newdatabase.db
fi

delivers the error  [:missing]

So I'll have to work through the scripting.  Sure would be nice to have 
something like "sqlite3 newdatabase.db .exit" work so that it would just 
create the DB and exit


Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 8/30/2011 8:14 PM, Simon Slavin wrote:

Forgot to mention: copying an existing database file also lets you set up the 
file the way you want without having to issue separate commands.  For instance, 
you could create blank tables.  Or set a specific page size.  Or include some 
sort of DRM or security check in the 'blank' file.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Pete Helgren]

The only issue I had was finding an example of how I could do all of 
what you describe below in bash script.  For example, if I put this in a 
script:


sqlite3 newdatabase.db

and save that as createdb.sh and execute it then the script never 
completes because SQLite is at the sqlite> prompt, waiting for 
commands.  Hence that option is a non-starter.


Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 8/30/2011 8:23 PM, Jay A. Kreibich wrote:

   Of course, I'm not sure what the big deal is.  By default, if you
   attempt to open an SQLite database file that does not exist, the
   system will just go ahead and create it.  This sounds like exactly
   the desired behavior.  There is no need to pre-create the file.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Jay A. Kreibich]

On Tue, Aug 30, 2011 at 09:54:21PM -0400, Igor Tandetnik scratched on the wall:
> Pete Helgren  wrote:
> > I have a need to create a database if it doesn't already exist.  The
> > obvious solution is to just use:
> > 
> > sqlite3 newdatabase.db
> > 
> > Except that it not only creates the db but also opens that db for
> > commands.  I am running this from a script so I want to just want to run
> > the command from a script so that I know the database exists before
> > issuing other commands.
> 
> Try something like
> 
> echo ".exit" | sqlite3 newdatabase.db

  Except that won't work**, since creating the database file is a lazy
  operation.  There are several ways to force the creation of a
  zero-byte file (open/commit a transaction, for example), but that can
  be done with something as simple as "touch(1)".
 
  Creating the file and writing the full database header (making it a
  recognizable SQLite file) requires putting something into the
  sqlite3_master table (e.g. creating a user-defined table).  This
  could be done with any CREATE TABLE IF NOT EXISTS... statement.


  Of course, I'm not sure what the big deal is.  By default, if you
  attempt to open an SQLite database file that does not exist, the
  system will just go ahead and create it.  This sounds like exactly
  the desired behavior.  There is no need to pre-create the file.
  
  Assuming the start-up process continues with a series of CREATE
  TABLE IF NOT EXISTS... statements, a new database will have the file
  created and defined, while an existing database will create/ignore
  the tables depending on the existing structure.



  ** Who are you, and what did you do with Igor?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Simon Slavin]

Forgot to mention: copying an existing database file also lets you set up the 
file the way you want without having to issue separate commands.  For instance, 
you could create blank tables.  Or set a specific page size.  Or include some 
sort of DRM or security check in the 'blank' file.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Simon Slavin]

On 31 Aug 2011, at 2:53am, Pete Helgren wrote:

> Thanks.  I'll add a little more info
> 
> This script is used to set up the initial DB in a programmable device that 
> will then record data to the database and the database should never be 
> replaced.  So I just figured there would be a simple way to issue the sqlite 
> commands in script.

The mechanism in SQLite which creates a database is to open one that doesn't 
exist.  There's no command or C function which just makes a database without 
opening it.  You could, of course, hack that functionality out of the source 
code but I think that's a poor solution.

> Even found an example using a createdb command, although I could never see 
> where that was an SQLite command
> 
> So, you suggest I script it like so:
> 
> if [ -f /data/newdatabase.db];
> then
> echo "Nothing to do, database exists"
> else
> cp newdatabase.db /data/newdatabase.db
> fi
> 
> I am not much of a Linux guy so the scripting might be wrong.

That would do fine.  But as a single-command alternative you could use 'cp -n':

cp -n newdatabase.db /data/newdatabase.db

the '-n' means 'don't replace an existing file'.  I tested it on my Unix box.  
I believe it's implemented in Linux, but you should definitely test it because 
I don't have Linux here.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Igor Tandetnik]

Pete Helgren  wrote:
> I have a need to create a database if it doesn't already exist.  The
> obvious solution is to just use:
> 
> sqlite3 newdatabase.db
> 
> Except that it not only creates the db but also opens that db for
> commands.  I am running this from a script so I want to just want to run
> the command from a script so that I know the database exists before
> issuing other commands.

Try something like

echo ".exit" | sqlite3 newdatabase.db

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Pete Helgren]

Thanks.  I'll add a little more info

This script is used to set up the initial DB in a programmable device 
that will then record data to the database and the database should never 
be replaced.  So I just figured there would be a simple way to issue the 
sqlite commands in script.  Even found an example using a createdb 
command, although I could never see where that was an SQLite command


So, you suggest I script it like so:

if [ -f /data/newdatabase.db];
then
echo "Nothing to do, database exists"
else
cp newdatabase.db /data/newdatabase.db
fi

I am not much of a Linux guy so the scripting might be wrong.

Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 8/30/2011 7:38 PM, Simon Slavin wrote:

On 31 Aug 2011, at 2:36am, Pete Helgren wrote:


I have a need to create a database if it doesn't already exist.  The obvious 
solution is to just use:

sqlite3 newdatabase.db

Except that it not only creates the db but also opens that db for commands.

Make yourself an empty database file and keep it somewhere safe.  When you need 
a new one just copy this existing file, and rename and/or move it to the right 
folder.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Simon Slavin]

On 31 Aug 2011, at 2:36am, Pete Helgren wrote:

> I have a need to create a database if it doesn't already exist.  The obvious 
> solution is to just use:
> 
> sqlite3 newdatabase.db
> 
> Except that it not only creates the db but also opens that db for commands.

Make yourself an empty database file and keep it somewhere safe.  When you need 
a new one just copy this existing file, and rename and/or move it to the right 
folder.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Creating a database with a script or SQLite command

[Pete Helgren]

I have a need to create a database if it doesn't already exist.  The 
obvious solution is to just use:


sqlite3 newdatabase.db

Except that it not only creates the db but also opens that db for 
commands.  I am running this from a script so I want to just want to run 
the command from a script so that I know the database exists before 
issuing other commands.


I searched around the Internet for what I thought would be an easy 
answer and didn't find one.  I am running SQLite 3.3.13 from BusyBox 1.1.3


Thanks

--
Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create custom function Sqlite Java to use in clause where

[Igor Tandetnik]

Erico Leite  wrote:
> Can I create a custom function to use in clause where?
> 
> Ex.
> select * from tbl where tbl.x myfunction '031';

No. You'll have to settle for myfunction(tbl.x, '031')

> this function will go return all register with start '0' or '03' or '031'.

This sentense doesn't make any sense to me, sorry. What's a "register" in this 
context?

Anything that starts with '03' or '031' also starts with '0'. It seems that 
your condition is equivalent to " register (whatever that is) that starts with 
'0' ".
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] writing data in wrong table

[Igor Tandetnik]

Thorsten Kersting  wrote:
> statement = "insert into parameters values('"//trim(adjustl(name))//"', 
> "//trim(adjustl(czkern))//", "//trim(adjustl(cnofel))//",
> "//trim(adjustl(cmb))//", "//trim(adjustl(ck))//", 
> "//trim(adjustl(cnmax))//", "//trim(adjustl(cbfeld))//",
> "//trim(adjustl(cenergy))//", "//trim(adjustl(cip))//");"//char(0)  
> call executeStatement(trim(statement))

Any possibility of a SQL injection attack in any of the parameters? Note that 
sqlite3_exec will happily execute multiple statements. Obligatory: 
http://xkcd.com/327/

> is it possible that this mistake is coming from the nfs?

What is the nature of the "mistake", exactly? What data ends up in what table, 
against your expectations?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Create custom function Sqlite Java to use in clause where

[Erico Leite]

Can I create a custom function to use in clause where?

Ex.
select * from tbl where tbl.x myfunction '031';

this function will go return all register with start '0' or '03' or '031'.

Thanks, sorry my bad english.

-- 
Érico Luis Barrientos Leite

Graduado em Ciência da Computação
Universidade Federal de Ouro Preto
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification about Triggers

[stefanos sofroniou]

old.rowid was an error of mine during typing; my apologies. 


Even though I have kind of managed to comprehend the logic behind of it, and 
made it partially work, I have decided to stick with a view which does what I 
want it to do. The only problem I have now is how can to update an individual 
value from a row of my choice.




From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Tuesday, August 30, 2011 3:18 PM
Subject: Re: [sqlite] Clarification about Triggers

stefanos sofroniou  wrote:
> Now what I want to do is to take two tables, (let's call them temp_01 and 
> temp_02) that would both have columns id and val, and I
> want with a trigger to add temp_01.val with temp_02.val and put their result 
> in a new table named total. 

With a trigger firing on which event? A value from which row in temp_01? Add to 
a value from which row of temp_02? Put the result into which row of total?

You talk about these tables as if they were scalars. But they potentially 
contain many rows with many values.

> I have tried something like this:
> 
> create trigger [after_insert_temp_01]
> after insert on temp_01
> begin
> update total
> set val = temp_01.val + temp_02.val
> where rowid = old.rowid;
> end;

old.rowid is not defined in an "after insert" trigger. You are inserting a new 
row, there's no old row involved.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error:library routine out of sequence

[NOCaut]

Ok I understand. And what i most do it?
-- 
View this message in context: 
http://old.nabble.com/error%3Alibrary-routine-out-of-sequence-tp32365078p32367279.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

GB schrieb am 25.08.2011 18:27:


Simon Slavin schrieb am 25.08.2011 02:00:

Had you thought of creating an explicit index on the rowid column, then running 
ANALYZE again ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


I tried that, with the same result as before. It still prefers the rowid.

regards
gerd


It seems that my answer was swallowed by the recent server issues so I 
thought I repost it. Sorry for any inconvenience.


regards
gerd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error:library routine out of sequence

[George Brink]

Yes, q.finalize() should close the previous query. But that is not the 
point here.
All query objects are build upon the same resource (database 
connection). This resource cannot be shared between two queries. They 
have to be created and killed sequentially.


CppSQLite3Query q1= db.execQuery("...");
while(q1.step()); // or q1.fetch()
q1.finalize();
somestring = db.SQLGetConfigValue("one"); // ok

CppSQLite3Query q2= db.execQuery("...");
while(q2.step()); // or q1.fetch()
otherstring = db.SQLGetConfigValue("two"); // out of sequence error
q2.finalize();


On 8/30/2011 12:41 PM, NOCaut wrote:


This line most finalizate
   q.finalize();<<

or not?

IO use in the COM object  when i open folder I do it somethink with Database

STDMETHODIMP CMyOverlayIcon2::IsMemberOf(LPCWSTR pwszPath, DWORD dwAttrib)
{
// use only select query
(!wcscmp(db.SQLGetConfigValue("Active") ...
}




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification about Triggers

[Igor Tandetnik]

stefanos sofroniou  wrote:
> Now what I want to do is to take two tables, (let's call them temp_01 and 
> temp_02) that would both have columns id and val, and I
> want with a trigger to add temp_01.val with temp_02.val and put their result 
> in a new table named total. 

With a trigger firing on which event? A value from which row in temp_01? Add to 
a value from which row of temp_02? Put the result into which row of total?

You talk about these tables as if they were scalars. But they potentially 
contain many rows with many values.

> I have tried something like this:
> 
> create trigger [after_insert_temp_01]
> after insert on temp_01
> begin
> update total
> set val = temp_01.val + temp_02.val
> where rowid = old.rowid;
> end;

old.rowid is not defined in an "after insert" trigger. You are inserting a new 
row, there's no old row involved.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error:library routine out of sequence

[Stephan Beal]

On Tue, Aug 30, 2011 at 6:41 PM, NOCaut  wrote:

>
> This line most finalizate
>  q.finalize();  <<
>
> or not?
>

it doesn't if the query throws unless the query class also calls finalize()
in its dtor.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in using overloaded functions for virtual tables?

[fbuser]

Von: "Richard Hipp" 
Gesendet: 30.08.2011 16:30:28
An: sqlite-users@sqlite.org
Betreff: [sqlite] Bug in using overloaded functions for virtual tables?

>On Tue, Aug 30, 2011 at 10:01 AM,  wrote:
>
>> The attached message matched the sqlite-users mailing list's content
>> filtering rules and was prevented from being forwarded on to the list
>> membership.
>> -- Forwarded message --
>> From: fbu...@web.de
>> To: sqlite-users@sqlite.org
>> Date: Tue, 30 Aug 2011 16:01:24 +0200 (CEST)
>> Subject: Bug in using overloaded functions for virtual tables?
>>
>> SELECT DISTINCT offsets(pages) FROM pages;
>> -or-
>> SELECT offsets(pages),count(*) FROM pages GROUP BY 1
>>
>> => error message: unable to use function offsets in the requested context
>>
>> Is this is a bug or a restriction, which is not documented? If this is a
>> restriction it should be considered as a feature request to allow using
>> virtual table functions in queries with the DISTINCT keyword or the GROUP BY
>> clause. Otherwise the bug should be fixed :-)
>>
>
>The offsets() function of FTS4 gives you the locations in each record of
>where your matching search terms are located. But you didn't specify any
>search terms - there is no MATCH operator in the WHERE clause. So what
>where you hoping to get back with these queries?
>
>--
>D. Richard Hipp

I had some troubles to send my mail to the list and somehow the first part of 
my mail got lost, when you inserted into the list, so here it is:

>I have problems with using overloaded functions which are defined for virtual 
>tables in queries, which includes the DISTINCT keyword or the GROUP BY clause.
>
>Steps to reproduce (only an example) with SQLite 3.7.7 (FTS4 needs to be 
>enabled):
>
>CREATE VIRTUAL TABLE pages USING fts4(title, body);
>INSERT INTO pages(docid, title, body) VALUES(53, 'Home Page', 'SQLite is a 
>software...');
>INSERT INTO pages(title, body) VALUES('Download', 'All SQLite source code...');

So, the problem is not related to using the offset() function of FTS4, it was 
only meant as an easy example to reproduce the problem, which I have, when I 
define a function for an own implementation of a virtual table by using 
sqlite3_overload_function and xFindFunction in the same way as offset() is 
implemented for FTS4.

___
Schon gehört? WEB.DE hat einen genialen Phishing-Filter in die
Toolbar eingebaut! http://produkte.web.de/go/toolbar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error:library routine out of sequence

[NOCaut]

This line most finalizate
  q.finalize();  <<

or not?

IO use in the COM object  when i open folder I do it somethink with Database

STDMETHODIMP CMyOverlayIcon2::IsMemberOf(LPCWSTR pwszPath, DWORD dwAttrib)
{
// use only select query
(!wcscmp(db.SQLGetConfigValue("Active") ... 
}

-- 
View this message in context: 
http://old.nabble.com/error%3Alibrary-routine-out-of-sequence-tp32365078p32365913.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

[Jean-Christophe Deschamps]

That is exactly the problem: such limits are app-specific logic, and that
3rd-party db manager cannot know about app-specific logic.


No, that is a metadata which belongs to the DB.

 Forcing this pragma to always apply would in fact hinder some cases, 
e.g. creating a backup copy of a table using CREATE TABLE ... AS 
SELECT ... could violate the space constraint.


That is the problem of the DB manager(s). Such programs already have to 
be very cautious and smart to perform complex operations on tables/DBs. 
They still can disable the pragma temporarily while they need it tobe OFF.



--
j...@antichoc.net  


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

[Stephan Beal]

On Tue, Aug 30, 2011 at 6:15 PM, Jean-Christophe Deschamps  wrote:

> I also hope this can get changed someday, as this particular pragma can
> have a devastating effect when the schema expects it to be set and you're
> using a third-party manager (not your well coded application[s]) and you
> inadvertantly forget to set it first.
>

That is exactly the problem: such limits are app-specific logic, and that
3rd-party db manager cannot know about app-specific logic. Forcing this
pragma to always apply would in fact hinder some cases, e.g. creating a
backup copy of a table using CREATE TABLE ... AS SELECT ... could violate
the space constraint.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

[Jean-Christophe Deschamps]

BTW this pragma should be applicable to a list of tables or * for all 
tables.


I realize I didn't state I was talking about recursive_triggers pragma. 
Sorry for not having made this clear in the previous post.



--
j...@antichoc.net  


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

[Jean-Christophe Deschamps]

> Most PRAGMAs, including others which modify engine behaviour like 
recursive_triggers, are not stored in the database but have to be 
restated every time you open the database file.

>

Ok, thanks, If that is the case for this PRAGMA I did not realise.

Similar to the page_count PRAGMA I would have intuitively expected 
max_page_count would not need restating every time the database file 
is open.


I also hope this can get changed someday, as this particular pragma can 
have a devastating effect when the schema expects it to be set and 
you're using a third-party manager (not your well coded application[s]) 
and you inadvertantly forget to set it first.


To the point where I wrote an auto-loadable SQLite extension, just to 
set it ON  to avoid spouting nonsense in the DB while using an external 
manager!  This is close to nonsense but my schema requires this pragma 
badly and it's way too easy to forget to set it ON everytime.


BTW this pragma should be applicable to a list of tables or * for all 
tables.


--
j...@antichoc.net  


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

[Simon Slavin]

On 30 Aug 2011, at 5:03pm, Nick wrote:

> On 30 Aug 2011, at 16:53, Simon Slavin wrote:
> 
>> Most PRAGMAs, including others which modify engine behaviour like 
>> recursive_triggers, are not stored in the database but have to be restated 
>> every time you open the database file.
> 
> Ok, thanks, If that is the case for this PRAGMA I did not realise. 

I understand.  It seems reasonable to me too that these settings might be 
preserved in the database file.

> Similar to the page_count PRAGMA I would have intuitively expected 
> max_page_count would not need restating every time the database file is open.

page_count lets the engine tell you something about the database file.  
max_page_count is the reverse: it lets you tell the engine something about how 
you want it to work from now on (until you change the setting or, as you just 
discovered, the connection is closed).  And there's even a third kind of 
PRAGMA: page_size, which lets you tell SQLite something about any new databases 
it creates but once the database is created this setting is set and can't be 
changed.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

[Nick]

On 30 Aug 2011, at 16:53, Simon Slavin wrote:

> 
> Most PRAGMAs, including others which modify engine behaviour like 
> recursive_triggers, are not stored in the database but have to be restated 
> every time you open the database file.
> 

Ok, thanks, If that is the case for this PRAGMA I did not realise. 

Similar to the page_count PRAGMA I would have intuitively expected 
max_page_count would not need restating every time the database file is open.

Nick

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

[Simon Slavin]

On 30 Aug 2011, at 4:46pm, Nick wrote:

> Using the test case below is appears SQLite is not remembering the 
> max_page_count across closing and opening a database file.

Most PRAGMAs, including others which modify engine behaviour like 
recursive_triggers, are not stored in the database but have to be restated 
every time you open the database file.

Actually, it might be worth stating that at the top of the appropriate page in 
the documentation.  Many users assume the opposite.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification about Triggers

[Black, Michael (IS)]

I found that if you used the default rowid it always gave -1 for the value.
That's why I put in it's own key.

I don't understand why this doesn't work...perhaps somebody can point out the 
error here...new.rowid contains -1.  I would think that rowid ought to be 
available after the insert.
This is using 3.7.4

create table temp_01(val float);
create table temp_02(val float);
create table total(val float);
create table row(row2 integer);
create trigger after insert on temp_01
begin
insert into total values((select new.val+temp_02.val from temp_02 where 
temp_02.rowid=new.rowid));
insert into row values(new.rowid);
end;
insert into temp_02 values(2.0);
insert into temp_01 values(1.0);
select * from total;

select * from row;
-1



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: stefanos sofroniou [stefanossofroniou...@yahoo.com]
Sent: Tuesday, August 30, 2011 9:42 AM
To: Black, Michael (IS)
Subject: EXT :Re: [sqlite] Clarification about Triggers


I did exactly what you have suggested but it does not work properly with my 
software.



It increments my id (i replaced rownum with id, the function still is the 
same), but the value is null for some reason.




From: "Black, Michael (IS)" 
To: stefanos sofroniou ; General Discussion of 
SQLite Database 
Sent: Tuesday, August 30, 2011 3:19 PM
Subject: RE:[sqlite] Clarification about Triggers


There may be a more elegant way but this works:

create table temp_01(rownum integer primary key,val float);
create table temp_02(rownum integer primary key,val float);
create table total(rownum integer primary key,val float);
create trigger after insert on temp_01
begin
insert into total values(new.rownum,(select new.val+temp_02.val from temp_02 
where temp_02.rownum=new.rownum));
end;
insert into temp_02 values(1,2.0);
insert into temp_01 values(1,1.0);
select * from total;
1|3.0
insert into temp_02 values(2,20.0);
insert into temp_01 values(2,30.0);
select * from total;
1|3.0
2|50.0



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of stefanos sofroniou [stefanossofroniou...@yahoo.com]
Sent: Tuesday, August 30, 2011 2:32 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Clarification about Triggers


Hello everyone.

I have successfully created a trigger for experimentation and I would like your 
help for implementing the concept behind of it.

My table pollaplasiasmos (which stands for multiplication in Greek, even though 
I used English characters to write it [Gree-glish]), takes 3 values:
* Quantity [int]
* amount [real(0,2)]

* VAT [real(0,2)]I have created the triggers to make the necessary 
calculations for vat_value (per item), vat_included (item price), and 
total_price (which includes vat).

Now what I want to do is to take two tables, (let's call them temp_01 and 
temp_02) that would both have columns id and val, and I want with a trigger to 
add temp_01.val with temp_02.val and put their result in a new table named 
total.

I have tried something like this:

create trigger [after_insert_temp_01]
after insert on temp_01
begin
 update total
 set val = temp_01.val + temp_02.val
 where rowid = old.rowid;
end;

I know that this is wrong of what I am doing, because there is no way to call 
the temp_02 table using this method; this is not an inner join where I can 
compare IDs and make the necessary adjustments on my code.

I am really out of ideas and if there's a link with more information that I 
should know already about it, by all means let me know because I need to learn 
these things.

Regards,

Stefanos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

[Nick]

Hi

When performing some testing I noticed that SQLite was not enforcing the 
max_page_count pragma. Using the test case below is appears SQLite is not 
remembering the max_page_count across closing and opening a database file. 
Instead of the expected 12800 from PRAGMA max_page_count; I got 1073741823.

Is anyone else affected by this?

Environment:

Ubuntu Linux 10.04
SQLite shell 3.7.6.3 downloaded from SQlite.org

The following is copy-n-paste from the command line

rm page_count_test.db 
rm: cannot remove `page_count_test.db': No such file or directory
./sqlite3 page_count_test.db
SQLite version 3.7.6.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
PRAGMA encoding = "UTF-8";
PRAGMA legacy_file_format = off;
PRAGMA auto_vacuum = 0;
PRAGMA page_size = 4096;
PRAGMA max_page_count = 12800;
12800
sqlite> PRAGMA journal_mode = WAL;
wal
sqlite> PRAGMA user_version = 20;
sqlite> 
sqlite> 
sqlite> PRAGMA page_size;
4096
sqlite> PRAGMA max_page_count;
12800
sqlite> .quit

./sqlite3 page_count_test.db
SQLite version 3.7.6.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA page_size;
4096
sqlite> PRAGMA max_page_count;
1073741823
sqlite> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] error:library routine out of sequence

[NOCaut]

when i Offen use this code i see error "library routine out of sequence".I
understand that swears on a second appeal to the already open as BD.No then
make additional requests to fetch? 

wchar_t * IconDB::SQLGetConfigValue(char *Key)
{   
CString s;
wchar_t * result;
char tmp[255];  
CppSQLite3Query q; 
try
{
strcpy(tmp,"SELECT Value FROM Config WHERE Key = \"");
strcat(tmp, Key); 
strcat(tmp, "\""); 

CppSQLite3Query q = base.execQuery( tmp ); // <<< Error
>>
result = utf8_to_unicode(q.getStringField( 0 ));
q.finalize();
}
catch(char *str){ MessageBoxA(0,str,"SQLGetConfigValue",0); }
return result;
}
-- 
View this message in context: 
http://old.nabble.com/error%3Alibrary-routine-out-of-sequence-tp32365078p32365078.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in using overloaded functions for virtual tables?

[Richard Hipp]

On Tue, Aug 30, 2011 at 10:01 AM,  wrote:

> The attached message matched the sqlite-users mailing list's content
> filtering rules and was prevented from being forwarded on to the list
> membership.
> -- Forwarded message --
> From: fbu...@web.de
> To: sqlite-users@sqlite.org
> Date: Tue, 30 Aug 2011 16:01:24 +0200 (CEST)
> Subject: Bug in using overloaded functions for virtual tables?
>
> SELECT DISTINCT offsets(pages) FROM pages;
> -or-
> SELECT offsets(pages),count(*) FROM pages GROUP BY 1
>
> => error message: unable to use function offsets in the requested context
>
> Is this is a bug or a restriction, which is not documented? If this is a
> restriction it should be considered as a feature request to allow using
> virtual table functions in queries with the DISTINCT keyword or the GROUP BY
> clause. Otherwise the bug should be fixed :-)
>

The offsets() function of FTS4 gives you the locations in each record of
where your matching search terms are located.  But you didn't specify any
search terms - there is no MATCH operator in the WHERE clause.  So what
where you hoping to get back with these queries?



>
> Thanks.
>
> Schon gehört? WEB.DE hat einen genialen Phishing-Filter in die
> Toolbar eingebaut! 
> *http://produkte.web.de/go/toolbar*
>
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comments in files intended for .read

[Simon Slavin]

On 30 Aug 2011, at 2:47pm, Richard Hipp wrote:

> On Tue, Aug 30, 2011 at 9:25 AM, Simon Slavin  wrote:
> 
>> Any problems with multi-line
>> quoted text strings containing comments ?  Anything else anyone wants to
>> warn me about ?  I don't actually need to use the '/*' format, but if anyone
>> wants to talk about that, that's cool too.
> 
> Strings that contain comment-like characters are not altered in any way -
> the comment-like characters are preserved.  The sqlite3_complete() routine
> takes care of this on behalf of the shell.

So ...

a) comment starters outside of statements introduce comments
b) comment starters inside of statements but outside of strings introduce 
comments
c) comment starters inside of strings are treated as part of the string

That checks with what I got when I experimented.  And I can work with that.  
Thanks.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comments in files intended for .read

[Richard Hipp]

On Tue, Aug 30, 2011 at 9:25 AM, Simon Slavin  wrote:

> I'm writing an app which generates text files intended for reading by the
> shell tool's '.read' command.  We're using SQL text files because I'm
> writing a design which needs to be completely open, understandable, and
> easily hackable by third parties.
>
> I want to include comments in my SQL files.  The following page
>
> 
>
> suggests I can use lines that begin with two consecutive "-" characters
> (ASCII 0x2d).  I have done some exploration to find out how the current
> version of the shell tool handles '--' inside quotes, inside
> statements, etc. but since it's not documented I can't be sure future
> versions will last the same way.
>
> Is anyone aware of any problems with this ?  Any problems with multi-line
> quoted text strings containing comments ?  Anything else anyone wants to
> warn me about ?  I don't actually need to use the '/*' format, but if anyone
> wants to talk about that, that's cool too.
>

Strings that contain comment-like characters are not altered in any way -
the comment-like characters are preserved.  The sqlite3_complete() routine
takes care of this on behalf of the shell.


>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Comments in files intended for .read

[Simon Slavin]

I'm writing an app which generates text files intended for reading by the shell 
tool's '.read' command.  We're using SQL text files because I'm writing a 
design which needs to be completely open, understandable, and easily hackable 
by third parties.

I want to include comments in my SQL files.  The following page



suggests I can use lines that begin with two consecutive "-" characters (ASCII 
0x2d).  I have done some exploration to find out how the current version of the 
shell tool handles '--' inside quotes, inside statements, etc. but 
since it's not documented I can't be sure future versions will last the same 
way.

Is anyone aware of any problems with this ?  Any problems with multi-line 
quoted text strings containing comments ?  Anything else anyone wants to warn 
me about ?  I don't actually need to use the '/*' format, but if anyone wants 
to talk about that, that's cool too.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] writing data in wrong table

[Black, Michael (IS)]

NFS is not a good idea as you have discovered.
http://www.sqlite.org/faq.html#q5

Here's what I'd do for starters.  Have your grid processes write their SQL 
statements (or data) to a flat file...one file for each grid member.

Then have a process that reads all those files using pipes like this, each pipe 
inside it's own thread.  And then you only use 1 database connection shared 
amongst the threads.

FILE *data1 = popen("tail -F gridfile1","r");

This way your grid processes won't have any wait states for the database and 
your database can be on NFS too (local is preferable though).

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Thorsten Kersting [thorsten.kerst...@itp1.uni-stuttgart.de]
Sent: Tuesday, August 30, 2011 7:39 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] writing data in wrong table


this is not possible. i have to run the programm on the bw-grid cluster,
else it is not fast enough.
if i try the program on my work-pc it works fine. only the mass
parallel-starting brings the mistake.

On 08/30/2011 02:34 PM, Simon Slavin wrote:
> On 30 Aug 2011, at 1:31pm, Thorsten Kersting wrote:
>
>> is it possible that this mistake is coming from the nfs?
> Try your program operating on a local file instead of one on a volume mounted 
> using NFS.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] writing data in wrong table

[Thorsten Kersting]

this is not possible. i have to run the programm on the bw-grid cluster, 
else it is not fast enough.
if i try the program on my work-pc it works fine. only the mass 
parallel-starting brings the mistake.


On 08/30/2011 02:34 PM, Simon Slavin wrote:

On 30 Aug 2011, at 1:31pm, Thorsten Kersting wrote:


is it possible that this mistake is coming from the nfs?

Try your program operating on a local file instead of one on a volume mounted 
using NFS.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] writing data in wrong table

[Simon Slavin]

On 30 Aug 2011, at 1:31pm, Thorsten Kersting wrote:

> is it possible that this mistake is coming from the nfs?

Try your program operating on a local file instead of one on a volume mounted 
using NFS.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] writing data in wrong table

[Thorsten Kersting]

i wouldn believe it if i hadnt seen it

my statements are the following (it is fortran code, and the function 
executeStatement is c++ where i call sqlite3_exec)


statement = "create table parameters(name varchar(128), zkern integer, eanz integer, 
nmax integer, k integer, m integer, bfeld double, energy double, intpoints 
integer);"//char(0)
call executeStatement(trim(statement))

statement = "insert into parameters values('"//trim(adjustl(name))//"', "//trim(adjustl(czkern))//", "//trim(adjustl(cnofel))//", 
"//trim(adjustl(cmb))//", "//trim(adjustl(ck))//", "//trim(adjustl(cnmax))//", "//trim(adjustl(cbfeld))//", 
"//trim(adjustl(cenergy))//", "//trim(adjustl(cip))//");"//char(0)
call executeStatement(trim(statement))

statement = "create table quantumnumbers(name varchar(128), m integer, nu 
integer);"//char(0)
call executeStatement(trim(statement))

statement = 'insert into quantumnumbers values("'//trim(adjustl(name))//'", 
'//trim(cm)//', '//trim(cnu)// ");"//char(0)
call executeStatement(trim(statement))

statement = "create table coefficients(name varchar(128), xj double, tvec double, 
pvec double);"//char(0)
call executeStatement(trim(statement))

statement = 'insert into coefficients values("'//trim(adjustl(name))//'", 
'//trim(cxj)//', '//trim(ctvec)//', '//trim(cpvec)//");"//char(0)
call executeStatement(trim(statement))


in between these statements i only cast variables into characters. is it 
possible that this mistake is coming from the nfs? i start different processes 
which have nothing to do with one another. could the nfs locking fail, so that 
one process writes into a table another process wants to write?

Thorsten Kerstinghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>>  wrote:

/  i have one database, with three tables. My programm (c++) tries to

/>/  create the tables every time it writes into them, but that shouldnt do
/>/  any harm.
/>/  the problem is now, that i find data from one table written into another
/>/  table.
/
With all due respect, I find it difficult to believe. If you execute a 
statement like

INSERT INTO TableX VALUES(...);

this statement may succeed and insert a row into TableX, or it may fail and not 
insert any rows. Under no circumstances can it insert a row into some other 
table, say TableY.

Perhaps you are building the statement on the fly, choosing the table name 
dynamically. Or perhaps you are renaming tables with ALTER TABLE statement, 
after some rows have already been inserted. Either way, look for problems in 
your code. If you still have difficulty, post here a reasonably small 
self-contained sample that reproduces the issue.
--
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification about Triggers

[Black, Michael (IS)]

There may be a more elegant way but this works:



create table temp_01(rownum integer primary key,val float);

create table temp_02(rownum integer primary key,val float);

create table total(rownum integer primary key,val float);

create trigger after insert on temp_01

begin

insert into total values(new.rownum,(select new.val+temp_02.val from temp_02 
where temp_02.rownum=new.rownum));

end;

insert into temp_02 values(1,2.0);

insert into temp_01 values(1,1.0);

select * from total;

1|3.0

insert into temp_02 values(2,20.0);

insert into temp_01 values(2,30.0);

select * from total;

1|3.0

2|50.0







Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of stefanos sofroniou [stefanossofroniou...@yahoo.com]
Sent: Tuesday, August 30, 2011 2:32 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Clarification about Triggers

Hello everyone.

I have successfully created a trigger for experimentation and I would like your 
help for implementing the concept behind of it.

My table pollaplasiasmos (which stands for multiplication in Greek, even though 
I used English characters to write it [Gree-glish]), takes 3 values:
* Quantity [int]
* amount [real(0,2)]

* VAT [real(0,2)]I have created the triggers to make the necessary 
calculations for vat_value (per item), vat_included (item price), and 
total_price (which includes vat).

Now what I want to do is to take two tables, (let's call them temp_01 and 
temp_02) that would both have columns id and val, and I want with a trigger to 
add temp_01.val with temp_02.val and put their result in a new table named 
total.

I have tried something like this:

create trigger [after_insert_temp_01]
after insert on temp_01
begin
 update total
 set val = temp_01.val + temp_02.val
 where rowid = old.rowid;
end;

I know that this is wrong of what I am doing, because there is no way to call 
the temp_02 table using this method; this is not an inner join where I can 
compare IDs and make the necessary adjustments on my code.

I am really out of ideas and if there's a link with more information that I 
should know already about it, by all means let me know because I need to learn 
these things.

Regards,

Stefanos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Clarification about Triggers

[stefanos sofroniou]

Hello everyone.

I have successfully created a trigger for experimentation and I would like your 
help for implementing the concept behind of it.

My table pollaplasiasmos (which stands for multiplication in Greek, even though 
I used English characters to write it [Gree-glish]), takes 3 values:
* Quantity [int]
* amount [real(0,2)]

* VAT [real(0,2)]I have created the triggers to make the necessary 
calculations for vat_value (per item), vat_included (item price), and 
total_price (which includes vat).

Now what I want to do is to take two tables, (let's call them temp_01 and 
temp_02) that would both have columns id and val, and I want with a trigger to 
add temp_01.val with temp_02.val and put their result in a new table named 
total.

I have tried something like this:

create trigger [after_insert_temp_01]
after insert on temp_01
begin
 update total
 set val = temp_01.val + temp_02.val
 where rowid = old.rowid;
end;

I know that this is wrong of what I am doing, because there is no way to call 
the temp_02 table using this method; this is not an inner join where I can 
compare IDs and make the necessary adjustments on my code.

I am really out of ideas and if there's a link with more information that I 
should know already about it, by all means let me know because I need to learn 
these things.

Regards,

Stefanos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users