Re: [sqlite] : about add sqlite database cloumn

[Kit]

2011/12/23 YAN HONG YE :
> 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 cloumn use sqlite in c code?

SELECT (price1>2)+(price2>1)+(price2>12) AS tt FROM table;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] : about add sqlite database cloumn

[Kees Nuyt]

On Fri, 23 Dec 2011 04:40:53 +, YAN HONG YE 
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 (
NameTEXT,
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

Re: [sqlite] : about add sqlite database cloumn

[YAN HONG YE]

I have a question about C binding for sqlite, I have a table like this:

NamePrice1  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 cloumn use sqlite in c code?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about add sqlite database cloumn

[Simon Slavin]

On 22 Dec 2011, at 2:52am, YAN HONG YE wrote:

> I have a question about C binding for sqlite, I have a table like this:
> 
> Name  Price1  Price2  Sum
> A123  231  
> A222  12   
> A321  223  
> 
> how to use functin int  sum(price1+price2)
> result to put into sum cloumn use sqlite in c code?

You should not call a column "Sum" because that word is reserved in SQLite.  So 
call it "PriceTotal" instead.

UPDATE MyTable SET PriceTotal = Price1 + Price2

But there is no need to do that and store the result.  Just add the two prices 
together whenever you want the total:

SELECT (Price1 + Price2) AS PriceTotal FROM MyTable

To learn how to call SQLite functions from C read the end of this page:



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

Re: [sqlite] about add sqlite database cloumn

[YAN HONG YE]

> I have a question about C binding for sqlite. I have seen those for
> integer, float... but I was wondering how to deal with a NUMERIC (x, y)
> type ?
> We can't use float or double, we could loose precision, so maybe with a
> string ?


I have a question about C binding for sqlite, I have a table like this:

NamePrice1  Price2  Sum
A1  23  231  
A2  22  12   
A3  21  223  

how to use functin int  sum(price1+price2)
result to put into sum cloumn use sqlite in c code?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of sqlite-users-requ...@sqlite.org
Sent: Thursday, December 22, 2011 1:00 AM
To: sqlite-users@sqlite.org
Subject: sqlite-users Digest, Vol 48, Issue 21

Send sqlite-users mailing list submissions to
sqlite-users@sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@sqlite.org

You can reach the person managing the list at
sqlite-users-ow...@sqlite.org

When replying, please edit your Subject line so it is more specific
than "Re: Contents of sqlite-users digest..."


Today's Topics:

   1. Re: Strange behavior for timeouts in transactions (romtek)
   2. Re: How about the way put a database of SQLite 3 beside   web
  folder? (romtek)
   3. Re: Strange behavior for timeouts in transactions (Simon Slavin)
   4. insert error (jim-on-linux)
   5. Re: insert error (Petite Abeille)
   6. Re: insert error (epank...@comcast.net)
   7. Re: Procedure (Conditional statement) workaround (Nico Williams)
   8. Re: Procedure (Conditional statement) workaround (Petite Abeille)
   9. run a script in Visual Basic.NET (Esteban Cervetto)
  10. Re: run a script in Visual Basic.NET (Simon Slavin)
  11. sqlite3_column_text() returning partial results (Jacob A. Camp)
  12. Re: sqlite3_column_text() returning partial results
  (Igor Tandetnik)
  13. Re: sqlite3_column_text() returning partial results (nobre)
  14. Re: sqlite3_column_text() returning partial results (Simon Slavin)
  15. Re: Bug in trigger: when comparing a value of an int column
  to a quoted value (Alexey Pechnikov)
  16. sqlite3_column_text() returning partial results (Jake)
  17. Re: sqlite3_column_text() returning partial results
  (Igor Tandetnik)
  18. Re: sqlite3_column_text() returning partial results
  (Jacob A. Camp)
  19. Re: sqlite3_column_text() returning partial results
  (Black, Michael (IS))
  20. Re: sqlite3_column_text() returning partial results (Pavel Ivanov)


--

Message: 1
Date: Tue, 20 Dec 2011 11:21:16 -0600
From: romtek 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Strange behavior for timeouts in transactions
Message-ID:

Content-Type: text/plain; charset=ISO-8859-1

On Tue, Dec 20, 2011 at 6:08 AM, Simon Slavin  wrote:
>
> First, there's more than one way of using SQLite3 from PHP. ?There's also the 
> interface SQLite3:: which is a much thinner wrapper around the basic SQLite C 
> library. ?I have no way of knowing what proportion of programmers use one 
> library or the other.

I started using SQLite with PHP in version 5.2.x, when there wasn't
SQLite3:: interface, so I chose to use PDO. Moreover, I don't want to
lock myself into SQLite, PDO provides a somewhat more flexible
approach.


> As long as you set an acceptable timeout, which you do with one of these 
> calls depending which library you're using
>
> 
>
> 
>
> your web service probably isn't going to run into problems. ?What might be 
> worrying you right now is the result of the default timeout being zero, 
> something which is arguably weird. ?In other words, unless you specify a 
> timeout yourself any report of a lock is instantly treated like an error. 
> ?Set your timeout to one second or five seconds and the behaviour will be 
> more reasonable.

I'd done some research into this since I asked my question and learned
some things. According to http://bugs.php.net/bug.php?id=38182 and
http://www.serverphorums.com/read.php?7,118071, PDO_SQLITE defaults to
a 60 second busy timeout. This should be enough.

> Second, most transactions and locks in SQLite from PHP are fleeting. 
> ?Generally you want your web page to list some records or do one update. ?You 
> wouldn't have a process keep a lock active for a long time because this would 
> correspond to your server taking a long time to show a web page, and nobody 
> wants that. ?So a lock by one process probably isn't going to last very long 
> -- probably a fraction of a