Re: [sqlite] Parametrized Queries issue (Possible bug)

2010-06-14 Thread Israel Lins Albuquerque
I done a path to make this works, if someone wants! 

Now... 

CREATE TABLE a (a, b); 
INSERT INTO a VALUES (0, 0); 
INSERT INTO a VALUES (0, 1); 
INSERT INTO a VALUES (1, 0); 
INSERT INTO a VALUES (1, 1); 
SELECT * FROM a WHERE a.a = :a AND a.b = ?1; 
SELECT * FROM a WHERE a.a = ?1 AND a.b = :b; 

Have the same behavior! 


|Index: D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c 
|=== 
|--- D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (revisão 323) 
|+++ D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (cópia de trabalho) 
|@@ -557,9 +557,21 @@ 
| sqlite3ErrorMsg(pParse, "variable number must be between ?1 and ?%d", 
| db->aLimit[SQLITE_LIMIT_VARIABLE_NUMBER]); 
| } 
|- if( i>pParse->nVar ){ 
|- pParse->nVar = i; 
|+ 
|+ // give to expression parameter with same index, another number to avoid 
conflicts 
|+ // with named parameter (:AAA, $AAA, @AAA) and indexed parameter (?NNN) 
|+ for(i=0; inVarExpr; i++){ 
|+ Expr *pE = pParse->apVarExpr[i]; 
|+ assert( pE!=0 ); 
|+ if( pE->iColumn==pExpr->iColumn ){ 
|+ pE->iColumn = (ynVar)(++pParse->nVar); 
|+ break; 
|+ } 
| } 
|+ 
|+ if( pExpr->iColumn>pParse->nVar ){ 
|+ pParse->nVar = pExpr->iColumn; 
|+ } 
| }else{ 
| /* Wildcards like ":aaa", "$aaa" or "@aaa". Reuse the same variable 
| ** number as the prior appearance of the same name, or if the name 



-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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


Re: [sqlite] Parametrized Queries issue (Possible bug)

2010-06-14 Thread Israel Lins Albuquerque
I read that but that say "To avoid confusion, it is best to avoid mixing...", 
but you need never mix them, if you want have none collateral effects !!! 


- Mensagem original - 
De: "Jay A. Kreibich" <j...@kreibi.ch> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Segunda-feira, 14 de Junho de 2010 15:41:01 
Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) 

On Mon, Jun 14, 2010 at 03:03:52PM -0300, Israel Lins Albuquerque scratched on 
the wall: 
> I know what you booth are are saying. But if this is a limitation of sqlite, 
> this need be documented, and doesn't have any comment of this behavior. 

http://sqlite.org/lang_expr.html#varparam 

"Named parameters are also numbered. The number assigned is the 
next unused number. To avoid confusion, it is best to avoid mixing 
named and numbered parameters." 

The docs are pretty clear about what happened, and that you shouldn't 
do that, and that if you do it anyways you will likely be confused. 

-j 

-- 
Jay A. Kreibich < J A Y @ K R E I B I.C H > 

"Intelligence is like underwear: it is important that you have it, 
but showing it to the wrong people has the tendency to make them 
feel uncomfortable." -- Angela Johnson 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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


Re: [sqlite] Parametrized Queries issue (Possible bug)

2010-06-14 Thread Jay A. Kreibich
On Mon, Jun 14, 2010 at 03:03:52PM -0300, Israel Lins Albuquerque scratched on 
the wall:
> I know what you booth are are saying. But if this is a limitation of sqlite, 
> this need be documented, and doesn't have any comment of this behavior. 

  http://sqlite.org/lang_expr.html#varparam

 "Named parameters are also numbered. The number assigned is the
  next unused number. To avoid confusion, it is best to avoid mixing
  named and numbered parameters."

  The docs are pretty clear about what happened, and that you shouldn't
  do that, and that if you do it anyways you will likely be confused.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Parametrized Queries issue (Possible bug)

2010-06-14 Thread Israel Lins Albuquerque
I know what you booth are are saying. But if this is a limitation of sqlite, 
this need be documented, and doesn't have any comment of this behavior. 
And the name of parameter ':a' are changed, we can't set his value, 
only using the index or your new name '?1' !!! 

- Mensagem original - 
De: "David Bicking" <dbic...@yahoo.com> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Segunda-feira, 14 de Junho de 2010 13:06:02 
Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) 

I think in your first example, the :a comes first, so it is assigned the first 
index value. You then use ?1, which also uses the first parameter index. 

In the second, you use ?1 first, then :b, which sees the first index has been 
used and thus assigns it to the second index. 

As I believe Jay said, you should not mix named and numbered parameter syntax. 

David 

--- On Mon, 6/14/10, Israel Lins Albuquerque <israel...@polibrasnet.com.br> 
wrote: 

> From: Israel Lins Albuquerque <israel...@polibrasnet.com.br> 
> Subject: Re: [sqlite] Parametrized Queries issue (Possible bug) 
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
> Date: Monday, June 14, 2010, 10:55 AM 
> Once more detail... 
> 
> Doing this... 
> 
> SELECT * 
> FROM a 
> WHERE a.a = :a 
> AND a.b = ?1; 
> 
> and this... 
> 
> SELECT * 
> FROM a 
> WHERE a.a = ?1 
> AND a.b = :b; 
> 
> Will have different behavior! 
> 
> 
> 
> - Mensagem original - 
> De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> 
> 
> Para: "sqlite-users" <sqlite-users@sqlite.org> 
> 
> Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 
> Assunto: [sqlite] Parametrized Queries issue (Possible bug) 
> 
> 
> Using the example above I see one not documented issue. 
> When I use parameters ?NNN and : or @ or $ , 
> in same query as above some of then will be omitted. I 
> don't know if this is the expected behavior or not. 
> 
> CREATE TABLE a (a, b); 
> CREATE TABLE b (a, b); 
> 
> INSERT INTO a VALUES (0, 1); 
> INSERT INTO a VALUES (1, 0); 
> INSERT INTO a VALUES (1, 1); 
> INSERT INTO b VALUES (0, 1); 
> INSERT INTO b VALUES (1, 0); 
> INSERT INTO b VALUES (1, 1); 
> 
> SELECT * 
> FROM a 
> JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
> WHERE a.a = ?1; 
> 
> In this query as 2 parameters but will be see like only 
> one! 
> 
> -- 
> 
> Atenciosamente/Regards, 
> 
> Israel Lins Albuquerque 
> Desenvolvimento/Development 
> Polibrás Brasil Software Ltda. 
> 
> 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 
> 
> 
> -- 
> 
> Atenciosamente/Regards, 
> 
> Israel Lins Albuquerque 
> Desenvolvimento/Development 
> Polibrás Brasil Software Ltda. 
> 
> 
> ___ 
> 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 


-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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


Re: [sqlite] Parametrized Queries issue (Possible bug)

2010-06-14 Thread David Bicking
I think in your first example, the :a comes first, so it is assigned the first 
index value. You then use ?1, which also uses the first parameter index.

In the second, you use ?1 first, then :b, which sees the first index has been 
used and thus assigns it to the second index.

As I believe Jay said, you should not mix named and numbered parameter syntax.

David

--- On Mon, 6/14/10, Israel Lins Albuquerque <israel...@polibrasnet.com.br> 
wrote:

> From: Israel Lins Albuquerque <israel...@polibrasnet.com.br>
> Subject: Re: [sqlite] Parametrized Queries issue (Possible bug)
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Monday, June 14, 2010, 10:55 AM
> Once more detail... 
> 
> Doing this... 
> 
> SELECT * 
> FROM a 
> WHERE a.a = :a 
> AND a.b = ?1; 
> 
> and this... 
> 
> SELECT * 
> FROM a 
> WHERE a.a = ?1 
> AND a.b = :b; 
> 
> Will have different behavior! 
> 
> 
> 
> - Mensagem original - 
> De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br>
> 
> Para: "sqlite-users" <sqlite-users@sqlite.org>
> 
> Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 
> Assunto: [sqlite] Parametrized Queries issue (Possible bug)
> 
> 
> Using the example above I see one not documented issue.
> When I use parameters ?NNN and : or @ or $ , 
> in same query as above some of then will be omitted. I
> don't know if this is the expected behavior or not. 
> 
> CREATE TABLE a (a, b); 
> CREATE TABLE b (a, b); 
> 
> INSERT INTO a VALUES (0, 1); 
> INSERT INTO a VALUES (1, 0); 
> INSERT INTO a VALUES (1, 1); 
> INSERT INTO b VALUES (0, 1); 
> INSERT INTO b VALUES (1, 0); 
> INSERT INTO b VALUES (1, 1); 
> 
> SELECT * 
> FROM a 
> JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
> WHERE a.a = ?1; 
> 
> In this query as 2 parameters but will be see like only
> one! 
> 
> -- 
> 
> Atenciosamente/Regards, 
> 
> Israel Lins Albuquerque 
> Desenvolvimento/Development 
> Polibrás Brasil Software Ltda. 
> 
> 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org
> 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> -- 
> 
> Atenciosamente/Regards, 
> 
> Israel Lins Albuquerque 
> Desenvolvimento/Development 
> Polibrás Brasil Software Ltda. 
> 
> 
> ___
> 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] Parametrized Queries issue (Possible bug)

2010-06-14 Thread Jay A. Kreibich
On Mon, Jun 14, 2010 at 12:07:08PM -0300, Israel Lins Albuquerque scratched on 
the wall:
> Yes. I know that, but the names of parameters are different, 
> and I debug this and the parameter named as ':a' will be overwrited by '?1', 
> and if I use sqlite3_bind_parameter_index(stmt, ":a") will fail, returning 0. 
> But actually they are not the same. 

  Yes they are.  Names are just short-cuts to indexes.  Named
  parameters use "the first unused index."  You then set an explicit
  index that is already in use.  Each index can only have one name,
  so it is over-written with the last-used representation.



  To avoid such problems, it is strongly advised you do not mix
  parameter types.  Either use bare parameters, explicit indexes,
  or names-- but pick one and stick with it.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Parametrized Queries issue (Possible bug)

2010-06-14 Thread Israel Lins Albuquerque
Yes. I know that, but the names of parameters are different, 
and I debug this and the parameter named as ':a' will be overwrited by '?1', 
and if I use sqlite3_bind_parameter_index(stmt, ":a") will fail, returning 0. 
But actually they are not the same. 


- Mensagem original - 
De: "Jay A. Kreibich" <j...@kreibi.ch> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Segunda-feira, 14 de Junho de 2010 12:00:32 
Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) 

On Mon, Jun 14, 2010 at 11:51:07AM -0300, Israel Lins Albuquerque scratched on 
the wall: 
> Using the example above I see one not documented issue. When I use parameters 
> ?NNN and : or @ or $ , 
> in same query as above some of then will be omitted. I don't know if this is 
> the expected behavior or not. 
> 
> CREATE TABLE a (a, b); 
> CREATE TABLE b (a, b); 
> 
> INSERT INTO a VALUES (0, 1); 
> INSERT INTO a VALUES (1, 0); 
> INSERT INTO a VALUES (1, 1); 
> INSERT INTO b VALUES (0, 1); 
> INSERT INTO b VALUES (1, 0); 
> INSERT INTO b VALUES (1, 1); 
> 
> SELECT * 
> FROM a 
> JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
> WHERE a.a = ?1; 
> 
> In this query as 2 parameters but will be see like only one! 


It is expected. 

Nearly all SQL interfaces work this way. 

It is a feature. The same value will be used in both locations in 
the query. 

-j 

-- 
Jay A. Kreibich < J A Y @ K R E I B I.C H > 

"Intelligence is like underwear: it is important that you have it, 
but showing it to the wrong people has the tendency to make them 
feel uncomfortable." -- Angela Johnson 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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


Re: [sqlite] Parametrized Queries issue (Possible bug)

2010-06-14 Thread Jay A. Kreibich
On Mon, Jun 14, 2010 at 11:51:07AM -0300, Israel Lins Albuquerque scratched on 
the wall:
> Using the example above I see one not documented issue. When I use parameters 
> ?NNN and : or @ or $ , 
> in same query as above some of then will be omitted. I don't know if this is 
> the expected behavior or not. 
> 
> CREATE TABLE a (a, b); 
> CREATE TABLE b (a, b); 
> 
> INSERT INTO a VALUES (0, 1); 
> INSERT INTO a VALUES (1, 0); 
> INSERT INTO a VALUES (1, 1); 
> INSERT INTO b VALUES (0, 1); 
> INSERT INTO b VALUES (1, 0); 
> INSERT INTO b VALUES (1, 1); 
> 
> SELECT * 
> FROM a 
> JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
> WHERE a.a = ?1; 
> 
> In this query as 2 parameters but will be see like only one! 


   It is expected.

   Nearly all SQL interfaces work this way.

   It is a feature.  The same value will be used in both locations in
   the query.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Parametrized Queries issue (Possible bug)

2010-06-14 Thread Israel Lins Albuquerque
Once more detail... 

Doing this... 

SELECT * 
FROM a 
WHERE a.a = :a 
AND a.b = ?1; 

and this... 

SELECT * 
FROM a 
WHERE a.a = ?1 
AND a.b = :b; 

Will have different behavior! 



- Mensagem original - 
De: "Israel Lins Albuquerque"  
Para: "sqlite-users"  
Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 
Assunto: [sqlite] Parametrized Queries issue (Possible bug) 

Using the example above I see one not documented issue. When I use parameters 
?NNN and : or @ or $ , 
in same query as above some of then will be omitted. I don't know if this is 
the expected behavior or not. 

CREATE TABLE a (a, b); 
CREATE TABLE b (a, b); 

INSERT INTO a VALUES (0, 1); 
INSERT INTO a VALUES (1, 0); 
INSERT INTO a VALUES (1, 1); 
INSERT INTO b VALUES (0, 1); 
INSERT INTO b VALUES (1, 0); 
INSERT INTO b VALUES (1, 1); 

SELECT * 
FROM a 
JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
WHERE a.a = ?1; 

In this query as 2 parameters but will be see like only one! 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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


-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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