Re: [sqlite] Regarding String Comparision
thanks for your help
I got my work done
ritesh
On Mon, 2005-12-05 at 17:11, Rob Lohman wrote:
> It seems we are both right :)
>
> sqlite> create table test (filename varchar(1000) primary key);
> sqlite> insert into test (filename) values ('test');
> sqlite> select * from test where filename='test';
> test
> sqlite> select * from test where filename='tesT';
> sqlite> select * from test where filename like 'tesT';
> test
> sqlite> insert into test (filename) values ('testing');
> sqlite> select * from test where filename like 'tesT';
> test
>
> In other words, when doing field = 'value' it is case sensitive,
> with a field like 'value' it isn't. If you don't use '%value%' it
> will do an exact match (it seems).
>
> This was tested on 3.2.1
>
> - Original Message -
> From: "Brandon, Nicholas" <[EMAIL PROTECTED]>
> To:
> Sent: Monday, December 05, 2005 12:15 PM
> Subject: RE: [sqlite] Regarding String Comparision
>
>
> >
> > Rob/Ritesh
> >
> >>Also keep in mind that such a search is CASE SENSITIVE.
> >>There are two solutions to that, either makes the collation
> >>case insensitive or do a:
> >
> > I don't have access to SQLite immediately but I seem to remember in one of
> > my applications that the use of
> >
> > select * from test where filename like '%file%';
> >
> > would return string that are case INsensitive. (ie 'file', 'FILE', 'File'
> > ...)
> >
> > Ritesh I would suggest that you confirm this before relying on it.
> >
> > Regards
> > Nick
> >
> >
> > This email and any attachments are confidential to the intended
> > recipient and may also be privileged. If you are not the intended
> > recipient please delete it from your system and notify the sender.
> > You should not copy it or use it for any purpose nor disclose or
> > distribute its contents to any other person.
> >
Re: [sqlite] updating .schema after ALTER TABLE
On 12/5/05, Paul Bohme <[EMAIL PROTECTED]> wrote: > Aaron Peterson wrote: > > >I added a column to a table with ALTER TABLE. I'm sure it worked as > >expected since trying to add it again produces an error that the > >column already exists. However, .schema doesn't show the new column. > > > >Is there some other command that shows all current columns? > >Is there a way to update the information shown by .schema? > > > > Did you close and re-open the database? (Perhaps close sqlite3 and > relaunch?) Yeah, I just didn't see the new column because it appeard on the same line with one of the other columns. Sorry for the trouble... Aaron
[sqlite] updating .schema after ALTER TABLE
I added a column to a table with ALTER TABLE. I'm sure it worked as expected since trying to add it again produces an error that the column already exists. However, .schema doesn't show the new column. Is there some other command that shows all current columns? Is there a way to update the information shown by .schema? Aaron
Re: [sqlite] sqlite3_create_function in delphi
They said it works only with plain (7-bit) ascii, no UTF. Dennis Cote wrote: Miha Vrhovnik wrote: Does anybody know how to add custom function to sqlite3 in Delphi? Cariotoglou Mike? I'd like to add function 'Lower' so I can match case insenisitive text columns in table. Regards, Miha There is a already a function lower() built in to SQLite. It returns a lowercase copy of a string. Dennis Cote
Re: [sqlite] sqlite3_create_function in delphi
Miha, what wrapper/library are you using? The following works for me (free). In your case, you need to change the sqlite3_result_int with sqlite3_result_text and pass the correct parameters to 'lower case' your var: - - - - - - - PROCEDURE fn(ctx:pointer;n:integer;args:ppchar);cdecl; VAR p : ppchar; theString : string; res:integer; BEGIN p := args; theString := trim(sqlite3_value_text(p^)); ...do something with theString... sqlite3_result_int(ctx,res); // < return a number based on string END; ... var i:integer; begin i := sqlite3_create_function(db3,'myfn',1,SQLITE_UTF8,nil,@fn,nil,nil); s := 'select myfn(thestring) from theTable;' ...execute statement... end; - - - - - - Miha Vrhovnik wrote: Does anybody know how to add custom function to sqlite3 in Delphi? Cariotoglou Mike? I'd like to add function 'Lower' so I can match case insenisitive text columns in table. Regards, Miha
Re: [sqlite] problem with blobs (perl code)
On Mon, Dec 05, 2005 at 08:23:19AM -0500, [EMAIL PROTECTED] wrote:
> > OK, so 1.11 is on CPAN which fixes this. However I have another bug
> > report about this not working for user defined functions, where I do
> > this:
> >
> > s = SvPV(result, len);
> > sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
I'm the original reporter of that bug, and I can confirm that while it
did not work in DBD::SQLite 1.09, it works without problem in 1.11.
sqlite3_result_text() appears to deal fine with embedded NUL's.
Attached is a tiny C program I just used to confirm this to myself.
Large thanks to Matt and Richard for so quickly ending up with a great
solution that just transparently works!
--nate
#include
#include
/* gcc test_blob_as_text.c -lsqlite3 -o test_blob_as_text */
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i
Re: [sqlite] problem with blobs (perl code)
On 5 Dec 2005, at 13:23, [EMAIL PROTECTED] wrote: I added a test case (check-in [2798]) that checks to make sure that sqlite3_result_text is able to deal with embedded '\000' characters in a string. I appears to work fine. I cannot reproduce the problem Can you suggest other ways of producing the problem? Not yet. Let me write a test case and see if there's really a bug or not. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] sqlite3_create_function in delphi
Miha Vrhovnik wrote: Does anybody know how to add custom function to sqlite3 in Delphi? Cariotoglou Mike? I'd like to add function 'Lower' so I can match case insenisitive text columns in table. Regards, Miha There is a already a function lower() built in to SQLite. It returns a lowercase copy of a string. Dennis Cote
Re: [sqlite] problem with blobs (perl code)
Matt Sergeant <[EMAIL PROTECTED]> wrote:
>
> OK, so 1.11 is on CPAN which fixes this. However I have another bug
> report about this not working for user defined functions, where I do
> this:
>
> s = SvPV(result, len);
> sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
>
> (SvPV is a macro that retrieves a char* from result, and as a side
> effect sets len to the length of the string in bytes, even if it
> contains nuls).
>
> Is this maybe a bug in sqlite3_result_text()? I could patch it to do:
>
> if (memchr(s, 0, len)) {
> /* if the result contains NUL(s) treat it as a blob */
> sqlite3_result_blob(context, s, len, SQLITE_TRANSIENT );
> }
> else {
> sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
> }
>
> But that seems a waste of resources if it's a bug in
> sqlite3_result_text().
>
I added a test case (check-in [2798]) that checks to make sure
that sqlite3_result_text is able to deal with embedded '\000'
characters in a string. I appears to work fine. I cannot
reproduce the problem
Can you suggest other ways of producing the problem?
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] problem with blobs (perl code)
On 2 Dec 2005, at 13:07, [EMAIL PROTECTED] wrote:
Right. So it's retreival that's the issue when this occurs, because I
do:
int col_type = sqlite3_column_type(stmt, i);
and it returns SQLITE_TEXT, so I then do:
val = (char*)sqlite3_column_text(stmt, i);
which doesn't return a length for me.
Would sqlite3_column_bytes() return the right length there rather than
me doing strlen() on the resulting data?
yes it will.
OK, so 1.11 is on CPAN which fixes this. However I have another bug
report about this not working for user defined functions, where I do
this:
s = SvPV(result, len);
sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
(SvPV is a macro that retrieves a char* from result, and as a side
effect sets len to the length of the string in bytes, even if it
contains nuls).
Is this maybe a bug in sqlite3_result_text()? I could patch it to do:
if (memchr(s, 0, len)) {
/* if the result contains NUL(s) treat it as a blob */
sqlite3_result_blob(context, s, len, SQLITE_TRANSIENT );
}
else {
sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
}
But that seems a waste of resources if it's a bug in
sqlite3_result_text().
Matt.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
Re: [sqlite] Regarding String Comparision
It seems we are both right :)
sqlite> create table test (filename varchar(1000) primary key);
sqlite> insert into test (filename) values ('test');
sqlite> select * from test where filename='test';
test
sqlite> select * from test where filename='tesT';
sqlite> select * from test where filename like 'tesT';
test
sqlite> insert into test (filename) values ('testing');
sqlite> select * from test where filename like 'tesT';
test
In other words, when doing field = 'value' it is case sensitive,
with a field like 'value' it isn't. If you don't use '%value%' it
will do an exact match (it seems).
This was tested on 3.2.1
- Original Message -
From: "Brandon, Nicholas" <[EMAIL PROTECTED]>
To:
Sent: Monday, December 05, 2005 12:15 PM
Subject: RE: [sqlite] Regarding String Comparision
Rob/Ritesh
Also keep in mind that such a search is CASE SENSITIVE.
There are two solutions to that, either makes the collation
case insensitive or do a:
I don't have access to SQLite immediately but I seem to remember in one of
my applications that the use of
select * from test where filename like '%file%';
would return string that are case INsensitive. (ie 'file', 'FILE', 'File'
...)
Ritesh I would suggest that you confirm this before relying on it.
Regards
Nick
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
RE: [sqlite] Regarding String Comparision
Rob/Ritesh >Also keep in mind that such a search is CASE SENSITIVE. >There are two solutions to that, either makes the collation >case insensitive or do a: I don't have access to SQLite immediately but I seem to remember in one of my applications that the use of select * from test where filename like '%file%'; would return string that are case INsensitive. (ie 'file', 'FILE', 'File' ...) Ritesh I would suggest that you confirm this before relying on it. Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
Re: [sqlite]cross compiling with powerpc
configure: error: unable to find a compiler for building build tools Have you tried setting the path where the powerpc compiler in installed? Regards, Ajay. On 12/2/05, Julien LEFORT <[EMAIL PROTECTED]> wrote: > > Hi, > I've been struggling for few hours trying to run the configure script the > right way so I can cross compile sqlite3 for a powerpc target. > I guess it is only a problem of options I pass into the configure script. > here is the command I pass: > > CC=powerpc-linux-gcc CXX=powerpc-linux-g++ BUILD_CC=gcc ../configure > --host=i686-linux --target=powerpc-linux --disable-shared --disable-tcl > > and the error I get: > > Version number set to 3002007 > checking for i686-linux-gcc... (cached) powerpc-linux-gcc > checking whether we are using the GNU C compiler... (cached) yes > checking whether powerpc-linux-gcc accepts -g... (cached) yes > checking for powerpc-linux-gcc option to accept ANSI C... (cached) none > needed > configure: error: unable to find a compiler for building build tools > > Anyone has any idea? > Thanks > > Julien > -- Prosperity is only an instrument to be used, not a deity to be worshipped.
Re: [sqlite] Regarding String Comparision
Keep in mind that string should be surrounded by
single quotes instead of double quotes:
exact match:
select * from test where filename = 'file';
partial match:
select * from test where filename like '%file%';
Also keep in mind that such a search is CASE SENSITIVE.
There are two solutions to that, either makes the collation
case insensitive or do a:
select * from test where lower(filename) = lower('TESTFILE.txt');
Hower it would be better to do the first lowercase in your
program code when inserting and the second when doing
the select so you don't have to do both in the select.
Rob
p.s. keep in mind that filenames can have single quotes in
them (at least Windows filenames can)! So you should
either call the sqlite interface with parameters (preferred)
or do a search and replace ' with '' in your filenames
before executing either the insert or the select
- Original Message -
From: "Martin Engelschalk" <[EMAIL PROTECTED]>
To:
Sent: Monday, December 05, 2005 10:06 AM
Subject: Re: [sqlite] Regarding String Comparision
Hi,
I am not sure if i understand your question right. Perhaps the following
will help:
The behaviour depends on the comparison operator.
If you do
select ...
from FileTable
where FileName = "testfile.txt"
then the whole string will be matched
If you do
select ...
from FileTable
where FileName like "%/testfile.txt"
then you would get all files named "testfile.txt" regardless of their
path. sqlite will search for strings ending in "/testfile.txt".
Martin
Ritesh Kapoor schrieb:
Hi,
I am debugging a program which has a FileTable field called FileName
declared as-
"FileName varchar(1024) primary key"
It stores filenames including their paths e.g. - "myDir1/testfile.txt"
If I were to search in this FileTable in the column FileName for a
string "testfile.txt" would it return me the row "myDir1/testfile.txt"?
I'm not sure about this but currently I think this is what is
happening. Is this the default behavior of SQLite?
Are there any setting which would make it match the whole string? So
that I don't have ambigious situations where two rows might match to the
same search string.
Thanks & Regards,
ritesh
Re: [sqlite] Regarding String Comparision
Thanks Martin Thats exactly what I wanted to know. And I think this is the fastest reply/solution I ever got!! ritesh On Mon, 2005-12-05 at 14:36, Martin Engelschalk wrote: > Hi, > > I am not sure if i understand your question right. Perhaps the following > will help: > > The behaviour depends on the comparison operator. > > If you do > > select ... > from FileTable > where FileName = "testfile.txt" > > then the whole string will be matched > > If you do > > select ... > from FileTable > where FileName like "%/testfile.txt" > > then you would get all files named "testfile.txt" regardless of their > path. sqlite will search for strings ending in "/testfile.txt". > > Martin > > Ritesh Kapoor schrieb: > > >Hi, > > > >I am debugging a program which has a FileTable field called FileName > >declared as- > > > >"FileName varchar(1024) primary key" > > > >It stores filenames including their paths e.g. - "myDir1/testfile.txt" > > > >If I were to search in this FileTable in the column FileName for a > >string "testfile.txt" would it return me the row "myDir1/testfile.txt"? > > > >I'm not sure about this but currently I think this is what is > >happening. Is this the default behavior of SQLite? > > > >Are there any setting which would make it match the whole string? So > >that I don't have ambigious situations where two rows might match to the > >same search string. > > > >Thanks & Regards, > >ritesh > > > > > >
Re: [sqlite] Regarding String Comparision
Hi, I am not sure if i understand your question right. Perhaps the following will help: The behaviour depends on the comparison operator. If you do select ... from FileTable where FileName = "testfile.txt" then the whole string will be matched If you do select ... from FileTable where FileName like "%/testfile.txt" then you would get all files named "testfile.txt" regardless of their path. sqlite will search for strings ending in "/testfile.txt". Martin Ritesh Kapoor schrieb: Hi, I am debugging a program which has a FileTable field called FileName declared as- "FileName varchar(1024) primary key" It stores filenames including their paths e.g. - "myDir1/testfile.txt" If I were to search in this FileTable in the column FileName for a string "testfile.txt" would it return me the row "myDir1/testfile.txt"? I'm not sure about this but currently I think this is what is happening. Is this the default behavior of SQLite? Are there any setting which would make it match the whole string? So that I don't have ambigious situations where two rows might match to the same search string. Thanks & Regards, ritesh
[sqlite] Regarding String Comparision
Hi, I am debugging a program which has a FileTable field called FileName declared as- "FileName varchar(1024) primary key" It stores filenames including their paths e.g. - "myDir1/testfile.txt" If I were to search in this FileTable in the column FileName for a string "testfile.txt" would it return me the row "myDir1/testfile.txt"? I'm not sure about this but currently I think this is what is happening. Is this the default behavior of SQLite? Are there any setting which would make it match the whole string? So that I don't have ambigious situations where two rows might match to the same search string. Thanks & Regards, ritesh
