Re: [sqlite] Connection with SQLite db encrypted

[Luca Scurati]

Ok, thanks!

Il giorno mer 27 nov 2019 alle ore 08:29 Simon Slavin 
ha scritto:

> On 27 Nov 2019, at 7:22am, Luca Scurati  wrote:
>
> > I ecnrypt the db through the DB Browser for SQLCipher tool. If I try to
> access the database from this application, it works.Do you have any other
> method for encrypt a SQLite db?
>
> SQLite databases can be encrypted in many different ways.
>
> The encryption method used by SQLCipher is not the same as the default
> encryption used by Microsoft.Data.Sqlite or System.Data.SQLite.  If you
> want to use it, read the documentation for SQLCipher on how to access your
> database in your own software.
>
> 
>
> I'm sorry but I don't know enough about Windows to advise you further, but
> perhaps someone else does.
> ___
> 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] Connection with SQLite db encrypted

[Simon Slavin]

On 27 Nov 2019, at 7:22am, Luca Scurati  wrote:

> I ecnrypt the db through the DB Browser for SQLCipher tool. If I try to 
> access the database from this application, it works.Do you have any other 
> method for encrypt a SQLite db?

SQLite databases can be encrypted in many different ways.

The encryption method used by SQLCipher is not the same as the default 
encryption used by Microsoft.Data.Sqlite or System.Data.SQLite.  If you want to 
use it, read the documentation for SQLCipher on how to access your database in 
your own software.



I'm sorry but I don't know enough about Windows to advise you further, but 
perhaps someone else does.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Connection with SQLite db encrypted

[Luca Scurati]

Anyway I found another library called Mono.Data.Sqlite that recognize the
SetPassworg and ChangePassword method (the two libraries listed above did
not recognize them) but it gives me the following error: Unable to find an
entry point named 'sqlite3_key' in DLL 'sqlite3'

Do you have any idea?

Il giorno mer 27 nov 2019 alle ore 08:22 Luca Scurati <
lucascurati2...@gmail.com> ha scritto:

> I ecnrypt the db through the DB Browser for SQLCipher tool. If I try to
> access the database from this application, it works.Do you have any other
> method for encrypt a SQLite db?
>
> Il giorno mar 26 nov 2019 alle ore 18:55 Simon Slavin <
> slav...@bigfraud.org> ha scritto:
>
>> On 26 Nov 2019, at 1:37pm, Luca Scurati 
>> wrote:
>>
>> > I'm trying to create a program that communicates with a SQLite database
>> encrypted by a password. If I try with a db without any password it work,
>> but with a database with password, after connecting, when executing the
>> query it gives the error "File is not a database" I've tried both with
>> Microsoft.Data.Sqlite and with System.Data.SQLite.
>>
>> Can it read the database correctly using any tool at all ?  In other
>> words, are you certain that the password is correct ?
>>
>> Which encryption method is used ?  Is it the Microsoft-only one, or SEE,
>> or something else ?
>>
>> How are you specifying the password ?  (Make sure you don't paste the
>> real password into your reply !)
>>
>> System.Data.SQLite uses a Microsoft-only form of encryption.  In other
>> words, if you have a database made with the supported SEE encryption
>> method, it can't open it.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Connection with SQLite db encrypted

[Luca Scurati]

I ecnrypt the db through the DB Browser for SQLCipher tool. If I try to
access the database from this application, it works.Do you have any other
method for encrypt a SQLite db?

Il giorno mar 26 nov 2019 alle ore 18:55 Simon Slavin 
ha scritto:

> On 26 Nov 2019, at 1:37pm, Luca Scurati  wrote:
>
> > I'm trying to create a program that communicates with a SQLite database
> encrypted by a password. If I try with a db without any password it work,
> but with a database with password, after connecting, when executing the
> query it gives the error "File is not a database" I've tried both with
> Microsoft.Data.Sqlite and with System.Data.SQLite.
>
> Can it read the database correctly using any tool at all ?  In other
> words, are you certain that the password is correct ?
>
> Which encryption method is used ?  Is it the Microsoft-only one, or SEE,
> or something else ?
>
> How are you specifying the password ?  (Make sure you don't paste the real
> password into your reply !)
>
> System.Data.SQLite uses a Microsoft-only form of encryption.  In other
> words, if you have a database made with the supported SEE encryption
> method, it can't open it.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Message Passing Interface

[Simon Slavin]

On 26 Nov 2019, at 10:54pm, Enzo Madda  wrote:

> Id appreciate constructive feedback or questions, thanks!

Your project is a feeder library to a SQL engine.  So you might want to add to 
your documentation something about how your users can avoid SQL injection 
vulnerabilities.  Or how your library makes them impossible.  Or what you've 
done to help reduce them.  Whatever.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Message Passing Interface

[Enzo Madda]

Hello,

Ive been working on something called the SQLite Message Passing Interface,
or SMPI for short.

It allows you to use SQLite from Javascript code in React Native apps.

There is an introduction at https://sqlitempi.com/.

Id appreciate constructive feedback or questions, thanks!

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

Ah-hah!

Keith Medcalf, on Tuesday, November 26, 2019 03:58 PM, wrote...
>
>
> Ah, you have missing dependancies.  You need to make sure that the
> dependencies can be loaded.  When Windows attempts to load the module
> all the dependencies must be loaded as well, otherwise the loader
> cannot return a handle to the loaded module.  If no module handle is
> returned then the message "Module not found" is returned because
> sqlite3_load_extension does not actually check whether the module
> exists, but only whether or not it was loaded.


> So if the module exists and you get the message that it was not found,
> that is because it could not be loaded ...
>
> use depends.exe to see what other dll's are required and either put
> them on the path or in the same directory ...


> >
> >Keith Medcalf, on Tuesday, November 26, 2019 03:38 PM, wrote...
> >>
> >>
> >> What is the canonical name of the file you are trying to load?
> >
> >c:\PMOProjects\libsqlite3decimal.dll
> >
> >As shown by the dir command,
> >
> >15:40:36.57>dir c:\PMOProjects\libsqlite3decimal.dll
> > Volume in drive C is Windows
> > Volume Serial Number is 40AA-E472
> >
> > Directory of c:\PMOProjects
> >
> >11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
> >   1 File(s)225,376 bytes
> >   0 Dir(s)  431,761,633,280 bytes free
> >
> >
> >> It seems that you are being told that
> >"c:\PMOProjects\libsqlite3decimal.dll" does not exist.
> >
> >But it is there,
> >
> >15:40:48.95>dir c:\PMOProjects
> > Volume in drive C is Windows
> > Volume Serial Number is 40AA-E472
> >
> > Directory of c:\PMOProjects
> >
> >11/26/2019  03:40 PM   , on
> >11/26/2019  03:40 PM   , on
> >11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
> >11/25/2019  09:21 AM 3,446,300 PMOProjs.exe
> >11/22/2019  02:17 PM   926,748 PMOUpdater.exe
> >10/04/2019  11:30 AM   932,223 sqlite3.dll
> >   4 File(s)  5,530,647 bytes
> >   2 Dir(s)  431,760,363,520 bytes free
> >
> >15:43:18.90>
> >
> >
> >> Are you sure that the file exists and that you have permission to
> >read/execute it?
> >
> >Aaaah, permissions... H... I created this in cygwin, and copied it to
> >that directory.  Let me try something...

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Keith Medcalf]

Ah, you have missing dependancies.  You need to make sure that the dependencies 
can be loaded.  When Windows attempts to load the module all the dependencies 
must be loaded as well, otherwise the loader cannot return a handle to the 
loaded module.  If no module handle is returned then the message "Module not 
found" is returned because sqlite3_load_extension does not actually check 
whether the module exists, but only whether or not it was loaded.

So if the module exists and you get the message that it was not found, that is 
because it could not be loaded ...

use depends.exe to see what other dll's are required and either put them on the 
path or in the same directory ...

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Jose Isaias Cabrera
>Sent: Tuesday, 26 November, 2019 13:45
>To: SQLite mailing list 
>Subject: Re: [sqlite] Passing a path to sqlite3.exe to load a dll
>
>>
>>
>
>Keith Medcalf, on Tuesday, November 26, 2019 03:38 PM, wrote...
>>
>>
>> What is the canonical name of the file you are trying to load?
>
>c:\PMOProjects\libsqlite3decimal.dll
>
>As shown by the dir command,
>
>15:40:36.57>dir c:\PMOProjects\libsqlite3decimal.dll
> Volume in drive C is Windows
> Volume Serial Number is 40AA-E472
>
> Directory of c:\PMOProjects
>
>11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
>   1 File(s)225,376 bytes
>   0 Dir(s)  431,761,633,280 bytes free
>
>
>> It seems that you are being told that
>"c:\PMOProjects\libsqlite3decimal.dll" does not exist.
>
>But it is there,
>
>15:40:48.95>dir c:\PMOProjects
> Volume in drive C is Windows
> Volume Serial Number is 40AA-E472
>
> Directory of c:\PMOProjects
>
>11/26/2019  03:40 PM  .
>11/26/2019  03:40 PM  ..
>11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
>11/25/2019  09:21 AM 3,446,300 PMOProjs.exe
>11/22/2019  02:17 PM   926,748 PMOUpdater.exe
>10/04/2019  11:30 AM   932,223 sqlite3.dll
>   4 File(s)  5,530,647 bytes
>   2 Dir(s)  431,760,363,520 bytes free
>
>15:43:18.90>
>
>
>> Are you sure that the file exists and that you have permission to
>read/execute it?
>
>Aaaah, permissions... H... I created this in cygwin, and copied it to
>that directory.  Let me try something...
>
>___
>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] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

Jose Isaias Cabrera, on Tuesday, November 26, 2019 03:44 PM, wrote...

> Keith Medcalf, on Tuesday, November 26, 2019 03:38 PM, wrote...
> > What is the canonical name of the file you are trying to load?
>
> c:\PMOProjects\libsqlite3decimal.dll
>
> As shown by the dir command,
>
> 15:40:36.57>dir c:\PMOProjects\libsqlite3decimal.dll
>  Volume in drive C is Windows
>  Volume Serial Number is 40AA-E472
>
>  Directory of c:\PMOProjects
>
> 11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
>1 File(s)225,376 bytes
>0 Dir(s)  431,761,633,280 bytes free
>
>
> > It seems that you are being told that 
> > "c:\PMOProjects\libsqlite3decimal.dll" does not exist.
>
> But it is there,
>
> 15:40:48.95>dir c:\PMOProjects
>  Volume in drive C is Windows
>  Volume Serial Number is 40AA-E472
>
>  Directory of c:\PMOProjects
>
> 11/26/2019  03:40 PM   , on
> 11/26/2019  03:40 PM   , on
> 11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
> 11/25/2019  09:21 AM 3,446,300 PMOProjs.exe
> 11/22/2019  02:17 PM   926,748 PMOUpdater.exe
> 10/04/2019  11:30 AM   932,223 sqlite3.dll
>4 File(s)  5,530,647 bytes
>2 Dir(s)  431,760,363,520 bytes free
>
> 15:43:18.90>
>
>
> > Are you sure that the file exists and that you have permission to 
> > read/execute it?
>
> Aaaah, permissions... H... I created this in cygwin, and copied it to 
> that directory.  Let me try something...

Yes, I am the owner,
15:46:48.04>dir /Q C:\PMOProjects
 Volume in drive C is Windows
 Volume Serial Number is 40AA-E472

 Directory of C:\PMOProjects

11/26/2019  03:40 PM  BUILTIN\Administrators .
11/26/2019  03:40 PM  NT SERVICE\TrustedInsta..
11/26/2019  02:19 PM   225,376 AMER\e608313   
libsqlite3decimal.dll
11/25/2019  09:21 AM 3,446,300 AMER\e608313   PMOProjs.exe
11/22/2019  02:17 PM   926,748 AMER\e608313   PMOUpdater.exe
10/04/2019  11:30 AM   932,223 AMER\e608313   sqlite3.dll
   4 File(s)  5,530,647 bytes
   2 Dir(s)  431,782,502,400 bytes free


I think there is a bug in the Windows tool.  I will keep trying until I get to 
the bottom of it.  Thanks.

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

>
>

Keith Medcalf, on Tuesday, November 26, 2019 03:38 PM, wrote...
>
>
> What is the canonical name of the file you are trying to load?

c:\PMOProjects\libsqlite3decimal.dll

As shown by the dir command,

15:40:36.57>dir c:\PMOProjects\libsqlite3decimal.dll
 Volume in drive C is Windows
 Volume Serial Number is 40AA-E472

 Directory of c:\PMOProjects

11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
   1 File(s)225,376 bytes
   0 Dir(s)  431,761,633,280 bytes free


> It seems that you are being told that "c:\PMOProjects\libsqlite3decimal.dll" 
> does not exist.

But it is there,

15:40:48.95>dir c:\PMOProjects
 Volume in drive C is Windows
 Volume Serial Number is 40AA-E472

 Directory of c:\PMOProjects

11/26/2019  03:40 PM  .
11/26/2019  03:40 PM  ..
11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
11/25/2019  09:21 AM 3,446,300 PMOProjs.exe
11/22/2019  02:17 PM   926,748 PMOUpdater.exe
10/04/2019  11:30 AM   932,223 sqlite3.dll
   4 File(s)  5,530,647 bytes
   2 Dir(s)  431,760,363,520 bytes free

15:43:18.90>


> Are you sure that the file exists and that you have permission to 
> read/execute it?

Aaaah, permissions... H... I created this in cygwin, and copied it to that 
directory.  Let me try something...

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Keith Medcalf]

What is the canonical name of the file you are trying to load?

For example, if you are trying to load the file "jitterbug.dll" from the 
directory "c:\a\b\c" then the canonical filename is "c:\a\b\c\jitterbug.dll"

It seems that you are being told that "c:\PMOProjects\libsqlite3decimal.dll" 
does not exist.  

Are you sure that the file exists and that you have permission to read/execute 
it?

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Jose Isaias Cabrera
>Sent: Tuesday, 26 November, 2019 13:28
>To: SQLite mailing list 
>Subject: Re: [sqlite] Passing a path to sqlite3.exe to load a dll
>
>
>Keith Medcalf, on Tuesday, November 26, 2019 02:57 PM, wrote...
>>
>>
>> Escape the reverse solstice with a duplicate reverse solstice (\ -> \\)
>or replace them
>> with normal solstice (\ -> /) since Windows recognizes either as the
>path separator.
>> The CLI, like most things, parses escape sequences on input.
>>
>> Same applies to the C API function.  You specify the full path and name
>of the file.
>> Since the compiler probably also parses escape sequences in its input,
>you probably
>> need to escape your reverse solstice there as well.
>
>Thanks for the support, Keith.  And yes, that is why I wrote.  I tried
>all of those.  Here are some examples:
>
>14:22:23.19>sqlite3
>SQLite version 3.30.0 2019-10-04 15:03:17
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .load
>Usage: .load FILE ?ENTRYPOINT?
>sqlite> .load c:/PMOProjects/libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .load c:\PMOProjects\libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .help load
>.load FILE ?ENTRY?   Load an extension library
>sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .load c:/PMOProjects/libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .load c://PMOProjects//libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .load /PMOProjects/libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .load c:\/PMOProjects\/libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite>
>sqlite> .cd c:\PMOProjects
>Cannot change to directory "c:PMOProjects"
>sqlite> .cd c:\\PMOProjects
>sqlite> .load ./libsqlite3decimal sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .load ./libsqlite3decimal.dll sqlite3_decimal_init
>Error: The specified module could not be found.
>
>sqlite> .cd
>Usage: .cd DIRECTORY
>sqlite> .cd c:\\PMOProjects
>sqlite> .cd c:\PMOProjects
>Cannot change to directory "c:PMOProjects"
>sqlite> .q
>
>
>15:08:12.49>sqlite3 --cmd '.load c:\\PMOProjects\\libsqlite3decimal
>sqlite3_decimal_init'
>Error: unrecognized token: "'.load"
>Error: near "sqlite3_decimal_init": syntax error
>
>15:09:37.68>sqlite3 --cmd ".load c:\\PMOProjects\\libsqlite3decimal
>sqlite3_decimal_init"
>Error: The specified module could not be found.
>
>SQLite version 3.30.0 2019-10-04 15:03:17
>Enter ".help" for usage hints.
>sqlite>
>
>
>
>> >I am trying to load a DLL to the DOS tool, but it's failing.  I am
>> >trying,
>> >
>> >sqlite> .load c:\PMOProjects\sqlite3libIN sqlite3_decimal_init
>> >Error: The specified module could not be found.
>> >
>> >Any help would be greatly appreciated.  Also, if I would like to use
>the
>> >call,
>> >
>> >int sqlite3_load_extension(
>> >  sqlite3 *db,  /* Load the extension into this database
>> >connection */
>> >  const char *zFile,/* Name of the shared library containing
>> >extension */
>> >  const char *zProc,/* Entry point.  Derived from zFile if 0 */
>> >  char **pzErrMsg   /* Put error message here if not 0 */
>> >);
>> >
>> >What would be the path to use in Windows?  Thanks so 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] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

Keith Medcalf, on Tuesday, November 26, 2019 02:57 PM, wrote...
>
>
> Escape the reverse solstice with a duplicate reverse solstice (\ -> \\) or 
> replace them
> with normal solstice (\ -> /) since Windows recognizes either as the path 
> separator.
> The CLI, like most things, parses escape sequences on input.
>
> Same applies to the C API function.  You specify the full path and name of 
> the file.
> Since the compiler probably also parses escape sequences in its input, you 
> probably
> need to escape your reverse solstice there as well.

Thanks for the support, Keith.  And yes, that is why I wrote.  I tried all of 
those.  Here are some examples:

14:22:23.19>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load
Usage: .load FILE ?ENTRYPOINT?
sqlite> .load c:/PMOProjects/libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c:\PMOProjects\libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .help load
.load FILE ?ENTRY?   Load an extension library
sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c:/PMOProjects/libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c://PMOProjects//libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load /PMOProjects/libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c:\/PMOProjects\/libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite>
sqlite> .cd c:\PMOProjects
Cannot change to directory "c:PMOProjects"
sqlite> .cd c:\\PMOProjects
sqlite> .load ./libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load ./libsqlite3decimal.dll sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .cd
Usage: .cd DIRECTORY
sqlite> .cd c:\\PMOProjects
sqlite> .cd c:\PMOProjects
Cannot change to directory "c:PMOProjects"
sqlite> .q


15:08:12.49>sqlite3 --cmd '.load c:\\PMOProjects\\libsqlite3decimal 
sqlite3_decimal_init'
Error: unrecognized token: "'.load"
Error: near "sqlite3_decimal_init": syntax error

15:09:37.68>sqlite3 --cmd ".load c:\\PMOProjects\\libsqlite3decimal 
sqlite3_decimal_init"
Error: The specified module could not be found.

SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
sqlite>



> >I am trying to load a DLL to the DOS tool, but it's failing.  I am
> >trying,
> >
> >sqlite> .load c:\PMOProjects\sqlite3libIN sqlite3_decimal_init
> >Error: The specified module could not be found.
> >
> >Any help would be greatly appreciated.  Also, if I would like to use the
> >call,
> >
> >int sqlite3_load_extension(
> >  sqlite3 *db,  /* Load the extension into this database
> >connection */
> >  const char *zFile,/* Name of the shared library containing
> >extension */
> >  const char *zProc,/* Entry point.  Derived from zFile if 0 */
> >  char **pzErrMsg   /* Put error message here if not 0 */
> >);
> >
> >What would be the path to use in Windows?  Thanks so much.

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Keith Medcalf]

Escape the reverse solstice with a duplicate reverse solstice (\ -> \\) or 
replace them with normal solstice (\ -> /) since Windows recognizes either as 
the path separator.  The CLI, like most things, parses escape sequences on 
input.

Same applies to the C API function.  You specify the full path and name of the 
file.  Since the compiler probably also parses escape sequences in its input, 
you probably need to escape your reverse solstice there as well.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Jose Isaias Cabrera
>Sent: Tuesday, 26 November, 2019 12:35
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Passing a path to sqlite3.exe to load a dll
>
>
>Greetings!
>
>I am trying to load a DLL to the DOS tool, but it's failing.  I am
>trying,
>
>sqlite> .load c:\PMOProjects\sqlite3libIN sqlite3_decimal_init
>Error: The specified module could not be found.
>
>Any help would be greatly appreciated.  Also, if I would like to use the
>call,
>
>int sqlite3_load_extension(
>  sqlite3 *db,  /* Load the extension into this database
>connection */
>  const char *zFile,/* Name of the shared library containing
>extension */
>  const char *zProc,/* Entry point.  Derived from zFile if 0 */
>  char **pzErrMsg   /* Put error message here if not 0 */
>);
>
>What would be the path to use in Windows?  Thanks so much.
>
>josé
>
>___
>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] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

Greetings!

I am trying to load a DLL to the DOS tool, but it's failing.  I am trying,

sqlite> .load c:\PMOProjects\sqlite3libIN sqlite3_decimal_init
Error: The specified module could not be found.

Any help would be greatly appreciated.  Also, if I would like to use the call,

int sqlite3_load_extension(
  sqlite3 *db,  /* Load the extension into this database connection */
  const char *zFile,/* Name of the shared library containing extension */
  const char *zProc,/* Entry point.  Derived from zFile if 0 */
  char **pzErrMsg   /* Put error message here if not 0 */
);

What would be the path to use in Windows?  Thanks so much.

josé

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

Re: [sqlite] Connection with SQLite db encrypted

[Simon Slavin]

On 26 Nov 2019, at 1:37pm, Luca Scurati  wrote:

> I'm trying to create a program that communicates with a SQLite database 
> encrypted by a password. If I try with a db without any password it work, but 
> with a database with password, after connecting, when executing the query it 
> gives the error "File is not a database" I've tried both with 
> Microsoft.Data.Sqlite and with System.Data.SQLite.

Can it read the database correctly using any tool at all ?  In other words, are 
you certain that the password is correct ?

Which encryption method is used ?  Is it the Microsoft-only one, or SEE, or 
something else ?

How are you specifying the password ?  (Make sure you don't paste the real 
password into your reply !)

System.Data.SQLite uses a Microsoft-only form of encryption.  In other words, 
if you have a database made with the supported SEE encryption method, it can't 
open it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 64-bit SQLite3.exe (2019)

[RichardR]

Over three years ago I asked if the official CLI tools could be compiled as a
64-bit exe.

http://sqlite.1065341.n5.nabble.com/64-bit-SQLite3-exe-tp90771.html

I thought I would ask again...



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Connection with SQLite db encrypted

[Luca Scurati]

I'm trying to create a program that communicates with a SQLite database
encrypted by a password. If I try with a db without any password it work,
but with a database with password, after connecting, when executing the
query it gives the error "File is not a database"
I've tried both with Microsoft.Data.Sqlite and with System.Data.SQLite.
Do you have any advice?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[David Raymond]

Not the reason for the slowdown, but note that both of these are redundant:

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
 data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
 ignored_id
);

...because you declared them as the primary keys in the table creation. So you 
now have 2 different indexes on the exact same data for each of those.


The rest of it looks fine to me anyway, and I'm not sure why you'd be seeing 
such slow times. Old slow hard disk?

If you analyze and vacuum it does it get any better?

I think the CLI has something like ".scanstats on" to get a little more info, 
but I'm not sure how much more info it'll provide.


-Original Message-
From: sqlite-users  On Behalf Of 
Hick Gunter
Sent: Tuesday, November 26, 2019 4:57 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

You are using text columns as primary keys and referencing them directly in 
foreign keys. This is probably not what you want, because it duplicates the 
text key. Also, with foreign keys enabled, your join is not accomplishing 
anything more than a direct select from joining_table, just with more effort 
(and circumventing the count() optimization).

SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the 
table. This is what you should be using as a foreign key, because it is twice 
as fast as using an index.

OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those 
too

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jonathan Moules
Gesendet: Dienstag, 26. November 2019 10:25
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes

Hi List,
I have a relational table setup where I've built indexes but I'm still seeing 
very slow join times on middling amounts of data. I'm guessing I'm doing 
something wrong but I can't see what. (SQLite: 3.24.0)

Simplified schema as below.
The ids are 16 character hex strings. I've included the ignore_me table only 
because it's relevant to the indexes.
Note: I can *guarantee* that the data inserted into `data_table` and 
`ignore_me` is ordered by their respective primary keys ASC. Entries in 
joining_table are ordered by one of either data_id ASC or ignored_id ASC 
depending on creation method.

--==

-- 1.7 million items
CREATE TABLE data_table (
 data_idTEXT PRIMARY KEY
 NOT NULL
 COLLATE NOCASE,
 data_1TEXT,
 data_2 TEXT );

-- 1.9 million items
CREATE TABLE joining_table (
 data_id TEXT REFERENCES data_table (data_id)
 NOT NULL
 COLLATE NOCASE,
 ignored_id TEXTREFERENCES ignore_me (ignored_id)
 NOT NULL
 COLLATE NOCASE,
 misc_col_1TEXT,
 misc_col_2TEXT
);

-- ~200,000 items
CREATE TABLE ignore_me (
 ignored_idTEXT PRIMARY KEY
 NOT NULL
 COLLATE NOCASE );

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
 data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
 ignored_id
);

-- Allow quick joining from data_table to ignore_me CREATE INDEX IF NOT EXISTS 
joining_table__data_ignored_id__fk_idx ON joining_table (
 data_id ASC,
 ignored_id ASC
);
-- Allow quick joining from ignore_me to data_table CREATE INDEX IF NOT EXISTS 
joining_table__ignored_data_id__fk_idx ON joining_table (
 ignored_id ASC,
 data_id ASC
);

-- Example data:

INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6'); INSERT INTO 
data_table (data_id) VALUES ('579c57f1268c0f5c');

INSERT INTO ignore_me VALUES ('c402eb3f05d433f2'); INSERT INTO ignore_me VALUES 
('d827e58953265f63'); INSERT INTO ignore_me VALUES ('ec1d2e817f55b249');

INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 
'c402eb3f05d433f2'); INSERT INTO joining_table (data_id, ignored_id) VALUES 
('00196a21e8c0f9f6', 'd827e58953265f63'); INSERT INTO joining_table (data_id, 
ignored_id) VALUES ('579c57f1268c0f5c', 'ec1d2e817f55b249');




-- Then to test the speed I'm simply doing:
 SELECT
 count(1)
 FROM
 data_table
 JOIN joining_table USING (data_id);

--==

The query plan says it's using the indexes:
 SCAN TABLE joining_table USING COVERING INDEX 
joining_table__ignored_data_id__fk_idx
 SEARCH TABLE data_table USING COVERING INDEX data_table__data_id__pk_idx 
(data_id=?)

But it takes about 20 seconds to do that count on 

Re: [sqlite] Shell commands for controlling headers

[John McMahon]

On 26/11/2019 02:49, David Raymond wrote:

Dr Hipp replied to this 2 days ago with this:


Documentation fix https://www.sqlite.org/docsrc/info/a2762f031964e774
will appears in the next release.

".header" is an abbreviation for ".headers" and does exactly the same thing.


AFAIK all dot commands can be abbreviated to the shortest distinct 
partial word, thus ".headers on" can be shortened to ".hea on". This has 
been the case at least back to sqlite2 and back then, this was mentioned 
in the CLI documentation and is probably also somewhere in the current 
documentation. I leave finding it as an exercise for the reader.


John




-Original Message-
From: sqlite-users  On Behalf Of 
John McKown
Sent: Monday, November 25, 2019 9:51 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Shell commands for controlling headers

On Mon, Nov 25, 2019 at 8:42 AM Craig Maynard  wrote:


All,

Could someone clarify the difference between the two sqlite3 shell
commands .header and .headers?

The relevant documentation page: https://www.sqlite.org/cli.html

On the cli page, .header is discussed in section 5 but does not appear in
Section 3.

Thanks,
Craig

--
Craig H Maynard
Rhode Island, USA



In the sqlite cli itself, doing an ".help", I see:

.header(s)

So I am guessing that they are the same things, perhaps for compatibility
with something in the past.



--
Regards
   John McMahon
  li...@jspect.fastmail.fm

When people say "The climate has changed before,"
these are the kinds of changes they're talking about.
https://xkcd.com/1732/

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

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[Hick Gunter]

You are using text columns as primary keys and referencing them directly in 
foreign keys. This is probably not what you want, because it duplicates the 
text key. Also, with foreign keys enabled, your join is not accomplishing 
anything more than a direct select from joining_table, just with more effort 
(and circumventing the count() optimization).

SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the 
table. This is what you should be using as a foreign key, because it is twice 
as fast as using an index.

OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those 
too

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jonathan Moules
Gesendet: Dienstag, 26. November 2019 10:25
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes

Hi List,
I have a relational table setup where I've built indexes but I'm still seeing 
very slow join times on middling amounts of data. I'm guessing I'm doing 
something wrong but I can't see what. (SQLite: 3.24.0)

Simplified schema as below.
The ids are 16 character hex strings. I've included the ignore_me table only 
because it's relevant to the indexes.
Note: I can *guarantee* that the data inserted into `data_table` and 
`ignore_me` is ordered by their respective primary keys ASC. Entries in 
joining_table are ordered by one of either data_id ASC or ignored_id ASC 
depending on creation method.

--==

-- 1.7 million items
CREATE TABLE data_table (
 data_idTEXT PRIMARY KEY
 NOT NULL
 COLLATE NOCASE,
 data_1TEXT,
 data_2 TEXT );

-- 1.9 million items
CREATE TABLE joining_table (
 data_id TEXT REFERENCES data_table (data_id)
 NOT NULL
 COLLATE NOCASE,
 ignored_id TEXTREFERENCES ignore_me (ignored_id)
 NOT NULL
 COLLATE NOCASE,
 misc_col_1TEXT,
 misc_col_2TEXT
);

-- ~200,000 items
CREATE TABLE ignore_me (
 ignored_idTEXT PRIMARY KEY
 NOT NULL
 COLLATE NOCASE );

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
 data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
 ignored_id
);

-- Allow quick joining from data_table to ignore_me CREATE INDEX IF NOT EXISTS 
joining_table__data_ignored_id__fk_idx ON joining_table (
 data_id ASC,
 ignored_id ASC
);
-- Allow quick joining from ignore_me to data_table CREATE INDEX IF NOT EXISTS 
joining_table__ignored_data_id__fk_idx ON joining_table (
 ignored_id ASC,
 data_id ASC
);

-- Example data:

INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6'); INSERT INTO 
data_table (data_id) VALUES ('579c57f1268c0f5c');

INSERT INTO ignore_me VALUES ('c402eb3f05d433f2'); INSERT INTO ignore_me VALUES 
('d827e58953265f63'); INSERT INTO ignore_me VALUES ('ec1d2e817f55b249');

INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 
'c402eb3f05d433f2'); INSERT INTO joining_table (data_id, ignored_id) VALUES 
('00196a21e8c0f9f6', 'd827e58953265f63'); INSERT INTO joining_table (data_id, 
ignored_id) VALUES ('579c57f1268c0f5c', 'ec1d2e817f55b249');




-- Then to test the speed I'm simply doing:
 SELECT
 count(1)
 FROM
 data_table
 JOIN joining_table USING (data_id);

--==

The query plan says it's using the indexes:
 SCAN TABLE joining_table USING COVERING INDEX 
joining_table__ignored_data_id__fk_idx
 SEARCH TABLE data_table USING COVERING INDEX data_table__data_id__pk_idx 
(data_id=?)

But it takes about 20 seconds to do that count on the full dataset.

The full EXPLAIN from the full dataset:

0Init016000
1Null01100
2OpenRead27718750k(3,NOCASE,NOCASE,)00
3OpenRead37377150k(2,NOCASE,)02
4Rewind2122000
5Column21200
6SeekGE3112100
7IdxGT3112100
8Integer13000
9AggStep0031count(1)01
10Next37100
11Next25001
12AggFinal110count(1)00
13Copy14000
14ResultRow41000
15Halt00000
16Transaction0077001
17Goto01000

Thoughts? What (probably obvious) thing am I missing?

Thanks,
Jonathan

[sqlite] Slow joining of tables with indexes

[Jonathan Moules]

Hi List,
I have a relational table setup where I've built indexes but I'm still 
seeing very slow join times on middling amounts of data. I'm guessing 
I'm doing something wrong but I can't see what. (SQLite: 3.24.0)


Simplified schema as below.
The ids are 16 character hex strings. I've included the ignore_me table 
only because it's relevant to the indexes.
Note: I can *guarantee* that the data inserted into `data_table` and 
`ignore_me` is ordered by their respective primary keys ASC. Entries in 
joining_table are ordered by one of either data_id ASC or ignored_id ASC 
depending on creation method.


--==

-- 1.7 million items
CREATE TABLE data_table (
    data_id                    TEXT     PRIMARY KEY
                                        NOT NULL
                                        COLLATE NOCASE,
    data_1                        TEXT,
    data_2                     TEXT
);

-- 1.9 million items
CREATE TABLE joining_table (
    data_id     TEXT     REFERENCES data_table (data_id)
                            NOT NULL
                            COLLATE NOCASE,
    ignored_id         TEXT    REFERENCES ignore_me (ignored_id)
                            NOT NULL
                            COLLATE NOCASE,
    misc_col_1        TEXT,
    misc_col_2        TEXT
);

-- ~200,000 items
CREATE TABLE ignore_me (
    ignored_id                    TEXT     PRIMARY KEY
                                        NOT NULL
                                        COLLATE NOCASE
);

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
    data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
    ignored_id
);

-- Allow quick joining from data_table to ignore_me
CREATE INDEX IF NOT EXISTS joining_table__data_ignored_id__fk_idx ON 
joining_table (

    data_id ASC,
    ignored_id ASC
);
-- Allow quick joining from ignore_me to data_table
CREATE INDEX IF NOT EXISTS joining_table__ignored_data_id__fk_idx ON 
joining_table (

    ignored_id ASC,
    data_id ASC
);

-- Example data:

INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6');
INSERT INTO data_table (data_id) VALUES ('579c57f1268c0f5c');

INSERT INTO ignore_me VALUES ('c402eb3f05d433f2');
INSERT INTO ignore_me VALUES ('d827e58953265f63');
INSERT INTO ignore_me VALUES ('ec1d2e817f55b249');

INSERT INTO joining_table (data_id, ignored_id) VALUES 
('00196a21e8c0f9f6', 'c402eb3f05d433f2');
INSERT INTO joining_table (data_id, ignored_id) VALUES 
('00196a21e8c0f9f6', 'd827e58953265f63');
INSERT INTO joining_table (data_id, ignored_id) VALUES 
('579c57f1268c0f5c', 'ec1d2e817f55b249');





-- Then to test the speed I'm simply doing:
    SELECT
        count(1)
    FROM
        data_table
        JOIN joining_table USING (data_id);

--==

The query plan says it's using the indexes:
    SCAN TABLE joining_table USING COVERING INDEX 
joining_table__ignored_data_id__fk_idx
    SEARCH TABLE data_table USING COVERING INDEX 
data_table__data_id__pk_idx (data_id=?)


But it takes about 20 seconds to do that count on the full dataset.

The full EXPLAIN from the full dataset:

0    Init    0    16    0        00
1    Null    0    1    1        00
2    OpenRead    2    771875    0    k(3,NOCASE,NOCASE,)    00
3    OpenRead    3    737715    0    k(2,NOCASE,)    02
4    Rewind    2    12    2    0    00
5    Column    2    1    2        00
6    SeekGE    3    11    2    1    00
7    IdxGT    3    11    2    1    00
8    Integer    1    3    0        00
9    AggStep0    0    3    1    count(1)    01
10    Next    3    7    1        00
11    Next    2    5    0        01
12    AggFinal    1    1    0    count(1)    00
13    Copy    1    4    0        00
14    ResultRow    4    1    0        00
15    Halt    0    0    0        00
16    Transaction    0    0    77    0    01
17    Goto    0    1    0        00

Thoughts? What (probably obvious) thing am I missing?

Thanks,
Jonathan



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