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

[GB]

Clyde Eisenbeis schrieb am 27.01.2017 um 18:51:

I tried this:

-
  System.Data.SQLite.SQLiteCommand sqliteCmd = 
sqliteConnection.CreateCommand());

   sqliteCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
stTableName + " WHERE " + stLikeFieldName + " LIKE '%' || ?1 || '%'";

   sqliteCmd.Parameters.Add(new SQLiteParameter("string",
liststLikeFieldValue[0]));

   SQLiteDataReader sqlReader = sqliteCmd.ExecuteReader();
-

Error msg: "unknown error ... Insufficient parameters supplied to the command"



Make sure the parameter name matches what is provided in the statement. 
So your


  sqliteCmd.Parameters.Add(new SQLiteParameter("string",
liststLikeFieldValue[0]));

shoud read:

  sqliteCmd.Parameters.Add(new SQLiteParameter("1",
liststLikeFieldValue[0]));

However, using parameters with a qualfied name is recommended:

sqliteCmd.CommandText = "SELECT" + stFieldNames + "FROM " + stTableName + " WHERE " + 
stLikeFieldName + " LIKE '%' || @likeFldName || '%'";
sqliteCmd.Parameters.Add(new SQLiteParameter("@likeFldName", 
liststLikeFieldValue[0]));

SQLite is quite generous about possible parameter naming: 
http://www.sqlite.org/lang_expr.html#varparam

___
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 agree completely!  I am grateful that SQLite exists.

It would be good if users could post solutions, similar to forums.
This relieves the burden on the benefactors ... and would, quite
likely, attract more users.

On Fri, Jan 27, 2017 at 2:38 PM, Warren Young  wrote:
> On Jan 27, 2017, at 1:09 PM, Clyde Eisenbeis  wrote:
>>
>> The SQLite websites would be more useful with examples.
>
> Certainly.  No question.
>
> The thing is, I think most of us are primarily grateful that SQLite *exists* 
> and that it works as well as it does.  Given a choice of where our 
> benefactors choose to put their efforts, I think most people on this list 
> would prefer that it be put into SQLite itself, rather than the docs, which 
> are already uncommonly good.
>
> One of the great virtues of open source is that when some behavior is not 
> explicitly documented, you can dig in and find out why it behaves the way it 
> does.  Closed-source products often have to have more extensive documentation 
> because that’s the only way you have to understand it, short of breaking out 
> the disassembler.
> ___
> 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?

[Warren Young]

On Jan 27, 2017, at 1:09 PM, Clyde Eisenbeis  wrote:
> 
> The SQLite websites would be more useful with examples.

Certainly.  No question.

The thing is, I think most of us are primarily grateful that SQLite *exists* 
and that it works as well as it does.  Given a choice of where our benefactors 
choose to put their efforts, I think most people on this list would prefer that 
it be put into SQLite itself, rather than the docs, which are already 
uncommonly good.

One of the great virtues of open source is that when some behavior is not 
explicitly documented, you can dig in and find out why it behaves the way it 
does.  Closed-source products often have to have more extensive documentation 
because that’s the only way you have to understand it, short of breaking out 
the disassembler.
___
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]

Hi Simon Slavin,

This works!  Almost identical to the approach used for OLE DB example
described in the 26 Jan, 11:45am email attachment.

The end result code for SQLite is attached.

-
Hi James K. Lowden,

Examples are a source I've used since 1968 (Fortran using key punch cards).

Examples have worked very well for me for many years ... Assembler
thru WPF ... along with considerable database code (CTREE, SQL, etc.)
... and microprocessor / microcontroller code ... worked at Texas
Instruments, 3M, Emerson.

Circa 1982, Microsoft provided good books for MS-DOS Assembler and C
... with many examples.  I learned how to program PC's in a few weeks,
including things such as TSR's and memory structure.

Microsoft's Windows books were terrible.  It was like looking through
"every" book in an encyclopedia trying to find a word.

The SQLite websites would be more useful with examples.

One example is worth more than a thousand words.

The Microsoft forums and StackOverflow forums provide many examples
with few words.

I switched to the SQLite Mailing List as I found limited sqlite
knowledge on those forums.

-
Hi SQLite Mailing List,

I am grateful for the assistance I've received via SQLite Mailing List
emails.  This has helped me write the code I need.  Thanks!

Clyde

On Fri, Jan 27, 2017 at 1:08 PM, James K. Lowden
 wrote:
> On Thu, 26 Jan 2017 11:45:41 -0600
> Clyde Eisenbeis  wrote:
>
>> Usually I look at examples, and extract the relevant info.
>
> In the Age of Google, that has become a popular technique.  But it's
> not a good way to learn anything.  I wonder, though: how do you know
> what the "relevant info" is?  How do you even know it's there?
>
> Think about it.  Did you learn geometry by extrapolating from examples
> of triangles, or by starting with 5 axioms and developing theorems?  In
> fact, have you ever learned *anything* by extrapolating from examples?
>
> The definitive explanation for use of the SQLite binding functions is
> the documentation.  It might be harder to read the first time than
> putzing with an example, but (as has been suggested) the process of
> working from the documentation forces you to understand what you're
> doing.  Working from knowledge is much more productive -- and
> satisfying -- than guessing.
>
>> I'd appreciate some example code which uses "sqlite3_bind_".  Thanks!
>
> More than once in this thread it's been suggested that you read the
> documentation.  That's where the answers lie.  Many people have shown a
> willingness to explain how the SQLite functions work.  Why should they
> take time to scout out examples for you, just help you get your job
> done?
>
> --jkl
> ___
> 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?

[James K. Lowden]

On Thu, 26 Jan 2017 11:45:41 -0600
Clyde Eisenbeis  wrote:

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

In the Age of Google, that has become a popular technique.  But it's
not a good way to learn anything.  I wonder, though: how do you know
what the "relevant info" is?  How do you even know it's there?  

Think about it.  Did you learn geometry by extrapolating from examples
of triangles, or by starting with 5 axioms and developing theorems?  In
fact, have you ever learned *anything* by extrapolating from examples? 

The definitive explanation for use of the SQLite binding functions is
the documentation.  It might be harder to read the first time than
putzing with an example, but (as has been suggested) the process of
working from the documentation forces you to understand what you're
doing.  Working from knowledge is much more productive -- and
satisfying -- than guessing.  

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

More than once in this thread it's been suggested that you read the
documentation.  That's where the answers lie.  Many people have shown a
willingness to explain how the SQLite functions work.  Why should they
take time to scout out examples for you, just help you get your job
done?  

--jkl
___
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 27 Jan 2017, at 5:59pm, Clyde Eisenbeis  wrote:

> sqliteCmd.CommandText = SELECT fstPriority, fstInfo, fstDateCreated,
> fstDateModified, fiKeyID FROM  MemoPadTable  WHERE fstInfo LIKE '%' ||
> ?1 || '%'"
> 
> liststLikeFieldValue[0] = "bruce"

Please try the following:
Change ?1 to @param1.
Change your new SQLiteParameter.

-

System.Data.SQLite.SQLiteCommand sqliteCmd = sqliteConnection.CreateCommand());

sqliteCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
stTableName + " WHERE " + stLikeFieldName + " LIKE '%' || @param1 || '%'";

sqliteCmd.Parameters.Add(new SQLiteParameter("@param1",
liststLikeFieldValue[0]));

SQLiteDataReader sqlReader = sqliteCmd.ExecuteReader();

-

Simon.
___
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]

sqliteCmd.CommandText = SELECT fstPriority, fstInfo, fstDateCreated,
fstDateModified, fiKeyID FROM  MemoPadTable  WHERE fstInfo LIKE '%' ||
?1 || '%'"

liststLikeFieldValue[0] = "bruce"

On Fri, Jan 27, 2017 at 11:55 AM, Simon Slavin  wrote:
>
> On 27 Jan 2017, at 5:51pm, Clyde Eisenbeis  wrote:
>
>> System.Data.SQLite.SQLiteCommand sqliteCmd = 
>> sqliteConnection.CreateCommand());
>>
>>  sqliteCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
>> stTableName + " WHERE " + stLikeFieldName + " LIKE '%' || ?1 || '%'";
>>
>>  sqliteCmd.Parameters.Add(new SQLiteParameter("string",
>> liststLikeFieldValue[0]));
>>
>>  SQLiteDataReader sqlReader = sqliteCmd.ExecuteReader();
>> -
>>
>> Error msg: "unknown error ... Insufficient parameters supplied to the 
>> command"
>
> Please tell us what string is in your variable sqliteCmd.CommandText when you 
> execute the sqliteCmd.ExecuteReader method.
>
> Simon.
> ___
> 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?

[Simon Slavin]

On 27 Jan 2017, at 5:51pm, Clyde Eisenbeis  wrote:

> System.Data.SQLite.SQLiteCommand sqliteCmd = 
> sqliteConnection.CreateCommand());
> 
>  sqliteCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
> stTableName + " WHERE " + stLikeFieldName + " LIKE '%' || ?1 || '%'";
> 
>  sqliteCmd.Parameters.Add(new SQLiteParameter("string",
> liststLikeFieldValue[0]));
> 
>  SQLiteDataReader sqlReader = sqliteCmd.ExecuteReader();
> -
> 
> Error msg: "unknown error ... Insufficient parameters supplied to the command"

Please tell us what string is in your variable sqliteCmd.CommandText when you 
execute the sqliteCmd.ExecuteReader method.

Simon.
___
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]

Thanks.  Was able to open the .chm file.

I tried this:

-
 System.Data.SQLite.SQLiteCommand sqliteCmd = sqliteConnection.CreateCommand());

  sqliteCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
stTableName + " WHERE " + stLikeFieldName + " LIKE '%' || ?1 || '%'";

  sqliteCmd.Parameters.Add(new SQLiteParameter("string",
liststLikeFieldValue[0]));

  SQLiteDataReader sqlReader = sqliteCmd.ExecuteReader();
-

Error msg: "unknown error ... Insufficient parameters supplied to the command"


On Fri, Jan 27, 2017 at 1:10 AM, GB  wrote:
> 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
___
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] 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] 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] 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] 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] 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

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] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Richard Hipp]

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

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

[Warren Young]

On Jan 25, 2017, at 8:50 AM, Clyde Eisenbeis  wrote:
> 
> Are there code examples similar to the following (OLE DB)?

Code examples?  Maybe, but the second link I gave you is pretty clear.  The 
bits you want are even in bold text.

> stCmdString += " AND " + stLikeFieldName + " LIKE '%" +
> liststLikeFieldValue[iii] + "%’";

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

Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into the 
string.
___
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 should clarify ... I am currently using:

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

for (int iii = 1; iii < liststLikeFieldValue.Count; iii++)
  stCmdString += " AND " + stLikeFieldName + " LIKE '%" +
liststLikeFieldValue[iii] + "%'";

which does not handle the cases where a ' is included in the field value.


On Wed, Jan 25, 2017 at 9:50 AM, Clyde Eisenbeis  wrote:
> Thanks for the prompt response!
>
> Are there code examples similar to the following (OLE DB)?
>
>oledbCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
> stTableName + " WHERE " + stLikeFieldName + " LIKE @p0";
>
> for (int iii = 1; iii < liststLikeFieldValue.Count; iii++)
>   oledbCmd.CommandText += stLikeFieldName + " AND " +
> stLikeFieldName + " LIKE @p" + (iii).ToString();
>
> On Wed, Jan 25, 2017 at 9:45 AM, Warren Young  wrote:
>> On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis  wrote:
>>>
>>> The use of .Parameters in OLE DB fixes this problem.  Is there an
>>> equivalent for SQLite?
>>
>> You’re looking for prepared statements with parameters:
>>
>>https://sqlite.org/c3ref/stmt.html
>>https://sqlite.org/lang_expr.html#varparam
>>
>> ___
>> 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]

Thanks for the prompt response!

Are there code examples similar to the following (OLE DB)?

   oledbCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
stTableName + " WHERE " + stLikeFieldName + " LIKE @p0";

for (int iii = 1; iii < liststLikeFieldValue.Count; iii++)
  oledbCmd.CommandText += stLikeFieldName + " AND " +
stLikeFieldName + " LIKE @p" + (iii).ToString();

On Wed, Jan 25, 2017 at 9:45 AM, Warren Young  wrote:
> On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis  wrote:
>>
>> The use of .Parameters in OLE DB fixes this problem.  Is there an
>> equivalent for SQLite?
>
> You’re looking for prepared statements with parameters:
>
>https://sqlite.org/c3ref/stmt.html
>https://sqlite.org/lang_expr.html#varparam
>
> ___
> 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?

[Warren Young]

On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis  wrote:
> 
> The use of .Parameters in OLE DB fixes this problem.  Is there an
> equivalent for SQLite?

You’re looking for prepared statements with parameters:

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

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

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

[Clyde Eisenbeis]

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

This works to find bruce and baseball:

string stCmdString = "SELECT fstInfo FROM PadTable WHERE fstInfo
LIKE '%bruce%' AND fstInfo LIKE '%baseball%'"

Not sure how to find bruce's and baseball.

I know it can be done by using two single quote marks ... but this is
a bit of a hassle to read every char of all new entries (or searches)
to the database before storing the.

The use of .Parameters in OLE DB fixes this problem.  Is there an
equivalent for SQLite?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users