Re: [sqlite] : about add sqlite database cloumn

[Kees Nuyt]

On Fri, 23 Dec 2011 04:40:53 +0000, YAN HONG YE <yanhong...@mpsa.com>
wrote:

>I have a question about C binding for sqlite, I have a table like this:
>
>Name   Price1  Price2  Sum
>A1     23      231      
>A2     22      12       
>A3     21      223      
>
>how to use functin
> int  myfunc()
>{
>int tt=0;
>if (price1 >2)
>tt++;
>if (price2>1)
>tt++;
>if (price2>12)
>tt++;
>
>...
>return tt
>
>}
>
> to put function result into  my table last added
> column use sqlite in c code?

To use SQLite from C, you have to use the C API.

http://www.sqlite.org/c3ref/funclist.html

Here are a few examples:

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

http://icculus.org/~chunky/stuff/sqlite3_example/sqlite3_example_bind.c

Assuming tt is what you would want to see in the sum column, there is no
need to use C:

CREATE TABLE t1 (
        Name            TEXT,
        Price1  INTEGER,
        Price2  INTEGER
);
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A1',23,231); -- 3
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A2',22,12);  -- 2
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A3',21,223); -- 3
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A4',1,1);    -- 0
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A5',1,2);    -- 1
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A6',1,13);   -- 2
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A7',3,1);    -- 1
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A8',3,2);    -- 2
INSERT INTO t1 (Name,Price1,Price2) VALUES ('A9',3,13);   -- 3

CREATE VIEW t1_with_sum AS
  SELECT Name,
                Price1,
                Price2,
                (Price1 > 2) + (Price2 > 1) + (Price2 > 12) AS Sum
   FROM t1
        ORDER BY Name;
.headers on
SELECT * FROM t1_with_sum;

outputs:

Name|Price1|Price2|Sum
A1|23|231|3
A2|22|12|2
A3|21|223|3
A4|1|1|0
A5|1|2|1
A6|1|13|2
A7|3|1|1
A8|3|2|2
A9|3|13|3


Note: the sum column should not be part of the table, as it is a derived
value. If you really want to add that column, you can populate it with: 

UPDATE t1 set Sum=(Price1 > 2) + (Price2 > 1) + (Price2 > 12);

SELECT * FROM t1 ORDER BY Name;
(same output)

Hope this helps.

-- 
Regards,

Kees Nuyt

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