Re: [sqlite] quote() function

2005-03-28 Thread Clay Dowling 

Iulian Popescu said:
 Hi,

 Would someone please explain me the semantics of this function? I've
 tried:

 SELECT quote('AAA'') AS value

 And it returns the following error:

 Error: near : syntax error (1)

Try SELECT quote('AAA\'') AS value

Not sure what purpose this function is supposed to server, since you need
to escape the string before passing it to this function.  As near as I can
tell this defeats the purpose of the function.

Clay

-- 
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development

RE: [sqlite] quote() function

2005-03-28 Thread Iulian Popescu 
That doesn't work either - I get the same error.
From the documentation:

This routine returns a string which is the value of its argument suitable
for inclusion into another SQL statement. Strings are surrounded by
single-quotes with escapes on interior quotes as needed. BLOBs are encoded
as hexadecimal literals. The current implementation of VACUUM uses this
function. The function is also useful when writing triggers to implement
undo/redo functionality.

Therefore I was expecting that something like:

SELECT quote(AA'AA)

would work in the sense that the actual SQL statement executed will be:

SELECT 'AA''AA'

but it doesn't seem to.

Iulian.

-Original Message-
From: Clay Dowling [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 28, 2005 12:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] quote() function


Iulian Popescu said:
 Hi,

 Would someone please explain me the semantics of this function? I've
 tried:

 SELECT quote('AAA'') AS value

 And it returns the following error:

 Error: near : syntax error (1)

Try SELECT quote('AAA\'') AS value

Not sure what purpose this function is supposed to server, since you need to
escape the string before passing it to this function.  As near as I can tell
this defeats the purpose of the function.

Clay

--
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web
development

Re: [sqlite] quote() function

2005-03-28 Thread Christopher Petrilli 
On Mon, 28 Mar 2005 11:57:10 -0500, Iulian Popescu [EMAIL PROTECTED] wrote:
 That doesn't work either - I get the same error.
 From the documentation:
 
 This routine returns a string which is the value of its argument suitable
 for inclusion into another SQL statement. Strings are surrounded by
 single-quotes with escapes on interior quotes as needed. BLOBs are encoded
 as hexadecimal literals. The current implementation of VACUUM uses this
 function. The function is also useful when writing triggers to implement
 undo/redo functionality.
 
 Therefore I was expecting that something like:
 
 SELECT quote(AA'AA)
 
 would work in the sense that the actual SQL statement executed will be:
 
 SELECT 'AA''AA'
 
 but it doesn't seem to.

I suspect it was intended to be used like this:

select quote(columname) from table;

Chris
-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

RE: [sqlite] quote() function

2005-03-28 Thread Iulian Popescu 
How about if the columnname is the AA'AA string? Wouldn't the still generate
a syntax error? 

-Original Message-
From: Christopher Petrilli [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 28, 2005 12:04 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] quote() function

On Mon, 28 Mar 2005 11:57:10 -0500, Iulian Popescu [EMAIL PROTECTED]
wrote:
 That doesn't work either - I get the same error.
 From the documentation:
 
 This routine returns a string which is the value of its argument 
 suitable for inclusion into another SQL statement. Strings are 
 surrounded by single-quotes with escapes on interior quotes as needed. 
 BLOBs are encoded as hexadecimal literals. The current implementation 
 of VACUUM uses this function. The function is also useful when writing 
 triggers to implement undo/redo functionality.
 
 Therefore I was expecting that something like:
 
 SELECT quote(AA'AA)
 
 would work in the sense that the actual SQL statement executed will be:
 
 SELECT 'AA''AA'
 
 but it doesn't seem to.

I suspect it was intended to be used like this:

select quote(columname) from table;

Chris
-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

Re: [sqlite] quote() function

2005-03-28 Thread D. Richard Hipp 
On Mon, 2005-03-28 at 12:03 -0500, Christopher Petrilli wrote:
 I suspect it was intended to be used like this:
 
 select quote(columname) from table;
 

Chris's suspicions are correct.  quote() is especially useful
within triggers where the trigger generates SQL code that will
undo the change the invoked the trigger.  This can be used to
generate a general-purpose undo/redo mechanism in a program that
uses sqlite as its primary data structure.
-- 
D. Richard Hipp [EMAIL PROTECTED]

RE: [sqlite] quote() function

2005-03-28 Thread Iulian Popescu 
Dear D. Hipp,

Thank you very much for getting involved in this discussion. Would you
please give a concrete example of use of quote()?
Is it something wrong with doing a:

SELECT quote('AA'AA')

Thank you very much,

Iulian.

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 28, 2005 12:15 PM
To: Christopher Petrilli
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] quote() function

On Mon, 2005-03-28 at 12:03 -0500, Christopher Petrilli wrote:
 I suspect it was intended to be used like this:
 
 select quote(columname) from table;
 

Chris's suspicions are correct.  quote() is especially useful within
triggers where the trigger generates SQL code that will undo the change the
invoked the trigger.  This can be used to generate a general-purpose
undo/redo mechanism in a program that uses sqlite as its primary data
structure.
--
D. Richard Hipp [EMAIL PROTECTED]

RE: [sqlite] quote() function

2005-03-28 Thread D. Richard Hipp 
On Mon, 2005-03-28 at 13:27 -0500, Iulian Popescu wrote:
 Is it something wrong with doing a:
 
 SELECT quote('AA'AA')
 

Yes, it is a syntax error.  The ' character within
a string in SQL is doubled to quote it - like in
Pascal.  That's the rules of SQL - I did not make
this stuff up.  

The following works:

   SELECT quote('AA''AA');

Compare the output against this:

   SELECT 'AA''AA';

-- 
D. Richard Hipp [EMAIL PROTECTED]

RE: [sqlite] quote() function

2005-03-28 Thread Iulian Popescu 
I'm really sorry D. Hipps. My question was silly - I didn't realize that the
function arguments are also parsed by the SQL engine and therefore they
should be valid SQL strings.

I apologize for the annoyance.

Iulian. 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 28, 2005 1:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] quote() function

On Mon, 2005-03-28 at 13:27 -0500, Iulian Popescu wrote:
 Is it something wrong with doing a:
 
 SELECT quote('AA'AA')
 

Yes, it is a syntax error.  The ' character within a string in SQL is
doubled to quote it - like in Pascal.  That's the rules of SQL - I did not
make this stuff up.  

The following works:

   SELECT quote('AA''AA');

Compare the output against this:

   SELECT 'AA''AA';

--
D. Richard Hipp [EMAIL PROTECTED]