Re: [sqlite] retrieving data from multiple tables

[T]

Hi Kirrthana,

I have created a database with four tables,i have to search and  
retrieve
data from all the four tables based on the entry i get from the  
previous

table and display all the entries,could anybody tell how to do it.


I'm not sure what you mean. If you mean you have four tables with  
similar fields, and you want to SELECT data from all of them at once,  
then you can create a dynamic compound table that will contain all  
the data, and SELECT from that. eg:


CREATE VIEW MyTables
   AS
 SELECT * FROM MyTable1
  UNION ALL
 SELECT * FROM MyTable2
  UNION ALL
 SELECT * FROM MyTable3
  UNION ALL
 SELECT * FROM MyTable4;

SELECT * FROM MyTables WHERE ;

Or, if you mean that the result of SELECTing in one table becomes the  
basis of the SELECT in the next, that is something like the sequence:


value1 = SELECT ResultField1 FROM MyTable1 WHERE SearchField1 =  
;


value2 = SELECT ResultField2 FROM MyTable2 WHERE SearchField2 =  
ResultField1;


value3 = SELECT ResultField3 FROM MyTable3 WHERE SearchField3 =  
ResultField2;


value4 = SELECT ResultField4 FROM MyTable4 WHERE SearchField4 =  
ResultField3;


Then I think you could do it in one action by:

SELECT ResultField4
   FROM
 MyTable1
  LEFT JOIN
 MyTable2
ON SearchField2 = ResultField1
  LEFT JOIN
 MyTable3
ON SearchField3 = ResultField2
  LEFT JOIN
 MyTable4
ON SearchField4 = ResultField3
   WHERE
  SearchField1 = ;

Tom


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

RE: [sqlite] retrieving data from multiple tables

[Kirrthana M]

I have four tables like Mytable1,Mytable2,Mytable3,Mytable4
Mytable1 and Mytable2 have one similar field rest al different,
Mytable2 and Mytable3 have one similar field rest al different,
Mytable3 and Mytable4 have one similar field rest al different,

i have to select from four tables by matching with all these field.


-Original Message-
From: T [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 14, 2007 12:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] retrieving data from multiple tables


Hi Kirrthana,

> I have created a database with four tables,i have to search and
> retrieve
> data from all the four tables based on the entry i get from the
> previous
> table and display all the entries,could anybody tell how to do it.

I'm not sure what you mean. If you mean you have four tables with
similar fields, and you want to SELECT data from all of them at once,
then you can create a dynamic compound table that will contain all
the data, and SELECT from that. eg:

CREATE VIEW MyTables
AS
  SELECT * FROM MyTable1
   UNION ALL
  SELECT * FROM MyTable2
   UNION ALL
  SELECT * FROM MyTable3
   UNION ALL
  SELECT * FROM MyTable4;

SELECT * FROM MyTables WHERE ;

Or, if you mean that the result of SELECTing in one table becomes the
basis of the SELECT in the next, that is something like the sequence:

value1 = SELECT ResultField1 FROM MyTable1 WHERE SearchField1 =
;

value2 = SELECT ResultField2 FROM MyTable2 WHERE SearchField2 =
ResultField1;

value3 = SELECT ResultField3 FROM MyTable3 WHERE SearchField3 =
ResultField2;

value4 = SELECT ResultField4 FROM MyTable4 WHERE SearchField4 =
ResultField3;

Then I think you could do it in one action by:

SELECT ResultField4
FROM
  MyTable1
   LEFT JOIN
  MyTable2
 ON SearchField2 = ResultField1
   LEFT JOIN
  MyTable3
 ON SearchField3 = ResultField2
   LEFT JOIN
  MyTable4
 ON SearchField4 = ResultField3
WHERE
   SearchField1 = ;

Tom



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Stef Mientki]

COS wrote:

Hi,

- Original Message - 
From: "Stef Mientki" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, March 13, 2007 3:47 PM
Subject: Re: [sqlite] Pragma table_info(), why no fields like UNIQUE,
AUTOINCREMENT


  

You should also consider how your change might effect
backwards compatibility.  The last time that table_info()
was modified, the Ruby-On-Rails community got really
upset.  I'm rather of a mind to leave table_info() alone.

  

Forgive my ignorance, I'm just a beginner in databases,
but what about TABLE_INFO2 ( ) ?
(with the explicit restriction that it can be extended in the future
when needed,
so Ruby users leave it alone ;-)
I really can't imagine that extension of a function can cause serious
compatibility issues)

The alternative is now that I've to
- build a table from table_info()
- query sqlite_master, to get the SQL string with which the table was
generated
- parse the SQL string from sqlite_master
- and add it to my edit grid form table_info
or are there simpler ways ?

--
cheers,
Stef Mientki
http://pic.flappie.nl




A small opinion on that matter: what I would really like to see is something
like system tables. Today sqlite uses only sqlite_master to keep information
about its objects and parsing is required to getter better info of each
object (if one needs to). Using other system tables to keep information
about each object seems very appropriated since most RDBMS already implement
that and it is compatible with SQL ANSI. I think it shouldn't be much of a
problem since sqlite engine already parses each object when it opens a
database. This would remove the need for PRAGMA commands and would make life
much simpler.

  
If it's  a standard habbit or at least a posibility to hold in the 
metadata in standard table,

that would be indeed a much better solution.

Just a thought.
  

But in my rather limited view a rather good one ;-)

cheers,
Stef Mientki


KvK: 41055629



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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Eric Bohlman]

COS wrote:

A small opinion on that matter: what I would really like to see is something
like system tables. Today sqlite uses only sqlite_master to keep information
about its objects and parsing is required to getter better info of each
object (if one needs to). Using other system tables to keep information
about each object seems very appropriated since most RDBMS already implement
that and it is compatible with SQL ANSI. I think it shouldn't be much of a
problem since sqlite engine already parses each object when it opens a
database. This would remove the need for PRAGMA commands and would make life
much simpler.


It might be possible to implement something like this using virtual 
tables.  I seem to remember that you wouldn't be able to support the 
exact syntax of ANSI INFORMATION_SCHEMA, but would be able to support 
most of the functionality.


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

Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[Ralf Junker]

Scott Hess wrote:


>>I am optimistic that the proper implementation will use even less than 50%:
>
>Indeed :-). 

Glad to read this ;-)

>>I found that _not_ adding the original text turned out to be a great time
>>saver. This makes sense if we know that the original text is about 4 times
>>the size of the index. Storing lots of text by itself is already quite time
>>consuming even without creating a FTS index. So I do not expect really
>>bad slow downs by adding a docid->term index.
>
>Are you doing your inserts in the implied transactions sqlite provides
>for you if you didn't open an explicit transaction?  I'm found that
>when doing bulk inserts, the maintenance of the content table is a
>pretty small part of the overall time, perhaps 10%.

My timings vary: I have just measured the insertion speeds with and without 
storing the original text and was _very_ surprised by the results:

WITHtext storage: 1055 KB / sec
WITHOUT text storage: 4948 KB / sec

FTS without text storage performed almost 5 (five!) times faster than with text 
storage (running WinXP on a fairly recent system with a 5200 rotations per sec 
hard drive).

The testing scenario: There were no changes to the code except that I commented 
out the text bindings as described in my earlier message. The same documents 
were indexed (10739 files, 239959 KB size in total). Insertion took place in a 
single transaction, SYNCHRONOUS = OFF was used as the only tweak to the 
database. I ran all tests multiple times consecutively on an empty database to 
avoid OS file buffering interferences.

>>Snippets are of course nice to have out of the box as it is right now. But
>>even without storing the original text, snippets could be created by
>>
>>1. supplying the text through other means (additional parameter or
>>callback function), so that not FTS but the application would read
>>it from a disk file or decompress it from a database field.
>>
>>2. constructing token-only snippets from the document tokens and
>>offsets. This would of course exclude all non-word characters, but
>>would still return legible information.
>
>A use-case that was considered was indexing PDF data, in which case
>the per-document tokenization cost would probably be a couple seconds.
>If you ran a query which matched a couple thousand documents and
>proceeded to re-tokenize them for snippet generation, you'd be in deep
>trouble.  This is somewhat addressable by providing scoring mechanisms
>and using subselects (basically, have the subselect order by score,
>then cap the number of results, and have the main select ask for
>snippets).  A variant on that would be an index of a CD.  In that case
>it's pretty much essential that the index be able to efficiently
>answer questions without having to seek all over the disk.

Quite true.  But is this indeed a realistic scenario? It sounds a bit like the 
"select * from my-million-row-table" problem. Nothing wrong with this per se, 
but be aware of the consequences.

>Option 2 has some attraction, though, because you have the option of
>transparently segmenting the document into blocks and thus not having
>to re-tokenize the entire document to generate snippets.

Thanks!

>>>Being able to have an index without storing the original data was a
>>>weak goal when fts1 was being developed, but every time we visitted
>>>it, we found that the negatives of that approach were substantial
>>>enough to discourage us for a time.  [The "we" in that sentence means
>>>"me and the various people I run wacky ideas past."]  I'm keeping an
>>>eye out for interesting implementation strategies and the time to
>>>explore them, though.
>>
>>Maybe my arguments could influence the opinion of "we"? I would love
>>to see FTS without text storage, especially since I just lost a project to
>>another FTS product because duplicating data was unfortunately "out
>>of disk space".
>
>Feel free to drop me a description of the types of things you're doing
>out-of-band, maybe something will gel.  No promises!  Most of the
>current use-cases are pretty clear - since the data is already going
>to be in the database, letting fts2 store it is no big deal.  I can
>imagine pretty broad classes of problems which could come up when
>indexing data which is not in the database, so one of the challanges
>is to narrow down which problems are real, and which are figments.

I conclude from your remarks that the offsets() problem is not predominant and 
could be solved even without storing full text in the database. If so, snippets 
could be created as well from those offsets. I realize that this will 
commplicate the FTS2 implementation, so please excuse if I am arguing from a 
user's perspective.

For users, I can see the following benefits in separating FTS index and 
original text:

* Space savings when indexing external documents not stored in the database.

* Possibility to add FTS to text stored in compressed format in the database.

* Possibility to mix FTS text rows with 

Re: [sqlite] retrieving data from multiple tables

[T]

I have four tables like Mytable1,Mytable2,Mytable3,Mytable4
Mytable1 and Mytable2 have one similar field rest al different,
Mytable2 and Mytable3 have one similar field rest al different,
Mytable3 and Mytable4 have one similar field rest al different,

i have to select from four tables by matching with all these field.


OK, let's say that the "similar field" in MyTable1 is Field1, and the  
"similar field" in MyTable2 is Field2 etc, then I think you want to  
create a view:


CREATE VIEW MyTables
AS
  SELECT Field1, otherFields1 AS MyField FROM MyTable1
   UNION ALL
  SELECT Field2, otherFields2 FROM MyTable2
   UNION ALL
  SELECT Field3, otherFields3 FROM MyTable3
   UNION ALL
  SELECT Field4, otherFields4 FROM MyTable4;

and thereafter simply query it as if it is one big table:

SELECT * FROM MyTables WHERE MyField is desiredValue;

I put in "otherFields" since I'm assuming that you want to return  
some fields other than the one you're searching.


Tom



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

Re: [sqlite] retrieving data from multiple tables

[T]

Oops, I meant:

CREATE VIEW MyTables
AS
  SELECT Field1 AS MyField, otherFields1 FROM MyTable1
   UNION ALL
  SELECT Field2, otherFields2 FROM MyTable2
   UNION ALL
  SELECT Field3, otherFields3 FROM MyTable3
   UNION ALL
  SELECT Field4, otherFields4 FROM MyTable4;


and thereafter simply query it as if it is one big table:

SELECT * FROM MyTables WHERE MyField is desiredValue;



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

RE: [sqlite] retrieving data from multiple tables

[Kirrthana M]

By getting the search string from previous table im not only retrieving data
from the table4,at each step i have to retrieve data from 3 tables and store
it in the data structure.

-Original Message-
From: T [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 14, 2007 3:35 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] retrieving data from multiple tables


Oops, I meant:

CREATE VIEW MyTables
 AS
   SELECT Field1 AS MyField, otherFields1 FROM MyTable1
UNION ALL
   SELECT Field2, otherFields2 FROM MyTable2
UNION ALL
   SELECT Field3, otherFields3 FROM MyTable3
UNION ALL
   SELECT Field4, otherFields4 FROM MyTable4;

> and thereafter simply query it as if it is one big table:
>
> SELECT * FROM MyTables WHERE MyField is desiredValue;



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

RE: [sqlite] date/time implementation question

[Rafi Cohen]

Thanks Denis for your detailed explanation. My needs differ from the
example you gave.
I need to compare the date on each row with the current date, if the
rowdate has passed then I either insert or update this row on a specific
table, otherwise I leave this row for a future check.
So, the comparison has to be made in C and not sqlite, I think.
Now, suppose I brought the rowdate to the format "-mm--dd HH:MM:SS".
In order to call strcmp() to compare with the current date, I need to
bring the current date to the same format. How can I do this, or you
have other ideas to make this comparison?
Thanks, Rafi.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 13, 2007 7:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date/time implementation question


Rafi Cohen wrote:
> Hi, I hope this question is not off-topic for this list.
> I'm recieveing, occasionally, a .csv file from a source which I need 
> to process accordcing to some criteria and either insert, delet or 
> update it's rows into a database. One of it's criterias is date 
> comparison. In other words, proceeding differently when the date/time 
> mentioned on a specific row is a past date or future date comparing 
> with the current date/time. My problem is the format in which I 
> recieve the date and time: "HH:MM:SS, --mm-dd".
> This means separate fileds, one for time and one for date.
> If the format was: "-mm-dd HH:MM:SS", I could call strptime to
stor
> this in a struct tm, then cakk mktime to get this in a time_t varialbe
> and then call difftime with the current date/time to make the
comparison
> (I'm using C as programmikng language on Linux).
> So, my question is: should I unify the 2 strings into one to obtain
the
> second format and then proceed as I explained above, or is there a way
> to compare the date with the current date separately and the time with
> the current time separately in case the dates are equal?
> If the conclusion will be to uify the strings into one, should I
create
> a table with a single date/time column or still keep the 2 fileds
> separately in my sql table?
> Thanks, Rafi.
>
>   
Rafi,

It depends. :-)

It depends on what you think is more important, execution speed, 
database size, simplicity of coding, etc...

Assuming the date format you gave has a typo and there is really only 
one '-' between the year and month in your existing fields, you should 
be able to do your date comparisons in sqlite. Use concatenation to 
build a single date and time string. With your date and time in ISO 
format you can then use string comparisons as date comparisons

select
case when (date || ' ' || time) < ?limit_date
then field_one
else field_two
end
from my_table


HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.10/720 - Release Date:
3/12/2007 7:19 PM



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

[sqlite] results from a prepared select statement

[Rafi Cohen]

Hi, I read the documentation but still miss this, so basic information.
I have a prepared select statement which should be executed in a later
state using sqlite3_step in my application.
For a very simple example, suppose the statement is: select * from tbl
where col1 = ?.
Now my question is how do I know, after sqlite3_step, if I got results
at all. I would like my application to have this information before
trying to retrieve the results by using sqlite3_column* functions. From
the other side, if I retrieve those results in a loop, I would like to
now how many rows I need to retrieve?
sorry, so basic but I still miss it.
Thanks, Rafi.

[sqlite] Re: results from a prepared select statement

[Igor Tandetnik]

Rafi Cohen <[EMAIL PROTECTED]> wrote:

Now my question is how do I know, after sqlite3_step, if I got results
at all.


sqlite3_step returns SQLITE_ROW if you got a row of results, SQLITE_DONE 
if no more rows, and SQLITE_ERROR in case of error. If resultset is 
empty, you'll just get SQLITE_DONE on the first call.



I would like my application to have this information before
trying to retrieve the results by using sqlite3_column* functions.


You can only use sqlite3_column* functions if the previous call to 
sqlite3_step returned SQLITE_ROW.



From the other side, if I retrieve those results in a loop, I would
like to now how many rows I need to retrieve?


You can't know that until you retrieve them all. SQLite engine itself 
doesn't know that. Just keep going until you get SQLITE_DONE.


Igor Tandetnik 



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

[sqlite] DB recovery

[Pavan]

Hi,

In a situation where if the sqliteDB gets corrupted is there a way to
recover the data ?
I understand that the word *corrupted* is too generic from technical point
of view. But, still
would like to know if anyone has faced a situation where indirect methods
are used
to retrieve data from DB.

Thanks,
Pavan.

--
'
Always finish stronger than you start
*

Re: [sqlite] Degrouping, desummarizing or integrating headings

[Dennis Cote]

T wrote:


It comes as a CSV or tabbed text file, then into a table in my 
database. So, it's in a table in my database, eg:


CREATE TABLE Shopping_Grouped(Aisle TEXT, Product TEXT, Cost REAL)

But the Aisle entries are in records by themselves, and apply to the 
subsequent records containing Product and Cost, for example:


INSERT INTO Shopping_Grouped(Aisle) VALUES('Dairy');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Milk', 2);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cream', 1);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cheese', 3);

INSERT INTO Shopping_Grouped(Aisle) VALUES('Bakery');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 4);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 3);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cake', 2);

But I want to get it into this schema:

INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Milk', 2);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cream', 1);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cheese', 3);

INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced', 4);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced', 3);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Cake', 2);


The heart of your problem is that the original database has no explicit 
relation between the rows that have a value in the Aisle column and the 
rows that don't but should. In general, since a table is nothing more 
than a set (an unordered collection) of rows this information must be 
supplied by some other means (i.e. user input or another table etc).


If you always insert the rows in the order shown above you can make use 
of the fact that sqlite will assign rowids in the order the rows are 
created. This is using a implementation detail of sqlite to supply the 
missing information. There are a couple of ways to do this that come to 
mind.


   create table shopping as
   select
   (select B.Aisle from shopping_grouped as B where B.rowid =
   (select max(C.rowid) from shopping_grouped as C
   where C.Aisle is not null and C.rowid < A.rowid)) as Ailse,
   A.Product as Product,
   A.Cost as Cost
   from shopping_grouped as A
   where A.product is not null;

   create table shopping2 as
   select
   (select B.Aisle from shopping_grouped as B
   where B.rowid < A.rowid and B.Aisle is not null
   order by B.rowid desc
   limit 1) as Aisle,
   A.Product as Product,
   A.Cost as Cost
   from shopping_grouped as A
   where A.product is not null;

This gives this result in sqlite 3.3.13

   sqlite> select * from shopping;
   Ailse   Product Cost
   --  --  --
   Dairy   Milk2.0
   Dairy   Cream   1.0
   Dairy   Cheese  3.0
   Bakery  Sliced  4.0
   Bakery  Sliced  3.0
   Bakery  Cake2.0
   sqlite> select * from shopping2;
   Aisle   Product Cost
   --  --  --
   Dairy   Milk2.0
   Dairy   Cream   1.0
   Dairy   Cheese  3.0
   Bakery  Sliced  4.0
   Bakery  Sliced  3.0
   Bakery  Cake2.0

HTH
Dennis Cote

  
  


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

RE: [sqlite] DB recovery

[Griggs, Donald]

 
Hi, Pavan,

Regarding:
  "In a situation where if the sqliteDB gets corrupted is there a way to
recover the data ?
I understand that the word *corrupted* is too generic from technical
point of view. But, still would like to know if anyone has faced a
situation where indirect methods are used to retrieve data from DB."

I think the general advice goes something like this:

1) After a power outage or system crash, be sure to let sqlite attempt
to bring the DB to a consistent state using its journal.

2) If the DB then asserts that it is corrupted, make a copy of it for
safety, than attempt a VACUUM command.

3) If the DB still asserts corruption, use the command line utility and
attempt to .DUMP individual tables.

4) Resort to your backups if all else fails.

And for the future, make sure you're not using "PRAGMA synchronous =
OFF;" for any data you care about losing.

The sqlite codes goes to great lengths to avoid data corruption where
possible.


See also: How To Corrupt Your Database Files
http://www.sqlite.org/lockingv3.html#how_to_corrupt

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

Re: [sqlite] date/time implementation question

[Dennis Cote]

Rafi Cohen wrote:

Thanks Denis for your detailed explanation. My needs differ from the
example you gave.
I need to compare the date on each row with the current date, if the
rowdate has passed then I either insert or update this row on a specific
table, otherwise I leave this row for a future check.
So, the comparison has to be made in C and not sqlite, I think.
Now, suppose I brought the rowdate to the format "-mm--dd HH:MM:SS".
In order to call strcmp() to compare with the current date, I need to
bring the current date to the same format. How can I do this, or you
have other ideas to make this comparison?
  

Rafi,

There is no need for C in case you have given. A simple insert of 
selected data should do.


   insert or ignore into over_due
   select id from schedule
   where (due_date || ' ' || due_time) < datetime('now');

This uses one of sqlite's builtin date and time functions (see 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ) to format 
the current time into an ISO format that can be compared directly with 
the concatenated fields from your existing table. The id of any records 
that meet the condition are inserted into the over_due table (or ignored 
if they already exist in that table).


HTH
Dennis Cote



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

Re: [sqlite] DB recovery

[Pavan]

Hi Donald,

Thanks, that was indeed a detailed explanation.
The scenrio is an embedded device with sqlite in it and user does not have
direct access to the DB.
So, are there any Sqlite interfaces which do the recovery job mentioned in
your email ?

Thanks,
Pavan.


On 3/14/07, Griggs, Donald <[EMAIL PROTECTED]> wrote:



Hi, Pavan,

Regarding:
"In a situation where if the sqliteDB gets corrupted is there a way to
recover the data ?
I understand that the word *corrupted* is too generic from technical
point of view. But, still would like to know if anyone has faced a
situation where indirect methods are used to retrieve data from DB."

I think the general advice goes something like this:

1) After a power outage or system crash, be sure to let sqlite attempt
to bring the DB to a consistent state using its journal.

2) If the DB then asserts that it is corrupted, make a copy of it for
safety, than attempt a VACUUM command.

3) If the DB still asserts corruption, use the command line utility and
attempt to .DUMP individual tables.

4) Resort to your backups if all else fails.

And for the future, make sure you're not using "PRAGMA synchronous =
OFF;" for any data you care about losing.

The sqlite codes goes to great lengths to avoid data corruption where
possible.


See also: How To Corrupt Your Database Files
http://www.sqlite.org/lockingv3.html#how_to_corrupt


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
'
Always finish stronger than you start
*

RE: [sqlite] DB recovery

[Griggs, Donald]

 
Regarding:
  "In a situation where if the sqliteDB gets corrupted is there a way to

   recover the data ?"
   "...The scenrio is an embedded device with sqlite in it and user does
not have direct access to the DB.
So, are there any Sqlite interfaces which do the recovery job mentioned
in your email ?"

Hmmm...you may need someone more knowledgeable than myself.

I would expect one would need considerably more information about the
embedded device.
Suppose there was a wonderful sqlite recovery utility program.   How
could that program get access to the database?  If you can export the
sqlite database and bring it to a desktop machine (either linux or
windows or other) you could presumably perform the steps easily, then
import the repaired database back into the device.

I know you mention "no direct access" -- but what sort of access would
the desired utility program have??

A feature of sqlite, BTW, is that you can transport a database file
between machines with different byte orders without problems.


If there is no way to get the database out of the embedded device, then
if the firmware can be updated, one could have the firmware perform the
vacuum itself (assuming there's sufficient flash ram or disk space on
the device).


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

[sqlite] Link error with -DSQLITE_OMIT_TRIGGER

[Ken]

Compile error with -DSQLITE_OMIT_TRIGGER
 
 I get a link error when using:  -DSQLITE_OMIT_TRIGGER
 ===
 gcc -g -DSQLITE_DEFAULT_PAGE_SIZE=8192 -DSQLITE_DEFAULT_CACHE_SIZE=4000 
-DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_AUTHORIZATION 
-DSQLITE_OMIT_VIRTUAL_TABLE -DSQLITE_OMIT_TRIGGER -DSQLITE_OMIT_LOAD_EXTENSION 
-DOS_UNIX=1 -DHAVE_USLEEP=1 -DHAVE_FDATASYNC=1 -I. 
-I../sqliteSrc/sqlite-3.3.13/src -DNDEBUG -DTHREADSAFE=1 
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 
-DHAVE_READLINE=1 -I/usr/include/readline -o .libs/sqlite3 
../sqliteSrc/sqlite-3.3.13/src/shell.c  ./.libs/libsqlite3.so -lpthread 
-lreadline
 ./.libs/libsqlite3.so: undefined reference to `sqlite3DropTrigger'
 ./.libs/libsqlite3.so: undefined reference to `sqlite3BeginTrigger'
 ./.libs/libsqlite3.so: undefined reference to `sqlite3FinishTrigger'
 ./.libs/libsqlite3.so: undefined reference to `sqlite3TriggerDeleteStep'
 ./.libs/libsqlite3.so: undefined reference to `sqlite3DeleteTriggerStep'
 ./.libs/libsqlite3.so: undefined reference to `sqlite3TriggerSelectStep'
 ./.libs/libsqlite3.so: undefined reference to `sqlite3TriggerUpdateStep'
 ./.libs/libsqlite3.so: undefined reference to `sqlite3TriggerInsertStep'
 =
 
 And  also from an application link:
 /home/ixion/LIB/sqlite3/.libs64/libsqlite3.a(parse.o): In function 
`yy_destructor':
 /home/ixion/LIB/sqlite3/parse.y:957: undefined reference to 
`sqlite3DeleteTriggerStep'
 /home/ixion/LIB/sqlite3/.libs64/libsqlite3.a(parse.o): In function `yy_reduce':
 /home/ixion/LIB/sqlite3/parse.y:924: undefined reference to 
`sqlite3FinishTrigger'
 /home/ixion/LIB/sqlite3/parse.y:930: undefined reference to 
`sqlite3BeginTrigger'
 /home/ixion/LIB/sqlite3/parse.y:973: undefined reference to 
`sqlite3TriggerUpdateStep'
 /home/ixion/LIB/sqlite3/parse.y:978: undefined reference to 
`sqlite3TriggerInsertStep'
 /home/ixion/LIB/sqlite3/parse.y:981: undefined reference to 
`sqlite3TriggerInsertStep'
 /home/ixion/LIB/sqlite3/parse.y:985: undefined reference to 
`sqlite3TriggerDeleteStep'
 /home/ixion/LIB/sqlite3/parse.y:988: undefined reference to 
`sqlite3TriggerSelectStep'
 /home/ixion/LIB/sqlite3/parse.y:1016: undefined reference to 
`sqlite3DropTrigger'
 collect2: ld returned 1 exit status
 
 

RE: [sqlite] date/time implementation question

[Rafi Cohen]

Dennis, I really appreciate your patience and willingness to help.
Unfortunately, this still did not bring me to the expected solution. I
will give you a small algorithm of what I need to do and I'm sure after
this you'll know how to assist me.
1. I read a .csv file into a linked list of structures.
2. I examine eacch structure one after the other:
Compare the datetime filed of the structure aginst the current date. If
bigger (future), I skup to the next structure.
If smaller or equal, I check if a row with the same id field already
exists in the table:
Select * from tbl where id = id-in-struct.
If no such row exists, I insert a row according to the structure's
field, otherwise, based on another criteria I EITHER UPDATE THE row with
a new value on the second column or delete the row.
3. After this process, I free the structure from the linked list and
move to the next structure.
Because of the last section, I thought I need to make the date
comparison in C, but I may be wrong here.
However, if the comparison is made thru sqlite, how can I know if indeed
an insert, update or delete was processed so that I can free the
structures?
All the sql statements I use above are, of course, prepared statements
which I execute with wqlite3_step for the fields of each structure.
I hope I'm clear, this time.
Thanks, Rafi.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 14, 2007 5:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date/time implementation question


Rafi Cohen wrote:
> Thanks Denis for your detailed explanation. My needs differ from the 
> example you gave. I need to compare the date on each row with the 
> current date, if the rowdate has passed then I either insert or update

> this row on a specific table, otherwise I leave this row for a future 
> check. So, the comparison has to be made in C and not sqlite, I think.
> Now, suppose I brought the rowdate to the format "-mm--dd
HH:MM:SS".
> In order to call strcmp() to compare with the current date, I need to
> bring the current date to the same format. How can I do this, or you
> have other ideas to make this comparison?
>   
Rafi,

There is no need for C in case you have given. A simple insert of 
selected data should do.

insert or ignore into over_due
select id from schedule
where (due_date || ' ' || due_time) < datetime('now');

This uses one of sqlite's builtin date and time functions (see 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ) to format 
the current time into an ISO format that can be compared directly with 
the concatenated fields from your existing table. The id of any records 
that meet the condition are inserted into the over_due table (or ignored

if they already exist in that table).

HTH
Dennis Cote




-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.11/721 - Release Date:
3/13/2007 4:51 PM



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

Re: [sqlite] results from a prepared select statement

[John Stanton]

Rafi Cohen wrote:

Hi, I read the documentation but still miss this, so basic information.
I have a prepared select statement which should be executed in a later
state using sqlite3_step in my application.
For a very simple example, suppose the statement is: select * from tbl
where col1 = ?.
Now my question is how do I know, after sqlite3_step, if I got results
at all. I would like my application to have this information before
trying to retrieve the results by using sqlite3_column* functions. From
the other side, if I retrieve those results in a loop, I would like to
now how many rows I need to retrieve?
sorry, so basic but I still miss it.
Thanks, Rafi.

Look at the returned status.  Each time you retrieve a row you get a ROW 
status.  Test for it and process the row.


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

Re: [sqlite] Link error with -DSQLITE_OMIT_TRIGGER

[drh]

Ken <[EMAIL PROTECTED]> wrote:
> Compile error with -DSQLITE_OMIT_TRIGGER
>  
>  I get a link error when using:  -DSQLITE_OMIT_TRIGGER
>  
>  And  also from an application link:
>  /home/ixion/LIB/sqlite3/.libs64/libsqlite3.a(parse.o): In function 
> `yy_destructor':

You have to pass the -DSQLITE_OMIT_... arguments to lemon
when you convert parse.y into parse.c.  Otherwise the parse.c
file will contain references to functions that do not exist.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Re: Meta Information: How to retrieve the column names of a table ?

[Stef Mientki]

How can I get all the names of a table without doing a query against
the table ?


PRAGMA table_info(table-name);

And it also works for views, as just found out by trial and error ;-)
Is this standard SQL behavior ?


--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] date/time implementation question

[Dennis Cote]

Rafi Cohen wrote:

Dennis, I really appreciate your patience and willingness to help.
Unfortunately, this still did not bring me to the expected solution. I
will give you a small algorithm of what I need to do and I'm sure after
this you'll know how to assist me.
1. I read a .csv file into a linked list of structures.
2. I examine eacch structure one after the other:
Compare the datetime filed of the structure aginst the current date. If
bigger (future), I skup to the next structure.
If smaller or equal, I check if a row with the same id field already
exists in the table:
Select * from tbl where id = id-in-struct.
If no such row exists, I insert a row according to the structure's
field, otherwise, based on another criteria I EITHER UPDATE THE row with
a new value on the second column or delete the row.
3. After this process, I free the structure from the linked list and
move to the next structure.
Because of the last section, I thought I need to make the date
comparison in C, but I may be wrong here.
However, if the comparison is made thru sqlite, how can I know if indeed
an insert, update or delete was processed so that I can free the
structures?
All the sql statements I use above are, of course, prepared statements
which I execute with wqlite3_step for the fields of each structure.
I hope I'm clear, this time.
  

Rafi,

I think I have followed your description and I agree that you could do 
this by iterating in C as you have described. If you want to do that and 
all you need is an expedient way to get a date and time string for the 
current time for the comparison in step 2 you have a couple of approaches.


One simple way is to let sqlite do it for you by executing a single SQL 
statement "select datetime('now')". This will return a ISO date and time 
string that you can compare with the concatenated strings that are in 
your structure. The other is to use the standard C library routines to 
build an ISO formatted date and time string. These two examples are 
shown below in a mix of C an pseudo code comments (you have to fill in 
the blanks).


Get current date and time from sqlite:

   sqlite3_stmt* get_now;
   sqlite3_prepare(db, "select datetime'now'", -1, _now, NULL);  


   char now_datetime[20];
   sqlite3_step(get_now);
   strcpy(now_datetime, sqlite3_column_text(get_now, 0));

   // read csv into list of structures

   //for each structure in the list 
   char rec_datetime[20];

   strcpy(rec_datetime, a_struct.date);
   strcat(rec_datetime, " ");
   strcat(rec_datetime, a_struct.time);

   if (strcmp(rec_datetime, now_datetime) <= 0) {
   //if row with matching id exists in table
   //if record should be deleted
   //delete record
   //else
   //update record based on structure
   //else
   //insert a new record into table   
   }


   //free the structure
  
Get current date and time from C library: 


   char now_datetime[20];
   time_t now = time(NULL);
   struct tm *now_tm = localtime();
   sprintf(now_datetime, "%4d-%02d-%02d %02d:%02d:%02d",
   now_tm->tm_year + 1900, now_tm->tm_mon + 1, now_tm->tm_mday,
   now_tm->tm_hour, now_tm->tm_min, now_tm->tm_sec);

   // read csv into list of structures

   //for each structure in the list 
   char rec_datetime[20];

   strcpy(rec_datetime, a_struct.date);
   strcat(rec_datetime, " ");
   strcat(rec_datetime, a_struct.time);

   if (strcmp(rec_datetime, now_datetime) <= 0) {
   //if row with matching id exists in table
   //if record should be deleted
   //delete record
   //else
   //update record based on structure
   //else
   //insert a new record into table   
   }


   //free the structure

Both of these approaches use the fact that ISO format date and time 
strings can be compared using a normal string comparison.

I think another approach might be worth considering though. Instead of 
reading your csv data into a list of structures, import it into an 
sqlite table. You could use the sqlite shell to do this, or you could 
copy the code from the import routine in the sqlite shell directly into 
your application (it's free open source code). With your csv records in 
a temporary table  you can do the manipulations in SQL.


   // read csv into temporary table csv
   system("sqlite3 mydb \"create temp table csv(...);.import myfile.csv 
csv\"");


   // delete the records with future dates (instead of skiping them)
   delete from csv
   where (date || ' ' || time) > datetime('now');

   // split the csv table into new and existing records
   create temp table new_csv as
   select * from csv where id not in (select id from perm_table);
   delete from csv
   where id in (select id from new_csv);

   // update the existing records in the permanent table
   update perm_table
   set field2 

[sqlite] Format lf csv file RE: [sqlite] date/time implementation question

[Rafi Cohen]

Hi Dennis, the first approach is clear now and I may proceed with it.
The second approach is interesting and chalenging, but leaves some
issues to clarify and in case I find solutions to those issues I well
may adopt it.
1. format of csv file: I have no idea how this csv file is created and
which database engine is used. I do know that I receive such a file once
or twice a day.
While reading the file onto the list of structures, I also validate the
consistency of the data in the file.
Before the data lines (starting with "d,") there is a header line
"hdr,". This line contains the sequential number of the file, number of
records in the file and a checksum on a specific field of the data (say
coumn 2).
As I knew nothing up to now about .import, I wonder if there is a way to
include those checings in the second approach?
2. The deletion of the future dates is incorrect. On the contrary, in
the first approach, I re-examine the remaining structures each half a
minute until any of them becomes past date, then I process it just like
any other past date structures and then free it.
In case a new .csv file arrives, I add the new list of structures to the
remaining ones and continue to examine them every half a minute.
I could do the same with the sql3_exec statement in the second approach,
but I need another approach for the case of the future records.
I hope you have satisfying answers for those 2 issues and then I'll be
glad to proceed with the second approach.
Thanks, Rafi.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 14, 2007 9:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date/time implementation question


Rafi Cohen wrote:
> Dennis, I really appreciate your patience and willingness to help. 
> Unfortunately, this still did not bring me to the expected solution. I

> will give you a small algorithm of what I need to do and I'm sure 
> after this you'll know how to assist me. 1. I read a .csv file into a 
> linked list of structures. 2. I examine eacch structure one after the 
> other: Compare the datetime filed of the structure aginst the current 
> date. If bigger (future), I skup to the next structure.
> If smaller or equal, I check if a row with the same id field already
> exists in the table:
> Select * from tbl where id = id-in-struct.
> If no such row exists, I insert a row according to the structure's
> field, otherwise, based on another criteria I EITHER UPDATE THE row
with
> a new value on the second column or delete the row.
> 3. After this process, I free the structure from the linked list and
> move to the next structure.
> Because of the last section, I thought I need to make the date
> comparison in C, but I may be wrong here.
> However, if the comparison is made thru sqlite, how can I know if
indeed
> an insert, update or delete was processed so that I can free the
> structures?
> All the sql statements I use above are, of course, prepared statements
> which I execute with wqlite3_step for the fields of each structure.
> I hope I'm clear, this time.
>   
Rafi,

I think I have followed your description and I agree that you could do 
this by iterating in C as you have described. If you want to do that and

all you need is an expedient way to get a date and time string for the 
current time for the comparison in step 2 you have a couple of
approaches.

One simple way is to let sqlite do it for you by executing a single SQL 
statement "select datetime('now')". This will return a ISO date and time

string that you can compare with the concatenated strings that are in 
your structure. The other is to use the standard C library routines to 
build an ISO formatted date and time string. These two examples are 
shown below in a mix of C an pseudo code comments (you have to fill in 
the blanks).

Get current date and time from sqlite:

sqlite3_stmt* get_now;
sqlite3_prepare(db, "select datetime'now'", -1, _now, NULL);  

char now_datetime[20];
sqlite3_step(get_now);
strcpy(now_datetime, sqlite3_column_text(get_now, 0));

// read csv into list of structures

//for each structure in the list 
char rec_datetime[20];
strcpy(rec_datetime, a_struct.date);
strcat(rec_datetime, " ");
strcat(rec_datetime, a_struct.time);

if (strcmp(rec_datetime, now_datetime) <= 0) {
//if row with matching id exists in table
//if record should be deleted
//delete record
//else
//update record based on structure
//else
//insert a new record into table   
}

//free the structure
   
 Get current date and time from C library: 

char now_datetime[20];
time_t now = time(NULL);
struct tm *now_tm = localtime();
sprintf(now_datetime, "%4d-%02d-%02d %02d:%02d:%02d",
now_tm->tm_year + 1900, now_tm->tm_mon + 1, now_tm->tm_mday,
now_tm->tm_hour, 

Re: [sqlite] Format lf csv file RE: [sqlite] date/time implementation question

[Dennis Cote]

Rafi Cohen wrote:

Hi Dennis, the first approach is clear now and I may proceed with it.
  

Good to hear.

The second approach is interesting and chalenging, but leaves some
issues to clarify and in case I find solutions to those issues I well
may adopt it.
1. format of csv file: I have no idea how this csv file is created and
which database engine is used. I do know that I receive such a file once
or twice a day.
While reading the file onto the list of structures, I also validate the
consistency of the data in the file.
Before the data lines (starting with "d,") there is a header line
"hdr,". This line contains the sequential number of the file, number of
records in the file and a checksum on a specific field of the data (say
coumn 2).
As I knew nothing up to now about .import, I wonder if there is a way to
include those checings in the second approach?
  
Firstly, this does not sound like a standard CSV format file (see 
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm ).


Secondly, your validation checks can not be added to the normal csv 
.import command using the sqlite shell, but they could be added to a 
customized copy of the import routine that you add to your own code. It 
might also be possible to convert some of the validation tests to SQL 
check constraints on the table columns as well.

2. The deletion of the future dates is incorrect. On the contrary, in
the first approach, I re-examine the remaining structures each half a
minute until any of them becomes past date, then I process it just like
any other past date structures and then free it.
In case a new .csv file arrives, I add the new list of structures to the
remaining ones and continue to examine them every half a minute.
I could do the same with the sql3_exec statement in the second approach,
but I need another approach for the case of the future records.
I hope you have satisfying answers for those 2 issues and then I'll be
glad to proceed with the second approach.
  
In that case you could split the imported data into two tables using the 
date test. And then process only the table that contains the old 
records. The future records would remain in a second table. This second 
table would probably be the same one you import your new csv file 
records into. The processing of the old records would proceed as before.


   //assumes table imported contains the imported records
   //select records to process based on date and time
   create table process as
   select * from imported where  (date || ' ' || time) <= 
datetime('now');

   delete from imported where id in (select id from process);

You could also skip the concatenation by splitting the data and time 
test if you replace the condition above with


   date <= date('now' and time <= time('now')

Its probably a case of six of one or half a dozen of the other.

There really are a lot of different possibilities for processing the 
data once you have them in table in the database.


HTH
Dennis Cote

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

Re: [sqlite] Format lf csv file RE: [sqlite] date/time implementation question

[Clark Christensen]

Hi Rafi,

If it were mine to do, I would concentrate on getting the data into a table 
where I can work with it using SQL.

It sounds like your best bet is to write some simple code to read through your 
CSV, validate its consistency (ignore the dates), and insert it into a table.  
Then use Dennis's temp table scenario to process the rows as appropriate.

Obviously, it doesn't necessarily need to be a temp table as in "create temp 
table...".  It could easily be a non-temp "scratch", or "pending" table that 
doesn't go away when the connection closes.  That way, you can close and come 
back later to deal with the rows that crossed-over into past-date state.

 -Clark

- Original Message 
From: Rafi Cohen <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, March 14, 2007 1:21:21 PM
Subject: [sqlite] Format lf csv file RE: [sqlite] date/time implementation 
question

Hi Dennis, the first approach is clear now and I may proceed with it.
The second approach is interesting and chalenging, but leaves some
issues to clarify and in case I find solutions to those issues I well
may adopt it.
1. format of csv file: I have no idea how this csv file is created and
which database engine is used. I do know that I receive such a file once
or twice a day.
While reading the file onto the list of structures, I also validate the
consistency of the data in the file.
Before the data lines (starting with "d,") there is a header line
"hdr,". This line contains the sequential number of the file, number of
records in the file and a checksum on a specific field of the data (say
coumn 2).
As I knew nothing up to now about .import, I wonder if there is a way to
include those checings in the second approach?
2. The deletion of the future dates is incorrect. On the contrary, in
the first approach, I re-examine the remaining structures each half a
minute until any of them becomes past date, then I process it just like
any other past date structures and then free it.
In case a new .csv file arrives, I add the new list of structures to the
remaining ones and continue to examine them every half a minute.
I could do the same with the sql3_exec statement in the second approach,
but I need another approach for the case of the future records.
I hope you have satisfying answers for those 2 issues and then I'll be
glad to proceed with the second approach.
Thanks, Rafi.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 14, 2007 9:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date/time implementation question


Rafi Cohen wrote:
> Dennis, I really appreciate your patience and willingness to help. 
> Unfortunately, this still did not bring me to the expected solution. I

> will give you a small algorithm of what I need to do and I'm sure 
> after this you'll know how to assist me. 1. I read a .csv file into a 
> linked list of structures. 2. I examine eacch structure one after the 
> other: Compare the datetime filed of the structure aginst the current 
> date. If bigger (future), I skup to the next structure.
> If smaller or equal, I check if a row with the same id field already
> exists in the table:
> Select * from tbl where id = id-in-struct.
> If no such row exists, I insert a row according to the structure's
> field, otherwise, based on another criteria I EITHER UPDATE THE row
with
> a new value on the second column or delete the row.
> 3. After this process, I free the structure from the linked list and
> move to the next structure.
> Because of the last section, I thought I need to make the date
> comparison in C, but I may be wrong here.
> However, if the comparison is made thru sqlite, how can I know if
indeed
> an insert, update or delete was processed so that I can free the
> structures?
> All the sql statements I use above are, of course, prepared statements
> which I execute with wqlite3_step for the fields of each structure.
> I hope I'm clear, this time.
>   
Rafi,

I think I have followed your description and I agree that you could do 
this by iterating in C as you have described. If you want to do that and

all you need is an expedient way to get a date and time string for the 
current time for the comparison in step 2 you have a couple of
approaches.

One simple way is to let sqlite do it for you by executing a single SQL 
statement "select datetime('now')". This will return a ISO date and time

string that you can compare with the concatenated strings that are in 
your structure. The other is to use the standard C library routines to 
build an ISO formatted date and time string. These two examples are 
shown below in a mix of C an pseudo code comments (you have to fill in 
the blanks).

Get current date and time from sqlite:

sqlite3_stmt* get_now;
sqlite3_prepare(db, "select datetime'now'", -1, _now, NULL);  

char now_datetime[20];
sqlite3_step(get_now);
strcpy(now_datetime, sqlite3_column_text(get_now, 0));

// read csv into 

Re: [sqlite] retrieving data from multiple tables

[T]

Hi Kirrthana,

I have created a database with four tables,i have to search and  
retrieve data from all the four tables based on the entry i get  
from the previous table and display all the entries,could anybody  
tell how to do it.


I'm not sure what you mean. If you mean you have four tables with  
similar fields, and you want to SELECT data from all of them at  
once, then you can create a dynamic compound table that will  
contain all the data, and SELECT from that.


Or, if you mean that the result of SELECTing in one table becomes  
the basis of the SELECT in the next, that is something like the  
sequence...



i have to select from four tables by matching with all these field.


By getting the search string from previous table im not only  
retrieving data
from the table4,at each step i have to retrieve data from 3 tables  
and store

it in the data structure.


Your two last statements still seem contradictory to me, as to what  
you want to achieve. As per my first response, I don't understand  
whether you want to search one corresponding field across four  
databases, or search one at a time, using the result as the criteria  
for the next one.


Please give us an example.

Thanks,
Tom


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

[sqlite] Re: Re: Meta Information: How to retrieve the column names of a table ?

[Igor Tandetnik]

Stef Mientki <[EMAIL PROTECTED]> wrote:

PRAGMA table_info(table-name);


And it also works for views, as just found out by trial and error ;-)
Is this standard SQL behavior ?


PRAGMA statement is not part of standard SQL. I don't believe there is a 
standardized way to access metadata. Every DBMS provides its own syntax.


Igor Tandetnik 



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

[sqlite] to quote or not ?

[Stef Mientki]

I've a problem with finding the correct algorithm for quoting.

When I've a view in the database, where 2 tables have the same field 
names "PatNr",

doing a pragma to get the fields of the view, I get:
 PatNr
 PatNr:1
For quering the data from that view, I don't ask for the view, but 
generate my own normal select

(that is because I want to reorder the fields, enable/disable fields etc)
and in that case the PatNr:1 is causing a problem.
So I thought I solved that, by double-quoting PatNr:1,
and indeed it works.

So I would expect that I now can always double quote the selection fields,
but unfortunately that doesn't seem to be true.

From a graphical design, I get for instance:

SELECT "Opnamen.PatNr", "Opnamen.Datum"
 FROM Patient
 LEFT JOIN Opnamen
   ON Opnamen.PatNr = Patient.PatNr

which doesn't work,
removing the double quotes here does work.

So I can think of the following solutions:
1. never double quote selection fields, unless there's a ':' or a space 
in the name

2. always double quote selection fields, unless there's a '.' in it

Which one is the most general, or are there even better ways ?

Thanks,

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

[sqlite] API enhancement

[Ken]

Question for the list,
 
 I'd like to optimize my code, using the following pseudo code as an example.
 
 ===
 int i = 0 ;
 char str[20];
 
 sqlite3_prepare_v2( "insert into t1 values (?,?)" )
 sqlite3_bind_int ( i )
 sqlite3_bind_text(str)
 
BEGIN TRANSACTION
 For (i = 0; i < 10; i++) {
   sqlite3_step (  );
   sqlite3_reset( )
 }
  COMMIT TRANSACTION
 ==
 
 However, the above code will fail to insert the values for i in the loop. It 
will only insert the value 0, since that was the binding value...
 
 An enhancement request would be to allow the user to bind the address to the 
statement  objects. This would be a huge benefit from the standpoint of fewer 
function calls to sqlite3_bind in the inside loop.
 
 So maybe the following API:
 
 sqlite3_pbind_int(sqlite3_stmt *, int, int * );
 sqlite3_pbind_int64(sqlite3_stmt *, int,  long long int * );
 sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
 sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *, void(*)(void*));
 notice the text takes a pointer to the length...
 sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *, void(*)(void*));
 
 Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is 
SQLITE_STATIC.
 
 Regards,
 Ken
 
 

Re: [sqlite] Re: Re: Meta Information: How to retrieve the column names of a table ?

[Dennis Cote]

Igor Tandetnik wrote:


I don't believe there is a standardized way to access metadata.


Igor,

The SQL:1999 and later standards define a standardized Definition Schema 
and Information Schema to allow users to get the meta information from a 
database.


From the SQL:1999 standard:

The views of the Information Schema are viewed tables defined in terms 
of the base tables of the
Definition Schema. The only purpose of the Definition Schema is to 
provide a data model to support
the Information Schema and to assist understanding. An 
SQL-implementation need do no more
than simulate the existence of the Definition Schema, as viewed through 
the Information Schema

views.

I think it should be possible to create a subset of the standard 
information schema in sqlite using virtual tables.


Dennis Cote

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

[sqlite] Re: to quote or not ?

[Igor Tandetnik]

Stef Mientki <[EMAIL PROTECTED]> wrote:

So I would expect that I now can always double quote the selection
fields, but unfortunately that doesn't seem to be true.

From a graphical design, I get for instance:

SELECT "Opnamen.PatNr", "Opnamen.Datum"


Opnamen, PatNr and Datum are three separate identifiers, and have to be 
quoted separatedly (if at all), as in "Opnamen"."PatNr". "Opnamen.PatNr" 
is a single identifier, distinct from Opnamen.PatNr (which is two 
identifiers separated by period).


To illustrate, consider these valid SQL statements:

create table Opnamen (PatNr, "Opnamen.PatNr");
insert into Opnamen values (1, 2);
select
   Opnamen.PatNr, "Opnamen"."PatNr",
   "Opnamen.PatNr", Opnamen."Opnamen.PatNr"
from Opnamen;

The last query should return a single row with values (1, 1, 2, 2)

Igor Tandetnik 



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

[sqlite] Schema

[Shilpa Sheoran]

All,
Are there database schema's (eg. for Phonebook ) available  on the net?

Thanks
Shilpa

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

Re: [sqlite] database is locked error on Mac OS X

[T]

Hi Richard and Puneet,

I just wanted to say thanks, and to record your combined advice that  
worked. Much of this may be superfluous or painfully obvious, but it  
worked:


1. In the sqlite-3.3.13 downloaded source directory, execute:

./configure

2. That creates a new file "MakeFile". Edit that file in any text  
editor, and add these lines:


# flag to deal with Mac OS X file locking on shared drives
TCC += -DSQLITE_ENABLE_LOCKING_STYLE=1

I put them just before the line:

# You should not have to change anything below this line

3. Execute:

sudo make
sudo make install

Done.

You can now run/test the new sqlite3 command line tool by executing:

/usr/local/bin/sqlite3

which will show:

SQLite version 3.3.13
Enter ".help" for instructions

Editing a database file on a remote volume now works with this  
modified latest version.


Thanks again.
Tom


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

Re: [sqlite] notice: embedded system and locked database

[Jakub Ladman]

Dne pondělí 12 březen 2007 18:04 Martin Jenkins napsal(a):
> Jakub Ladman wrote:
> > Problem is, that this is pretty obscure system. Renesas SuperH SH4
> > CPU Heavily patched 2.4.18 kernel. (patches will be presented on
> > internet, but not at this time) Gentoo-embedded linux, based on
> > uclibc 0.9.28 and busybox . Main storage is SD flash card.
> >
> > I must confess, that i do not know, how to check the nfs version. (it
> > is binary distributed kernel and i have not the actual .config of it)
> > Dmesg shows it not.
>
> Hmm. I see your problem. 2.4.18 is quite old but if it's been patched
> about... It's possible to build the .config into the Linux kernel so
> that it appears under /proc (/proc/config.gz?) - I don't suppose they've
> done that?
>
> Martin

I will try it.
Yes i know, that this kernelis old, but our hardware and drivers supplier does 
not support any newer one :-(
It should be better in near future, i hope, but not at this time.

Jakub Ladman

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

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