Re: [sqlite] Getting the last row

[Trey Mack]

select * from t order by rowid desc limit 1;

Thanks,
Trey Mack

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Aravinda babu
Sent: Wednesday, September 17, 2008 2:15 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Getting the last row

Hi all,

Is there any easy way to get the last row in the table ?

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

No virus found in this incoming message.
Checked by AVG - http://www.avg.com 
Version: 8.0.169 / Virus Database: 270.6.21/1675 - Release Date: 9/16/2008
7:06 PM

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

Re: [sqlite] DATETIME data type

[Trey Mack]

Store it in '-mm-dd' format, or use the julian date that's suggested at:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Yong Zhao wrote:
> It seems that sqlite3 does not support DATETIME data type.
>
> If I have the following data in table t1, how do I select people who is
> older than certain date?
>
> create table t1(dob text, name text);
> insert into t1('11/12/1930', 'Larry');
> insert into t1('2/23/2003', 'Mary');
>
> select * from t1 where dob < '3/24/1950';
>
> Thank you.
> ___
> 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

Re: [sqlite] Concatenating values from multiple varchar fields

[Trey Mack]

Two pipes

||

Jason Salas wrote:
> I'm new to SQLite, coming over from SQL Server.  I often do string 
> concatenation like so:
>
> lastName + ', ' + firstName as [name] from myTable
>
> But it tries to run a math computation and returns '0.0' for each 
> field.  I've tried some other concat operators that I know of, but none 
> work.  How is this achieved in SQLite?
>
> Thanks!
>
>
> ___
> 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

Re: [sqlite] Return row order

[Trey Mack]

You are not guaranteed any particular order unless you use ORDER BY, 
even between two runs of the same SQL statement.

Roland Romvary wrote:
> Hi!
>
> Can I take it for sure that the order of the rows returned by 2 queries are
> the same?
> The only difference between the queries is that the selected columns of the
> first query are subset of the second.
>
> Ex:
> select name, age from table1 where age > 18;
>
> select name, age, addr from table1 where age > 18;
>
> (queries may include joins and unions and are generated based on a complex
> set of switches)
>
> Thank you for your answer!
> Bye
> ___
> 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

Re: [sqlite] Counting rows in multiple tables and joining on an index

[Trey Mack]

create table t1 (rpsIndex INTEGER);
create table t2 (rpsIndex INTEGER);
create table t3 (rpsIndex INTEGER);

insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (2);
insert into t2 values (1);
insert into t2 values (2);
insert into t2 values (3);
insert into t3 values (3);
insert into t3 values (3);
insert into t3 values (3);

select rpsIndex, count(1) frequency from
(
select rpsIndex from t1
union all
select rpsIndex from t2
union all
select rpsIndex from t3
)
group by rpsIndex;

- Trey

jrpfinch wrote:
> I have three tables, each of which has the column rpsIndex.  This column is
> not a unique index/primary key.
>
> I would like to count the number of times each rpsIndex appears in all three
> tables. E.g.
>
> Table1
> rpsIndex=1
> rpsIndex=1
> rpsIndex=2
> Table2
> rpsIndex=1
> rpsIndex=2
> rpsIndex=3
> Table3
> rpsIndex=3
> rpsIndex=3
> rpsIndex=3
>
> Query would return:
> rpsIndex  Frequency
> 13
> 22
> 34
>
> It is possible to do this in pure SQL in SQLite?
>
> Many thanks
>
> jon
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sorting the result of a select. Is this possible?

[Trey Mack]

Is this what you're after?

create table LSOpenJobs (PSubClass);
insert into lsopenjobs values ('DOC-Trans');
insert into lsopenjobs values ('DTP');
insert into lsopenjobs values ('PM');
insert into lsopenjobs values ('Post-Proc');
insert into lsopenjobs values ('Pre-Proc');

select * from lsopenjobs
order by case psubclass
when 'Pre-Proc' then 1
when 'Post-Proc' then 2
when 'DOC-Trans' then 3
when 'DTP' then 4
when 'PM' then 5
end;


jose isaias cabrera wrote:
> Greetings.
>
> I would like to have the results of a select be returned sorted in an 
> specific way.  Let me show you what I mean:
>
> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP BY 
> PSubClass;
> DOC-Trans
> DTP
> PM
> Post-Proc
> Pre-Proc
> sqlite>
>
> What I would like is to have the SELECT result be,
>
> Pre-Proc
> Post-Proc
> DOC-Trans
> DTP
> PM
>
> is this possible?  Yes, I know I can sort it in the program, but how can I 
> get this special sort from the DB?
>
> thanks,
>
> josé
>
>
>
> ___
> 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

Re: [sqlite] Version 3.2.2

[Trey Mack]

UPDATE listings SET buyer = 'Price' WHERE listnum = 12345

Double quotes are for column names.

Walt wrote:
> I have a table 'listings' with fields 'listnum', 'price' and 'buyer' etc.
>
> executing the following sql
>
> UPDATE listings SET buyer = "Price" WHERE listnum = 12345
>
> results in the 'buyer' field being set to the contents of the 'price' field 
> instead of setting the field 'buyer' to "Price".
>
> Need HELP
>
> Walt Mc Whirter
> ___
> 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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Trey Mack]

3.6.0 in the next release?  Or can we call the change
a "bug fix" and number the next release 3.5.4?


I guess I'm in the minority, but I'd find a change in the meaning of  
my queries surprising in a bug fix release. That sounds like a 3.6 to  
me.


You may be in the minority, but you're not alone. +1.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

[Trey Mack]

Can you please tell me what
other databases do with this:

   CREATE TABLE t1(a,b,c);
   INSERT INTO t1 VALUES(1,2,4);
   SELECT a+b AS c FROM t1 WHERE c==4;


SQL Server returns a 3.
ORACLE 9 returns a 3.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] LIKE operator syntax for white space

[Trey Mack]

How do I filter out records that contain WHITE SPACE in a field or
have this field empty?

For example:
select * from mytable
where fld <> "" and fld LIKE .


Do you mean "contain ONLY white space"? Are you after this?

select * from mytable
where trim(fld) <> ""



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database file size

[Trey Mack]

I add records to database tables. Then when i delete them the database do 
not

reduce size. I add BLOB elements. Do you know what can be the problem?
Thanks


http://www.sqlite.org/lang_vacuum.html 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Openinig 1 or more connections to db

[Trey Mack]

At the moment I open 1 connection to the SQL Serv.. at the moment of
login and I keep it open until the user exit the program.



Can I do the same with SQLite, or do you suggest to open and close the
connection every time I need it? Will I lose Performance?



As I understand it, that's the way to do it to maximize cache benefits.

- Trey

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] best way to match a date but not a time?

[Trey Mack]

I want to have queries that will match dates but not care about times.
The date might be today, anything within the last week, month, year,
or a range of dates.  I'm using Python's datetime function, so the
dates enter the database in this format 2007-09-01 12:00:02.

So far, < or > queries seem to work, like:

SELECT duration FROM specactivities WHERE date < "2006"

but what I can't do is use =, since it seems like it is trying to match
both the date and the exact time.


http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

You're storing dates as TEXT, not DOUBLE, correct?

WHERE date(colName) = '2007-09-01' should work to match a particular date. 
Be aware though, this approach disables the use of indices. So, if you have 
an index that will be used with < or > queries you mentioned before, the 
specific date-match with date(colName) will be slower because it has to do a 
full table scan.


Perhaps this would be better:

SELECT duration FROM specactivities WHERE date >= '2007-09-01' AND date < 
'2007-09-02'


To the experts: will an index be used for both comparisons in the WHERE 
clause? Or just the first? I think I remember reading somewhere that an 
index can be used for any number of exact matches, but only 1 less-than or 
greater-than comparison, and that would be the last usable column of the 
index. Or maybe it could be used for >= AND < on the same column at the same 
time, but that would be the last usable column of the index?


HTH,
Trey 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Index Creation Questions

[Trey Mack]

I'm creating some indices, and want to know if it's going to help or hurt me 
(or be totally irrelevant because of optimization) to include the primary key 
at the end of the index. All of my tables (in question) have an INTEGER PRIMARY 
KEY AUTOINCREMENT field, and I have several textual descriptors for the tables 
in question. I want the fastest lookups (of course) of the PRIMARY KEY value 
given the text descriptor(s).

It is my understanding that each index is a btree ordered starting with 
whatever columns you're indexing, and the rowid for subsequent lookups of 
fields not included in the index. The rowid in my case is, of course, the same 
as the PRIMARY KEY that I'm after, so I believe this will skip a lookup in the 
actual table.

Enough words.. concrete example:

CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, a TEXT, b TEXT, c TEXT);
CREATE INDEX i1 ON t (a); -- is this sufficient?
CREATE INDEX i2 ON t (a, id); -- or is this necessary to avoid going back to 
the original table?
CREATE INDEX i3 ON t (b); 
CREATE INDEX i4 ON t (b, id);
CREATE INDEX i5 ON t (c); 
CREATE INDEX i6 ON t (c, id);

Also, a, b, and c may be constrained independently or together. So, should I 
also create a composite index including them all? In every possible ordering?

(a,b,c)
(a,c,b)
(b,a,c)
(b,c,a)
(c,a,b)
(c,b,a)

I guess that would cover all bases, but seems like overkill. I think the answer 
to this question is just let a,b and c ride independently as in the earlier 
indices, and I'll have constrained alot, though not as fully as possible.

Thanks in advance for any insight,
Trey

Re: [sqlite] UI question

[Trey Mack]

When using the command line interface sqlite3, a couple of times I have 
forgotten to use the "." before a command.  After that I get a "...>" 
prompt  that I can't seem to escape from and accepts no commands?  My only 
choice is to shut down that terminal and start a new one..


There must be an easy  way to  get back to the command mode? And what is 
the "...>"  mode?


It's a continuation line, which allows your SQL statements to span multiple 
lines. Just hit


;

and you'll receive an error that says "invalid sql" or something similar, 
and you'll be able to enter another command.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Milliseconds

[Trey Mack]

I rather thought about retrieving and storing milliseconds in SQL. I
mean, a SQL data type which is able to store milliseconds and a SQL
function to return the current timestamp including milliseconds. I had
no luck with CURRENT_TIMESTAMP for instance.


There is no true SQLite DATE data type. It's either TEXT or REAL, depending 
on how you choose to store your dates.


Is this what you're after?

SQLite version 3.4.0
Enter ".help" for instructions
sqlite> select strftime("%Y-%m-%d %H:%M:%f", "now");
2007-07-12 13:58:34.797

Info about date/time functions in SQLite can be found at 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trigger update of multiple columns

[Trey Mack]

I want to insert the transactions data  (product_id, buy, sell, desc) into 
the sale_products table. But I  want a mechanism whereby if I enter the 
product_id, then the buy,  sell, desc columns are auto entered (copied) 
from their corresponding  row in the products table.


Given:

CREATE TABLE products ( -- I believe this is a view of UNIONs, but this 
should be a close approximation

 product_id INTEGER PRIMARY KEY,
 buy REAL,
 sell REAL,
 desc TEXT
);

CREATE TABLE sales (
 sale_id INTEGER PRIMARY KEY,
 product_id INTEGER,   -- FK to products table
 customer_id INTEGER   -- FK to customes table
);

CREATE TABLE sale_products (
 sale_id INTEGER,  -- FK to sales table
 product_id INTEGER,   -- FK to products table
 buy REAL,
 sell REAL,
 desc TEXT
);

Yes, getting your database closer to 3NF would be better. But as a quick 
fix, could you do this on insert just after entering the sale record into 
the sales table?


INSERT INTO sale_products (sale_id, product_id, buy, sell, desc)
   SELECT s.sale_id, s.product_id, p.buy, p.sell, p.desc
   FROM sales s INNER JOIN products p
   ON s.product_id = p.product_id
   WHERE s.sale_id = @sale_id; -- sqlite3_last_insert_rowid()

Or, if you know sale_id and product_id, save the JOIN:

INSERT INTO sale_products (sale_id, product_id, buy, sell, desc)
   SELECT @sale_id, @product_id, buy, sell, desc
   FROM products
   WHERE product_id = @product_id;

- Trey



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL query help...

[Trey Mack]

Here's what I tried, which didn't work...

select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
where diff > 0.0005
group by zone,location

That causes a "misuse of aggregate" error.


select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
group by zone,location
having diff > 0.0005;

- tmack

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Can't update table from itself or multiple tables

[Trey Mack]

I checked not implemented features list of sqlite and found nothing
about "update ... from".


There's no "FROM" on http://www.sqlite.org/lang_update.html, so that's a 
hint that it's not supported. All the same, maybe this should be added to 
http://www.sqlite.org/omitted.html



update table1 set val = ss.v
from
(select t2.some as v, t1.id as id
from table1 t1, table2 t2
where t1.id = t2.nid) as ss
where ss.id = table1.id


How about

update table1
set val =
(select some from table2
 where table1.id = table2.nid
);

- TMack


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Why does "SELECT julianday('some_date') AS dateNumber" get me a string via ODBC?

[Trey Mack]

I see that it is a variant of type VT_BSTR containing
"2345678.9123" (or whatever), and I have to use strtod() to convert it
to the number I want.  Is there a way to write the query to ensure that
it will give me a number instead of a string?


The type selection is done in your ODBC wrapper, and if it's storing that 
floating-point value as a BSTR in its returned variant, my guess is that 
it's not checking the return value from sqlite3_column_type(), and it'll 
just store everything as a BSTR. No query will change that. (The exception 
is blobs, they should be VT_ARRAY | VT_UI1).


So I would think the answer is no. Then again, I've never used the wrapper 
you're using.


- TMack


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite function list?

[Trey Mack]

Included functions:
http://www.sqlite.org/lang_expr.html
See the section "Core Functions" near the bottom of the page for the builtin 
functions.


To add more:
http://www.sqlite.org/contrib
extension-functions.tgz  for details on adding functions like ceil.

- TM

- Original Message - 
From: "Scott Baker" <[EMAIL PROTECTED]>

To: 
Sent: Monday, June 04, 2007 3:43 PM
Subject: [sqlite] sqlite function list?



Is there a list somewhere (I can't find it on the wiki) of all the
functions (specifically math) functions that sqlite understands?

I'm thinking things like: int, round, floor, ceil, sqrt etc.

--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Inserting text string with quotes gives an error - php

[Trey Mack]

Inside string literals, escape single quotes with 2 single quotes

'test 'test' test' ==> 'test ''test'' test'

When I try to insert text containg single quotes via php it gives me 
syntax

error:


...

// so query looks like:
INSERT INTO version (date,active,category,entry) VALUES ('2007-05-31
18:50','x','NOTE','test 'test' test');



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] A suggestion

[Trey Mack]

Works for me straight out of the box on Windows XP.


That program does have the capability, but may not be implemented that way 
on Windows.  Why not make the change yourself?


A.J.Millan wrote:
As a suggestion, and even in the risk to abuse of Mr Hipp's patience. 
Would

it be possible to include in the command-line program (sqlite3.exe) the
ability to edit, an repeat at least the five or six last commands, as in
Linux?. Is to say with up-arrow and down-arrow.  I believe it would be 
too

helpful.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR REPLACE without new rowid

[Trey Mack]

Phani,

INSERT OR REPLACE is *close* to an UPSERT / MERGE / whatever you wanna call it, 
but changes the rowid (actually, creates a whole new row, I think) on collision 
with a constraint. An example to illustrate:

SQLite version 3.3.11
Enter ".help" for instructions
sqlite> .mode column
sqlite> .headers on
sqlite>
sqlite> create table t (id integer primary key autoincrement, a text, b text, c 
text);
sqlite> create unique index idx1 on t (a, b, c);
sqlite>
sqlite> insert into t (a, b, c) values ('a', 'b', 'c');
sqlite> insert into t (a, b, c) values ('b', 'b', 'c');
sqlite>
sqlite> select * from t;
id  a   b   c
--  --  --  --
1   a   b   c
2   b   b   c
sqlite>
sqlite> insert or replace into t (a, b, c) values ('a', 'b', 'c');
sqlite>
sqlite> select * from t;
id  a   b   c
--  --  --  --
2   b   b   c
3   a   b   c

I needed to retain the previous rowid (1) on collision. For what it's worth, 
I'm just selecting to test for existence first (I need the rowid anyway, so no 
real penalty), so no problem here. There are other ways, like performing an 
UPDATE WHERE a = 'a' AND b = 'b' AND c = 'c', then testing if 
(sqlite3_changes() == 0), if so, INSERT the data, which is likely faster 
(thanks for the idea Igor).

I think the SQL standard is going to adopt the MERGE command for this type of 
operation, but I'm not sure.. is anyone in the know? It's in ORACLE now, surely 
elsewhere too..

- Trey

- Original Message - 
From: "B V, Phanisekhar" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, May 08, 2007 10:59 AM
Subject: RE: [sqlite] INSERT OR REPLACE without new rowid


Hi Trey,
  Even I was looking for something like this. But I don't think SQL
allows you to do this. I didn't get what u said about INSERT OR REPLACE
looks good.


Regards,
Phani

-Original Message-
From: Trey Mack [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 24, 2007 9:11 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] INSERT OR REPLACE without new rowid

Hello all,

I'd like to perform an update to a row if it exists (uniquely identified
by 
3 text columns), otherwise insert a new row with the right data. INSERT
OR 
REPLACE looks good, but it generates a new primary key each time there
is a 
conflict. If the row exists, I need to keep the original primary key 
(rowid).

Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ?

Thanks,
Trey



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] 3.3.17 Source code ZIP file missing most files?

[Trey Mack]

The 3.3.17 ZIP file seems to only contain 2 source files, instead of the
full SQLite source. Is this on purpose?


It is the full source. See
http://www.sqlite.org/cvstrac/wiki?p=TheAmalgamation
and a big ol' thread at
http://www.nabble.com/May-I-ask-why-the-source-distribution-mechanism-was-changed-starting-with-3.3.14--tf3657836.html



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] best performance

[Trey Mack]

Am I able to perform a transaction where I execute a query with a
begin..insert..end and commit?  Would that even be faster? 


Yep, that's the way to go.

http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations

See
Transactions and performance


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] INSERT OR REPLACE without new rowid

[Trey Mack]

Hello all,

I'd like to perform an update to a row if it exists (uniquely identified by 
3 text columns), otherwise insert a new row with the right data. INSERT OR 
REPLACE looks good, but it generates a new primary key each time there is a 
conflict. If the row exists, I need to keep the original primary key 
(rowid).


Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ?

Thanks,
Trey


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] handling empty table in a callback function to sqlite3_exec

[Trey Mack]

Hi, I'm querying a table by writing the query in sqlite3_exec with a
callback function that does some operation on the resulting row.
Everything is fine when the table has even one row, but when it is
empty, my program halts with "segmentation fault". I can add a dummy row
to the table to prevent this, but is there a nicer solution to this
issue?
Thanks, Rafi.



sqlite3_get_table wraps sqlite3_exec and uses a callback, check it out as an 
example.. (or just use it instead, if possible)


http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/table.c=1.26

- Trey


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What is wrong with this simple query (offset)?

[Trey Mack]

It looks all as it should work and it compiles with the same number of
warnings, but I get a bad dll calling convention in VB with the extra
integer argument iFields.


You've changed the signature of the method you're calling, and it looks like 
you changed it correctly in the VB declaration. Maybe you have an older 
version of the DLL with the older signature in your system32 directory? It's 
possible this older version is being loaded, and that would cause the error 
you see.


There's a method called sqlite_libversion in that dll that returns 
VB_SQLITE_VERSION (#defined in vbsql.h). Mine's "3.3.8c" now. Added the 
extra letter just so I could make sure I have the right version of the dll 
loaded.


HTH,
- Trey


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Triggers+callbacks = GUI?

[Trey Mack]

Wesley wrote:
As an example: in an instant messenger a new text message arrives.  The 
application puts it into the message log. The chat window  automatically 
updates with the new text. The statistics window about  total # of 
messages updates. Possibly other things happen. The point  is I don't want 
to have to think about the 'possible other things'.  If the program gets 
fat, I'll screw this up.




Sam wrote:

Personally I think a database should provide long-term storage for your
application's state, not drive the application's UI.  If you need to 
update
views based on state then that state should be in memory with mechanisms 
to

easily detect changes.


True.
http://en.wikipedia.org/wiki/Model-view-controller
A change to the Model would raise events that the View can react to.

Hope this helps,
- Trey 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I know sqlite_get_table is finished

[Trey Mack]

The function ArrayToSheet has nil to do with SQLite and I left that out.


Ah, but that's where you access the variant array. That can be tricksy. Do 
you always access that variant array (arr) in the range (0 To NumRecords, 0 
To NumColumns - 1) where the row arr(0, *) holds the column names, and 
UBound(arr, 2) is NumColumns ??


Also, when you say "crash", I assume you mean catastrophic-style, so you 
don't have a specific error number or description. Correct?


- Trey 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I know sqlite_get_table is finished

[Trey Mack]

- Original Message - 
From: "Guy Hachlili" <[EMAIL PROTECTED]>

To: 
Sent: Friday, February 02, 2007 10:13 AM
Subject: RE: [sqlite] How do I know sqlite_get_table is finished



In any case, I've worked a bit on the function, and here's the result:


Thanks alot. It's much appreciated.

- Trey


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Does julianday work according to the manual?

[Trey Mack]

Wikipedia also disagrees with the 24. Nov of 4714 B.C. as start of 
julian days...


http://en.wikipedia.org/wiki/Julian_day



(Footnote from that page..)
^ This equals November 24, 4714 BC in the proleptic Gregorian calendar.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: An SQL question (Not directly related to SQLite)

[Trey Mack]

Actually, my query is something like
SELECT ... FROM ... WHERE `pid` = (SELECT `id` FROM ...);
if i put that group by... will it group all rows, or only those with
the same pid?


Use a subquery

SELECT price, sum(count) FROM (
   -- your original query here
   SELECT price, count FROM ... WHERE `pid` = (SELECT `id` FROM ...)
) GROUP BY price;

And you treat your subquery as just any other table. That way it's clear 
where your GROUPing is applied.


Side note: I'd be careful about naming columns names like 'count'. That's a 
function in SQL, and some will simply not allow you to use it as a column 
name. Some will allow you to use it as a column name with some special 
handling (like "[count]"). SQLite appears to allow a column named "count", 
but did not for some others ("limit" comes to mind). "Precision" is another 
example in SQLServer.. It's easy to get around, just prepend some 
description to the word, like "UnitPrice, ItemCount"...


Regards,
   Trey


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] return table data without fields

[Trey Mack]

I cannot find a PRAGMA for turning column names on/off either. You may be 
thinking of ".headers on/off" in the SQLite shell..


I don't think there's a way to turn this off, except rewriting the code for 
sqlite_get_table (which, thankfully, he's provided). Maybe to include a 4th 
parameter that would be a flag to include column headers or not.


Or you could wrap all your calls through a VB function of your design that 
strips that first row (the column headers) out of the array, but I'd worry 
about performance with that method.


One thing worth mentioning about this wrapper is that it doens't handle NULL 
return values as you might expect. If you issue a query that should return 
NULL.. like:


   create table t (a integer); -- no records..
   select max(a) from t; -- should return NULL, since there is no max
   select typeof(max(a)) from t; -- proves that SQLite returns NULL

you'll receive "" (the empty string) in the spot in the array where your 
result ends up.


But again, thankfully, he's provided the source, so you can add the 
following lines


   else { // I hope the following is safe, as I'm definitely NOT a C/C++ 
guru.. especially with memory allocation / deallocation

   tmpVariant.vt = VT_NULL;
   hr = SafeArrayPutElement(resultp, indices, );
   VariantClear();
   }

at line 103 in my copy of VBSQL.c (I may have done some formatting, I 
forget), as the else for the following if:


   if (SQL_Results[sqlite_return_array_int]) {

then, the Variant that will be sent to VB6 will be of type Null and can be 
tested with the VB6 function IsNull(..) properly.


Regards,
   Trey

- Original Message - 
From: "RB Smissaert" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, January 27, 2007 7:01 AM
Subject: [sqlite] return table data without fields



Using the VB wrapper dll from Todd Tanner:
http://www.tannertech.net/sqlite3vb/index.htm

and it has this function to return table rows:

Private Declare Function sqlite_get_table _
 Lib "SQLite3VB.dll" _
 (ByVal DB_Handle As Long, _
  ByVal SQLString As String, _
  ByRef ErrStr As String) As Variant()

This will by default include the table field names.
Is there a way to return the table data without these field names?
I thought there was a Pragma command for this, but I couldn't find it.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite3VB.dll

[Trey Mack]

I have just one question.
What exactly does this function do:

Private Declare Function sqlite3_changes _
Lib "SQLite3VB.dll" (ByVal DB_Handle As Long) As Long

It looks it will always give one, unless you have just created a database
and done nil with it. Is this how it is?


It returns the number of rows affected by a delete, update, or insert (not 
updated by selects). It's not the number _changed_, but the number of rows 
addressed by the WHERE clause, whether an update actually changes a value or 
not. One special case is when you DELETE FROM table with no where clause.. 
explained below.



From sqlite3.h:

/*

** This function returns the number of database rows that were changed

** (or inserted or deleted) by the most recent called sqlite3_exec().

**

** All changes are counted, even if they were later undone by a

** ROLLBACK or ABORT. Except, changes associated with creating and

** dropping tables are not counted.

**

** If a callback invokes sqlite3_exec() recursively, then the changes

** in the inner, recursive call are counted together with the changes

** in the outer call.

**

** SQLite implements the command "DELETE FROM table" without a WHERE clause

** by dropping and recreating the table. (This is much faster than going

** through and deleting individual elements form the table.) Because of

** this optimization, the change count for "DELETE FROM table" will be

** zero regardless of the number of elements that were originally in the

** table. To get an accurate count of the number of rows deleted, use

** "DELETE FROM table WHERE 1" instead.

*/


- Trey 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3 lib do not open old sqlite databases

[Trey Mack]

From http://www.sqlite.org/formatchng.html
"Version 3.0.0 is a major upgrade for SQLite that incorporates support for 
UTF-16, BLOBs, and a more compact encoding that results in database files 
that are typically 25% to 50% smaller. The new file format is very different 
and is completely incompatible with the version 2 file format."


At the bottom of that page are some suggestions for upgrading your database 
to the current format.


- Trey

- Original Message - 
From: "anis chaaba" <[EMAIL PROTECTED]>

To: 
Sent: Monday, January 22, 2007 11:15 AM
Subject: [sqlite] sqlite3 lib do not open old sqlite databases



Hello everybody
I'm moving from sqlite 2.8.17 to sqlite3.3.11 but when i'm trying to open 
my

databases created with sqlite 2.8.X i have this error message:
file is encrypted or is not a database
Is that normal or did i have a mistake somewhere?
Thank you for your help in advance




-
To unsubscribe, send email to [EMAIL PROTECTED]
-