Good day to you all,
I am very inexperienced with SQlite and any help will be appreciated.
I want to know if I can use indirect referencing within the SQL language
as per SQlite standards. I will give an example.
If I create a table as illustrated below.
CREATE TABLE Testing (
ID INTEGER
On 1 Nov 2011, at 10:25am, Stander, Bertus (Pretoria) wrote:
> If I create a table as illustrated below.
>
> CREATE TABLE Testing (
>
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
>
> Tbl_Name VARCHAR (45));
In this example you have a TABLE called 'Testing' and a COLUMN called
'Tbl_Name'.
>
Thanks for the response Simon.
I am working with field loggers where the field workers are capturing
different assets using a Trimble mobile device.
I have more than 1200 individual tables, each table holding the values
of a unique asset.
Living in South Africa where bandwidth is a myth in some a
I am using sqlite 3.7.8.
My custom VFS xDelete method returned SQLITE_NOTFOUND in a case when the
file to be deleted was not found, and this worked fine for older
versions (probably prior to 3.7.x where the write ahead log was
introduced), particularly for 3.6.10.
But now the sqlite core sys
On 1 Nov 2011, at 11:44am, Stander, Bertus (Pretoria) wrote:
> That is one aspect. I also want to use minimal triggers to identify
> certain abnormalities and perform certain processes. This I want to keep
> Central and not writing triggers for all 1200 tables. So key to a
> solution I was thinki
Pete wrote:
> Thanks. I guess I'd like to confirm just where column aliases can
> be referenced. I think they cannot be referenced within the list of column
> names in which they are defined, and they can be referenced in any other
> clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY,
On 1 Nov 2011, at 12:26pm, Igor Tandetnik wrote:
> Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and
> HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON
> clauses, but again, such usage is non-standard (though very convenient at
> times).
Don V Nielsen wrote:
> I need help with a complex UPDATE. I want to update each row in a table,
> calculating an average, and then apply that value back into a column of the
> same row. Is this possible with Sqlite? Below is code that should work
> with SqlServer; its UPDATE supports a FROM sta
It's one of the best programs I have tested myself. I have even suggested to
Bogdan to add forms to make it even a better application for covering GUI needs
for complex database like in my case where I have to deal with stock taking and
cartridges. I hope he do something about it because honestl
SQLite version 3.7.9 is now available on the primary and on the backup websites:
http://www.sqlite.org/
http://www2.sqlite.org/
http://www3.sqlite.org/
Version 3.7.9 is a periodic maintenance release. Upgrading from versions
3.7.6.3 and later is optional. Upgrading from prior versi
Thanks, Igor. My original macro did replicate the where in the sub-select.
I temporarily got rid of them in my testing because it was so messy and I
didn't know how to get the update working.
I will give this a shot, and thanks for time and effort.
On Tue, Nov 1, 2011 at 7:45 AM, Igor Tandetnik
Thanks Simon,
The response I am giving here is to assist others in deciding whether
they can use SQlite.
I understand Triggers reasonably well as I am using it continuously very
successfully.
The 1200 tables in the SQlite database has no impact on performance as
every transaction executed on a s
Bertus --
The only way to reference a Variable Table Name would
be to create a Dynamic SQL Statement at runtime, prep
the Dynamic Statement then exec.
This would be true for all implementations of SQL that
I am aware of ...
-- kjh
Stander, Bertus (Pretoria) wrote, On 11/01/2011 05:25 AM:
Goo
Hmmm...how hard would it be allow one to bind the table name with prepare?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org
On 1 Nov 2011, at 2:01pm, Black, Michael (IS) wrote:
> Hmmm...how hard would it be allow one to bind the table name with prepare?
If you are in a position to bind the table name, does that not mean you could
just merge all the tables together ?
Simon.
__
Boy Howdy !
I've wished for that capability just about every time I've had to generate
Dynamic SQL Statements to execute a common query against varying Table Names :)
-- kjh
Black, Michael (IS) wrote, On 11/01/2011 09:01 AM:
Hmmm...how hard would it be allow one to bind the table name with pr
Though you probably COULD merge them all together there are situations where
you don't want to. Just like his original question.
Imagine you have a bunch of tables of consumer products split by type. Yes,
you could stick it all in one humogous database...but...
The split allows you to reduc
On Tue, Nov 01, 2011 at 02:01:30PM +, Black, Michael (IS) scratched on the
wall:
> Hmmm...how hard would it be allow one to bind the table name with prepare?
Generally, "very."
Or, rather, it would be fairly straight forward if you're willing
to skip the query optimization step and d
On Fri, Oct 28, 2011 at 06:42:06PM +0200, Tobias Sj??sten scratched on the wall:
> I have a table:
>
> CREATE TABLE t
> (
> i INT,
> g VARCHAR(1),
> v INT
> );
> But when I group it by 'g' it completely disregards the ordering:
>
> > SELECT g,v FROM t GROUP BY g ORDER BY v ASC;
> a|3
> b|3
>
>
On Sun, Oct 30, 2011 at 05:08:51PM +0400, ??? scratched on the
wall:
> Default column values conflict with not null option.
By default, yes.
The default default value for all columns is NULL. If you add a
NOT NULL constraint and do not re-define the default value using a
DE
Sometimes queries using < and > will include values that are equal. Here is an
example.
SELECT id,base,thick FROM test_col WHERE base > 281.01 AND base - thick <
283.11;
Id|base|thick
89|281.04|0.03
90|282.09|1.05
91|283.11|1.02
92|290.08|6.97
For that last result, 290.08 - 6.97 = 283.11; so
I should have added that I have database with lots of values like this and I
run many queries like this. Sometimes sqlite returns the correct results and
sometimes it includes results where the values are equal to the base or base -
thick. It probably returns the correct result a little over t
On Nov 1, 2011, at 3:34 PM, Ryan Belcher wrote:
> Is this a bug or something I'm not doing right or don't understand?
Check your data type. Make sure to use one which can hold the necessary
precision (i.e. real):
http://www.sqlite.org/datatype3.html
If necessary, cast when appropriate:
http
When we are able to reproduce the problem again, I will run these
checks. It's fairly random however consistent. I will e-mail the
results to you later today.
Thanks,
Korey
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of R
On Mon, Oct 31, 2011 at 10:44 PM, Richard Hipp wrote:
> If an interrupt occurs that stops the read before it can complete, then the
> read should be tried again.
> http://www.sqlite.org/src/artifact/07acbb3e074e?ln=2968
This topic came up before a few months ago and someone (don't remember who)
On Tue, Nov 1, 2011 at 10:34 AM, Ryan Belcher wrote:
> Sometimes queries using < and > will include values that are equal. Here
> is an example.
>
> SELECT id,base,thick FROM test_col WHERE base > 281.01 AND base - thick <
> 283.11;
>
> Id|base|thick
> 89|281.04|0.03
> 90|282.09|1.05
> 91|283.11
If it helps the discussion any, we are using JFFS file system on Linux
2.6.8.1.
We are running uclibc, not sure if that matters or not.
And one more thing, we updated from an old version of SQLite that has
been in our product (and stable) for years. (I want to say it was
3.3.something.) We didn'
Have you heard of rounding errors? If you want exact numbers you need to
convert to integers:
sqlite> create table test_col (id integer,base real,thick real);
sqlite> insert into test_col values(89,281.04,0.03);
sqlite> insert into test_col values(90,282.09|1.05);
Error: table test_col has 3 c
On 1 Nov 2011, at 2:52pm, Richard Hipp wrote:
> This is why you are admonished to never compare floating point numbers for
> equality - in any system, not just in SQLite.
And in this case, the most rigid and correct solution is to hold those numbers
as integers. So given the numbers you suppli
On Mon, Oct 31, 2011 at 5:44 PM, Richard Hipp wrote:
> On Mon, Oct 31, 2011 at 5:40 PM, Pavel Ivanov wrote:
>
>> > Error code 522 is SQLITE_IOERR_SHORT_READ. It is generated here:
>> >
>> > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012
>> >
>> > SQLite was trying to read N bytes an
On Nov 1, 2011, at 3:34 PM, Ryan Belcher wrote:
> For that last result, 290.08 - 6.97 = 283.11; so base - thick is equal to
> 283.11 but not less. Is this a bug or something I'm not doing right or don't
> understand?
As mentioned, precision, precision, precision :P
select * FROM test WHERE b
Thanks to all who responded. I understand the problem now. I also see the
wisdom of using integer type as a substitute for not having decimal type.
I do have one other question. Are there more downsides to using a tolerance
value in my comparisons? It seems to me that either going the intege
On Tue, Nov 1, 2011 at 10:40 AM, Korey Calmettes wrote:
> When we are able to reproduce the problem again, I will run these
> checks. It's fairly random however consistent. I will e-mail the
> results to you later today.
>
If you are able, please recompile using the version of SQLite here:
On 1 Nov 2011, at 3:24pm, Ryan Belcher wrote:
> I do have one other question. Are there more downsides to using a tolerance
> value in my comparisons? It seems to me that either going the integer or
> tolerance route, I'm committing to a fixed precision for base and thick. If
> I know that
Thanks for the information and the VIEW suggestion.
Since SQLite already provides an extension to reference column aliases in
WHERE and JOIN clauses, is there any likelihood that it might be further
extended to allow them in the SELECT statement itself?
Thanks,
Pete
> Message: 14
> Date: Tu
For .0001 worse-case just mulitply all your values by 1000 and all decimal
places will work just as you want and you can compute any difference you want
accurately and compare accurately.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating
I don't think the file is truncated.
Interesting stuff here when I run pragma integrity_check:
/data # sqlite3 test.db "pragma integrity_check;"
*** in database main ***
Page 480: unable to get the page. error code=522
Page 481: unable to get the page. error code=522
Page 482: unable to get the
Please copy the database file to a workstation and try "PRAGMA
integrity_check" on the work station. This will help us to decide if the
problem is with the SQLite code (or JFFS) or a if it is a corrupt database.
On Tue, Nov 1, 2011 at 1:37 PM, Korey Calmettes wrote:
> I don't think the file is t
On 1 Nov 2011, at 4:44pm, Pete wrote:
> Since SQLite already provides an extension to reference column aliases in
> WHERE and JOIN clauses, is there any likelihood that it might be further
> extended to allow them in the SELECT statement itself?
That would require processing the SELECT statement
On 11/02/2011 12:37 AM, Korey Calmettes wrote:
I don't think the file is truncated.
What is the size of the file on disk?
Interesting stuff here when I run pragma integrity_check:
/data # sqlite3 test.db "pragma integrity_check;"
*** in database main ***
Page 480: unable to get the page.
On November 1, 2011 08:48:25 PDT, Richard Hipp wrote:
On Tue, Nov 1, 2011 at 10:40 AM, Korey Calmettes >wrote:
When we are able to reproduce the problem again, I will run these
checks. It's fairly random however consistent. I will e-mail the
results to you later today.
If you are able, ple
On Tue, Nov 1, 2011 at 3:09 PM, Kyle McKay wrote:
> "If write() is interrupted by a signal after it successfully writes some
> data, it shall return the number of bytes written."
>
> and it doesn't appear that SQLite currently permits that behavior either
> with or without the above patch.
>
See
I have a slowness problem when running sqlite3 based application.
I have a small db (500 K on disk, only 1 rows) and I have a loop that
perform 1 selections according to
the primary key of a certain table.
for some reason in takes 15-20 seconds to run the loop below.
by experimentation I fo
On Tue, Nov 1, 2011 at 6:25 PM, Tal Tabakman wrote:
> I have a slowness problem when running sqlite3 based application.
> I have a small db (500 K on disk, only 1 rows) and I have a loop that
> perform 1 selections according to
> the primary key of a certain table.
> for some reason in ta
Yes!, now it flys.
can you please elaborate ? did originally reset dumped all data to disk ?
suppose that I open a db for read only, is it recommended to issue the
"BEGIN" command at the start of analysis ?
thanks in advance
Tal
On Wed, Nov 2, 2011 at 12:33 AM, Richard Hipp wrote:
> On Tue, Nov
Do you know why it goes to core dump?
ChingChang
The source code is shown as below,
vector dbStatements;
dbStatements.push_back( "BEGIN TRANSACTION;" );
for ( int x = 0; x < 10; x++ ) {
sprintf( tempString,
"update utilization_table set utilization=%5.2f,sample=%d where
On Tue, Nov 1, 2011 at 6:50 PM, Tal Tabakman wrote:
> Yes!, now it flys.
> can you please elaborate ? did originally reset dumped all data to disk ?
> suppose that I open a db for read only, is it recommended to issue the
> "BEGIN" command at the start of analysis ?
>
When you didn't reset the
On 1 Nov 2011, at 11:09pm, ChingChang Hsiao wrote:
> dbStatements.push_back( "COMMIT;" );
>
> // populate the DB
> vector::iterator dbStatementsIter;
> SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
> for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter !=
> dbStatements.en
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 31/10/11 09:25, Всеволод Новиков wrote:
> I am using sqlite 3.7.8.
>
> My custom VFS xDelete method returned SQLITE_NOTFOUND in a case when
> the file to be deleted was not found, and this worked fine for older
> versions (probably prior to 3.7.x w
Hello ChingChang,
How big is tempString? What kind of type is it? I wonder if you're
trashing the stack with the sprintf.
What happens if you replace all the sprinfs with some simple inserts
inserted directly into vector?
dbStatements.push_back("INSERT INTO...");
You need to simplify your
I can't reply in my system, so I create the problem description again.
I miss one source code line "char tempString[1024];"in the last email. The
code dump happened after 4 days' run in a test script not immediately. The
SQLITE statements seem to be ok. Could be a performance issue?
ChingCha
Seeking some help/pointers on how one of the production databases at one of our
customers got corrupted. The customer started with 3.6 and upgraded to 3.7 on
windows.
Attached:
a) output of pragma quick_check
b) pretty print of the first page
c) pretty print of the corrupted page
I used conte
52 matches
Mail list logo