Re: [sqlite] How to select from a temp table with same name as a main table.

[dochsm]

Thanks to everyone.
I have always prefixed  table names with the db name when I have attached
dbs myself but I didn't appreciate that the temp database was 'attached' as
such as well.
  
In a nutshell then, if you only use one database then you can refer to
tables with no prefix (although you can prefix with main if you like). But
as soon as you either attach another db or use a temp database then it is
wise to prefix all the tables with the database name to avoid any issues
that might crop up with table names inadvertantly or deliberately being the
same.
Thanks, I've learnt something!



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-select-from-a-temp-table-with-same-name-as-a-main-table-tp69470p69544.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] How to select from a temp table with same name as a main table.

[Clemens Ladisch]

Igor Tandetnik wrote:
> On 6/19/2013 8:18 AM, Clemens Ladisch wrote:
>>  says:
>> | If two or more tables in different databases have the same name and
>> | the database-name prefix is not used on a table reference, then the
>> | table chosen is the one in the database that was least recently
>> | attached.
>>
>> As the first (i.e., most recently) attached, main always loses.
>
> You seem to have it backwards. The first would be least recent (that is,
> oldest); the last would be most recent.

Sorry, you're right.

The actual search order is first temp, then main, then any attached
databases in attachment order.


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

Re: [sqlite] How to select from a temp table with same name as a main table.

[Igor Tandetnik]

On 6/19/2013 8:18 AM, Clemens Ladisch wrote:

 says:
| If two or more tables in different databases have the same name and
| the database-name prefix is not used on a table reference, then the
| table chosen is the one in the database that was least recently
| attached.

As the first (i.e., most recently) attached, main always loses.


You seem to have it backwards. The first would be least recent (that is, 
oldest); the last would be most recent.

--
Igor Tandetnik

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

Re: [sqlite] How to select from a temp table with same name as a main table.

[Clemens Ladisch]

dochsm wrote:
> I would have thought having no database prefix would default to the
> main

 says:
| If two or more tables in different databases have the same name and
| the database-name prefix is not used on a table reference, then the
| table chosen is the one in the database that was least recently
| attached.

As the first (i.e., most recently) attached, main always loses.


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

Re: [sqlite] How to select from a temp table with same name as a main table.

[dochsm]

That brilliant. It is as I thought. Odd behaviour though. I would have
thought having no database prefix would default to the main, after all I
don't put the prefix there usually and if you try to create two tables in
the same db you get an error (obviously). To be on the safe side I've
re-written the code to give the temp table a different name and altered all
the sql to use the new name. That seems fine.
I'll have to remember that issue though as it might crop up again if ever I
inadvertantly give a temp table the same name as one alreay in main.
regards
Howard 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-select-from-a-temp-table-with-same-name-as-a-main-table-tp69470p69475.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] How to select from a temp table with same name as a main table.

[Simon Davies]

On 17 June 2013 23:19, dochsm  wrote:
> I have a table in the main database called 'students' and a two page block of
> sql that processes it, referring to it as simply 'students'.
>
> Experimenting with a different approach, I created a temp table, also called
> 'students' that contained a subset of the main.students. (I intended to
> modify the sql, replacing 'students' with 'temp.students' but have not yet
> done so)
>
> However, running my original sql now, before editing it, it appears to be
> using both the table 'students' and the table temp.students as it is now
> repeating some rows.
>
> Can sombody please explain what might be happening?
> If no database prefix is given, doesn't SQLite default to the 'main'
> database or does it somehow use a combination of all the tables with the
> same name regardless of database?

Does the following output give a clue?

SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t( data text );
sqlite> create temp table t( data text );
sqlite> insert into t values( 'main' );
sqlite> insert into temp.t values( 'temp' );
sqlite>
sqlite>
sqlite> select * from t;
main
temp
sqlite> select * from main.t;
sqlite> select * from temp.t;
main
temp
sqlite> insert into main.t values( 'really main' );
sqlite> select * from t;
main
temp
sqlite> select * from main.t;
really main
sqlite> select * from temp.t;
main
temp
sqlite>

It looks like the temp db is searched first for a match of table name...

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

Re: [sqlite] how to select " char in sqlite

[Black, Michael (IS)]

You appear to be programming in C so that's what this is...
Here's a complete example where you can control the table formatting yourself.
This is using sqlite3 calls and I made it produce a simple, complete HTML page.
This is, of course, tied to your database due to the specific column names.  
It's more work to make it generic.
But this should get you a lot closer to what you really want I hope.

Compile and run like this:

myhtml t9_engine.db "select id,partnumber,pic from engine where id>7" > n.html



#include 
#include 
#include 
#include "sqlite3.h"

void checkrc(int rc,int check,sqlite3 *db)
{
  if (rc != check) {
fprintf(stderr,"%s\n",sqlite3_errmsg(db));
exit(1);
  }
}

void doMySQL(char *dbname, char *sql)
{
  sqlite3 *db;
  int rc;
  sqlite3_stmt *stmt;
  rc=sqlite3_open(dbname,);
  checkrc(rc,SQLITE_OK,db);
  rc = sqlite3_prepare_v2(db,sql,strlen(sql),,NULL);
  checkrc(rc,SQLITE_OK,db);
  printf("http://www.w3.org/TR/REC-html40/strict.dtd\;>\n");
  printf("\n\nParts List");
  printf("\n");
  printf("\n");
  printf("IDPart#Picture\n");
  while((rc=sqlite3_step(stmt))==SQLITE_ROW) {
int id=sqlite3_column_int(stmt,0);
printf("\n%d\n",id);
char *partnumber = sqlite3_column_text(stmt,1);
printf("%s\n",partnumber);
char *pic = sqlite3_column_text(stmt,2);
printf("\n",pic,pic);
printf("\n");
  }
  checkrc(rc,SQLITE_DONE,db);
  rc=sqlite3_finalize(stmt);
  checkrc(rc,SQLITE_OK,db);
  printf("\n\n");
  rc = sqlite3_close(db);
  checkrc(rc,SQLITE_OK,db);
}

int main(int argc, char *argv[])
{
  if (argc !=3) {
fprintf(stderr,"Usage: %s database \"sql\"",argv[0]);
exit(1);
  }
  doMySQL(argv[1],argv[2]);
  return 0;
}



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of YAN HONG YE [yanhong...@mpsa.com]
Sent: Thursday, October 25, 2012 8:25 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] FW: how to select " char in sqlite

char bh1[320];
memset(bh1,0,320);
strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
system(bh1);  //here couldn't work

error:
sqlite3 -html -header t9_engine.db "select id,partnumber,substr('',1,180) as img,pcs from engine where id>7;" >> n.htmlError: n
ear "'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'' as img,pcs from engine where id>7;\" >> n.html");
system(bh1);  //here could work
the result is:
8
AA34841687 000 INSONO-SOUS-MOTEUR--
img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
1


and the best way is change
to  <
to  >

___
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] how to select " char in sqlite

[Black, Michael (IS)]

Here it is with your desire to use system().

The table output you get probably is not going to be formatted the way you like.
You can extend the logic here to put special sequences in the string to then 
replace with formatting.
It would really be easier oveall to do this yourself by using the sqlite calls 
instead of system().

#include 
#include 
#include 

char *str_replace(char *orig, char *rep, char *with) {
char *result; // the return string
char *ins;// the next insert point
char *tmp;// varies
int len_rep;  // length of rep
int len_with; // length of with
int len_front; // distance between rep and end of last rep
int count;// number of replacements

if (!orig)
return NULL;
if (!rep || !(len_rep = strlen(rep)))
return NULL;
if ((ins = strstr(orig, rep)) == NULL)
return NULL;
if (!with)
with = "";
len_with = strlen(with);

for (count = 0; (tmp = strstr(ins, rep)); ++count) {
ins = tmp + len_rep;
}

// first time through the loop, all the variable are set correctly
// from here on,
//tmp points to the end of the result string
//ins points to the next occurrence of rep in orig
//orig points to the remainder of orig after "end of rep"
tmp = result = malloc(strlen(orig) + (len_with - len_rep) * count + 1);

if (!result)
return NULL;

while (count--) {
ins = strstr(orig, rep);
len_front = ins - orig;
tmp = strncpy(tmp, orig, len_front) + len_front;
tmp = strcpy(tmp, with) + len_with;
orig += len_front + len_rep; // move to next "end of rep"
}
strcpy(tmp, orig);
return result;
}

int main() {
//char *sqlcmd="sqlite3 -html -header t9_engine.db \"select 
id,partnumber,'' from engine where 
id>7;\" >> n.html";
FILE *fp;
char buf[65535];
char *sqlcmd="sqlite3 -html -header t9_engine.db \"select 
id,partnumber,'' from engine where 
id>7;\" >> n.html";
system(sqlcmd);
fp = fopen("n.html","r");
while(fgets(buf,sizeof(buf),fp)) {
char *s=str_replace(buf,"#quot;","\"");
if (s) {strcpy(buf,s);free(s);}
s=str_replace(buf,"","<");
if (s) {strcpy(buf,s);free(s);}
s=str_replace(buf,"","<");
if (s) {strcpy(buf,s);free(s);}
printf("%s",buf);
}
fclose(fp);
return 0;
}


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Kees Nuyt [k.n...@zonnet.nl]
Sent: Friday, October 26, 2012 5:08 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] FW: how to select " char in sqlite

On Fri, 26 Oct 2012 01:25:24 +,
YAN HONG YE <yanhong...@mpsa.com> wrote:

>char bh1[320];
>memset(bh1,0,320);
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here couldn't work
>
>error:
>sqlite3 -html -header t9_engine.db "select id,partnumber,substr('src="'||pi
>c||'" height=220/>',1,180) as img,pcs from engine where id>7;" >> n.htmlError: 
>n
>ear "'operable program or batch file.
>The system cannot find the path specified.




>char bh1[320];
>memset(bh1,0,320);
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'src='||pic||' height=220/>' as img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here could work
>the result is:
>8
>AA34841687 000 INSONO-SOUS-MOTEUR--
>img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
>//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
>1
>
>
>and the best way is change
>   to  <
>   to  >

You will never get that right. Quoting will always stay a problem.
Forking out from C to a shell is bad practice. Forking out to a DOS
shell is a headache. It's not SQLite related and off topic in this list.

Nevertheless, Michael Black did provide a working solution on Wed, 24
Oct 2012 15:09:24 +, did you read it?

Please have a look at the sample C code I linked to before.
There are more examples there.
http://icculus.org/~chunky/stuff/sqlite3_example/

Good luck!

--
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
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] how to select " char in sqlite

[Kees Nuyt]

On Thu, 25 Oct 2012 01:20:24 +, YAN HONG YE 
wrote:

>char bh1[320];
>memset(bh1,0,320);
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here couldn't work

As we said before "here couldn't work" is insufficient to describe your
problem.  Please show us the contents of bh1 just before the system()
call, and the error codes and error messages returned.

It is bad practice to shell out to the sqlite3 commandline tool from
within a program. As you see, you'll get all kinds of quoting issues
(probably the root cause of the failure here). 

You are supposed to use the API, either the C-API
http://sqlite.org/c3ref/intro.html or some API provided by a "wrapper"
for the computer language your application is coded in.

There is excellent sample code on the internet, like
http://icculus.org/~chunky/stuff/sqlite3_example/sqlite3_example_bind.c

By the way, I don't think the substr() is at the right place.

Hope this helps.


-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] how to select " char in sqlite

[YAN HONG YE]

char bh1[320];
memset(bh1,0,320);
strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
system(bh1);  //here couldn't work
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to select " char in sqlite

[Black, Michael (IS)]

If Windows get FART (find and replace text) from here:
http://blog.secaserver.com/2011/07/windows-find-and-replace-text-command-line-utility/
If Unix learn sed:
http://www.thegeekstuff.com/2009/09/unix-sed-tutorial-replace-text-inside-a-file-using-substitute-command/

Then
sqlite3 test.db
create table engine(id,partnumber,pic);
insert into engine values(1,11,'1.jpg');
insert into engine values(2,22,'2.jpg');
insert into engine values(3,33,'3.jpg');
D:\SQLite>sqlite3 -html test.db "select id,partnumber,'XXLTimg 
src='||\"XXQUOTE\"||pic||\"XXQUOTE\"||' height=220XXGT' from eng
ine;"
fart test.html XXGT ">"
fart test.html XXLT "<"
fart test.html XXQUOTE ''"

And you end up with:
1
11


2
22


3
33




Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of YAN HONG YE [yanhong...@mpsa.com]
Sent: Wednesday, October 24, 2012 4:00 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] FW: how to select " char in sqlite

sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as img,pcs 
from engine where id>7;" >> n.html
here   \"abc.jpg\" couldn't work.

sqlite3 -html -header t9_engine.db "select id,partnumber,'' as img,pcs from engine where id>7; ">> n.html
Same problem.

strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
Same 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] how to select "

[Jay A. Kreibich]

On Wed, Oct 24, 2012 at 07:27:57AM +, YAN HONG YE scratched on the wall:
> sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\"
>as img,pcs from engine where id>7;" >> n.html
> here   \"abc.jpg\" couldn't work.

  SQL string literals use single quotes.

   -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] how to select " char in sqlite

[Clemens Ladisch]

Igor Tandetnik wrote:
> YAN HONG YE  wrote:
>> sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as 
>> img,pcs from engine where id>7;" >> n.html
>> here   \"abc.jpg\" couldn't work.
>>
>> sqlite3 -html -header t9_engine.db "select id,partnumber,'> '||pic||' \" height=220/>' as img,pcs from engine where
>> id>7; ">> n.html Same problem.
>
> Define "couldn't work". What outcome do you observe, what outcome do you 
> expect, and how do the two differ?

I'd guess the purpose of these queries is to generate an HTML table with images.

sqlite> .mode html
sqlite> select '';
img src=something.jpg


But the sqlite shell always escapes special characters and thus cannot
generate HTML tags.

I'd recommend to export the data in some other format and then reformat
it to HTML.


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

Re: [sqlite] how to select " char in sqlite

[Igor Tandetnik]

YAN HONG YE  wrote:
> sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as 
> img,pcs from engine where id>7;" >> n.html
> here   \"abc.jpg\" couldn't work.
> 
> sqlite3 -html -header t9_engine.db "select id,partnumber,' '||pic||' \" height=220/>' as img,pcs from engine where
> id>7; ">> n.html Same problem.

Define "couldn't work". What outcome do you observe, what outcome do you 
expect, and how do the two differ?
-- 
Igor Tandetnik

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

Re: [sqlite] How to select from table

[Black, Michael (IS)]

setup() must be some other function in the book.  Get rid of it.  Probably runs 
some pragmas.



You can force the name error to go away by casting to char *.



Or you can strdup the sqlite3_column_text value and free it when you're done.



The error is warning you that you need to be aware that this value is not 
permanent.



You'll also need to rename the function to int main() -- otherwise it won't 
link.







Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of deltagam...@gmx.net [deltagam...@gmx.net]
Sent: Wednesday, June 20, 2012 10:04 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] How to select from table

Am 20.06.2012 14:55, schrieb Igor Tandetnik:
> deltagam...@gmx.net wrote:
>> how can i select from sqlite3 db ?
> By executing a SELECT statement, of course. See this example:
>
> http://books.google.com/books?id=VsZ5bUh0XAkC=PA222
>
>> How do I retrieve the number of records in a table ?
> By running this statement: select count(*) from MyTable;

thx for the hint, it is an interesting book, but in the example on page 222
I get 2 errors

  select_all_from_db.cpp(23): error C3861: 'setup': identifier not found
  select_all_from_db.cpp(41): error C2440: '=' : cannot convert from
'const unsigned char *' to 'char *'
line 41 is :   name = sqlite3_column_text(stmt, 2 );

I checked on the documentation side http://sqlite.org/capi3ref.html ,
there is nothing about setup ...
And what is the problem with name ?


==
#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


void select_all_rows_db() {

 int rc, i, ncols, id, cid;
 char *name, *sql;
 sqlite3 *db;
 sqlite3_stmt *stmt;

 sql = "Select ID, EVENTTYPE FROM eventlog";
 sqlite3_open("ah.db", );

 setup(db);

 sqlite3_prepare(db, sql, strlen(sql), , NULL);
 rc = sqlite3_step(stmt);

/*Print column information */
for (i=0; i< ncols; i++ ) {
 fprintf(stdout, "Column: name=%s, storage class=%i, declared=%s\n",
 sqlite3_column_name(stmt, i ),
 sqlite3_column_type(stmt, i ),
 sqlite3_column_decltype(stmt, i ));
} // for

fprintf(stdout, "\n");

while(rc == SQLITE_ROW) {
 id = sqlite3_column_int(stmt, 0 );
 cid = sqlite3_column_int(stmt, 1 );
 name = sqlite3_column_text(stmt, 2 );

 if (name != NULL ) {
 fprintf(stderr, "Row: id=%i, cid=%i, name='%s'\n", id, cid, name);
 } else {
 /* Field is NULL */
 fprintf(stderr, "Row: id=%i, cid=%i, name=NULL\n", id, cid);
 }
 rc = sqlite3_step(stmt);
} // while

sqlite3_finalize(stmt);
sqlite3_close(db);


} // select_all_rows_db


___
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] How to select from table

[deltagam...@gmx.net]

Am 20.06.2012 14:55, schrieb Igor Tandetnik:

deltagam...@gmx.net wrote:

how can i select from sqlite3 db ?

By executing a SELECT statement, of course. See this example:

http://books.google.com/books?id=VsZ5bUh0XAkC=PA222


How do I retrieve the number of records in a table ?

By running this statement: select count(*) from MyTable;


thx for the hint, it is an interesting book, but in the example on page 222
I get 2 errors

  select_all_from_db.cpp(23): error C3861: 'setup': identifier not found
  select_all_from_db.cpp(41): error C2440: '=' : cannot convert from 
'const unsigned char *' to 'char *'

line 41 is :   name = sqlite3_column_text(stmt, 2 );

I checked on the documentation side http://sqlite.org/capi3ref.html , 
there is nothing about setup ...

And what is the problem with name ?


==
#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


void select_all_rows_db() {

int rc, i, ncols, id, cid;
char *name, *sql;
sqlite3 *db;
sqlite3_stmt *stmt;

sql = "Select ID, EVENTTYPE FROM eventlog";
sqlite3_open("ah.db", );

setup(db);

sqlite3_prepare(db, sql, strlen(sql), , NULL);
rc = sqlite3_step(stmt);

/*Print column information */
for (i=0; i< ncols; i++ ) {
fprintf(stdout, "Column: name=%s, storage class=%i, declared=%s\n",
sqlite3_column_name(stmt, i ),
sqlite3_column_type(stmt, i ),
sqlite3_column_decltype(stmt, i ));
} // for

fprintf(stdout, "\n");

while(rc == SQLITE_ROW) {
id = sqlite3_column_int(stmt, 0 );
cid = sqlite3_column_int(stmt, 1 );
name = sqlite3_column_text(stmt, 2 );

if (name != NULL ) {
fprintf(stderr, "Row: id=%i, cid=%i, name='%s'\n", id, cid, name);
} else {
/* Field is NULL */
fprintf(stderr, "Row: id=%i, cid=%i, name=NULL\n", id, cid);
}
rc = sqlite3_step(stmt);
} // while

sqlite3_finalize(stmt);
sqlite3_close(db);


} // select_all_rows_db


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

Re: [sqlite] How to select from table

[Igor Tandetnik]

deltagam...@gmx.net wrote:
> how can i select from sqlite3 db ?

By executing a SELECT statement, of course. See this example:

http://books.google.com/books?id=VsZ5bUh0XAkC=PA222

> How do I retrieve the number of records in a table ?

By running this statement: select count(*) from MyTable;
-- 
Igor Tandetnik

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

Re: [sqlite] How to select from table

[deltagam...@gmx.net]

Am 20.06.2012 12:02, schrieb deltagam...@gmx.net:

Hello,

how can i select from sqlite3 db  ?

How do I retrieve the number of records in a table ?

Thx in advance


Sorry forgot to tell, I'm using the c++ API
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[Peng Yu]

On Sat, Jul 3, 2010 at 8:14 AM, Tim Romano  wrote:
>  I'm not quite sure what you meant by "only show n times if it appears more
> than n times'.   Is the pseudo-code below analogous to what you are trying
> to do?
>
>
> for each surname in
>  (select surname from phone-book order by surname)
>      {
>      print surname no more than n times
>      }

Yes.

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

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[Tim Romano]

 I'm not quite sure what you meant by "only show n times if it appears more
than n times'.   Is the pseudo-code below analogous to what you are trying
to do?


for each surname in
 (select surname from phone-book order by surname)
  {
  print surname no more than n times
  }

Regards
Tim Romano
Swarthmore PA





> > On 2 Jul 2010, at 5:15pm, Peng Yu wrote:
>  > I want to select an entry that appears
> > <=n times and only show n times if it appears more than n times. I
> > think that "group by" might help.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select an entry that appears l t;=n times and only show n times if it appears mor e than n times?

[Oliver Peters]

o.k.,

got it - next time I'll read twice (at least ;-) )

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

Re: [sqlite] How to select an entry that appears =n times and only show n times if it appears more th an n times?

[Oliver Peters]

Peng Yu  writes:

> 
> Hi,
> 
> SELECT DISTINCT type_id FROM foods;
> 
> If I use 'distinct', any entry that shows up greater or equal to one
> time will only appear once. But I want to select an entry that appears
> <=n times and only show n times if it appears more than n times. I
> think that "group by" might help. But I'm not familiar with SQL enough
> yet. Would you please let me know what command to use?
> 


what about

SELECT entry,
CASE
WHEN cnt <= n THEN cnt
ELSE n
END AS result
FROM
(
SELECT entry, COUNT(entry) as cnt
FROM table
GROUP BY entry
)
;


Oliver

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

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[Jim Morris]

Are you thinking of limit?

On 7/2/2010 9:58 AM, Simon Slavin wrote:
> On 2 Jul 2010, at 5:15pm, Peng Yu wrote:
>
>
>> I want to select an entry that appears
>> <=n times and only show n times if it appears more than n times. I
>> think that "group by" might help.
>>  
> There's no simple format which will do what you want.  Do it in software.
>
> 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] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[Simon Slavin]

On 2 Jul 2010, at 5:15pm, Peng Yu wrote:

> I want to select an entry that appears
> <=n times and only show n times if it appears more than n times. I
> think that "group by" might help.

There's no simple format which will do what you want.  Do it in software.

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

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[Pavel Ivanov]

> But this doesn't show anything that count more than n times. I want
> the type_id shows up more than n times in the database only appear n
> times in the result of the query.

That's some exotic requirements you've got there. Is it possible to
elaborate them? Probably your best solution is not in SQL but in your
programming language. Because I believe it's possible to do that in
SQL but the performance of such query would be awful.


Pavel

On Fri, Jul 2, 2010 at 12:29 PM, Peng Yu  wrote:
> On Fri, Jul 2, 2010 at 11:19 AM, P Kishor  wrote:
>> On Fri, Jul 2, 2010 at 11:19 AM, P Kishor  wrote:
>>> On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu  wrote:
 Hi,

 SELECT DISTINCT type_id FROM foods;

 If I use 'distinct', any entry that shows up greater or equal to one
 time will only appear once. But I want to select an entry that appears
 <=n times and only show n times if it appears more than n times. I
 think that "group by" might help. But I'm not familiar with SQL enough
 yet. Would you please let me know what command to use?

>>>
>>> Try
>>>
>>> SELECT , Count(type_id)
>>> FROM foods
>>> GROUP BY 
>>> HAVING Count(type_id) < n
>>
>> make that
>>
>> HAVING Count(type_id) <= n
>
> But this doesn't show anything that count more than n times. I want
> the type_id shows up more than n times in the database only appear n
> times in the result of the query.
>
>
> --
> Regards,
> Peng
> ___
> 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] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[Peng Yu]

On Fri, Jul 2, 2010 at 11:19 AM, P Kishor  wrote:
> On Fri, Jul 2, 2010 at 11:19 AM, P Kishor  wrote:
>> On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu  wrote:
>>> Hi,
>>>
>>> SELECT DISTINCT type_id FROM foods;
>>>
>>> If I use 'distinct', any entry that shows up greater or equal to one
>>> time will only appear once. But I want to select an entry that appears
>>> <=n times and only show n times if it appears more than n times. I
>>> think that "group by" might help. But I'm not familiar with SQL enough
>>> yet. Would you please let me know what command to use?
>>>
>>
>> Try
>>
>> SELECT , Count(type_id)
>> FROM foods
>> GROUP BY 
>> HAVING Count(type_id) < n
>
> make that
>
> HAVING Count(type_id) <= n

But this doesn't show anything that count more than n times. I want
the type_id shows up more than n times in the database only appear n
times in the result of the query.


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

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[P Kishor]

On Fri, Jul 2, 2010 at 11:19 AM, P Kishor  wrote:
> On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu  wrote:
>> Hi,
>>
>> SELECT DISTINCT type_id FROM foods;
>>
>> If I use 'distinct', any entry that shows up greater or equal to one
>> time will only appear once. But I want to select an entry that appears
>> <=n times and only show n times if it appears more than n times. I
>> think that "group by" might help. But I'm not familiar with SQL enough
>> yet. Would you please let me know what command to use?
>>
>
> Try
>
> SELECT , Count(type_id)
> FROM foods
> GROUP BY 
> HAVING Count(type_id) < n

make that

HAVING Count(type_id) <= n


>
>
>> --
>> Regards,
>> Peng
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[P Kishor]

On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu  wrote:
> Hi,
>
> SELECT DISTINCT type_id FROM foods;
>
> If I use 'distinct', any entry that shows up greater or equal to one
> time will only appear once. But I want to select an entry that appears
> <=n times and only show n times if it appears more than n times. I
> think that "group by" might help. But I'm not familiar with SQL enough
> yet. Would you please let me know what command to use?
>

Try

SELECT , Count(type_id)
FROM foods
GROUP BY 
HAVING Count(type_id) < n


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?

[Igor Tandetnik]

Atul_Vaidya
 wrote:
>  I want to make this query faster, for this, i created
> indexes for Entity_Id

Entity_Id doesn't appear anywhere in the query. How is an index on it 
supposed to help?

> and grpuid

Which of them? There are three tables, all having a column named grpuid.

Run the query with EXPLAIN QUERY PLAN in front. The output will tell you 
which indexes, if any, SQLite uses for each join.

> and also used a pragmas :
> PRAGMA journal_mode = MEMORY;
> PRAGMA synchronous = OFF;
> PRAGMA temp_store = MEMORY;
> PRAGMA count_changes = OFF;
> PRAGMA cache_size = 12000;

These pragmas are mostly used when you are writing to the database. They 
do little to speed up SELECTs.

> I also do Begin Transaction and End Transaction

Same thing - this doesn't help SELECTs.

> 2. Entity_ID is a Primary Integer Key

Then there's already an index on it. No reason to create a second one.

Igor Tandetnik 



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

Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?

[Atul_Vaidya]

Hi List,
  I want to make this query faster, for this, i created indexes for
Entity_Id and grpuid and also used a pragmas :
PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
PRAGMA count_changes = OFF;
PRAGMA cache_size = 12000;
I also do Begin Transaction and End Transaction, but still i havn't achieved
the speed the way i like.
My Questions :
1. Am i missing out on anything?
2. Entity_ID is a Primary Integer Key, so is there any need to create an
Index for it,as per my understanding,Indexing increases the database size
and also the INSERT gets slower 

Thanks in Advance,
Atul 

Igor Tandetnik wrote:
> 
> Atul_Vaidya wrote:
>> Hi, I have three tables,
>> 1. Table Entity_xdata containing following fields
>>> Entity_id|Layer|grpuid|
>>
>> 2. Table, group_xdata_pipe containing following fields 
>>> grpuid|LNV|
>>
>> 3. Table group_id_vs_reggappname containing following fields 
>>> grpuid|reggappname|
>>
>> Now, I need to Fire a query to SQlite where in I get the list of all
>> the distinct LNVs. Currently I achieve it by following two query
>> commands as follows 
>> SELECT DISTINCT Entity_xData.grpuid from Entity_xdata INNER JOIN
>> group_id_vs_regappname ON(Entity_xdata.grpuid =
>> group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname =
>> 'CPD1')
>> I get the grpuids using this command and then i use the grpuids that
>> i get from this query, as an input to my next query, something like
>> this ...
>>
>> SELECT DISTINCT Line_Number_View FROM (SELECT grpuid,line_number_view
>> FROM group_xdata_pipe WHERE grpuid = '%s' ORDER BY grpuid ASC)ORDER BY
>> Line_Number_View ASC",query_result[x])
>>
>> My question is
>> Is it possible to combine both these querries into one ?
> 
> SELECT DISTINCT Line_Number_View
> FROM group_xdata_pipe WHERE grpuid IN (
> SELECT Entity_xData.grpuid
> FROM Entity_xdata INNER JOIN group_id_vs_regappname ON (
> Entity_xdata.grpuid = group_id_vs_regappname.grpuid AND
> group_id_vs_regappname.reg_appname = 'CPD1')
> )
> ORDER BY Line_Number_View ASC;
> 
> 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://www.nabble.com/how-to-Select-using-results-from-other-selects-in-a-single-SQlite-querry-statement---tp25149885p25271121.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] how to Select using results from other selects in a single SQlite querry statement ?

[Igor Tandetnik]

Atul_Vaidya wrote:
> Hi, I have three tables,
> 1. Table Entity_xdata containing following fields
>> Entity_id|Layer|grpuid|
>
> 2. Table, group_xdata_pipe containing following fields 
>> grpuid|LNV|
>
> 3. Table group_id_vs_reggappname containing following fields 
>> grpuid|reggappname|
>
> Now, I need to Fire a query to SQlite where in I get the list of all
> the distinct LNVs. Currently I achieve it by following two query
> commands as follows 
> SELECT DISTINCT Entity_xData.grpuid from Entity_xdata INNER JOIN
> group_id_vs_regappname ON(Entity_xdata.grpuid =
> group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname =
> 'CPD1')
> I get the grpuids using this command and then i use the grpuids that
> i get from this query, as an input to my next query, something like
> this ...
>
> SELECT DISTINCT Line_Number_View FROM (SELECT grpuid,line_number_view
> FROM group_xdata_pipe WHERE grpuid = '%s' ORDER BY grpuid ASC)ORDER BY
> Line_Number_View ASC",query_result[x])
>
> My question is
> Is it possible to combine both these querries into one ?

SELECT DISTINCT Line_Number_View
FROM group_xdata_pipe WHERE grpuid IN (
SELECT Entity_xData.grpuid
FROM Entity_xdata INNER JOIN group_id_vs_regappname ON (
Entity_xdata.grpuid = group_id_vs_regappname.grpuid AND
group_id_vs_regappname.reg_appname = 'CPD1')
)
ORDER BY Line_Number_View ASC;

Igor Tandetnik 



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

Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?

[Atul_Vaidya]

Atul_Vaidya wrote:
> 
> 
> SELECT DISTINCT Entity_xData.grpuid from Entity_xdata INNER JOIN
> group_id_vs_regappname ON(Entity_xdata.grpuid =
> group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname =
> 'CPD1')
> I get the grpuids using this command and then i use the grpuids that i get
> from this query, as an input to my next query, something like this ...
> 
> SELECT DISTINCT Line_Number_View FROM (SELECT grpuid,line_number_view FROM
> group_xdata_pipe WHERE grpuid = '%s' ORDER BY grpuid ASC)ORDER BY
> Line_Number_View ASC",query_result[x])
> 
> My question is
> Is it possible to combine both these querries into one ? or in other words 
> How to Select using Result obtained from other Select statements ?
> Thanks in Advance,
> Atul
> 
atul.vai...@prototechsolutions.com
Hi,
  I was able to achieve this by using two INNER JOINS as follows 
Select DISTINCT group_xdata_pipe.Line_Number_View from group_xdata_pipe
INNER JOIN Entity_xData ON(group_xdata_pipe.grpuid =
Entity_xData.grpuid)INNER JOIN group_id_vs_regappname ON(Entity_xdata.grpuid
= group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname =
'CPD1')
Thanks, and any suggestions to this query are welcome, i am sure, there must
be some more elegant ways of doing this.
Regards,
Atul
-- 
View this message in context: 
http://www.nabble.com/how-to-Select-using-results-from-other-selects-in-a-single-SQlite-querry-statement---tp25149885p25151110.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] How to select data from 2 lines in one line?

[Kit]

2009/8/19 Mário Anselmo Scandelari Bussmann :
> Both work for me, Kit solution is very fast, but I think John is right. In
> my case, the tables have sequencial rowid. If I delete some row, then will
> not work anymore.

When you create an index of column data, John's solution will be fast too.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[Mário Anselmo Scandelari Bussmann]

Both work for me, Kit solution is very fast, but I think John is right. In
my case, the tables have sequencial rowid. If I delete some row, then will
not work anymore.


On Wed, Aug 19, 2009 at 12:44 PM, John Machin  wrote:

> On 20/08/2009 12:57 AM, Kit wrote:
> > Right form (tested):
> >
> > SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
> > previous_data,temp.preult AS previous_preult
> >FROM petr4,petr4 AS temp
> >WHERE petr4.rowid=temp.rowid+1;
>
> Don't you think that relying on (a) rowid being consecutive (b) rowid
> order being identical to date order is just a little bit dodgy? What if
> the table has been created by a bulk load, not necessarily in ascending
> date order? What if some rows have been deleted?
> ___
> 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] How to select data from 2 lines in one line?

[Kit]

2009/8/19 John Machin :
> On 20/08/2009 12:57 AM, Kit wrote:
>> Right form (tested):
>>
>> SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
>> previous_data,temp.preult AS previous_preult
>>FROM petr4,petr4 AS temp
>>WHERE petr4.rowid=temp.rowid+1;
>
> Don't you think that relying on (a) rowid being consecutive (b) rowid
> order being identical to date order is just a little bit dodgy? What if
> the table has been created by a bulk load, not necessarily in ascending
> date order? What if some rows have been deleted?

OK, you found more better solution.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[John Machin]

On 20/08/2009 12:57 AM, Kit wrote:
> Right form (tested):
> 
> SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
> previous_data,temp.preult AS previous_preult
>FROM petr4,petr4 AS temp
>WHERE petr4.rowid=temp.rowid+1;

Don't you think that relying on (a) rowid being consecutive (b) rowid 
order being identical to date order is just a little bit dodgy? What if 
the table has been created by a bulk load, not necessarily in ascending 
date order? What if some rows have been deleted?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[Mário Anselmo Scandelari Bussmann]

I said its slow but I forget index. Now is as fast as a lightningbolt!!
Thanks again!

On Wed, Aug 19, 2009 at 12:23 PM, Mário Anselmo Scandelari Bussmann <
mario.bussm...@gmail.com> wrote:

> Bingo! This works very well (a little bit slow, since I have a 30
> rows). Thank you all!!!
>
>
> On Wed, Aug 19, 2009 at 11:42 AM, John Machin wrote:
>
>> On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote:
>> > I have a table like this:
>> >
>> > petr4
>> > ---
>> > rowid|data|preabe|premax|premin|preult|voltot
>> > 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
>> > 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
>> [snip]
>> > 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
>> > 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
>> >
>> > I need a select that returns data,preult,previous data and previous
>> preult:
>> >
>> > 2007-01-03|48.7|2007-01-02|50.45
>> > 2007-01-04|47.65|2007-01-03|48.7
>> [snip]
>> > 2007-01-12|45.15|2007-01-11|45.21
>> > 2007-01-15|44.89|2007-01-12|45.15
>> >
>> > How can I do that using only sql (no python, c or perl, no cursor)?
>>
>> No Python? How cruel :-)
>>
>> This works but you'd better have an index on 'data', and it looks like
>> at least O(N**2) OTTOMH:
>>
>> sqlite> create table x (data,preabe,premax,premin,preult,voltot);
>> sqlite> insert into x values
>> ('2007-01-02',50.0,50.45,49.76,50.45,256115409.0);
>> /* etc etc*/
>> sqlite> select a.data, a.preult, b.data, b.preult from x a, x b
>>...> where b.data = (select max(c.data) from x c where c.data < a.data)
>>...> order by a.data;
>> 2007-01-03|48.7|2007-01-02|50.45
>> 2007-01-04|47.65|2007-01-03|48.7
>> [snip]
>> 2007-01-12|45.15|2007-01-11|45.21
>> 2007-01-15|44.89|2007-01-12|45.15
>> sqlite>
>>
>> ___
>> 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] How to select data from 2 lines in one line?

[Mário Anselmo Scandelari Bussmann]

Bingo! This works very well (a little bit slow, since I have a 30 rows).
Thank you all!!!

On Wed, Aug 19, 2009 at 11:42 AM, John Machin  wrote:

> On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote:
> > I have a table like this:
> >
> > petr4
> > ---
> > rowid|data|preabe|premax|premin|preult|voltot
> > 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> > 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
> [snip]
> > 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> > 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
> >
> > I need a select that returns data,preult,previous data and previous
> preult:
> >
> > 2007-01-03|48.7|2007-01-02|50.45
> > 2007-01-04|47.65|2007-01-03|48.7
> [snip]
> > 2007-01-12|45.15|2007-01-11|45.21
> > 2007-01-15|44.89|2007-01-12|45.15
> >
> > How can I do that using only sql (no python, c or perl, no cursor)?
>
> No Python? How cruel :-)
>
> This works but you'd better have an index on 'data', and it looks like
> at least O(N**2) OTTOMH:
>
> sqlite> create table x (data,preabe,premax,premin,preult,voltot);
> sqlite> insert into x values
> ('2007-01-02',50.0,50.45,49.76,50.45,256115409.0);
> /* etc etc*/
> sqlite> select a.data, a.preult, b.data, b.preult from x a, x b
>...> where b.data = (select max(c.data) from x c where c.data < a.data)
>...> order by a.data;
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
> [snip]
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15
> sqlite>
>
> ___
> 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] How to select data from 2 lines in one line?

[Kit]

Right form (tested):

SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
previous_data,temp.preult AS previous_preult
   FROM petr4,petr4 AS temp
   WHERE petr4.rowid=temp.rowid+1;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[John Machin]

On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote:
> I have a table like this:
> 
> petr4
> ---
> rowid|data|preabe|premax|premin|preult|voltot
> 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
[snip]
> 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
> 
> I need a select that returns data,preult,previous data and previous preult:
> 
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
[snip]
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15
> 
> How can I do that using only sql (no python, c or perl, no cursor)?

No Python? How cruel :-)

This works but you'd better have an index on 'data', and it looks like 
at least O(N**2) OTTOMH:

sqlite> create table x (data,preabe,premax,premin,preult,voltot);
sqlite> insert into x values 
('2007-01-02',50.0,50.45,49.76,50.45,256115409.0);
/* etc etc*/
sqlite> select a.data, a.preult, b.data, b.preult from x a, x b
...> where b.data = (select max(c.data) from x c where c.data < a.data)
...> order by a.data;
2007-01-03|48.7|2007-01-02|50.45
2007-01-04|47.65|2007-01-03|48.7
[snip]
2007-01-12|45.15|2007-01-11|45.21
2007-01-15|44.89|2007-01-12|45.15
sqlite>

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

Re: [sqlite] How to select data from 2 lines in one line?

[Kit]

2009/8/19 Mário Anselmo Scandelari Bussmann :
> I have a table like this:
>
> petr4
> ---
> rowid|data|preabe|premax|premin|preult|voltot
> 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
> 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0
> 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0
> 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0
> 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0
> 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0
> 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0
> 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
>
> I need a select that returns data,preult,previous data and previous preult:
>
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
> 2007-01-05|46.19|2007-01-04|47.65
> 2007-01-08|46.59|2007-01-05|46.19
> 2007-01-09|45.52|2007-01-08|46.59
> 2007-01-10|45.25|2007-01-09|45.52
> 2007-01-11|45.21|2007-01-10|45.25
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15

SELECT data,preult,temp.data,temp.preult FROM petr4,petr4 AS temp
WHERE petr4.rowid=temp.rowid-1;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[Pavel Ivanov]

Without looking at your select statement it's very hard to help. But
general suggestion is insert your results into temporary table and
then issue a select on that table joined with itself with condition
like t.rowid = prev.rowid + 1.

Pavel

On Wed, Aug 19, 2009 at 10:10 AM, Mário Anselmo Scandelari
Bussmann wrote:
> I have a table like this:
>
> petr4
> ---
> rowid|data|preabe|premax|premin|preult|voltot
> 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
> 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0
> 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0
> 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0
> 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0
> 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0
> 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0
> 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
>
> I need a select that returns data,preult,previous data and previous preult:
>
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
> 2007-01-05|46.19|2007-01-04|47.65
> 2007-01-08|46.59|2007-01-05|46.19
> 2007-01-09|45.52|2007-01-08|46.59
> 2007-01-10|45.25|2007-01-09|45.52
> 2007-01-11|45.21|2007-01-10|45.25
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15
>
> How can I do that using only sql (no python, c or perl, no cursor)?
> ___
> 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] How to Select using Wild Characters?

[Igor Tandetnik]

"Pramoda M. A" 
wrote in message
news:f7846b8f3c78c049b6a1dff861f6c16f0362e...@kcinblrexb01.kpit.com
> I have to select using wild charcters? How to do it?
>
> For eg: I have to select field which should contain "sqlite"... Then
> *sqlite* is not working...

select * from mytable where somefield like '%sqlite%';
-- or
select * from mytable where somefield glob '*sqlite*';

http://sqlite.org/lang_expr.html#like

Igor Tandetnik 



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

Re: [sqlite] How to Select using Wild Characters?

[Mihai Limbasan]

Use % (percent instead of * and use _ (underscore) instead of ?

Pramoda M. A wrote:
> Hi All,
>
>  
>
> I have to select using wild charcters? How to do it?
>
> For eg: I have to select field which should contain "sqlite"... Then
> *sqlite* is not working...
>
> Pleas help me.
>
>  
>
> With Regards
>
> Pramoda.M.A
>
> KPIT Cummins Infosystems Limited | Bengaluru | Board: +91 80 30783905
>
>  
>
>   
> 
>
> ___
> 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] how to select uncomitted rows?

[Alex Katebi]

Scott,

   Because of the issues that you have raised I realized that multiple
configuration commands rolled into one transaction is not a good idea and is
not necessary. The router will act on a single command from any CLI session.
As far as command action goes it will be connection less towards CLI
sessions. When any user asks to show configuration I will show running
configuration to that user. I think a complicated design is a bad design.

Thanks!
-Alex

On Thu, Apr 17, 2008 at 8:17 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> What will happen if you use BEGIN is that multiple users can get into
> the configuration mode, but once one user gets past BEGIN and runs
> anything which updates the database, the updates in other transactions
> will start throwing SQLITE_LOCKED.  Spin up two sqlite3 command-line
> tools against the same database and check it out.
>
> If you use BEGIN IMMEDIATE, then this problem won't occur, because
> multiple threads can't get past BEGIN IMMEDIATE on the same database
> in the first place :-).
>
> What you have is basically a revision-control problem.  If you let
> multiple users configure at the same time, you're going to handle
> merging the config changes in a sensible way and handle conflicts.
>
> -scott
>
>
> On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > Scott,
> >
> >   Every user will have thier own sqlite connection. So multiple users
> are
> > allowed for configuration. There will be one router connection to
> actually
> > act on the commited configurations. The router will act on
> > individual configuration rows. The router and the users interact with
> each
> > other via the database file only. They run on seperate processes. All
> the
> > router sees is configuration rows being inserted deleted or updated by
> > whomever. It does not care. So here is my next question. If I have
> temporary
> > triggers for the CLI users to keep track of the uncommited rows. Then I
> have
> > another temoprary trigger for the router to act on the configurations
> after
> > being committed, would this work?  Would the temporary trigger in the
> router
> > connection actually trigger? I guess I need to try this out.
> >
> >   This is my own home project. I am my own boss. Once I have my design
> > figured out I will post it for analysis.
> >
> > Thanks,
> > -Alex
> >
> >
> > On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> >> OK.  I suspect that you might find exposing the SQLite transaction
> >> semantics as part of your user interface may be ... unsatisfactory.
> >> For instance, by keeping a long-lived transaction in this way, you
> >> cannot set any of the _other_ data in the config and commit it.  This
> >> would include other users, so, for instance, while one user is
> >> configuring something really complex, like firewall rules, another
> >> user would not be able to set the timezone, or turn on logging, or
> >> something like that.  I don't know, this may be satisfactory, but it
> >> seems like a regrettable thing to design into the system at such a low
> >> level (not letting multiple people configure so that they don't screw
> >> up is good, but not allowing it just because your design didn't allow
> >> it, less good).
> >>
> >> As an alternative, you might consider layering your config-management
> >> over something like the undo/redo example (*).  Since this is more
> >> explicit (_you_ craft the structures in terms of SQLite, rather than
> >> relying on SQLite's internal semantics), when upper management comes
> >> to you with some crazy feature request which does not conform to the
> >> SQL transaction model, you'll be able to change things without too
> >> much pain.
> >>
> >> -scott
> >>
> >> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
> >>
> >>
> >> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]>
> >> wrote:
> >> >   I am glad you asked. I am designing an interactive command line
> >> > interface to an ip router. A user will begin a transaction and start
> >> > configuring. At any time he can query for his configurations since
> the
> >> > begining of the transaction. When he is satisfied with his
> configuration
> >> he
> >> > will commit the configuration. After this his query should show
> nothing
> >> > until he begins another transaction. Also he might press the ?mark
> key
> >> on
> >> > his keyboard at anytime for help information or tab key for automatic
> >> > command token completion.
> >> >
> >> > So I will have to know what are the list of commands since the
> beginning
> >> > of his transaction.
> >> >
> >> >
> >> >
> >> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >> >
> >> >> I don't mean in a separate database table - I mean in an in-memory
> >> >> hashtable or array or something of the sort.  Depending on what the
> >> >> real goal you're trying to accomplish is, you might use triggers to
> >> >> call  custom function to accomplish 

Re: [sqlite] how to select uncomitted rows?

[Scott Hess]

Grr.  Copy/paste error.  The create statement was:

CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, config TEXT);

On Thu, Apr 17, 2008 at 5:20 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> Just to be clear on "try it out", I mean something like the following,
> where A) is in one shell, and B) in another.
>
> A) ...> ./sqlite3 test.db
> A) sqlite> CREATE TABLE t (id INTEGER AUTOINCREMENT PRIMARY KEY NOT
> NULL, config TEXT);
> B) ...> ./sqlite3 test.db
> B) sqlite> BEGIN;
> A) sqlite> BEGIN;
> A) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection a');
> B) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection b');
> B) SQL error: database is locked
>
> -scott
>
> On Thu, Apr 17, 2008 at 5:17 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>> What will happen if you use BEGIN is that multiple users can get into
>> the configuration mode, but once one user gets past BEGIN and runs
>> anything which updates the database, the updates in other transactions
>> will start throwing SQLITE_LOCKED.  Spin up two sqlite3 command-line
>> tools against the same database and check it out.
>>
>> If you use BEGIN IMMEDIATE, then this problem won't occur, because
>> multiple threads can't get past BEGIN IMMEDIATE on the same database
>> in the first place :-).
>>
>> What you have is basically a revision-control problem.  If you let
>> multiple users configure at the same time, you're going to handle
>> merging the config changes in a sensible way and handle conflicts.
>>
>> -scott
>>
>>
>> On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
>>> Scott,
>>>
>>>   Every user will have thier own sqlite connection. So multiple users are
>>> allowed for configuration. There will be one router connection to actually
>>> act on the commited configurations. The router will act on
>>> individual configuration rows. The router and the users interact with each
>>> other via the database file only. They run on seperate processes. All the
>>> router sees is configuration rows being inserted deleted or updated by
>>> whomever. It does not care. So here is my next question. If I have temporary
>>> triggers for the CLI users to keep track of the uncommited rows. Then I have
>>> another temoprary trigger for the router to act on the configurations after
>>> being committed, would this work?  Would the temporary trigger in the router
>>> connection actually trigger? I guess I need to try this out.
>>>
>>>   This is my own home project. I am my own boss. Once I have my design
>>> figured out I will post it for analysis.
>>>
>>> Thanks,
>>> -Alex
>>>
>>>
>>> On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>>>
 OK.  I suspect that you might find exposing the SQLite transaction
 semantics as part of your user interface may be ... unsatisfactory.
 For instance, by keeping a long-lived transaction in this way, you
 cannot set any of the _other_ data in the config and commit it.  This
 would include other users, so, for instance, while one user is
 configuring something really complex, like firewall rules, another
 user would not be able to set the timezone, or turn on logging, or
 something like that.  I don't know, this may be satisfactory, but it
 seems like a regrettable thing to design into the system at such a low
 level (not letting multiple people configure so that they don't screw
 up is good, but not allowing it just because your design didn't allow
 it, less good).

 As an alternative, you might consider layering your config-management
 over something like the undo/redo example (*).  Since this is more
 explicit (_you_ craft the structures in terms of SQLite, rather than
 relying on SQLite's internal semantics), when upper management comes
 to you with some crazy feature request which does not conform to the
 SQL transaction model, you'll be able to change things without too
 much pain.

 -scott

 (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo


 On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]>
 wrote:
 >   I am glad you asked. I am designing an interactive command line
 > interface to an ip router. A user will begin a transaction and start
 > configuring. At any time he can query for his configurations since the
 > begining of the transaction. When he is satisfied with his configuration
 he
 > will commit the configuration. After this his query should show nothing
 > until he begins another transaction. Also he might press the ?mark key
 on
 > his keyboard at anytime for help information or tab key for automatic
 > command token completion.
 >
 > So I will have to know what are the list of commands since the beginning
 > of his transaction.
 >
 >
 >
 > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
 >
 >> I don't mean in a separate database table - I 

Re: [sqlite] how to select uncomitted rows?

[Scott Hess]

Just to be clear on "try it out", I mean something like the following,
where A) is in one shell, and B) in another.

A) ...> ./sqlite3 test.db
A) sqlite> CREATE TABLE t (id INTEGER AUTOINCREMENT PRIMARY KEY NOT
NULL, config TEXT);
B) ...> ./sqlite3 test.db
B) sqlite> BEGIN;
A) sqlite> BEGIN;
A) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection a');
B) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection b');
B) SQL error: database is locked

-scott

On Thu, Apr 17, 2008 at 5:17 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> What will happen if you use BEGIN is that multiple users can get into
> the configuration mode, but once one user gets past BEGIN and runs
> anything which updates the database, the updates in other transactions
> will start throwing SQLITE_LOCKED.  Spin up two sqlite3 command-line
> tools against the same database and check it out.
>
> If you use BEGIN IMMEDIATE, then this problem won't occur, because
> multiple threads can't get past BEGIN IMMEDIATE on the same database
> in the first place :-).
>
> What you have is basically a revision-control problem.  If you let
> multiple users configure at the same time, you're going to handle
> merging the config changes in a sensible way and handle conflicts.
>
> -scott
>
>
> On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
>> Scott,
>>
>>   Every user will have thier own sqlite connection. So multiple users are
>> allowed for configuration. There will be one router connection to actually
>> act on the commited configurations. The router will act on
>> individual configuration rows. The router and the users interact with each
>> other via the database file only. They run on seperate processes. All the
>> router sees is configuration rows being inserted deleted or updated by
>> whomever. It does not care. So here is my next question. If I have temporary
>> triggers for the CLI users to keep track of the uncommited rows. Then I have
>> another temoprary trigger for the router to act on the configurations after
>> being committed, would this work?  Would the temporary trigger in the router
>> connection actually trigger? I guess I need to try this out.
>>
>>   This is my own home project. I am my own boss. Once I have my design
>> figured out I will post it for analysis.
>>
>> Thanks,
>> -Alex
>>
>>
>> On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>>
>>> OK.  I suspect that you might find exposing the SQLite transaction
>>> semantics as part of your user interface may be ... unsatisfactory.
>>> For instance, by keeping a long-lived transaction in this way, you
>>> cannot set any of the _other_ data in the config and commit it.  This
>>> would include other users, so, for instance, while one user is
>>> configuring something really complex, like firewall rules, another
>>> user would not be able to set the timezone, or turn on logging, or
>>> something like that.  I don't know, this may be satisfactory, but it
>>> seems like a regrettable thing to design into the system at such a low
>>> level (not letting multiple people configure so that they don't screw
>>> up is good, but not allowing it just because your design didn't allow
>>> it, less good).
>>>
>>> As an alternative, you might consider layering your config-management
>>> over something like the undo/redo example (*).  Since this is more
>>> explicit (_you_ craft the structures in terms of SQLite, rather than
>>> relying on SQLite's internal semantics), when upper management comes
>>> to you with some crazy feature request which does not conform to the
>>> SQL transaction model, you'll be able to change things without too
>>> much pain.
>>>
>>> -scott
>>>
>>> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>>>
>>>
>>> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]>
>>> wrote:
>>> >   I am glad you asked. I am designing an interactive command line
>>> > interface to an ip router. A user will begin a transaction and start
>>> > configuring. At any time he can query for his configurations since the
>>> > begining of the transaction. When he is satisfied with his configuration
>>> he
>>> > will commit the configuration. After this his query should show nothing
>>> > until he begins another transaction. Also he might press the ?mark key
>>> on
>>> > his keyboard at anytime for help information or tab key for automatic
>>> > command token completion.
>>> >
>>> > So I will have to know what are the list of commands since the beginning
>>> > of his transaction.
>>> >
>>> >
>>> >
>>> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>>> >
>>> >> I don't mean in a separate database table - I mean in an in-memory
>>> >> hashtable or array or something of the sort.  Depending on what the
>>> >> real goal you're trying to accomplish is, you might use triggers to
>>> >> call  custom function to accomplish this.
>>> >>
>>> >> You presumably desire to get this information in the interests of
>>> >> 

Re: [sqlite] how to select uncomitted rows?

[Scott Hess]

What will happen if you use BEGIN is that multiple users can get into
the configuration mode, but once one user gets past BEGIN and runs
anything which updates the database, the updates in other transactions
will start throwing SQLITE_LOCKED.  Spin up two sqlite3 command-line
tools against the same database and check it out.

If you use BEGIN IMMEDIATE, then this problem won't occur, because
multiple threads can't get past BEGIN IMMEDIATE on the same database
in the first place :-).

What you have is basically a revision-control problem.  If you let
multiple users configure at the same time, you're going to handle
merging the config changes in a sensible way and handle conflicts.

-scott


On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> Scott,
>
>   Every user will have thier own sqlite connection. So multiple users are
> allowed for configuration. There will be one router connection to actually
> act on the commited configurations. The router will act on
> individual configuration rows. The router and the users interact with each
> other via the database file only. They run on seperate processes. All the
> router sees is configuration rows being inserted deleted or updated by
> whomever. It does not care. So here is my next question. If I have temporary
> triggers for the CLI users to keep track of the uncommited rows. Then I have
> another temoprary trigger for the router to act on the configurations after
> being committed, would this work?  Would the temporary trigger in the router
> connection actually trigger? I guess I need to try this out.
>
>   This is my own home project. I am my own boss. Once I have my design
> figured out I will post it for analysis.
>
> Thanks,
> -Alex
>
>
> On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>
>> OK.  I suspect that you might find exposing the SQLite transaction
>> semantics as part of your user interface may be ... unsatisfactory.
>> For instance, by keeping a long-lived transaction in this way, you
>> cannot set any of the _other_ data in the config and commit it.  This
>> would include other users, so, for instance, while one user is
>> configuring something really complex, like firewall rules, another
>> user would not be able to set the timezone, or turn on logging, or
>> something like that.  I don't know, this may be satisfactory, but it
>> seems like a regrettable thing to design into the system at such a low
>> level (not letting multiple people configure so that they don't screw
>> up is good, but not allowing it just because your design didn't allow
>> it, less good).
>>
>> As an alternative, you might consider layering your config-management
>> over something like the undo/redo example (*).  Since this is more
>> explicit (_you_ craft the structures in terms of SQLite, rather than
>> relying on SQLite's internal semantics), when upper management comes
>> to you with some crazy feature request which does not conform to the
>> SQL transaction model, you'll be able to change things without too
>> much pain.
>>
>> -scott
>>
>> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>>
>>
>> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]>
>> wrote:
>> >   I am glad you asked. I am designing an interactive command line
>> > interface to an ip router. A user will begin a transaction and start
>> > configuring. At any time he can query for his configurations since the
>> > begining of the transaction. When he is satisfied with his configuration
>> he
>> > will commit the configuration. After this his query should show nothing
>> > until he begins another transaction. Also he might press the ?mark key
>> on
>> > his keyboard at anytime for help information or tab key for automatic
>> > command token completion.
>> >
>> > So I will have to know what are the list of commands since the beginning
>> > of his transaction.
>> >
>> >
>> >
>> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>> >
>> >> I don't mean in a separate database table - I mean in an in-memory
>> >> hashtable or array or something of the sort.  Depending on what the
>> >> real goal you're trying to accomplish is, you might use triggers to
>> >> call  custom function to accomplish this.
>> >>
>> >> You presumably desire to get this information in the interests of
>> >> implementing a solution to a problem.  You should perhaps post asking
>> >> for suggestions on how to solve the problem.  I think the question
>> >> itself probably indicates that there's a disconnect in how you're
>> >> trying to model the problem, but without knowing what the problem is,
>> >> it's hard to do much.
>> >>
>> >> -scott
>> >>
>> >> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]>
>> >> wrote:
>> >> > The reason I did not keep track in a seperate table was because I
>> wanted
>> >> to
>> >> > do it using triggers. But triggers don't trigger until commit.
>> >> >
>> >> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess 

Re: [sqlite] how to select uncomitted rows?

[Alex Katebi]

Scott,

   Every user will have thier own sqlite connection. So multiple users are
allowed for configuration. There will be one router connection to actually
act on the commited configurations. The router will act on
individual configuration rows. The router and the users interact with each
other via the database file only. They run on seperate processes. All the
router sees is configuration rows being inserted deleted or updated by
whomever. It does not care. So here is my next question. If I have temporary
triggers for the CLI users to keep track of the uncommited rows. Then I have
another temoprary trigger for the router to act on the configurations after
being committed, would this work?  Would the temporary trigger in the router
connection actually trigger? I guess I need to try this out.

   This is my own home project. I am my own boss. Once I have my design
figured out I will post it for analysis.

Thanks,
-Alex


On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> OK.  I suspect that you might find exposing the SQLite transaction
> semantics as part of your user interface may be ... unsatisfactory.
> For instance, by keeping a long-lived transaction in this way, you
> cannot set any of the _other_ data in the config and commit it.  This
> would include other users, so, for instance, while one user is
> configuring something really complex, like firewall rules, another
> user would not be able to set the timezone, or turn on logging, or
> something like that.  I don't know, this may be satisfactory, but it
> seems like a regrettable thing to design into the system at such a low
> level (not letting multiple people configure so that they don't screw
> up is good, but not allowing it just because your design didn't allow
> it, less good).
>
> As an alternative, you might consider layering your config-management
> over something like the undo/redo example (*).  Since this is more
> explicit (_you_ craft the structures in terms of SQLite, rather than
> relying on SQLite's internal semantics), when upper management comes
> to you with some crazy feature request which does not conform to the
> SQL transaction model, you'll be able to change things without too
> much pain.
>
> -scott
>
> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>
>
> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> >   I am glad you asked. I am designing an interactive command line
> > interface to an ip router. A user will begin a transaction and start
> > configuring. At any time he can query for his configurations since the
> > begining of the transaction. When he is satisfied with his configuration
> he
> > will commit the configuration. After this his query should show nothing
> > until he begins another transaction. Also he might press the ?mark key
> on
> > his keyboard at anytime for help information or tab key for automatic
> > command token completion.
> >
> > So I will have to know what are the list of commands since the beginning
> > of his transaction.
> >
> >
> >
> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> >> I don't mean in a separate database table - I mean in an in-memory
> >> hashtable or array or something of the sort.  Depending on what the
> >> real goal you're trying to accomplish is, you might use triggers to
> >> call  custom function to accomplish this.
> >>
> >> You presumably desire to get this information in the interests of
> >> implementing a solution to a problem.  You should perhaps post asking
> >> for suggestions on how to solve the problem.  I think the question
> >> itself probably indicates that there's a disconnect in how you're
> >> trying to model the problem, but without knowing what the problem is,
> >> it's hard to do much.
> >>
> >> -scott
> >>
> >> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]>
> >> wrote:
> >> > The reason I did not keep track in a seperate table was because I
> wanted
> >> to
> >> > do it using triggers. But triggers don't trigger until commit.
> >> >
> >> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >> >
> >> >> Until the data is committed, it's not really in the database.  If
> you
> >> >> crash, it will be rolled back.  So if it's really important to know
> >> >> what data has been written to the database but not committed, why
> >> >> don't you just track what you're writing to the database in an
> >> >> in-memory data structure of some sort?  Or, to save space, just
> track
> >> >> the rowid of the rows you modify.
> >> >>
> >> >> -scott
> >> >>
> >> >>
> >> >> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]
> >
> >> >> wrote:
> >> >> > Hi Richard,
> >> >> >
> >> >> > create table t1 (name);
> >> >> > insert into t1 values ('Alex');
> >> >> > begin;
> >> >> > insert into t1 values ('Richard');
> >> >> > select * from t1;
> >> >> >
> >> >> > How can I select only the second row in the above example?
> >> >> > If 

Re: [sqlite] how to select uncomitted rows?

[Alex Katebi]

   I am glad you asked. I am designing an interactive command line
interface to an ip router. A user will begin a transaction and start
configuring. At any time he can query for his configurations since the
begining of the transaction. When he is satisfied with his configuration he
will commit the configuration. After this his query should show nothing
until he begins another transaction. Also he might press the ?mark key on
his keyboard at anytime for help information or tab key for automatic
command token completion.

So I will have to know what are the list of commands since the beginning
of his transaction.



On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> I don't mean in a separate database table - I mean in an in-memory
> hashtable or array or something of the sort.  Depending on what the
> real goal you're trying to accomplish is, you might use triggers to
> call  custom function to accomplish this.
>
> You presumably desire to get this information in the interests of
> implementing a solution to a problem.  You should perhaps post asking
> for suggestions on how to solve the problem.  I think the question
> itself probably indicates that there's a disconnect in how you're
> trying to model the problem, but without knowing what the problem is,
> it's hard to do much.
>
> -scott
>
> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > The reason I did not keep track in a seperate table was because I wanted
> to
> > do it using triggers. But triggers don't trigger until commit.
> >
> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> >> Until the data is committed, it's not really in the database.  If you
> >> crash, it will be rolled back.  So if it's really important to know
> >> what data has been written to the database but not committed, why
> >> don't you just track what you're writing to the database in an
> >> in-memory data structure of some sort?  Or, to save space, just track
> >> the rowid of the rows you modify.
> >>
> >> -scott
> >>
> >>
> >> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
> >> wrote:
> >> > Hi Richard,
> >> >
> >> > create table t1 (name);
> >> > insert into t1 values ('Alex');
> >> > begin;
> >> > insert into t1 values ('Richard');
> >> > select * from t1;
> >> >
> >> > How can I select only the second row in the above example?
> >> > If there is not an easy way to do this I would probably have to use
> >> another
> >> > connection then diff the two selects right?
> >> >
> >> > Thanks,
> >> > -Alex
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]>
> wrote:
> >> >
> >> >>
> >> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> >> >> > Is there a way to select rows that have not been committed yet?
> >> >> >
> >> >>
> >> >> No.  SQLite doesn't really commit rows.  It commits pages.  A
> >> >> single page might hold multiple rows, only some of which might
> >> >> have changed.  Or a single row might span multiple pages.
> >> >>
> >> >>
> >> >> D. Richard Hipp
> >> >> [EMAIL PROTECTED]
> >> >>
> >> >>
> >> >>
> >> >> ___
> >> >> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to select uncomitted rows?

[Alex Katebi]

Yap I was wrong about triggers. Triggers are part of the same connection. So
I will try your suggestions. I will let you know how I made out. And thanks
so much for clearing my mistakes.
-Alex

On Thu, Apr 17, 2008 at 6:06 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> I remember trying it before but I will try it again. Maybe I was wrong. I
> will let you know.
> Thanks!
>
>   On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]>
> wrote:
>
> > Alex Katebi wrote:
> > > But triggers don't trigger until commit.
> > >
> >
> > That is not true.
> >
> > Trigger code executes inline with the statement that caused the trigger
> > to fire.
> >
> > Try a few triggers with the command line shell to convince yourself.
> >
> > Dennis Cote
> >  ___
> > 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] how to select uncomitted rows?

[Alex Katebi]

I remember trying it before but I will try it again. Maybe I was wrong. I
will let you know.
Thanks!

On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > But triggers don't trigger until commit.
> >
>
> That is not true.
>
> Trigger code executes inline with the statement that caused the trigger
> to fire.
>
> Try a few triggers with the command line shell to convince yourself.
>
> Dennis Cote
>  ___
> 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] how to select uncomitted rows?

[Dennis Cote]

Alex Katebi wrote:
> But triggers don't trigger until commit.
> 

That is not true.

Trigger code executes inline with the statement that caused the trigger 
to fire.

Try a few triggers with the command line shell to convince yourself.

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

Re: [sqlite] how to select uncomitted rows?

[Scott Hess]

I don't mean in a separate database table - I mean in an in-memory
hashtable or array or something of the sort.  Depending on what the
real goal you're trying to accomplish is, you might use triggers to
call  custom function to accomplish this.

You presumably desire to get this information in the interests of
implementing a solution to a problem.  You should perhaps post asking
for suggestions on how to solve the problem.  I think the question
itself probably indicates that there's a disconnect in how you're
trying to model the problem, but without knowing what the problem is,
it's hard to do much.

-scott

On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> The reason I did not keep track in a seperate table was because I wanted to
> do it using triggers. But triggers don't trigger until commit.
>
> On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>
>> Until the data is committed, it's not really in the database.  If you
>> crash, it will be rolled back.  So if it's really important to know
>> what data has been written to the database but not committed, why
>> don't you just track what you're writing to the database in an
>> in-memory data structure of some sort?  Or, to save space, just track
>> the rowid of the rows you modify.
>>
>> -scott
>>
>>
>> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
>> wrote:
>> > Hi Richard,
>> >
>> > create table t1 (name);
>> > insert into t1 values ('Alex');
>> > begin;
>> > insert into t1 values ('Richard');
>> > select * from t1;
>> >
>> > How can I select only the second row in the above example?
>> > If there is not an easy way to do this I would probably have to use
>> another
>> > connection then diff the two selects right?
>> >
>> > Thanks,
>> > -Alex
>> >
>> >
>> >
>> >
>> >
>> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>> >
>> >>
>> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
>> >> > Is there a way to select rows that have not been committed yet?
>> >> >
>> >>
>> >> No.  SQLite doesn't really commit rows.  It commits pages.  A
>> >> single page might hold multiple rows, only some of which might
>> >> have changed.  Or a single row might span multiple pages.
>> >>
>> >>
>> >> D. Richard Hipp
>> >> [EMAIL PROTECTED]
>> >>
>> >>
>> >>
>> >> ___
>> >> 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] how to select uncomitted rows?

[Marco Bambini]

Another approach could be to create an in-memory database (and in in- 
memory table, like CREATE TABLE last_transaction(id INTEGER);)
and after each write operation save the rowid of the row using  
sqlite3_last_insert_rowid (in C) or using SELECT last_insert_rowid();  
(SQL) into that table.

---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



On Apr 17, 2008, at 9:43 PM, Alex Katebi wrote:

> The reason I did not keep track in a seperate table was because I  
> wanted to
> do it using triggers. But triggers don't trigger until commit.
>
> On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>
>> Until the data is committed, it's not really in the database.  If you
>> crash, it will be rolled back.  So if it's really important to know
>> what data has been written to the database but not committed, why
>> don't you just track what you're writing to the database in an
>> in-memory data structure of some sort?  Or, to save space, just track
>> the rowid of the rows you modify.
>>
>> -scott
>>
>>
>> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
>> wrote:
>>> Hi Richard,
>>>
>>> create table t1 (name);
>>> insert into t1 values ('Alex');
>>> begin;
>>> insert into t1 values ('Richard');
>>> select * from t1;
>>>
>>> How can I select only the second row in the above example?
>>> If there is not an easy way to do this I would probably have to use
>> another
>>> connection then diff the two selects right?
>>>
>>> Thanks,
>>> -Alex
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]>  
>>> wrote:
>>>

 On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> Is there a way to select rows that have not been committed yet?
>

 No.  SQLite doesn't really commit rows.  It commits pages.  A
 single page might hold multiple rows, only some of which might
 have changed.  Or a single row might span multiple pages.


 D. Richard Hipp
 [EMAIL PROTECTED]



 ___
 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] how to select uncomitted rows?

[Alex Katebi]

The reason I did not keep track in a seperate table was because I wanted to
do it using triggers. But triggers don't trigger until commit.

On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> Until the data is committed, it's not really in the database.  If you
> crash, it will be rolled back.  So if it's really important to know
> what data has been written to the database but not committed, why
> don't you just track what you're writing to the database in an
> in-memory data structure of some sort?  Or, to save space, just track
> the rowid of the rows you modify.
>
> -scott
>
>
> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > Hi Richard,
> >
> > create table t1 (name);
> > insert into t1 values ('Alex');
> > begin;
> > insert into t1 values ('Richard');
> > select * from t1;
> >
> > How can I select only the second row in the above example?
> > If there is not an easy way to do this I would probably have to use
> another
> > connection then diff the two selects right?
> >
> > Thanks,
> > -Alex
> >
> >
> >
> >
> >
> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> >
> >>
> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> >> > Is there a way to select rows that have not been committed yet?
> >> >
> >>
> >> No.  SQLite doesn't really commit rows.  It commits pages.  A
> >> single page might hold multiple rows, only some of which might
> >> have changed.  Or a single row might span multiple pages.
> >>
> >>
> >> D. Richard Hipp
> >> [EMAIL PROTECTED]
> >>
> >>
> >>
> >> ___
> >> 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] how to select uncomitted rows?

[Scott Hess]

Until the data is committed, it's not really in the database.  If you
crash, it will be rolled back.  So if it's really important to know
what data has been written to the database but not committed, why
don't you just track what you're writing to the database in an
in-memory data structure of some sort?  Or, to save space, just track
the rowid of the rows you modify.

-scott


On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> Hi Richard,
>
> create table t1 (name);
> insert into t1 values ('Alex');
> begin;
> insert into t1 values ('Richard');
> select * from t1;
>
> How can I select only the second row in the above example?
> If there is not an easy way to do this I would probably have to use another
> connection then diff the two selects right?
>
> Thanks,
> -Alex
>
>
>
>
>
> On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
>>
>> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
>> > Is there a way to select rows that have not been committed yet?
>> >
>>
>> No.  SQLite doesn't really commit rows.  It commits pages.  A
>> single page might hold multiple rows, only some of which might
>> have changed.  Or a single row might span multiple pages.
>>
>>
>> D. Richard Hipp
>> [EMAIL PROTECTED]
>>
>>
>>
>> ___
>> 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] how to select uncomitted rows?

[Alex Katebi]

Hi Richard,

create table t1 (name);
insert into t1 values ('Alex');
begin;
insert into t1 values ('Richard');
select * from t1;

How can I select only the second row in the above example?
If there is not an easy way to do this I would probably have to use another
connection then diff the two selects right?

Thanks,
-Alex





On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> > Is there a way to select rows that have not been committed yet?
> >
>
> No.  SQLite doesn't really commit rows.  It commits pages.  A
> single page might hold multiple rows, only some of which might
> have changed.  Or a single row might span multiple pages.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] how to select uncomitted rows?

[D. Richard Hipp]

On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> Is there a way to select rows that have not been committed yet?
>

No.  SQLite doesn't really commit rows.  It commits pages.  A
single page might hold multiple rows, only some of which might
have changed.  Or a single row might span multiple pages.


D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] How to select Strict Affinity or No Affinity modes?Attention: DRH

[John Stanton]

I found that it was a fairly simple change to Sqlite to stop it changing 
formats and causing grief.  Out of the box it merges nicely with 
scripting environments like Javascript, Python and TCL but can be a pain 
in other places.  Fortunately the changes needed where format changes 
are detrimental are tiny.

Fowler, Jeff wrote:
> I agree. After many years with SQL Server and Oracle (but new to
> SQLite), the concept of storing different datatypes within the same
> field is something I've had difficulty grasping. I'm not saying it's a
> bad thing, but from a business perspective I can't think of a situation
> where we would not want strict affinity. So if it becomes an option
> we'll use it throughout our application.
> 
> - Jeff
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Samuel Neff
> Sent: Friday, February 08, 2008 11:52 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to select Strict Affinity or No Affinity
> modes?Attention: DRH
> 
> I would like to have strict affinity mode too.  In our schemas we use
> check constraints to enforce strict affinity.  Unless you're working in
> a dynamic typed environment, I can't imagine why you would want to have
> inconsistent data within a single database field.  Also for consistency
> with (every?) other database engine out there, a strict affinity mode
> would be good.
> Strict affinity will also benefit all wrapper writers who write wrappers
> following a framework that assumes strict field typing (which I think is
> pretty much all of them since all other db's have strongly typed
> fields).
> 
> Thanks,
> 
> Sam
> 
> 
> On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote:
> 
>> I second the strict affinity mode as an optional feature, for the same
> 
>> reasons as Lee.
>>
>>A while back I ran into a problem while using the bit and feature 
>> of sqlite and got unexpected results because sqlite changed the type 
>> from a 64bit integer into a real. (I think)... In this case it would 
>> have been simpler to debug, if there had been a type conversion
> warning or a failure.
>> Regards,
>> Ken
>>
>>
> ___
> 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] How to select Strict Affinity or No Affinity modes?Attention: DRH

[Fowler, Jeff]

I agree. After many years with SQL Server and Oracle (but new to
SQLite), the concept of storing different datatypes within the same
field is something I've had difficulty grasping. I'm not saying it's a
bad thing, but from a business perspective I can't think of a situation
where we would not want strict affinity. So if it becomes an option
we'll use it throughout our application.

- Jeff


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Samuel Neff
Sent: Friday, February 08, 2008 11:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to select Strict Affinity or No Affinity
modes?Attention: DRH

I would like to have strict affinity mode too.  In our schemas we use
check constraints to enforce strict affinity.  Unless you're working in
a dynamic typed environment, I can't imagine why you would want to have
inconsistent data within a single database field.  Also for consistency
with (every?) other database engine out there, a strict affinity mode
would be good.
Strict affinity will also benefit all wrapper writers who write wrappers
following a framework that assumes strict field typing (which I think is
pretty much all of them since all other db's have strongly typed
fields).

Thanks,

Sam


On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote:

> I second the strict affinity mode as an optional feature, for the same

> reasons as Lee.
>
>A while back I ran into a problem while using the bit and feature 
> of sqlite and got unexpected results because sqlite changed the type 
> from a 64bit integer into a real. (I think)... In this case it would 
> have been simpler to debug, if there had been a type conversion
warning or a failure.
>
> Regards,
> Ken
>
>
___
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] How to select Strict Affinity or No Affinity modes?

[Ken]

Yes choice is good, Either way I'm greatful for an Excellent Tool, I can 
workaround and Live without strict Affinity. But the option to enable it would 
be a welcome feature.

Would there be any performance implications of Strict Affinity, either positive 
or negative?



Samuel Neff <[EMAIL PROTECTED]> wrote: But the important point is that no 
matter how much discussion we have, we
will never all agree that untyped is better than typed or that typed is
better than typed.  That's why an option so individual developers can choose
is good.  We don't have to agree, with an option we can agree to disagree.

Sam


On Feb 7, 2008 11:46 PM, Roger Binns  wrote:

>
> There are also a whole school of people who believe that dynamic typing
> as used in SQLite is far more productive and results in less code.   For
> example the Python programming language is typed in a similar way.
>
>   http://en.wikipedia.org/wiki/Duck_typing
>
> There are many sites with many discussion threads containing opinions,
> anecdotes and evidence and code supporting all the positions on this
> issue.  Feel free to discuss the merits there.
>
> Roger
>
>
___
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] How to select Strict Affinity or No Affinity modes?

[Samuel Neff]

But the important point is that no matter how much discussion we have, we
will never all agree that untyped is better than typed or that typed is
better than typed.  That's why an option so individual developers can choose
is good.  We don't have to agree, with an option we can agree to disagree.

Sam


On Feb 7, 2008 11:46 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

>
> There are also a whole school of people who believe that dynamic typing
> as used in SQLite is far more productive and results in less code.   For
> example the Python programming language is typed in a similar way.
>
>   http://en.wikipedia.org/wiki/Duck_typing
>
> There are many sites with many discussion threads containing opinions,
> anecdotes and evidence and code supporting all the positions on this
> issue.  Feel free to discuss the merits there.
>
> Roger
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH

[Samuel Neff]

I would like to have strict affinity mode too.  In our schemas we use check
constraints to enforce strict affinity.  Unless you're working in a dynamic
typed environment, I can't imagine why you would want to have inconsistent
data within a single database field.  Also for consistency with (every?)
other database engine out there, a strict affinity mode would be good.
Strict affinity will also benefit all wrapper writers who write wrappers
following a framework that assumes strict field typing (which I think is
pretty much all of them since all other db's have strongly typed fields).

Thanks,

Sam


On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote:

> I second the strict affinity mode as an optional feature, for the same
> reasons as Lee.
>
>A while back I ran into a problem while using the bit and feature of
> sqlite and got unexpected results because sqlite changed the type from a
> 64bit integer into a real. (I think)... In this case it would have been
> simpler to debug, if there had been a type conversion warning or a failure.
>
> Regards,
> Ken
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH

[Ken]

I second the strict affinity mode as an optional feature, for the same reasons 
as Lee.

A while back I ran into a problem while using the bit and feature of sqlite 
and got unexpected results because sqlite changed the type from a 64bit integer 
into a real. (I think)... In this case it would have been simpler to debug, if 
there had been a type conversion warning or a failure.

Regards,
Ken



Lee Crain <[EMAIL PROTECTED]> wrote: DRH,

">> Can you explain why you think strict affinity mode
>> might be beneficial to you?  If somebody can provide a good
>> enough rational to justify strict affinity mode, we might just put it
>> in."

My response to your request for an example of a benefit is that I have
always been an adherent of strict datatyping as a means of trapping
inadvertent software development errors. It's just one of the many aspects
of my self-checking software that makes certain everything is done on
purpose and not by chance. 

Lee Crain




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott Chapman
Sent: Wednesday, February 06, 2008 8:23 AM
To: D. Richard Hipp
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes?

I take it that there's no way to work around this currently?

Scott

Scott Chapman wrote:
> D. Richard Hipp wrote:
>   
>> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote:
>>
>>   
>> 
>>> I've looked high and low and can't find a way to invoke the other 2
>>> affinity modes.  Are they available? I'm on 3.5.4.
>>> 
>>>   
>> The concept of "strict" affinity mode was briefly discussed years
>> ago, but we never implemented it, having never seen any benefit
>> for such a thing.  Can you explain why you think strict affinity mode
>> might be beneficial to you?  If somebody can provide a good
>> enough rational to justify strict affinity mode, we might just put it
>> in.
>>   
>> 
> I'm working on a Python adapter that goes on top of APSW.  It will 
> enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and 
> automatically convert these to and from Python's respective data types.
>
> The case I'm dealing with that is not working like I want is the case of

> NUMERIC column type.  In SQLite, this column type gets an affinity of 
> REAL. If I put in a value to the column as a string literal, say 
> '123.23', it's stored as a REAL even though I specified it as a string 
> in quotes.  I want it to store it as a string.  The only way I've found 
> to fix this is to use a column type of NUMERIC_TEXT.  The presense of 
> "TEXT" in the column type changes the affinity to string.  This is not 
> very elegant and I was looking for any other way to make this work 
> correctly.  "No Affinity" would probably work, if I understand it 
> correctly.
>
> I want to avoid the use of REAL types in this case because they can lead

> to rounding errors, which is the whole purpose of the NUMERIC type to 
> begin with, in my understanding.  I also would like to be able to make 
> the column type just NUMERIC as that is compilant with the SQL standard.
>
> Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes 
> page.  If there are no plans to implement these, please consider 
> removing them from the docs.
>
> Thanks!
> Scott
>
> ___
> 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] How to select Strict Affinity or No Affinity modes?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Lee Crain wrote:
> My response to your request for an example of a benefit is that I have
> always been an adherent of strict datatyping as a means of trapping
> inadvertent software development errors.

There are also a whole school of people who believe that dynamic typing
as used in SQLite is far more productive and results in less code.   For
example the Python programming language is typed in a similar way.

   http://en.wikipedia.org/wiki/Duck_typing

There are many sites with many discussion threads containing opinions,
anecdotes and evidence and code supporting all the positions on this
issue.  Feel free to discuss the merits there.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHq96dmOOfHg372QQRAnA/AJ9cHWhoprB0l1HAczzaQPiwNMgLZwCgr6tC
aop4JoSVzKZp5UPwvr88WFs=
=I8av
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH

[Lee Crain]

DRH,

">> Can you explain why you think strict affinity mode
>> might be beneficial to you?  If somebody can provide a good
>> enough rational to justify strict affinity mode, we might just put it
>> in."

My response to your request for an example of a benefit is that I have
always been an adherent of strict datatyping as a means of trapping
inadvertent software development errors. It's just one of the many aspects
of my self-checking software that makes certain everything is done on
purpose and not by chance. 

Lee Crain




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott Chapman
Sent: Wednesday, February 06, 2008 8:23 AM
To: D. Richard Hipp
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes?

I take it that there's no way to work around this currently?

Scott

Scott Chapman wrote:
> D. Richard Hipp wrote:
>   
>> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote:
>>
>>   
>> 
>>> I've looked high and low and can't find a way to invoke the other 2
>>> affinity modes.  Are they available? I'm on 3.5.4.
>>> 
>>>   
>> The concept of "strict" affinity mode was briefly discussed years
>> ago, but we never implemented it, having never seen any benefit
>> for such a thing.  Can you explain why you think strict affinity mode
>> might be beneficial to you?  If somebody can provide a good
>> enough rational to justify strict affinity mode, we might just put it
>> in.
>>   
>> 
> I'm working on a Python adapter that goes on top of APSW.  It will 
> enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and 
> automatically convert these to and from Python's respective data types.
>
> The case I'm dealing with that is not working like I want is the case of

> NUMERIC column type.  In SQLite, this column type gets an affinity of 
> REAL. If I put in a value to the column as a string literal, say 
> '123.23', it's stored as a REAL even though I specified it as a string 
> in quotes.  I want it to store it as a string.  The only way I've found 
> to fix this is to use a column type of NUMERIC_TEXT.  The presense of 
> "TEXT" in the column type changes the affinity to string.  This is not 
> very elegant and I was looking for any other way to make this work 
> correctly.  "No Affinity" would probably work, if I understand it 
> correctly.
>
> I want to avoid the use of REAL types in this case because they can lead

> to rounding errors, which is the whole purpose of the NUMERIC type to 
> begin with, in my understanding.  I also would like to be able to make 
> the column type just NUMERIC as that is compilant with the SQL standard.
>
> Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes 
> page.  If there are no plans to implement these, please consider 
> removing them from the docs.
>
> Thanks!
> Scott
>
> ___
> 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] How to select Strict Affinity or No Affinity modes?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Scott Chapman wrote:
> I take it that there's no way to work around this currently?

You can leave out the type for each column when declaring the table
schema and no affinity conversions will happen.

Alternatively, if you require that a type be declared then you can make
up your own scheme that doesn't include any of the strings that SQLite
picks up, somewhat similar to how C++ compilers do it:

  http://en.wikipedia.org/wiki/Name_mangling

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHqek7mOOfHg372QQRAm5XAJ0T2F/dA3TNEejFyFncfKAZkPIgywCglrG2
t0sPG/jg50Kevkb2eZQKUfs=
=GlXb
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select Strict Affinity or No Affinity modes?

[Scott Chapman]

I take it that there's no way to work around this currently?

Scott

Scott Chapman wrote:
> D. Richard Hipp wrote:
>   
>> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote:
>>
>>   
>> 
>>> I've looked high and low and can't find a way to invoke the other 2
>>> affinity modes.  Are they available? I'm on 3.5.4.
>>> 
>>>   
>> The concept of "strict" affinity mode was briefly discussed years
>> ago, but we never implemented it, having never seen any benefit
>> for such a thing.  Can you explain why you think strict affinity mode
>> might be beneficial to you?  If somebody can provide a good
>> enough rational to justify strict affinity mode, we might just put it
>> in.
>>   
>> 
> I'm working on a Python adapter that goes on top of APSW.  It will 
> enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and 
> automatically convert these to and from Python's respective data types.
>
> The case I'm dealing with that is not working like I want is the case of 
> NUMERIC column type.  In SQLite, this column type gets an affinity of 
> REAL. If I put in a value to the column as a string literal, say 
> '123.23', it's stored as a REAL even though I specified it as a string 
> in quotes.  I want it to store it as a string.  The only way I've found 
> to fix this is to use a column type of NUMERIC_TEXT.  The presense of 
> "TEXT" in the column type changes the affinity to string.  This is not 
> very elegant and I was looking for any other way to make this work 
> correctly.  "No Affinity" would probably work, if I understand it 
> correctly.
>
> I want to avoid the use of REAL types in this case because they can lead 
> to rounding errors, which is the whole purpose of the NUMERIC type to 
> begin with, in my understanding.  I also would like to be able to make 
> the column type just NUMERIC as that is compilant with the SQL standard.
>
> Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes 
> page.  If there are no plans to implement these, please consider 
> removing them from the docs.
>
> Thanks!
> Scott
>
> ___
> 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] How to select Strict Affinity or No Affinity modes?

[Scott Chapman]

D. Richard Hipp wrote:
> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote:
>
>   
>> I've looked high and low and can't find a way to invoke the other 2
>> affinity modes.  Are they available? I'm on 3.5.4.
>> 
> The concept of "strict" affinity mode was briefly discussed years
> ago, but we never implemented it, having never seen any benefit
> for such a thing.  Can you explain why you think strict affinity mode
> might be beneficial to you?  If somebody can provide a good
> enough rational to justify strict affinity mode, we might just put it
> in.
>   
I'm working on a Python adapter that goes on top of APSW.  It will 
enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and 
automatically convert these to and from Python's respective data types.

The case I'm dealing with that is not working like I want is the case of 
NUMERIC column type.  In SQLite, this column type gets an affinity of 
REAL. If I put in a value to the column as a string literal, say 
'123.23', it's stored as a REAL even though I specified it as a string 
in quotes.  I want it to store it as a string.  The only way I've found 
to fix this is to use a column type of NUMERIC_TEXT.  The presense of 
"TEXT" in the column type changes the affinity to string.  This is not 
very elegant and I was looking for any other way to make this work 
correctly.  "No Affinity" would probably work, if I understand it 
correctly.

I want to avoid the use of REAL types in this case because they can lead 
to rounding errors, which is the whole purpose of the NUMERIC type to 
begin with, in my understanding.  I also would like to be able to make 
the column type just NUMERIC as that is compilant with the SQL standard.

Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes 
page.  If there are no plans to implement these, please consider 
removing them from the docs.

Thanks!
Scott

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

Re: [sqlite] How to select Strict Affinity or No Affinity modes?

[D. Richard Hipp]

On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote:

> I've looked high and low and can't find a way to invoke the other 2
> affinity modes.  Are they available? I'm on 3.5.4.
>
>

The concept of "strict" affinity mode was briefly discussed years
ago, but we never implemented it, having never seen any benefit
for such a thing.  Can you explain why you think strict affinity mode
might be beneficial to you?  If somebody can provide a good
enough rational to justify strict affinity mode, we might just put it
in.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] how to select first n records

[Scott Baker]

Rael Bauer wrote:

Hi,
   
  Can someone tell me how to select first n records from a query
   
  (for e.g. Interbase has syntax: "rows 1 to n")


SELECT * FROM Table LIMIT 10;

or

SELECT * FROM Table LIMIT 15,10;

Shows 10 records, starting at the 15th.

--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

-
To unsubscribe, send email to [EMAIL PROTECTED]
-