Re: [sqlite] How to get the RecNo ???

[BareFeetWare]

select rowid, * from TestTable

Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

 
On 11/02/2013, at 11:23 AM, roystonja...@comcast.net wrote:

> After you do a retrieve from the database, how would to access the RecNo for 
> each record?  I can get to all the fields but I don't know how to access the 
> record number that sqlite creates when it creates your record.  I am not 
> looking for the last record number created.
> 
> I will be populating a ListView and I want to add the RecNo in the caption.
> 
> "SELECT * FROM TestTable"

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

Re: [sqlite] Saving ubyte[] zlib data in sqlite and calling it back

[jose isaias cabrera]

True...

"Simon Slavin" wrote...


On 12 Feb 2013, at 1:33am, jose isaias cabrera  
wrote:


have you thought of including these as part of the normal sqlite core 
functions?


I feel that zlib has nothing to do with SQL or databases.  It doesn't 
belong in a database library.


Oh yeah, that... 


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

Re: [sqlite] Saving ubyte[] zlib data in sqlite and calling it back

[Simon Slavin]

On 12 Feb 2013, at 1:33am, jose isaias cabrera  wrote:

> have you thought of including these as part of the normal sqlite core 
> functions?

I feel that zlib has nothing to do with SQL or databases.  It doesn't belong in 
a database library.

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

Re: [sqlite] Saving ubyte[] zlib data in sqlite and calling it back

[jose isaias cabrera]

"Richard Hipp" wrote...

On Mon, Feb 11, 2013 at 1:45 PM, jose isaias cabrera wrote:



Greetings.

I have this table,

CREATE TABLE Test (login primary key, password);

and I would like to save zlib data using ubyte[] and also call it back 
in.



Are you trying to store zlib-compressed content in the database file, then
read back the original uncompressed content?  If so, then I suggest adding
two application-defined functions compress() and decompress():

   UPDATE test SET password=compress(password) WHERE login='test';

   SELECT decompress(password) FROM test WHERE login='test';

Sample implementations for the compress() and decompress() functions can 
be

copied from here:

   http://www.fossil-scm.org/fossil/artifact/a59638aa4c0?ln=53-105

The code for registering these application-defined functions with the
SQLite database connection is seen here:

   http://www.fossil-scm.org/fossil/artifact/a59638aa4c0?ln=119-122


Dr. Hipp,

have you thought of including these as part of the normal sqlite core 
functions?


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

Re: [sqlite] help with select

[Igor Tandetnik]

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 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.


select * from MyTable where f1 = '' and f2 in
  (select f3 from MyTable where f1 != '');

--
Igor Tandetnik

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

Re: [sqlite] Saving ubyte[] zlib data in sqlite and calling it back

[Teg]

Hello jose,

I compress/encrypt to a vector, then write the vector as a blob and
reverse. Read the blob as a vector then reverse the process.

Monday, February 11, 2013, 1:45:34 PM, you wrote:


jic> Greetings.

jic> I have this table,

jic> CREATE TABLE Test (login primary key, password);

jic> and I would like to save zlib data using ubyte[] and also call
jic> it back in.  I have things like this:
jic> 1. To save in SQLite,
jic> cmd =
jic> "  UPDATE Test password = " ~
jic> cast(char[])cast(ubyte[])std.zlib.compress(cast(void[])"password")
jic> "WHERE login = 'test';"; 

jic> This works, or at least, it saves in SQlite, but when I call it back, it 
does not.

jic> 2. To call it in SQLite,

jic> char[] passWord =
jic> cast(char[])cast(ubyte[])std.zlib.uncompress(cast(void[])r[0]["password"]);

jic> where r[0]["password"] is what I just SELECTED from SQlite.  I
jic> am getting an UTF8 error.  This is a D program, so, the syntax
jic> may not be known to some or all of you.  However, the more
jic> realistic SQLite question would be, how can I save ubyte data in
jic> SQLite and also call it back.  Thoughts?  Comments?  Jokes? :-)

jic> thanks.

jic> josé

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




-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Saving ubyte[] zlib data in sqlite and calling it back

[jose isaias cabrera]

Richard Hipp wrote...


On Mon, Feb 11, 2013 at 1:45 PM, jose isaias cabrera
wrote:



Greetings.

I have this table,

CREATE TABLE Test (login primary key, password);

and I would like to save zlib data using ubyte[] and also call it back
in.



Are you trying to store zlib-compressed content in the database file, then
read back the original uncompressed content?  If so, then I suggest adding
two application-defined functions compress() and decompress():

   UPDATE test SET password=compress(password) WHERE login='test';

   SELECT decompress(password) FROM test WHERE login='test';

Sample implementations for the compress() and decompress() functions can
be
copied from here:

   http://www.fossil-scm.org/fossil/artifact/a59638aa4c0?ln=53-105

The code for registering these application-defined functions with the
SQLite database connection is seen here:

   http://www.fossil-scm.org/fossil/artifact/a59638aa4c0?ln=119-122


Dr. Hipp, I don't care what Kees or Igor say about you.  You're pretty hip.
This is perfect, thanks.

josé

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

Re: [sqlite] Saving ubyte[] zlib data in sqlite and calling it back

[Richard Hipp]

On Mon, Feb 11, 2013 at 1:45 PM, jose isaias cabrera
wrote:

>
> Greetings.
>
> I have this table,
>
> CREATE TABLE Test (login primary key, password);
>
> and I would like to save zlib data using ubyte[] and also call it back in.


Are you trying to store zlib-compressed content in the database file, then
read back the original uncompressed content?  If so, then I suggest adding
two application-defined functions compress() and decompress():

UPDATE test SET password=compress(password) WHERE login='test';

SELECT decompress(password) FROM test WHERE login='test';

Sample implementations for the compress() and decompress() functions can be
copied from here:

http://www.fossil-scm.org/fossil/artifact/a59638aa4c0?ln=53-105

The code for registering these application-defined functions with the
SQLite database connection is seen here:

http://www.fossil-scm.org/fossil/artifact/a59638aa4c0?ln=119-122




>  I have things like this:
> 1. To save in SQLite,
> cmd =
> "  UPDATE Test password = " ~
> cast(char[])cast(ubyte[])std.zlib.compress(cast(void[])"password") ~
> "WHERE login = 'test';";
>
> This works, or at least, it saves in SQlite, but when I call it back, it
> does not.
>
> 2. To call it in SQLite,
>
> char[] passWord =
> cast(char[])cast(ubyte[])std.zlib.uncompress(cast(void[])r[0]["password"]);
>
> where r[0]["password"] is what I just SELECTED from SQlite.  I am getting
> an UTF8 error.  This is a D program, so, the syntax may not be known to
> some or all of you.  However, the more realistic SQLite question would be,
> how can I save ubyte data in SQLite and also call it back.  Thoughts?
>  Comments?  Jokes? :-)
>
> thanks.
>
> josé
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Saving ubyte[] zlib data in sqlite and calling it back

[jose isaias cabrera]

Greetings.

I have this table,

CREATE TABLE Test (login primary key, password);

and I would like to save zlib data using ubyte[] and also call it back in.  I 
have things like this:
1. To save in SQLite,
cmd =
"  UPDATE Test password = " ~ 
cast(char[])cast(ubyte[])std.zlib.compress(cast(void[])"password") ~
"WHERE login = 'test';"; 

This works, or at least, it saves in SQlite, but when I call it back, it does 
not.

2. To call it in SQLite,

char[] passWord = 
cast(char[])cast(ubyte[])std.zlib.uncompress(cast(void[])r[0]["password"]);

where r[0]["password"] is what I just SELECTED from SQlite.  I am getting an 
UTF8 error.  This is a D program, so, the syntax may not be known to some or 
all of you.  However, the more realistic SQLite question would be, how can I 
save ubyte data in SQLite and also call it back.  Thoughts?  Comments?  Jokes? 
:-)

thanks.

josé

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

Re: [sqlite] Set Mode to HTML via VBScript

[Kees Nuyt]

On Mon, 11 Feb 2013 05:42:17 -0800 (PST), Jeff Steffanina
 wrote:

> 
>Hello All,
>During a sqlite3 session, I need to APPEN output to a FILE in the HTML mode.
> 
>Here is my SELECT:  Select * from Booking;
> 
>What I really want to say is Set the mode, Execute select and append to 
>MySummary:
> 
>mode html    Select * from Booking >> MySummary.html


Two oneliners (possibly wrapped in transmission)

1)
printf ".mode html\nSELECT * FROM Booking;\n"|sqlite3 Mydb >>My.html


2)
sqlite3 -html Mydb "SELECT * FROM Booking;" >>My.html

Try: 
sqlite3 -help 
for more options.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] help with select

[e-mail mgbg25171]

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 " & _
"(select tnode from tbl_tv where new_tnode <> ''  )"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help with select

[e-mail mgbg25171]

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 to
a treeview or not i.e. non-'' means it has been written
and let's say that f2 is the parent node...and f3 is the old node handle
for one of the nodes just written.

This is probably a better summary of what I'm trying to do.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help with select

[e-mail mgbg25171]

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 " & _
"pnode in (select tnode from tbl_tv where new_tnode <>
'')"  & _
")limit 1;"
exit loop when you've got no more records with an empty new_tnode
loop

I'm trying to draw a treeview using a stored table
i.e.
CREATE TABLE tbl_tv(
idinteger primary key,
pnode  text,
anode  text,
tnode  text,
new_tnode  text,
txttext,
cnodes text)

pnode is parent node
anode is after node...ie node that comes before the one your going to draw
tnode is old this node
new_tnode is handle of treeview node you've JUST drawn
txt is label of treeview node
cnodes are old handles of child nodes

All the old node handles are useful because they show the tree structure

If I limit the query to
sql_s = "select * from tbl_tv where id in " & _
"(" & _
"select id from tbl_tv where " & _
"new_tnode = '' " & _
) limit 1;"

and write a 1 in each returned rec's new_tnode then I can cycle through all
nodes fine.

Unfortunately the more complete query at the top seems to keep returning
the first non-0 parent, non-0 after node record even though it's got a 1 in
the new_tnode field which I THOUGHT I'd precluded i.e. only want to return
records which have a '' newtnode but which have pnode and anode values that
match tnodes in records which have a new_tnode of 1.

Hope I explained my self

Heres the full procedure just in case it helps

SUB aaTbl_to_tv(frm$,tv_id&)
DEF_SQL_VARS
LOCAL i&, hTv&, ub&
LOCAL form$, props$
LOCAL t AS tTv_rec
form$  = gTv.form
props$ = gTv.props
sqlite3_open("db_spread",sql_h???)
DO
sql_s = "select * from tbl_tv where id in " & _
"(" & _
"select id from tbl_tv where " & _
"new_tnode = '' and "  & _
"pnode = 0 or " & _
"pnode in (select tnode from tbl_tv where new_tnode <>
'')"  & _
")limit 1;"
'"and " & _
'"pnode = 0 " & _
'"or " & _
'"pnode in " & _
'"(select tnode from tbl_tv where new_tnode <> '') " & _
'"and " & _
'"anode = 0 " & _
'"or " & _
'"anode in " & _
'"(select tnode from tbl_tv where new_tnode <> '') " & _
'") limit 1;"
do_it

IF UBOUND(sql_a) <> -1 THEN
aaSql_to_tTv_rec(sql_a(),t)

sql_s = "UPDATE tbl_tv SET new_tnode=fld_val WHERE id=id_val;"
REPLACE "fld_val" WITH $SQ & "1" & $SQ IN sql_s
REPLACE "id_val" WITH t.id IN sql_s
DO_IT

'aaTest_tTv_rec(t)

'now find new parent and after nodes
IF t.pnode <> "0" THEN
sql_s = "select new_tnode from tbl_tv " & _
"where tnode = tnode_val;"
REPLACE "tnode_val" WITH t.pnode IN sql_s
do_it
IF UBOUND(sql_a) THEN
? t.lbl & " setting parent to " & sql_a(1)
t.pnode = sql_a(1)
ELSE
? t.lbl & " parent is 0"
END IF
ELSE

END IF

IF t.anode <> "0" THEN
sql_s = "select new_tnode from tbl_tv " & _
"where tnode = tnode_val;"
REPLACE "tnode_val" WITH t.anode IN sql_s
do_it
IF UBOUND(sql_a) THEN
t.anode = sql_a(1)
? t.lbl & " setting t.anode to " & sql_a(1)
ELSE
? t.lbl & "no rec returned for anode"
END IF
ELSE
?  t.lbl & "changing t.anode from 0 to " & t.pnode
t.anode = t.pnode
END IF

hTV& = EZ_AddTVItem( _
form$, gTv.tv_id,  _
VAL(t.pnode), VAL(t.pnode), BYCOPY t.lbl, _
0,0,"" _
)
ELSE
? "no more blank new_tnode recs",,FUNCNAME$
EXIT DO
END IF
LOOP
sqlite3_close(sql_h???)
END SUB
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the RecNo ???

[Simon Slavin]

On 11 Feb 2013, at 3:01pm, Clemens Ladisch  wrote:

> Igor Tandetnik wrote:
>> INTEGER PRIMARY KEY simply introduces a named alias for ROWID; its
>> presence doesn't make anything possible that wasn't already possible
>> without it.
> 
> If the ROWID isn't 'officially' made part of the table's columns by
> declaring some INTEGER PRIMARY KEY, then it can be renumbered by the
> next VACUUM.

Hmm.  In which case you'd still get the rows in the same order, but the values 
reported for one of the columns could change.  Contra-intuitive.

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

Re: [sqlite] How to get the RecNo ???

[Clemens Ladisch]

Igor Tandetnik wrote:
> INTEGER PRIMARY KEY simply introduces a named alias for ROWID; its
> presence doesn't make anything possible that wasn't already possible
> without it.

If the ROWID isn't 'officially' made part of the table's columns by
declaring some INTEGER PRIMARY KEY, then it can be renumbered by the
next VACUUM.


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

Re: [sqlite] How to get the RecNo ???

[Igor Tandetnik]

On 2/10/2013 11:55 PM, Mohit Sindhwani wrote:

I just meant to say that ROWID is not a sequence number of insertion in
the case when an INTEGER PRIMARY KEY is used  - it comes across as a
sequence number when we don't have an integer primary key.


I'm pretty sure (but too lazy to check) that you can write something like

insert into MyTable (ROWID, OtherField) values (42, 'xyz');

and mess up your ROWID this way, if you are so inclined. INTEGER PRIMARY 
KEY simply introduces a named alias for ROWID; its presence doesn't make 
anything possible that wasn't already possible without it.

--
Igor Tandetnik

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

Re: [sqlite] How to get the RecNo ???

[Igor Tandetnik]

On 2/11/2013 6:51 AM, Simon Slavin wrote:


On 11 Feb 2013, at 3:45am, Igor Tandetnik  wrote:


On 2/10/2013 10:06 PM, Mohit Sindhwani wrote:


* You decide then to do a sort by ROWID ASC - expecting that ROWID is
maintaining the sequence since when you do a general SELECT * from
mytable; you often get it in insertion sequence (if I'm not wrong).


You are wrong. Normally, with a simple SELECT like this, you get the results 
ordered by ROWID. SQLite table is stored as a b-tree, with ROWID as its key, so 
that's the natural order of traversal.


You're both wrong.  Because someone may have done "PRAGMA reverse_unordered_selects 
= YES"


That's why I said "normally". Yes, there are certain unusual 
circumstances where the claim is not true.



As I'm sure you know, the SQL standard makes no assurances about any part of 
SELECT row order you don't specify.  Not only that, but even if you do two 
identical SELECT statements in a row, you may get the rows in a different 
order.  I only ever found one setup which did this (one where the database was 
distributed over different computers, and the order returned depended on which 
host was free at the time), but I wanted to flag it up as a possibility.


Again, my statement is about what you would normally expect from SQLite 
in particular, not what may generally happen in some hypothetical SQL 
implementation.

--
Igor Tandetnik

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

Re: [sqlite] Set Mode to HTML via VBScript

[Simon Slavin]

On 11 Feb 2013, at 1:42pm, Jeff Steffanina  wrote:

> During a sqlite3 session, I need to APPEN output to a FILE in the HTML mode.
>  
> Here is my SELECT:  Select * from Booking;
>  
> What I really want to say is Set the mode, Execute select and append to 
> MySummary:
>  
> mode htmlSelect * from Booking >> MySummary.html
>  
> Any help is greatly appreciated.

I don't think you can do the 'append mode' within the shell tool, but you can 
do it within your commandline shell (probably using '>>' as you did above) and 
tell the shell tool to prepare a text file which becomes part of your HTML 
file.  See the '.output' command in



So you would have a set of commands to the shell like

.mode html
.output SqlTable.txt
SELECT * FROM Booking;
.output stdout

and then in your shell script you could have

cp SummaryStart.txt > MySummary.html
cp SqlTable.txt >> MySummary.html
cp SummaryEnd.txt >> MySummary.html

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

Re: [sqlite] MIN(x), MAX(x) confusion

[Stephan Beal]

On Mon, Feb 11, 2013 at 2:52 PM, Michael Black  wrote:

> Probably because you're storing your numbers as text.  How are you
> inserting
> them?
> Do a .dump from the shell and you'll see it in the record dump.
>
> So '7' > '20' but 7 < 20
>

Indeed, i just added type affinities to the schema and it now behaves as i
would expect. Thank you (and Richard) for the tip!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MIN(x), MAX(x) confusion

[Richard Hipp]

On Mon, Feb 11, 2013 at 8:46 AM, Stephan Beal  wrote:

> Hiho,
>
> Summary: i'm getting an incorrect/unexpected MIN()/MAX() results and have
> no clue why. After running these results by a colleague, we're both
> confused, and thus this post. Here's my data (JMeter output) and results:
>

Is is possible that some of the [elapsed] values are strings instead of
integers?  What does this say:

   SELECT DISTINCT typeof(elapsed) FROM res WHERE label='access service';



>
> SQLite version 3.7.16 2013-01-03 19:34:46
> (built from sources updated an hour or so ago)
> ...
> sqlite> .header on
> sqlite> select * from res where label='access service';
> ts,elapsed,label,rc,msg,thread,dataType,success,bytes,latency
> 1360586493370,27,access service,200,OK,Thread Group 1-1,text,true,4379,27
> 1360586494947,20,access service,200,OK,Thread Group 1-2,text,true,4378,20
> 1360586496927,6,access service,200,OK,Thread Group 1-3,text,true,4378,6
> 1360586499102,5,access service,200,OK,Thread Group 1-4,text,true,4378,5
> 1360586501135,6,access service,200,OK,Thread Group 1-5,text,true,4378,6
> 1360586503112,5,access service,200,OK,Thread Group 1-6,text,true,4378,5
> 1360586505116,7,access service,200,OK,Thread Group 1-7,text,true,4378,7
> 1360586507133,6,access service,200,OK,Thread Group 1-8,text,true,4378,6
> 1360586509333,6,access service,200,OK,Thread Group 1-9,text,true,4377,6
> 1360586511238,6,access service,200,OK,Thread Group 1-10,text,true,4378,6
> 1360586513321,6,access service,200,OK,Thread Group 1-11,text,true,4378,6
> 1360586515244,7,access service,200,OK,Thread Group 1-12,text,true,4378,7
> 1360586517295,6,access service,200,OK,Thread Group 1-13,text,true,4378,6
> 1360586519255,6,access service,200,OK,Thread Group 1-14,text,true,4378,6
> 1360586521261,5,access service,200,OK,Thread Group 1-15,text,true,4378,5
> 1360586523329,6,access service,200,OK,Thread Group 1-16,text,true,4378,6
> 1360586525316,6,access service,200,OK,Thread Group 1-17,text,true,4377,6
> 1360586527325,6,access service,200,OK,Thread Group 1-18,text,true,4378,6
> 1360586529301,6,access service,200,OK,Thread Group 1-19,text,true,4378,6
> 1360586531308,6,access service,200,OK,Thread Group 1-20,text,true,4378,6
>
>
> Notice that the min/max "elapsed" values are 5 and 27, respectively. Now my
> results (which i cannot explain)...
>
> sqlite>  SELECT label, count(*), MIN(elapsed), AVG(elapsed), MAX(elapsed)
> FROM res where label='access service';
> label,count(*),MIN(elapsed),AVG(elapsed),MAX(elapsed)
> access service,20,20,7.7,7
>
> sqlite>  SELECT label, count(*), MIN(elapsed), AVG(elapsed), MAX(elapsed)
> FROM res where label='access service' GROUP BY label;
> label,count(*),MIN(elapsed),AVG(elapsed),MAX(elapsed)
> access service,20,20,7.7,7
>
> sqlite> select count(*) from res where label='access service';
> count(*)
> 20
>
> sqlite> select MIN(elapsed) from res where label='access service';
> MIN(elapsed)
> 20
>
> sqlite> select AVG(elapsed) from res where label='access service';
> AVG(elapsed)
> 7.7
>
> sqlite> select MAX(elapsed) from res where label='access service';
> MAX(elapsed)
> 7
>
> sqlite> select label, MAX(elapsed) from res group by label;
> label,MAX(elapsed)
> access service,7
> autocomplete: choose LeadSource,70
> autocomplete: choose LeadType,79
> ...
>
> sqlite> select MIN(elapsed), MAX(elapsed) from res where label='access
> service';
> MIN(elapsed) MAX(elapsed)
> 20 7
>
>
> i'm very confused.  Surely i'm doing something wrong here (as i refuse to
> believe that this is an sqlite3 bug)?
>
> :-?
>
> PS: i'm not an sql guru.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MIN(x), MAX(x) confusion

[Michael Black]

Probably because you're storing your numbers as text.  How are you inserting
them?
Do a .dump from the shell and you'll see it in the record dump.

So '7' > '20' but 7 < 20



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal
Sent: Monday, February 11, 2013 7:47 AM
To: General Discussion of SQLite Database
Subject: [sqlite] MIN(x), MAX(x) confusion

Hiho,

Summary: i'm getting an incorrect/unexpected MIN()/MAX() results and have
no clue why. After running these results by a colleague, we're both
confused, and thus this post. Here's my data (JMeter output) and results:

SQLite version 3.7.16 2013-01-03 19:34:46
(built from sources updated an hour or so ago)
...
sqlite> .header on
sqlite> select * from res where label='access service';
ts,elapsed,label,rc,msg,thread,dataType,success,bytes,latency
1360586493370,27,access service,200,OK,Thread Group 1-1,text,true,4379,27
1360586494947,20,access service,200,OK,Thread Group 1-2,text,true,4378,20
1360586496927,6,access service,200,OK,Thread Group 1-3,text,true,4378,6
1360586499102,5,access service,200,OK,Thread Group 1-4,text,true,4378,5
1360586501135,6,access service,200,OK,Thread Group 1-5,text,true,4378,6
1360586503112,5,access service,200,OK,Thread Group 1-6,text,true,4378,5
1360586505116,7,access service,200,OK,Thread Group 1-7,text,true,4378,7
1360586507133,6,access service,200,OK,Thread Group 1-8,text,true,4378,6
1360586509333,6,access service,200,OK,Thread Group 1-9,text,true,4377,6
1360586511238,6,access service,200,OK,Thread Group 1-10,text,true,4378,6
1360586513321,6,access service,200,OK,Thread Group 1-11,text,true,4378,6
1360586515244,7,access service,200,OK,Thread Group 1-12,text,true,4378,7
1360586517295,6,access service,200,OK,Thread Group 1-13,text,true,4378,6
1360586519255,6,access service,200,OK,Thread Group 1-14,text,true,4378,6
1360586521261,5,access service,200,OK,Thread Group 1-15,text,true,4378,5
1360586523329,6,access service,200,OK,Thread Group 1-16,text,true,4378,6
1360586525316,6,access service,200,OK,Thread Group 1-17,text,true,4377,6
1360586527325,6,access service,200,OK,Thread Group 1-18,text,true,4378,6
1360586529301,6,access service,200,OK,Thread Group 1-19,text,true,4378,6
1360586531308,6,access service,200,OK,Thread Group 1-20,text,true,4378,6


Notice that the min/max "elapsed" values are 5 and 27, respectively. Now my
results (which i cannot explain)...

sqlite>  SELECT label, count(*), MIN(elapsed), AVG(elapsed), MAX(elapsed)
FROM res where label='access service';
label,count(*),MIN(elapsed),AVG(elapsed),MAX(elapsed)
access service,20,20,7.7,7

sqlite>  SELECT label, count(*), MIN(elapsed), AVG(elapsed), MAX(elapsed)
FROM res where label='access service' GROUP BY label;
label,count(*),MIN(elapsed),AVG(elapsed),MAX(elapsed)
access service,20,20,7.7,7

sqlite> select count(*) from res where label='access service';
count(*)
20

sqlite> select MIN(elapsed) from res where label='access service';
MIN(elapsed)
20

sqlite> select AVG(elapsed) from res where label='access service';
AVG(elapsed)
7.7

sqlite> select MAX(elapsed) from res where label='access service';
MAX(elapsed)
7

sqlite> select label, MAX(elapsed) from res group by label;
label,MAX(elapsed)
access service,7
autocomplete: choose LeadSource,70
autocomplete: choose LeadType,79
...

sqlite> select MIN(elapsed), MAX(elapsed) from res where label='access
service';
MIN(elapsed) MAX(elapsed)
20 7


i'm very confused.  Surely i'm doing something wrong here (as i refuse to
believe that this is an sqlite3 bug)?

:-?

PS: i'm not an sql guru.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] MIN(x), MAX(x) confusion

[Stephan Beal]

Hiho,

Summary: i'm getting an incorrect/unexpected MIN()/MAX() results and have
no clue why. After running these results by a colleague, we're both
confused, and thus this post. Here's my data (JMeter output) and results:

SQLite version 3.7.16 2013-01-03 19:34:46
(built from sources updated an hour or so ago)
...
sqlite> .header on
sqlite> select * from res where label='access service';
ts,elapsed,label,rc,msg,thread,dataType,success,bytes,latency
1360586493370,27,access service,200,OK,Thread Group 1-1,text,true,4379,27
1360586494947,20,access service,200,OK,Thread Group 1-2,text,true,4378,20
1360586496927,6,access service,200,OK,Thread Group 1-3,text,true,4378,6
1360586499102,5,access service,200,OK,Thread Group 1-4,text,true,4378,5
1360586501135,6,access service,200,OK,Thread Group 1-5,text,true,4378,6
1360586503112,5,access service,200,OK,Thread Group 1-6,text,true,4378,5
1360586505116,7,access service,200,OK,Thread Group 1-7,text,true,4378,7
1360586507133,6,access service,200,OK,Thread Group 1-8,text,true,4378,6
1360586509333,6,access service,200,OK,Thread Group 1-9,text,true,4377,6
1360586511238,6,access service,200,OK,Thread Group 1-10,text,true,4378,6
1360586513321,6,access service,200,OK,Thread Group 1-11,text,true,4378,6
1360586515244,7,access service,200,OK,Thread Group 1-12,text,true,4378,7
1360586517295,6,access service,200,OK,Thread Group 1-13,text,true,4378,6
1360586519255,6,access service,200,OK,Thread Group 1-14,text,true,4378,6
1360586521261,5,access service,200,OK,Thread Group 1-15,text,true,4378,5
1360586523329,6,access service,200,OK,Thread Group 1-16,text,true,4378,6
1360586525316,6,access service,200,OK,Thread Group 1-17,text,true,4377,6
1360586527325,6,access service,200,OK,Thread Group 1-18,text,true,4378,6
1360586529301,6,access service,200,OK,Thread Group 1-19,text,true,4378,6
1360586531308,6,access service,200,OK,Thread Group 1-20,text,true,4378,6


Notice that the min/max "elapsed" values are 5 and 27, respectively. Now my
results (which i cannot explain)...

sqlite>  SELECT label, count(*), MIN(elapsed), AVG(elapsed), MAX(elapsed)
FROM res where label='access service';
label,count(*),MIN(elapsed),AVG(elapsed),MAX(elapsed)
access service,20,20,7.7,7

sqlite>  SELECT label, count(*), MIN(elapsed), AVG(elapsed), MAX(elapsed)
FROM res where label='access service' GROUP BY label;
label,count(*),MIN(elapsed),AVG(elapsed),MAX(elapsed)
access service,20,20,7.7,7

sqlite> select count(*) from res where label='access service';
count(*)
20

sqlite> select MIN(elapsed) from res where label='access service';
MIN(elapsed)
20

sqlite> select AVG(elapsed) from res where label='access service';
AVG(elapsed)
7.7

sqlite> select MAX(elapsed) from res where label='access service';
MAX(elapsed)
7

sqlite> select label, MAX(elapsed) from res group by label;
label,MAX(elapsed)
access service,7
autocomplete: choose LeadSource,70
autocomplete: choose LeadType,79
...

sqlite> select MIN(elapsed), MAX(elapsed) from res where label='access
service';
MIN(elapsed) MAX(elapsed)
20 7


i'm very confused.  Surely i'm doing something wrong here (as i refuse to
believe that this is an sqlite3 bug)?

:-?

PS: i'm not an sql guru.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Set Mode to HTML via VBScript

[Jeff Steffanina]

 
Hello All,
During a sqlite3 session, I need to APPEN output to a FILE in the HTML mode.
 
Here is my SELECT:  Select * from Booking;
 
What I really want to say is Set the mode, Execute select and append to 
MySummary:
 
mode html    Select * from Booking >> MySummary.html
 
Any help is greatly appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and AS400

[Mauro Bertoli]

>>  Hi all, I'm a new user in this list.

>> 
>>  Is possible to connect to a SQLITE database from AS400?
> 
> Please read the mailing list you post to.
> 
> Simon.


Sorry, this was my mistake, a wrong double post that was approved today.
Mauro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and AS400

[Simon Slavin]

On 10 Feb 2013, at 6:38pm, Stellar Solutions  wrote:

> Hi all, I'm a new user in this list.
> 
> Is possible to connect to a SQLITE database from AS400?

Please read the mailing list you post to.

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

[sqlite] Sqlite and AS400

[Stellar Solutions]

Hi all, I'm a new user in this list.

Is possible to connect to a SQLITE database from AS400?

Any help will be appreciated.
Mauro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: How to prevent View sqlite database structure and contents from database browsers

[Robert Hairgrove]

On Mon, 2013-02-11 at 11:04 +, SR Volatile wrote:
> Is there any option to encrypt fields in a table when creating it?

There is the SQLite Encryption Extension (SEE):
http://www.hwaci.com/sw/sqlite/see.html

It is not free. But it looks like it does a pretty good job (I never
used it myself).

Other than that, you can encrypt/decrypt the data in your application
using some 3rd party encryption software. Depending on your security
needs, please be aware that no encryption mechanism is 100% safe, and to
achieve an adequate level of security through encryption is anything but
trivial.

Of course, my suggestion to make the file read-only does not prohibit
users from reading the data, only from editing it. You'd have to deny
read AND write access to any users except for those using the database
to prevent all prying eyes.

Although setting up such permissions is fairly straightforward on a *nix
system, I believe that more recent versions of Windows allow user-level
permissions as well.

And I still think this is probably the best way to go with any
file-based RDBMS because once the file-system security is breached, the
database file can be copied and a dedicated intruder can hack away at
leisure to remove whatever encryption there is.

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

Re: [sqlite] How to get the RecNo ???

[Simon Slavin]

On 11 Feb 2013, at 3:45am, Igor Tandetnik  wrote:

> On 2/10/2013 10:06 PM, Mohit Sindhwani wrote:
>> 
>> * You decide then to do a sort by ROWID ASC - expecting that ROWID is
>> maintaining the sequence since when you do a general SELECT * from
>> mytable; you often get it in insertion sequence (if I'm not wrong).
> 
> You are wrong. Normally, with a simple SELECT like this, you get the results 
> ordered by ROWID. SQLite table is stored as a b-tree, with ROWID as its key, 
> so that's the natural order of traversal.

You're both wrong.  Because someone may have done "PRAGMA 
reverse_unordered_selects = YES" 



in which case you'll get records in a completely different order.

As I'm sure you know, the SQL standard makes no assurances about any part of 
SELECT row order you don't specify.  Not only that, but even if you do two 
identical SELECT statements in a row, you may get the rows in a different 
order.  I only ever found one setup which did this (one where the database was 
distributed over different computers, and the order returned depended on which 
host was free at the time), but I wanted to flag it up as a possibility.

As a response to the original poster, if you have your own intuitions about 
exactly how "RecNo" should behave, then make your own column called "RecNo" and 
put into it whatever you want to be there.  If you just want to be sure that 
every row has a unique integer, you can refer to a magical invisible column 
called 'rowid' which will have one unless you messed with it yourself.

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

Re: [sqlite] SQLite Trace Log

[Patrik Nilsson]

Hello,

Not exactly the program language you asked for, but here it is how I
have it in C for gtk:

Call this function after opening the connection:

sqlite3_trace ( sqliteconnection, sqlite_trace, NULL );


static void sqlite_trace ( void *arg, const char *query )
{
FILE *file = fopen ( "/tmp/sqlitetrace.txt", "a" );

if ( file != 0 )
{
GString *stringquery = g_string_new ( query );
dmemory_gstring_free_on_destruction_with_temporary_object (
stringquery );

GString *stringleft = g_string_new ( "" );
dmemory_gstring_free_on_destruction_with_temporary_object (
stringleft );
GString *stringright = g_string_new ( "" );
dmemory_gstring_free_on_destruction_with_temporary_object (
stringright );

GString *stringconcatenated = g_string_new ( "" );
dmemory_gstring_free_on_destruction_with_temporary_object (
stringconcatenated );

if ( stringquery->len > 127 )
{
g_string_assign ( stringleft, stringquery->str );
g_string_truncate ( stringleft, 64 );

if ( stringquery->len > 63 )
{
g_string_assign ( stringright, stringquery->str );
g_string_erase ( stringright, 0, stringquery->len - 63 );
}

g_string_assign ( stringconcatenated, stringleft->str );

if ( stringright->len > 0 )
{
g_string_append ( stringconcatenated, " [...] " );
g_string_append ( stringconcatenated, stringright->str );
}

fprintf ( file, "%s\n", stringconcatenated->str );
}
else
{
fprintf ( file, "%s\n", stringquery->str );
}

fclose ( file );
}
}

Best Regards,
Patrik

On 02/11/2013 08:17 AM, Winston Brummer wrote:
> Hi
> 
> I saw in the version history that SQLite makes use of trace listeners.
> Could anyone give me an example of how to attach a trace listener to an
> application that uses System.Data.SQLite? Specifically related to the
> compact frame work version for Windows Mobile and Pocket PC.
> 
> Any help would be greatly appreciated.
> Winston
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users