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 insert your criteria here; 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 = initialValue; 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 = initialValue; Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] retrieving data from multiple tables
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: TB [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 insert your criteria here; 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 = initialValue; 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 = initialValue; 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
COS wrote: Hi, - Original Message - From: Stef Mientki [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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
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
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 numeric or blob rows in a single table. The current implementation does not allow INTEGERs or BLOBs in FTS
Re: [sqlite] retrieving data from multiple tables
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
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
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: TB [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
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
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
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
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
TB 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
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
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
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
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
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
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
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
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 ?
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
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, get_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(now); 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 = (select field2
[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, get_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(now); 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); //
Re: [sqlite] Format lf csv file RE: [sqlite] date/time implementation question
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
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, get_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
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. 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 ?
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 ?
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
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 ?
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 ?
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
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
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
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] -