Re: [sqlite] How do I optimize a query in this situation?

[He Shiming]

>
> Consider replacing this query with a programmatic loop using blob API:
>
> http://sqlite.org/c3ref/blob_open.html
>
> or simply running a query like this:
>
> SELECT bin_content FROM FILE_CONTENT WHERE id = ?;
>
> with a new Id on every loop iteration.
>
> Igor Tandetnik
>

Thank you for the pointers. It looks like both options require me to 
implement the loop within C. So is it true that running this loop inside C 
would be faster than SQLite? Or, is

SELECT * FROM TABLE WHERE id in (1,2,3);

slower than three times of:

SELECT * FROM TABLE WHERE id = ?;

?

>From what I understand, the only difference is the 
number-to-string-to-number conversion. Are there any other factors? In 
general, should I always keep the query string short, regardless of the fact 
that it's "an easy query"? I only begin to worry because the query string 
can be up to 5KB, or even 20KB if the user continues to use the program. Is 
that too long for the engine to perform good?

Thanks again!

Best regards,
He Shiming 

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

[sqlite] How do I optimize a query in this situation?

[He Shiming]

Hi,

I'm working on this project. It puts a lot of files into a single sqlite
database (including the actual file content, the program is designed to
store the actual content) for search. When a search command is issued, I run
a SELECT command on the FILE_INFO table to get stuff like file name, size
and date. Then I run a background thread to obtain the actual file content
from FILE_CONTENT table. The syntax of the query to obtain the file content
is quite silly. Since I have all the row IDs in the first query, I ran a
query like this: SELECT bin_content FROM FILE_CONTENT WHERE id in
(1,2,3,4...);

The query is built by sprintf with a for loop, obviously. Sometimes, there
can be several hundreds or thousands of numbers in those brackets.

I couldn't help but thinking there might be a way to improve this query
syntax.

I could mix the two query into one using LEFT JOIN. But that'll slow things
down, and I wanted some quick response before seeing the actual content. It
looks like other options will also slow down the second query.

So I would like to know if it's okay to have queries running this way? Is it
typically done this way or can I improve it?

Many thanks in advance,
He Shiming
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help on sql syntax, left join and group_concat

[He Shiming]

> First, note that left join is a red herring here, since you don't in fact 
> have any records in T1 without a matching record in T2.
>
> Try this:
>
> select NAME, group_concat(COUNT), group_concat(TYPE)
> from (
>  select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE
>  from T1 left join T2 on T1.ID=T2.REFID
>  order by T2.COUNT);
>
> Igor Tandetnik
>
>

Well, in fact, it's possible that a record in T1 doesn't have a matching 
record in T2. So I have to use left join. I'm sorry for not clearing that 
up.

Thanks for the hint. It works, but I noticed the query takes much longer to 
complete (5 times actually in my smaller testing db). My real scenario is 
much more complicated and the performance is even worse. So, any 
alternatives? Can it be done without a sub-select?

About performance, it is important in my scenario. That's why I used 
group_concat. I could use additional queries to fetch the data from T2. But 
I figured as long as it's done in a single query instead of multiple, the 
performance shall be better. Please correct me if I'm wrong here, because 
I'm not so sure if I'm heading in the right direction.

Thanks,
He Shiming 

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

[sqlite] Help on sql syntax, left join and group_concat

[He Shiming]

Hi,

I need some help on a particular sql statement syntax. Consider the following 
tables:

T1:
ID, NAME
1, John

T2:
REFID, COUNT, TYPE
1, 9, B
1, 5, U
1, 8, T

I have the following statement:
select T1.NAME, group_concat(T2.COUNT), group_concat(T2.TYPE) from T1 left join 
T2 on T1.ID=T2.REFID;

And the result is:
John, 9,5,8, B,U,T

What I want is to make the join part sorted by T2.COUNT, so that the result 
goes:
John, 5,8,9, U,T,B

I can't think of a way to make it happen. I tried putting in an additional 
ORDER BY T2.COUNT but it has no effect. Any hints?

Thanks in advance,
He Shiming
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat for binary?

[He Shiming]

Well, I didn't think BLOB stores text more efficiently. It's just that my 
data is actually in binary. To store it in text I'll have to encode it, 
which thus require twice storage as large.

Anyway, I've managed to create my own custom aggregate function to 
accomplish this. The initial results look fine. I'll post again if I ran 
into troubles.

Best regards,
He Shiming

--
From: "John Stanton" <[EMAIL PROTECTED]>
Sent: Monday, March 10, 2008 12:24 PM
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Subject: Re: [sqlite] group_concat for binary?

> Why do you think a BLOB stores text more efficiently?  Do you compress it?
>
> He Shiming wrote:
>> Hi,
>>
>> I would like to know if there is some workaround to use group_concat on
>> binary columns.
>>
>> For performance consideration, I need to query two tables and accomplish
>> data retrieval in a single SELECT. There is this BLOB column, originally
>> designed as BLOB rather than text so that the storage is efficient. So 
>> can I
>> use group_concat to join binary columns? I don't know if the 
>> implementation
>> actually treats the output column as a string, or it will determine the
>> actual length, in which case, it might be possible.
>>
>> What choices do I have? Is it a good idea to add another column that
>> contains the encoded text representation of the BLOB?
>>
>> Thanks in advance,
>> He Shiming
>>
>> ___
>> 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] group_concat for binary?

[He Shiming]

Hi,

I would like to know if there is some workaround to use group_concat on 
binary columns.

For performance consideration, I need to query two tables and accomplish 
data retrieval in a single SELECT. There is this BLOB column, originally 
designed as BLOB rather than text so that the storage is efficient. So can I 
use group_concat to join binary columns? I don't know if the implementation 
actually treats the output column as a string, or it will determine the 
actual length, in which case, it might be possible.

What choices do I have? Is it a good idea to add another column that 
contains the encoded text representation of the BLOB?

Thanks in advance,
He Shiming 

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

Re: [sqlite] Question - Multiple Users, Multiple files

[He Shiming]

Sorry about the previous mail, I hit tab in a gmail and it automatically
sent it.

Anyway, back to my question
I'm planning out a program right now - web based - that would require each
user of the application to be able to create their own independent data
store with its own schema and modifications etc.  In a perfect world, 
sqlite
would be great for this as I could just give each individual user their 
own

sqlite database and let them manipulate tables and schema as they see fit
before they start to store information into the db.

My concern is scalability and the performance hit of having let's say 5000
users potentially, accessing 5000 databases at the same time on the 
server.

Based on my readings around the web there are ways to optimize sql queries
(transactions, in memory dbs and all that) but even with that, would it be
feasible or better yet advisable?

The main issue here is the ability of the users to create and modify their
own schemas which may leave my only other option to be xml

Thanks.

Jason



It of course depends on the nature of your application, i.e., how the user 
will be using the database. Are they going to perform inserts or updates of 
large data set all the time? There are some common techniques for sqlite to 
improve its performance, such as use PRAGMA synchronous = OFF;, use 
transactions to wrap up inserts and updates whenever possible, and use 
indexes. My experience and the tests done here: 
http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison indicated that indexes 
do improve performance greatly.


If your users were to input a CREATE TABLE statement, there's nothing much 
you can do. However, if you only let the user design a table, when you 
generate the CREATE TABLE statements, you might also want to take care of 
index creations.


Another thing you should look into is the concurrency. SQLite can't write 
(inserts and updates), when another connection is reading (selects) from the 
same database file. It looks like each user having their own database file 
is the way to go, but just make sure you handle busy status properly.


My 2 cents.

Best regards,
He Shiming 



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

Re: [sqlite] SQLite Order BY

[He Shiming]

Thanks shiming
we have tried this method as u suggested , but creating an index on a 5 
million records

take a long time, nearly 4 seconds.

And in our case the Database contain 5 Million to 30 or 50 million 
records. so if I try to create additional indices on these tables it takes 
too much time.


any other suggestions if u have please.
Best regards,
Manzoor Ilahi



I don't understand. Index creation is a one time deal, it's like tables 
(there are temporary indexes though, which are also similar to tables). You 
don't have to recreate indexes before queries. It only cost you 4 seconds, 
but future queries will be a lot faster. Newly inserted rows will be 
automatically indexed. Did you compare the performance before and after the 
creation of indexes? In my experience, indexes do improve performance a lot 
as long as you've indexed the correct columns, i.e. the columns appearing in 
where clause and order by clause.


Best regards,
He Shiming 



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

Re: [sqlite] SQLite Order BY

[He Shiming]

Dar All,

Can someone guide me, why the ORDER BY in SQLite is too slow. we observed 
that if the   change in data values is small then ORDER BY works better . 
but if the change is big then the performance is very slow. Even sometime 
I can not finish a query execution, and wait and may be sometime need to 
kill the process.


also another point that i observed is ,  when ..order by fieldName , if 
fieldName  is varchar,the excutiion is faster ,and if fieldName is 
float,the execution can't be finished!



Any Idea Please

thanks

Manzoor Ilahi

Queries that we tested and the Schema is given as under.


//--
select * from hvh5m,itm where hvh5m.Column4=itm.Icol1 order by Column8

this SQl can't be finished

//--

select * from hvh5m,itm where hvh5m.column4=itm.Icol1 order by itm.Icol1;

this sql can be finished!

//--

hvh5m: column1 varchar(8 );
column2 varchar(4);
Column3 varchar(2);
Column4 varchar(4)£»
column5 varchar(7);
column6 varchar(8 );
column7 varchar(1);
Column8float(10£¬2);

itm£º Icol1 varchar(4);
itemname varchar(20);
Icol3 varchar(1);
Icol4 varchar(1);
Icol5 varchar(1);
Icol6 varchar(1);
Icol7 varchar(1);
Icol8 varchar(1);
Icol 9 varchar(1);
Icol 10 varchar(1);
Icol 11 varchar(1);
Icol 12 varchar(1);
Icol 13 varchar(1);
Icol 14 varchar(1);
Icol 15 varchar(1);
Icol 16 varchar(1);

There is one index on itm.Icol1.



You can try creating two additional indexes, one on hvh5m.Column4 and the 
other on hvh5m.Column8. They should speed things up.


Best regards,
He Shiming 



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

Re: [sqlite] results of SELECT contains carriage returns

[He Shiming]

Hi,

I have a database where some values contain carriage returns. I am using 
the command line to execute sqlite commands, eg:


sqlite3 Disney.db "SELECT * FROM Characters"

When I get the result of a SELECT statement, the output has columns 
separated by pipe characters and rows by new lines. So, if a value 
contains a return, it prematurely starts a new line, and messes up my 
output result.


What's the best approach to deal with this?

I guess I could use the command:

.mode csv

to change the output to csv (which wraps newlines in values within 
quotes). But it doesn't hold from one sqlite3 command to the next. And I 
can't see how to do this in a single command line, and there'd be too much 
overhead to write the ".mode" and SELECT commands to a temporary file to 
then invoke through a sqlite3 command.


I hope I'm missing something simple. Can anyone help, please?

Thanks,
Tom




Well, you didn't say which midware or platform were you using. I'll talk in 
the perspective of C API.


In reality, we never parse query results directly from the "table" output 
from command line shell. I think, usually, the command line shell is 
provided for the convenience of quickly evaluating some of the details of 
the database system. But when you were to develop an application, you simply 
don't use the database through a command line shell.


You are supposed to use a combination of sqlite3_open, sqlite3_prepare, 
sqlite3_step, which are the APIs provided by sqlite, to access the database. 
And use APIs like sqlite3_column_* to get the content of the query. It 
certainly doesn't matter if there are return characters in the results. It 
won't matter even if characters are unreadable binaries. That's why we have 
a BLOB type. You are not supposed to parse them, you can get them directly 
through these APIs. You might want to learn more about a database management 
system, after all, sqlite is one.


Best regards,
He Shiming 



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

Re: [sqlite] Regarding sqlite3_exec

[He Shiming]

Hi List,
 If I use sqlite3_exec to query a database,
How can I know that the results in the data base got over. For example If
I am expecting a 10 results in some for loop and actually there are only
five results , How can I get a notification or return value that the
results completed or Is there any way I can get SQLITE_DONE through
sqlite3_Exec.  What return value I will get If I query an empty table.


Thanks and Regards,
 Vivek R



SQLite didn't provide a "get number of rows" function for the result set. It 
is mentioned in the document that sqlite3_exec is actually a wrapper for 
sqlite3_prepare and sqlite3_step. It is in my opinion that sqlite3_exec 
should only be used when the result of the query isn't that important. For 
instance, a pragma query. For the record, sqlite3_exec did provide a 
callback function in which you can count and get the number of rows in a 
resultset. The optimal way is that you prepare the statement, fetch and 
count the results with sqlite3_step.


Another thing I noticed from your question is that you might not want to 
"expect 10 results". It's not very wise to design a hard loop such as 
for(i=0;i<10;i++) when comes to a database query resultset. A better way 
would be to use an array to store the result set they way you could 
understand, and process them later. Then you'll have 
for(i=0;i<array.size()<10?array.size():10;i++).


Best regards,
He Shiming 



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

Re: Re[2]: [sqlite] Regarding aborting a query

[He Shiming]

Actually it implements such syntax. See LIMIT and OFFSET at
http://www.sqlite.org/lang_select.html

Filip




Right, I missed that. Sorry for the confusion.

Best regards,
He Shiming

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

Re: [sqlite] Regarding aborting a query

[He Shiming]

Hi List,
  Let us assume there are 10 results for a Query. If I want to abort the
Query after 5 results How can I do that? What notification I will get once 
I

got all the results and of if there are any errors after 6th result.

How to specify a primary Key, foreign key while creating a table.

Thanks and Regards,
 Vivek R



At the present time, sqlite hadn't implemented syntax such as LIMIT 0,5. So 
you have to do it manually. It's fairly easy if you use prepared statements. 
The steps are as follows:


1. Call sqlite3_prepare with your query string
2. Call sqlite3_bind_* to bind parameters if applicable
3. Call sqlite3_step and sqlite3_column_* to fetch results and keep count, 
since sqlite3_step only returns one row at a time, you can decide from its 
return values, if it's SQLITE_DONE, there's probably less than 5 results, if 
it's still SQLITE_ROW when your counter reaches 5, you break the while loop.
4. Call sqlite3_reset or sqlite3_finalize, and yes you can reset or finalize 
a query when it's not done.


Best regards,
He Shiming 



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

Re: [sqlite] Regarding Performance and removing create view

[He Shiming]

Hi List,
  we have ported SQLite to one of our consumer products. We require some
minimal applications only like creating and deleting table and Inserting ,
Querying, deleting rows in a table. So, We are planning to remove some of
the features like create view and etc , as we are facing some memory
problems and we want to increase the performance of SQLite. Could you 
please
suggest me what are the things to be done to remove the features like 
views

and others ( which we usually not required) and How to increase the
performance of SQLite.


Thanks and Regards,
 Vivek R



Generally, you can use transaction to wrap up your inserts and updates to 
improve writing performance. The details are discussed here: 
http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations . 
Additionally, PRAGMA synchronous = OFF; will force sqlite to rely on 
operating system cache when writing. It's a great time saver. Check 
http://www.sqlite.org/pragma.html for its details and safety concerns.


After all, sqlite is a database management system.  So to improve reading 
(selecting) speed, you'll need to create indexes on those columns appears 
often in your where clause. Sometimes it's necessary to do some benchmarks 
and use queries such as EXPLAIN QUERY PLAN SELECT * ... to find out whether 
these indexes are improving the performance.


Best regards,
He Shiming 



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

Re: [sqlite] Multiple Updates

[He Shiming]

Hi All,

Is it possible to do multiple updates of blobs using the bind variables, I 
was doing them 1 at a time but it was a little slow.


For example :-

rc = sqlite3_prepare(objects_db, "UPDATE table SET proprietary_data = ? 
WHERE device_id = ? and instance = ?", -1, , 0);


for (i= 0; i <10;i++)
{
   sqlite3_bind_blob(pStmt, 1, proprietary_data, proprietary_data_len, 
SQLITE_STATIC);

   sqlite3_bind_int(pStmt, 2, object->device_id);
   sqlite3_bind_int(pStmt, 3, object->objectIdentifier.instance);

   rc = sqlite3_step(pStmt);
}
   if (sqlite3_finalize(pStmt))


Regards,

Chris



There is a pretty simple answer to all these kinds of questions. Use 
transactions. You can see the details about transactions and performance 
here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations . In my 
experience, performance do improve a lot when transaction is used for such 
an update or insert iteration.


If you don't know it yet, another idea is to use PRAGMA synchronous = OFF; . 
This way, sqlite will rely on operating system disk cache, which is an even 
greater improvement in performance.


Best regards,
He Shiming 



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

Re: [sqlite] Locking problems

[He Shiming]

Hi !

I'm getting a lots of "database is locked" (code:5).

My app keeps giving up on one machine, it might be that
there is a network problem but I not 100% sure.

Anyway, when the app hangs all the other machines
including the machine where the database file is get
the "database is locked" (code:5) error.
To get rid of the error I have to close my app on all
machines (five total) and sometimes reboot the machine
with the database. As far as I can tell there are no
processes still running.

The database is as far as I know not corrupted and seems
ok after "restarting the network".

Why do I get "database is locked" (code:5) and what do I
have to do to avoid it ?
I can try to ensure that there is a try/catch block but
I'm unsure how to unlock the database, Is it enough to
do a sqlite3_close() or do I need to ROLLBACK TRANSACTION
and sqlite3_finalize() before I sqlite3_close() ?

Windows XP, SQLite 3.3.4.

/Martin
ma1999ATjmaDOTse



Usually, the SQLITE_LOCKED means that you tried to prepare or execute a new 
SQL statement without resetting or finalizing a previous one on a single 
connection. The statements should be prepared and executed this way:

sqlite3_prepare
sqlite3_bind_*
sqlite3_step
// more sqlite3_steps if there are more statements
sqlite3_finalize

You can't put another sqlite3_prepare in the middle. If you did, you'll get 
the database is locked error. And if any error encountered during the 
process, you should call sqlite3_reset to reset the statement.


I suggest that you check your code very carefully to see if any statements 
are not finalized or resetted.


Best regards,
He Shiming 



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

Re: [sqlite] Wish to store a C structure in sqlite column

[He Shiming]

Dear Friends,


  I am in the process of forming a Generic API,(sql oriented and
BerkelyDB
and sister databases). In the process of integration ,i like to store a
Structure in Sqlite.

 as far as my knowledge SQLITE allows me to declare the column types
suppoted by the programming languare or say i am using blob . My
requirement
is i wish to store a structure in the SQLite column.

  I am unable to form a sql statement to store the structure ,i am also
not
clear with whether i can have a strucure as column type.

suggestions will be really helpful.

 Thanking you,
 B.Narendran


A C struct is already a blob. Inserting it to a table is quite 
straightforward. I assume you already created your tables, and have your 
connection open. You can try these to insert a struct:


struct MyStruct {
   long nSomeStuff[1024];
};
MyStruct thisStruct = {0};
sqlite3* db; // already opened
sqlite3_stmt* pStmt = NULL;
const char* pszUnused;

sqlite3_prepare (db, "INSERT INTO TABLE (BLOBCOLUMN) VALUES (?);", -1, 
, );

sqlite3_bind_blob (pStmt, 1, , sizeof(MyStruct), SQLITE_STATIC);
sqlite3_step (pStmt);
sqlite3_finalize (pStmt);

Blob data must be prepared using a wildcard (?) and be bound later. Remember 
that when binding, the index of the first column is 1, not 0. And you have 
to check return values for each of the sqlite3_* functions, they may fail or 
return busy. There's no need to do any memory copy. If you will destroy the 
struct before sqlite3_step is called, then change SQLITE_STATIC to 
SQLITE_TRANSIENT. This way, sqlite will make an internal copy when 
sqlite3_bind_blob is called.


When retrieving data, the size of the column is determined by 
sqlite3_column_bytes. You use the value returned by this function to decide 
how much memory you needed to copy from the pointer returned by 
sqlite3_column_blob to your own struct.


I'm not sure if it'll help you to understand, in the eye of a database 
system, a C struct doesn't have any difference to the data in a block of 
memory buffer, or something like long long nVars[100];. They only need two 
things to get started, a pointer, and the size.


Best regards,
He Shiming 



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

Re: [sqlite] Disabling locking?

[He Shiming]

I want to use SQLite with only one user executing queries sequentially , so
I don't need locking. Is there a simple way to completely disabling 
locking?

Specific instructions if possible please, thanks...



There are two kinds of locks.

First one is a file lock, as in functions returning SQLITE_BUSY. From what I 
understand, if you do execute queries sequentially, or serialize the queries 
from threads yourself. You won't be experiencing any file lockings.


The second is the database lock, as in functions returning SQLITE_ERROR. 
This could only happen when you didn't write your code correctly. For 
instance, if you forgot to finalize one prepared INSERT statement, and tried 
to prepare another INSERT statement right behind, you'll get SQLITE_ERROR, 
while sqlite3_reset will return SQLITE_LOCKED.


Queries must be executed sequentially on a single sqlite connection. It's 
not your choice. If you need to execute parallel queries, you have to open 
another connection and handle busy status. Locking is not a feature for you 
to disable. It's a fact that you can't read or write before another writing 
process is finished.


Best regards,
He Shiming 



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

Re: [sqlite] Quotation handling bug?

[He Shiming]

- Original Message - 
From: "Robert Simpson" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Tuesday, September 19, 2006 2:19 PM
Subject: RE: [sqlite] Quotation handling bug?

Single quotes are supposed to be used for string literals, and double
quotes/brackets for identifiers such as table names and column names.

SELECT 'ID' FROM 'MYTABLE' is selecting the literal string 'ID', not the
column.

I am not positive, but I think if you use single quotes around something,
SQLite will first try and treat it like a literal -- and if the SQL parser
is expecting an identifier where you've placed a literal, it will try and
re-evaluate it as an identifier instead.  So since the statement CREATE
TABLE 'MYTABLE' ('ID' INTEGER PRIMARY KEY) contains literals in places
identifiers are expected, SQLite treats them as identifiers instead of
literals.

Conversely, SELECT 'ID' FROM 'MYTABLE' is ambiguous in that 'ID' could 
mean
the literal string 'ID' or could mean an identifier.  In a SELECT clause 
the

string is first evaluated as a literal, and since literals are allowed in
the return columns of a SELECT, the literal code path is taken and there 
is
no need to try and evaluate it as an identifier.  The FROM 'MYTABLE' 
portion

is parsed later, but literals aren't allowed as a target in a FROM clause,
so 'MYTABLE' is treated as an identifier.

In short ... Don't use single-quotes around identifiers.  Use 
single-quotes
for string literals, and use double-quotes or brackets around identifiers 
so

your code is more readable and explicit.

Robert



That's very helpful. Thanks.

Best regards,
He Shiming 



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

[sqlite] Quotation handling bug?

[He Shiming]

Hi,

I think I found a bug in sqlite version 3.3.7. The steps to reproduce it is 
as follows. I've tested it on Windows only.


C:\Something>sqlite3 newdb.db
CREATE TABLE 'MYTABLE' ('ID' INTEGER PRIMARY KEY);
INSERT INTO 'MYTABLE' ('ID') VALUES(1);
INSERT INTO 'MYTABLE' ('ID') VALUES(2);
INSERT INTO 'MYTABLE' ('ID') VALUES(3);

This is pretty straightfoward. But when I try to fetch the data out...
SELECT 'ID' FROM 'MYTABLE' WHERE 'ID'=2;  // no result
SELECT 'ID' FROM 'MYTABLE' WHERE ID=2; // result is ID
SELECT ID FROM 'MYTABLE' WHERE ID=2; // result is 2
SELECT 'MYTABLE'.'ID' FROM 'MYTABLE' WHERE 'MYTABLE'.'ID'=2; // result is 2

I guess, to make it safer, I'll have to use the last one. However, the 
behavior or the first one and the second one looks like malfunctioning. The 
four queries should produce completely equivalent results, which is "2". Or 
is it something I did wrong?


Best regards,
He Shiming 



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