[sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

The commented out lines work. I'm wondering... a) is it possible to do what's not commented out b) what's the syntax re the "sql =..." and "sql +=..." lines Any help much appreciated! [code] //sql = "create table episodes (id integer primary key, season int, name text);"; rc =

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

; sql += "insert into episodes(id, season, name) Values(1,2,'bill');"; > sql += "insert into episodes(id, season, name) Values(2,3,'bob');"; > sql += "COMMIT;"; > > I would say most people don't do this as any error returns won't tell you > much. You'd normally prepare each

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

vid Bicking <dbic...@yahoo.com> wrote: > On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote: > > The commented out lines work. > > I'm wondering... > > a) is it possible to do what's not commented out > > b) what's the syntax re the "sql =..." and "sql +=.

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

Thank you Igor On 21 June 2011 12:52, Igor Tandetnik <itandet...@mvps.org> wrote: > e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > > Howto...multi-sqlite command string through sqlite3_prepare_v2() to > create SINGLE statement > > You can't do that. >

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

David, Simon...that's good to know...Thank you very much indeed! On 21 June 2011 13:19, Simon Slavin <slav...@bigfraud.org> wrote: > > On 21 Jun 2011, at 12:59pm, e-mail mgbg25171 wrote: > > > I was looking at prepare/step/finalise as a means of avoiding the &g

[sqlite] Ensure that query acts on PRE-SORTED tables

I know that ORDER BY sorts result but I want to sort a table BEFORE it gets queried and am not sure of the syntax. Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y (by column pos) BEFORE I do the SELECT BETWEEN on THEM i.e. I purposefully inserted t_d row 1,1,1 to see

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Thank you all for your responses. I had to go out after posting and have just come back. My concern is with... SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' and SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'. t_x and t_y are dimension tables. that hold the x and y margins of a

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

not rowid. > > So, can you add "ORDER BY pos" to your queries? > > > Pavel > > > On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171 > <mgbg25...@blueyonder.co.uk> wrote: > > Thank you all for your responses. > > I had to go out after posting and

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

> > I don't understand what you are talking about here. You should write > it like this: > > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos; > > > Pavel > > > On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171 > <mgbg25...@blueyonder.co.uk&g

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

gt; result of a query differs depending on what order SQLite processes > rows in then you are wrong. > > > Pavel > > > On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171 > <mgbg25...@blueyonder.co.uk> wrote: > > I'll certainly try > >>SELECT pos FROM t_x

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

BETWEEN when you're looking at x1 > and > > x2 from the pos order perspective > > Then David's query is a way to go. > > > Pavel > > > On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171 > <mgbg25...@blueyonder.co.uk> wrote: > > Pavel, David > &g

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

ot;pos BETWEEN txt = 1990 and 1991" would return 1 2 and this is what I want. On 1 July 2011 18:24, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > Here's an example of what I'm trying to do with my query > t_x > rowid=1,pos=1, txt=1990 > rowid=2,pos=3, txt=1992 >

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

> If you believe that result of a query differs depending on what order SQLite processes rows in then you are wrong. I am wrong! On 1 July 2011 18:38, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

2011 18:41, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > > If you believe that > result of a query differs depending on what order SQLite processes > rows in then you are wrong. > > I am wrong! > > > On 1 July 2011 18:38, e-mail mgbg25171 <mgbg25..

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Thank you Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multidimensional representation

The layout where x and y are dimensions eg x 12 y 1 | 3 4 2 | 5 6 might be represented the following fact table xid yid data 1 1 3 2 1 4 1 2 5 2 2 6 If I added another dimension eg yy then the following

[sqlite] multidimensional representation

Thanks for your response. I think your question is my point i.e. indeed...which one do you keep? It's a little ambiguous isn't it? I suppose it makes most sense to return back to the data BEFORE you added the yy dimension which result in returning to... xid yid data 1 1 3 2 1

[sqlite] multidimensional representation

Luuk Reading your email again...I think you've misunderstood me Each element of yy represent an instance of the WHOLE original array before the dimension was added. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] multidimensional representation

Thank you for your response I didn't quite catch what you mean't but would be happy to give examples of the app. A picture paints 1000 words Would it be ok to attach .png files of what I'm trying to do? ___ sqlite-users mailing list

Re: [sqlite] multidimensional representation

On 6 July 2011 15:39, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > I've done it in the email body > eg total income = north.total + south.total where north and south are from dimension "region" and sales, cogs are from dimension "line item" ie all I

Re: [sqlite] multidimensional representation

Simon only just saw your post so hope my image didn't come through. It didn't on mine but makes my post pretty meaningless. I'll try to do it in text ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] multidimensional representation

19901991 year <= dimension north sales108 cogs (5) (4) southsales 6 5 cogs (2) (1) regionline item <==

Re: [sqlite] multidimensional representation

Luuk, Roger Yes it is like a pivot table. I was stuck on how you delete dimensions you decide you don't want anymore. After a day thinking about it I think I've cracked it so... Thanks very much for your assistance. ___ sqlite-users mailing list

[sqlite] getting list of column names

Here's what I'm doing to get a list of column names as a first step in my "add column" routine. I see that limit 1 isn't limiting the output to the first row ie the column names... like it did when I used SQLitening. Is there a better way to do this? Any advice much appreciated. int

Re: [sqlite] getting list of column names

> > http://www.sqlite.org/pragma.html#pragma_table_info > I wasn't aware of that. Thank you very much! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help with query

Here are my tables specified as... tbl_nm | col1, col2... std_nms | id, nm raw_nms | id, nm nm_pairs | id, std_nms_id, raw_nms_id I'm wondering how to supply a single std_nms.nm and get back a list of pairs i.e. std_nm.nm, raw_nms.nm that reflect each record in nm_pairs with a std_nms_id =

[sqlite] help with my query...cracked it!

Orig question Here are my tables specified as... tbl_nm | col1, col2... std_nms | id, nm raw_nms | id, nm nm_pairs | id, std_nms_id, raw_nms_id I'm wondering how to supply a single std_nms.nm and get back a list of pairs i.e. std_nm.nm, raw_nms.nm that reflect each record in nm_pairs with a

[sqlite] help with query

Simon... Yes I forgot the "where sn.nm='std1';" restriction and... also see you've used 2 inner joins. Thank you very much for your thoroughness. It's very much appreciated. > > On 06 April 2016 at 12:41 Simon Davies > wrote: > > > On 6 April 201

[sqlite] finding duplicate records i.e. records with same values across 4 colums...

I'm using SQLIte. All columns can have duplicate values but it would be helpful to report on those rows which are identical across all columns. More specifically I'm looking for matching itm values where the first 3 cols ALSO match but am not sure of the sqlite select query to do this. epic, yr,

[sqlite] get records for foreign keys which come from various places

I've only just discovered that you can get rows for foreign keys doing this SELECT f.* FROM firms f inner JOIN calls c ON f.id = c.firm_id group by c.firm_id; that's fine where the foreign keys are from a single field in a single table (i.e. firm_id in calls) but what

[sqlite] just a test

I've posted a couple of mails lately...I'm not getting them via the list or any responses. Admin says Igor responded to one of them...Thanks Igor! This is just a test to see if the mail is coming to me (as a member of the list). Therefore please just ignore this.

[sqlite] just a test...please ignore

test ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another example of windows users needing help. Fwd: sqlite.dll

It's not easy being an incon On 4 December 2012 10:44, Richard Hipp wrote: > Here is another example of the kind of email I get on a regular basis. > > Note that I also get phone calls about this. Sometimes at odd hours. > > -- Forwarded message -- > From: Åke

[sqlite] just a test...ignore

just a test please ignore ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] tesing please ignore

please ignore ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test ignore

test ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test

please ignore ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ordering result sets

I am returning records if today is more than x days from lastdate or I have previously specified a next date and that date is today. Here's my query "select f.*, lastdate, nextdate from" & _ "(" & _ "SELECT f.*, lastdate, nextdate" & _ "FROM firms f" & _ "LEFT JOIN" & _ "

Re: [sqlite] ordering result sets

:39, Igor Tandetnik <i...@tandetnik.org> wrote: > e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > > I have modified my program to have next time (not shown) as well as next > > date > > What I'd to know is... > > How do I order results firstly by earles

[sqlite] ordering result sets

>order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''),f.lastdate This worked fine re making sure that non-null nexttimes come BEFORE null nexttimes. How would I extend this so that AFTER non-null nexttimes I get NON-NULL lasttimes and then... null nexttimes and lastimes in

Re: [sqlite] ordering result sets

FIRST ONE SEEMS COUNTER INTUITIVE AND I'D LIKE TO UNDERSTAND THIS BTW NULL = CHR$(0) vs '' = '' YES??? On 19 December 2012 13:42, Igor Tandetnik <i...@tandetnik.org> wrote: > e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > >> order by (f.nexttime is null or f

Re: [sqlite] ordering result sets

Igor >case when f.nexttime IS NOT NULL then 0 else 1 end explains it very well and I see that your alternative is indeed an elegant shortcut Additionally... thank you for putting me straight re NULL in SQL I didn;t appreciate that Your help is very much appreciated Dean On 19 December 2012

[sqlite] trying to exclude records which have a field that is null

wSQL = _ "Select f.* " & _ "from firms f " & _ "left join " & _ "(" & _ "select firm_id, max(by_or_on) as boo " & _ "from calls " & _ "group by firm_id " & _ "having by_or_on is not null " & _<==THIS ISN'T DOING IT FOR ME AND I'D LIKE TO KNOW

Re: [sqlite] trying to exclude records which have a field that is null

from calls > > group by firm_id > >having boo is not null) c > > on c.firm_id = f.id > > order by boo desc; > > > > but perhaps you really mean: > > > > Select f.* > >from firms f > > left join (select firm_i

Re: [sqlite] trying to exclude records which have a field that is null

I've done most of it with this select f.* from firms f inner join (select * from calls where by_or_on <> '') c on f.id = c.firm_id Phew! On 3 February 2013 09:40, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>wrote: > Keith, Petite > I'm really grateful for your assistanc

Re: [sqlite] trying to exclude records which have a field that is null

I need to test this but this is looking promising select f.* from firms f inner join (select firm_id, max(by_or_on) from calls where by_or_on <> '' group by firm_id order by by_or_on) c on f.id = c.firm_id What do you think On 3 February 2013 09:55, e-mail mgbg25171 <mgbg25...@blueyon

Re: [sqlite] trying to exclude records which have a field that is null

<petite.abei...@gmail.com> wrote: > > On Feb 3, 2013, at 10:55 AM, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> > wrote: > > > (select * from calls where by_or_on <> '') c > > For the record… one thing to watch out… the empty string (aka '') and null > are n

[sqlite] sorting two distinct groups

SELECT f.id FROM firms AS f WHERE f.id NOT IN (SELECT c1.firm_id FROM calls AS c1) OR f.id IS NULL union SELECT f2.id FROM firms AS f2 WHERE f2.id IN (SELECT c2.firm_id FROM calls AS c2) order by (c1.last is null, c2.last is not null) I have two tables firms and calls. I'd like to list all the

Re: [sqlite] sorting two distinct groups

an "order by clause" for the firms stuff I can't quite see how to insert it without again causing an error. Any advice greatfully received. Dean On 4 February 2013 03:40, Igor Tandetnik <i...@tandetnik.org> wrote: > On 2/3/2013 3:43 PM, e-mail mgbg25171 wrote: > >>

Re: [sqlite] sorting two distinct groups

This seems to have answered part of my problem SELECT id FROM firms f left outer join (select firm_id from calls group by firm_id) c on f.id = c.firm_id order by firm_id On 4 February 2013 07:40, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>wrote: > Igor > Thank you! > Your que

Re: [sqlite] sorting two distinct groups

the firms in order of EARLIEST of those "latest call" times first i.e. the firm you called longest ago, first I think your query does this and thank you very much for it On 4 February 2013 14:25, Igor Tandetnik <i...@tandetnik.org> wrote: > On 2/4/2013 2:40 AM, e-mail mgbg25171 wrote:

[sqlite] match on single column but with multiple value

Sorry if this is a very basic question but I'm just wondering if there's a more elegant way of doing this select * from tbl where col1 = 'a' or col1 = 'b' or col1 = 'c' i.e. selecting rows if a particular column has one of SEVERAL values. Any help much appreciated.

Re: [sqlite] match on single column but with multiple value

That's great Mike. Thanks very much! On 10 February 2013 20:26, Mike King <making1...@gmail.com> wrote: > Select * from tbl where col1 in ('a', 'b', 'c') > > > > On Sunday, 10 February 2013, e-mail mgbg25171 wrote: > > > Sorry if this is a very basic question but

[sqlite] help with select

Here's a chopped down query that demonstrates my difficulty do sql_s = "select * from tbl_tv where id in " & _ "(" & _ "select id from tbl_tv where " & _ "new_tnode = '' and " & _ "pnode = 0 or " & _

[sqlite] help with select

Perhaps I'm making a meal of my question... "All" I want to do is select only records that have field f1 = '' where it's other field f2 matches the value of f3 only in other records (1 or more) which have a non-'' f1 value. To explain the f1 field shows whether or not the record has been written

[sqlite] help with select

I think this does it sql_s = "select * from " & _ "(select * from tbl_tv a where new_tnode = '') " & _ "where pnode = 0 or pnode in " & _ "(select tnode from tbl_tv where new_tnode <> '' )" & _ "and anode = 0 or anode in " &

Re: [sqlite] help with select

Yes that works great. Thanks Igor! On 11 February 2013 22:24, Igor Tandetnik <i...@tandetnik.org> wrote: > On 2/11/2013 10:45 AM, e-mail mgbg25171 wrote: > >> Perhaps I'm making a meal of my question... >> "All" I want to do is >> select only records t

[sqlite] got selected items ok but can't update using them

After a bit of a struggle I've got the fields I want but I'm failing to use them in an update operation. This SELECT i,o,n FROM ( SELECT alias_id i, (SELECT orig_itm FROM stmnts where ID = alias_id) o, (SELECT itm FROM std_itms where ID = std_id) n FROM (SELECT std_id, alias_id FROM alias_itms) )

Re: [sqlite] got selected items ok but can't update using them

ntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] Thanks for your help On 20 February 2013 12:01, Simon Davies <simon.james.dav...@gmail.com>wrote: > On 20 February 2013 11:3

Re: [sqlite] got selected items ok but can't update using them

m input.t1; > sqlite> .dump > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE copy(n); > INSERT INTO "copy" VALUES(1); > INSERT INTO "copy" VALUES(2); > COMMIT; > sqlite> .dump input.t1 > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION

[sqlite] I asked for an email sending me a new password/letting me reset

I just got confirmation re sending me a new password/reset email but it hasn't arrived, Not being able to log in and not getting emails seems very strange ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] getting transactions working with prepare, step & finallise

Tbank you in anticipation for any forthcoming advice I include my program below in Powerbasic (not disimilar to C) and wonder what I need to do in order to "wrap" multiple statements (which use prepare,step & finalise) as transactions? #COMPILE EXE #DIM ALL #INCLUDE "sqlite3.inc" 'created 09 May

Re: [sqlite] getting transactions working with prepare, step & finallise

Simon Thanks very much for the direction On 12 June 2013 17:32, Simon Davies <simon.james.dav...@gmail.com> wrote: > On 12 June 2013 16:50, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> > wrote: > > Tbank you in anticipation for any forthcoming advice > &

[sqlite] unique id for table

I need to access a row just using numbers. The row id's fine but can I specify the table using a numeric id too? If not I'll just create a look-up table so there's no problem. I'm just wondering... Any help much appreciated. ___ sqlite-users mailing list

Re: [sqlite] unique id for table

Thanks very much Simon Looks like plan B then. On 22 June 2013 19:40, Simon Slavin <slav...@bigfraud.org> wrote: > > On 22 Jun 2013, at 7:27pm, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> > wrote: > > > I need to access a row just using numbers. > >

Re: [sqlite] unique id for table

f-list as it may not really be in the scope of the list (I > think) if your awaited reply prompts further discussion. > > Have a great day! > > On 2013/06/22 20:27, e-mail mgbg25171 wrote: > >> I need to access a row just using numbers. >> The row id's fine but can I speci

Re: [sqlite] unique id for table

org> wrote: > > On 23 Jun 2013, at 12:26pm, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> > wrote: > > > The "table as a number" idea is necessary to fit my model > > where word definitions are stored as streams of code pointers (NUMBERS) > &g