Re: [sqlite] SQLite.Interop.dll

[Clyde Eisenbeis]

Lee, I have an .sln file that is in a folder (there is no .csproj or
.vbproj file).  Inside that folder are additional folders ->
eventually drills down to bin\Debug.

"Right-click your solution" ... do you mean inside VS (Visual Studio
2013)?  When I right-click inside VS, my only choice is "new solution
explorer view".

On Fri, Feb 24, 2017 at 12:11 PM, Lee Gray <lee.g...@morrisdickson.com> wrote:
> Yeah, the interop.dll won't let you add it as a reference.
>
> What I meant below is that you have to create the actual folder structure 
> (\Library\x64 etc), but to add that folder to a solution 
> (the .sln file as opposed to a project, the .csproj or .vbproj file), you 
> have to duplicate the structure in the solution, since solutions do not 
> contain physical disk folders, just "containers". Right-click on your 
> solution, then click Add, then New Solution Folder. Name it the same as the 
> physical folder ("Library"). Then create the two solution folders for x64 and 
> x86 under that. Finally, right-click the x64 folder, then Add, then Existing 
> Item. Browse to the file Library\x64\SQLite.Interop.dll. Repeat for x86. 
> Technically, you don't have to reproduce the folder structure in the 
> solution. You can add the files at the top level, and Visual Studio just 
> automatically lumps them into a "Solution Items" solution folder. I like to 
> reproduce what's actually on disk.
>
> At the project level, create the x64 and x86 project folders (which will also 
> create real folders on your disk, unlike at the solution level). Then right 
> click the folder, then Add, then Existing Item. Browse to the dll. Don't 
> click the Add button, though. Click the drop-down arrow, then Add As Link. 
> That creates a project-level link to the physical file, without copying it to 
> the project level folder. Right-click each file link, then click Properties. 
> Set the Build Action to Content, and set Copy to Output Directory to Copy if 
> newer.
>
> To answer your last question (should you reference from WPF or from Custom 
> dll), that depends on what your code accesses. In my case, I have a Windows 
> Forms app which does *not* directly access System.Data.SQLite, but it does 
> reference a class library project which references SQLite. In the class 
> library project, I do *not* have the project level x64/x86 folders with the 
> interop files added as links, but I *do* have a reference to 
> System.Data.SQLite.dll. The Windows Forms app has the project level x64/x86 
> folders with the interop files added as links so that they get added to the 
> *.exe's* bin folder at build time.
>
> If your library dll is intended to be shared among other applications, you'll 
> still need to provide a mechanism to get the interop files into the compiled 
> .exe's folder by reproducing those project folders with linked files as 
> above, or as previously mentioned by another poster, in other ways such as 
> Build Events or editing the project file's XML, etc. I did it my way so that 
> all of the required files could be seen at a glance in the solution/project 
> structure.
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Clyde Eisenbeis
> Sent: Friday, February 24, 2017 11:03 AM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] SQLite.Interop.dll
>
> Barry, I have tried adding SQLite.Interop.dll as a Reference -> error message 
>  "SQLite.Interop.dll could not be added "
>
> Lee, What is the procedure to reference a "Solution-level Library folder"?  
> Also, would it be referenced by my genealogy WPF or my Custom Control Library 
> dll?
>
> On Fri, Feb 24, 2017 at 9:27 AM, Lee Gray <lee.g...@morrisdickson.com> wrote:
>> I never could get the NuGet package to cooperate with the interop dlls, so 
>> here's what I've done. I create a Solution-level Library folder (and 
>> matching physical folder) with this layout:
>>
>> 
>> \Library
>> System.Data.SQLite.dll
>> \x64
>> SQLite.Interop.dll
>> \x86
>> SQLite.Interop.dll
>> 
>> ...
>> 
>>
>> I then reference System.Data.SQLite.dll in each project as needed, directly 
>> from Library. In any project that references it, I add project-level x64 and 
>> x86 folders. I then add the each interop file, but as a *Link* to the 
>> physical file under Library. I set those interop files' Build Action to 
>> Content, and Copy if newer. That copies the interop files, under their x64 
>> and x86 directories, to the 

Re: [sqlite] SQLite.Interop.dll

[Clyde Eisenbeis]

Barry, I have tried adding SQLite.Interop.dll as a Reference -> error
message  "SQLite.Interop.dll could not be added "

Lee, What is the procedure to reference a "Solution-level Library
folder"?  Also, would it be referenced by my genealogy WPF or my
Custom Control Library dll?

On Fri, Feb 24, 2017 at 9:27 AM, Lee Gray <lee.g...@morrisdickson.com> wrote:
> I never could get the NuGet package to cooperate with the interop dlls, so 
> here's what I've done. I create a Solution-level Library folder (and matching 
> physical folder) with this layout:
>
> 
> \Library
> System.Data.SQLite.dll
> \x64
> SQLite.Interop.dll
> \x86
> SQLite.Interop.dll
> 
> ...
> 
>
> I then reference System.Data.SQLite.dll in each project as needed, directly 
> from Library. In any project that references it, I add project-level x64 and 
> x86 folders. I then add the each interop file, but as a *Link* to the 
> physical file under Library. I set those interop files' Build Action to 
> Content, and Copy if newer. That copies the interop files, under their x64 
> and x86 directories, to the correct project output directories.
>
> The initial setup is a minor pain, but after that, it just works.
>
> To get the necessary files any time there is an upgrade, I create a temp 
> project and install the NuGet System.Data.SQLite.Core package, then just copy 
> the appropriate 3 files to that structure above.
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Barry Smith
> Sent: Thursday, February 23, 2017 12:53 PM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] SQLite.Interop.dll
>
> Oh, I do remember having this issue before.
>
> I think the cause is this: Visual studio attempts to trace which dlls are 
> required for each project. Then if project A is dependent on project B, 
> visual studio will copy what it thinks is all the required dlls for both 
> projects into project A's output directory.
>
> Unfortunately visual studio is only so smart and doesn't realise that the 
> SQLite.interop.dll is a required dependency. I believe the NuGet package puts 
> instructions to move those dlls to the output directory by means of xcopy. 
> So, you have this situation:
>
> Project GlobalSQLite, with reference to SQLite.
> Project GeneologyControls, with reference to GlobalSQLite.
>
> Tell visual studio to build and it goes and executes the step where the 
> SQLite interop DLL is copied to the output of the GlobalSQLite project. But 
> then it builds GeneologyControls and doesn't realise it needs to copy the 
> interop DLLs! So finally your program executes in the output directory of the 
> GeneologyControls project, but the SQLite.interop.dll files are in the output 
> of the other project.
>
> You need to ensure that the SQLite interop dlls are in the final output 
> directory (and also included when you distribute your application). You can 
> do this manually using windows explorer, or you can put in a pre or post 
> build event of your final project to copy the x64 and x86 folders (containing 
> the SQLite.interop.dll files) into its output directory. Doing it from 
> Windows explorer is easier, but you may forget then if you do a clean build a 
> few years down the line you'll run into the problem again.
>
> Perhaps there's a cleaner way to do this. Those were my solutions, though...
>
>> On 23 Feb 2017, at 1:58 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>>
>> ---
>> About two years ago, I downloaded and installed SQLite.  I don't
>> recall the details, but it was a program that installed SQLite.
>>
>> I ended up with files such as EntityFramework.dll,
>> EntityFramework.SqlServer.dll, System.Data.SQLite.dll, etc.  This
>> required "using System.Data.SQLite".
>>
>> ---
>> I then created a WPF C# genealogy program ... and a GlobalSQLite.dll
>> library (as a WPF Custom Control Library).
>>
>> The GlobalSQLite.dll library contains commonly used functions.  For example:
>>
>>   boCreateFileAndTables(string stPathFilename,
>> List liststTableNames,
>> List liststFieldDefinitions)
>>   {...}
>>
>> ---
>> I am working to improve / clean up the original code for WPF C#
>> genealogy program.
>>
>> When I have SQLite installed on my genealogy program and on my
>> GlobalSQLite.dll library, it works fine.
>>
>> When I don't have SQLite installed on m

Re: [sqlite] SQLite.Interop.dll

[Clyde Eisenbeis]

---
About two years ago, I downloaded and installed SQLite.  I don't
recall the details, but it was a program that installed SQLite.

I ended up with files such as EntityFramework.dll,
EntityFramework.SqlServer.dll, System.Data.SQLite.dll, etc.  This
required "using System.Data.SQLite".

---
I then created a WPF C# genealogy program ... and a GlobalSQLite.dll
library (as a WPF Custom Control Library).

The GlobalSQLite.dll library contains commonly used functions.  For example:

   boCreateFileAndTables(string stPathFilename,
 List liststTableNames,
 List liststFieldDefinitions)
   {...}

---
I am working to improve / clean up the original code for WPF C#
genealogy program.

When I have SQLite installed on my genealogy program and on my
GlobalSQLite.dll library, it works fine.

When I don't have SQLite installed on my genealogy program, I get an
exception "Unable to load DLL 'SQLite.Interop.dll'".  It occurs in my
GlobalSQLite.dll library program:

sqliteConn = new System.Data.SQLite.SQLiteConnection("Data
Source=" + stPathFilename + ";").

I do find SQLite.Interop.dll under ...
GlobalsSQLite\packages\System.Data.SQLite.Core.1.0.101.0\build\net46\x86
... and under ... \x64.

Is there a better place to put SQLite.Interop.dll so it works?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Options

[Clyde Eisenbeis]

in my SQLite.dll library program.

On Tue, Feb 21, 2017 at 2:42 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
> I don't recall how I obtained the SQLite files ... was two years ago.
>
> When I have SQLite installed on my genealogy program and on my SQLite
> .dll library, it works fine.
>
> When I don't have SQLite installed on my genealogy program, I get an
> exception "Unable to load DLL 'SQLite.Interop.dll': ... " with
>
>   sqliteConn = new System.Data.SQLite.SQLiteConnection("Data Source="
> + stPathFilename + ";").
>
> On Tue, Feb 21, 2017 at 12:26 PM, Barry <smith.bar...@gmail.com> wrote:
>> Are you using NuGet or downloading the binaries from System.Data.Sqlite.org?
>>
>> Ah. You'll have to excuse me, it's been a while since I actually started a
>> new project and set SQLite as a dependency. I'm not sure if things have
>> changed, or if my memory is terrible, but what I said may not have been
>> completely clear or correct.
>>
>> If you're using NuGet (the easiest way):
>>  Turns out there's a NuGet package called System.Data.Sqlite.Core* that
>> includes only the core SQLite wrapper. If you install System.Data.Sqlite
>> you get the LINQ and EF stuff (which I generally try to stay away from). If
>> you use this package you will get both the x64 and x86 interop binaries in
>> their respective folders. If you have already installed the
>> System.Data.Sqlite package, and got the LINQ and EF dependencies, you
>> should be able to remove them as a dependency by right clicking the the
>> unnecessary dependencies to open their context menu, and selecting "Remove".
>>
>> If you're downloading from the website System.Data.Sqlite.org:
>>  You only get an x86 or a x64 version of the interop binaries, depending on
>> what you download. The download page has some instructions on how you can
>> make the wrapper library automatically select the correct interop dll for
>> whichever architecture you're targetting. See the section entitled "Native
>> Library pre-loading". I haven't gone through this setup personally so
>> cannot help you any more than what is there on the site (The NuGet package
>> comes with this preconfigured). You can choose to download the mixed mode
>> assemblies which will not have the 'interop' DLLs. I haven't played around
>> with these because the download page specifically advises to avoid these
>> packages unless you need to put SQLite in the Global Assembly Cache (which
>> the authors also recommend against). Note that if you download from the
>> website, you'll get the LINQ and EF6 dlls included in the zip file. If you
>> never reference them from your project, they won't be copied to your output
>> directory and you can ignore them.
>>
>> Hope this helps. I am by no means an expert, I've only messed with it a few
>> times. I hope that if I have got things wrong that someone more experienced
>> can jump in and correct my mistakes.
>>
>> * This name might be a little confusing since Microsoft have recently
>> release an unrelated product called Entity Framework Core. It appears
>> unrelated to System.Data.Sqlite.Core.
>>
>> On 21 February 2017 at 13:48, Clyde Eisenbeis <cte...@gmail.com> wrote:
>>
>>> Thanks for the good info!
>>>
>>> I can't find SQLite.Interop.dll ... is referenced at
>>> https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki,
>>> but don't see a download.
>>>
>>> On Mon, Feb 20, 2017 at 12:30 PM, Barry Smith <smith.bar...@gmail.com>
>>> wrote:
>>> > I would use system.data.sqlite in that situation.
>>> >
>>> > But I would also say it depends on what you already have written, and
>>> what your strengths are. I am under the impression from your first email
>>> that you already have something written using system.data.sqlite. i.e.
>>> Using the class System.Data.SQLite.SQLiteConnection to create a
>>> connection to the db, then using the methods of that to manipulate the db
>>> or extract data from it. Have I assumed wrong?
>>> >
>>> > If I am wrong, and you have yet to start writing anything, I would still
>>> recommend using system.data.sqlite. Only if you particularly like LINQ over
>>> SQL and you are prepared to learn the caveats of the entity framework would
>>> I recommend that.
>>> >
>>> > Note that if you're using system.data.sqlite you will ultimately produce
>>> a few dlls that must be distributed together:
>>> >  - your custom library, 

Re: [sqlite] SQLite Options

[Clyde Eisenbeis]

I don't recall how I obtained the SQLite files ... was two years ago.

When I have SQLite installed on my genealogy program and on my SQLite
.dll library, it works fine.

When I don't have SQLite installed on my genealogy program, I get an
exception "Unable to load DLL 'SQLite.Interop.dll': ... " with

  sqliteConn = new System.Data.SQLite.SQLiteConnection("Data Source="
+ stPathFilename + ";").

On Tue, Feb 21, 2017 at 12:26 PM, Barry <smith.bar...@gmail.com> wrote:
> Are you using NuGet or downloading the binaries from System.Data.Sqlite.org?
>
> Ah. You'll have to excuse me, it's been a while since I actually started a
> new project and set SQLite as a dependency. I'm not sure if things have
> changed, or if my memory is terrible, but what I said may not have been
> completely clear or correct.
>
> If you're using NuGet (the easiest way):
>  Turns out there's a NuGet package called System.Data.Sqlite.Core* that
> includes only the core SQLite wrapper. If you install System.Data.Sqlite
> you get the LINQ and EF stuff (which I generally try to stay away from). If
> you use this package you will get both the x64 and x86 interop binaries in
> their respective folders. If you have already installed the
> System.Data.Sqlite package, and got the LINQ and EF dependencies, you
> should be able to remove them as a dependency by right clicking the the
> unnecessary dependencies to open their context menu, and selecting "Remove".
>
> If you're downloading from the website System.Data.Sqlite.org:
>  You only get an x86 or a x64 version of the interop binaries, depending on
> what you download. The download page has some instructions on how you can
> make the wrapper library automatically select the correct interop dll for
> whichever architecture you're targetting. See the section entitled "Native
> Library pre-loading". I haven't gone through this setup personally so
> cannot help you any more than what is there on the site (The NuGet package
> comes with this preconfigured). You can choose to download the mixed mode
> assemblies which will not have the 'interop' DLLs. I haven't played around
> with these because the download page specifically advises to avoid these
> packages unless you need to put SQLite in the Global Assembly Cache (which
> the authors also recommend against). Note that if you download from the
> website, you'll get the LINQ and EF6 dlls included in the zip file. If you
> never reference them from your project, they won't be copied to your output
> directory and you can ignore them.
>
> Hope this helps. I am by no means an expert, I've only messed with it a few
> times. I hope that if I have got things wrong that someone more experienced
> can jump in and correct my mistakes.
>
> * This name might be a little confusing since Microsoft have recently
> release an unrelated product called Entity Framework Core. It appears
> unrelated to System.Data.Sqlite.Core.
>
> On 21 February 2017 at 13:48, Clyde Eisenbeis <cte...@gmail.com> wrote:
>
>> Thanks for the good info!
>>
>> I can't find SQLite.Interop.dll ... is referenced at
>> https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki,
>> but don't see a download.
>>
>> On Mon, Feb 20, 2017 at 12:30 PM, Barry Smith <smith.bar...@gmail.com>
>> wrote:
>> > I would use system.data.sqlite in that situation.
>> >
>> > But I would also say it depends on what you already have written, and
>> what your strengths are. I am under the impression from your first email
>> that you already have something written using system.data.sqlite. i.e.
>> Using the class System.Data.SQLite.SQLiteConnection to create a
>> connection to the db, then using the methods of that to manipulate the db
>> or extract data from it. Have I assumed wrong?
>> >
>> > If I am wrong, and you have yet to start writing anything, I would still
>> recommend using system.data.sqlite. Only if you particularly like LINQ over
>> SQL and you are prepared to learn the caveats of the entity framework would
>> I recommend that.
>> >
>> > Note that if you're using system.data.sqlite you will ultimately produce
>> a few dlls that must be distributed together:
>> >  - your custom library, which contains the code you've written
>> >  - System.Data.Sqlite.dll, which contains the wrapper to make an
>> interface to access SQLite in a more dotNet friendly manner
>> >  - x64\sqlite.interop.dll
>> >  - x86\sqlite.interop.dll
>> > The last two contain the 'raw' SQLite library (for either 32 or 64 bit
>> systems).
>> >
>> > You should not need the other libraries for

Re: [sqlite] SQLite Options

[Clyde Eisenbeis]

Thanks for the good info!

I can't find SQLite.Interop.dll ... is referenced at
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki,
but don't see a download.

On Mon, Feb 20, 2017 at 12:30 PM, Barry Smith <smith.bar...@gmail.com> wrote:
> I would use system.data.sqlite in that situation.
>
> But I would also say it depends on what you already have written, and what 
> your strengths are. I am under the impression from your first email that you 
> already have something written using system.data.sqlite. i.e. Using the class 
> System.Data.SQLite.SQLiteConnection to create a connection to the db, then 
> using the methods of that to manipulate the db or extract data from it. Have 
> I assumed wrong?
>
> If I am wrong, and you have yet to start writing anything, I would still 
> recommend using system.data.sqlite. Only if you particularly like LINQ over 
> SQL and you are prepared to learn the caveats of the entity framework would I 
> recommend that.
>
> Note that if you're using system.data.sqlite you will ultimately produce a 
> few dlls that must be distributed together:
>  - your custom library, which contains the code you've written
>  - System.Data.Sqlite.dll, which contains the wrapper to make an interface to 
> access SQLite in a more dotNet friendly manner
>  - x64\sqlite.interop.dll
>  - x86\sqlite.interop.dll
> The last two contain the 'raw' SQLite library (for either 32 or 64 bit 
> systems).
>
> You should not need the other libraries for a simple application. If you find 
> that visual studio is placing them in your project's output directory, check 
> if they are listed as a reference and try to remove them then recompile.
>
>> On 20 Feb 2017, at 1:05 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>>
>> Thanks for the clarification.  In my case:
>>
>> 1) Speed is not an issue.  Size is not an issue.
>>
>> 2) This is a personal use database (genealogy).
>>
>> 3) Typically I create .dll's that serve as a library (WPF Custom
>> Control Library) ... easy to use for different programs.
>>
>> 4) For example, I have an Excel .dll library (uses Excel as a
>> database).  When the program runs the first time using this .dll
>> library, it creates the Excel file along with multiple sheets.
>>
>> 5) I'd like to create a similar .dll for an SQLite library.  The
>> program that uses this .dll is a simple WPF program that uses the .dll
>> class name to access the functions.
>>
>> With this info, which option would you recommend?
>>
>>> On Sun, Feb 19, 2017 at 9:45 PM, Barry Smith <smith.bar...@gmail.com> wrote:
>>> Strange, I replied to this earlier... Perhaps my messages are not getting 
>>> through.
>>>
>>> You cannot include a .c file for compilation in a c# project. You'd have to 
>>> do use a separate DLL and do some pinvoke stuff to get to the raw SQLite 
>>> interface, but in my opinion you're better off using the system.data.sqlite 
>>> wrapper. If you need the speed and power of the raw interface, you probably 
>>> need to drop out of an interpreted and managed language (c#) too...
>>>
>>> You don't need the entity framework (EF) to run system.data.sqlite. That is 
>>> an object relational mapper (ORM) that uses a lot of fancy reflection to 
>>> make data access a little easier* (until you get stung by it) and a lot 
>>> slower. EF is developed my Microsoft, although SQLite must provide some 
>>> input to make it work with its syntax. You should be able to remove the 
>>> entity framework dependencies from your project and still compile with no 
>>> issues. Try a complete rebuild / clean compile to try get rid of the 
>>> unnecessary dlls.
>>>
>>> *whether an ORM actually makes data access easier is debatable, they 
>>> basically allow you to write your data access queries in LINQ rather than 
>>> SQL, and automatically instansiate c# objects for each line in the results. 
>>> I find SQL easier...
>>>
>>>> On 19 Feb 2017, at 1:50 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>>>>
>>>> Sorry for the slow response.
>>>>
>>>> My code is in C#.  I don't know if the amalgamation source code in C
>>>> can be compiled so it is compatible with C#.
>>>>
>>>> If it can, I'd be interested in details.  Thanks!
>>>>
>>>>> On Sat, Feb 18, 2017 at 1:29 AM, R Smith <rsm...@rsweb.co.za> wrote:
>>>>>
>>>>>
>>>>>>> On 2017/02/18 12:45 AM, Warren Young wrote:
>>

Re: [sqlite] SQLite Options

[Clyde Eisenbeis]

Thanks for the clarification.  In my case:

1) Speed is not an issue.  Size is not an issue.

2) This is a personal use database (genealogy).

3) Typically I create .dll's that serve as a library (WPF Custom
Control Library) ... easy to use for different programs.

4) For example, I have an Excel .dll library (uses Excel as a
database).  When the program runs the first time using this .dll
library, it creates the Excel file along with multiple sheets.

5) I'd like to create a similar .dll for an SQLite library.  The
program that uses this .dll is a simple WPF program that uses the .dll
class name to access the functions.

With this info, which option would you recommend?

On Sun, Feb 19, 2017 at 9:45 PM, Barry Smith <smith.bar...@gmail.com> wrote:
> Strange, I replied to this earlier... Perhaps my messages are not getting 
> through.
>
> You cannot include a .c file for compilation in a c# project. You'd have to 
> do use a separate DLL and do some pinvoke stuff to get to the raw SQLite 
> interface, but in my opinion you're better off using the system.data.sqlite 
> wrapper. If you need the speed and power of the raw interface, you probably 
> need to drop out of an interpreted and managed language (c#) too...
>
> You don't need the entity framework (EF) to run system.data.sqlite. That is 
> an object relational mapper (ORM) that uses a lot of fancy reflection to make 
> data access a little easier* (until you get stung by it) and a lot slower. EF 
> is developed my Microsoft, although SQLite must provide some input to make it 
> work with its syntax. You should be able to remove the entity framework 
> dependencies from your project and still compile with no issues. Try a 
> complete rebuild / clean compile to try get rid of the unnecessary dlls.
>
> *whether an ORM actually makes data access easier is debatable, they 
> basically allow you to write your data access queries in LINQ rather than 
> SQL, and automatically instansiate c# objects for each line in the results. I 
> find SQL easier...
>
>> On 19 Feb 2017, at 1:50 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>>
>> Sorry for the slow response.
>>
>> My code is in C#.  I don't know if the amalgamation source code in C
>> can be compiled so it is compatible with C#.
>>
>> If it can, I'd be interested in details.  Thanks!
>>
>>> On Sat, Feb 18, 2017 at 1:29 AM, R Smith <rsm...@rsweb.co.za> wrote:
>>>
>>>
>>>> On 2017/02/18 12:45 AM, Warren Young wrote:
>>>>
>>>>> On Feb 17, 2017, at 7:32 AM, R Smith <rsm...@rsweb.co.za> wrote:
>>>>>
>>>>> You can even checkout the latest commits via SVN
>>>>
>>>> There’s a Subversion mirror of the official Fossil code repository for
>>>> SQLite?
>>>
>>>
>>> Apologies, force of habit nomenclature. Have fallen to calling any Software
>>> Versioning system just 'SVN' for short. I did of course mean for it to be
>>> checked out via Fossil.
>>>
>>>> https://goo.gl/KzLcV8
>>>>
>>>> (Excuse the shortener, it’s a raly long URL.)
>>>>
>>>> I could give you that Zip file link, but I suspect it’s purposely not
>>>> being published to avoid load on the SQLite repository server caused by 
>>>> bots
>>>> repeatedly requesting Zip files and tarballs.
>>>
>>>
>>> The bots can read goo links nowadays. ;)
>>>
>>>> Using Fossil is far more efficient than downloading Zip archives, but as I
>>>> keep getting reminded in my own Fossil-hosted public project, some people
>>>> just refuse to install and use anything they don’t absolutely have to.  
>>>> It’s
>>>> six easy steps, but apparently that’s too many for some.
>>>
>>>
>>> Agreed, and what is more sad is that Fossil is so much better at actual
>>> "Version-Control" (as opposed to making sharing code easiest). If we could
>>> get the rest of the World to rather Fossil, everybody wins. (I can already
>>> hear Linus clutching his chest and breathing erratically!)
>>>
>>>
>>>
>>> ___
>>> 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] SQLite Options

[Clyde Eisenbeis]

Sorry for the slow response.

My code is in C#.  I don't know if the amalgamation source code in C
can be compiled so it is compatible with C#.

If it can, I'd be interested in details.  Thanks!

On Sat, Feb 18, 2017 at 1:29 AM, R Smith  wrote:
>
>
> On 2017/02/18 12:45 AM, Warren Young wrote:
>>
>> On Feb 17, 2017, at 7:32 AM, R Smith  wrote:
>>>
>>> You can even checkout the latest commits via SVN
>>
>> There’s a Subversion mirror of the official Fossil code repository for
>> SQLite?
>
>
> Apologies, force of habit nomenclature. Have fallen to calling any Software
> Versioning system just 'SVN' for short. I did of course mean for it to be
> checked out via Fossil.
>
>>  https://goo.gl/KzLcV8
>>
>> (Excuse the shortener, it’s a raly long URL.)
>>
>> I could give you that Zip file link, but I suspect it’s purposely not
>> being published to avoid load on the SQLite repository server caused by bots
>> repeatedly requesting Zip files and tarballs.
>
>
> The bots can read goo links nowadays. ;)
>
>> Using Fossil is far more efficient than downloading Zip archives, but as I
>> keep getting reminded in my own Fossil-hosted public project, some people
>> just refuse to install and use anything they don’t absolutely have to.  It’s
>> six easy steps, but apparently that’s too many for some.
>
>
> Agreed, and what is more sad is that Fossil is so much better at actual
> "Version-Control" (as opposed to making sharing code easiest). If we could
> get the rest of the World to rather Fossil, everybody wins. (I can already
> hear Linus clutching his chest and breathing erratically!)
>
>
>
> ___
> 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] SQLite Options

[Clyde Eisenbeis]

Is SQLite Version 3 the same as SQLite3? ...
http://www.sqlite.org/download.html.

On Thu, Feb 16, 2017 at 3:01 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 16 Feb 2017, at 8:40pm, Clyde Eisenbeis <cte...@gmail.com> wrote:
>
>> Is there an SQLite version that is comprised of fewer dlls, etc.? ...
>> Perhaps SQLite3?
>
> If you’re writing C or C++ code all you need is the amalgamation source code 
> files from the SQLite3 site.  You compile them into your program.  No DLLs 
> needed at all.
>
> The need for DLLs comes when you need a 'shim' to allow your favourite 
> programming language to be able to call the SQLite API functions.  Even then 
> you can do without them if your favourite programming language has the 
> ability to call C functions directly.
>
> 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

[sqlite] SQLite Options

[Clyde Eisenbeis]

I started writing SQLite code about two years ago (Visual Studio 2013,
C#, WPF) ... with a significant delay, since then, because of a
physical move.

The code is written for a specific use on my computer ... no other users.

SQLite was chosen so my sons could eventually install this program on
their computer ... no database needs to be installed ... no other
installation required.

I don't recall the actions taken then, but I do see quite a few
additional files (EntityFramework.dll, EntityFramework.SqlServer.dll,
etc.) as references ... see attachment.

Is there an SQLite version that is comprised of fewer dlls, etc.? ...
Perhaps SQLite3?

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

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

I have a solution, thanks to Alex Smith.  This works:

sqliteCmd.CommandText = "INSERT INTO "

System.Data.SQLite.SQLiteTransaction sqliteTran =
sqliteConnection.BeginTransaction();
sqliteCmd.ExecuteNonQuery();
long lKeyID = sqliteConnection.LastInsertRowId;
sqliteTran.Commit();
stKeyID = lKeyID.ToString();

This does not require SQLite3.  Thanks to all on the SQLite Mailing List!

Clyde


On Tue, Feb 7, 2017 at 6:17 PM, Kevin Benson <kevin.m.ben...@gmail.com> wrote:
> On Tue, Feb 7, 2017 at 4:11 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>
>> int iKeyID = (int)sqliteCmd.ExecuteScalar();
>>
>
> I believe the type of last_insert_rowid() is *always* INT64
>
> --
>--
>   --
>  --Ö¿Ö--
> K e V i N
> ___
> 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] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

Chris,

I have tried:

using (System.Data.SQLite.SQLiteConnection sqliteConnection = new
System.Data.SQLite.SQLiteConnection("Data Source=" + stPathFilename +
";")) {
using (System.Data.SQLite.SQLiteCommand sqliteCmd =
sqliteConnection.CreateCommand()) {
...
sqliteCmd.CommandText = "INSERT INTO ..."
...
sqliteCmd.ExecuteNonQuery();
sqliteCmd.CommandText = "SELECT last_insert_rowid()";
int iKeyID = (int)sqliteCmd.ExecuteScalar();

End up with an exception: "Specified cast is not valid."

Could you provide a specific example for SQLite that does work?  Thanks!



On Mon, Feb 6, 2017 at 9:55 AM, Chris Locke <ch...@chrisjlocke.co.uk> wrote:
> Why do you say 'there is no equivalence' ?
> Have you read the link I posted in the reply to your question nearly 3 days
> ago?
>
> Last_insert_rowid()
>
> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> select @@identity and 'select last_insert_rowid()' perform the same action
> - retrieving the last unique row reference.  It was the answer to your
> query.  The link provides further research.
>
>
> On Mon, Feb 6, 2017 at 1:19 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>
>> In this case, there is only one record added ... no one else has
>> access to this database.
>>
>> In the past, I have locked a record, so no one else can access that
>> record while it is being modified.  Is locking an option in SQLite?
>>
>> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
>> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?
>>
>> On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter <h...@scigames.at> wrote:
>> > But only if you can guarantee that your statement inserts exactly one
>> record and that nothing is executed on your connection between the insert
>> and the call.
>> >
>> > -Ursprüngliche Nachricht-
>> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> Im Auftrag von Chris Locke
>> > Gesendet: Freitag, 03. Februar 2017 15:41
>> > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>> > Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY
>> >
>> > Last_insert_rowid()
>> >
>> > https://www.sqlite.org/c3ref/last_insert_rowid.html
>> >
>> > On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <cte...@gmail.com>
>> wrote:
>> >
>> >> For OLE DB SQL, I have retrieved the primary key:
>> >>
>> >> -
>> >>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
>> >> System.Data.OleDb.OleDbConnection(stConnectString))
>> >>   {
>> >> using (System.Data.OleDb.OleDbCommand oledbCmd =
>> >> oledbConnect.CreateCommand())
>> >> {
>> >>   ...
>> >>   oledbCmd.ExecuteNonQuery();
>> >>   //Retrieve the ID
>> >>   oledbCmd.CommandText = "Select @@Identity";
>> >>   int iKeyID = (int)oledbCmd.ExecuteScalar();
>> >>   stKeyID = iKeyID.ToString();
>> >> -
>> >>
>> >> What is the correct nomenclature for SQLite?
>> >> ___
>> >> 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
>> >
>> >
>> > ___
>> >  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
>> ___
>> 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] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

I'm writing WPF / C# code.  This is part of a function that is called
whenever the user enters data that is saved.  The primary key is used
whenever the user changes that record ... to ensure the correct data
is changed.

On Mon, Feb 6, 2017 at 10:11 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 6 Feb 2017, at 1:30pm, Clyde Eisenbeis <cte...@gmail.com> wrote:
>
>> What is the correct nomenclature for using last_insert_rowid() for
>> SQLite?  For OLE DB I've used ... oledbCmd.CommandText = "Select
>> @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar().
>
> last_insert_row() is an SQL variable.  In whatever API you’re using, do 
> whatever you’d do to execute a SELECT command but instead of selecting data 
> from a table do
>
> SELECT last_insert_row()
>
> instead.  Please note that the standard SQLite API is C code.  If you’re not 
> programming in C you have to tell us what you are programming in, or we have 
> no clue how your programming language accesses SQLite databases.
>
> 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] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

What is the correct nomenclature for using last_insert_rowid() for
SQLite?  For OLE DB I've used ... oledbCmd.CommandText = "Select
@@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar().

On Mon, Feb 6, 2017 at 7:24 AM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Clyde Eisenbeis wrote:
>> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
>> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?
>
> This has *nothing* to do with OLE DB; @@Identity is an SQL Server
> specific thing.  SQLite's is last_insert_rowid(), and both have
> exactly the same restrictions.
>
>
> Regards,
> Clemens
> ___
> 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] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

In this case, there is only one record added ... no one else has
access to this database.

In the past, I have locked a record, so no one else can access that
record while it is being modified.  Is locking an option in SQLite?

Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
"Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?

On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter <h...@scigames.at> wrote:
> But only if you can guarantee that your statement inserts exactly one record 
> and that nothing is executed on your connection between the insert and the 
> call.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Chris Locke
> Gesendet: Freitag, 03. Februar 2017 15:41
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY
>
> Last_insert_rowid()
>
> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>
>> For OLE DB SQL, I have retrieved the primary key:
>>
>> -
>>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
>> System.Data.OleDb.OleDbConnection(stConnectString))
>>   {
>> using (System.Data.OleDb.OleDbCommand oledbCmd =
>> oledbConnect.CreateCommand())
>> {
>>   ...
>>   oledbCmd.ExecuteNonQuery();
>>   //Retrieve the ID
>>   oledbCmd.CommandText = "Select @@Identity";
>>   int iKeyID = (int)oledbCmd.ExecuteScalar();
>>   stKeyID = iKeyID.ToString();
>> -
>>
>> What is the correct nomenclature for SQLite?
>> ___
>> 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
>
>
> ___
>  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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

To clarify further, the SQLite function is attached.  Retrieving the
primary key is commented (used by System.Data.OleDb function).

On Sun, Feb 5, 2017 at 10:40 AM, J Decker <d3c...@gmail.com> wrote:
> http://data.sqlite.org/c3ref/last_insert_rowid.html
>
> there is an api call to get it; or you can use select and get it
>
> the .net library has it as a connection property  LastInsertRowId
>
> https://www.crestron.com/reference/simpl_sharp/html/P_Crestron_SimplSharp_SQLite_SQLiteConnection_LastInsertRowId.htm
>
> On Sun, Feb 5, 2017 at 5:54 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>>
>> On 5 Feb 2017, at 1:26pm, Clyde Eisenbeis <cte...@gmail.com> wrote:
>>
>> > The compiler complains about "SELECT last_insert_rowid()" ... which
>> > appears to be limited to SQLite3.
>>
>> The compiler should never have got that string.  The string is executed
>> when the program is already compiled, just like any other SELECT command.
>>
>> Find some context where you can execute any other SELECT command, and use
>> "last_insert_rowid()" as a column name.
>>
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Pros / Cons

[Clyde Eisenbeis]

This is good information!

I posted "[sqlite] Retrieve INTEGER PRIMARY KEY" a few days ago.  The
only solution proposed appears to use sqlite3.

On Sat, Feb 4, 2017 at 5:34 PM, Joe Mistachkin  wrote:
>
> Random Coder wrote:
>>
>> And in case it's not obvious: System.Data.Sqlite _is_ sqlite3
>>
>
> To clarify and expand upon the above reply:
>
> System.Data.SQLite is an ADO.NET based managed wrapper around the
> SQLite.
>
> It includes the SQLite core library, compiled with a few extra
> options and loadable extensions, and some extra code to help it
> integrate better with the .NET Framework.  The file name for the
> native portions of System.Data.SQLite (also known as the "interop
> assembly") is typically "SQLite.Interop.dll" (i.e. this file name
> is used instead of "sqlite3.dll" by the P/Invoke integration).
>
> --
> 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] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

The compiler complains about "SELECT last_insert_rowid()" ... which
appears to be limited to SQLite3.  Perhaps I'm missing something?

On Sat, Feb 4, 2017 at 10:28 AM, Wolfgang Enzinger <sql...@enzinger.net> wrote:
> Am Sat, 4 Feb 2017 09:04:58 -0600 schrieb Clyde Eisenbeis:
>
>> When I enter last_insert_rowid(), the compiler complains.  I think
>> last_insert_rowid() is SQLite3.
>>
>> Is there an equivalent for System.Data.SQLite?
>
> I don't know, but you can use the SQL function of the same name:
> https://www.sqlite.org/lang_corefunc.html#last_insert_rowid
>
> SELECT last_insert_rowid();
>
> HTH, Wolfgang
>
> ___
> 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] SQLite3 Pros / Cons

[Clyde Eisenbeis]

I'm new to SQLite ... started using it a few months ago.  I was
unaware of SQLite3 until I joined the SQLite mailing list.

What are the pros / cons of SQLite3?

If I switched from "using System.Data.SQLite" to SQLite3, are all of
the functions in a .dll I could download and use?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

When I enter last_insert_rowid(), the compiler complains.  I think
last_insert_rowid() is SQLite3.

Is there an equivalent for System.Data.SQLite?



On Fri, Feb 3, 2017 at 8:40 AM, Chris Locke <ch...@chrisjlocke.co.uk> wrote:
> Last_insert_rowid()
>
> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>
>> For OLE DB SQL, I have retrieved the primary key:
>>
>> -
>>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
>> System.Data.OleDb.OleDbConnection(stConnectString))
>>   {
>> using (System.Data.OleDb.OleDbCommand oledbCmd =
>> oledbConnect.CreateCommand())
>> {
>>   ...
>>   oledbCmd.ExecuteNonQuery();
>>   //Retrieve the ID
>>   oledbCmd.CommandText = "Select @@Identity";
>>   int iKeyID = (int)oledbCmd.ExecuteScalar();
>>   stKeyID = iKeyID.ToString();
>> -
>>
>> What is the correct nomenclature for SQLite?
>> ___
>> 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] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

For OLE DB SQL, I have retrieved the primary key:

-
  using (System.Data.OleDb.OleDbConnection oledbConnect = new
System.Data.OleDb.OleDbConnection(stConnectString))
  {
using (System.Data.OleDb.OleDbCommand oledbCmd =
oledbConnect.CreateCommand())
{
  ...
  oledbCmd.ExecuteNonQuery();
  //Retrieve the ID
  oledbCmd.CommandText = "Select @@Identity";
  int iKeyID = (int)oledbCmd.ExecuteScalar();
  stKeyID = iKeyID.ToString();
-

What is the correct nomenclature for SQLite?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite use field definitions?

[Clyde Eisenbeis]

What about the INTEGER PRIMARY KEY (defined in my first email post)?
Does this field start at 1, and then auto increment for each new line?

On Thu, Feb 2, 2017 at 5:02 AM, R Smith <rsm...@rsweb.co.za> wrote:
>
>
> On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote:
>>
>> I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
>> handle char strings longer than 256.
>>
>> However, I don't see any complaints by SQLite when I use MEMO.
>
>
> SQLite will never complain about anything you use as a type. What you should
> to use is TEXT. In SQLite any string value, of any length* can be stored in
> any column, but will be "handled like text" when the column is of the TEXT
> type affinity, and also allow a collation method. Honestly, you can put the
> text of the complete volumes of Don Quixotic in a single TEXT field in a
> database.
>
> * - As long as the string memory doesn't exceed 4GB, the char-size of which
> may differ based on encoding, and if you add the quoted string to the query
> text directly (as opposed to binding it via the API), then the maximum size
> will be governed by the max character length of the query input, which can
> be configured via the API also. You can add the string as memory bytes as a
> BLOB to go bigger, but then you lose collation capability I'll stop
> there, it's probably more unnecessary info than you wished for already.
>
>
> ___
> 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] Does SQLite use field definitions?

[Clyde Eisenbeis]

I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
handle char strings longer than 256.

However, I don't see any complaints by SQLite when I use MEMO.

On Tue, Jan 31, 2017 at 10:33 AM, Hick Gunter <h...@scigames.at> wrote:
> Yes. See http://sqlite.org/lang_createtable.html for details. I also suggest 
> you look at http://sqlite.org/datatype3.html too
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Clyde Eisenbeis
> Gesendet: Dienstag, 31. Jänner 2017 17:07
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: [sqlite] Does SQLite use field definitions?
>
> In the past, when using Access as a database, I have specified field 
> definitions.  These field definitions have been used when creating a table.
>
> public const string stFIELD_DEFINITIONS = " fstPriority TEXT, fstInfo MEMO, 
> fstDateCreated TEXT, fstDateModified TEXT, fiKeyID INTEGER PRIMARY KEY ";
>
> Does SQLite (System.Data.SQLite) use field definitions too?
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does SQLite use field definitions?

[Clyde Eisenbeis]

In the past, when using Access as a database, I have specified field
definitions.  These field definitions have been used when creating a
table.

public const string stFIELD_DEFINITIONS = " fstPriority TEXT, fstInfo
MEMO, fstDateCreated TEXT, fstDateModified TEXT, fiKeyID INTEGER
PRIMARY KEY ";

Does SQLite (System.Data.SQLite) use field definitions too?
___
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 <war...@etr-usa.com> wrote:
> On Jan 27, 2017, at 1:09 PM, Clyde Eisenbeis <cte...@gmail.com> 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?

[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
<jklow...@schemamania.org> wrote:
> On Thu, 26 Jan 2017 11:45:41 -0600
> Clyde Eisenbeis <cte...@gmail.com> 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?

[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 <slav...@bigfraud.org> wrote:
>
> On 27 Jan 2017, at 5:51pm, Clyde Eisenbeis <cte...@gmail.com> 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?

[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 <gbi...@web.de> 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
> <https://msdn.microsoft.com/en-us/library/system.data.common%28v=vs.110%29.aspx>
> (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?

[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 <sql...@mistachkin.com> 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?

[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 <war...@etr-usa.com> wrote:
> On Jan 26, 2017, at 8:40 AM, Clyde Eisenbeis <cte...@gmail.com> 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?

[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 <cte...@gmail.com> 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 <cte...@gmail.com> wrote:
>> I'm searching the internet for sqlite3_bind_* () examples.
>>
>> On Thu, Jan 26, 2017 at 9:14 AM, Andy Ling <andy.l...@s-a-m.com> 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 <herib...@scharnagl.com> 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 <d...@sqlite.org> wrote:
>>>>>>
>>>>>> On 1/25/17, Warren Young <war...@etr-usa.com> 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

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 <cte...@gmail.com> wrote:
> I'm searching the internet for sqlite3_bind_* () examples.
>
> On Thu, Jan 26, 2017 at 9:14 AM, Andy Ling <andy.l...@s-a-m.com> 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 <herib...@scharnagl.com> 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 <d...@sqlite.org> wrote:
>>>>>
>>>>> On 1/25/17, Warren Young <war...@etr-usa.com> 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 <andy.l...@s-a-m.com> 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 <herib...@scharnagl.com> 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 <d...@sqlite.org> wrote:
>>>>
>>>> On 1/25/17, Warren Young <war...@etr-usa.com> 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]

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 <cte...@gmail.com> 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 <herib...@scharnagl.com> 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 <d...@sqlite.org> wrote:
>>>>
>>>> On 1/25/17, Warren Young <war...@etr-usa.com> 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?

[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 <herib...@scharnagl.com> 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 <d...@sqlite.org> wrote:
>>>
>>> On 1/25/17, Warren Young <war...@etr-usa.com> 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?

[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?

[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 <cte...@gmail.com> 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 <war...@etr-usa.com> wrote:
>> On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis <cte...@gmail.com> 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 <war...@etr-usa.com> wrote:
> On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis <cte...@gmail.com> 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

[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

Re: [sqlite] _SQLITEINT_H_ vs SQLITEINT_H

[Clyde Eisenbeis]

It's not clear how I post questions.  Do I just send to this mailing list?

On Wed, Jan 25, 2017 at 6:59 AM, Theo Veenker  wrote:
> Hi all,
>
> Just downloaded the 3.16.2 amalgamation (used 3.11.1 before). I then wanted
> to apply the userauth extension. The resulting sqlite3.c doesn't compile
> however because userauth.c contains this fragment:
> #ifndef _SQLITEINT_H_
> # include "sqliteInt.h"
> #endif
> Apparently _SQLITEINT_H_ should now be SQLITEINT_H. Maybe this needs a fix?
>
> A grep in sqlite3.c shows one more (harmless however) occurrence of
> _SQLITEINT_H_ for json1.c.
>
> Theo
>
> --
> Theo Veenker  |  Beexy - Behavioral Experiment Software
> +31(0)524-541531  |  +31(0)6-42525777 mobile
> theo.veen...@beexy.nl  |  www.beexy.nl
> ___
> 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