Re: [sqlite] compile sqlite in Windows

[Cory Nelson]

you can build a .lib from the .def - please read your compilers
documentation.  (vc++ comes with lib.exe)

On 8/4/05, Bo Chen <[EMAIL PROTECTED]> wrote:
> 
> Hello there,
> 
> 
> I need sqlite .lib file and .dll file in Windows. I downloaded
> precompiled binaries for Windows. The package only contains a .dll file
> and a .def file. I also downloaded the source code. However, it does not
> include Makefile and other utility files. Does anyone have idea where I
> can get .lib file or Makefile in Windows. Thank you for your help.
> 
> Bo Chen
> 


-- 
Cory Nelson
http://www.int64.org

RE: [sqlite] compile sqlite in Windows

[Aaron Schneider]

There's a version of the source code download that is configured for
Windows.  Just add all the files to a DLL project and compile.  If you
don't use TCL (we don't), then just remove tclsqlite.c from the project.

Don't forget to define THREADSAFE in your preprocessor defines!

(Or you could learn the mysteries of LoadLibrary() and GetProcAddress()
to use the dll you already downloaded.)

(I just compiled the windows DLL version 2.8.16 last week for the first
time.)

Aaron

-Original Message-
From: Bo Chen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 04, 2005 6:01 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] compile sqlite in Windows



Hello there,


I need sqlite .lib file and .dll file in Windows. I downloaded 
precompiled binaries for Windows. The package only contains a .dll file 
and a .def file. I also downloaded the source code. However, it does not

include Makefile and other utility files. Does anyone have idea where I 
can get .lib file or Makefile in Windows. Thank you for your help.

Bo Chen 

[sqlite] compile sqlite in Windows

[Bo Chen]

Hello there,


I need sqlite .lib file and .dll file in Windows. I downloaded 
precompiled binaries for Windows. The package only contains a .dll file 
and a .def file. I also downloaded the source code. However, it does not 
include Makefile and other utility files. Does anyone have idea where I 
can get .lib file or Makefile in Windows. Thank you for your help.

Bo Chen 

[sqlite] Re: Limitation on no. of Indices in a table?

[R S]

Also the column is a tinyint..Guess that shouldnt matter..But just FYI...

On 8/4/05, R S <[EMAIL PROTECTED]> wrote:
> Hi,
>   I have a table with a large # of indices (almost as the # of
> columns) in the table. SQLite works beautifully with most constraints
> and magically takes a long time for a query with constraints on the
> column with its index last created. Any limitations on the max # of
> indices allowed for a table?
>

Re: [sqlite] ATTACH, query and differentiate

[Dennis Cote]

Andrew,

I though I should point out a couple of problem with my proposed case 
statement before you run into them. A case expression can only return a 
single value, so you can't return name and otherdata at the same time, 
you can only get one field (otherdata in this case). Also the subselects 
in the case expression must be enclosed in brackets. Finally you may 
want to alias the result of the case expression to give it a name that 
matches the data you are returning since the default name of an 
expression result column is the expression itself (which isn't really 
very useful in this case). Try this instead;


select 
   case :uid % 10
   when 1 then 
	(select otherdata from main.sometable where id = :uid / 10)

   when 2 then
   (select otherdata from database2.sometable where id = :uid / 10)
   end
as otherdata;

Dennis Cote

[sqlite] Limitation on no. of Indices in a table?

[R S]

Hi,
  I have a table with a large # of indices (almost as the # of
columns) in the table. SQLite works beautifully with most constraints
and magically takes a long time for a query with constraints on the
column with its index last created. Any limitations on the max # of
indices allowed for a table?

Re: [sqlite] Coverting databases - 2.8 to latest 3

[Ray Mosley]

Cool! When the same answers appear so quickly, it must be correct! Thanks to 
Donald & Christian.

On 8/4/05, Griggs, Donald <[EMAIL PROTECTED]> wrote: 
> 
> 
> Re: how do I convert databases?
> 
> --
> The command-line utility (for both versions) has a nice ".dump" command, 
> and
> you just feed sqlite3 on it's output. If you don't need to keep the .dump
> output file you can just pipe it:
> 
> 
> sqlite OLD.DB .dump | sqlite3 NEW.DB
> 
> 
> 
> 
> Donald Griggs
> 
> 
> Opinions are not necessarily those of Misys Healthcare Systems nor its 
> board
> of directors.
> 



-- 
Ray Mosley

RE: [sqlite] Coverting databases - 2.8 to latest 3

[Griggs, Donald]

Re:  how do I convert databases?

--
The command-line utility (for both versions) has a nice ".dump" command, and
you just feed sqlite3 on it's output.  If you don't need to keep the .dump
output file you can just pipe it:


sqlite OLD.DB .dump | sqlite3 NEW.DB




Donald Griggs


Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.

Re: [sqlite] International Language Support

[Jan-Eric Duden]

Hi Dan,

I think we need to know this here:

How do your SELECT statements look like?
What's the keyword your international clients use -  to find what data?
And what's the data in the table?


What might be already a help is:
" The LIKE operator is not case sensitive and will match upper case 
characters on one side against lower case characters on the other. (A 
bug: SQLite only understands upper/lower case for 7-bit Latin 
characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 
characters or UTF-8 characters. For example, the expression 
*'a' LIKE 'A'* is TRUE but *'æ' LIKE 'Æ'* is FALSE.). The infix LIKE 
operator is identical the user function like(/X/,/Y/) 
. "

(pasted from the sqlite docs).

Thus case insensitive searches will fail when they contain german 
umlauts and so on. That happens of course only if you don't use your own 
comparison operator.


Jan-Eric


Dan Wellisch wrote:


Austin:

Are you saying that 8859-1 encoding does not work with these 
international versions of MS Windows, so we would need
to ensure that we are putting UTF-8 chars in the data?  This would 
make sense if the OS uses UTF-8 chars. in the WHERE

clause so that it is searching against 8859-1 chars.
If the above understanding is correct, then I must make the inference 
that MS Windows for English uses 8859-1 chars in the WHERE clause of 
SQLlite; otherwise, the English version of MS Windows would not work 
either.  But, it does.




Can anyone else please chime in here and either agree or dispute what 
Austin is saying?  I would like some more feedback

before I start doing more investigation.

Thanks,

Dan
Austin Ziegler wrote:


On 8/4/05, Dan Wellisch <[EMAIL PROTECTED]> wrote:
 


We just put a SQLlite application in production. It handles the
display of ISO 8859-1characters just fine if they appear in the
search results.

However, users that are running German, French, etc. versions of
Microsoft Windows are complaining that search results are coming
back with 0 results whereas we know that these querys work when
the app. is running on the English version of MS Windows.

What do we do to fix this so this app works correctly on any
language version of OS? Furthermore, we also have a version of
this app. that runs under Mac OSX so I pose the same question
concerning that OS.
  



You may need to do some data massaging to make sure that it's *not*
ISO-8859-1 but rather convert UTF-8. This may require a couple of
round trips with WideCharToMultibyte and MultibyteToWideChar so that
you go from the OEM encoding (presuming that you're using char* as
your characters) to UTF-8 via Windows wide characters (which are
UCS-2, IIRC).

Alternatively, use something like Iconv to do this. The main problem
is (likely) that you're inserting data as if it were always ISO
8859-1, when it's not.

-austin
 

Re: [sqlite] International Language Support

[Austin Ziegler]

On 8/4/05, Dan Wellisch <[EMAIL PROTECTED]> wrote:
> Are you saying that 8859-1 encoding does not work with these
> international versions of MS Windows, so we would need to ensure
> that we are putting UTF-8 chars in the data? This would make sense
> if the OS uses UTF-8 chars. in the WHERE clause so that it is
> searching against 8859-1 chars.

What I'm saying is that unless you're explicitly doing something to
ensure that your input is 8859-1, you're getting something else. In
fact, on English Windows, you're probably not getting 8859-1,
either, you're getting Windows 1252 (I *think* that's the right
number), which is similar to, but not *quite* the same as 8859-1.

If you're just using char* (or std::string) and getting input from
Windows, then you're getting it in ANSI/OEM, most of the time, which
is most decidedly *not* 8859-1. It might be Windows 1252, but it's
not necessarily Windows 1252 on non-English versions of Windows.

If you're compiling with UNICODE and are using TCHAR*, you'll be
getting wchar* (or std::wstring), which is actually UCS-2 (related
in some way to UTF-16, but again not *quite* the same since UCS-2
doesn't support surrogates). This is better than ANSI/OEM, but not
by much because it causes other problems.

The real trick is that UTF-8 (which SQLite uses internally; I
haven't quite understood this part in the documentation, and haven't
yet needed to, because I don't think it does any auto-conversion for
you) and US ASCII (that is to say, the first 128 characters of ANSI)
map perfectly.

If it's something where you can test this, you might be able to
reproduce it by trying to use a high-bit character (e.g., c-cedilla
or something) in the English version to see if it searches right.

-austin
-- 
Austin Ziegler * [EMAIL PROTECTED]
   * Alternate: [EMAIL PROTECTED]

Re: [sqlite] Coverting databases - 2.8 to latest 3

[Christian Smith]

On Thu, 4 Aug 2005, Ray Mosley wrote:

>I started using 2.8 because I still have Tcl 8.3; If I convert to Tcl 8.4 to
>go to SQlite 3, how do I convert databases?


Use the sqlite/sqlite3 shells to dump then restore the database in SQL
statement format:
$ sqlite sqlite2db .dump | sqlite3 sqlite3db

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] International Language Support

[Dan Wellisch]

Austin:

Are you saying that 8859-1 encoding does not work with these 
international versions of MS Windows, so we would need
to ensure that we are putting UTF-8 chars in the data?  This would make 
sense if the OS uses UTF-8 chars. in the WHERE
clause so that it is searching against 8859-1 chars. 

If the above understanding is correct, then I must make the inference 
that MS Windows for English uses 8859-1 chars in the WHERE clause of 
SQLlite; otherwise, the English version of MS Windows would not work 
either.  But, it does.


Can anyone else please chime in here and either agree or dispute what 
Austin is saying?  I would like some more feedback

before I start doing more investigation.

Thanks,

Dan
Austin Ziegler wrote:


On 8/4/05, Dan Wellisch <[EMAIL PROTECTED]> wrote:
 


We just put a SQLlite application in production. It handles the
display of ISO 8859-1characters just fine if they appear in the
search results.

However, users that are running German, French, etc. versions of
Microsoft Windows are complaining that search results are coming
back with 0 results whereas we know that these querys work when
the app. is running on the English version of MS Windows.

What do we do to fix this so this app works correctly on any
language version of OS? Furthermore, we also have a version of
this app. that runs under Mac OSX so I pose the same question
concerning that OS.
   



You may need to do some data massaging to make sure that it's *not*
ISO-8859-1 but rather convert UTF-8. This may require a couple of
round trips with WideCharToMultibyte and MultibyteToWideChar so that
you go from the OEM encoding (presuming that you're using char* as
your characters) to UTF-8 via Windows wide characters (which are
UCS-2, IIRC).

Alternatively, use something like Iconv to do this. The main problem
is (likely) that you're inserting data as if it were always ISO
8859-1, when it's not.

-austin
 

[sqlite] Coverting databases - 2.8 to latest 3

[Ray Mosley]

I started using 2.8 because I still have Tcl 8.3; If I convert to Tcl 8.4 to 
go to SQlite 3, how do I convert databases?

-- 
Ray Mosley

Re: [sqlite] Documentation

[L. S.]

D. Richard Hipp wrote:

On Thu, 2005-08-04 at 12:59 -0700, L. S. wrote:

What is the prescribed way to create the complete documentation (without 
having to learn yet another scripting language--or whatever it is)?





make doc

Thanks!

Re: [sqlite] International Language Support

[Austin Ziegler]

On 8/4/05, Dan Wellisch <[EMAIL PROTECTED]> wrote:
> We just put a SQLlite application in production. It handles the
> display of ISO 8859-1characters just fine if they appear in the
> search results.
> 
> However, users that are running German, French, etc. versions of
> Microsoft Windows are complaining that search results are coming
> back with 0 results whereas we know that these querys work when
> the app. is running on the English version of MS Windows.
> 
> What do we do to fix this so this app works correctly on any
> language version of OS? Furthermore, we also have a version of
> this app. that runs under Mac OSX so I pose the same question
> concerning that OS.

You may need to do some data massaging to make sure that it's *not*
ISO-8859-1 but rather convert UTF-8. This may require a couple of
round trips with WideCharToMultibyte and MultibyteToWideChar so that
you go from the OEM encoding (presuming that you're using char* as
your characters) to UTF-8 via Windows wide characters (which are
UCS-2, IIRC).

Alternatively, use something like Iconv to do this. The main problem
is (likely) that you're inserting data as if it were always ISO
8859-1, when it's not.

-austin
-- 
Austin Ziegler * [EMAIL PROTECTED]
   * Alternate: [EMAIL PROTECTED]

[sqlite] Calling sqlite3RunParser directly to just *parse* SQL code

[scunacc]

Dear all,

I have a "meta" application that's a bit like the sqlite shell (except
it's not interactive) in which people write statements that are parsed
by Perl that contain SQL statements that are passed *unparsed* by Perl
to SQLite via DBD::SQLite.

Some of the queries are long.

To save time, users would like to be able to parse the SQL in the meta
app before actually running the meta app. Clearly since this is going to
run through SQLite, it'd be nice if the actual SQLite parser could parse
it 1st. (I looked at SQL::Statement::Structure and wasn't happy with it
- broke on things that sqlite accepts - and I didn't want to have to
rewrite the grammar to append sqlite-OK items)

So, having looked at the source code for the DBD driver and sqlite3, it
would seem that the appropriate function to call would be
sqlite3RunParser.

Is there any extant code that would show this being used more or less
"by itself" in an application, (i.e. w/o the heavy weight of the rest of
the sqlite code scaffolding)? C code would be fine (and I'd call it as
an external app 1st of all and maybe integrate later) - though I'd like
to really have this as a Perl module if possible.

(I wanted to avoid the trial and error of figuring out which headers and
what linkage and which data structures I would need to establish to
minimally call this function I guess.)

Any help appreciated.

Thanks!

Kind regards

Derek Jones.

BTW: Richard, if you read this - I still have the core dump problem from
the other thread... :-( The good news is I've been coding the Perl & SQL
around it to optimize which has been a useful exercise in its own right.

[sqlite] International Language Support

[Dan Wellisch]

Hello:

We just put a SQLlite application in production.  It handles the display 
of ISO 8859-1characters just fine if they appear in the search

results.

However, users that are running German, French, etc. versions of 
Microsoft Windows are complaining that search results are coming
back with 0 results whereas we know that these querys work when the app. 
is running on the English version of MS Windows.


What do we do to fix this so this app works correctly on any language 
version of OS?  Furthermore, we also have a version
of this app. that runs under Mac OSX so I pose the same question 
concerning that OS.


Thanks,

Dan

Re: [sqlite] Documentation

[D. Richard Hipp]

On Thu, 2005-08-04 at 12:59 -0700, L. S. wrote:
> What is the prescribed way to create the complete documentation (without 
> having to learn yet another scripting language--or whatever it is)?
> 

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

Re: [sqlite] Documentation

[L. S.]

Ralf Junker wrote:

Hello L. S.,

you can create a HTML version by running the TCL scripts which are part of the 
tarball sources.


And how do I do that? I found enough info about TCL to do...
'tclsh foo.tcl > foo.html'
but that fails on some files.

What is the prescribed way to create the complete documentation (without 
having to learn yet another scripting language--or whatever it is)?

Re: [sqlite] ATTACH, query and differentiate

[Dennis Cote]

Andrew Gatt wrote:


Donald,

Thanks for your reply, its all a bit new to me, so i'm still trying to get my 
head round it and hopefully be able to explain myself better.

I'm actually creating the unique ids, it is just a number.

I now think my understanding of an attached database is wrong, my first 
thoughts were that a single SELECT command would query both databases, but now 
i believe i would have to use the following syntax to do a query on both 
databases:

SELECT id, name FROM database1.main, database2.main WHERE name == %me% ORDER BY 
name

 


Which would produce alphabetically sorted results from both attached tables? 
But because both the databases will have been created separately, they could 
have overlapping ids, which i need to avoid.

To avoid overlapping ids i could manually get all the ids from database1 and 
(for arguments sake) add a '1' to the end and all the ids from database2 and 
add a '2' to the end. If the client then requested further information on id 
6731, i would know it was from database 1, strip that digit out and so get 
further information from id 673 database1. Doing it this way however means i 
have to alphabetically sort the results from the two tables, after the two 
queries.

So i guess i was just wondering if this was all possible in a single query, or 
sqlite gave some indication of where the results were coming from so i could 
tag them for id mangling.

I hope i've managed to explain myself better and not made it even worse!

Thanks
Andrew
 


Andrew,

I think what you are after is more like this;

select id * 10 + 1 as uid, name from main.sometable where name like '%me%'
union
select id * 10 + 2 as uid, name from database2.sometable where name like 
'%me%'

order by name

This will combine the results of the two queries, one on the sometable 
of each database, and adjust the returned id so that the correct 
database can be determined from the uid (by using the modulus operator 
to get the database number, ie. uid % 10 is the database). This should 
work as long as your id values are small enough.


To return other data from a record specified by this uid value you can 
use a case statement like this;


select case :uid % 10
   when 1 then
  select name, otherdata from main.sometable where id = :uid / 10
   when 2 then
  select name, otherdata from database2.sometable where id = :uid / 10
   end;

You can then bind the :uid value (or generate the sql string on the fly) 
and get the requested data from the correct database.


HTH
Dennis Cote

[sqlite] Congratulations to the Integrator!

[Jeremy Hinegardner]

I would just like to offer my congratulations to D. Richard Hipp for
receiving the Open Source Integrator Award for SQLite.

http://osdir.com/Article6677.phtml

Your work is appreciated.

enjoy,

-jeremy

-- 

 Jeremy Hinegardner  [EMAIL PROTECTED] 

Re: [sqlite] Slow Queries, FROM order and indexes

[Dennis Cote]

Aaron Schneider wrote:


Hello All,

I've using SQLite 2.8.16 with a music management application, and I'm
trying out why certain queries take a long time and to figure out how
SQLite uses my indexes.  I've got a master "media" table with a couple
of auxiliary tables like "artists", "albums",  and "genres".  Each
table's primary key is id (mediaid, artistid, albumid, and genreid),
and the media table contains columns to each of the side tables' primary
key (and an index for each one, too).

I found that a particular query  takes forever when run on a particular
database:
SELECT DISTINCT albums.albumid, albums.name_lower FROM artists, albums,
media WHERE albums.media_count > 0 AND media.albumid=albums.albumid AND
media.artistid=artists.artistid ORDER BY artists.name_key,
albums.name_key;

 

In this query sqlite does a table scan through the first table, artists, 
to select the records. This table is joined to the media table by the 
third condition. I suspect that you do not have an index on the artistid 
column in the media table, so sqlite must do a full table scan of the 
media table for each record in the artists table (this is what is 
slowing it down). This generates a set of virtual records that is the 
size of the artists table multiplied by the size of the media table. 
Each of these virtual records are then joined to the albums table using 
the second condition. In this case I believe that the albumid field is 
the key for the albums table, so sqlite can go directly to each matching 
record in the albums table. Finally it checks the first condition for 
each of these extended records to see if it should be selected. All the 
matching records are collected and then sorted before being returned to 
you.


The lack of an index on the media table artistid field forces many scans 
of your largest table.



Well, the largest table (by far) in these queries is always the media
table, and I found that by moving the media table to the beginning of
the FROM list, it runs almost instantly:
SELECT DISTINCT albums.albumid, albums.name_lower FROM media, artists,
albums WHERE albums.media_count > 0 AND media.albumid=albums.albumid AND
media.artistid=artists.artistid ORDER BY artists.name_key,
albums.name_key
 




Thus, when "media" is first, SQLite scans each record in the media table
and tests the smaller tables with the NotExists command which uses the
primary key to locate the record immediately.
 

Yes, I believe this is correct. This requires only one table scan of the 
media table thought.




I've been reading up on the virtual machine, op-codes, and performance
tips in the wiki for the past few days, and I've got a few questions
about what was happening:

1)  Shouldn't it be faster to iterate through one of the smaller tables
and then use an index on the media table to join with the other small
table?   
 


Yes, see above. You just need the correct index.



2)  It's unclear to me what order my FROM tables should be in.  In the
first query, did I accidently choose the absolute worst order for the
FROM list?  
 

Yes, I think you did. If you had used the albums table first, it would 
have been able to apply the first condition to each record as it scanned 
that table. This would have eliminated some records immediately. It 
would have then done a slow join by scanning the media table to find 
matching albumids (assuming there is no index on the albumid field in 
the media table). This would have been just as bad as the first case. 
The relative time would depend on the size of the artists table vs the 
number of records in the albums table that meet the first criterion. 
Again this can be corrected by creating the required index.




So the tables in the FROM clause should appear in the order that tables
are introduced, with special consideration to the first table in the
list be the primary value we are SELECTing?  In the query above, that
would mean "FROM albums, media, artists".  (Both "FROM media, albums,
artists" and "FROM albums, media, artists" run too fast for eye-ball
speed compare.)

3)  What role, if any, do the ORDER BY columns play in the FROM order?
(I would assume none since by the time you've selected a row, you have
all of the data for the sorting of that row.)
 

I believe sqlite will optimize the order by clause if it can use an 
index to scan the first table in the the order that is required. If not 
it just does a table scan through the first table and then sorts the 
collected records. In your case, you are ordering by columns from two 
tables, so you can't create an index, and it can't be optimized.




4)  Is there a query optimizer for sqlite?  A program that automagically
discovered which queries were not in optimal form that could propose a
different order or alternate values for certain terms in FROM and WHERE
clauses?  I'm thinking that the input would be a query and an already
existing database (with indexes), and the output would be a (better)
optimized query.  This 

Re: [sqlite] how to get notice when database is free after it was busy

[Jay Sprenkle]

On 8/3/05, Walter Meerschaert <[EMAIL PROTECTED]> wrote:
> As a courtesy to my users, I wish to pop up a message thingy telling the user 
> when the program is waiting for the database to not be busy. Right now I 
> register a busy handler, and so I know how long I have been waiting, because 
> we have the count parameter, when it is 1, I look at the clock, when the 
> clock goes past 3 seconds or so it is time to tell the user tio cool his 
> heels while we wait for some process to commit or rollback. As far as I can 
> tell, there is no direct way to know when the database has begun responding 
> again. The busy handler simply stops being called.
> 
> Is there a direct way I fail to see? Or, perhaps there could be an addition 
> to the api wherein we are told when the buy state is no more. Perhaps we 
> could register a no_longer_busy_handler, which could take the same parameters 
> as the busy handler but pass in -1 for the count.

keep trying to obtain a lock? You can set the wait time to a short value
and just repeat until the user cancels or it works.

Re: [sqlite] ATTACH, query and differentiate

[Jay Sprenkle]

> I now want to be able to give the option of opening a second database file, 
> with the same table structure - which i believe is acheived using the ATTACH 
> command. However the ids sent to the client still need to be unique, it is my 
> understanding that a SELECT command will treat both databases as one and so 
> return potentially overlapping ids. Is there anyway to craft the SELECT 
> command - or otherwise -  so that i can know where the id came from and do 
> some form of id mangling, keeping in mind i will need to do the same process 
> in reverse to access the information, given the mangled id.


If you attach the second database a join will let you connects rows in
one table with rows in another table. Then a select can return 2
columns, the id from each table. The two id's concatenated will create
a simple unique key that can be used to find rows in either table.

example:
two tables, named 'a' and 'b'
select a.id, b.id
 from a
 inner join b on b.something = a.something

result:
a.idb.id
23   34

create a single key by using 6 digits (or binary if you understand
that) for each:

2334

There's your unique key that can be used to find rows in either or both tables.

RE: [sqlite] ATTACH, query and differentiate

[Andrew Gatt]

Donald,

Thanks for your reply, its all a bit new to me, so i'm still trying to get my 
head round it and hopefully be able to explain myself better.

I'm actually creating the unique ids, it is just a number.

I now think my understanding of an attached database is wrong, my first 
thoughts were that a single SELECT command would query both databases, but now 
i believe i would have to use the following syntax to do a query on both 
databases:

SELECT id, name FROM database1.main, database2.main WHERE name == %me% ORDER BY 
name

Which would produce alphabetically sorted results from both attached tables? 
But because both the databases will have been created separately, they could 
have overlapping ids, which i need to avoid.

To avoid overlapping ids i could manually get all the ids from database1 and 
(for arguments sake) add a '1' to the end and all the ids from database2 and 
add a '2' to the end. If the client then requested further information on id 
6731, i would know it was from database 1, strip that digit out and so get 
further information from id 673 database1. Doing it this way however means i 
have to alphabetically sort the results from the two tables, after the two 
queries.

So i guess i was just wondering if this was all possible in a single query, or 
sqlite gave some indication of where the results were coming from so i could 
tag them for id mangling.

I hope i've managed to explain myself better and not made it even worse!

Thanks
Andrew

-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED]
Sent: 04 August 2005 13:56
To: 'sqlite-users@sqlite.org'
Subject: RE: [sqlite] ATTACH, query and differentiate


Hi Andrew,

Perhaps I shouldn't be replying since I don't think I'm understanding your
question fully.

When you wrote: 
   "with each entry having a unique id"
did you mean that you are creating a unique identifier, or instead that you
are making  use of the ROWID within sqlite?

Regarding:
   "it is my understanding that a SELECT command will treat both databases
as one and so return potentially overlapping ids"

Can you elaborate on that understanding?   Sqlite 3 can create transactions
that are atomic across multiple databases (if the main database is not an
in-memory one), but I'm not sure what "treat as ... one" would otherwise
mean.

Even for a single database, ROWID values are unique ONLY within a particular
table.  Therefore (to my understanding) if you call out a particular
database, a particular table, and a particular ROWID, then you've uniquely
identified a row.

Dan Kennedy just referred to the ATTACH documentation which may be helpful:
http://www.sqlite.org/lang_attach.html


Donald Griggs


Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.



-Original Message-
From: Andrew Gatt [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 04, 2005 6:56 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] ATTACH, query and differentiate

Re: [sqlite] Incomplete SQL on SELECT

[Boris Herbinière-Sève]

how do you tell sqlite the request? Maybe it's just the terminating
semicolon that's missing:


Hum... Ok, I'm a bit ashamed right now... I guess this is expected from 
too much MySQL bad habits... Thanks anyway, it works.


Did any of you use sqlite with zeoslib ? I'm experiencing some 
unexpected slowdowns with SELECTs...


--
Boris Herbinière-Sève
Technical Support

[sqlite] error when running dgSQLite3

[Giorgio Bocchi]

hi all

I am new to sqlite and to tcl as well. I have tried to install dgSQLite3 and it 
returns the following error

---
Error in startup script
---
wrong # args: should be "load fileName ?packageName? ?interp?"
while executing
"load C:/Documents\ and Settings/All Users/Application 
Data/SQL/dgSQLite3.exe/lib/tclsqlite3/Windows/libtclsqlite3.dll sqlite3 "
("package ifneeded" script)
invoked from within
"package require sqlite"
(procedure "packages" line 13)
invoked from within
"packages"
(procedure "app::main" line 12)
invoked from within
"app::main"
(file "C:/Documents and Settings/All Users/Application 
Data/SQL/dgSQLite3.exe/lib/app-dgSQLite/dgSQLite.tcl" line 1993)
invoked from within
"source {C:/Documents and Settings/All Users/Application 
Data/SQL/dgSQLite3.exe/lib/app-dgSQLite/dgSQLite.tcl}"
("package ifneeded" script)
invoked from within
"package require app-dgSQLite"
(file "C:/Documents and Settings/All Users/Application 
Data/SQL/dgSQLite3.exe/main.tcl" line 3)
---
OK
---

if anybody can help out it will be really v much appreciated

thanks

Giorgio


--

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received this e-mail in error) please 
notify the sender immediately and destroy this e-mail. Any unauthorized 
copying, disclosure or distribution of the material in this e-mail is strictly 
forbidden.

Re: [sqlite] Qn on Transaction across 2 DBs

[Dan Kennedy]

--- R S <[EMAIL PROTECTED]> wrote:

> Hi,
>I have a requirement where I need to update 2 tables in 2 separate
> DBs within a transaction. Do I need to attach the second table to the
> first at the start of the transaction? As I understand SQLite commits
> a transaction across a single DB only?
> Thanks!
> 

Right, you need to attach the second database using the ATTACH command.
If you are using version 3 and your original database is not an 
in-memory database, then the transaction involving two databases will
be atomic.

http://www.sqlite.org/lang_attach.html




Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

[sqlite] ATTACH, query and differentiate

[Andrew Gatt]

Hi,

I'm not sure if what i am trying to acheive is possible, but if i explain what 
i'm trying to do someone may be able to point me in the right direction.

I have a database table that holds some information, with each entry having a 
unique id. So when i query the database i can get a list of ids to pass around. 
This is a server program on a network and so passing integer ids to clients 
keeps things simple.

I now want to be able to give the option of opening a second database file, 
with the same table structure - which i believe is acheived using the ATTACH 
command. However the ids sent to the client still need to be unique, it is my 
understanding that a SELECT command will treat both databases as one and so 
return potentially overlapping ids. Is there anyway to craft the SELECT command 
- or otherwise -  so that i can know where the id came from and do some form of 
id mangling, keeping in mind i will need to do the same process in reverse to 
access the information, given the mangled id.

Any ideas?
Thanks
Andrew

Re: [sqlite] Incomplete SQL on SELECT

[Jakob Hirsch]

Boris Herbinière-Sève wrote:

> Incomplete SQL: SELECT r.Id, r.IdParams, r.SagN2, r.SagL2, r.SagN3,

how do you tell sqlite the request? Maybe it's just the terminating
semicolon that's missing:

> $ echo "select * from t1 as tab where tab.domain='dom1'" | sqlite3 test.db
> Incomplete SQL: select * from t1 as tab where tab.domain='dom1'
> $ echo "select * from t1 as tab where tab.domain='dom1';" | sqlite3 test.db
> dom1|jakob
> $ sqlite3 test.db "select * from t1 as tab where tab.domain='dom1'"
> dom1|jakob

[sqlite] Incomplete SQL on SELECT

[Boris Herbinière-Sève]

Hello,

I try to use SQLite (2.8) for a project, and ran into (what seems to be) 
a little problem.


A MySQL-valid SELECT request only output 'Incomplete SQL' with SQLite. I 
guess my SQL isn't standard enough, but I can't find what's wrong.


Incomplete SQL: SELECT r.Id, r.IdParams, r.SagN2, r.SagL2, r.SagN3, 
r.SagL3, r.SagN4, r.SagL4, r.SagN5, r.SagL5 FROM results AS r, params AS 
p WHERE r.IdParams = p.Id  AND p.GarId = 0 AND Inc > 29. AND Inc < 
30.0001 AND Gdi > -5.0001 AND Gdi < -4. AND Gda > -5.0001 AND Gda < 
-4. AND Mud > 1.5999 AND Mud < 1.6001 AND Fric > 0.1999 AND Fric < 
0.2001 AND Ovg > 0.2499 AND Ovg < 0.2501 AND Mwd > 2. AND Mwd < 
3.0001 LIMIT 1


Any ideas ?
Thanks

--
Boris Herbinière-Sève
Technical Support