Re: [sqlite] Problem with binding parameters to LIKE

[Navaneeth.K.N]

On Sun, Oct 23, 2011 at 2:21 PM, Baruch Burstein  wrote:
> I have done something similar and it worked for me, but there is an issue
> with indexes you should take into account, as discussed here:
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-July/031470.html

Thanks a lot for that link. Sqlite was not using index even in my
case. So I removed the concatenation in the query and passed a single
parameters which will have % appended. I can see Sqlite uses index
now. Much better!

> .
> Out of curiosity (since this query and it's field names seem very similar to
> one I am using), what are you using this for?

I am developing a text editor for indic languages. It has some amount
of artificial inteligence builtin. I use the above said scheme to
remeber words entered into the editor.

> On Sun, Oct 23, 2011 at 7:36 PM, Igor Tandetnik  wrote:
>
> It should. Check the value of "data" variable - you are probably passing 
> something other than what you think you are. I don't think anything wrong 
> with the code you've shown - the problem must lie in the code you haven't.

My bad. I was passing an incorrectly encoded string. Corrected the
encoding and all started working.

> On Sun, Oct 23, 2011 at 4:28 PM, Richard Hipp  wrote:
>
> sqlite3_trace() does, since version 3.6.21 (2009-12-07).  What version of
> SQLite did you say you were using?

I got it working. I was reseting the parameters at a wrong location.
This is the reason why trace was not showing the parameter value. All
works well.

Thanks for the help.

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

Re: [sqlite] Problem with binding parameters to LIKE

[Igor Tandetnik]

Navaneeth.K.N  wrote:
> I am trying to use parameters in a LIKE query. I have the following
> code which uses Sqlite C/C++ API.
> 
> const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%'
> ORDER BY freq DESC LIMIT 10;";
> 
> int rc = sqlite3_prepare_v2 (db, sql, -1, , NULL);
> if ( rc != SQLITE_OK )
> return false;
> 
> sqlite3_bind_text ( stmt, 1, data , -1, NULL );
> 
> Unfortunaltly, this won't work.

It should. Check the value of "data" variable - you are probably passing 
something other than what you think you are. I don't think anything wrong with 
the code you've shown - the problem must lie in the code you haven't.

> When I execute
> the same statement after removing parameters it works perfectly.
> Something like,
> 
> const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%'
> ORDER BY freq DESC LIMIT 10;";

That can't be right - there's an extra apostrophe before % sign.
-- 
Igor Tandetnik

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

Re: [sqlite] Problem with binding parameters to LIKE

[Richard Hipp]

On Sat, Oct 22, 2011 at 11:53 PM, Navaneeth.K.N wrote:

>  I hooked up sqlite3_trace and
> sqlite3_profile and printed the SQL being executed. Unfortunatly,
> these routines won't give the SQL with values bound to it.
>

sqlite3_trace() does, since version 3.6.21 (2009-12-07).  What version of
SQLite did you say you were using?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with binding parameters to LIKE

[Baruch Burstein]

I have done something similar and it worked for me, but there is an issue
with indexes you should take into account, as discussed here:
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-July/031470.html
.
Out of curiosity (since this query and it's field names seem very similar to
one I am using), what are you using this for?

On Sun, Oct 23, 2011 at 5:53 AM, Navaneeth.K.N wrote:

> Hello,
>
> I am trying to use parameters in a LIKE query. I have the following
> code which uses Sqlite C/C++ API.
>
> const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%'
> ORDER BY freq DESC LIMIT 10;";
>
> int rc = sqlite3_prepare_v2 (db, sql, -1, , NULL);
> if ( rc != SQLITE_OK )
> return false;
>
> sqlite3_bind_text ( stmt, 1, data , -1, NULL );
>
> Unfortunaltly, this won't work. Sqlite is executing the statement
> successfully, but I am not getting the expected result. When I execute
> the same statement after removing parameters it works perfectly.
> Something like,
>
> const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%'
> ORDER BY freq DESC LIMIT 10;";
>
> It looks like concatentation with parameters is not working for some
> reason. To debug the issue, I hooked up sqlite3_trace and
> sqlite3_profile and printed the SQL being executed. Unfortunatly,
> these routines won't give the SQL with values bound to it.
>
> I am running out of ideas and any help would be great to address the
> problem.
>
> Thanks
> --
> -n
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with binding parameters to LIKE

[Navaneeth.K.N]

Hello,

I am trying to use parameters in a LIKE query. I have the following
code which uses Sqlite C/C++ API.

const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%'
ORDER BY freq DESC LIMIT 10;";

int rc = sqlite3_prepare_v2 (db, sql, -1, , NULL);
if ( rc != SQLITE_OK )
 return false;

sqlite3_bind_text ( stmt, 1, data , -1, NULL );

Unfortunaltly, this won't work. Sqlite is executing the statement
successfully, but I am not getting the expected result. When I execute
the same statement after removing parameters it works perfectly.
Something like,

const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%'
ORDER BY freq DESC LIMIT 10;";

It looks like concatentation with parameters is not working for some
reason. To debug the issue, I hooked up sqlite3_trace and
sqlite3_profile and printed the SQL being executed. Unfortunatly,
these routines won't give the SQL with values bound to it.

I am running out of ideas and any help would be great to address the problem.

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