Re: [sqlite] Multiple inner join confusion

[Rob Duncan]

Thanks to Richard for responding—I will do as he suggests.  I am still 
curious about the cause of the error, though.  After sleeping on it my 
hypothesis is that the temporary table generated by the first of my 
inner joins has columns with names "aa.i" and "aa.j", so the using 
clause of the second inner join fails (because it requires the 
existence of a column named "j").  Am I on the right track?

Secondly, does the select suggested below do the full cartesian product 
of the tables before extracting the rows that match the condition?  If 
the tables are large do I get better performance by explicitly using a 
series of joins instead?  In that case would it be better to code it 
something like this:

select * from aa inner join bb on (aa.i=bb.i) inner join cc on 
(aa.j=cc.j);

Thirdly, what is the explanation for the different results from these 
similar selects?

select * from aa inner join bb on (aa.i=bb.i);
aa.i  aa.j  bb.i
    --
1 2 1
5 6 5
select * from aa inner join bb using (i);
aa.i  aa.j
  
1 2
5 6
Thanks,

Rob.

On Apr 12, 2004, at 5:00 AM, D. Richard Hipp wrote:

Rob Duncan wrote:
I'm a novice SQL user, and I'm confused by an apparently arbitrary 
limitation on multiple inner joins. select * from aa inner join bb 
using (i) inner join cc using (j);
SQL error: cannot join using column j - column not present in both 
tables
I suggest you work around the problem by coding the select like this:

   select * from aa, bb, cc where aa.i=bb.i and cc.j=aa.j;


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Multiple inner join confusion

[Darren Duncan]

At 11:53 PM +0100 4/12/04, Cronos wrote:
I am also having difficulty getting to grips with the (awkward) join
notation. In 3.0 how about support for Oracle type joins with (+) which
seems far simpler to me :)
I oppose this.

Oracle's "(+)" was a nasty hack and I find it more difficult to 
understand than the "from a left join b on b.foo = a.foo" syntax.

The latter syntax is what most databases support, and it is what the 
ANSI/ISO SQL standard supports.  If you want to be adding support for 
anything, it should be in the form that the SQL standards define.

And Oracle themselves have even seen the light and support that 
standard syntax in Oracle 9 and later (though not in 8 or earlier, 
unfortunately).

-- Darren Duncan

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Multiple inner join confusion

[Cronos]

I am also having difficulty getting to grips with the (awkward) join
notation. In 3.0 how about support for Oracle type joins with (+) which
seems far simpler to me :)

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: 12 April 2004 13:00
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] Multiple inner join confusion


Rob Duncan wrote:
> I'm a novice SQL user, and I'm confused by an apparently arbitrary
> limitation on multiple inner joins.
>
> select * from aa inner join bb using (i) inner join cc using (j);
> SQL error: cannot join using column j - column not present in both tables
>

I suggest you work around the problem by coding the select like this:

select * from aa, bb, cc where aa.i=bb.i and cc.j=aa.j;


--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] PRAGMA empty_result_callbacks

[Aaron Patterson]

aducom wrote:

We thought that this was a problem too, but investigating we found out 
that in version 12 the execution of the pragma did not trigger the 
callback, but version 13 does. Which means that you have to ignore this 
first trigger, since it contains no data. 

Albert Drent
aducom software
 

Thats interesting.  Is it supposed to work that way?  That seems like it 
wouldn't be very backwards compatable.

Thanks for the info!

--Aaron

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] PRAGMA empty_result_callbacks

[aducom]

We thought that this was a problem too, but investigating we found out 
that in version 12 the execution of the pragma did not trigger the 
callback, but version 13 does. Which means that you have to ignore this 
first trigger, since it contains no data. 

Albert Drent
aducom software

> Hi, I just upgraded from 2.8.12 to 2.8.13 via RPM.  I was wondering 
if 
> anyone else noticed that PRAGMA empty_result_callbacks seems to break 
> stuff now.  Whenever I execute a statement with that pragma, my 
callback 
> function can't get any column name information.  Do I need to update 
my 
> code for the .13 release?  I downgraded back to the .12 release, and 
> everything works fine.
> 
> Here is the query I used which works fine on 2.8.12, but doesn't work 
on 
> 2.8.13:
> 
> PRAGMA empty_result_callbacks = ON; select * from test
> 
> Any help I can get would be greatly appreciated!
> 
> --Aaron
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> 
> 
> 

-- 


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] PRAGMA empty_result_callbacks

[Aaron Patterson]

Hi, I just upgraded from 2.8.12 to 2.8.13 via RPM.  I was wondering if 
anyone else noticed that PRAGMA empty_result_callbacks seems to break 
stuff now.  Whenever I execute a statement with that pragma, my callback 
function can't get any column name information.  Do I need to update my 
code for the .13 release?  I downgraded back to the .12 release, and 
everything works fine.

Here is the query I used which works fine on 2.8.12, but doesn't work on 
2.8.13:

PRAGMA empty_result_callbacks = ON; select * from test

Any help I can get would be greatly appreciated!

--Aaron

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Using SQLite with WIN32 (VB6) with Oledb (No odbc)

[ISA Programmi]

Hallo,

is it possible to integrate a SQLite db in a
VB6 program, without using ODBC, but
working with OLEDB (faster)?

Thank you,

Giuliano



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] A proposal for SQLite version 3.0

[Mark D. Anderson]

Echoing some others' responses, particularly Darren's:

1. I don't see the rationale for putting much priority on 
multiple string encodings. After all, blobs still can't
be stored natively :).

UTF-16 adds extra complexity, because of embedded nulls,
and because of its own need for a byte-order-mark (BOM).
Furthermore, it is not a fixed width encoding.
(Java, and Windows prior to win2k, behaved as it if it was
fixed length, by neglecting surrogate pairs, but they are
just broken.)

UTF-8 is not a fixed width encoding either, but it does
not have embedded nulls, and it is supported "natively" by
practically all scripting languages.

The wchar_t issue is a pain; on Windows it is typically
a 16-bit type, and on Unix it is typically 32-bit, and in
either case you still don't know whether it is UCS-2, UTF-16,
or (on unix) UCS-4.
But using a char* declaration for a string that can contain
embedded nulls is an invitation to rampant bugs, IMHO.

2. I would be very interested to hear more about better
concurrency support, particularly along the lines of
the HUT HiBase/Shades ideas.
Doug Currie has written up some ideas about this in
cvstrac ("BlueSky"), but I'd also urge people to read
the HUT papers. At least K. Oksanen's publications are
still online at http://hibase.cs.hut.fi/publications.shtml

3. I'm unsure what is driving a desire for variable typing
among the values of a single column. Is this some deep-seated
distaste for the relational model :).

-mda

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] long filenames on Mac OS

[James W. Walker]

Will Leshner <[EMAIL PROTECTED]> wrote:

My guess is that very few people care about this problem, but on the
"Classic" versions of Mac OS (basically 8 and 9), you can't have
file names longer than 31 characters. This becomes a problem when
you have a database file name that is very long, but not longer than
31 characters. When you try to update such a database file, SQLite
needs to create a journal file and it does that by creating a new
file with the name of the database file plus "-journal", which can
end up exceeding the 31 character limit. In that case, the journal
file fails to be created and the entire update procedure fails as
well. I don't know what the solution to this is. One idea I had was
to conditionally shorten the journal file's suffix to simply "-j".
I ran into that problem too, and changed "journal" to "jrnl". 
Haven't had any problem with it.

Note that in certain cases, SQLite also adds 20 random characters to 
the file name.  I changed it to use 10.

By the way, it is possible that you can have this problem on Mac OS 
X, if someone has a hard drive formatted with the old "Mac OS 
Standard" file system.
--
  James W. Walker, ScriptPerfection Enterprises, Inc.
  

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] long filenames on Mac OS

[Williams, Kenneth (Ken) (TLR Corp)]

> -Original Message-
> From: Bernie Cosell [mailto:[EMAIL PROTECTED]
> Sent: Monday, April 12, 2004 12:15 PM
> To: Forum SQLite
> Subject: RE: [sqlite] long filenames on Mac OS
> 
> I don't understand -- why is whatever algorithm a program uses for 
> generating a private temporary file a "penalty" no matter how 
> the file 
> name works out?  Even "sqlite001", "sqlite002"...etc' would 
> suffice for 
> all I care; why do you care what name sqlite uses for its 
> journal files?

Because if "ReallyLongDatabaseNameNumber1" and "ReallyLongDatabaseNameNumber2" both 
map to the same journal file, I've got a problem.

 -Ken

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] long filenames on Mac OS

[Bernie Cosell]

On 12 Apr 2004 at 11:56, Williams, Kenneth (Ken) (TLR Corp) wrote:


> > -Original Message-
> > From: Ralph Wetzel [mailto:[EMAIL PROTECTED]

> > As only sqlite needs to know, how a journal is named, how about always
> > truncating the original filename so that it fits (with the
> > concetanated -j* ) into whatever length is ok for all 
> > supported systems?
> 
> I'd prefer that it only did that on systems where filename length
 ... is a problem, i.e. that the max length is #define'd per-system.  No
 ... need to penalize people whose OSs aren't so draconian.

I don't understand -- why is whatever algorithm a program uses for 
generating a private temporary file a "penalty" no matter how the file 
name works out?  Even "sqlite001", "sqlite002"...etc' would suffice for 
all I care; why do you care what name sqlite uses for its journal files?

   /Bernie\

-- 
Bernie Cosell Fantasy Farm Fibers
mailto:[EMAIL PROTECTED] Pearisburg, VA
-->  Too many people, too few sheep  <--   




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] long filenames on Mac OS

[Darren Duncan]

I suggest an alternate solution for this problem which should be more 
elegant, and is already used by Apple when copying dual-fork files to a 
single-fork file system.

What you could do is create a subdirectory within the directory having the 
SQLite "main" data file that has a constant name (probably determined in 
os.h) like ".SQLite-journal".  The journal file would then go into this 
subdirectory and have the same name as the main file.  So we have:

Normal way:
1 - ./mydatafile
2 - ./mydatafile-journal

Altered way:
1 - ./mydatafile
2 - ./.SQLite-journal/mydatafile

This solution would also work on file systems that are limited to 255 
character names, or 8 character names, or whatever.  The old Mac OS isn't 
the only one with a limit.

Also, if future versions of SQLite wanted to add more temp files, they 
could add a new directory for each on less capable systems, as they would 
add a new suffix on more capable ones.

-- Darren Duncan


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] long filenames on Mac OS

[Williams, Kenneth (Ken) (TLR Corp)]

> -Original Message-
> From: Ralph Wetzel [mailto:[EMAIL PROTECTED]
> Sent: Monday, April 12, 2004 11:52 AM
> To: Will Leshner
> Cc: Forum SQLite
> Subject: Re: [sqlite] long filenames on Mac OS
> 
> 
> Hi!
> 
> Just my 5 ct:
> 
> As only sqlite needs to know, how a journal is named, how about always
> truncating the original filename so that it fits (with the
> concetanated -j* ) into whatever length is ok for all 
> supported systems?

I'd prefer that it only did that on systems where filename length is a problem, i.e. 
that the max length is #define'd per-system.  No need to penalize people whose OSs 
aren't so draconian.

 -Ken


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] long filenames on Mac OS

[Ralph Wetzel]

Hi!

Just my 5 ct:

As only sqlite needs to know, how a journal is named, how about always
truncating the original filename so that it fits (with the
concetanated -j* ) into whatever length is ok for all supported systems?

Greetings, R. Wetzel

- Original Message - 
From: "Will Leshner" <[EMAIL PROTECTED]>
To: "Forum SQLite" <[EMAIL PROTECTED]>
Sent: Monday, April 12, 2004 5:43 PM
Subject: [sqlite] long filenames on Mac OS


> My guess is that very few people care about this problem, but on the
> "Classic" versions of Mac OS (basically 8 and 9), you can't have file
> names longer than 31 characters. This becomes a problem when you have a
> database file name that is very long, but not longer than 31
> characters. When you try to update such a database file, SQLite needs
> to create a journal file and it does that by creating a new file with
> the name of the database file plus "-journal", which can end up
> exceeding the 31 character limit. In that case, the journal file fails
> to be created and the entire update procedure fails as well. I don't
> know what the solution to this is. One idea I had was to conditionally
> shorten the journal file's suffix to simply "-j". That doesn't really
> solve the problem, but it might make things a little better. Would such
> a change be dangerous? Is there some chance that this would interact
> badly with another version of SQLite that hadn't made such a change?
>
> Thanks for any advice.
>
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] long filenames on Mac OS

[Will Leshner]

My guess is that very few people care about this problem, but on the 
"Classic" versions of Mac OS (basically 8 and 9), you can't have file 
names longer than 31 characters. This becomes a problem when you have a 
database file name that is very long, but not longer than 31 
characters. When you try to update such a database file, SQLite needs 
to create a journal file and it does that by creating a new file with 
the name of the database file plus "-journal", which can end up 
exceeding the 31 character limit. In that case, the journal file fails 
to be created and the entire update procedure fails as well. I don't 
know what the solution to this is. One idea I had was to conditionally 
shorten the journal file's suffix to simply "-j". That doesn't really 
solve the problem, but it might make things a little better. Would such 
a change be dangerous? Is there some chance that this would interact 
badly with another version of SQLite that hadn't made such a change?

Thanks for any advice.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] user id select

[Lloyd thomas]

Thanks Ken,
You make it look so simple.
Lloyd
- Original Message - 
From: "Williams, Kenneth (Ken) (TLR Corp)" <[EMAIL PROTECTED]>
To: "Lloyd thomas" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Monday, April 12, 2004 3:50 PM
Subject: RE: [sqlite] user id select




> -Original Message-
> From: Lloyd thomas [mailto:[EMAIL PROTECTED]
> Sent: Sunday, April 11, 2004 1:18 PM
> To: [EMAIL PROTECTED]
> Subject: [sqlite] user id select
>
>
> I am trying to create a query which selects a user where it
> is not part of a
> specified group.

Perhaps you just want:

 SELECT user_id FROM users
 WHERE user_id NOT IN
  (SELECT user_id FROM grp_user WHERE group_id = 42)

 -Ken




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] user id select

[Williams, Kenneth (Ken) (TLR Corp)]

> -Original Message-
> From: Lloyd thomas [mailto:[EMAIL PROTECTED]
> Sent: Sunday, April 11, 2004 1:18 PM
> To: [EMAIL PROTECTED]
> Subject: [sqlite] user id select
> 
> 
> I am trying to create a query which selects a user where it 
> is not part of a
> specified group. 

Perhaps you just want:

 SELECT user_id FROM users
 WHERE user_id NOT IN
  (SELECT user_id FROM grp_user WHERE group_id = 42)

 -Ken

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Multiple inner join confusion

[D. Richard Hipp]

Rob Duncan wrote:
I'm a novice SQL user, and I'm confused by an apparently arbitrary 
limitation on multiple inner joins. 

select * from aa inner join bb using (i) inner join cc using (j);
SQL error: cannot join using column j - column not present in both tables
I suggest you work around the problem by coding the select like this:

   select * from aa, bb, cc where aa.i=bb.i and cc.j=aa.j;

--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]