Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Dominique Devienne]

On Fri, Jan 27, 2017 at 1:24 AM, Richard Hipp  wrote:

> On 1/26/17, dandl  wrote:
> >
> > 1. Why was VS the first compiler to detect this?
>
> Perhaps VS is the only compiler still in frequent use that does not
> support C99?


I guess David's question can also be rephrased as to why didn't the SQLite
team's
internal CI detect this before the community did?

SQLite is known for its outstanding testing in general, so I guess it's
surprising that if it
targets ANSI C 1989/1990 compatibility there isn't a build-bot that checks
this continuously. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[GB]

Clyde Eisenbeis schrieb am 26.01.2017 um 18:45:

It appears that sqlite3 is not part of System.Data.SQLite.

Aaah! Now we get somewhere! You should have told us from the beginning 
that you are using SQLite through the .NET/MONO Wrapper. All the 
mentioning of sqlite3_bind_* and the like refer to the native interface 
of SQLite.


Set up your command string with placeholders like others already stated:

" AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
LIKE ('%' || ?2 || '%')"

Now add your values to the Parameters-Collection of your Command-Object:

myCmd.Parameters.Add(new SQLiteParameter(...));

Also seedocumentation to System.Data.Common 
  (especially DbCommand and DbParameter) which System.Data.SQLite is derived from.


If you are working with VS on windows, download the .chm-File from 
http://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimetype=application/x-chm
Then right-click on the file and open its properties. In the lower part of that dialog 
you will find a security warning with an "Unblock"-button. Click it, and you 
should be able to open the file.

If you don't have a .chm-reader, see 
https://support.microsoft.com/en-us/help/917607/error-opening-help-in-windows-based-programs-feature-not-included-or-help-not-supported

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

Re: [sqlite] SQLite output mode

[Michael Falconer]

Just from some quick command line tool playing around, a dirt quick fix
presented itself. .mode csv followed by .mode column (following the .mode
ascii operation) seems to return the .column mode to normal. This on SQLite
3.16.2 on Linux command line. I'm not suggesting this as a fix as Dr Hipp
has already attended to that but I found it interesting anyway.



On 27 January 2017 at 12:54, Richard Hipp  wrote:

> On 1/26/17, Nikolas Manes  wrote:
> > Hello,
> > I am Nikolas and facing an issue with sqlite, could you please help?
> > You will find more details on the link bellow.
> > http://stackoverflow.com/q/41730574/6293866
>
> The ".mode ascii" command changes the default row and column separators.
>
> Check-in https://www.sqlite.org/src/info/58f02e6eae8fc9e2 enhances the
> command-line shell to change the row and column separator strings back
> to the default when you do ".mode column".  This enhancement will
> appear in the next release.  Or you can recompile using the code on
> trunk, which is stable.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite output mode

[Richard Hipp]

On 1/26/17, Nikolas Manes  wrote:
> Hello,
> I am Nikolas and facing an issue with sqlite, could you please help?
> You will find more details on the link bellow.
> http://stackoverflow.com/q/41730574/6293866

The ".mode ascii" command changes the default row and column separators.

Check-in https://www.sqlite.org/src/info/58f02e6eae8fc9e2 enhances the
command-line shell to change the row and column separator strings back
to the default when you do ".mode column".  This enhancement will
appear in the next release.  Or you can recompile using the code on
trunk, which is stable.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite output mode

[Simon Slavin]

On 26 Jan 2017, at 8:34pm, Nikolas Manes  wrote:

> I am Nikolas and facing an issue with sqlite, could you please help?
> You will find more details on the link bellow.
> http://stackoverflow.com/q/41730574/6293866
> * Please post your answer to Stack Overflow.

No.  I have no Stack Overflow account.

You can post your message here.  Include all the commands necessary to recreate 
your problem: the CREATE TABLE and INSERT commands, then the command which 
gives the unexpected results.

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

[sqlite] SQLite output mode

[Nikolas Manes]

Hello,
I am Nikolas and facing an issue with sqlite, could you please help?
You will find more details on the link bellow.
http://stackoverflow.com/q/41730574/6293866
* Please post your answer to Stack Overflow.
Thank you,
Nikolas.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[dspublic]

On 2017/01/26 6:23 PM, dspub...@freemail.hu wrote:

Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order by 
name desc) t order by name In this query, the server why doesn't use the 
nested "order by"?
I want to numbering the inner data descending, but I can't, because looks 
like, the server ignores it.
There exists no such thing as "nested" ORDER BY clauses. One and only one 
ORDER BY clause can ever determine ordering. The outer-most is the Boss.

What you perhaps are trying to achieve is this:

SELECT COUNT(B.name) AS id, A.name
 FROM example AS A
 LEFT JOIN example AS B ON B.name < A.name
GROUP BY A.name
ORDER BY A.name

Couldn't test it so there might be a typo in there, but the principle 
works.


Actually , I want to adopt some sql analytic function with application 
defined function, and I want to use them with "with" statement.

For example:

with
seg1 (account_num,account_type) as (select account_num,account_type from 
account where account_name like 'p%'  )
,seg2 (t10,t10rid,t12,t12rid) as (select t10.account_num 
,t10.rid,t12.item,t12.rid

 from (select *,row_number() as rid from seg1 order by account_num) t10
left join (select *,row_number() as rid from account_sub order by item DESC) 
t12 on t12.account_num=t10.account_num

)
select * from seg2 where t10rid<=5  order by t10rid,t12rid

('00032', 1, 'a', 1)
('00032ST', 2, 'a', 1)
('0014615', 3, 'a', 1)
('001604313', 4, 'A', 1) problem is here, it should be ('001604313', 4, 1, 
'B') because of order by item DESC
('001604313', 4, 'B', 2)problem is here, it should be ('001604313', 4, 1, 
'A') because of order by item DESC

('001610179', 5, 'a', 1)

Thanks, 


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

Re: [sqlite] Possible bug when creating a calculated index

[Simon Slavin]

On 26 Jan 2017, at 11:33pm, Ersin Akinci  wrote:

> (Please someone correct me if I'm wrong here. I'm a SQLite n00b who
> managed to stumble onto a subtle problem while debugging Rails. =)

Nope, you got it right.  And Michael has too.  I took your original report and 
tried to isolate the part of it I felt was a bug.  Although we were able to 
explain some of what you saw, I thought part of it signalled a genuine problem.

It’s a silly bug because it emerged when doing something nobody could ever want 
to do: make an index on a constant string.  But there’s a chance that the 
underlying fault could affect some command someone might want to do, so it’s 
probably best that someone on the development team investigate, even if just to 
say "We looked at it and it could never affect anything useful.".

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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Richard Hipp]

On 1/26/17, dandl  wrote:
>
> 1. Why was VS the first compiler to detect this?

Perhaps VS is the only compiler still in frequent use that does not support C99?

> 2. Is there an authoritative view on which standard Sqlite should comply
> with?
>

SQLite should compile with VS.  I checked in a fix for the problem
here: https://www.sqlite.org/src/info/50e60cb44fd3687d

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Empson

>>>The ANSI/ISO C 1990 standard states this in section 6.5.7, under Constraints:
>>>“All the expressions in an initializer for an object that has static storage 
>>>duration or in an initializer list for an object that has aggregate or union 
>>>type shall be constant expressions.”

>>>In this case the code is trying to initialize a field of an auto struct 
>>>using the db parameter passed to the function. That is not a constant 
>>>expression, and it is in an initializer list for an object that has 
>>>aggregate type (whether or not the object has static storage duration), so 
>>>is disallowed under ANSI/ISO C 1990.

>>>Later versions of the C standard removed the bit about aggregate or union 
>>>types, leaving only the static restriction, e.g. from section 6.7.8 of the 
>>>draft C99 standard:
>>>"All the expressions in an initializer for an object that has static storage 
>>>duration shall be constant expressions or string literals.”

I can confirm that. So in summary, the Sqlite code is not valid ANSI C (1990) 
but it is valid according to the C99 standard. It's only broken for old 
compilers, not new ones.

1. Why was VS the first compiler to detect this?
2. Is there an authoritative view on which standard Sqlite should comply with?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Thank you Simon,

I do see the inconsistency and thanks for those examples. I had answered
previously before I saw your explanation and I now see why there is
concern. It certainly appears to be inconsistent given such use cases.

On 27 January 2017 at 10:26, Michael Falconer 
wrote:

> Ersin,
>
> apologies if I seem to be suffering from terminal thickness, but I still
> don't get it. Why would I expect anything other than column interpretation
> from a single quoted argument. I *want to be told* that my column does
> not exist, I don't want a calculated index so why should I be expecting
> one. On the other hand if I choose double quotes I'm probably doing
> something different. Maybe someone else should weigh in and the penny will
> finally drop if I am missing the point, but I'm still not seeing crawly
> things. :-)
>
>
> On 27 January 2017 at 10:01, Ersin Akinci  wrote:
>
>> Michael,
>>
>> If I understood DRH and Simon correctly, I think the cause for concern
>> is that SQLite should be interpreting the single quotes as a string
>> literal, yet it interprets it as a column. Perhaps it's a strange
>> example (i.e., why would you want to index a string literal?), but
>> still, the behavior deviates from what's expected, the expected
>> behavior being that we should get a calculated index.
>>
>> Best,
>> Ersin
>>
>> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>>  wrote:
>> > Simon,
>> >
>> > as I see it there is no problem here. Explicit quoting regardless, the
>> > column does not exist and an error is returned, isn't this the expected
>> > outcome? In the DRH quoted section a reason is presented as to why no
>> error
>> > is returned due to a built in default action. This may or may not be a
>> > point for further analysis (ie. is this an appropriate default) but I'm
>> not
>> > seeing obvious crawly things. Perhaps it's me missing something Simon
>> but
>> > I'm not overly concerned about the above. Don't use double quotes
>> (always
>> > single) and it would appear things are just fine. You'll get told if
>> your
>> > column is non-existent.
>> >
>> >
>> > On 27 January 2017 at 00:12, Simon Slavin  wrote:
>> >
>> >>
>> >> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>> >>
>> >> > Bug is as follows:
>> >>
>> >> Anyone ?  Did I miss something and you’re all too polite to point it
>> out ?
>> >>
>> >> Simon.
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > Regards,
>> >  Michael.j.Falconer.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> Ersin Y. Akinci -- ersinakinci.com
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Ersin Akinci]

Michael,

I think part of the problem is that the "obviously" (or 'obviously',
ha) useful behavior here would be for single quoted arguments to
result in column interpretation. You're quite right to say that you
want to be told that your column doesn't exist--that is very helpful
information!. But if my understanding is correct, you *should* expect
a calculated index even though you don't *want* one because single
quoted arguments are always supposed to be interpreted as string
literals, not interpolated columns. So the correct behavior is the
unhelpful one, in this case.

tl;dr: You should expect something other than column interpretation
because single quoted arguments are never supposed to be interpolated,
even when that leads to not useful results (like indexing a string
literal).

(Please someone correct me if I'm wrong here. I'm a SQLite n00b who
managed to stumble onto a subtle problem while debugging Rails. =)

-Ersin

On Thu, Jan 26, 2017 at 3:26 PM, Michael Falconer
 wrote:
> Ersin,
>
> apologies if I seem to be suffering from terminal thickness, but I still
> don't get it. Why would I expect anything other than column interpretation
> from a single quoted argument. I *want to be told* that my column does not
> exist, I don't want a calculated index so why should I be expecting one. On
> the other hand if I choose double quotes I'm probably doing something
> different. Maybe someone else should weigh in and the penny will finally
> drop if I am missing the point, but I'm still not seeing crawly things. :-)
>
>
> On 27 January 2017 at 10:01, Ersin Akinci  wrote:
>
>> Michael,
>>
>> If I understood DRH and Simon correctly, I think the cause for concern
>> is that SQLite should be interpreting the single quotes as a string
>> literal, yet it interprets it as a column. Perhaps it's a strange
>> example (i.e., why would you want to index a string literal?), but
>> still, the behavior deviates from what's expected, the expected
>> behavior being that we should get a calculated index.
>>
>> Best,
>> Ersin
>>
>> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>>  wrote:
>> > Simon,
>> >
>> > as I see it there is no problem here. Explicit quoting regardless, the
>> > column does not exist and an error is returned, isn't this the expected
>> > outcome? In the DRH quoted section a reason is presented as to why no
>> error
>> > is returned due to a built in default action. This may or may not be a
>> > point for further analysis (ie. is this an appropriate default) but I'm
>> not
>> > seeing obvious crawly things. Perhaps it's me missing something Simon but
>> > I'm not overly concerned about the above. Don't use double quotes (always
>> > single) and it would appear things are just fine. You'll get told if your
>> > column is non-existent.
>> >
>> >
>> > On 27 January 2017 at 00:12, Simon Slavin  wrote:
>> >
>> >>
>> >> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>> >>
>> >> > Bug is as follows:
>> >>
>> >> Anyone ?  Did I miss something and you’re all too polite to point it
>> out ?
>> >>
>> >> Simon.
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > Regards,
>> >  Michael.j.Falconer.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> Ersin Y. Akinci -- ersinakinci.com
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Ersin Y. Akinci -- ersinakinci.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Ersin,

apologies if I seem to be suffering from terminal thickness, but I still
don't get it. Why would I expect anything other than column interpretation
from a single quoted argument. I *want to be told* that my column does not
exist, I don't want a calculated index so why should I be expecting one. On
the other hand if I choose double quotes I'm probably doing something
different. Maybe someone else should weigh in and the penny will finally
drop if I am missing the point, but I'm still not seeing crawly things. :-)


On 27 January 2017 at 10:01, Ersin Akinci  wrote:

> Michael,
>
> If I understood DRH and Simon correctly, I think the cause for concern
> is that SQLite should be interpreting the single quotes as a string
> literal, yet it interprets it as a column. Perhaps it's a strange
> example (i.e., why would you want to index a string literal?), but
> still, the behavior deviates from what's expected, the expected
> behavior being that we should get a calculated index.
>
> Best,
> Ersin
>
> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>  wrote:
> > Simon,
> >
> > as I see it there is no problem here. Explicit quoting regardless, the
> > column does not exist and an error is returned, isn't this the expected
> > outcome? In the DRH quoted section a reason is presented as to why no
> error
> > is returned due to a built in default action. This may or may not be a
> > point for further analysis (ie. is this an appropriate default) but I'm
> not
> > seeing obvious crawly things. Perhaps it's me missing something Simon but
> > I'm not overly concerned about the above. Don't use double quotes (always
> > single) and it would appear things are just fine. You'll get told if your
> > column is non-existent.
> >
> >
> > On 27 January 2017 at 00:12, Simon Slavin  wrote:
> >
> >>
> >> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
> >>
> >> > Bug is as follows:
> >>
> >> Anyone ?  Did I miss something and you’re all too polite to point it
> out ?
> >>
> >> Simon.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > Regards,
> >  Michael.j.Falconer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Ersin Y. Akinci -- ersinakinci.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Simon Slavin]

On 26 Jan 2017, at 10:56pm, Michael Falconer  
wrote:

> as I see it there is no problem here. Explicit quoting regardless, the
> column does not exist and an error is returned, isn't this the expected
> outcome?

Yet it’s not consistent.  I should have given more examples, so here they are.  
I can do this:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> CREATE TABLE reports (a INT, b TEXT);
sqlite> CREATE INDEX index_reports_addition ON reports (1 + 2);
sqlite> PRAGMA index_xinfo(index_reports_addition);
0|-2||0|BINARY|1
1|-1||0|BINARY|0

which is an index based on a constant numeric expression, so that works.  I can 
do this:

sqlite> CREATE INDEX index_reports_string ON reports ('a' + b + 'c');
sqlite> PRAGMA index_xinfo(index_reports_string);
0|-2||0|BINARY|1
1|-1||0|BINARY|0

but I cannot do this:

sqlite> CREATE INDEX index_reports_3strings ON reports ('a',b,'c');
Error: no such column: c

Why is it objecting to 'c' here and not 'a' ?  If I remove the 'c' I get

sqlite> CREATE INDEX index_reports_2strings ON reports ('a',b);
sqlite> PRAGMA index_xinfo(index_reports_2strings);
0|0|a|0|BINARY|1
1|1|b|0|BINARY|1
2|-1||0|BINARY|0
sqlite> CREATE INDEX index_reports_1string ON reports ('a');
sqlite> PRAGMA index_xinfo(index_reports_1string);
0|0|a|0|BINARY|1
1|-1||0|BINARY|0

so it seems there’s no problem with the 'a'.  So why is it objecting to the 'c' 
?

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

Re: [sqlite] Possible bug when creating a calculated index

[Ersin Akinci]

Michael,

If I understood DRH and Simon correctly, I think the cause for concern
is that SQLite should be interpreting the single quotes as a string
literal, yet it interprets it as a column. Perhaps it's a strange
example (i.e., why would you want to index a string literal?), but
still, the behavior deviates from what's expected, the expected
behavior being that we should get a calculated index.

Best,
Ersin

On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
 wrote:
> Simon,
>
> as I see it there is no problem here. Explicit quoting regardless, the
> column does not exist and an error is returned, isn't this the expected
> outcome? In the DRH quoted section a reason is presented as to why no error
> is returned due to a built in default action. This may or may not be a
> point for further analysis (ie. is this an appropriate default) but I'm not
> seeing obvious crawly things. Perhaps it's me missing something Simon but
> I'm not overly concerned about the above. Don't use double quotes (always
> single) and it would appear things are just fine. You'll get told if your
> column is non-existent.
>
>
> On 27 January 2017 at 00:12, Simon Slavin  wrote:
>
>>
>> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>>
>> > Bug is as follows:
>>
>> Anyone ?  Did I miss something and you’re all too polite to point it out ?
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Ersin Y. Akinci -- ersinakinci.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Simon,

as I see it there is no problem here. Explicit quoting regardless, the
column does not exist and an error is returned, isn't this the expected
outcome? In the DRH quoted section a reason is presented as to why no error
is returned due to a built in default action. This may or may not be a
point for further analysis (ie. is this an appropriate default) but I'm not
seeing obvious crawly things. Perhaps it's me missing something Simon but
I'm not overly concerned about the above. Don't use double quotes (always
single) and it would appear things are just fine. You'll get told if your
column is non-existent.


On 27 January 2017 at 00:12, Simon Slavin  wrote:

>
> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>
> > Bug is as follows:
>
> Anyone ?  Did I miss something and you’re all too polite to point it out ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Joe Mistachkin]

Clyde Eisenbeis wrote:
>
> I can't read CHM docs.
>

I'm somewhat curious about that.  Anyhow, there are alternatives:

1. IntelliSense in the Visual Studio IDE (this requires the
   XML file "System.Data.SQLite.xml", which should have been
   included with your DLL).

2. Read the XML documentation comments in the source code.

3. Use the "Doc\buildChm.tcl" tool in the source tree (it
   requires NDoc3 and Tcl).  It will *ALSO* generate the
   raw HTML files.

>
> I'd appreciate some example code which uses "sqlite3_bind_".
> Thanks!
>

When using System.Data.SQLite, you won't be using those native
APIs directly.

Numerous examples may be found at:

https://stackoverflow.com/

Some good keywords to search for include "System.Data.SQLite",
"SQLiteParameter", "SQLiteCommand", "SQLiteConnection",
"SQLiteDataReader", etc.

--
Joe Mistachkin @ https://urn.to/r/mistachkin

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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

I can't read CHM docs.

I'd appreciate some example code which uses "sqlite3_bind_".  Thanks!

On Thu, Jan 26, 2017 at 12:02 PM, Joe Mistachkin  wrote:
>
> Clyde Eisenbeis wrote:
>>
>> It appears that sqlite3 is not part of System.Data.SQLite.
>>
>
> Actually, it is.  However, the managed components provide a "wrapper"
> around the core library functionality.  The documentation, in Windows
> CHM format, is available here:
>
> https://urn.to/r/sds_docs
>
> The primary classes you'll want to look into include SQLiteConnection,
> SQLiteCommand, SQLiteParameter, and SQLiteDataReader.
>
> --
> Joe Mistachkin @ https://urn.to/r/mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[David Empson]

> On 26/01/2017, at 8:46 PM, Clemens Ladisch  wrote:
> 
> Ziemowit Laski wrote:
>> Visual C++
> 
> Which one?
> 
>> correctly catches this.
> 
> Oh?  What exactly is illegal about this?
> 
>>  struct IcuScalar {
>>const char *zName;/* Function name */
>>int nArg; /* Number of arguments */
>>int enc;  /* Optimal text encoding */
>>void *pContext;   /* sqlite3_user_data() context 
>> */
>>void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>>  } scalars[] = {
>>...
>>{"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>>  };

The ANSI/ISO C 1990 standard states this in section 6.5.7, under Constraints:

“All the expressions in an initializer for an object that has static storage 
duration or in an initializer list for an object that has aggregate or union 
type shall be constant expressions.”

In this case the code is trying to initialize a field of an auto struct using 
the db parameter passed to the function. That is not a constant expression, and 
it is in an initializer list for an object that has aggregate type (whether or 
not the object has static storage duration), so is disallowed under ANSI/ISO C 
1990.

Later versions of the C standard removed the bit about aggregate or union 
types, leaving only the static restriction, e.g. from section 6.7.8 of the 
draft C99 standard:

"All the expressions in an initializer for an object that has static storage 
duration shall be constant expressions or string literals.”

Visual C++ is based on C90, and assuming Wikipedia has the details right, it 
wasn’t until Visual C++ 2013 that Microsoft started making changes to support 
C99.

Should SQLite be aiming for the 1990 version of ANSI/ISO C as a baseline, for 
widest compatibility, or is it OK to drop older compilers and require C99 
compliance?

The only obvious reference I found in the SQLite documentation was 
http://www.sqlite.org/howtocompile.html which mentions “ANSI-C”. That is 
generally understood to mean the ANSI C 1989 standard, which was adopted 
internationally as ISO 9899:1990.

-- 
David Empson
demp...@emptech.co.nz

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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Joe Mistachkin]

Clyde Eisenbeis wrote:
>
> It appears that sqlite3 is not part of System.Data.SQLite. 
>

Actually, it is.  However, the managed components provide a "wrapper"
around the core library functionality.  The documentation, in Windows
CHM format, is available here:

https://urn.to/r/sds_docs

The primary classes you'll want to look into include SQLiteConnection,
SQLiteCommand, SQLiteParameter, and SQLiteDataReader.

--
Joe Mistachkin @ https://urn.to/r/mistachkin

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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

Usually I look at examples, and extract the relevant info.

After I have extracted relevant info, I create functions that are
universally valid for different programs ... see attachment for one
example.

In this case, I have attempted to create code using what has been
described so far.  However, when I started entering "sqlite3_bind_"
the compiler starts complaining.

It appears that sqlite3 is not part of System.Data.SQLite.

On Thu, Jan 26, 2017 at 10:59 AM, Warren Young  wrote:
> On Jan 26, 2017, at 8:40 AM, Clyde Eisenbeis  wrote:
>>
>> When I tried entering:
>>
>>  sqlite3_bind_
>>
>> the compiler starts complaining.
>
> What I wrote was "sqlite3_bind_*()” which you were expected to understand as 
> a reference to the 15 functions beginning with “sqlite3_bind_” listed on the 
> first page I linked you to yesterday.
>
>> I'm searching the internet for sqlite3_bind_* () examples.
>
> This is why programming by copy-and-paste is a problem.  When the copy-able 
> code runs out, you’re stuck.
>
> Take it as a challenge: write this one on your own using only the information 
> you’ve been given so far.  You’ll learn much.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[David Raymond]

Did you mean to order by name in both the inside and outside? Or did you mean 
"order by id desc" for the inner part? If so you can always do that on the 
outside, which as mentioned is the only one that counts in the end.

select row_number(name) as id, name from example order by name asc, id desc;



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of dspub...@freemail.hu
Sent: Thursday, January 26, 2017 11:24 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] application function value in sql where


Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order by name 
desc) t order by name
In this query, the server why doesn't use the nested "order by"?
I want to numbering the inner data descending, but I can't, because looks 
like, the server ignores it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[R Smith]

On 2017/01/26 6:23 PM, dspub...@freemail.hu wrote:


Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order 
by name desc) t order by name

In this query, the server why doesn't use the nested "order by"?
I want to numbering the inner data descending, but I can't, because 
looks like, the server ignores it.


There exists no such thing as "nested" ORDER BY clauses. One and only 
one ORDER BY clause can ever determine ordering. The outer-most is the Boss.


What you perhaps are trying to achieve is this:

SELECT COUNT(B.name) AS id, A.name
  FROM example AS A
  LEFT JOIN example AS B ON B.name < A.name
 GROUP BY A.name
 ORDER BY A.name

Couldn't test it so there might be a typo in there, but the principle works.

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

Re: [sqlite] application function value in sql where

[dspublic]

On 2017/01/25 3:18 PM, Dominique Devienne wrote:

On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp  wrote:


On 1/25/17, Richard Hipp  wrote:

On 1/25/17, dspub...@freemail.hu  wrote:

I get weird sql result with subselect too
select * from (select row_number(name) as id,name from example ) t 
where

id<=5


SQLite is invoking your row_number() function twice for each row -
once for the return value and a second time when evaluating the "id<5"
expression.

Further information:

The query optimizer is transforming your nested query into a single
query.  You wrote:

 SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE 
id<5;


Evaluated directly, this would require two separate queries.  For
improved performance, SQLite "flattens" the inner query into the
second, like this:

 SELECT func(name), name FROM example WHERE func(name)<5;


Hi Richard,

Would SQLite invoke the function only once though, had the function been
declared "deterministic"?
I.e. when compiling that "flattened" query into VDBE, it would use a
"register" to avoid calling it twice?


Not only would it avoid calling it twice, the QP might even cache it for 
future iterations with the same parameter... The immediate problem here is 
that his function is specifically NOT deterministic, it returns an 
ever-growing result upon each call (from the looks of it - untested).


Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order by name 
desc) t order by name

In this query, the server why doesn't use the nested "order by"?
I want to numbering the inner data descending, but I can't, because looks 
like, the server ignores it.

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


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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[R Smith]

On 2017/01/26 5:46 PM, Clyde Eisenbeis wrote:

To answer a previous question, here is an example of stCmdString contents:

   stCmdString = "SELECT fstPriority, fstInfo, fiKeyID FROM  PadTable
WHERE fstInfo LIKE '%macro%'"

This works.


Yes, and so it should, but this is what you think it /should/ end up as, 
we know what you intended, we are more interested in what it /actually/ 
ends up as...


Can you print the stCmdString after it gets assigned in your original 
question, and show us the text that ended up in there?


(We are skeptics to the assumption, so we want to see the real result) :)

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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Warren Young]

On Jan 26, 2017, at 8:40 AM, Clyde Eisenbeis  wrote:
> 
> When I tried entering:
> 
>  sqlite3_bind_
> 
> the compiler starts complaining.

What I wrote was "sqlite3_bind_*()” which you were expected to understand as a 
reference to the 15 functions beginning with “sqlite3_bind_” listed on the 
first page I linked you to yesterday.

> I'm searching the internet for sqlite3_bind_* () examples.

This is why programming by copy-and-paste is a problem.  When the copy-able 
code runs out, you’re stuck.

Take it as a challenge: write this one on your own using only the information 
you’ve been given so far.  You’ll learn much.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Simon Slavin]

On 26 Jan 2017, at 3:04pm, Clyde Eisenbeis  wrote:

>  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE ('%' || " +
> liststLikeFieldValue[0] + " || '%')";

Still looks like there would be spaces missing from that.  After SELECT and 
before FROM, for example.  Can you make your program print that string before 
using it and check its syntax ?

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

Re: [sqlite] text/numeric comparison confusion

[Dominique Devienne]

On Thu, Jan 26, 2017 at 5:04 PM, David Raymond 
wrote:

> Other thing to point out is that constants/(expressions that aren't coming
> from a field in a table) have "no affinity", so explicitly typing '25' is a
> no affinity, not a text affinity, and 25 is no affinity, not integer
> affinity. (Section 4.2 in the page linked by DRH)
>

And countless SQLite users must be bitten by this everyday! :)

Could we have a affinityof() built-in function similar to typeof()?

That would go a long way to understanding SQLite's peculiar type system via
self experimentation IMHO.

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] text/numeric comparison confusion

[Hick Gunter]

The constant '1' (of storage class TEXT) has no affinity, neither has the 
constant 1 (of storage class INTEGER), nor the result of an expression - with 
documented exceptions, notably CAST( AS ).

Consider:

1 = '1'
---
0

(1=1) = '1'
---
0

cast((1=1) as integer) = '1'

1

cast((1=1) as text) = '1'
-
1

(1=1) = cast('1' as integer)

1
 (1=1) = cast('1' as text)
-
1

The first and last CAST() examples are particularly interesting. The CAST 
doesn't change it's operand's storage class (argument is already in the target 
storage class); it only sets an affinity, so the subsequent comparison is 
prompted to convert the result on the *other* side of the equality operator.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jeffrey Mattox
Gesendet: Donnerstag, 26. Jänner 2017 11:03
An: SQLite mailing list 
Betreff: [sqlite] text/numeric comparison confusion

When used in a SELECT, I expect this comparison to be true (and it is):
   ( cast('25' as INTEGER) = 25 )  <--- true

But, why is this false:
   ( '25' = 25 )  <--- false?

and this is true:
   ( cast(25 as TEXT) = 25 )  <--- true

So, being that second comparison is false (why?), then why isn't the third 
comparison also false?



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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] text/numeric comparison confusion

[David Raymond]

Other thing to point out is that constants/(expressions that aren't coming from 
a field in a table) have "no affinity", so explicitly typing '25' is a no 
affinity, not a text affinity, and 25 is no affinity, not integer affinity. 
(Section 4.2 in the page linked by DRH)

From section 4.3 linked by DRH:

-Your first example is covered by bullet point 1 (integer compared to none -> 
the none becomes and integer)
-Your second example is covered by bullet point 3 (none compared to none so no 
conversion)
-Your third example is covered by bullet point 2 (text compared to none -> the 
none becomes text)

(Please correct me if I'm mixed up)

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, January 26, 2017 10:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] text/numeric comparison confusion

On 1/26/17, Jeffrey Mattox  wrote:
> When used in a SELECT, I expect this comparison to be true (and it is):
>( cast('25' as INTEGER) = 25 )  <--- true
>
> But, why is this false:
>( '25' = 25 )  <--- false?
>
> and this is true:
>( cast(25 as TEXT) = 25 )  <--- true
>
> So, being that second comparison is false (why?), then why isn't the third
> comparison also false?
>

https://www.sqlite.org/datatype3.html#compaff

The third condition applies to your first two examples.  The second
condition applies on the third example.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Andy Ling]

Now you know a bit more about what you are looking for, perhaps you
should revisit the links Warren Young gave a few days ago...

>> You’re looking for prepared statements with parameters:

>>https://sqlite.org/c3ref/stmt.html
>>https://sqlite.org/lang_expr.html#varparam


Regards

Andy


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clyde Eisenbeis
Sent: Thu 26 January 2017 15:41
To: SQLite mailing list
Subject: Re: [sqlite] Using SQLite, how can I search for chars that include a 
', similar to OLE DB .Parameters?

Not finding much.

I will try searching for a single word:

  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE ('%' || ?1 || '%') ";

When I tried entering:

  sqlite3_bind_

the compiler starts complaining.

On Thu, Jan 26, 2017 at 9:18 AM, Clyde Eisenbeis  wrote:
> I'm searching the internet for sqlite3_bind_* () examples.
>
> On Thu, Jan 26, 2017 at 9:14 AM, Andy Ling  wrote:
>> I think the point is, you need to use a prepared statement and bind the 
>> parameters to it.
>> The bind process handles the special characters.
>>
>> So you will need to create a command string with question mark operators in 
>> like
>>
>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>> LIKE ('%' || ?2 || '%')"
>>
>> Then use the sqlite3_bind_* () calls to replace the ?n markers with the " 
>> liststLikeFieldValue" strings.
>>
>> HTH
>>
>> Andy
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>> Behalf Of Clyde Eisenbeis
>> Sent: Thu 26 January 2017 15:04
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Using SQLite, how can I search for chars that include 
>> a ', similar to OLE DB .Parameters?
>>
>> I've also tried:
>>
>>   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE ('%' || " +
>> liststLikeFieldValue[0] + " || '%')";
>>
>> which does not work.
>>
>> On Thu, Jan 26, 2017 at 8:14 AM, heribert  wrote:
>>> There are some missing spaces i think:
>>>
>>> string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
>>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>>> liststLikeFieldValue[0] + "||'%'";
>>>
>>> Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:

 I tried replacing this:


string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
 + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
 "%'";

 with this:

string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
 + " WHERE " + stLikeFieldName + " LIKE '%'||" +
 liststLikeFieldValue[0] + "||'%'";

 Does not work.

 On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:
>
> On 1/25/17, Warren Young  wrote:
>>
>> stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;
>>
>> Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
>> the
>> string.
>
> Not quite.  You cannot have parameters embedded in the middle of
> strings.  The whole string is replaced by a parameter.
>
> stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"
>
> Then the application has to prepend and append the "%" on the strings
> before binding.  Or, if your application does not want to do that:
>
> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
> LIKE ('%' || ?2 || '%')"
>
> Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
> || is the string concatenation operator in SQL.)
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

 ___
 sqlite-users mailing list
 sqlite-users@mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ---
>> This email has been scanned for email related threats and delivered safely 
>> by Mimecast.
>> For more information please visit http://www.mimecast.com
>> 

Re: [sqlite] text/numeric comparison confusion

[Richard Hipp]

On 1/26/17, Jeffrey Mattox  wrote:
> When used in a SELECT, I expect this comparison to be true (and it is):
>( cast('25' as INTEGER) = 25 )  <--- true
>
> But, why is this false:
>( '25' = 25 )  <--- false?
>
> and this is true:
>( cast(25 as TEXT) = 25 )  <--- true
>
> So, being that second comparison is false (why?), then why isn't the third
> comparison also false?
>

https://www.sqlite.org/datatype3.html#compaff

The third condition applies to your first two examples.  The second
condition applies on the third example.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

To answer a previous question, here is an example of stCmdString contents:

  stCmdString = "SELECT fstPriority, fstInfo, fiKeyID FROM  PadTable
WHERE fstInfo LIKE '%macro%'"

This works.


On Thu, Jan 26, 2017 at 9:40 AM, Clyde Eisenbeis  wrote:
> Not finding much.
>
> I will try searching for a single word:
>
>   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE ('%' || ?1 || '%') ";
>
> When I tried entering:
>
>   sqlite3_bind_
>
> the compiler starts complaining.
>
> On Thu, Jan 26, 2017 at 9:18 AM, Clyde Eisenbeis  wrote:
>> I'm searching the internet for sqlite3_bind_* () examples.
>>
>> On Thu, Jan 26, 2017 at 9:14 AM, Andy Ling  wrote:
>>> I think the point is, you need to use a prepared statement and bind the 
>>> parameters to it.
>>> The bind process handles the special characters.
>>>
>>> So you will need to create a command string with question mark operators in 
>>> like
>>>
>>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>>> LIKE ('%' || ?2 || '%')"
>>>
>>> Then use the sqlite3_bind_* () calls to replace the ?n markers with the " 
>>> liststLikeFieldValue" strings.
>>>
>>> HTH
>>>
>>> Andy
>>>
>>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>>> Behalf Of Clyde Eisenbeis
>>> Sent: Thu 26 January 2017 15:04
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] Using SQLite, how can I search for chars that include 
>>> a ', similar to OLE DB .Parameters?
>>>
>>> I've also tried:
>>>
>>>   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>>> + " WHERE " + stLikeFieldName + " LIKE ('%' || " +
>>> liststLikeFieldValue[0] + " || '%')";
>>>
>>> which does not work.
>>>
>>> On Thu, Jan 26, 2017 at 8:14 AM, heribert  wrote:
 There are some missing spaces i think:

 string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
 + " WHERE " + stLikeFieldName + " LIKE '%'||" +
 liststLikeFieldValue[0] + "||'%'";

 Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:
>
> I tried replacing this:
>
>
>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
> "%'";
>
> with this:
>
>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
> liststLikeFieldValue[0] + "||'%'";
>
> Does not work.
>
> On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:
>>
>> On 1/25/17, Warren Young  wrote:
>>>
>>> stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;
>>>
>>> Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
>>> the
>>> string.
>>
>> Not quite.  You cannot have parameters embedded in the middle of
>> strings.  The whole string is replaced by a parameter.
>>
>> stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"
>>
>> Then the application has to prepend and append the "%" on the strings
>> before binding.  Or, if your application does not want to do that:
>>
>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>> LIKE ('%' || ?2 || '%')"
>>
>> Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
>> || is the string concatenation operator in SQL.)
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


 ___
 sqlite-users mailing list
 sqlite-users@mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> ---
>>> This email has been scanned for email related threats and delivered safely 
>>> by Mimecast.
>>> For more information please visit http://www.mimecast.com
>>> ---
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> 

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

Not finding much.

I will try searching for a single word:

  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE ('%' || ?1 || '%') ";

When I tried entering:

  sqlite3_bind_

the compiler starts complaining.

On Thu, Jan 26, 2017 at 9:18 AM, Clyde Eisenbeis  wrote:
> I'm searching the internet for sqlite3_bind_* () examples.
>
> On Thu, Jan 26, 2017 at 9:14 AM, Andy Ling  wrote:
>> I think the point is, you need to use a prepared statement and bind the 
>> parameters to it.
>> The bind process handles the special characters.
>>
>> So you will need to create a command string with question mark operators in 
>> like
>>
>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>> LIKE ('%' || ?2 || '%')"
>>
>> Then use the sqlite3_bind_* () calls to replace the ?n markers with the " 
>> liststLikeFieldValue" strings.
>>
>> HTH
>>
>> Andy
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>> Behalf Of Clyde Eisenbeis
>> Sent: Thu 26 January 2017 15:04
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Using SQLite, how can I search for chars that include 
>> a ', similar to OLE DB .Parameters?
>>
>> I've also tried:
>>
>>   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE ('%' || " +
>> liststLikeFieldValue[0] + " || '%')";
>>
>> which does not work.
>>
>> On Thu, Jan 26, 2017 at 8:14 AM, heribert  wrote:
>>> There are some missing spaces i think:
>>>
>>> string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
>>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>>> liststLikeFieldValue[0] + "||'%'";
>>>
>>> Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:

 I tried replacing this:


string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
 + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
 "%'";

 with this:

string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
 + " WHERE " + stLikeFieldName + " LIKE '%'||" +
 liststLikeFieldValue[0] + "||'%'";

 Does not work.

 On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:
>
> On 1/25/17, Warren Young  wrote:
>>
>> stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;
>>
>> Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
>> the
>> string.
>
> Not quite.  You cannot have parameters embedded in the middle of
> strings.  The whole string is replaced by a parameter.
>
> stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"
>
> Then the application has to prepend and append the "%" on the strings
> before binding.  Or, if your application does not want to do that:
>
> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
> LIKE ('%' || ?2 || '%')"
>
> Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
> || is the string concatenation operator in SQL.)
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

 ___
 sqlite-users mailing list
 sqlite-users@mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ---
>> This email has been scanned for email related threats and delivered safely 
>> by Mimecast.
>> For more information please visit http://www.mimecast.com
>> ---
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] text/numeric comparison confusion

[Jeffrey Mattox]

When used in a SELECT, I expect this comparison to be true (and it is):
   ( cast('25' as INTEGER) = 25 )  <--- true

But, why is this false:
   ( '25' = 25 )  <--- false?

and this is true:
   ( cast(25 as TEXT) = 25 )  <--- true

So, being that second comparison is false (why?), then why isn't the third 
comparison also false?



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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

I'm searching the internet for sqlite3_bind_* () examples.

On Thu, Jan 26, 2017 at 9:14 AM, Andy Ling  wrote:
> I think the point is, you need to use a prepared statement and bind the 
> parameters to it.
> The bind process handles the special characters.
>
> So you will need to create a command string with question mark operators in 
> like
>
> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
> LIKE ('%' || ?2 || '%')"
>
> Then use the sqlite3_bind_* () calls to replace the ?n markers with the " 
> liststLikeFieldValue" strings.
>
> HTH
>
> Andy
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Clyde Eisenbeis
> Sent: Thu 26 January 2017 15:04
> To: SQLite mailing list
> Subject: Re: [sqlite] Using SQLite, how can I search for chars that include a 
> ', similar to OLE DB .Parameters?
>
> I've also tried:
>
>   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE ('%' || " +
> liststLikeFieldValue[0] + " || '%')";
>
> which does not work.
>
> On Thu, Jan 26, 2017 at 8:14 AM, heribert  wrote:
>> There are some missing spaces i think:
>>
>> string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>> liststLikeFieldValue[0] + "||'%'";
>>
>> Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:
>>>
>>> I tried replacing this:
>>>
>>>
>>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>>> + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
>>> "%'";
>>>
>>> with this:
>>>
>>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>>> liststLikeFieldValue[0] + "||'%'";
>>>
>>> Does not work.
>>>
>>> On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:

 On 1/25/17, Warren Young  wrote:
>
> stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;
>
> Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
> the
> string.

 Not quite.  You cannot have parameters embedded in the middle of
 strings.  The whole string is replaced by a parameter.

 stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"

 Then the application has to prepend and append the "%" on the strings
 before binding.  Or, if your application does not want to do that:

 stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
 LIKE ('%' || ?2 || '%')"

 Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
 || is the string concatenation operator in SQL.)

 --
 D. Richard Hipp
 d...@sqlite.org
 ___
 sqlite-users mailing list
 sqlite-users@mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ---
> This email has been scanned for email related threats and delivered safely by 
> Mimecast.
> For more information please visit http://www.mimecast.com
> ---
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Andy Ling]

I think the point is, you need to use a prepared statement and bind the 
parameters to it.
The bind process handles the special characters.

So you will need to create a command string with question mark operators in like

stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
LIKE ('%' || ?2 || '%')"

Then use the sqlite3_bind_* () calls to replace the ?n markers with the " 
liststLikeFieldValue" strings.

HTH

Andy

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clyde Eisenbeis
Sent: Thu 26 January 2017 15:04
To: SQLite mailing list
Subject: Re: [sqlite] Using SQLite, how can I search for chars that include a 
', similar to OLE DB .Parameters?

I've also tried:

  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE ('%' || " +
liststLikeFieldValue[0] + " || '%')";

which does not work.

On Thu, Jan 26, 2017 at 8:14 AM, heribert  wrote:
> There are some missing spaces i think:
>
> string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
> liststLikeFieldValue[0] + "||'%'";
>
> Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:
>>
>> I tried replacing this:
>>
>>
>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
>> "%'";
>>
>> with this:
>>
>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>> liststLikeFieldValue[0] + "||'%'";
>>
>> Does not work.
>>
>> On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:
>>>
>>> On 1/25/17, Warren Young  wrote:

 stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;

 Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
 the
 string.
>>>
>>> Not quite.  You cannot have parameters embedded in the middle of
>>> strings.  The whole string is replaced by a parameter.
>>>
>>> stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"
>>>
>>> Then the application has to prepend and append the "%" on the strings
>>> before binding.  Or, if your application does not want to do that:
>>>
>>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>>> LIKE ('%' || ?2 || '%')"
>>>
>>> Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
>>> || is the string concatenation operator in SQL.)
>>>
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

Re-reading the previous emails, it appears I also need to use sqlite3_bind_*().

On Thu, Jan 26, 2017 at 9:04 AM, Clyde Eisenbeis  wrote:
> I've also tried:
>
>   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE ('%' || " +
> liststLikeFieldValue[0] + " || '%')";
>
> which does not work.
>
> On Thu, Jan 26, 2017 at 8:14 AM, heribert  wrote:
>> There are some missing spaces i think:
>>
>> string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>> liststLikeFieldValue[0] + "||'%'";
>>
>> Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:
>>>
>>> I tried replacing this:
>>>
>>>
>>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>>> + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
>>> "%'";
>>>
>>> with this:
>>>
>>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>>> liststLikeFieldValue[0] + "||'%'";
>>>
>>> Does not work.
>>>
>>> On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:

 On 1/25/17, Warren Young  wrote:
>
> stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;
>
> Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
> the
> string.

 Not quite.  You cannot have parameters embedded in the middle of
 strings.  The whole string is replaced by a parameter.

 stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"

 Then the application has to prepend and append the "%" on the strings
 before binding.  Or, if your application does not want to do that:

 stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
 LIKE ('%' || ?2 || '%')"

 Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
 || is the string concatenation operator in SQL.)

 --
 D. Richard Hipp
 d...@sqlite.org
 ___
 sqlite-users mailing list
 sqlite-users@mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[R Smith]

On 2017/01/26 5:04 PM, Clyde Eisenbeis wrote:

I've also tried:

   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE ('%' || " +
liststLikeFieldValue[0] + " || '%')";

which does not work.


Could you kindly simply output the resulting string to the console or 
whatever output you use so we can see the final query string that gets 
passed to SQLite.


There's a whole list of things that could go wrong. Guessing will get us 
nowhere.





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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

I've also tried:

  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE ('%' || " +
liststLikeFieldValue[0] + " || '%')";

which does not work.

On Thu, Jan 26, 2017 at 8:14 AM, heribert  wrote:
> There are some missing spaces i think:
>
> string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
> liststLikeFieldValue[0] + "||'%'";
>
> Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:
>>
>> I tried replacing this:
>>
>>
>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
>> "%'";
>>
>> with this:
>>
>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>> liststLikeFieldValue[0] + "||'%'";
>>
>> Does not work.
>>
>> On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:
>>>
>>> On 1/25/17, Warren Young  wrote:

 stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;

 Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
 the
 string.
>>>
>>> Not quite.  You cannot have parameters embedded in the middle of
>>> strings.  The whole string is replaced by a parameter.
>>>
>>> stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"
>>>
>>> Then the application has to prepend and append the "%" on the strings
>>> before binding.  Or, if your application does not want to do that:
>>>
>>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>>> LIKE ('%' || ?2 || '%')"
>>>
>>> Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
>>> || is the string concatenation operator in SQL.)
>>>
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[heribert]

There are some missing spaces i think:

string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE '%'||" +
liststLikeFieldValue[0] + "||'%'";

Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:

I tried replacing this:

   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
"%'";

with this:

   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE '%'||" +
liststLikeFieldValue[0] + "||'%'";

Does not work.

On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:

On 1/25/17, Warren Young  wrote:

stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;

Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into the
string.

Not quite.  You cannot have parameters embedded in the middle of
strings.  The whole string is replaced by a parameter.

stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"

Then the application has to prepend and append the "%" on the strings
before binding.  Or, if your application does not want to do that:

stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
LIKE ('%' || ?2 || '%')"

Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
|| is the string concatenation operator in SQL.)

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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


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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[heribert]

There are some missing spaces i think:

string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE '%'||" +
liststLikeFieldValue[0] + "||'%'";


Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:

I tried replacing this:

   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
"%'";

with this:

   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE '%'||" +
liststLikeFieldValue[0] + "||'%'";

Does not work.

On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:

On 1/25/17, Warren Young  wrote:

stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;

Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into the
string.

Not quite.  You cannot have parameters embedded in the middle of
strings.  The whole string is replaced by a parameter.

stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"

Then the application has to prepend and append the "%" on the strings
before binding.  Or, if your application does not want to do that:

stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
LIKE ('%' || ?2 || '%')"

Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
|| is the string concatenation operator in SQL.)

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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


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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

I tried replacing this:

  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
"%'";

with this:

  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE '%'||" +
liststLikeFieldValue[0] + "||'%'";

Does not work.

On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:
> On 1/25/17, Warren Young  wrote:
>>
>> stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;
>>
>> Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into the
>> string.
>
> Not quite.  You cannot have parameters embedded in the middle of
> strings.  The whole string is replaced by a parameter.
>
>stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"
>
> Then the application has to prepend and append the "%" on the strings
> before binding.  Or, if your application does not want to do that:
>
>stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
> LIKE ('%' || ?2 || '%')"
>
> Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
> || is the string concatenation operator in SQL.)
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when creating a calculated index

[Simon Slavin]

On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:

> Bug is as follows:

Anyone ?  Did I miss something and you’re all too polite to point it out ?

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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Richard Damon]

On 1/26/17 8:05 AM, Hick Gunter wrote:

The OP proposes intializing the structure member with "0" instead of "(void*)db", 
which I read the other way around and reminds me of certain implicit equivalences 0 <==> (void*)0, 
on eof which was recently discussed here.

Perhaps the OP's Compiler does not allow initialization of a dynamic structure 
with a parameter value.

If the structure is a static/global, then the initializers need to be 
compile time constants for C. If this is a function local object (which 
it looks like), that initialization with a variable is perfectly legal 
(but perhaps VC is warning that the presence of the variable is going to 
make that initilization less efficient, as it can't just make a static 
copy of the initialization value and memcpy that to the variable).



--
Richard Damon

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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[dandl]

>>>From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>>>Behalf Of Ziemowit Laski
Sent: Thursday, 26 January 2017 7:36 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit
>>>
>>>Visual C++ correctly catches this.  The fragment
>>>
>>>  struct IcuScalar {
>>>const char *zName;/* Function name */
>>>int nArg; /* Number of arguments */
>>>int enc;  /* Optimal text encoding */
>>>void *pContext;   /* sqlite3_user_data() context 
>>> */
>>>void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>>>  } scalars[] = {
>>>{"regexp", 2, SQLITE_ANY|SQLITE_DETERMINISTIC,  0, 
>>> icuRegexpFunc},
>>>
>>>{"lower",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, 
>>> icuCaseFunc16},
>>>{"lower",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, 
>>> icuCaseFunc16},
>>>{"upper",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, 
>>> icuCaseFunc16},
>>>{"upper",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, 
>>> icuCaseFunc16},
>>>
>>>{"lower",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, 
>>> icuCaseFunc16},
>>>{"lower",  2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, 
>>> icuCaseFunc16},
>>>{"upper",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC,  (void*)1, 
>>> icuCaseFunc16},
>>>{"upper",  2, SQLITE_UTF8|SQLITE_DETERMINISTIC,  (void*)1, 
>>> icuCaseFunc16},
>>>
>>>{"like",   2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc},
>>>{"like",   3, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc},
>>>
>>>{"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>>>  };
>>>
>>>  int rc = SQLITE_OK;
>>>  int i;
>>>
>>>should read
>>>
>>>   struct IcuScalar {
>>>  const char *zName;/* Function name */
>>>  int nArg; /* Number of 
>>> arguments */
>>>  int enc;  /* Optimal text 
>>> encoding */
>>>  void *pContext;   /* 
>>> sqlite3_user_data() context */
>>>  void(*xFunc)(sqlite3_context*, int, sqlite3_value**);
>>>   } scalars[] = {
>>>  { "regexp", 2, SQLITE_ANY | SQLITE_DETERMINISTIC,  0, 
>>> icuRegexpFunc },
>>>
>>>  { "lower",  1, SQLITE_UTF16 | SQLITE_DETERMINISTIC,0, 
>>> icuCaseFunc16 },
>>>  { "lower",  2, SQLITE_UTF16 | SQLITE_DETERMINISTIC,0, 
>>> icuCaseFunc16 },
>>>  { "upper",  1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, (void*)1, 
>>> icuCaseFunc16 },
>>>  { "upper",  2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, (void*)1, 
>>> icuCaseFunc16 },
>>>
>>>  { "lower",  1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
>>> icuCaseFunc16 },
>>>  { "lower",  2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
>>> icuCaseFunc16 },
>>>  { "upper",  1, SQLITE_UTF8 | SQLITE_DETERMINISTIC,  (void*)1, 
>>> icuCaseFunc16 },
>>>  { "upper",  2, SQLITE_UTF8 | SQLITE_DETERMINISTIC,  (void*)1, 
>>> icuCaseFunc16 },
>>>
>>>  { "like",   2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
>>> icuLikeFunc },
>>>  { "like",   3, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
>>> icuLikeFunc },
>>>
>>>  { "icu_load_collation",  2, SQLITE_UTF8, 0, icuLoadCollation }
>>>   };
>>>
>>>   int rc = SQLITE_OK;
>>>   int i;
>>>
>>>   scalars[11].pContext = (void*)db;
>>>

Why would you say that? What error message are you getting on what compiler, 
and why would that change produce any different result? What type is 'db'?

Can you quote some specified reference to the C standard document in support of 
your contention?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Hick Gunter]

The OP proposes intializing the structure member with "0" instead of 
"(void*)db", which I read the other way around and reminds me of certain 
implicit equivalences 0 <==> (void*)0, on eof which was recently discussed here.

Perhaps the OP's Compiler does not allow initialization of a dynamic structure 
with a parameter value.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Clemens Ladisch
Gesendet: Donnerstag, 26. Jänner 2017 13:07
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

Hick Gunter wrote:
> On ILP_32 architectures, the integer 0

What integer 0?  The message is about initializing scalars[11].pContent (a 
"void*") with "(void*)db", which is "sqlite3*".

> Oh?  What exactly is illegal about this?
>
>>   struct IcuScalar {
>> const char *zName;/* Function name */
>> int nArg; /* Number of arguments */
>> int enc;  /* Optimal text encoding */
>> void *pContext;   /* sqlite3_user_data() context 
>> */
>> void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>>   } scalars[] = {
>> ...
>> {"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>>   };
>>
>> should read
>>
>>} scalars[] = {
>>   ...

Look right below here...

>>   { "icu_load_collation",  2, SQLITE_UTF8, 0, icuLoadCollation }
>>};
>>

Look just above here...

>>scalars[11].pContext = (void*)db;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Clemens Ladisch]

Hick Gunter wrote:
> On ILP_32 architectures, the integer 0

What integer 0?  The message is about initializing scalars[11].pContent
(a "void*") with "(void*)db", which is "sqlite3*".

> Oh?  What exactly is illegal about this?
>
>>   struct IcuScalar {
>> const char *zName;/* Function name */
>> int nArg; /* Number of arguments */
>> int enc;  /* Optimal text encoding */
>> void *pContext;   /* sqlite3_user_data() context 
>> */
>> void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>>   } scalars[] = {
>> ...
>> {"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>>   };
>>
>> should read
>>
>>} scalars[] = {
>>   ...
>>   { "icu_load_collation",  2, SQLITE_UTF8, 0, icuLoadCollation }
>>};
>>
>>scalars[11].pContext = (void*)db;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Hick Gunter]

On ILP_32 architectures, the integer 0 is not discernible from the (void *)0 
(aka NULL) and so most compilers don't bother to issue a warning. This comes 
from an age where programmers were expected to know how computers work on an 
assembly language level and would "know what they are doing" when passing an 
integer of the same size to a pointer argument.

On LP_64 architactures, the integer 0 is 32 bits while (void *)0 is 64 bits, 
which makes more than a bit of a difference. A 64 bit integer 0 would be 
denoted by 0L. Additionally, compilers have become more pedantic (because 
compiler builders have become aware of the fact that not "knowing what you are 
doing" in respect to pointers and integers is a common source of errors) and 
will complain about "making a pointer from an integer of a different size" 
and/or "making a pointer from an integer without a cast".

There are many casese of 0 vs. NULL in sqlite code; they do not, however, 
influence the operation of the code, just compileability in "nit picking" mode.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Clemens Ladisch
Gesendet: Donnerstag, 26. Jänner 2017 08:47
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

Ziemowit Laski wrote:
> Visual C++

Which one?

> correctly catches this.

Oh?  What exactly is illegal about this?

>   struct IcuScalar {
> const char *zName;/* Function name */
> int nArg; /* Number of arguments */
> int enc;  /* Optimal text encoding */
> void *pContext;   /* sqlite3_user_data() context 
> */
> void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>   } scalars[] = {
> ...
> {"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>   };
>
> should read
>
>} scalars[] = {
>   ...
>   { "icu_load_collation",  2, SQLITE_UTF8, 0, icuLoadCollation }
>};
>
>scalars[11].pContext = (void*)db;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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