Re: [sqlite] How to Use an Apostrophe in a Text Field?

[venkat easwar]

Hi,

True. I will get rid of the habit of using double quotes for string literals. 
Thanks for information. But most of the databases support this non standard 
behavior.

Thanks
Venkat

 VENKAT





From: Jean-Christophe Deschamps 
To: General Discussion of SQLite Database 
Sent: Thu, April 21, 2011 4:03:49 PM
Subject: Re: [sqlite] How to Use an Apostrophe in a Text Field?


>  The apostrophes are escaped by apostrophes.

Correct.  http://www.sqlite.org/faq.html#q14

>  One more way you can do.
>
>insert into  () values ("*Goin' Down
> >> the Road Feelin' Bad*");
>
>It is double quotes before and after *. Similarly double quotes will 
>be escaped by one more double quote

Don't do that: it can reveal a pitfall.
It's not SQL even if SQLite does its best to interpret it without 
issuing an error.

Double quotes should be reserved to enclose database, table and column 
names, not string literals. SQLite also accepts square brackets as 
well: "My Table" is the same as [My Table].

Only use single quotes (apostrophes) for string literals.

A statement like:
delete from "my table" where column = "column";
is prone to disapoint you!
http://www.sqlite.org/faq.html#q24

___
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] How to Use an Apostrophe in a Text Field?

[venkat easwar]

 The apostrophes are escaped by apostrophes. One more way you can do.

insert into  () values ("*Goin' Down
>> the Road Feelin' Bad*");

It is double quotes before and after *. Similarly double quotes will be escaped 
by one more double quote
VENKAT





From: Jim Morris 
To: General Discussion of SQLite Database 
Sent: Mon, April 18, 2011 8:25:54 PM
Subject: Re: [sqlite] How to Use an Apostrophe in a Text Field?

Did you try doubling the apostrophes?

*Goin'' Down the Road Feelin'' Bad*


On 4/17/2011 6:16 PM, Simon Slavin wrote:
> On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote:
>
>> I'm using SQLite with VBE2008.  I've defined a table with a number of text
>> fields in it.  If the information I want to write to the database contains
>> an embedded apostrophe, the program throws an error.  That is, if I set
>> textfield1 to *Going Down the Road Feeling Bad*, the data gets written
>> correctly and the program continues.  But if I set textfield1 to *Goin' Down
>> the Road Feelin' Bad*, I get an error.
>>
>> Is there a way I can use an apostrophe in the data to be written?
> Your library might do it for you.  If you're writing directly to the SQLite 
>library then I believe you can double the apostrophe:
>
> Goin'' Down the Road Feelin'' Bad
>
> so it might be worth trying that.
>
> Simon.
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert statement using temp variable

[venkat easwar]

Hi,

Very simple, What will be the output of printf("i"); it won't be 0 right?

use snprintf or sprintf and formulate the string then execute the query.

int i=0;

char * a[100];
snprintf(a,100,"insert into emp values(%d);",i); /or
/*sprintf(a,"insert into emp values(%d);",i);*/

rc = sqlite3_exec(db, "create table emp (empid num);", callback, 0, );
rc = sqlite3_exec(db, a, 0, 0, );

This should work.

 VENKAT
Bug the Bugs





From: RAKESH HEMRAJANI 
To: sqlite-users@sqlite.org
Sent: Tue, April 5, 2011 10:51:09 AM
Subject: [sqlite] insert statement using temp variable


hi,

need help with very basic question.. More of C than SQLite.

have a very simple C program using sqlite DB.

..
int i=0;

rc = sqlite3_exec(db, "create table emp (empid num);", callback, 0, );
rc = sqlite3_exec(db, "insert into emp values(i);", 0, 0, );

---

the insert query fails with the message stating no such column i.

the aim is very simple to insert the value of i into empid column but not sure 
how to achieve it.

pls note that value of i is dynamic and wont be hardcoded.

  
___
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] UPDATE WITH ORDER BY LIMIT ERROR

[venkat easwar]

Oh... Thanks Kennedy. Between any options on run time to enable the feature?

 VENKAT





From: Dan Kennedy <danielk1...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Tue, February 15, 2011 4:48:24 PM
Subject: Re: [sqlite] UPDATE WITH ORDER BY LIMIT ERROR

On 02/15/2011 06:04 PM, venkat easwar wrote:
> Forgot to mention what error I am getting.
>
> near "order": syntax error
> near "limit": syntax error - if i remove the order by clause

See under the "Optional LIMIT and ORDER BY Clauses" heading
on this page:

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

You need to build SQLite with SQLITE_ENABLE_UPDATE_DELETE_LIMIT
defined.

  http://www.sqlite.org/compile.html#enable_update_delete_limit

___
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] UPDATE WITH ORDER BY LIMIT ERROR

[venkat easwar]

I apologize for multiple mails. In create I missed to add one column. The 
actual 
create statement is 


create table check_update( a int, b char, c int);
insert into check_update values (1,'venkat',22);
insert into check_update values (2,'venkat',23);

 
Failing query:

update check_update set b='venkat n' where b='venkat' order by a limit 1;

support link: http://www.sqlite.org/syntaxdiagrams.html#update-stmt-limited
VENKAT





From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tue, February 15, 2011 4:34:21 PM
Subject: Re: [sqlite] UPDATE WITH ORDER BY LIMIT ERROR

Forgot to mention what error I am getting.

near "order": syntax error 
near "limit": syntax error - if i remove the order by clause

VENKAT




________
From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tue, February 15, 2011 4:32:40 PM
Subject: [sqlite] UPDATE WITH ORDER BY LIMIT ERROR

Hi Buddies,

Sqlite support document says, update with limit and order by clauses are 
supported. But I found it actually not working. Sample DB schema,

create table check_update( a int, b char);
insert into check_update values (1,'venkat',22);
insert into check_update values (2,'venkat',23);

Now a update like this

update check_update set b='venkat n' where b='venkat' order by a limit 1;

should actually update the first row but not the second one as per document 
http://www.sqlite.org/syntaxdiagrams.html#update-stmt-limited 


The scenario is given just for reproduction, my actual scenes are different and 
which needs this implementation. :( 


Well, now am I missing something in the update.

VENKAT



  
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE WITH ORDER BY LIMIT ERROR

[venkat easwar]

Forgot to mention what error I am getting.

near "order": syntax error 
near "limit": syntax error - if i remove the order by clause

 VENKAT




________
From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tue, February 15, 2011 4:32:40 PM
Subject: [sqlite] UPDATE WITH ORDER BY LIMIT ERROR

Hi Buddies,

Sqlite support document says, update with limit and order by clauses are 
supported. But I found it actually not working. Sample DB schema,

create table check_update( a int, b char);
insert into check_update values (1,'venkat',22);
insert into check_update values (2,'venkat',23);

Now a update like this

update check_update set b='venkat n' where b='venkat' order by a limit 1;

should actually update the first row but not the second one as per document 
http://www.sqlite.org/syntaxdiagrams.html#update-stmt-limited 


The scenario is given just for reproduction, my actual scenes are different and 
which needs this implementation. :( 


Well, now am I missing something in the update.

VENKAT



  
___
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] UPDATE WITH ORDER BY LIMIT ERROR

[venkat easwar]

Hi Buddies,

Sqlite support document says, update with limit and order by clauses are 
supported. But I found it actually not working. Sample DB schema,

create table check_update( a int, b char);
insert into check_update values (1,'venkat',22);
insert into check_update values (2,'venkat',23);

Now a update like this

update check_update set b='venkat n' where b='venkat' order by a limit 1;

should actually update the first row but not the second one as per document 
http://www.sqlite.org/syntaxdiagrams.html#update-stmt-limited 


The scenario is given just for reproduction, my actual scenes are different and 
which needs this implementation. :( 


Well, now am I missing something in the update.

VENKAT



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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Harish,

Consider doing things outside sqlite. Definitely you should be using C API or 
php API or some other for making query to DB. Why don't you think of doing 
things outside sqlite
 VENKAT
Bug the Bugs




From: Harish CS 
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 6:08:29 PM
Subject: Re: [sqlite] Trigger to filter out characters


Simon,

Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
'9' and a few more characters. 
180+ characters need to be removed (filtered out).
So I will need to call Trim() so many times.
Since we cannot write loops I wonder how I can call it many times.

-Harish

Simon Slavin-3 wrote:
> 
> 
> On 7 Feb 2011, at 8:38am, Harish CS wrote:
> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
> 
> I don't think you need a trigger.  You might find the 'trim(X,Y)' function
> from
> 
> http://www.sqlite.org/lang_corefunc.html
> 
> helpful.  You'll have to list all characters  you /don't/ want left in the
> string.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863194.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Trigger to filter out characters

[venkat easwar]

Simon,

As far as I am aware trim will remove the characters only in ends, not in the 
middle. Assume an input like "12venkatpw", this cannot be trimmed. Or am I 
wrong 
somewhere. Guess NO.

Thanks
 VENKAT
Bug the Bugs.




From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Mon, February 7, 2011 6:19:04 PM
Subject: Re: [sqlite] Trigger to filter out characters


On 7 Feb 2011, at 12:38pm, Harish CS wrote:

> Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
> '9' and a few more characters. 
> 180+ characters need to be removed (filtered out).
> So I will need to call Trim() so many times.
> Since we cannot write loops I wonder how I can call it many times.

No, you just call it once and list all those characters in the second parameter:

UPDATE contacts SET phoneNumber to TRIM(rawPhoneNumber, 'abcdefghi ... 
xyz!@£$%^&()_-={}[]:"|;''\<>?,./')

Note that as above to put an apostrophe inside an SQLite string you double-it.

Simon.
___
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] Trigger to filter out characters

[venkat easwar]

Harish, neither trigger nor trim will be useful for you directly as such. I 
suggest writing a custom sqlite function to perform the operation. You try 
writing custom functions, if I figure out any idea, I will get back to you.

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

VENKAT
Bug the Bugs




From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 6:01:31 PM
Subject: Re: [sqlite] Trigger to filter out characters


Venkat,

Thanks. 
But this is not filtering characters.
For example, if user enters '12ab34', it should remove 'ab' and enter
'1234'.

Thanks,
Harish


venkat easwar wrote:
> 
> Well. I thought it should be an easy deal for you hence left that part.
> Now 
> giving the conditioned trigger assuming the following condition. I leave
> the 
> testing part to your concern.
> 
>>I need to take out the value of A, filter out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w'
> 
> create trigger if not exists  after insert on  when 
> new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
> beginupdate  B=new.A;
> end;
> 
> Only if the values inside the braces are inserted it will be copied to
> column B.
> 
> 
> VENKAT
> 
> Bug the Bugs
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:59:50 PM
> Subject: Re: [sqlite] Trigger to filter out characters
> 
> 
> Hi Venkat,
> 
> Could you please show me how to write the condition (to filter out
> characters)?
> 
> Thanks,
> Harish
> 
> 
> 
> venkat easwar wrote:
>> 
>> Hi Harish,
>> 
>> Yes it is possible. Look below for solution.
>> 
>> create trigger if not exists  after insert on 
>> when 
>> new.A=
>> begin
>> update  B=new.A;
>> end;
>> 
>> 
>> There is nothing tricky or hard for inserting into another table, same
>> things 
>> should go.
>> 
>> VENKAT
>> Bug the Bugs
>> 
>> 
>> 
>> 
>> 
>> From: Harish CS <cshar...@gmail.com>
>> To: sqlite-users@sqlite.org
>> Sent: Mon, February 7, 2011 2:08:16 PM
>> Subject: [sqlite]  Trigger to filter out characters
>> 
>> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
>> 
>> Thanks
>> -Harish
>> 
>> -- 
>> View this message in context: 
>> 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>> 
>> ___
>> 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
>> 
>> 
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863148.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Trigger to filter out characters

[venkat easwar]

Well. This is the one I did not expect. Update without condition is updating 
all 
the rows. Hence make it with conditioned update :) :)

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
begin
update  set B=new.A where A=new.A;
end;
 
If you find more bugs, well it is good, we will get them bugged.
VENKAT
Bug the Bugs




From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, February 7, 2011 5:19:51 PM
Subject: Re: [sqlite] Trigger to filter out characters

OOPS.. a mistake.. find the corrected things below.

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  set B=new.A;
end;

Missed set in my last mail. It would have given you a syntax error.
VENKAT
Bug the Bugs



____
From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, February 7, 2011 4:41:19 PM
Subject: Re: [sqlite] Trigger to filter out characters


Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
>  Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> 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
> 
> 

-- 
View this  message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

OOPS.. a mistake.. find the corrected things below.

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  set B=new.A;
end;

Missed set in my last mail. It would have given you a syntax error.
VENKAT
Bug the Bugs




From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, February 7, 2011 4:41:19 PM
Subject: Re: [sqlite] Trigger to filter out characters


Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
>  Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> 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
> 
> 

-- 
View this  message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Trigger to filter out characters

[venkat easwar]

Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Trigger to filter out characters

[venkat easwar]

Hi Harish,

Yes it is possible. Look below for solution.

create trigger if not exists  after insert on  when 
new.A=
begin
update  B=new.A;
end;


There is nothing tricky or hard for inserting into another table, same things 
should go.

VENKAT
Bug the Bugs





From: Harish CS 
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:08:16 PM
Subject: [sqlite]  Trigger to filter out characters


Table T has two varchar columns A and B. UI allows entering values into
column A only.
On insert/update of records, I need to take out the value of A, filter out
anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
it to column B. Is it possible to write a trigger to achieve this? If yes
please give me a sample. (Also, if column B is in another table, is it
possible?)

Thanks
-Harish

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Help on DELETE FROM...

[venkat easwar]

 Hi,

This is something which will work in round robin fashion. I will suggest 
something like a trigger which will delete the older entries, when the table is 
updated with new data. 


There are some papers out for implementing rrd from sql. Google them, they 
should be helpful


VENKAT
Bug the Bugs





From: Marcus Grimm 
To: General Discussion of SQLite Database 
Sent: Mon, January 17, 2011 5:47:45 PM
Subject: [sqlite] Help on DELETE FROM...

Hi List,

sorry for not being very sqlite specific here but I would
like to have an advice on a delete operation for which
I can't find the right sql command.
Currently I do it on C programming level using a loop
but I think there must be a better sql way.

Anyway, here is the story:

I have a table to record some history data, for example
items a user recently selected:

CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, 
DataID INTEGER);

That table needs to trace only the last 10 events, thus I would like
to remove entries from all users until each user has only 10 recent entries
in that table.

I can delete for a specific user (42), using this:

DELETE FROM THI WHERE
  (UserID=42) AND
  (ID NOT IN (SELECT ID FROM THI WHERE UserID=42 ORDER BY TimeStamp DESC LIMIT 
10));

But how do I do this for all users without using a surrounding loop
on application level ?

Thank you

Marcus
___
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] Using local variables through sqlite

[venkat easwar]

Thanks Igor,

I coded in the second syntax previously, but the first syntax looks good for 
me. 
I will use that one for my project.

Thanks for helping me out guys.

Cheers
Venkat





From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Sat, January 8, 2011 3:33:17 AM
Subject: Re: [sqlite] Using local variables through sqlite

On 1/7/2011 4:45 PM, Venkat Victorious wrote:
> On Fri, Jan 7, 2011 at 5:43 AM, BareFeetWare
>   wrote:
>> This is sometimes called "re-injection", where you're extracting the
>> results of one query, only to re-inject it into another query. In SQL, this
>> is a very inefficient way to do it. Most situations like this can be better
>> handled by combining the select and insert into one SQL command.
>>
> Combining select and insert will be useful if i am inserting from same
> table.

It works just as well between tables.

> Will this work even with inserting three values when one is a
> constant (something like 12), other one is variable from one table and third
> one is variable from some other table.

Yes.

> will
> the following thing work
>
> insert into  (a,b,c) select 1,b from  where
> , c from  where;

insert into TargetTable (a, b, c)
select 1, table1.b, table2.c
from table1, table2
where ;

-- or

select into TargetTable(a, b, c) values (1,
   (select b from table1 where ),
   (select c from table2 where ));

The first syntax allows inserting multiple records in a single 
statement, the second always inserts exactly one record.
-- 
Igor Tandetnik

___
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