Re: [sqlite] Very Slow delete times on larger databases, please help!

[drh]

"Allan, Mark" <[EMAIL PROTECTED]> wrote:
> Have you been able to investigate this yet? 

I have investigated and I found nothing wrong.  I am unable
to reproduce the problem.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] and , or

[drh]

"Eggert, Henri" <[EMAIL PROTECTED]> wrote:
> Hi sqlite-users
> 
> Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
> ) )
> But NOT: select Id from Data where ( ( Text = '' ) and ( Comming =
> '' ) )
> 

Both work fine when I try them.  Why do you think the second one
is not working?

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

Re: AW: [sqlite] and , or

[Kurt Welgehausen]

> Hi Henri,
>
> On 18.10.2005, at 14:56, Eggert, Henri wrote:
>
> > I have found that the problem is the column name "Text".
> > If I replace the column name "Text" by another all works fine.
> > So I wonder : is "Text" a keyword for sqlite ?
> >
>
> It indeed is [1]. :)
>
>
> -Markus
>
> [1] http://www.sqlite.org/datatype3.html


Actually, 'text' is not a designated keyword; see
. In any
event, being a keyword would not explain why the
query with 'or' works and the one with 'and' does
not, or why both queries work in older versions of
Sqlite3.

This seems to be a recently introduced change in
behavior; whether it's a bug is perhaps a matter
of opinion.

Regards

Re: AW: [sqlite] and , or

[Dennis Cote]

Eggert, Henri wrote:


Hi,

I want to select the records which have both columns Text and Comming empty.

I have found that the problem is the column name "Text".
If I replace the column name "Text" by another all works fine.
So I wonder : is "Text" a keyword for sqlite ?


 


Henri,

The rules regarding the use of keywords as identifiers are discussed at 
http://www.sqlite.org/lang_keywords.html. The word "text" is recognized 
as a data type by sqlite but it is not a keyword. It can safely be used 
as a column identifier without quoting as shown below using the sqlite3 
shell:


sqlite> create table t4(id integer primary key, text text, other text);
sqlite> insert into t4 values(NULL, 'a', 'b');
sqlite> insert into t4 values(NULL, 'c', '');
sqlite> insert into t4 values(NULL, '', 'd');
sqlite> insert into t4 values(NULL, '', '');
sqlite> select * from t4;
id  textother
--  --  --
1   a   b
2   c
3   d
4
sqlite> select * from t4 where text = '' and other = '';
id  textother
--  --  --
4
sqlite>

Are you using a wrapper or some other interface to access sqlite?

HTH
Dennis Cote

Re: [sqlite] sqlite_column_text converting result to a c++ string

[Jay Sprenkle]

> >However I will look at using the reinterpret method and I will look more
> >at Jay's C++ wrapper (any chance of a peek at the src definitions?)
> >
> >
> >
> >
> Terry,
>
> You might want to check out the C++ wrapper classes at
> http://www.codeproject.com/database/CppSQLite.asp. There are also links
> to other wrappers at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
> (scroll down to C++).

Mine is very simple, but sufficient for my project.
These are definitely worth looking at.

Re: [sqlite] sqlite_column_text converting result to a c++ string

[Dennis Cote]

Terence MacDonald wrote:



However I will look at using the reinterpret method and I will look more
at Jay's C++ wrapper (any chance of a peek at the src definitions?)


 


Terry,

You might want to check out the C++ wrapper classes at 
http://www.codeproject.com/database/CppSQLite.asp. There are also links 
to other wrappers at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers 
(scroll down to C++).


Dennis Cote

Re: AW: [sqlite] and , or

[Weissmann Markus]

Hi Henri,

On 18.10.2005, at 14:56, Eggert, Henri wrote:


I have found that the problem is the column name "Text".
If I replace the column name "Text" by another all works fine.
So I wonder : is "Text" a keyword for sqlite ?



It indeed is [1]. :)


-Markus

[1] http://www.sqlite.org/datatype3.html



-Ursprüngliche Nachricht-
Von: Gerald Dachs [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 18. Oktober 2005 14:50
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] and , or




Hi sqlite-users

Works fine : select Id from Data where ( ( Text = '' ) or  
( Comming = ''

) )
But NOT: select Id from Data where ( ( Text = '' ) and  
( Comming '' )

)



What about this: select Id from Data where ( ( Text = '' ) and  
( Comming =

'' ))

Gerald




---
Markus W. Weissmann
http://www.mweissmann.de/
http://www.opendarwin.org/~mww/

Re: AW: [sqlite] and , or

[Gerald Dachs]

> Hi,
>
> I want to select the records which have both columns Text and Comming
> empty.
>
> I have found that the problem is the column name "Text".
> If I replace the column name "Text" by another all works fine.
> So I wonder : is "Text" a keyword for sqlite ?

Why do you wonder? You used it yourself as keyword when you declared "text"
as type text.

Gerald

AW: [sqlite] and , or

[Eggert, Henri]

Hi,

I want to select the records which have both columns Text and Comming empty.

I have found that the problem is the column name "Text".
If I replace the column name "Text" by another all works fine.
So I wonder : is "Text" a keyword for sqlite ?

Thank's

Henri 

-Ursprüngliche Nachricht-
Von: Gerald Dachs [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 18. Oktober 2005 14:50
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] and , or

>
> Hi sqlite-users
>
> Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
> ) )
> But NOT: select Id from Data where ( ( Text = '' ) and ( Comming '' )
> )

What about this: select Id from Data where ( ( Text = '' ) and ( Comming =
'' ))

Gerald

Re: [sqlite] and , or

[Gerald Dachs]

>
> Hi sqlite-users
>
> Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
> ) )
> But NOT: select Id from Data where ( ( Text = '' ) and ( Comming '' )
> )

What about this: select Id from Data where ( ( Text = '' ) and ( Comming =
'' ))

Gerald

[sqlite] and , or

[Eggert, Henri]

Hi sqlite-users

Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
) )
But NOT: select Id from Data where ( ( Text = '' ) and ( Comming =
'' ) )

Any help would be appreciated.

Thanks's

Henri


PS : The table has been created with :

CREATE TABLE Data 
( 
  Id integer primary key 
, Date Date 
, Text Text 
, Comming Text  
, Going Text   
, .   
, constraint c1 unique ( Date )
 )

Re: [sqlite] Quoted identifiers

[Bert Verhees]

> delimited identifiers.  Alternate output
> behaviour can be accomplished by a connection or
> statement specific pragma, or a wrapper.

IMHO a very good Delphi-wrapper
http://www.aducom.com/sqlite/

>
> -- Darren Duncan

-- 
Met vriendelijke groet
Bert Verhees
ROSA Software

Re: [sqlite] Quoted identifiers

[Darren Duncan]

At 9:50 PM -0300 10/17/05, [EMAIL PROTECTED] wrote:
I need to use a quoted identifiers and sqlite 
support it well but when i obtain
the data from the database the cursos give to me 
the fields with quotes. When i

do this directly there is no problem because i can remove the quotes, but i
access to sqlite through delphi using zeosdbo and i can´t touch the resultset
directly.
I don´t think this a useful thing, there isn´t any other database which
behavior like that, maybe the next versions will avoid that.


It sounds like Delphi needs to be fixed, then; 
having quotes *is* standard, and many databases 
support it, including both MySQL and Oracle.


In the SQL:2003 standard, delimited/quoted 
identifiers are a different beast than bareword 
identifiers.  Delimited identifiers can contain 
any characters at all, just as a string literal 
can, including whitespace and punctuation, and 
they are case-sensitive; they are always defined 
and invoked using the delimiters, which are (") 
usually, but MySQL uses (`) instead, and both are 
different than the string literal delimiter of 
(').  Non-delimited identifiers can not contain 
whitespace or most punctuation and can not be 
plain numbers, and they are case-insensitive; 
likewise, usually defined and invoked without 
delimiters.  As far as I know, SQL:2003 allows 
you to invoke non-delimited identifiers using the 
delimited format, in which case any non-delimited 
identifiers match in their folded-to-uppercase 
form; in this respect, non-delimited is a full 
and clearly defined subset of delimited, so if 
they are internally stored like character 
strings, everything would just work.


And before anyone says that identifiers 
containing whitespace is a stupid idea, I counter 
that thought.  From a non-programmer user 
perspective, people who create databases using 
GUI tools and that type names of tables and 
fields into individual GUI form boxes, it is just 
as natural to use spaces and punctuation as it is 
to put those in file system file names, usually 
done in a GUI.  So it is all well and proper to 
support this internally and expose it where 
possible.  Database design is not programming and 
should not be subjected to the same limitations; 
those elements are not variable or function 
names.  And even when we are programming to a 
database, we are often writing programs that use 
a data dictionary and/or are data driven, and 
used by non-programmers.  When we are composing 
SQL directly, adding those delimiters is very 
easy.


SQLite needs to default to the most compatible 
and representitive format there is, which is the 
delimited identifiers.  Alternate output 
behaviour can be accomplished by a connection or 
statement specific pragma, or a wrapper.


-- Darren Duncan

Re: [sqlite] To 'sqlite3_step' or not to 'sqlite3_step' as is the case...

[Terry MacDonald]

Dan Kennedy wrote:


My hunch is that you need to change the while() line to:

while ( (rc = sqlite3_step(pStmt)) != SQLITE_DONE )

But I could be wrong, and I don't have a computer with a C compiler to test 
this right now. 




Terence MacDonald <[EMAIL PROTECTED]> wrote:
The following code is part of a class member function that loads data
from an existing database. The database has been opened and there IS
data in the target table. I have had it working with callbacks, but
using the prepare/step/finalise approach below I consistently get the
log message:

Step : ERROR - not an error

i.e. the 'sqlite3_step' loop returns only once indicating an error but
the error message says 'not an error'. Can anyone spot my silly error?


string sql = "SELECT name,value FROM setting;" ;
sqlite3_stmt *pStmt ;

LOGLN("SQL: "+sql) ;
if( sqlite3_prepare( db, sql.c_str(), -1, &pStmt, 0 ) != SQLITE_OK )
{
LOGLN(string("Prepare: ERROR -")+sqlite3_errmsg(db)) ;
}
else
{
int rc ;
while( rc=sqlite3_step(pStmt) != SQLITE_DONE )
{
if( rc == SQLITE_ROW )
{
string name = (const char*)(sqlite3_column_text(pStmt,0)) ;
string value = (const char*)(sqlite3_column_text(pStmt,1)) ;
settings[name] = value.c_str() ? value : "" ;
LOGLN(name+"="+value) ;
}
else if( rc == SQLITE_BUSY )
{
LOGLN("Step: BUSY") ;
continue ;
}
else if( rc == SQLITE_ERROR )
{
LOGLN(string("Step : ERROR - ")+sqlite3_errmsg(db)) ;
break ;
}
else if( rc == SQLITE_MISUSE )
{
LOGLN("Step: MISUSE") ;
break ;
}
}
}
sqlite3_finalize(pStmt) ;




-
Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
 


Spot on Dan! You spotted by unintentional stoopid mistake.  Thanks

RE: [sqlite] Very Slow delete times on larger databases, please help!

[Allan, Mark]

Have you been able to investigate this yet? Any ideas or recommendations? I 
sent you the analyzer output to [EMAIL PROTECTED] as it was too large to post 
on here.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 12 October 2005 14:58
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Very Slow delete times on larger databases, please
help!


"Allan, Mark" <[EMAIL PROTECTED]> wrote:
> We are experiencing incredibly slow delete times when deleting a 
> large number of rows:-
> 
> We are using SQLite on an embdedded platform with an ARM7 processor,
> 2Mb RAM and 32Mb NOR Flash memory 25Mb of which is allocated for 
> storage for our database. 
>

What operating system?

Can you send the output of sqlite3_analyzer run against your
database file prior to doing the delete?

Have you tried upgrading to a later version of SQLite?

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




DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.