Re: [sqlite] Can I dynamically select a table?

I don't see how that helps. Let's say I have table with rules which determine from which view to select: create rules_table (condition integer, myview integer); insert into rules_table values (1,10); insert into rules_table values (2,20); insert into rules_table values (3,30); insert into

Re: [sqlite] Can I dynamically select a table?

John wrote: > So now I know that case can return column names. But is there a way to tell > it from which table to select. > Earlier I assumed that it is not possible. But know I requestioning > everything. > This, at least, has not worked: > > create table t1 (col integer);

Re: [sqlite] Can I dynamically select a table ?

I meant to attach some language that would give me more flexibility than straight sql. On Wed, May 11, 2011 at 10:35 PM, John wrote: > I've developed a mobile app that I want on all smartpnones (that means at > least 4 different platformorms/languages). I've managed to put

Re: [sqlite] Can I dynamically select a table ?

I've developed a mobile app that I want on all smartpnones (that means at least 4 different platformorms/languages). I've managed to put 80% of logic in sqlite db (which is on all smartphones). The idea is that the more I can put in sqlite the less I have to explain to four different programmer -

Re: [sqlite] Can I dynamically select a table ?

> I can't trully construct sql statement piece by piece with SQL > db as I did with Oracle. Just wanted to confirm. Why do you need to construct SQL specifically with db's tools? Why can't you do that in your host language? Oracle needs dynamic SQL feature because it will work much faster than

Re: [sqlite] Can I dynamically select a table ?

To be fair, I asked a theoritical question and created a very simple example for it. The real situation involves choosing dynamically from which view to select. I have very complicated db logic. I ended up asking the application developer to do just what you suggesting: implement a little piece of

[sqlite] Embedding in a VSEE2010 project?

I copied the following from the help file ("C:\Program Files\System.Data.SQLite\doc\SQLite.NET.chm But I don't see a SQLite.Net folder. Where do I find this INSTALL.EXE file? I'm using Visual Studio Express Edition 2010 (C++). TIA! Don Installing SQLite Visual Studio Design-Time Support

Re: [sqlite] Can I dynamically select a table ?

On Wed, May 11, 2011 at 9:03 PM, John wrote: > Yes, I could. But considering that I'm applying tons of logic and not just > selected this would be a real mess. Not even sure I could pull it. > Normalization was something I lacked with regard to previous post. But in > this

Re: [sqlite] Can I dynamically select a table ?

Yes, I could. But considering that I'm applying tons of logic and not just selected this would be a real mess. Not even sure I could pull it. Normalization was something I lacked with regard to previous post. But in this case, I don't think it has anything to do with it. It's just alack of dynamic

Re: [sqlite] Can I dynamically select a table ?

On Wed, May 11, 2011 at 8:47 PM, John wrote: > That would work if I needed to select a single column from a table. But if I > need to select multiple values (c1, c2), then it wouldn't work. Can't have > subquery with more than one column selected, in general, I think. You can

Re: [sqlite] Can I dynamically select a table ?

That would work if I needed to select a single column from a table. But if I need to select multiple values (c1, c2), then it wouldn't work. Can't have subquery with more than one column selected, in general, I think. select * from (select case when 1=1 then (select c1, c2 from t1) else

Re: [sqlite] Can I dynamically select a table ?

It won't work due to syntax. Try putting a select statement as the when part of the case statement. Woody wizard at large(I'm in shape. Round is a shape) Connected by MOTOBLUR™ on T-Mobile -Original message- From: John To: General Discussion of SQLite Database

[sqlite] Can I dynamically select a table?

So now I know that case can return column names. But is there a way to tell it from which table to select. Earlier I assumed that it is not possible. But know I requestioning everything. This, at least, has not worked: create table t1 (col integer); create table t2 (col integer); select * from

Re: [sqlite] Dynamic SQL for SQLite?

I tried using case myself as the first thing but it didn't work, I though I need dynamic sql for that. Didn't realize it's that simple - just the column name. On Wed, May 11, 2011 at 9:04 PM, John wrote: > yes, using Case worked for me too and that's exactly what I need

Re: [sqlite] Dynamic SQL for SQLite?

yes, using Case worked for me too and that's exactly what I need since I need only today's value. Thank you so much guys! My kudos to you, Igor :) On Wed, May 11, 2011 at 9:02 PM, John wrote: > too much logic > > > On Wed, May 11, 2011 at 9:01 PM, John

Re: [sqlite] Dynamic SQL for SQLite?

too much logic On Wed, May 11, 2011 at 9:01 PM, John wrote: > I should have done it like that (normal referential table). But now I'm at > the end of this project and too logic uses that table already. So perhaps > I'll refactor some day. > > > On Wed, May 11, 2011 at 8:56

Re: [sqlite] Dynamic SQL for SQLite?

I should have done it like that (normal referential table). But now I'm at the end of this project and too logic uses that table already. So perhaps I'll refactor some day. On Wed, May 11, 2011 at 8:56 PM, Igor Tandetnik wrote: > On 5/11/2011 8:52 PM, John wrote: > > here

Re: [sqlite] Dynamic SQL for SQLite?

On 5/11/2011 8:52 PM, John wrote: > here is the view: > > create view today_goal as > select 'monday' dow, monday_goal value from user_goals > union all > select 'tuesday' dow, tuesday_goal value from user_goals > union all > select 'wednesday' dow, wednesday_goal value from user_goals > union all

Re: [sqlite] Dynamic SQL for SQLite?

here is the view: create view today_goal as select 'monday' dow, monday_goal value from user_goals union all select 'tuesday' dow, tuesday_goal value from user_goals union all select 'wednesday' dow, wednesday_goal value from user_goals union all select 'thursday' dow, thursday_goal value from

Re: [sqlite] Dynamic SQL for SQLite?

On 5/11/2011 8:30 PM, Igor Tandetnik wrote: > select case strftime('%w', 'now') > when 0 then sunday_value > when 1 then monday_value > ... > else saturday_value > end > from seven_days; Make it select case cast(strftime('%w', 'now') as integer) ... -- or select

Re: [sqlite] Dynamic SQL for SQLite?

ok, I will try. Meanwhile I created the actual view that does what I need. (considering I have many mondays, tuesdays... I replaced UNION with UNION ALL) On Wed, May 11, 2011 at 8:44 PM, Mr. Puneet Kishor wrote: > > On May 11, 2011, at 7:37 PM, John wrote: > > > Igor, > >

Re: [sqlite] Dynamic SQL for SQLite?

On May 11, 2011, at 7:37 PM, John wrote: > Igor, > What you are suggesting will not work. You can only select values not > columns using case. > > > select case strftime('%w', 'now') > when 0 then sunday_value > when 1 then monday_value > ... > else saturday_value >

Re: [sqlite] Dynamic SQL for SQLite?

Are you asking if SQLite itself can figure out which day it is and retrieve the appropriate data? Don Ireland -Original Message- From: John To: sqlite-users@sqlite.org Sent: Wed, 11 May 2011 7:15 PM Subject: [sqlite] Dynamic SQL for SQLite? I am in situation where

Re: [sqlite] Dynamic SQL for SQLite?

On 5/11/2011 8:37 PM, John wrote: > What you are suggesting will not work. You can only select values not > columns using case. Will too. Try it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Dynamic SQL for SQLite?

Igor, What you are suggesting will not work. You can only select values not columns using case. select case strftime('%w', 'now') when 0 then sunday_value when 1 then monday_value ... else saturday_value end from seven_days; On Wed, May 11, 2011 at 8:30 PM, Igor

Re: [sqlite] Dynamic SQL for SQLite?

Nico, thanks a lot! This is awesome. create view today_dow as select monday_goal from user_goals union select tuesday_goal from user_goals union select wednesday_goal from user_goals union select thursday_goal from user_goals union select friday_goal from user_goals union select saturday_goal

Re: [sqlite] Dynamic SQL for SQLite?

On 5/11/2011 8:14 PM, John wrote: > I am in situation where I need to keep as much logic as possible within > SQLite. However the query that I need to perform does not seem to be > possible to perform. > > let's say I have a table with columns for each day of the week > > create table seven_days

Re: [sqlite] Dynamic SQL for SQLite?

Wonderful. The other answer is that one probably should not have a table with seven columns and one row when one could have a table with two columns (day of week and value) and seven rows. Like the view you are suggesting. --David Nico Williams writes: > On May 11, 2011 7:14 PM, "John"

Re: [sqlite] Dynamic SQL for SQLite?

On May 11, 2011 7:14 PM, "John" wrote: > let's say I have a table with columns for each day of the week > > create table seven_days > (monday_value integer, > tueday_value integer, > wednesday_value integer, > ... ); > > I want to select value from whatever day it is

[sqlite] Dynamic SQL for SQLite?

I am in situation where I need to keep as much logic as possible within SQLite. However the query that I need to perform does not seem to be possible to perform. let's say I have a table with columns for each day of the week create table seven_days (monday_value integer, tueday_value integer,

[sqlite] Can you add rows to fts virtual table?

Some basic questions: I have been creating the fts virtual table at the start of my program like this: CREATE VIRTUAL TABLE ItemFts USING FTS3(ItemId, ItemName, Description); INSERT INTO ItemFts (ItemId, ItemName, Description) SELECT ItemId, ItemName, Description FROM Item"; As rows are added

[sqlite] SQLite & Visual Studio 2010 (C++)

I copied the following from the help file ("C:\Program Files\System.Data.SQLite\doc\SQLite.NET.chm But I don't see a SQLite.Net folder. Where do I find this INSTALL.EXE file? I'm using Visual Studio Express Edition 2010 (C++). Thanks. Don Installing SQLite Visual Studio Design-Time

Re: [sqlite] SQLITE return codes for insert/delete/update/select

>I agree with what you stated but it would have been more clearer if >the result of the update statement was a "RECORD NOT FOUND" return >value since it did not find any that met the query's criteria. How can >you say that the UPDATE was successful when the record you were >looking for does

Re: [sqlite] SQLITE return codes for insert/delete/update/select

On Wed, May 11, 2011 at 10:27 PM, Igor Tandetnik wrote: > See also: http://en.wikipedia.org/wiki/Vacuous_truth > Thanks for that - that's a new term for me. i think the relevant bit of that article in this context is: -- Thus we say S is vacuously true; it is true, but

Re: [sqlite] SQLITE return codes for insert/delete/update/select

On 5/11/2011 4:21 PM, cricketfan wrote: > How can you say that the UPDATE was successful when the > record you were looking for does not even exist in the DB? Your statement says: update all records that meet condition X. SQLite did exactly that. How is this not a success? A failure would be if

Re: [sqlite] SQLITE return codes for insert/delete/update/select

Igor Tandetnik wrote: > > On 5/11/2011 3:52 PM, cricketfan wrote: >> 1. Return code for an UPDATE/DELETE query (ignoring other error >> conditions) >> will be SQLITE_DONE regardless of the fact whether UPDATE succeeded or >> failed. The only way to know whether the UPDATE succeeded or not is

Re: [sqlite] SQLITE return codes for insert/delete/update/select

On 5/11/2011 3:52 PM, cricketfan wrote: > 1. Return code for an UPDATE/DELETE query (ignoring other error conditions) > will be SQLITE_DONE regardless of the fact whether UPDATE succeeded or > failed. The only way to know whether the UPDATE succeeded or not is to use > sqlite3_changes() to

[sqlite] SQLITE return codes for insert/delete/update/select

I dont know if it is just me but I find the return codes for SQL operation quite confusing. I am new to SQLITE, have learnt a few things and wanted to know if going in the correct direction, 1. Return code for an UPDATE/DELETE query (ignoring other error conditions) will be SQLITE_DONE

Re: [sqlite] Three questions

On Wed, May 11, 2011 at 12:18 PM, Martin Engelschalk wrote: > This question does not arise with SQLite, because parallel transaction > are not supported, as Igor and Pavel pointed out. > > However, consider this: If you have a unique constraint on a table like > in your

Re: [sqlite] Three questions

Martin, Thank you for great explanation. I think I understand it better now. :) Regards, Serge Martin Engelschalk писал(а) в своём письме Wed, 11 May 2011 23:18:13 +0600: > Hello, > > This question does not arise with SQLite, because parallel transaction > are not

Re: [sqlite] Three questions

> Therefore, the second insert fails on every database system i ever > encountered. Apparently you didn't encounter Oracle. In such situation Oracle freezes transaction B until transaction A is committed or rollbacked. After that it knows what to return to transaction B - error or success

Re: [sqlite] VS2010 Issue

Sorry I should have added that I converted the project to VS 2010. --- On Wed, 5/11/11, Kit Pat wrote: From: Kit Pat Subject: [sqlite] VS2010 Issue To: sqlite-users@sqlite.org Date: Wednesday, May 11, 2011, 12:56 PM Any help or direction is

[sqlite] VS2010 Issue

Any help or direction is appreciated.  I have a VS 2005 application using SQLite 1.0.66.0 and Net Framework 2.0.  I'm trying to take the applicaiton to a Windows 7 64 bit machine but not sure what I need to do to convert SQLite to use Net Framework 4.  Is this even possible and how?

Re: [sqlite] Three questions

Hello, This question does not arise with SQLite, because parallel transaction are not supported, as Igor and Pavel pointed out. However, consider this: If you have a unique constraint on a table like in your example, when should the database enforce it? To use your example and add a second

Re: [sqlite] Three questions

Simon Slavin писал(а) в своём письме Wed, 11 May 2011 22:31:54 +0600: > That would be purely for a free-format presentation of the entire data in a > row. That's ideal for a utility. Normally when you write a database > application you're specifically looking up some

Re: [sqlite] Three questions

> We're not saying there no place for '*', merely that it shouldn't be used unless you actually want '*'. And suppose there is very_large_blob_field you don't need right now. If you do a 'select *' it will be read from disk unnecessary even if you are not going to use it. That is reason enough

Re: [sqlite] Three questions

On 11 May 2011, at 4:24pm, Dagdamor wrote: > Earlier in this list, I noticed several replies generally saying "using > SELECT * FROM is a no-no, it's evil, you should never using that" etc. > > If you use fetching methods that give you associated (named) data, like > mysqli_fetch_assoc() or

[sqlite] SQL Error: Result Set closed (sqlite jdbc)

I'm getting a Result Set closed exception every time I hit a file which isn't in the database. Here's the java code: /* * To change this template, choose Tools | Templates * and open the template in the editor. */ package testsqlite; import java.io.File; import java.io.FileInputStream;

Re: [sqlite] Three questions

>> 00:01 Transaction A: BEGIN >> 00:02 Transaction B: BEGIN >> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay >> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate >> key' error! why??? > > I get SQLITE_BUSY "database is locked" at this point, as I would

Re: [sqlite] Three questions

On 5/11/2011 11:24 AM, Dagdamor wrote: > Hello. First of all, sorry for posting three completely different questions > into the same message ;) > > Question #1. I was working with transactions in InnoDB (MySQL) and noticed a > weird behavior. Consider we have a table: > > CREATE TABLE test (id

[sqlite] Three questions

Hello. First of all, sorry for posting three completely different questions into the same message ;) Question #1. I was working with transactions in InnoDB (MySQL) and noticed a weird behavior. Consider we have a table: CREATE TABLE test (id INT NOT NULL PRIMARY KEY); and two transactions

Re: [sqlite] iOS Mem Alloc Issue - sqlite3_extended_errcode

Thanks, Richard. I'm escalating this issue to Apple and will post the results when they come in. Joseph -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Monday, May 09, 2011 3:27 PM To: General Discussion

[sqlite] Do we have a concurrent file access problem on Windows?

Hi! First of all thank you for this excellent piece of software! Maybe we've got a problem which occurs when concurrent processes access a sqlite database on Windows. We used procmon from the sysinternals suite (sysinternals.microsoft.com) to trace our problem. It looks like a call to