Re: [sqlite] sorting two distinct groups

[e-mail mgbg25171]

Igor
Thank you!
Your query is extremely close
but for the repetition of the c.ids at the end

I've tried putting group by (c,id) but got an error
Indeed my weakness seems to be having little idea of how to insert the
stuff for a single query into compound queries.
e.g.
If I want to introduce an "order by clause" for the firms stuff I can't
quite see how to insert it without again causing an error.

Any advice greatfully received.
Dean


On 4 February 2013 03:40, Igor Tandetnik  wrote:

> On 2/3/2013 3:43 PM, e-mail mgbg25171 wrote:
>
>> SELECT f.id FROM firms AS f
>> WHERE f.id NOT IN (SELECT c1.firm_id FROM calls AS c1) OR f.id IS NULL
>> union
>> SELECT f2.id FROM firms AS f2
>> WHERE f2.id IN (SELECT c2.firm_id FROM calls AS c2)
>> order by (c1.last is null, c2.last is not null)
>>
>> I have two tables firms and calls.
>> I'd like to list all the firms for which no calls records exist
>> then
>> I'd like to list all firms for which calls records DO exist
>> in order of earliest last call first
>>
>
> select f.id from firms f left join calls c on (f.id = c.firm_id)
> order by c.last;
>
> NULL compares less than any other value, so rows for which there are no
> calls will sort at the top.
> --
> Igor Tandetnik
>
> __**_
> 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] FTS problem with 'NOT term' queries

[Dan Kennedy]

On 02/04/2013 12:18 AM, Alexey Pechnikov wrote:

Hello!




And as result it's impossible to search docs in some situations:
SELECT * FROM docs WHERE docs MATCH 'NOT sqlite';
Error: malformed MATCH expression: [NOT sqlite]



As far as I can tell, in MATCH syntax NOT is a binary operator, denoting
set difference. You are trying to use it as a unary operator.



Well, and how to rewrite query MATCH 'NOT sqlite'? In simple FTS qery
syntax is possible to use "-sqlite" but is the equal construction by
extended syntax?


Not possible. And note that the query "-sqlite" doesn't actually work
either. Always returns an empty set.

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

Re: [sqlite] sorting two distinct groups

[Igor Tandetnik]

On 2/3/2013 3:43 PM, e-mail mgbg25171 wrote:

SELECT f.id FROM firms AS f
WHERE f.id NOT IN (SELECT c1.firm_id FROM calls AS c1) OR f.id IS NULL
union
SELECT f2.id FROM firms AS f2
WHERE f2.id IN (SELECT c2.firm_id FROM calls AS c2)
order by (c1.last is null, c2.last is not null)

I have two tables firms and calls.
I'd like to list all the firms for which no calls records exist
then
I'd like to list all firms for which calls records DO exist
in order of earliest last call first


select f.id from firms f left join calls c on (f.id = c.firm_id)
order by c.last;

NULL compares less than any other value, so rows for which there are no 
calls will sort at the top.

--
Igor Tandetnik

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

Re: [sqlite] select max(x), y from table

[Simon Slavin]

On 4 Feb 2013, at 2:38am, Igor Tandetnik  wrote:

> On 2/3/2013 1:58 PM, Simon Slavin wrote:
>>> SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y)
>> 
>> Your query is not standard SQL92.  SQL92 does not allow SELECT commands as 
>> part of expressions.
> 
> Does too:
> 
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
> 
> 6.11  ::= ... |  | ...
> 7.11   ::= 
>  ::=   

I stand -- sprawl -- corrected.  I misread the documentation.  The 1999 spec 
introduced /named/ subqueries not subqueries.  Thanks for the correction.

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

Re: [sqlite] select max(x), y from table

[Igor Tandetnik]

On 2/3/2013 1:58 PM, Simon Slavin wrote:

SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y)


Your query is not standard SQL92.  SQL92 does not allow SELECT commands as part 
of expressions.


Does too:

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

6.11  ::= ... |  | ...
7.11   ::= 
 ::=   

--
Igor Tandetnik

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

Re: [sqlite] sqlite 2.1 visual basic 5 or 6

[Richard Hipp]

On Sun, Feb 3, 2013 at 11:43 AM, Jean-Claude Faure wrote:

> Hello
>
> I use sqlite 2.1 and vb 5/6 with psvutils32.dll witch is a wrapper
> compiled by pivotal solution (O hara) many years ago
>
> may I use sqlite3 with same VB ?
>

No.  The file format changed going from SQLite2 to SQLite3.  (That was 8.5
years ago.  You *definitely* need to upgrade.  This will be painful due to
the file format change, but once that is done, your life will be much
easier.)





>
> Advantages ?? Inconvenients ?
>
>
> Remarq : I use only
> open
> close
> select from  where
> insert
> update
> delete
> begin
> commit
> It's suffisant for me !!!
>
>
>
>
> Excuse me for my english (my maternal language is french)
> Thanks if you respond to me
> --
> jcf.s...@gmail.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] populating a table as quickly as possible

[Simon Slavin]

On 4 Feb 2013, at 12:02am, Paul Sanderson  wrote:

> currently using :
> journal_mode = off
> page_size=16386
> cache_size = 1
> synchronous = off

Reasonable.

> I load lots of similar data sets (each into a separate db) and load time is
> definitely an issue. There are processing delays and loading a db can take
> 30+ minutes, if I can shave off even a few minutes on each run it is worth
> it to my customers

Note that unless you all have identical hardware, the settings which will make 
your computer do it fastest are not necessarily the same settings that will 
make your customers' computers run fastest.

> Trial and error seems to indicate that this works best - but interesed in
> others views.

The thing that's likely to speed that operation up most is switching from 
rotating hard disks to SSD.  If saving that amount of time is that important to 
your customers, that's what they'll do.  If they're not willing to pay the 
extra for an SSD in their own computer, they're probably not willing to pay you 
for your testing and programming time.

On the other hand if you're all already running SSDs you've already taken the 
big step and sorry for wasting your time.

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

Re: [sqlite] populating a table as quickly as possible

[Paul Sanderson]

currently using :
journal_mode = off
page_size=16386
cache_size = 1
synchronous = off

I load lots of similar data sets (each into a separate db) and load time is
definitely an issue. There are processing delays and loading a db can take
30+ minutes, if I can shave off even a few minutes on each run it is worth
it to my customers

Trial and error seems to indicate that this works best - but interesed in
others views.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does

[Keith Medcalf]

All that is weird is that SQLite appends a LIMIT 1 to the subquery to ensure it 
only returns  one row.  This can be seen clearly if you look at the EXPLAIN 
ouput.

Some other SQL engines just explode ... some throw an error.

I suppose it all depends on your interpretation of "do as I mean, not as I say".


Sent from Samsung Mobile

 Original message 
From: Petite Abeille  
Date:  
To: General Discussion of SQLite Database  
Subject: Re: [sqlite] sqlite bug? Subquery containing like-expression in
where clause with starting '%' returns no rows, but without '%' does 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select max(x), y from table

[E.Pasma]

Op 3 feb 2013, om 22:06 heeft Gabor Grothendieck het volgende  
geschreven:


On Sun, Feb 3, 2013 at 4:00 PM, Petite Abeille  wrote:


On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck  wrote:



What is the SQLite consortium?


http://www.sqlite.org/consortium.html
___


So is E. Plasma saying that there is a private version of sqlite that
has the feature in question but the free version of sqlite that the
rest of us have does not have that feature?


As you said:

In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x..

And that is about the free version. Not all that is gold does glitter.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does

[Thomas Mittelstaedt]

Am Sonntag, den 03.02.2013, 14:00 -0700 schrieb Keith Medcalf:
> > SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent
> >   FROM moz_places, moz_bookmarks
> >  WHERE moz_places.id = moz_bookmarks.fk
> >and moz_bookmarks.parent = (select id 
> >  from moz_bookmarks 
> > where title like '%arbeit%')
> 
> Firstly, are you sure you shouldn't be saying IN rather that = ?

Yep. Correct. Thanks, Keith. No bug!

> 
>  SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent
>FROM moz_places, moz_bookmarks
>   WHERE moz_places.id = moz_bookmarks.fk
> and moz_bookmarks.parent = (select id 
>   from moz_bookmarks 
>  where title like '%arbeit%' limit 1)
> 
> Is what is actually being processed.  
> 
> If the id of the one row (selected by happenchance) satisfying the constraint 
> in the subquery does not happen to have any rows where that id is used in the 
> parent column, you will get no result rows at all.
> 
> You probably mean something like:
> 
>  SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent
>FROM moz_places, moz_bookmarks
>   WHERE moz_places.id = moz_bookmarks.fk
> and moz_bookmarks.parent IN (select id 
>from moz_bookmarks 
>   where title like '%arbeit%')
> 
> which will return results if ANY of the selected id are used in the parent 
> column, rather than requiring that only the happenchance selected id be used 
> in the parent column.
> 
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
> 
> 
> 
> 


-- 
thomas


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

Re: [sqlite] select max(x), y from table

[Petite Abeille]

On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck  wrote:

> What is the SQLite consortium?

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

Re: [sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does

[Keith Medcalf]

> SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent
>   FROM moz_places, moz_bookmarks
>  WHERE moz_places.id = moz_bookmarks.fk
>and moz_bookmarks.parent = (select id 
>  from moz_bookmarks 
> where title like '%arbeit%')

Firstly, are you sure you shouldn't be saying IN rather that = ?

 SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent
   FROM moz_places, moz_bookmarks
  WHERE moz_places.id = moz_bookmarks.fk
and moz_bookmarks.parent = (select id 
  from moz_bookmarks 
 where title like '%arbeit%' limit 1)

Is what is actually being processed.  

If the id of the one row (selected by happenchance) satisfying the constraint 
in the subquery does not happen to have any rows where that id is used in the 
parent column, you will get no result rows at all.

You probably mean something like:

 SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent
   FROM moz_places, moz_bookmarks
  WHERE moz_places.id = moz_bookmarks.fk
and moz_bookmarks.parent IN (select id 
   from moz_bookmarks 
  where title like '%arbeit%')

which will return results if ANY of the selected id are used in the parent 
column, rather than requiring that only the happenchance selected id be used in 
the parent column.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

[sqlite] sorting two distinct groups

[e-mail mgbg25171]

SELECT f.id FROM firms AS f
WHERE f.id NOT IN (SELECT c1.firm_id FROM calls AS c1) OR f.id IS NULL
union
SELECT f2.id FROM firms AS f2
WHERE f2.id IN (SELECT c2.firm_id FROM calls AS c2)
order by (c1.last is null, c2.last is not null)

I have two tables firms and calls.
I'd like to list all the firms for which no calls records exist
then
I'd like to list all firms for which calls records DO exist
in order of earliest last call first i.e. the one I'd called furthest back
first
if that makes sense.
Igore helped me last time with an order by (), coalesce() structure
but I can't see how to map this to my current problem
Any advice very much appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does

[Thomas Mittelstaedt]

Could this be a bug?

SELECT moz_places.id, moz_places.url, moz_places.title,
moz_bookmarks.parent
FROM moz_places
, moz_bookmarks
 WHERE moz_places.id = moz_bookmarks.fkand
 moz_bookmarks.parent = (select id from moz_bookmarks where title
like '%arbeit%')

returns no rows,

but using the subquery without the starting '%' returns data:

"297607","http://www.minijob-zentrale.de/DE/0_Home/node.html","Minijob-Zentrale 
- Startseite","83332"

Also just doing a 
select id from moz_bookmarks where title like '%arbeit%'
also returns several rows.

I am using sqlite3 version 3.7.14.1 under firefox 18 (Gecko 18.0.1)
using a firefox plugin called Sqlite Manager.

CREATE TABLE moz_bookmarks (id INTEGER PRIMARY KEY,type INTEGER, fk
INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title
LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER,
lastModified INTEGER, guid TEXT)

CREATE TABLE moz_places (id INTEGER PRIMARY KEY, url LONGVARCHAR, title
LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden
INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id
INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER,
guid TEXT)

-- 
thomas


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

Re: [sqlite] select max(x), y from table

[E.Pasma]

Op 3 feb 2013, om 20:01 heeft Gabor Grothendieck het volgende  
geschreven:



On Sun, Feb 3, 2013 at 1:40 PM, E.Pasma  wrote:


Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende  
geschreven:




On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma  wrote:



Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende  
geschreven:




On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:



In 3.7.11 there was a change to support the feature in the  
subject
which refers to guaranteeing that y comes from the same row  
having

maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as  
the

query here:



http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html




The query in that thread is of the form "select * from MyTable  
group by
Name having max(Timestamp)", and the expectation, somehow, is  
that the
HAVING clause would cause each group to be represented by a row  
for

which
max(Timestamp) is reached. I'm not sure where this expectation  
comes

from.
This is a valid SQL statement whose HAVING clause means "only  
include a
group in the resultset if max(Timestamp) for this group is  
logically

true"
(that is, not NULL, 0, empty string or empty blob).

A semantic change of the nature you envision is not backward  
compatible

-
it modifies the meaning of existing valid statements. Also, I'm  
pretty

sure
it's not supported by any SQL standard; and I'm not aware of any  
DBMS

that
would interpret the statement the way you want (which doesn't  
mean none

such
exists, of course).

All in all, It seems unlikely that such a proposal would be  
entertained.

--
Igor Tandetnik




Hi,

Is it then not a perfect solution? it works, if in the example the
timestamp
is always logically true (i.e nit 0 or NULL). Otherwise you might  
write

something like HAVING MAX(timestamp) OR MAX(timestamp) IS NULL.

This construction comes in useful to deal with the issue as was  
recently

observed with this featurre (see

http://www.mail-archive.com/sqlite-users@sqlite.org/msg74761.html

The construction does not involve any semantic change. The  
question is

still
if it will work also in future versions of SQLite.



What was wanted was to pick out the row with the largest timestamp  
in

each group (and not to pick out those groups with a non-null maximum
timestamp) so the fact that the desired result was returned in the
example would seem to be a  coincidence assuming no specific feature
along these lines has been implemented in SQLite.




OK, but if one does not assume any specific (non SQL standard)  
features, the

query is something like:

   SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE  
t2.y =

t.y)

If one looks for a shortcut for this quite common query, then the  
equivalent

SQLite query is really attractive:

   SELECT * FROM t GROUP BY y HAVING MAX (x) IS NOT NULL



This seems to be going around in circles. These two are not equivalent
unless there has been some specific feature in sqlite to guarantee
that they are the same.   This was my original question.

If x is never null then the first gives the row in each group with
largest x

Indeed



and the second only gives an arbitrary row from each group
although it may in specific instances give the same result by chance.
True but not by chance. The feature is supported for a member of the  
SQLite consortium and applies if there is only a single aggregate  
function. Special is the example is that the aggregate function comes  
in the HAVING clause. But that appears to work fine too. I hope that  
the customer who requested this feature agrees that it should remain so


Thanks, EPasma.

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

Re: [sqlite] populating a table as quickly as possible

[Dominique Pellé]

Simon Slavin  wrote:

>
> On 3 Feb 2013, at 6:34pm, Paul Sanderson  wrote:
>
>> I want to populate a large table (millions of rows) as quickly as possible,
>>
>> The data set will not be operated on until the table is fully populated and
>> if the operation fails I will be starting again. the operation is a one off
>> and the table will not be added to at a future date.
>>
>> What are optimisations will work best for me?
>
> Don't worry about hacking into SQLite.  You should initially try it doing 
> something like
>
> DROP all your indexes
> DELETE FROM myTable
> BEGIN
> INSERT ...
> INSERT ...
> INSERT ...
> ...
> END
> CREATE whatever indexes you want
>
> If that proves not to be fast enough for you, you can mess
> with making a new transaction every 100,000 rows or
> something: just do "END; BEGIN" every so-often.
> If it's still not fast enough you can mess with
> 'PRAGMA journal_mode = OFF'.

In addition to all this, you can also try:

PRAGMA synchronous=off

But read about the risks:

http://www.sqlite.org/pragma.html#pragma_synchronous

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

Re: [sqlite] select max(x), y from table

[Simon Slavin]

On 3 Feb 2013, at 6:40pm, "E.Pasma"  wrote:

> OK, but if one does not assume any specific (non SQL standard) features, the 
> query is something like:
> 
>   SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y)

Your query is not standard SQL92.  SQL92 does not allow SELECT commands as part 
of expressions.  Subqueries weren't introduced into SQL until SQL99.  Very few 
of the engines you see in popular use today implement all the features of 
SQL99, they're mostly supersets of SQL92.

> If one looks for a shortcut for this quite common query, then the equivalent 
> SQLite query is really attractive:
> 
>   SELECT * FROM t GROUP BY y HAVING MAX (x) IS NOT NULL

That is a way of including only groups where NONE of the x values in the group 
are NULL.  Is that what you want ?

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

Re: [sqlite] select max(x), y from table

[E.Pasma]

Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende  
geschreven:



On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma  wrote:


Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven:



On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:


In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as the
query here:


http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html



The query in that thread is of the form "select * from MyTable  
group by
Name having max(Timestamp)", and the expectation, somehow, is that  
the
HAVING clause would cause each group to be represented by a row  
for which
max(Timestamp) is reached. I'm not sure where this expectation  
comes from.
This is a valid SQL statement whose HAVING clause means "only  
include a
group in the resultset if max(Timestamp) for this group is  
logically true"

(that is, not NULL, 0, empty string or empty blob).

A semantic change of the nature you envision is not backward  
compatible -
it modifies the meaning of existing valid statements. Also, I'm  
pretty sure
it's not supported by any SQL standard; and I'm not aware of any  
DBMS that
would interpret the statement the way you want (which doesn't mean  
none such

exists, of course).

All in all, It seems unlikely that such a proposal would be  
entertained.

--
Igor Tandetnik



Hi,

Is it then not a perfect solution? it works, if in the example the  
timestamp
is always logically true (i.e nit 0 or NULL). Otherwise you might  
write

something like HAVING MAX(timestamp) OR MAX(timestamp) IS NULL.

This construction comes in useful to deal with the issue as was  
recently

observed with this featurre (see

http://www.mail-archive.com/sqlite-users@sqlite.org/msg74761.html

The construction does not involve any semantic change. The question  
is still

if it will work also in future versions of SQLite.


What was wanted was to pick out the row with the largest timestamp in
each group (and not to pick out those groups with a non-null maximum
timestamp) so the fact that the desired result was returned in the
example would seem to be a  coincidence assuming no specific feature
along these lines has been implemented in SQLite.




OK, but if one does not assume any specific (non SQL standard)  
features, the query is something like:


SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y)

If one looks for a shortcut for this quite common query, then the  
equivalent SQLite query is really attractive:


SELECT * FROM t GROUP BY y HAVING MAX (x) IS NOT NULL


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

Re: [sqlite] FTS problem with 'NOT term' queries

[Alexey Pechnikov]

Hello!

>
>> And as result it's impossible to search docs in some situations:
>> SELECT * FROM docs WHERE docs MATCH 'NOT sqlite';
>> Error: malformed MATCH expression: [NOT sqlite]
>>
>
> As far as I can tell, in MATCH syntax NOT is a binary operator, denoting
> set difference. You are trying to use it as a unary operator.


Well, and how to rewrite query MATCH 'NOT sqlite'? In simple FTS qery
syntax is possible to use "-sqlite" but is the equal construction by
extended syntax?


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

[sqlite] sqlite 2.1 visual basic 5 or 6

[Jean-Claude Faure]

Hello

I use sqlite 2.1 and vb 5/6 with psvutils32.dll witch is a wrapper
compiled by pivotal solution (O hara) many years ago

may I use sqlite3 with same VB ?

Advantages ?? Inconvenients ?


Remarq : I use only
open
close
select from  where
insert
update
delete
begin
commit
It's suffisant for me !!!




Excuse me for my english (my maternal language is french)
Thanks if you respond to me
-- 
jcf.s...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[Stephen Chrzanowski]

Just had a thought;

You could do a few things, unfortunately all at the code base level;

1> I don't know if Python will handle it, but I know most other languages
have a string-replace function, or, more specifically, in Delphi, there is
a "format" command in which you would create a string, fill it in with
parameters (Like %s, %d, etc) and then you have an array of variables the
system would fill in.  Create a variable that contains the SQL you need,
but replace what needs to be quoted with a format specifier.  Then,
regenerate the SQL with either quoted or non-quoted variables.  This would
become a PITA if you had 30 field names or so, however.  Not to mention bug
prone.

2> In line with a true string replace, replace all the field names in the
string to a format like '%db..table%' then run a string replace against all
the possible field names and replace them with either a quoted string, or a
non-quoted string.

3> Another option is to manually mangle the string based on what Sybase is
being fed.  So, if you're going to toss it into SQLite:
- scan the string for double-periods, and remember the position
- backup, character by character, until the first non-valid character, and
put a single/double quote
- go forward+1 from the remembered position until the first non-valid
character, and drop the same quote.

4> The last thing, off the top of my head, would be to locate each and
every SQL string, and move it into a file of its own, then duplicate for
SQLite and mangle as needed.  Along with option 1, nightmare to manage.

My thought, #3 would probably be the easiest to implement, as it'd require
one function to be created and an appropriate result returned then going
through the code and applying the function where needed, but it'd probably
the slowest since you'll be practically touching each and every character
in the string before its fed to the DBMS.  #2 would be next in line as a
search and replace based on field names would PROBABLY be more quicker
instead of mangling strings character by character, but it'd involve going
in and modifying the SQL calls themselves, as well as adding the
appropriate string replace commands.


On Sun, Feb 3, 2013 at 3:50 AM, Message Adams wrote:

>
> Thanks for everyone's help. Unfortunately, as Ryan highlighted, the double
> quotes around the table 'database..name' cannot be handled by sybase. It's
> a shame as I rather like Sqlite and works soo well with Python.
>
> All the best,
> Marc
>
>
>
>
> On 02/02/13 07:56, Mohit Sindhwani wrote:
>
>> Hi Ryan,
>>
>> On 2/2/2013 1:55 AM, Ryan Johnson wrote:
>>
>>> That would break sybase, though: the quotes would also tell it to treat
>>> the db name and periods as part of the table name, too:
>>>
>>> sqlite3> create table foo(x,y);
>>> sqlite3> .tables
>>> foo
>>> sqlite3> select * from "main.foo";
>>> Error: no such table: main.foo
>>>
>>
>> Since the OP (you aren't the OP, right?)  seems to have control over the
>> code, I wonder if a compile-time macro could be used to attach the quotes
>> around the database name only in the test environment. But, otherwise, I
>> think we're at the end of what can be done with SQLite.
>>
>> Best Regards,
>> Mohit.
>>
>>
>>
>> __**_
>> 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] trying to exclude records which have a field that is null

[e-mail mgbg25171]

I thought I tried
where by_or_on is not null
to start with and it didn't SEEM to work hence the <> ''.
However...I've just replaced <> '' with IS NOT NULL and
it works fine so I'm a bit mystified
Thanks for the advice all the same though

On 3 February 2013 10:08, Petite Abeille  wrote:

>
> On Feb 3, 2013, at 10:55 AM, e-mail mgbg25171 
> wrote:
>
> > (select * from calls where by_or_on <> '') c
>
> For the record… one thing to watch out… the empty string (aka '') and null
> are not the same… so if you are looking to eliminate nulls you have to use
> 'foo is not null'… as oppose to "foo != ''" for empty strings… in general,
> if you wish to maintain your sanity, do *not* use empty strings… just
> saying…
>
> http://en.wikipedia.org/wiki/Null_(SQL)
>
> All in all, you could rewrite your join along the following lines:
>
> select  firms.*
> fromfirms
>
> joincalls
> on  calls.firm_id = firms.id
> and nullif( calls.by_or_on, '' ) is not null -- FIXME: suspicious use
> of the empty string...
> ___
> 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] select max(x), y from table

[E.Pasma]

Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven:


On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:

In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as the
query here:

http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html


The query in that thread is of the form "select * from MyTable group  
by Name having max(Timestamp)", and the expectation, somehow, is  
that the HAVING clause would cause each group to be represented by a  
row for which max(Timestamp) is reached. I'm not sure where this  
expectation comes from. This is a valid SQL statement whose HAVING  
clause means "only include a group in the resultset if  
max(Timestamp) for this group is logically true" (that is, not NULL,  
0, empty string or empty blob).


A semantic change of the nature you envision is not backward  
compatible - it modifies the meaning of existing valid statements.  
Also, I'm pretty sure it's not supported by any SQL standard; and  
I'm not aware of any DBMS that would interpret the statement the way  
you want (which doesn't mean none such exists, of course).


All in all, It seems unlikely that such a proposal would be  
entertained.

--
Igor Tandetnik


Hi,

Is it then not a perfect solution? it works, if in the example the  
timestamp is always logically true (i.e nit 0 or NULL). Otherwise you  
might write something like HAVING MAX(timestamp) OR MAX(timestamp) IS  
NULL.


This construction comes in useful to deal with the issue as was  
recently observed with this featurre (see


 http://www.mail-archive.com/sqlite-users@sqlite.org/msg74761.html

The construction does not involve any semantic change. The question is  
still if it will work also in future versions of SQLite.


Regards, EPasma







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

Re: [sqlite] trying to exclude records which have a field that is null

[Petite Abeille]

On Feb 3, 2013, at 10:55 AM, e-mail mgbg25171  
wrote:

> (select * from calls where by_or_on <> '') c

For the record… one thing to watch out… the empty string (aka '') and null are 
not the same… so if you are looking to eliminate nulls you have to use 'foo is 
not null'… as oppose to "foo != ''" for empty strings… in general, if you wish 
to maintain your sanity, do *not* use empty strings… just saying…

http://en.wikipedia.org/wiki/Null_(SQL)

All in all, you could rewrite your join along the following lines:

select  firms.*
fromfirms

joincalls
on  calls.firm_id = firms.id
and nullif( calls.by_or_on, '' ) is not null -- FIXME: suspicious use of 
the empty string...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] trying to exclude records which have a field that is null

[e-mail mgbg25171]

I need to test this but this is looking promising

select f.* from firms f
inner join
(select firm_id, max(by_or_on) from calls
where by_or_on <> ''
group by firm_id
order by by_or_on) c
on f.id = c.firm_id

What do you think

On 3 February 2013 09:55, e-mail mgbg25171 wrote:

> I've done most of it with this
>
> select f.* from firms f
> inner join
> (select * from calls where by_or_on <> '') c
> on f.id = c.firm_id
>
> Phew!
>
>
> On 3 February 2013 09:40, e-mail mgbg25171 wrote:
>
>> Keith, Petite
>> I'm really grateful for your assistance. I've tried your solutions and
>> they don't quite give me what I want so...
>> sorry for misleading you. Secondly your suggestion that I explain in
>> words is a good one.
>> Here goes...
>>
>> I've got a table of FIRMS and a table of CALLS made to those firms.
>> In the table of calls to the firms, two of the fields are
>> unsurprisingly...FIRM_ID and
>> a a field called BY_OR_ON that records the date by which something must
>> be done.
>> BY_OR_ON is mostly null.
>> I want to FILTER the FIRMS records to EXCLUDE any which do not have a
>> single calls record with a non-null by field
>> I'm only interest in the LATEST/MAX BY_OR_ON date for each firm which
>> will exclude many of the calls records.
>> I then want to SORT the FIRMS records to order them by earlest...latest
>> BY_OR_ON date for each firm, first...
>> if earlies...latest BY_OR_ON makes sense.
>>
>> I hope this helps to explain
>> Best Regards
>>
>>
>>
>>
>>
>> On 3 February 2013 03:39, Keith Medcalf  wrote:
>>
>>> > Mayhaps you mean:
>>> >
>>> >  Select f.*
>>> >from firms f
>>> > left join (select firm_id, max(by_or_on) as boo
>>> >  from calls
>>> >  group by firm_id
>>> >having by_or_on is not null
>>> >  order by by_or_on desc) c
>>> >   on c.firm_id = f.id
>>> > order by boo desc;
>>>
>>> Should be:
>>>
>>>  Select f.*
>>>from firms f
>>> left join (select firm_id, max(by_or_on) as boo
>>>  from calls
>>>  group by firm_id
>>>having max(by_or_on) is not null
>>>  order by max(by_or_on) desc) c
>>>   on c.firm_id = f.id
>>> order by boo desc;
>>>
>>> or the equivalent:
>>>
>>>  Select f.*
>>>from firms f
>>> left join (select firm_id, max(by_or_on) as boo
>>>  from calls
>>>  group by firm_id
>>>having boo is not null
>>>  order by boo desc) c
>>>   on c.firm_id = f.id
>>> order by boo desc;
>>>
>>> ---
>>> ()  ascii ribbon campaign against html e-mail
>>> /\  www.asciiribbon.org
>>>
>>>
>>> > -Original Message-
>>> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>> > boun...@sqlite.org] On Behalf Of Keith Medcalf
>>> > Sent: Saturday, 02 February, 2013 17:11
>>> > To: General Discussion of SQLite Database
>>> > Subject: Re: [sqlite] trying to exclude records which have a field
>>> that is
>>> > null
>>> >
>>> >
>>> > I'm surprised you are getting anything at all since the statement is
>>> > semantically invalid.
>>> >
>>> >  Select f.*
>>> >from firms f
>>> > left join (select firm_id, max(by_or_on) as boo
>>> >  from calls
>>> >  group by firm_id
>>> >having by_or_on is not null
>>> >  order by by_or_on desc)
>>> >   on c.firm_id = f.id
>>> > order by boo desc;
>>> >
>>> > Perhaps you can describe the result you are attempting to obtain.
>>> >
>>> > **the having clause filters the "return rows" of a "group by"
>>> (aggregate)
>>> > select.  Therefore the references in the HAVING can only test against
>>> > "returned columns", not against the "source columns" from which the
>>> result
>>> > is derived.  Once uses the WHERE clause to apply conditions to the
>>> input
>>> > of the aggregate, and HAVING to apply conditions to the output.
>>> >
>>> > **the same applies to the "order by" of a "group by" (aggregate)
>>> select.
>>> > You can only order by the "returned result columns", not by the "source
>>> > data columns".
>>> >
>>> > Mayhaps you mean:
>>> >
>>> >  Select f.*
>>> >from firms f
>>> > left join (select firm_id, max(by_or_on) as boo
>>> >  from calls
>>> >  group by firm_id
>>> >having by_or_on is not null
>>> >  order by by_or_on desc) c
>>> >   on c.firm_id = f.id
>>> > order by boo desc;
>>> >
>>> > of course, the order by inside the table subquery is useless, so this
>>> > would become:
>>> >
>>> >  Select f.*
>>> >from firms f
>>> > left join (select firm_id, max(by_or_on) as boo
>>> >  from calls
>>> >  group by firm_id
>>> >having boo is not null) c
>>> >   on c.firm_id = f.id
>>> > order by boo desc;
>>> >
>>> > but perhaps you really mean:
>>> >
>>> >  Select f.*
>>> >from firms f
>>> >

Re: [sqlite] trying to exclude records which have a field that is null

[e-mail mgbg25171]

I've done most of it with this

select f.* from firms f
inner join
(select * from calls where by_or_on <> '') c
on f.id = c.firm_id

Phew!

On 3 February 2013 09:40, e-mail mgbg25171 wrote:

> Keith, Petite
> I'm really grateful for your assistance. I've tried your solutions and
> they don't quite give me what I want so...
> sorry for misleading you. Secondly your suggestion that I explain in words
> is a good one.
> Here goes...
>
> I've got a table of FIRMS and a table of CALLS made to those firms.
> In the table of calls to the firms, two of the fields are
> unsurprisingly...FIRM_ID and
> a a field called BY_OR_ON that records the date by which something must be
> done.
> BY_OR_ON is mostly null.
> I want to FILTER the FIRMS records to EXCLUDE any which do not have a
> single calls record with a non-null by field
> I'm only interest in the LATEST/MAX BY_OR_ON date for each firm which will
> exclude many of the calls records.
> I then want to SORT the FIRMS records to order them by earlest...latest
> BY_OR_ON date for each firm, first...
> if earlies...latest BY_OR_ON makes sense.
>
> I hope this helps to explain
> Best Regards
>
>
>
>
>
> On 3 February 2013 03:39, Keith Medcalf  wrote:
>
>> > Mayhaps you mean:
>> >
>> >  Select f.*
>> >from firms f
>> > left join (select firm_id, max(by_or_on) as boo
>> >  from calls
>> >  group by firm_id
>> >having by_or_on is not null
>> >  order by by_or_on desc) c
>> >   on c.firm_id = f.id
>> > order by boo desc;
>>
>> Should be:
>>
>>  Select f.*
>>from firms f
>> left join (select firm_id, max(by_or_on) as boo
>>  from calls
>>  group by firm_id
>>having max(by_or_on) is not null
>>  order by max(by_or_on) desc) c
>>   on c.firm_id = f.id
>> order by boo desc;
>>
>> or the equivalent:
>>
>>  Select f.*
>>from firms f
>> left join (select firm_id, max(by_or_on) as boo
>>  from calls
>>  group by firm_id
>>having boo is not null
>>  order by boo desc) c
>>   on c.firm_id = f.id
>> order by boo desc;
>>
>> ---
>> ()  ascii ribbon campaign against html e-mail
>> /\  www.asciiribbon.org
>>
>>
>> > -Original Message-
>> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> > boun...@sqlite.org] On Behalf Of Keith Medcalf
>> > Sent: Saturday, 02 February, 2013 17:11
>> > To: General Discussion of SQLite Database
>> > Subject: Re: [sqlite] trying to exclude records which have a field that
>> is
>> > null
>> >
>> >
>> > I'm surprised you are getting anything at all since the statement is
>> > semantically invalid.
>> >
>> >  Select f.*
>> >from firms f
>> > left join (select firm_id, max(by_or_on) as boo
>> >  from calls
>> >  group by firm_id
>> >having by_or_on is not null
>> >  order by by_or_on desc)
>> >   on c.firm_id = f.id
>> > order by boo desc;
>> >
>> > Perhaps you can describe the result you are attempting to obtain.
>> >
>> > **the having clause filters the "return rows" of a "group by"
>> (aggregate)
>> > select.  Therefore the references in the HAVING can only test against
>> > "returned columns", not against the "source columns" from which the
>> result
>> > is derived.  Once uses the WHERE clause to apply conditions to the input
>> > of the aggregate, and HAVING to apply conditions to the output.
>> >
>> > **the same applies to the "order by" of a "group by" (aggregate) select.
>> > You can only order by the "returned result columns", not by the "source
>> > data columns".
>> >
>> > Mayhaps you mean:
>> >
>> >  Select f.*
>> >from firms f
>> > left join (select firm_id, max(by_or_on) as boo
>> >  from calls
>> >  group by firm_id
>> >having by_or_on is not null
>> >  order by by_or_on desc) c
>> >   on c.firm_id = f.id
>> > order by boo desc;
>> >
>> > of course, the order by inside the table subquery is useless, so this
>> > would become:
>> >
>> >  Select f.*
>> >from firms f
>> > left join (select firm_id, max(by_or_on) as boo
>> >  from calls
>> >  group by firm_id
>> >having boo is not null) c
>> >   on c.firm_id = f.id
>> > order by boo desc;
>> >
>> > but perhaps you really mean:
>> >
>> >  Select f.*
>> >from firms f
>> > left join (select firm_id, max(by_or_on) as boo
>> >  from calls
>> > where by_or_on is not null
>> >  group by firm_id) c
>> >   on c.firm_id = f.id
>> > order by boo desc;
>> >
>> > which is a somewhat different thing.
>> >
>> > ---
>> > ()  ascii ribbon campaign against html e-mail
>> > /\  www.asciiribbon.org
>> >
>> >
>> > > -Original Message-
>> > > From: sqlite-users-boun...@sqlite.org 

Re: [sqlite] trying to exclude records which have a field that is null

[e-mail mgbg25171]

Keith, Petite
I'm really grateful for your assistance. I've tried your solutions and they
don't quite give me what I want so...
sorry for misleading you. Secondly your suggestion that I explain in words
is a good one.
Here goes...

I've got a table of FIRMS and a table of CALLS made to those firms.
In the table of calls to the firms, two of the fields are
unsurprisingly...FIRM_ID and
a a field called BY_OR_ON that records the date by which something must be
done.
BY_OR_ON is mostly null.
I want to FILTER the FIRMS records to EXCLUDE any which do not have a
single calls record with a non-null by field
I'm only interest in the LATEST/MAX BY_OR_ON date for each firm which will
exclude many of the calls records.
I then want to SORT the FIRMS records to order them by earlest...latest
BY_OR_ON date for each firm, first...
if earlies...latest BY_OR_ON makes sense.

I hope this helps to explain
Best Regards




On 3 February 2013 03:39, Keith Medcalf  wrote:

> > Mayhaps you mean:
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> >  group by firm_id
> >having by_or_on is not null
> >  order by by_or_on desc) c
> >   on c.firm_id = f.id
> > order by boo desc;
>
> Should be:
>
>  Select f.*
>from firms f
> left join (select firm_id, max(by_or_on) as boo
>  from calls
>  group by firm_id
>having max(by_or_on) is not null
>  order by max(by_or_on) desc) c
>   on c.firm_id = f.id
> order by boo desc;
>
> or the equivalent:
>
>  Select f.*
>from firms f
> left join (select firm_id, max(by_or_on) as boo
>  from calls
>  group by firm_id
>having boo is not null
>  order by boo desc) c
>   on c.firm_id = f.id
> order by boo desc;
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Keith Medcalf
> > Sent: Saturday, 02 February, 2013 17:11
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] trying to exclude records which have a field that
> is
> > null
> >
> >
> > I'm surprised you are getting anything at all since the statement is
> > semantically invalid.
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> >  group by firm_id
> >having by_or_on is not null
> >  order by by_or_on desc)
> >   on c.firm_id = f.id
> > order by boo desc;
> >
> > Perhaps you can describe the result you are attempting to obtain.
> >
> > **the having clause filters the "return rows" of a "group by" (aggregate)
> > select.  Therefore the references in the HAVING can only test against
> > "returned columns", not against the "source columns" from which the
> result
> > is derived.  Once uses the WHERE clause to apply conditions to the input
> > of the aggregate, and HAVING to apply conditions to the output.
> >
> > **the same applies to the "order by" of a "group by" (aggregate) select.
> > You can only order by the "returned result columns", not by the "source
> > data columns".
> >
> > Mayhaps you mean:
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> >  group by firm_id
> >having by_or_on is not null
> >  order by by_or_on desc) c
> >   on c.firm_id = f.id
> > order by boo desc;
> >
> > of course, the order by inside the table subquery is useless, so this
> > would become:
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> >  group by firm_id
> >having boo is not null) c
> >   on c.firm_id = f.id
> > order by boo desc;
> >
> > but perhaps you really mean:
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> > where by_or_on is not null
> >  group by firm_id) c
> >   on c.firm_id = f.id
> > order by boo desc;
> >
> > which is a somewhat different thing.
> >
> > ---
> > ()  ascii ribbon campaign against html e-mail
> > /\  www.asciiribbon.org
> >
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of e-mail mgbg25171
> > > Sent: Saturday, 02 February, 2013 16:19
> > > To: General Discussion of SQLite Database
> > > Subject: [sqlite] trying to exclude records which have a field that is
> > > null
> > >
> > > wSQL = _
> > > "Select f.* " & _
> > > "from firms f " & _
> > > "left join " & _
> > > "(" & _
> > > "select 

Re: [sqlite] I suspect not - but is the database separator configurable?

[Message Adams]

Thanks for everyone's help. Unfortunately, as Ryan highlighted, the 
double quotes around the table 'database..name' cannot be handled by 
sybase. It's a shame as I rather like Sqlite and works soo well with Python.


All the best,
Marc



On 02/02/13 07:56, Mohit Sindhwani wrote:

Hi Ryan,

On 2/2/2013 1:55 AM, Ryan Johnson wrote:
That would break sybase, though: the quotes would also tell it to 
treat the db name and periods as part of the table name, too:


sqlite3> create table foo(x,y);
sqlite3> .tables
foo
sqlite3> select * from "main.foo";
Error: no such table: main.foo


Since the OP (you aren't the OP, right?)  seems to have control over 
the code, I wonder if a compile-time macro could be used to attach the 
quotes around the database name only in the test environment. But, 
otherwise, I think we're at the end of what can be done with SQLite.


Best Regards,
Mohit.



___
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