Re: [sqlite] Control the scheme layout
Hello Delf, Look at the PRAGMA commands: http://www.sqlite.org/pragma.html#schema HTH Philipp If I understand your question, the FAQ may help: http://sqlite.org/faq.html#q9 Oops, I did not see this point. But the FAQ does not explain how to retreive the field names from a table. It deals with the indices only and I think indice != field. Thanks. -- Delf
Re: [sqlite] Testing for table existence?
Hi, How can I test for the existence of a single table in a SQLite database? I tried sqlite3_get_table with select * from persons to test for the existence of the table 'persons' and I had hoped the return value would be SQLITE_NOTFOUND in the case of a non-existent table. However, I got SQLITE_ERROR, which is less helpfull. Any hints? Regards, Olaf Hello Olaf You can access the sqlite_master table. http://www.sqlite.org/cvstrac/wiki?p=InformationSchema enjoy! Philipp
Re: [sqlite] How to get the schema
Is there another way to get the column schema besides parsing the original CREATE TABLE command? thx -Brett G. This message was sent using IMP, the Internet Messaging Program. Hello Brett You can access the sqlite_master table to get a list of tables. Something like: SELECT name FROM sqlite_master WHERE type='table'; http://www.sqlite.org/cvstrac/wiki?p=InformationSchema For each table then, use PRAGMA table_info(name); http://www.sqlite.org/pragma.html#schema There are as well information about indices available. HTH Philipp
Re: [sqlite] Problems with character '
Hi Guys I'm having some problems dealing with the character '. Sqlite thinks fair enough that it should terminate the string, which I would like it to avoid. Are there any solutions to my problems. Thanks, Niels Hello Niels try: sqlite CREATE TABLE t (id INTEGER, bez TEXT); sqlite INSERT INTO t (id, bez) values (1,'); sqlite INSERT INTO t (id, bez) values (2,); sqlite SELECT * FROM t; 1|' 2|' Does this already solve your problem, or do you need something in another context? Philipp
Re: [sqlite] Managing trees in the database
SanjayK schrieb: Since SQLite is perfect for use in single-user desktop utility applications and since such applications typically store hierarchial data (tree) in a single table, it would be nice to have support for special features like connect by of oracle. See: http://www.adp-gmbh.ch/ora/sql/connect_by.html -- View this message in context: http://www.nabble.com/Managing-trees-in-the-database-t113.html#a2974277 Sent from the SQLite forum at Nabble.com. Depending on your goals, this concept might give you another solution: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html As the code examples are for mysql, you need to adapt it a bit. HTH Philipp
[sqlite] Using variables within trigger definitions
Hello SQLite Users Is there a possibility to define variables within trigger definitions? I would like to to something like this (simplified example): CREATE TRIGGER Side_Insert AFTER INSERT ON Side BEGIN SET LOWDATE = SELECT MIN(Startdate) FROM Basis; INSERT INTO BASIS (Name,Startdate) VALUES (Trigger, LOWDATE); END; I know, there is the possibility to do the same with SUBSELECTS, but I'm looking for something easier/faster/more elegant than doing the same subselect several times. (as my real example is more complicated) Even if there is no possibility right know, I would probably suggest this as a feature for the future. Thanks a lot! Philipp
[sqlite] [OT] SQL: limit a query by sum(val)?
Hello SQLite-Users This is offtopic, but as I would like to use it within SQLite... so let's try: I would like to reduce the resultset of a query like using limit. But instead of using the number of records by the sum of values. A small example: create table t1 (val int, period text); insert into t1 values ( 5, '2001-3'); insert into t1 values (10, '2001-4'); insert into t1 values (15, '2002-1'); insert into t1 values ( 5, '2002-2'); insert into t1 values (10, '2002-3'); insert into t1 values (15, '2002-4'); insert into t1 values (10, '2003-1'); insert into t1 values ( 5, '2003-2'); insert into t1 values (25, '2003-3'); insert into t1 values ( 5, '2003-4'); select * from t1 where period '2003-1' order by period desc: -- val periodsum(val) -- --- -- 15 2002-4 15 -- 10 2002-3 25 -- 5 2002-2 30 -- 15 2002-1 45 -- 10 2001-4 -- 5 2001-3 I need only the first records to fulfill sum(val) = 40 (or all records if sum(val) 40) -- so the result should be limited to: -- val period -- --- -- 15 2002-4 -- 10 2002-3 -- 5 2002-2 -- 15 2002-1 Sure, there is always the way to reduce the resultset within an application. Is there a way of using a subselect? I asked google, the nearest term that comes to my mind is sliding window, but maybe someone of you knows much better. Your time is truly appreciated. Thank you! Kind regards Philipp
[sqlite] [OT] SQL: limit a query by sum(val)?
Hello SQLite-Users This is offtopic, but as I would like to use it within SQLite... so let's try: I would like to reduce the resultset of a query like using limit. But instead of using the number of records by the sum of values. A small example: create table t1 (val int, period text); insert into t1 values ( 5, '2001-3'); insert into t1 values (10, '2001-4'); insert into t1 values (15, '2002-1'); insert into t1 values ( 5, '2002-2'); insert into t1 values (10, '2002-3'); insert into t1 values (15, '2002-4'); insert into t1 values (10, '2003-1'); insert into t1 values ( 5, '2003-2'); insert into t1 values (25, '2003-3'); insert into t1 values ( 5, '2003-4'); select * from t1 where period '2003-1' order by period desc: -- val periodsum(val) -- --- -- 15 2002-4 15 -- 10 2002-3 25 -- 5 2002-2 30 -- 15 2002-1 45 -- 10 2001-4 -- 5 2001-3 I need only the first records to fulfill sum(val) = 40 (or all records if sum(val) 40) -- so the result should be limited to: -- val period -- --- -- 15 2002-4 -- 10 2002-3 -- 5 2002-2 -- 15 2002-1 Sure, there is always the way to reduce the resultset within an application. Is there a way of using a subselect? I asked google, the nearest term that comes to my mind is sliding window, but maybe someone of you knows much better. Your time is truly appreciated. Thank you! Kind regards Philipp
