Re: [sqlite] system.data.sqlite & encryption

[Simon Slavin]

On 7 Feb 2010, at 12:21am, Sylvain Pointeau wrote:

> I already recompiled it, but integrating the encryption extension is again
> another step.
> how to be sure of that before to buy the extension ($2'000)  ?

Do what the documentation of the extension says: email the company that issues 
it and ask.  Discussion of that extension is not really allowed here.

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

Re: [sqlite] system.data.sqlite & encryption

[Sylvain Pointeau]

I already recompiled it, but integrating the encryption extension is again
another step.
how to be sure of that before to buy the extension ($2'000)  ?

Sylvain

On Sat, Feb 6, 2010 at 1:44 AM, Shane Harrelson  wrote:

> Yes.   If you're willing to compile (and possibly modify) the
> system.data.sqlite libraries for yourself, it should work.
>
> -Shane
>
> On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau <
> sylvain.point...@gmail.com
> > wrote:
>
> > I posted on this forum
> > http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx
> >
> > The answer was: "I'm afraid not.  This is an open-source project, and the
> > encryption Dr. Hipp sells is not open source."
> >
> > but as the license of system.data.sqlite is "Released to the public
> domain,
> > use at your own risk!"
> > I think I can integrate the encrypted sqlite lib, that I will buy, into
> > system.data.sqlite.
> >
> > I would just like to have a bit of guidance...
> >
> > Best regards,
> > Sylvain
> >
> > On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson 
> wrote:
> >
> > > I think you should probably ask this question on the System.data.sqlite
> > > support forums at
> > >
> > > http://sqlite.phxsoftware.com/forums
> > >
> > > I'm not familiar with enough with their ADO .NET implementation to
> answer
> > > your question.
> > >
> > > -Shane
> > >
> > >
> > > On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau <
> > > sylvain.point...@gmail.com> wrote:
> > >
> > > > Hi all,
> > > >
> > > > I am using System.data.sqlite from my .NET project,
> > > > but I am planning to access my db from C/C++ and C# / C++/CI
> > > >
> > > > The point is that I want an encryted database, and I plan to use the
> > > > extension from
> > > > http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
> > > >
> > > > how to use / implement this exension in system.data.sqlite?
> > > > or are they compatible both?
> > > >
> > > > thank you in advance for your help.
> > > >
> > > > Best regards,
> > > > Sylvain
> > > > ___
> > > > 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
> >
> ___
> 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] pdo_sqlite 3.3 or above

[Steffenhagen Kerm]

Thank you for your efforts!

Steffenhagen Kerm
kst...@visi.com



On Feb 6, 2010, at 6:12 AM, Kees Nuyt wrote:

> On Fri, 5 Feb 2010 21:49:22 -0600, Steffenhagen Kerm
>  wrote:
>
>> I cannot use the binaries at the download site for the user machine,
>> they are built for Intel (X86) , and my target machine is a Power PC
>> (ppc7400) CPUs.
>>
>> I looked for info on "php_pdo_sqlite_external" and I didn't find
>> anything helpful, and I don't know what it is or how to use it. Is it
>> part of PHP? Assuming the architecture would be correct, how would I
>> used that with PHP?
>
>
> Hm, it turns out it is a windows-only module.
> http://php.net/ChangeLog-5.php
>
> Sorry for guiding you into a dead end street.
>
>> Steffenhagen Kerm
>> kst...@visi.com
> -- 
>  (  Kees Nuyt
>  )
> c[_]
> ___
> 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] Passing Value from one query to another

[Edzard Pasma]

Op 6-feb-2010, om 18:03 heeft Eric Bohlman het volgende geschreven:

> BareFeet wrote:
>> In general, I think it's much better (performance and logic) to do
>> all you can in SQL, without passing values out of SQL results, into
>> your non-SQL code, then re-injecting back into another SQL query etc.
>
> With SQLite, that's not really going to make a difference. Since  
> it's an
> embedded library rather than an out-of-process server,
> "passing/injecting" doesn't require marshalling or network  
> overhead, so
> there's no performance hit. In terms of logic, since SQLite doesn't  
> have
> stored procedures the idea of centralizing database interactions into
> the DB itself rather than distributing the logic between applications
> isn't applicable here.


If you are in favour of centralizing processing in the database, then  
an SQLite temporary table could serve as a vehicle for passing  
results from one query to another. I hope this needs not to much  
clarification. The first query insert the parameter into the temp  
table. The other reads it back using a sub-query.

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

Re: [sqlite] Passing Value from one query to another

[Eric Bohlman]

BareFeet wrote:
> In general, I think it's much better (performance and logic) to do
> all you can in SQL, without passing values out of SQL results, into
> your non-SQL code, then re-injecting back into another SQL query etc.

With SQLite, that's not really going to make a difference. Since it's an 
embedded library rather than an out-of-process server, 
"passing/injecting" doesn't require marshalling or network overhead, so 
there's no performance hit. In terms of logic, since SQLite doesn't have 
stored procedures the idea of centralizing database interactions into 
the DB itself rather than distributing the logic between applications 
isn't applicable here.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] load_extension() and extension-functions.c

[Liam Healy]

Thank you for the Windows compilation instructions.  I have updated
the file http://sqlite.org/contrib/download/extension-functions.c?get=25
to include them, along with a small fix to the assert for squareFunc.

Liam

On Tue, Jan 19, 2010 at 4:14 AM, Oliver Peters  wrote:
> Liam Healy  writes:
>
> [...]
>>
>> >
>> >
>> > s2: extension-functions.c
>> > -
>> > add compilation instructions for windows
>>
>> I don't know how to compile in windows; if you or
>> someone has some instructions, I'll add them.
>
>
> compile instructions for Windows:
> 
>
> 1. Install MinGW (http://www.mingw.org/) and you will get the gcc (gnu 
> compiler
> collection)
>
> 2. add the path to your path variable (isn't done during the installation!)
>
> 3. compile this way:
>
> gcc -shared -I "path" -o libsqlitefunctions.so extension-functions.c
>
> (path = path of sqlite3ext.h; i.e. C:\programs\sqlite)
>
>
> greetings
> Oliver
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing Value from one query to another

[BareFeet]

In general, I think it's much better (performance and logic) to do all you can 
in SQL, without passing values out of SQL results, into your non-SQL code, then 
re-injecting back into another SQL query etc. Having said that, I'm not quite 
sure why you are doing what you're doing, so I can't attempt to offer the best 
solution.

On 07/02/2010, at 1:20 AM, personalt wrote:

> I was looking to store this whole query inside a view and just query the view 
> from outside sqlite.
> 
> Is there a way to inside a large sql statement to store a intermediate value 
> which could be used later in that query?

You could create views, something like this:

create view MySubQuery as
select kwhcost1 from applications
;

create view MyQuery as
select
monitordata_hourly.deviceaddress
,   Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh
,   Round(Sum(monitordata_hourly.ch1kwh),3)*(select * from MySubQuery) AS 
SumOfch1kwh_cost
from monitordata_hourly
where monitordata_hourly.deviceaddress=142265
and (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', 
'-30 days')
group by monitordata_hourly.deviceaddress
;

select * from MyQuery
;


or you could set up a table to hold the intermediate variables:

create table Variables
(   Name text unique
,   Value
)
;

insert or replace into Variables(Name, value)
select 'kwhcost', kwhcost1 from applications
;

select
monitordata_hourly.deviceaddress
,   Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh
,   Round(Sum(monitordata_hourly.ch1kwh),3)*(select value from Variables 
where Name = 'kwhcost') AS SumOfch1kwh_cost
from monitordata_hourly
where monitordata_hourly.deviceaddress=142265
and (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', 
'-30 days')
group by monitordata_hourly.deviceaddress
;

Tom
BareFeet

--
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml

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

Re: [sqlite] Passing Value from one query to another

[Igor Tandetnik]

personalt wrote:
> I read that page last night..  That sounded like what I wanted to do but I
> had no idea how to get my simple query to be a parameterized query. That
> is really what I was looking for help on.
> 
> select kwhcost1 from applications;
> 
> SELECT monitordata_hourly.deviceaddress,
> Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
> Round(Sum(monitordata_hourly.ch1kwh),3)*.19 AS SumOfch1kwh_cost
> FROM monitordata_hourly
> Where monitordata_hourly.deviceaddress=142265 and
> (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
> days')
> Group by monitordata_hourly.deviceaddress

So you run the first query (select kwhcost1 from applications;) and retrieve 
the KWH value into a variable in your program. Then you prepare (using 
sqlite3_prepare[_v2]) your second query with a parameter:

SELECT monitordata_hourly.deviceaddress,
Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
Round(Sum(monitordata_hourly.ch1kwh),3)* ? AS SumOfch1kwh_cost
FROM monitordata_hourly
Where monitordata_hourly.deviceaddress=142265 and
(datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
days')
Group by monitordata_hourly.deviceaddress

Note the question mark in place of hardcoded .19. You may also want to 
parameterize other hardcoded constants while you are at it. Anyway, next you 
use a suitable sqlite3_bind_* flavor to bind the parameter (sqlite3_bind_double 
in your case), several calls to sqlite3_step to iterate over the resultset, and 
finally sqlite3_reset (if you want to reuse the query) or sqlite3_finalize (if 
you are done with it).

Igor Tandetnik

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

Re: [sqlite] Passing Value from one query to another

[personalt]

I read that page last night..  That sounded like what I wanted to do but I
had no idea how to get my simple query to be a parameterized query. That
is really what I was looking for help on. 

select kwhcost1 from applications;

SELECT monitordata_hourly.deviceaddress,
Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
Round(Sum(monitordata_hourly.ch1kwh),3)*.19 AS SumOfch1kwh_cost
FROM monitordata_hourly
Where monitordata_hourly.deviceaddress=142265 and
(datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
days')
Group by monitordata_hourly.deviceaddress





Igor Tandetnik wrote:
> 
> personalt wrote:
>> I realize this works fine for this query but is there a way to to do this
>> by
>> passing the results from one query to the second?  I have some more
>> complex
>> calculations coming up where I think this would be an easier way to go
> 
> Read about parameterized queries:
> 
> http://www.sqlite.org/c3ref/bind_blob.html
> 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Passing-Value-from-one-query-to-another-tp27478736p27480189.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] any command to find last rowid in a table

[Vasanta]

I found this C function call, this solved my problem,
sqlite3_last_insert_rowid(),
I just want to append new entries to existing imported table.







On Sat, Feb 6, 2010 at 8:36 AM, Ibrahim A wrote:

> Am 05.02.2010 22:33, schrieb Vasanta:
> > Kittayya:
> >
> > My issue is, I already have imported table in the Database, there alreay
> > around 1000 records in that table where ROWID is from 1 to 1000, now
> system
> > generates new events, where ROWID again starts from beginning from 1, now
> > these new events are overwriting the earlier imported events by "REPLACE
> > INTO..", I made that change to instead REPLACE, I need INSERT, but
> now I
> > need new ROWID (I need to update at the end of previous imported records.
> I
> > don't want to overwrite original records.
> >
> >
> >
> First of all a few questions to make things clear :
>
> 1) you import your data with a script that inserts rows with existing
> rowid's ?
>
> after that :
>
> 2) you try to insert new rows with INSERT ... but it doesn't work as you
> expect because you think you need to specify a new unused rowid ?
>
> if so :
>
> a) don't classify the field rowid in your insert command because sqlite
> then generates automatically new unused values.
> b) if your script (from earlier posts) restores your database with sql
> commands (most likely) then try to avoid using rowid in that script at
> all. The default behaviour for rowid works fine especially for your
> problem with only a few thousand entries in the database
> c) if rowid is your primary key to identify your data, then you depend
> on a internal feature of the engine and you should change this with a
> user defined field called id which could also be a autoincrement field
> similar to rowid for better performanc (integer primary key autoinc ---
> look at the documentation)
>
> your problem is that you try to insert new rows with a rowid and you
> will only get performance problems when you have to search for the
> maximum value of a rowid each time you search for a new valid id. try to
> avoid this by defining your own primary key. At least don't try to
> specify a rowid value when you insert new rows.
>
> Hope this could solve your problem ;)
>  ___
> 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] Passing Value from one query to another

[Igor Tandetnik]

personalt wrote:
> Is there a way to inside a large sql statement to store a intermediate value
> which could be used later in that query?

Yes - by using a subquery, as you were shown.

Igor Tandetnik

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

Re: [sqlite] Passing Value from one query to another

[personalt]

I was looking to store this whole query inside a view and just query the view
from outside sqlite.

Is there a way to inside a large sql statement to store a intermediate value 
which could be used later in that query?


Simon Slavin-3 wrote:
> 
> 
> On 6 Feb 2010, at 1:54pm, personalt wrote:
> 
>> I realize this works fine for this query but is there a way to to do this
>> by
>> passing the results from one query to the second?  I have some more
>> complex
>> calculations coming up where I think this would be an easier way to go
> 
> Yes, you use whatever programming language you're using to execute your
> SQL queries.  That's what programming languages are for.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Passing-Value-from-one-query-to-another-tp27478736p27480046.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] Passing Value from one query to another

[Igor Tandetnik]

personalt wrote:
> I realize this works fine for this query but is there a way to to do this by
> passing the results from one query to the second?  I have some more complex
> calculations coming up where I think this would be an easier way to go

Read about parameterized queries:

http://www.sqlite.org/c3ref/bind_blob.html

Igor Tandetnik

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

Re: [sqlite] Passing Value from one query to another

[Tom Holden]

Store the results of the first query in a temporary table?

- Original Message - 
From: "personalt" 
To: 
Sent: Saturday, February 06, 2010 8:54 AM
Subject: Re: [sqlite] Passing Value from one query to another


>
> I realize this works fine for this query but is there a way to to do this 
> by
> passing the results from one query to the second?  I have some more 
> complex
> calculations coming up where I think this would be an easier way to go
>
>
>
> BareFeet wrote:
>>
>> On 06/02/2010, at 10:07 PM, personalt wrote:
>>
>>> I am just looking to pass a value from one query into a second.  Is
>>> that possible?  Can I modify the query below to get the two queries to
>>> work
>>> together so that the .19 is repalce by the kwhcost1 from the first 
>>> query?
>>>
>>> select kwhcost1 from applications;
>>>
>>> SELECT monitordata_hourly.deviceaddress,
>>> Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
>>> Round(Sum(monitordata_hourly.ch1kwh),3)*.19 AS SumOfch1kwh_cost
>>> FROM monitordata_hourly
>>> Where monitordata_hourly.deviceaddress=142265 and
>>> (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
>>> days')
>>> Group by monitordata_hourly.deviceaddress
>>
>> Simply enter your first query as a "subquery" in the second, like this:
>>
>> SELECT monitordata_hourly.deviceaddress,
>> Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
>> Round(Sum(monitordata_hourly.ch1kwh),3)*(select kwhcost1 from
>> applications) AS SumOfch1kwh_cost
>> FROM monitordata_hourly
>> Where monitordata_hourly.deviceaddress=142265 and
>> (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
>> days')
>> Group by monitordata_hourly.deviceaddress
>>
>> Tom
>> BareFeet
>>
>>  --
>> Comparison of SQLite GUI tools:
>> http://www.tandb.com.au/sqlite/compare/?ml
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> -- 
> View this message in context: 
> http://old.nabble.com/Passing-Value-from-one-query-to-another-tp27478736p27479816.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing Value from one query to another

[personalt]

I realize this works fine for this query but is there a way to to do this by
passing the results from one query to the second?  I have some more complex
calculations coming up where I think this would be an easier way to go



BareFeet wrote:
> 
> On 06/02/2010, at 10:07 PM, personalt wrote:
> 
>> I am just looking to pass a value from one query into a second.  Is
>> that possible?  Can I modify the query below to get the two queries to
>> work
>> together so that the .19 is repalce by the kwhcost1 from the first query?
>> 
>> select kwhcost1 from applications;
>> 
>> SELECT monitordata_hourly.deviceaddress,
>> Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
>> Round(Sum(monitordata_hourly.ch1kwh),3)*.19 AS SumOfch1kwh_cost
>> FROM monitordata_hourly
>> Where monitordata_hourly.deviceaddress=142265 and
>> (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
>> days')
>> Group by monitordata_hourly.deviceaddress
> 
> Simply enter your first query as a "subquery" in the second, like this:
> 
> SELECT monitordata_hourly.deviceaddress,
> Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
> Round(Sum(monitordata_hourly.ch1kwh),3)*(select kwhcost1 from
> applications) AS SumOfch1kwh_cost
> FROM monitordata_hourly
> Where monitordata_hourly.deviceaddress=142265 and
> (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
> days')
> Group by monitordata_hourly.deviceaddress
> 
> Tom
> BareFeet
> 
>  --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Passing-Value-from-one-query-to-another-tp27478736p27479816.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] any command to find last rowid in a table

[Ibrahim A]

Am 05.02.2010 22:33, schrieb Vasanta:
> Kittayya:
>
> My issue is, I already have imported table in the Database, there alreay
> around 1000 records in that table where ROWID is from 1 to 1000, now system
> generates new events, where ROWID again starts from beginning from 1, now
> these new events are overwriting the earlier imported events by "REPLACE
> INTO..", I made that change to instead REPLACE, I need INSERT, but now I
> need new ROWID (I need to update at the end of previous imported records. I
> don't want to overwrite original records.
>
>
>
First of all a few questions to make things clear :

1) you import your data with a script that inserts rows with existing 
rowid's ?

after that :

2) you try to insert new rows with INSERT ... but it doesn't work as you 
expect because you think you need to specify a new unused rowid ?

if so :

a) don't classify the field rowid in your insert command because sqlite 
then generates automatically new unused values.
b) if your script (from earlier posts) restores your database with sql 
commands (most likely) then try to avoid using rowid in that script at 
all. The default behaviour for rowid works fine especially for your 
problem with only a few thousand entries in the database
c) if rowid is your primary key to identify your data, then you depend 
on a internal feature of the engine and you should change this with a 
user defined field called id which could also be a autoincrement field 
similar to rowid for better performanc (integer primary key autoinc --- 
look at the documentation)

your problem is that you try to insert new rows with a rowid and you 
will only get performance problems when you have to search for the 
maximum value of a rowid each time you search for a new valid id. try to 
avoid this by defining your own primary key. At least don't try to 
specify a rowid value when you insert new rows.

Hope this could solve your problem ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing Value from one query to another

[BareFeet]

On 06/02/2010, at 10:07 PM, personalt wrote:

> I am just looking to pass a value from one query into a second.  Is
> that possible?  Can I modify the query below to get the two queries to work
> together so that the .19 is repalce by the kwhcost1 from the first query?
> 
> select kwhcost1 from applications;
> 
> SELECT monitordata_hourly.deviceaddress,
> Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
> Round(Sum(monitordata_hourly.ch1kwh),3)*.19 AS SumOfch1kwh_cost
> FROM monitordata_hourly
> Where monitordata_hourly.deviceaddress=142265 and
> (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
> days')
> Group by monitordata_hourly.deviceaddress

Simply enter your first query as a "subquery" in the second, like this:

SELECT monitordata_hourly.deviceaddress,
Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
Round(Sum(monitordata_hourly.ch1kwh),3)*(select kwhcost1 from applications) AS 
SumOfch1kwh_cost
FROM monitordata_hourly
Where monitordata_hourly.deviceaddress=142265 and
(datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
days')
Group by monitordata_hourly.deviceaddress

Tom
BareFeet

 --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml

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

Re: [sqlite] pdo_sqlite 3.3 or above

[Kees Nuyt]

On Fri, 5 Feb 2010 21:49:22 -0600, Steffenhagen Kerm
 wrote:

>I cannot use the binaries at the download site for the user machine,  
>they are built for Intel (X86) , and my target machine is a Power PC  
>(ppc7400) CPUs.
>
>I looked for info on "php_pdo_sqlite_external" and I didn't find  
>anything helpful, and I don't know what it is or how to use it. Is it  
>part of PHP? Assuming the architecture would be correct, how would I  
>used that with PHP?


Hm, it turns out it is a windows-only module.
http://php.net/ChangeLog-5.php

Sorry for guiding you into a dead end street.

>Steffenhagen Kerm
>kst...@visi.com
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Passing Value from one query to another

[personalt]

I have a query the calcuates number of KWH and the cost of energy used in the
last 30 days.  Right now the cost per KWH is hardcoded in the 2nd query at
.19.  What I really want to do is get the value from 
'select kwhcost1 from applications;'  store that as a variable/paramter and
pass it into the send part of the query. 

I have looked all over the place but all my google searchs return
instructions on how to pass values into a sqlite query from C/PHP/python
etc.  I am just looking to pass a value from one query into a second.  Is
that possible?  Can I modify the query below to get the two queries to work
together so that the .19 is repalce by the kwhcost1 from the first query?
 
select kwhcost1 from applications;

SELECT monitordata_hourly.deviceaddress,
Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
Round(Sum(monitordata_hourly.ch1kwh),3)*.19 AS SumOfch1kwh_cost
FROM monitordata_hourly
Where monitordata_hourly.deviceaddress=142265 and
(datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
days')
Group by monitordata_hourly.deviceaddress
-- 
View this message in context: 
http://old.nabble.com/Passing-Value-from-one-query-to-another-tp27478736p27478736.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