[sqlite] Indirect Referencing

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

Re: [sqlite] Indirect Referencing

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'.

Re: [sqlite] Indirect Referencing

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

[sqlite] VFS minor issue with xDelete method return value

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

Re: [sqlite] Indirect Referencing

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

Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29

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

Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29

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).

Re: [sqlite] UPDATE

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

Re: [sqlite] SQLite Expert

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

[sqlite] SQLite version 3.7.9

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

Re: [sqlite] UPDATE

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

Re: [sqlite] Indirect Referencing

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

Re: [sqlite] Indirect Referencing

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:

Re: [sqlite] Indirect Referencing

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

Re: [sqlite] Indirect Referencing

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.

Re: [sqlite] Indirect Referencing

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

Re: [sqlite] Indirect Referencing

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

Re: [sqlite] Indirect Referencing

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

Re: [sqlite] ORDER BY disregarded for GROUP BY

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 > >

Re: [sqlite] Default column values conflict with not null option

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

[sqlite] Occasional problems with < and >

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

Re: [sqlite] Occasional problems with < and >

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

Re: [sqlite] Occasional problems with < and >

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:

Re: [sqlite] Disk I/O Error

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

Re: [sqlite] Disk I/O Error

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

Re: [sqlite] Occasional problems with < and >

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 >

Re: [sqlite] Occasional problems with < and >

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

Re: [sqlite] Occasional problems with < and >

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

Re: [sqlite] Disk I/O Error

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 >> > >>

Re: [sqlite] Occasional problems with < and >

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

Re: [sqlite] Occasional problems with < and >

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

Re: [sqlite] Disk I/O Error

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

Re: [sqlite] Occasional problems with < and >

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

Re: [sqlite] Referencing column aliases

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:

Re: [sqlite] Occasional problems with < and >

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

Re: [sqlite] Disk I/O Error

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

Re: [sqlite] Disk I/O Error

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

Re: [sqlite] Referencing column aliases

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

Re: [sqlite] Disk I/O Error

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.

Re: [sqlite] Disk I/O Error

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

Re: [sqlite] Disk I/O Error

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

[sqlite] does sqlite3_reset have a performance impact on queries?

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

Re: [sqlite] does sqlite3_reset have a performance impact on queries?

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

Re: [sqlite] does sqlite3_reset have a performance impact on queries?

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

[sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond

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

Re: [sqlite] does sqlite3_reset have a performance impact on queries?

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 ? >

Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond

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 != >

Re: [sqlite] VFS minor issue with xDelete method return value

-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

Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond

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

[sqlite] FW: core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 2

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?

[sqlite] sqlite3 corruption (maybe a resend)

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