[sqlite] Retrieving column names?

[Gilles]

Hello

I'd like to display the column names as headers in an HTML table.

1. I need to retrieve the column names, but the following doesn't work:

# cat cols.php
exec($sql);

 $sql = "SELECT * FROM customer";
 $row = $dbh->query($sql)->fetch();
 foreach ($row as $col) {
 print $col . "\n";
 }

 $sql = ".header OFF";
 $dbh->exec($sql);

 $dbh = null;
?>

2. Additionally, if possible, I'd like to display a user-friendly name 
instead of the names I use internally.

Does someone have some code handy?

Thank you.

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

[sqlite] sqlite 3.5.5 and SQLITE_ENABLE_LOCKING_STYLE=1

[Marco Bambini]

It is safe to define SQLITE_ENABLE_LOCKING_STYLE=1 with sqlite 3.5.5  
if I am interested in opening database files on a shared volumes on Mac?
As far as I know it was the only workaround ... or something is  
changed in recent versions?

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



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

Re: [sqlite] Retrieving column names?

[Kees Nuyt]

On Mon, 04 Feb 2008 10:46:53 +0100, Gilles
<[EMAIL PROTECTED]> wrote:

>Hello
>
> I'd like to display the column names as headers in an HTML table.
>
> 1. I need to retrieve the column names, but the following doesn't work:
>
> # cat cols.php
>  $dbh = new 
> PDO("sqlite:db.sqlite");
>
> $sql = ".header ON";
> $dbh->exec($sql);
>
> $sql = "SELECT * FROM customer";
> $row = $dbh->query($sql)->fetch();
> foreach ($row as $col) {
> print $col . "\n";
> }
>
> $sql = ".header OFF";
> $dbh->exec($sql);
>
> $dbh = null;
>?>

You can't use the dot commands with the SQLite
library, they are only valid in the command line tool.
Here is a method in my sqlitedb class, 
query($sql);  
if ($res){
if ($row = $res->fetch()){
$nrofcols = $res->columnCount();
if ($nrofcols > 0){
$ret = sprintf('%s
',$caption);
//  column horizontal alignment according to type
for ($i = 0;$i < $nrofcols;$i++){
$metadata = 
$res->getColumnMeta($i);
reset($metadata);
$class = preg_replace(
 array('/[)(0-9]*/')
,array("")
,strtolower(
 (array_key_exists ( 'sqlite:decl_type', $metadata )
)?$metadata['sqlite:decl_type']:$metadata['native_type']
));
$ret .= sprintf('  %s',$class,"\n");
}
$ret .= '  ';
//  column headings
for ($i = 0;$i < $nrofcols;$i++){
$metadata = 
$res->getColumnMeta($i);
$ret .= sprintf(
'%s',$metadata['name']);
}
$ret .= "\n";
// table rows
while ($row){
$ret .= '  ';
for ($i = 0;$i < 
$nrofcols;$i++){
// column data
$ret .=
sprintf('%s',$row[$i]);
}
$ret .= "\n";
$row = $res->fetch();
}
$ret .= '';
} else {
$ret = 'no columns';
}
} else {
$ret = 'no data';
}
} else {
$ret = 'no table';
}
if ($ReturnString){
return $ret;
} else {
print $ret;
unset($ret);
}
} // HTMLtable()

} // end class sqlitedb

?>
> 2. Additionally, if possible, I'd like to 
> display a user-friendly name 
> instead of the names I use internally.

col1namecol2name'
?>

You could fetch the column names from a 'dictionary'
table in your database:
CREATE TABLE Friendlynames (
tablenmTEXT NOT NULL,
columnnm   TEXT NOT NULL,
friendlynm TEXT NOT NULL,
PRIMARY KEY (tablenm,columnnm)
);

> Does someone have some code handy?
>
>Thank you.

HTH
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[���۟�][sqlite] Pysqlite issue no attribute 'autocommit'

[sqlite-users]

수신자가 sqlite-users@sqlite.org 로부터 오는 메일의 수신을 거부하였습니다.


_
--- Begin Message ---
Hi there,

  Im trying to run a Python based program which uses MySQL with python-sqlite 
and Im recieving this error,

'Connection' object has no attribute 'autocommit'

I´ve had a google for this and its seems like it may be a bug python-sqlite or 
sqlite bug , but also I tried searching 
for it on the python issue traker and didnt find anything. Is anyone else aware 
of this issue and any solution?

thanks for any help! Andy.

PS sorry if I didnt include much info, hoping its a known issue (and also I 
didnt write the code, so not sure what
else to include off the top of my head! :P)___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--- End Message ---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pysqlite issue no attribute 'autocommit'

[sqlite-users]

Hi there,

  Im trying to run a Python based program which uses MySQL with python-sqlite 
and Im recieving this error,

'Connection' object has no attribute 'autocommit'

I´ve had a google for this and its seems like it may be a bug python-sqlite or 
sqlite bug , but also I tried searching 
for it on the python issue traker and didnt find anything. Is anyone else aware 
of this issue and any solution?

thanks for any help! Andy.

PS sorry if I didnt include much info, hoping its a known issue (and also I 
didnt write the code, so not sure what
else to include off the top of my head! :P)___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Vacuum

[sqlite-users]

Hi all, 

Hopefully you can help, while running sqlite version 3.5.5 

Two seperate threads each attempt to run a vacuum command against the same db. 
Each thread has an independent connnection to the db. 

One thread succeeds and the other gets a return code of 1 (SQLITE_ERROR) from 
sqlite3_step. 

The sqlite3_error msg generated is "SQL logic error or missing database". The 
Vacuum command is executed using the sqlite3_prepare_v2 interface. 

This only seems to occur when the database is actually vacuumed by the first 
thread. The second thread then gets this error.

Thanks for any help.
Ken



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

Re: [sqlite] Vacuum

[Ken]

DRH,

   I guess, the question being asked is twofold. 
   
   Should  there be an error returned at all ? Or if there is an error, 
wouldn't  SQLITE_BUSY be more appropriate...  So the users app could attempt a 
retry?

   Since there is an error, it seems that the error message is not the same as 
the return code from the Step command. 

Thanks,
Ken



sqlite-users@sqlite.org wrote: sqlite-users@sqlite.org wrote:
> Hi all, 
> 
> Hopefully you can help, while running sqlite version 3.5.5 
> 
> Two seperate threads each attempt to run a vacuum command against the same 
> db. Each thread has an independent connnection to the db. 
> 
> One thread succeeds and the other gets a return code of 1 (SQLITE_ERROR) from 
> sqlite3_step. 
> 
> The sqlite3_error msg generated is "SQL logic error or missing database". The 
> Vacuum command is executed using the sqlite3_prepare_v2 interface. 
> 
> This only seems to occur when the database is actually vacuumed by the first 
> thread. The second thread then gets this error.
> 
> Thanks for any help.

Are you concerned that you are unable to do two vacuums
simulataneously, or are you asking that the error message
returned be adjusted to be more lucid?

--
D. Richard Hipp 

___
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] Update value from either database table?

[Gussimulator]

I have a field I need to update given a condition, but I don't know wether the 
condition occurs in table A or table B, how can I perform this query?

I have 2 identical tables in design, but one contains system data and the other 
one contains user data... On my update routine (in C) I have to increase an 
INTEGER field from either table given a condition... The thing is, the 
condition could be present on both tables... what should I do??

Thanks!

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

Re: [sqlite] open statements upon close

[drh]

Ken <[EMAIL PROTECTED]> wrote:
> Hi all, 
> 
> I'm getting a sqlite error when running sqlite3_close.  The error indicates 
> that there are open statements. 
> 
> To the best of my knowledge all statements are closed. I loooked at the 
> sqlite3_close function and call to close the virtual tables appears to be 
> causing this issue. 
> 
> My question: Are there any means in sqlite to get a listing of what it thinks 
> are open statements?
> 

There is no published way to do this.  If you run in a debugger,
you can look at the linked list of "struct Vdbe" objects that
sqlite3.pVdbe points to.  This is the list of open statements
in the current implementation (and subject to change without 
notice).

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

[sqlite] open statements upon close

[Ken]

Hi all, 

I'm getting a sqlite error when running sqlite3_close.  The error indicates 
that there are open statements. 

To the best of my knowledge all statements are closed. I loooked at the 
sqlite3_close function and call to close the virtual tables appears to be 
causing this issue. 

My question: Are there any means in sqlite to get a listing of what it thinks 
are open statements?

Since this is in a multi threaded app I'm not entirely certain if there is some 
form of corruption or if its a logic error in my code somewhere. But I do go 
through a loop prior to calling sqlite3_close and close each statement that has 
been opened. 

Thanks  For any help.
Ken



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

Re: [sqlite] Update value from either database table?

[Gussimulator]

table a: dict_base
table b: dict_user
condition: wether 'sz_word' = 'X' increase the value of 'dw_uses' by 1

sz_word could be in either table, dw_uses must be updated on the pertinent 
table/s though.

Sorry for not being too clear :)

Thanks again


- Original Message - 
From: "Fowler, Jeff" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Monday, February 04, 2008 4:59 PM
Subject: Re: [sqlite] Update value from either database table?


> Without more info, here's the general approach:
>
> UPDATE c
> SET int_field = int_field+1
> WHERE (table a condition) OR (table b condition)
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Gussimulator
> Sent: Monday, February 04, 2008 1:57 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Update value from either database table?
>
> I have a field I need to update given a condition, but I don't know
> wether the condition occurs in table A or table B, how can I perform
> this query?
>
> I have 2 identical tables in design, but one contains system data and
> the other one contains user data... On my update routine (in C) I have
> to increase an INTEGER field from either table given a condition... The
> thing is, the condition could be present on both tables... what should I
> do??
>
> Thanks!
>
> ___
> 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] 3.5.5 Compilation failure / Bug

[drh]

Ken <[EMAIL PROTECTED]> wrote:
> Amalgamation fails to compile when -DSQLITE_OMIT_VIEW is defined.
> 

Neither the nor the preprocessed sources work with -DSQLITE_OMIT macros. 
If you need to use -DSQLITE_OMIT, then you will need to compile on
Unix using the original source code.

Both the amalgamation and the preprocessed sources contain
generated C code and the code generators have to know about
the -DSQLITE_OMIT macros.

--
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] Update value from either database table?

[Igor Tandetnik]

Gussimulator <[EMAIL PROTECTED]>
wrote:
> I have a field I need to update given a condition, but I don't know
> wether the condition occurs in table A or table B, how can I perform
> this query?
>
> I have 2 identical tables in design, but one contains system data and
> the other one contains user data... On my update routine (in C) I
> have to increase an INTEGER field from either table given a
> condition... The thing is, the condition could be present on both
> tables... what should I do??

When the condition holds in both tables, which one are you supposed to 
update? Do you need to update both? You could do the latter simply by 
running two almost-identical UPDATE statements, one against each table.

Igor Tandetnik 



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

Re: [sqlite] Update value from either database table?

[Fowler, Jeff]

Without more info, here's the general approach:

UPDATE c
SET int_field = int_field+1
WHERE (table a condition) OR (table b condition)

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gussimulator
Sent: Monday, February 04, 2008 1:57 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Update value from either database table?

I have a field I need to update given a condition, but I don't know
wether the condition occurs in table A or table B, how can I perform
this query?

I have 2 identical tables in design, but one contains system data and
the other one contains user data... On my update routine (in C) I have
to increase an INTEGER field from either table given a condition... The
thing is, the condition could be present on both tables... what should I
do??

Thanks!

___
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] 3.5.5 Compilation failure / Bug

[Ken]

Amalgamation fails to compile when -DSQLITE_OMIT_VIEW is defined.

./sqliteSrc/sqlite-3.5.5/sqlite3.c: In function 'sqlite3Insert':
../sqliteSrc/sqlite-3.5.5/sqlite3.c:55512: error: syntax error before numeric 
constant
../sqliteSrc/sqlite-3.5.5/sqlite3.c:55543: error: invalid lvalue in assignment
../sqliteSrc/sqlite-3.5.5/sqlite3.c: In function 
'sqlite3GenerateConstraintChecks':
../sqliteSrc/sqlite-3.5.5/sqlite3.c:56323: warning: cast to pointer from 
integer of different size
../sqliteSrc/sqlite-3.5.5/sqlite3.c: In function 'sqlite3_get_table':
../sqliteSrc/sqlite-3.5.5/sqlite3.c:63649: warning: cast to pointer from 
integer of different size
../sqliteSrc/sqlite-3.5.5/sqlite3.c: In function 'sqlite3_free_table':
../sqliteSrc/sqlite-3.5.5/sqlite3.c:63694: warning: cast from pointer to 
integer of different size
../sqliteSrc/sqlite-3.5.5/sqlite3.c: In function 'sqlite3Update':
../sqliteSrc/sqlite-3.5.5/sqlite3.c:64663: error: syntax error before numeric 
constant
../sqliteSrc/sqlite-3.5.5/sqlite3.c:64700: error: invalid lvalue in assignment
make: *** [sqlite3.lo] Error 1


int isView ;   delcaration is wrapped in SQLITE_OMIT_TRIGGER instead of 
SQLITE_OMIT_VIEW.


Thanks,
Ken



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

Re: [sqlite] [3.5.4] Fails importing CSV file

[Gilles]

At 22:47 04/02/2008 +1100, John Machin wrote:
>Well, obviously(?)  you are closer to success with "\t" than with '\t'. 
>You probably have an extra unseen TAB. It won't like that NULL.

Thanks, but no matter what I try, it doesn't work:
- just two columns, assuming it will just increment the primary key since 
it didn't find anything for this column
- three columns, with col#1 set to "NULL"
- three columns, with col#1 set to just ASCII 09, ie. TAB

How is your "barzot.tsv" formatted? How do you handle the first column that 
works as an auto-incremented primary key?

Thanks anyway.

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

[���۟�]Re: [sqlite] Vacuum

[sqlite-users]

수신자가 sqlite-users@sqlite.org 로부터 오는 메일의 수신을 거부하였습니다.


_
--- Begin Message ---
sqlite-users@sqlite.org wrote:
> Hi all, 
> 
> Hopefully you can help, while running sqlite version 3.5.5 
> 
> Two seperate threads each attempt to run a vacuum command against the same 
> db. Each thread has an independent connnection to the db. 
> 
> One thread succeeds and the other gets a return code of 1 (SQLITE_ERROR) from 
> sqlite3_step. 
> 
> The sqlite3_error msg generated is "SQL logic error or missing database". The 
> Vacuum command is executed using the sqlite3_prepare_v2 interface. 
> 
> This only seems to occur when the database is actually vacuumed by the first 
> thread. The second thread then gets this error.
> 
> Thanks for any help.

Are you concerned that you are unable to do two vacuums
simulataneously, or are you asking that the error message
returned be adjusted to be more lucid?

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] [3.5.4] Fails importing CSV file

[John Machin]

Gilles wrote:
> At 22:47 04/02/2008 +1100, John Machin wrote:
>   
>> Well, obviously(?)  you are closer to success with "\t" than with '\t'. 
>> You probably have an extra unseen TAB. It won't like that NULL.
>> 
>
> Thanks, but no matter what I try, it doesn't work:
> - just two columns, assuming it will just increment the primary key since 
> it didn't find anything for this column
> - three columns, with col#1 set to "NULL"
> - three columns, with col#1 set to just ASCII 09, ie. TAB
>   
If you are using TAB as the separator, col#1 can't be set to TAB -- I 
presume that you mean that col#1 is empty i.e. a zero-length string.

> How is your "barzot.tsv" formatted? How do you handle the first column that 
> works as an auto-incremented primary key?
>   
Like I said in my message:
"""

Here's what works for me:
C:\junk>type barzot.tsv
1   bar zot
42  plugh   xyzzy
666 far narkle
= Yes, there are 2 TABs per line.
C:\junk>sqlite3

"""
It seems that .import doesn't do auto increment. You will have to use 
Dennis's two-pass technique, or write a small one-pass script in e.g. 
Python to read your file and insert the rows into the table.

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

Re: [sqlite] [3.5.4] Fails importing CSV file

[Dennis Cote]

Gilles wrote:
> At 22:47 04/02/2008 +1100, John Machin wrote:
>> Well, obviously(?)  you are closer to success with "\t" than with '\t'. 
>> You probably have an extra unseen TAB. It won't like that NULL.
> 
> Thanks, but no matter what I try, it doesn't work:
> - just two columns, assuming it will just increment the primary key since 
> it didn't find anything for this column
> - three columns, with col#1 set to "NULL"
> - three columns, with col#1 set to just ASCII 09, ie. TAB
> 
> How is your "barzot.tsv" formatted? How do you handle the first column that 
> works as an auto-incremented primary key?
> 

Gilles,

You can't import into an auto-incremented field.

The import command always inserts a value into each column of the table 
that is being imported into. There is no way to import a null value.

Your datatype mismatch error is caused by your attempt to insert the 
string 'NULL' into the id column. Because this column is declared as 
integer primary key it can only store integer row id values (unlike all 
other columns in SQLite).

You will need to do your import in two steps. First import into a temp 
table without the integer primary key column.

CREATE TEMP TABLE temp_customer ( tel VARCHAR(32), name VARCHAR(255));

.sepatator \t
.import test.csv temp_customer

Then copy the data from the temp table into the final table leaving the 
id unassigned, and finally drop the temp table

insert into customer select null, tel, name from temp_customer;
drop table temp_customer;

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

Re: [sqlite] [3.5.4] Fails importing CSV file

[John Machin]

Gilles wrote:
> At 02:27 04/02/2008 +0100, Gilles wrote:
>   
>> Thanks for the tip, but I tried that too, with no success:
>> 
>
> I tried both:
>
> sqlite> .separator "\t"
> sqlite> .import test.csv customer
> test.csv line 1: expected 3 columns of data but found 4
>
> sqlite> .separator '\t'
> sqlite> .import test.csv customer
> test.csv line 1: expected 3 columns of data but found 1
>
>
>   

Well, obviously(?)  you are closer to success with "\t" than with '\t'. 
You probably have an extra unseen TAB.
It won't like that NULL.

Here's what works for me:
C:\junk>type barzot.tsv
1   bar zot
42  plugh   xyzzy
666 far narkle
= Yes, there are 2 TABs per line.
C:\junk>sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> CREATE TABLE customer (id INTEGER PRIMARY KEY AUTOINCREMENT, tel 
VARCHAR
(32), name VARCHAR(255));
sqlite> .separator "\t"
sqlite> .import barzot.tsv customer
sqlite> select * from customer;
1   bar zot
42  plugh   xyzzy
666 far narkle
sqlite> .separator ,
sqlite> select * from customer;
1,bar,zot
42,plugh,xyzzy
666,far,narkle
sqlite> .quit

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

Re: [sqlite] Vacuum

[sqlite-users]

sqlite-users@sqlite.org wrote:
> Hi all, 
> 
> Hopefully you can help, while running sqlite version 3.5.5 
> 
> Two seperate threads each attempt to run a vacuum command against the same 
> db. Each thread has an independent connnection to the db. 
> 
> One thread succeeds and the other gets a return code of 1 (SQLITE_ERROR) from 
> sqlite3_step. 
> 
> The sqlite3_error msg generated is "SQL logic error or missing database". The 
> Vacuum command is executed using the sqlite3_prepare_v2 interface. 
> 
> This only seems to occur when the database is actually vacuumed by the first 
> thread. The second thread then gets this error.
> 
> Thanks for any help.

Are you concerned that you are unable to do two vacuums
simulataneously, or are you asking that the error message
returned be adjusted to be more lucid?

--
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] Pysqlite issue no attribute 'autocommit' RESOLVED

[Andy Smith]

Ok, simple fix... Updated to MySQL_python-1.2.2 and all ok now! :D

  - Original Message - 
  From: Andy Smith 
  To: sqlite-users@sqlite.org 
  Sent: Monday, February 04, 2008 3:52 PM
  Subject: Pysqlite issue no attribute 'autocommit'


  Hi there,

Im trying to run a Python based program which uses MySQL with python-sqlite 
and Im recieving this error,

  'Connection' object has no attribute 'autocommit'

  I´ve had a google for this and its seems like it may be a bug python-sqlite 
or sqlite bug , but also I tried searching 
  for it on the python issue traker and didnt find anything. Is anyone else 
aware of this issue and any solution?

  thanks for any help! Andy.

  PS sorry if I didnt include much info, hoping its a known issue (and also I 
didnt write the code, so not sure what
  else to include off the top of my head! :P)___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Selecting ID for MAX() + GROUP BY

[Piotr Budny]

Hello,
I've got strange behaviour, I want to select row ID of MAX(something) row for 
group:

CREATE TABLE "test" ( "id" INTEGER , "name" CHAR(50)  , "weight" INTEGER  );
INSERT INTO "test" VALUES(1,'A',1);
INSERT INTO "test" VALUES(2,'A',50);
INSERT INTO "test" VALUES(3,'B',1);
INSERT INTO "test" VALUES(4,'C',35);
INSERT INTO "test" VALUES(10,'C',2);
INSERT INTO "test" VALUES(15,'C',123);

select name,max(weight) from test group by name;

The result is fine:
A|50
B|1
C|123

Now, I want to get the ID for such max rows:
select id,name,max(weight) from test group by name;

2|A|50
3|B|1
15|C|123

It is OK. Now:
delete from test;
INSERT INTO "test" VALUES(15,'C',123);
INSERT INTO "test" VALUES(1,'A',1);
INSERT INTO "test" VALUES(2,'A',50);
INSERT INTO "test" VALUES(3,'B',1);
INSERT INTO "test" VALUES(4,'C',35);
INSERT INTO "test" VALUES(10,'C',2);

(the id=15 is now on the top)

sqlite> select id,name,max(weight) from test group by name;
2|A|50
3|B|1
10|C|123

No way, the id=10 is C, but not "123".
What's wrong? Isn't it a bug?
Tried this on MySQL with same data. MySQL works fine.

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

Re: [sqlite] Selecting ID for MAX() + GROUP BY

[P Kishor]

On 2/4/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Piotr Budny <[EMAIL PROTECTED]> wrote:
> > select name,max(weight) from test group by name;
> >
> > The result is fine:
> > A|50
> > B|1
> > C|123
> >
> > Now, I want to get the ID for such max rows:
> > select id,name,max(weight) from test group by name;
> >
> > 2|A|50
> > 3|B|1
> > 15|C|123
> >
> > It is OK.
>
> It's only OK by accident. The id comes from some row in each group, but
> there's no guarantee that it comes from the same row that MAX(weight)
> comes from. In general, it is impossible to match up a field not
> mentioned in either GROUP BY or an aggregate with the row on which MIN
> or MAX is achieved. Consider:
>
> select id, name, min(weight), max(weight) from test group by name;
>
> Which id would you expect to see in the result? Also, in the original
> query, what id do you expect to see if there are two rows with the same
> name and the same weight that is highest in their group?
>
> In fact, many SQL engines consider such a query to be invalid: they
> insist that any field should either be inside an aggregate, or mentioned
> in GROUP BY clause.
>
> > Tried this on MySQL with same data. MySQL works fine.
>
> You mean, happens to work fine for a particular set of data and a
> particular order of insertion.
>
> Igor Tandetnik
>
>


The general rule I follow (a paraphrase of what Igor is saying) --

use only one aggregate function in the SELECT clause.

then take all other columns in the SELECT clause and repeat them in
the GROUP BY clause. If that doesn't give you what you want, then
rework your query.

SELECT col1, col2... AggreateFunction(coln)...
FROM table
GROUP BY col1, col2... (all columns except coln)
WHERE if required
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] open statements upon close

[Ken]

I wrote a function to traverse the linked list and print the zSql to stderr.

There was indeed a sql statement being left open by my code!

Thats a really usefull debugging technique. I might write an extension to 
implement this in a more formally.

Thanks,
Ken

[EMAIL PROTECTED] wrote: Ken  wrote:
> Hi all, 
> 
> I'm getting a sqlite error when running sqlite3_close.  The error indicates 
> that there are open statements. 
> 
> To the best of my knowledge all statements are closed. I loooked at the 
> sqlite3_close function and call to close the virtual tables appears to be 
> causing this issue. 
> 
> My question: Are there any means in sqlite to get a listing of what it thinks 
> are open statements?
> 

There is no published way to do this.  If you run in a debugger,
you can look at the linked list of "struct Vdbe" objects that
sqlite3.pVdbe points to.  This is the list of open statements
in the current implementation (and subject to change without 
notice).

--
D. Richard Hipp 

___
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] Selecting ID for MAX() + GROUP BY

[P Kishor]

On 2/4/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> P Kishor <[EMAIL PROTECTED]> wrote:
> > On 2/4/08, Piotr Budny
> > <[EMAIL PROTECTED]> wrote:
> >> Now, I want to get the ID for such max rows:
> >>
> >> select id,name,max(weight) from test group by name;
> >
> > sqlite> select t.* from test t join (select name, max(weight) as m
> > from test group by name) w on t.weight = w.m and t.name = w.name;
> > 15|C|123
> > 2|A|50
> > 3|B|1
> > sqlite>
>
> Beware of ties (two or more rows in the same group sharing the largest
> weight).
>


right, in which case Max(weight) will return two values, both being max.


sqlite> select * from test;
15|C|123
1|A|1
2|A|50
3|B|1
4|C|35
10|C|2
sqlite> INSERT INTO "test" VALUES(18,'C',123);
sqlite> select t.* from test t join (select name, max(weight) as m
from test group by name) w on t.weight = w.m and t.name = w.name;
15|C|123
2|A|50
3|B|1
18|C|123
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting ID for MAX() + GROUP BY

[Igor Tandetnik]

Piotr Budny <[EMAIL PROTECTED]> wrote:
> select name,max(weight) from test group by name;
>
> The result is fine:
> A|50
> B|1
> C|123
>
> Now, I want to get the ID for such max rows:
> select id,name,max(weight) from test group by name;
>
> 2|A|50
> 3|B|1
> 15|C|123
>
> It is OK.

It's only OK by accident. The id comes from some row in each group, but 
there's no guarantee that it comes from the same row that MAX(weight) 
comes from. In general, it is impossible to match up a field not 
mentioned in either GROUP BY or an aggregate with the row on which MIN 
or MAX is achieved. Consider:

select id, name, min(weight), max(weight) from test group by name;

Which id would you expect to see in the result? Also, in the original 
query, what id do you expect to see if there are two rows with the same 
name and the same weight that is highest in their group?

In fact, many SQL engines consider such a query to be invalid: they 
insist that any field should either be inside an aggregate, or mentioned 
in GROUP BY clause.

> Tried this on MySQL with same data. MySQL works fine.

You mean, happens to work fine for a particular set of data and a 
particular order of insertion.

Igor Tandetnik 



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

Re: [sqlite] Selecting ID for MAX() + GROUP BY

[Piotr Budny]

Dnia poniedziałek, 4 lutego 2008, Simon Davies napisał:
> Hi Piotr,

Hello,

> Looks fine to me.
> Isn't 123 the max weight for those rows with name 'C'?

Yes, the "C" is the right answer, but that row (row with max weight value) is 
not a row with ID=10. The row with max val is row with ID=15.

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

Re: [sqlite] Selecting ID for MAX() + GROUP BY

[Simon Davies]

Hi Piotr,

Looks fine to me.
Isn't 123 the max weight for those rows with name 'C'?

Rgds,
Simon

On 04/02/2008, Piotr Budny <[EMAIL PROTECTED]> wrote:
> Hello,
> I've got strange behaviour, I want to select row ID of MAX(something) row for
> group:
>
> CREATE TABLE "test" ( "id" INTEGER , "name" CHAR(50)  , "weight" INTEGER  );
> INSERT INTO "test" VALUES(1,'A',1);
> INSERT INTO "test" VALUES(2,'A',50);
> INSERT INTO "test" VALUES(3,'B',1);
> INSERT INTO "test" VALUES(4,'C',35);
> INSERT INTO "test" VALUES(10,'C',2);
> INSERT INTO "test" VALUES(15,'C',123);
>
> select name,max(weight) from test group by name;
>
> The result is fine:
> A|50
> B|1
> C|123
>
> Now, I want to get the ID for such max rows:
> select id,name,max(weight) from test group by name;
>
> 2|A|50
> 3|B|1
> 15|C|123
>
> It is OK. Now:
> delete from test;
> INSERT INTO "test" VALUES(15,'C',123);
> INSERT INTO "test" VALUES(1,'A',1);
> INSERT INTO "test" VALUES(2,'A',50);
> INSERT INTO "test" VALUES(3,'B',1);
> INSERT INTO "test" VALUES(4,'C',35);
> INSERT INTO "test" VALUES(10,'C',2);
>
> (the id=15 is now on the top)
>
> sqlite> select id,name,max(weight) from test group by name;
> 2|A|50
> 3|B|1
> 10|C|123
>
> No way, the id=10 is C, but not "123".
> What's wrong? Isn't it a bug?
> Tried this on MySQL with same data. MySQL works fine.
>
> Regards,
> vip
> ___
> 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] Selecting ID for MAX() + GROUP BY

[P Kishor]

On 2/4/08, Piotr Budny <[EMAIL PROTECTED]> wrote:
> Hello,
> I've got strange behaviour, I want to select row ID of MAX(something) row for
> group:
>
> CREATE TABLE "test" ( "id" INTEGER , "name" CHAR(50)  , "weight" INTEGER  );
> INSERT INTO "test" VALUES(1,'A',1);
> INSERT INTO "test" VALUES(2,'A',50);
> INSERT INTO "test" VALUES(3,'B',1);
> INSERT INTO "test" VALUES(4,'C',35);
> INSERT INTO "test" VALUES(10,'C',2);
> INSERT INTO "test" VALUES(15,'C',123);
>
> select name,max(weight) from test group by name;
>
> The result is fine:
> A|50
> B|1
> C|123
>
> Now, I want to get the ID for such max rows:
> select id,name,max(weight) from test group by name;
>
> 2|A|50
> 3|B|1
> 15|C|123
>
> It is OK. Now:
> delete from test;
> INSERT INTO "test" VALUES(15,'C',123);
> INSERT INTO "test" VALUES(1,'A',1);
> INSERT INTO "test" VALUES(2,'A',50);
> INSERT INTO "test" VALUES(3,'B',1);
> INSERT INTO "test" VALUES(4,'C',35);
> INSERT INTO "test" VALUES(10,'C',2);
>
> (the id=15 is now on the top)
>
> sqlite> select id,name,max(weight) from test group by name;
> 2|A|50
> 3|B|1
> 10|C|123
>
> No way, the id=10 is C, but not "123".
> What's wrong? Isn't it a bug?
> Tried this on MySQL with same data. MySQL works fine.
>



sqlite> select t.* from test t join (select name, max(weight) as m
from test group by name) w on t.weight = w.m and t.name = w.name;
15|C|123
2|A|50
3|B|1
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting ID for MAX() + GROUP BY

[Gerry Snyder]

P Kishor wrote:
>
>
> sqlite> select t.* from test t join (select name, max(weight) as m
> from test group by name) w on t.weight = w.m and t.name = w.name;
>   
To me the following almost identical query is more readable, but I guess 
it is objectively better only if you are having trouble with the Period 
key of your keyboard:

select * from test join (select name as n, max(weight) as m from test
group by name) where name = n and weight = m;

Gerry

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

Re: [sqlite] Selecting ID for MAX() + GROUP BY

[Igor Tandetnik]

P Kishor <[EMAIL PROTECTED]> wrote:
> On 2/4/08, Piotr Budny
> <[EMAIL PROTECTED]> wrote:
>> Now, I want to get the ID for such max rows:
>>
>> select id,name,max(weight) from test group by name;
>
> sqlite> select t.* from test t join (select name, max(weight) as m
> from test group by name) w on t.weight = w.m and t.name = w.name;
> 15|C|123
> 2|A|50
> 3|B|1
> sqlite>

Beware of ties (two or more rows in the same group sharing the largest 
weight).

Igor Tandetnik 



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

Re: [sqlite] Is it possible to do this using only SQL?

[Dennis Volodomanov]

Hmm, strange - my testing produces very slow results (it took over a
minute to update 120K rows).

Maybe it's because I'm also creating an index on the new row and doing
an ANALYZE - do these 2 operations take considerable amount of time?

There's also a VACUUM later on, which might be slow? (The database is
around 900 MBs)

   Dennis


-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 05, 2008 11:04 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is it possible to do this using only SQL?

On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> Oh, that looks simpler than I thought. Thank you for the reply!

not only is it simple...


>
> On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote:
>
> > Is that possible? If not, I'll have to do it in the code, but that
> > will
> > probably be slower and I'm expecting to have tens of thousands of
> > rows.


it is also very fast. On my laptop it takes 7 secs to add a million
upcased strings, and 8 secs to lowercase them.


>
> Sure:
>
> sqlite> create table x(a);
> sqlite> insert into x(a) values('ABC');
> sqlite> insert into x(a) values('DEF');
> sqlite> alter table x add column b;
> sqlite> update x set b=lower(a);
> sqlite> select * from x;
> ABC|abc
> DEF|def
> 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
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
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] Is it possible to do this using only SQL?

[P Kishor]

On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> Oh, that looks simpler than I thought. Thank you for the reply!

not only is it simple...

...
>
> On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote:
>
> > Is that possible? If not, I'll have to do it in the code, but that
> > will
> > probably be slower and I'm expecting to have tens of thousands of
> > rows.


it is also very fast. On my laptop it takes 7 secs to add a million
upcased strings, and 8 secs to lowercase them.


>
> Sure:
>
> sqlite> create table x(a);
> sqlite> insert into x(a) values('ABC');
> sqlite> insert into x(a) values('DEF');
> sqlite> alter table x add column b;
> sqlite> update x set b=lower(a);
> sqlite> select * from x;
> ABC|abc
> DEF|def
> 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
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to do this using only SQL?

[Dennis Volodomanov]

Oh, that looks simpler than I thought. Thank you for the reply!

   Dennis


-Original Message-
From: Steven Fisher [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 05, 2008 10:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is it possible to do this using only SQL?

On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote:

> Is that possible? If not, I'll have to do it in the code, but that  
> will
> probably be slower and I'm expecting to have tens of thousands of  
> rows.

Sure:

sqlite> create table x(a);
sqlite> insert into x(a) values('ABC');
sqlite> insert into x(a) values('DEF');
sqlite> alter table x add column b;
sqlite> update x set b=lower(a);
sqlite> select * from x;
ABC|abc
DEF|def
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

[sqlite] Is it possible to do this using only SQL?

[Dennis Volodomanov]

Hello all,

 

I'm not sure if it's possible to do this using only SQL, so I'd like to
ask:

 

I need to add a column to a table which will be populated with data from
another column in that table, but converted to lower-case.

 

So, in pseudo-code I need to do this:

 

ALTER TABLE MyTable ADD COLUMN LowerCase

 

Let's say the schema becomes:

 

MyTable(OriginalCase, LowerCase)

 

So - something like the following:

 

FOR EACH ROW IN MyTable 

UPDATE MyTable SET LowerCase = lower(OriginalCase);

 

Is that possible? If not, I'll have to do it in the code, but that will
probably be slower and I'm expecting to have tens of thousands of rows.

 

Thanks in advance for any ideas and suggestions.

 

   Dennis

 

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

Re: [sqlite] Is it possible to do this using only SQL?

[Steven Fisher]

On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote:

> Is that possible? If not, I'll have to do it in the code, but that  
> will
> probably be slower and I'm expecting to have tens of thousands of  
> rows.

Sure:

sqlite> create table x(a);
sqlite> insert into x(a) values('ABC');
sqlite> insert into x(a) values('DEF');
sqlite> alter table x add column b;
sqlite> update x set b=lower(a);
sqlite> select * from x;
ABC|abc
DEF|def
sqlite>

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

Re: [sqlite] Is it possible to do this using only SQL?

[Dennis Volodomanov]

After removing ANALYZE and VACUUM things are going fast enough. I assume
I don't need to ANALYZE because the index is being created from scratch
anyway and VACUUM I can avoid as well, because I don't expect the
database to opened with any previous versions of SQLite anyway.

Thanks!

   Dennis


-Original Message-
From: Dennis Volodomanov 
Sent: Tuesday, February 05, 2008 11:28 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is it possible to do this using only SQL?

I'll try taking out the ANALYZE and VACUUM and see if it helps. I'm
using transactions and I am creating the index after the update (and
dropping it before). So, the only 2 possible delays are in those two
commands, from what I can see...

The problem is that it's actually inside a Windows service, so it has
limited time in which it has to start, otherwise Windows complains that
it took too long and aborts starting the service altogether. I wouldn't
care about this time otherwise.

   Dennis


-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 05, 2008 11:17 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is it possible to do this using only SQL?

On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> Hmm, strange - my testing produces very slow results (it took over a
> minute to update 120K rows).

well, for one, you are adding different stuff from me.

>
> Maybe it's because I'm also creating an index on the new row and doing
> an ANALYZE - do these 2 operations take considerable amount of time?

yes, you want to suspend indexing while doing all this mucking around.
Indexing is used in searching, so do all your data entry and
lowercasing and then create the index later.

>
> There's also a VACUUM later on, which might be slow? (The database is
> around 900 MBs)

sure, keep adding tasks and the task will take more time. Vacuum is
only useful to reclaim space from deleted records. If you are not
deleting anything, why vacuum?

Finally, the most important factor -- use transactions. You are using
transactions, no?

What! No! Why "no"?

Go use transactions.


>
>Dennis
>
>
> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 05, 2008 11:04 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is it possible to do this using only SQL?
>
> On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> > Oh, that looks simpler than I thought. Thank you for the reply!
>
> not only is it simple...
>
> 
> >
> > On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote:
> >
> > > Is that possible? If not, I'll have to do it in the code, but that
> > > will
> > > probably be slower and I'm expecting to have tens of thousands of
> > > rows.
>
>
> it is also very fast. On my laptop it takes 7 secs to add a million
> upcased strings, and 8 secs to lowercase them.
>
>
> >
> > Sure:
> >
> > sqlite> create table x(a);
> > sqlite> insert into x(a) values('ABC');
> > sqlite> insert into x(a) values('DEF');
> > sqlite> alter table x add column b;
> > sqlite> update x set b=lower(a);
> > sqlite> select * from x;
> > ABC|abc
> > DEF|def
> > 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
> >
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
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] Select Error - SQL error: unrecognized token: ""select"

[Carl Lindgren]

Could someone please give me a clue on why I'm getting this message "SQL 
error: unrecognized token: ""select" "
from the command line using Bash for Dos and a sh script?

Thanks for any help,
Carl

-- 
Carl Lindgren
C. R. Lindgren Consulting / Business on the Desktop


Using Bash for Dos and MS-KSH
-
 

 >>>Script<<<

#!/foobar/bash
desired_data=Test
DB_Path="/foobar/foo_DB"
DB_Statement="\"select Data_Value from Table_Name where Data_Key = 
'$desired_data' ; \""
Sql_return=$(sqlite3.exe "$DB_Path" $DB_Statement)
echo $Sql_return

 >>>End Script<<<

 >>>OUTPUT<<<

sqlite3 /foobar/foo_DB "select Data_Value from Table_Name where Data_Key 
= 'Test' ; "
SQL error: unrecognized token: ""select"

 >>> END OUTPUT<<<

-- 
Carl Lindgren
C. R. Lindgren Consulting / Business on the Desktop

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

Re: [sqlite] Is it possible to do this using only SQL?

[P Kishor]

On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> Hmm, strange - my testing produces very slow results (it took over a
> minute to update 120K rows).

well, for one, you are adding different stuff from me.

>
> Maybe it's because I'm also creating an index on the new row and doing
> an ANALYZE - do these 2 operations take considerable amount of time?

yes, you want to suspend indexing while doing all this mucking around.
Indexing is used in searching, so do all your data entry and
lowercasing and then create the index later.

>
> There's also a VACUUM later on, which might be slow? (The database is
> around 900 MBs)

sure, keep adding tasks and the task will take more time. Vacuum is
only useful to reclaim space from deleted records. If you are not
deleting anything, why vacuum?

Finally, the most important factor -- use transactions. You are using
transactions, no?

What! No! Why "no"?

Go use transactions.


>
>Dennis
>
>
> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 05, 2008 11:04 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is it possible to do this using only SQL?
>
> On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> > Oh, that looks simpler than I thought. Thank you for the reply!
>
> not only is it simple...
>
> 
> >
> > On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote:
> >
> > > Is that possible? If not, I'll have to do it in the code, but that
> > > will
> > > probably be slower and I'm expecting to have tens of thousands of
> > > rows.
>
>
> it is also very fast. On my laptop it takes 7 secs to add a million
> upcased strings, and 8 secs to lowercase them.
>
>
> >
> > Sure:
> >
> > sqlite> create table x(a);
> > sqlite> insert into x(a) values('ABC');
> > sqlite> insert into x(a) values('DEF');
> > sqlite> alter table x add column b;
> > sqlite> update x set b=lower(a);
> > sqlite> select * from x;
> > ABC|abc
> > DEF|def
> > 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
> >
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to do this using only SQL?

[Dennis Volodomanov]

I'll try taking out the ANALYZE and VACUUM and see if it helps. I'm
using transactions and I am creating the index after the update (and
dropping it before). So, the only 2 possible delays are in those two
commands, from what I can see...

The problem is that it's actually inside a Windows service, so it has
limited time in which it has to start, otherwise Windows complains that
it took too long and aborts starting the service altogether. I wouldn't
care about this time otherwise.

   Dennis


-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 05, 2008 11:17 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is it possible to do this using only SQL?

On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> Hmm, strange - my testing produces very slow results (it took over a
> minute to update 120K rows).

well, for one, you are adding different stuff from me.

>
> Maybe it's because I'm also creating an index on the new row and doing
> an ANALYZE - do these 2 operations take considerable amount of time?

yes, you want to suspend indexing while doing all this mucking around.
Indexing is used in searching, so do all your data entry and
lowercasing and then create the index later.

>
> There's also a VACUUM later on, which might be slow? (The database is
> around 900 MBs)

sure, keep adding tasks and the task will take more time. Vacuum is
only useful to reclaim space from deleted records. If you are not
deleting anything, why vacuum?

Finally, the most important factor -- use transactions. You are using
transactions, no?

What! No! Why "no"?

Go use transactions.


>
>Dennis
>
>
> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 05, 2008 11:04 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is it possible to do this using only SQL?
>
> On 2/4/08, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> > Oh, that looks simpler than I thought. Thank you for the reply!
>
> not only is it simple...
>
> 
> >
> > On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote:
> >
> > > Is that possible? If not, I'll have to do it in the code, but that
> > > will
> > > probably be slower and I'm expecting to have tens of thousands of
> > > rows.
>
>
> it is also very fast. On my laptop it takes 7 secs to add a million
> upcased strings, and 8 secs to lowercase them.
>
>
> >
> > Sure:
> >
> > sqlite> create table x(a);
> > sqlite> insert into x(a) values('ABC');
> > sqlite> insert into x(a) values('DEF');
> > sqlite> alter table x add column b;
> > sqlite> update x set b=lower(a);
> > sqlite> select * from x;
> > ABC|abc
> > DEF|def
> > 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
> >
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
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] sqlite3_db_handle

[Ken]

The sqlite3_db_handle returns a different address for my statements than the 
database session address. (using sqlite3_open)

Should it return the .aDb field instead of the pVfs ?

Thanks,
Ken




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

[sqlite] DBD::SQLite 1.14 prepare_cached bug?

[Clark Christensen]

Sorry, this was mis-addressed.  Should have gone to the list...

- Forwarded Message 
From: Clark Christensen <[EMAIL PROTECTED]>
To: Alexander Batyrshin <[EMAIL PROTECTED]>
Sent: Monday, February 4, 2008 9:46:49 AM
Subject: Re: [sqlite] DBD::SQLite 1.14 prepare_cached bug?

bash,

What do you expect to see?  From the code, I'm guessing something like:

$VAR1=[...]

If you're just trying to silence the "closing dbh with active handles..." 
warning, "undef $sth;" usually works for me.  I see you have it commented in 
your code?  DBD-SQLite has spewed this warning for as long as I can remember.  
And $dbh->finish; doesn't squash it.

Also, I see you could save the sprintf and $dbh->quote by changing to:

my $sql = "select a_session from sessions where id = ?";
my $sth = $dbh->prepare_cached($sql);
$sth->execute($sid);

In your example, the value of $sid, after doing the $dbh->quote, is parsed by 
the SQL parser.  Doing that has always been unreliable for me, and it's 
generally open to SQL injection.  In the example above, $sid isn't 
parsed/compiled by SQLite, it's just passed as-is as a bound parameter after 
$sth is prepared.

Are you building a web session manager using SQLite as the data store?  How is 
Storable working for you?  I usually just use Data::Dumper, and eval the stored 
hash.  But doing the eval has always worried me :-))

Thanks!

 -Clark


- Original Message 
From: Alexander Batyrshin <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, February 2, 2008 3:27:35 PM
Subject: [sqlite] DBD::SQLite 1.14 prepare_cached bug?

 Hello All,
I don't know is it right place to discuss this or not. Sorry If I am
doing something wrong.
Installed sqlite-3.5.4 and DBD::SQLite-1.14
I get problems with this code:

%<
#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dumper;
use Storable;
use warnings;

sub get_session {
my ($dbh) = shift;
#$dbh->{TraceLevel} = 2;
my $sid = $ARGV[0];
my $SQL = sprintf("select a_session from sessions where id = %s",
$dbh->quote($sid));
my $sth = $dbh->prepare_cached($SQL, undef, 3);
$sth->execute;
my ($val) = $sth->fetchrow_array;
#my ($val2) = $sth->fetchrow_array;
$sth->finish;
#[3] undef $sth;
my $session = Storable::thaw($val);
}


my $dbh = DBI->connect('dbi:SQLite:dbname=db/sessions.db');
print Dumper(get_session($dbh));
$dbh->disconnect;
%<

If we run program as it looks, result will be:

DBI::db=HASH(0x87a79c)->disconnect invalidates 1 active statement
handle (either destroy statement handles or call finish on them before
disconnecting) at ./decode_sessions.pl line 26.
closing dbh with active statement handles at ./decode_sessions.pl line 26.

Note: that my database does not contain duplicated records.

If I uncomment (1), (2) or (1)+(2) result:

closing dbh with active statement handles at ./decode_sessions.pl line 26.

Inside DBD-SQLite this errors goes from this:
%<
int
sqlite_db_disconnect (SV *dbh, imp_dbh_t *imp_dbh)
{
dTHR;
DBIc_ACTIVE_off(imp_dbh);

if (DBIc_is(imp_dbh, DBIcf_AutoCommit) == FALSE) {
sqlite_db_rollback(dbh, imp_dbh);
}

if (sqlite3_close(imp_dbh->db) == SQLITE_BUSY) {
/* active statements! */
warn("closing dbh with active statement handles");
}
imp_dbh->db = NULL;

av_undef(imp_dbh->functions);
imp_dbh->functions = (AV *)NULL;

av_undef(imp_dbh->aggregates);
imp_dbh->aggregates = (AV *)NULL;

return TRUE;
}
%<


-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
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] Mailing List Changes

[Clark Christensen]

So, I sent a reply this morning to a list message, and it seems to have gone to 
the OP's email address rather than to the list (sorry bash).

I don't remember having that issue with the old software (ezmlm).  To fix, is 
it a client configuration, or is there a reply-to header that should be set in 
Mailman?  I would really like to NOT have to remember to change the recipient 
address when I reply.

Thanks!

 -Clark

- Original Message 
From: Mike Owens <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, February 2, 2008 10:55:04 AM
Subject: [sqlite] Mailing List Changes

The SQLite mailing list has been moved over to Postfix and GNU Mailman. Please
do not use the ezmlm mail accounts to modify your subscription status from this
point on.

You can now configure your list status and options via the Mailman
interface at:

  http://sqlite.org:8080/cgi-bin/mailman/options/sqlite-users

You will need your password to do so. To get it, fill in your email address and
hit the "Remind" button under the the "Password Reminder" section. You will
receive an email with your auto-generated password. You can change this password
after logging into your admin page.

While it shouldn't be a problem for you, we are using the following blacklists:

   list.dsbl.org
   zen.spamhaus.org
   bl.spamcop.net
   dnsbl.njabl.org

If you have any problems with passwords, settings, or sending/receiving mail, or
anything else related to the mailing list, please email [EMAIL PROTECTED] I
apologize in advance for any problems or inconvenience, and will work
to fix them
as quickly as possible.

-- Mike
___
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] Mailing List Changes

[P Kishor]

On 2/4/08, Clark Christensen <[EMAIL PROTECTED]> wrote:
> So, I sent a reply this morning to a list message, and it seems to have gone 
> to the OP's email address rather than to the list (sorry bash).
>
> I don't remember having that issue with the old software (ezmlm).  To fix, is 
> it a client configuration, or is there a reply-to header that should be set 
> in Mailman?  I would really like to NOT have to remember to change the 
> recipient address when I reply.

+1

This mailing-list business is becoming a royal pain in the derriere.
Every other mailing list behaves differently... some default to the
list, others to the OP. Why can't we all get along.

Please set the list so default reply is to the list.




>
> Thanks!
>
>  -Clark
>
> - Original Message 
> From: Mike Owens <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Saturday, February 2, 2008 10:55:04 AM
> Subject: [sqlite] Mailing List Changes
>
> The SQLite mailing list has been moved over to Postfix and GNU Mailman. Please
> do not use the ezmlm mail accounts to modify your subscription status from 
> this
> point on.
>
> You can now configure your list status and options via the Mailman
> interface at:
>
>   http://sqlite.org:8080/cgi-bin/mailman/options/sqlite-users
>
> You will need your password to do so. To get it, fill in your email address 
> and
> hit the "Remind" button under the the "Password Reminder" section. You will
> receive an email with your auto-generated password. You can change this 
> password
> after logging into your admin page.
>
> While it shouldn't be a problem for you, we are using the following 
> blacklists:
>
>list.dsbl.org
>zen.spamhaus.org
>bl.spamcop.net
>dnsbl.njabl.org
>
> If you have any problems with passwords, settings, or sending/receiving mail, 
> or
> anything else related to the mailing list, please email [EMAIL PROTECTED] I
> apologize in advance for any problems or inconvenience, and will work
> to fix them
> as quickly as possible.
>
> -- Mike
> ___
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailing List Changes

[Doug Currie]

On Monday, February 04, 2008 P Kishor wrote: 

> On 2/4/08, Clark Christensen <[EMAIL PROTECTED]> wrote:
>> So, I sent a reply this morning to a list message, and it seems to have gone 
>> to the OP's email address rather than to the list (sorry bash).
>>
>> I don't remember having that issue with the old software (ezmlm).  To fix, 
>> is it a client configuration, or is there a reply-to header that should be 
>> set in Mailman?  I would really like to NOT have to remember to change the 
>> recipient address when I reply.

> +1

> This mailing-list business is becoming a royal pain in the derriere.
> Every other mailing list behaves differently... some default to the
> list, others to the OP. Why can't we all get along.

> Please set the list so default reply is to the list.

http://www.unicom.com/pw/reply-to-harmful.html

e

-- 
Doug Currie
Londonderry, NH, USA

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

Re: [sqlite] Mailing List Changes

[drh]

Doug Currie <[EMAIL PROTECTED]> wrote:
> 
> > Please set the list so default reply is to the list.
> 
> http://www.unicom.com/pw/reply-to-harmful.html
> 

One finds various screeds such as the one Doug references
above.  And on the configuration screen for GNU mailman,
it "strongly recommends" that replys be to the author and
not to the list.

And yet nearly everyone I know loaths that behavior.  The
overwhelming majority of users prefer mailing list replies
to go back to the mailing list *only*.

I think we have things configured now so that replies go
back to the list instead of the to original author.  There
are likely other settings that will need to be adjusted as
we move forward.  Please let me know if you see anything
unusual.

In a semi-related rant: Setting up a new mailing list is
*way* harder than it needs to be.  Way, Way harder.  In order
to go from ezmlm to GNU mailman, we had to prototype the
setup on a separate machine, then spend a day debugging
the setup after transferring it to the production machine.
A day.  For a mailing list.  And this is with mailing list
software that is suppose to be *easy* to configure.  I
tremble to thing what the difficult-to-configure software
must be like.

To tie this back to the original question, when people who
write mail handling and mailing list software get their
programs to the point where I can set up a new mail system
and a new mailing list manager on a system in 15 minutes or
less with reasonable assurance that I have not opened major
security holes in the system, then, perhaps, I will be in a
better mood to listen to their polemics on Reply-To field 
munging.

--
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] Mailing List Changes

[Rich Shepard]

On Tue, 5 Feb 2008, [EMAIL PROTECTED] wrote:

> One finds various screeds such as the one Doug references above. And on
> the configuration screen for GNU mailman, it "strongly recommends" that
> replys be to the author and not to the list.
>
> And yet nearly everyone I know loaths that behavior. The overwhelming
> majority of users prefer mailing list replies to go back to the mailing
> list *only*.

   I'm in that group. If it's a mail list, then threads should stay on the
list. Most of the lists to which I subscribe have both the list and the
individual sender as return addresses; sometimes the list is the To: and the
individual is the Cc:, other times it's the opposite. Regardless, it's a
minor hassle to clean out the individual's address when I want to reply to
the list.

   OTOH, pine is very helpful when I prepare to compose a reply. It asks if I
want to use the From: or Reply-to: address; if the former, it then asks if I
want to respond to everyone. I like this behavior; when I want to send a
private message I can, otherwise the default behavior is to send the reply
to the mail list.

   Thank you Richard and crew for spending the time to successfully migrate
from ezmlm to mailman. I use majordomo, but my lists are few, small, and
very low volume.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DBD::SQLite 1.14 prepare_cached bug?

[Alexander Batyrshin]

 Hello,

> What do you expect to see?  From the code, I'm guessing something like:

This is "test-case" program for testing DBD-SQLite behavior. Dumper is
only for be sure, that data was read correctly from database.

> If you're just trying to silence the "closing dbh with active handles..." 
> warning, "undef $sth;" usually works for me.  I see you have it commented in 
> your code?  DBD-SQLite has spewed this warning for as long as I can remember. 
>  And $dbh->finish; doesn't squash it.

Yes, this is what I want. "undef $sth" doesn't work for statement that
was prepare_cached. Because statement is still allocated inside $dbh
buffers for cached statement.


> Also, I see you could save the sprintf and $dbh->quote by changing to:
>
> my $sql = "select a_session from sessions where id = ?";
> my $sth = $dbh->prepare_cached($sql);
> $sth->execute($sid);

Yes, i know, but this is only "test-case" program without any
optimization and code-beauty refactoring.

> In your example, the value of $sid, after doing the $dbh->quote, is parsed by 
> the SQL parser.  Doing that has always been unreliable for me, and it's 
> generally open to SQL injection.  In the example above, $sid isn't 
> parsed/compiled by SQLite, it's just passed as-is as a bound parameter after 
> $sth is prepared.

What kind of SQL injection is possible here?

> Are you building a web session manager using SQLite as the data store?  How 
> is Storable working for you?  I usually just use Data::Dumper, and eval the 
> stored hash.  But doing the eval has always worried me :-))

It's work without any problems for me handling over 150k hits/day.


-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailing List Changes

[Aristotle Pagaltzis]

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2008-02-05 02:35]:
> The overwhelming majority of users prefer mailing list replies
> to go back to the mailing list *only*.

Reply-To munging is still harmful, because if the original sender
had set this header, that information is lost; if someone really
wants to send to mail to the sender instead of the list, after
going through the contortions necessary, they will end up sending
it to the sender’s From address, ie. the wrong one.

So it goes.

Rather, mailing list software should be setting Mail-Followup-To.
Unfortunately there are a lot of broken clients out there which
don’t have any clue about that one whatsoever. I believe the fine
Microsoft products are among them, though I could be mistaken. So
this Reply-To meddling persists.

So it goes.

Anyway, there are mail clients which largely work sanely despite
adversity – read: mutt. Once told that a particular address is a
mailing list, mutt will plainly ignore a munged Reply-To when
doing a regular reply, offering instead a separate list-reply
function which will send the reply to the list *only*, regardless
of how the mailing list software is configured, and will also set
Mail-Followup-To in the right circumstances.

What mutt can’t do, of course, is recover the original value of
a Reply-To header mangled by officious mailing list software.

So it goes.

(It’s kind of ludicrous, if you think about it, that most mail
clients have not even the most basic dedicated support for
mailing lists, nearly half a century after the birth of SMTP.)

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

Re: [sqlite] Mailing List Changes

[Glenn McAllister]

[EMAIL PROTECTED] wrote:
> In a semi-related rant: Setting up a new mailing list is
> *way* harder than it needs to be.  Way, Way harder.  In order
> to go from ezmlm to GNU mailman, we had to prototype the
> setup on a separate machine, then spend a day debugging
> the setup after transferring it to the production machine.
> A day.  For a mailing list.  And this is with mailing list
> software that is suppose to be *easy* to configure.  I
> tremble to thing what the difficult-to-configure software
> must be like.

Out of curiosity, what prompted the move from ezmlm to mailman?  I've 
used ezmlm for years and haven't had any problems with it.  That being 
said, I've only actually setup an ezmlm list a couple of times, and that 
was several years ago, so I'm probably forgetting the headaches.

-- 
Glenn McAllister <[EMAIL PROTECTED]>  +1 416 348 1594
SOMA Networks, Inc.  http://www.somanetworks.com/  +1 416 977 1414
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can't use cursor keys

[Gilles]

Hello

I can't use the cursor keys ("arrows") when using SQLite on Linux from 
a 
remote Windows host. Both are supposed to be set to "xterm", but when I hit 
eg. the up key in SQLite, it shows "^[[A". Other applications work OK. I've 
tried using vt100 instead of xterm, but it made no difference.

===
# echo $SHELL
/bin/sh

# echo $TERM
xterm

# cd /tmp/
# sqlite3 db.sqlite
SQLite version 3.5.4
sqlite> ^[[A
===

Any idea what I could try?

Thank you. 

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

Re: [sqlite] Mailing List Changes

[Nicolas Williams]

On Tue, Feb 05, 2008 at 01:30:01AM +, [EMAIL PROTECTED] wrote:
> And yet nearly everyone I know loaths that behavior.  The
> overwhelming majority of users prefer mailing list replies
> to go back to the mailing list *only*.

Users need to learn to do "list reply" or "reply all" :)

Getting dups (reply all) is no big deal (and even desirable!).

There's a mailing list I'm on where someone tried to respond privately
to another poster, but didn't realize that reply-to was set, and the
reply went to the list; that reply was a bit embarrassing.  I don't
recommend that.

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

Re: [sqlite] Mailing List Changes

[drh]

Nicolas Williams <[EMAIL PROTECTED]> wrote:
> On Tue, Feb 05, 2008 at 01:30:01AM +, [EMAIL PROTECTED] wrote:
> > And yet nearly everyone I know loaths that behavior.  The
> > overwhelming majority of users prefer mailing list replies
> > to go back to the mailing list *only*.
> 
> Users need to learn to do "list reply" or "reply all" :)
> 
> Getting dups (reply all) is no big deal (and even desirable!).
> 
> There's a mailing list I'm on where someone tried to respond privately
> to another poster, but didn't realize that reply-to was set, and the
> reply went to the list; that reply was a bit embarrassing.  I don't
> recommend that.
> 

The much, much more common mistake is that users press reply
instead of reply-all and they message does not go back to the
list.  This happens with alarming regularity.  And when the
reply does not go back to the list, their contribution to the
conversation is lost from the archive.  This is bad.

Furthermore, reply-all causes the original sender to get two
copies of the message instead of one.  My mailbox is full enough
already without getting two of everything.

In the very rare case where you want to respond to the individual
rather than to the list, it is easy enough to change the To: field
of your email.  But responding to an individual should be the
exception, rather than the rule.  Remember, we want all responses
to be on-list because usually when one person asks a question, there
are a dozen others that have the same question but have not yet
asked it.  If people reply off-list, then the same question gets
asked and answered over, and over again.  But if the answer is
on-list, then multiple people can benefit from the answer.

The common case is responding to the list.  Without Reply-To munging,
if you press reply-all, then you have to go up and manually remove
the original senders name from the To: field.  This is extra work
in the common case.  We perfer to optimize the common case, rather
than the exceptional case.

A better way of avoiding embarrassment from public revelation of
secretive correspondence is to deal openly and honestly with 
everybody in the first place.

--
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] DBD::SQLite 1.14 prepare_cached bug?

[Alexander Batyrshin]

I think i found solution.
The problem is that DBD::SQlite->disconnect() method execute
sqlite3_close() function.
This function return SQLITE_BUSY in case if there are any active statement.
>From API:
"Applications should finalize all prepared statements and close all
BLOBs associated with the sqlite3 object prior to attempting to close
the sqlite3 object."
Currently DBD::SQLite can finalize statements only via DESTROY method.

In simplest case you can always use "undef $sth" or wait untill it
goes out of scope
which will finalize statement.

But if you prepared statement via cache (prepare_cached) it will not
work for you,
because statement is till inside DBI cache. In this case we can call
DESTROY on our cached statement only via DESTROY for database handler.
And we can achieve it by "undef $dbh".
"undef $dbh" - will close all cached statements and close database
without any errors.

Conclusion: avoid using $dbh->disconnect() for DBD::SQLite, instead
use "undef $dbh".

On Feb 5, 2008 3:37 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
>  Hello,
>
> > What do you expect to see?  From the code, I'm guessing something like:
>
> This is "test-case" program for testing DBD-SQLite behavior. Dumper is
> only for be sure, that data was read correctly from database.
>
> > If you're just trying to silence the "closing dbh with active handles..." 
> > warning, "undef $sth;" usually works for me.  I see you have it commented 
> > in your code?  DBD-SQLite has spewed this warning for as long as I can 
> > remember.  And $dbh->finish; doesn't squash it.
>
> Yes, this is what I want. "undef $sth" doesn't work for statement that
> was prepare_cached. Because statement is still allocated inside $dbh
> buffers for cached statement.
>
>
> > Also, I see you could save the sprintf and $dbh->quote by changing to:
> >
> > my $sql = "select a_session from sessions where id = ?";
> > my $sth = $dbh->prepare_cached($sql);
> > $sth->execute($sid);
>
> Yes, i know, but this is only "test-case" program without any
> optimization and code-beauty refactoring.
>
> > In your example, the value of $sid, after doing the $dbh->quote, is parsed 
> > by the SQL parser.  Doing that has always been unreliable for me, and it's 
> > generally open to SQL injection.  In the example above, $sid isn't 
> > parsed/compiled by SQLite, it's just passed as-is as a bound parameter 
> > after $sth is prepared.
>
> What kind of SQL injection is possible here?
>
> > Are you building a web session manager using SQLite as the data store?  How 
> > is Storable working for you?  I usually just use Data::Dumper, and eval the 
> > stored hash.  But doing the eval has always worried me :-))
>
> It's work without any problems for me handling over 150k hits/day.
>
>
>
> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] After sqlite3_create_function, calling function returns no such function

[Joe Cosby]

I can't spot what I'm doing wrong here, maybe somebody else can see it ...

I define a function:

static void sqliteTimeToHms(sqlite3_context *context, int argc, sqlite3_value 
**argv)
{
sqlite3_result_text(context, "0:00:00", 8, NULL);
}

Then call

rc = sqlite3_create_function16(db, L"TimeToHms", 1, SQLITE_ANY, NULL, 
, NULL, NULL);

Which returns SQLITE_OK, then later I have this query

wstring testSql = L" select TimeToHms(time)"
L" from meta_data_master"
L" where meta_data_master.id <= 100";

And try to execute it:

sqlite3_prepare16_v2(db, [0] ,-1, ,);

And get the error message "no such function: TimeToHms"

I'm not sure where to even look ... I think I have tried every possible 
combination of 16-bit chars versus UTF-8 both in the result_text function and 
in the create_function function, plus using lower case both in the function 
creation and in the SQL ... 

But I'm not sure if that's even relevant;  the DB doesn't seem to even be 
finding the function, so it seems like it must be something more basic I am 
doing wrong.  (I thought maybe if I was passing in 16-bit text and had defined 
the function to take something different, SQLITE might not match the call up 
with the function at all.)

Does anybody see anything wrong with how I've done things?  Is it possible that 
a wrong match of text encoding to function would cause "no such function", or 
would it rather just give me garbled results?


Thanks for any help

-- 
Joe Cosby
http://joecosby.com/ 
To see a World in a Grain of Sand
  And Heaven in a Wild Flower
Hold Infinity in the palm of your hand
  And Eternity in an hour
- William Blake

:: Currently listening to O Fortuna, 1937, by Orff, from "Carmina Burana"
_
Shed those extra pounds with MSN and The Biggest Loser!
http://biggestloser.msn.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users