[sqlite] system.data.sqlite.dll and Widows 10

[Jeff Steinkamp]

I ran this utility and what I discovered is problematic.

On my development machine, it is always looking for this dependency in 
the GAC first and loading it from there. as opposed to the start-up 
folder where the DLL is located.  Why is this looking for the DLL in the 
GAC first?  This is not normal windows behavior.  I then uninstalled the 
SQLite setup bundle and ran the program again and according to the 
utility, it fails finding the DLL in the GAC and loads it from the 
start-up folder.  This is the behavior on both Windows 7 and 8.


On windows 10, which is a basic install of the preview, one for 64 bit 
and one for 32 bit, and without any development tools installed, or the 
SQLite setup bundle installed, it failed to find 
system.data.sqlite.dll.  The first thing I did on my 64 bit was to 
install the x86 setup bundle.  Now the program runs and loads the dll 
from the GAC.  I then uninstalled the setup bundle and tried the program 
again, and I'll be damned it now runs and find the DLL in the launch 
folder.  Why is that???

Tried the program on a fresh install of the 32 bit win10 and it failed, 
unable to located the DLL.  I loaded up the SDKs' so I could get the 
fuslogvw utility.  With the utility running ran the program and It now 
runs and find the dll in the executable folder.  What's up with this???

I suspect this might be a Windows 10 issues, but why is it looking for 
the DLL in the GAC first and not in the executable folder which has been 
the behavior of windows for the last 20 some odd years I have been 
working with this pig.

Jeff K. Steinkamp (N7YG)
Tucson, AZ
Scud Missile Coordinates
N32.2319 W110.8477

On 3/18/2015 13:09, Nicholas Smit wrote:
> fuslogvw (
> https://msdn.microsoft.com/en-us/library/e74a18c4%28v=vs.110%29.aspx )
> might help you understand the DLL binding issue.
>
> On 18 March 2015 at 20:04, Jeff Steinkamp  wrote:
>
>> One of my users reported that he was unable to get a piece of software
>> that uses this .NET assembly (x86 version 1.0.9.6) to run on windows 10.
>> It is throwing an error saying that it is unable to located
>> system.data.sqlite.dll even though it is included in the same folder as the
>> program executable.
>>
>> This software is compiled as x86 because of some other included assemblies
>> are complied as x86.  This has been running just fine on both Win7 and Win8
>> both 32 and 64 bits this way for quite some time.
>>
>> So, I setup a 64 bit Win10 technical preview in a VM and tried it myself
>> and I got the same error.  I tired forcing the loading of the assembly, but
>> it would puke all over itself with the "your application quit and windows
>> will get back with you when hell freezes over"
>>
>> So I loaded up the SQLite setup bundle for both the X86 and X64 and had
>> them install into the GAC (Global Assembly Cache) and now the software
>> works.  So this is telling me that Windows 10 is only looking in the GAC
>> for this assembly.  Any idea why this might be as I have two other .NET
>> assemblies that are used in this software that it does not have trouble
>> finding and they are not installed in the GAC?
>>
>> I suspect this may be a Mico$oft Issue, but wanted to check here first to
>> make sure I have not got something setup incorrectly.
>>
>> --
>> Jeff K. Steinkamp (N7YG)
>> Tucson, AZ
>> Scud Missile Coordinates
>> N32.2319 W110.8477
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] Source code for 3.8.7.4

[Saurav Sarkar]

Hi Richard,

Thanks for the info.
I got this link from the site for 3.8.7.4
http://www.sqlite.org/cgi/src/info/f66f7a17b78ba617

Hope this is fine.

I want this version because my project is using this release.

Best Regards,
Saurav

On Thu, Mar 19, 2015 at 8:38 PM, Richard Hipp  wrote:

> On 3/19/15, Saurav Sarkar  wrote:
> > Hi All,
> >
> > Can anyone please tell me how can i get the source code for 3.8.7.4 ?
> > In the website of SQLite i could get hold of a amalgamation source code
> of
> > latest release 3.8.8.3 in a zip file.
> >
> > Can i similarly get for 3.8.7.4 ?
> >
>
> Over 14,400 different historical versions of SQLite are available at
> https://www.sqlite.org/src/
>
> Why do you want 3.8.7.4 instead of 3.8.8.3?
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Source code for 3.8.7.4

[Saurav Sarkar]

Hi All,

Can anyone please tell me how can i get the source code for 3.8.7.4 ?
In the website of SQLite i could get hold of a amalgamation source code of
latest release 3.8.8.3 in a zip file.

Can i similarly get for 3.8.7.4 ?

thanks and Regards,
Saurav

[sqlite] What is the right order of the main SQLite functions?

[Igor Tandetnik]

On 3/19/2015 7:48 PM, Bart Smissaert wrote:
> I know this is basic and should be in the documentation, but it is not
> quite clear to me.
> Basically I have 3 type of procedures:
>
> 1. Getting values from a table. For this I have the following steps:
>
> (Open)
> Prepare
>
> Then in a loop:
>
> Step
> ColumnInt, ColumnDouble, ColumnText
>
> After the loop:
>
> Finalize

Looks good to me.

> 2. Putting values in a table:
>
> (Open)
> Start Transaction
> Prepare

These two could be in any order

> Then in a loop:
> Bind
> Step
> Reset
>
> After the loop:
>
> Clearbindings
> Finalize
> Commit Transaction

It's pointless to clear bindings right before finalize. And commit could 
happen either before or after.

> 3. Non looped procedures, eg, delete and update:
>
> (Open)
> Prepare
> Step
> Reset
> Finalize

You don't need reset before finalize.

> Is this all how it should be?

It'll work. There are a few redundancies, but they are harmless.

> Where/when should I call sqlite3_close?

When you no longer need the connection, of course.

> Does Clearbindings come indeed before finalize?

I have yet to find a reason to call sqlite3_clear_bindings. It's needed 
very rarely, if ever. It's absolutely pointless right before finalize.
-- 
Igor Tandetnik

[sqlite] Why is empty string not equal to empty blob?

[R.Smith]

On 2015-03-19 05:32 PM, Paul wrote:
>>On 19 Mar 2015, at 3:19pm, Paul  wrote:
>>
>>> This may cause very nasty hard-to-find bugs, since SQLite allows to store 
>>> any content inside BLOB field:
>>>
>>> sqlite> create table foo(a int, b int, primary key(a, b));
>>> sqlite> insert into foo(a, b) VALUES(1, ''), (1, x'');
>>> sqlite> select *, length(b) from foo;
>> True, but the content which is stored is not of BLOB type unless you stored 
>> a BLOB.  Do
>>
>> select *, typeof(b), length(b) from foo;
>>
>> and you'll find that you get out what you put in.
>>
> Yeah, that's true. But still, this can cause nasty bugs.
> It feels unnatural, because there is implicit conversion from string to int:

I hear what you are saying Paul, but there is no comparison where 
different types (especially involving blobs) can ever be confused in a 
way that one iteration will survive a Uniqueness test and another won't.

At least, I thought there wouldn't be, but from your post I decided to 
check anyway, trying all different kinds of ways to "trick" SQLite into 
duplicating a key by using casts and the like.

One of my test scripts attached hereunder, but as of now I am 
unsuccessful. Someone else might be able to find a hole, but I couldn't 
break it in any way - thus no reason yet to think any kind of bug 
exists. The question remains as to whether one may regard an empty blob 
to be equal to zero or not, or indeed an empty string. I don't think so, 
but other opinions may exist.


   -- 

CREATE TABLE foo(a int, b int, PRIMARY KEY(a, b));
INSERT INTO foo(a, b) VALUES(1, ''), (1, x'');
INSERT INTO foo(a, b) VALUES(2, 255), (2, x'FF');
INSERT INTO foo(a, b) VALUES(3, CAST(x'7F' AS INT)), (3, x'7F');

SELECT T1.*, length(T1.b) AS L1, T2.*, length(T2.b) AS L2
   FROM foo AS T1
   JOIN foo AS T2 ON T1.b=T2.b
WHERE 1;

   -- a  b  L1   a  b   L2
   -- -  -  --   -  -   --
   -- 1 01  0
   -- 1 01  0
   -- 2  25532  255 3
   -- 2  0xFF   12  0xFF1
   -- 3  0  13  0   1
   -- 3  0x7F   13  0x7F1

   --Item Stats:  Item No:   5 Query Size 
(Chars):  118
   -- Result Columns:6 Result Rows: 6
   -- VM Work Steps: 154   Rows 
Modified:   0
   -- Full Query Time:   -- --- --- --- --.
   -- Query Result:  Success.
   -- 


SELECT T1.*, length(T1.b) AS L1, T2.*, length(T2.b) AS L2
   FROM foo AS T1
   JOIN foo AS T2 ON CAST(T1.b AS INT)=CAST(T2.b AS INT)
WHERE 1;

   -- ab L1   a   b  L2
   -- -- --   -   -  --
   -- 1  01  0
   -- 1  01  0
   -- 1  02   0xFF   1
   -- 1  03   0  1
   -- 1  03   0x7F   1
   -- 1  01  0
   -- 1  01  0
   -- 1  02   0xFF   1
   -- 1  03   0  1
   -- 1  03   0x7F   1
   -- 2255   32   2553
   -- 20xFF  11  0
   -- 20xFF  11  0
   -- 20xFF  12   0xFF   1
   -- 20xFF  13   0  1
   -- 20xFF  13   0x7F   1
   -- 30 11  0
   -- 30 11  0
   -- 30 12   0xFF   1
   -- 30 13   0  1
   -- 30 13   0x7F   1
   -- 30x7F  11  0
   -- 30x7F  11  0
   -- 30x7F  12   0xFF   1
   -- 30x7F  13   0  1
   -- 30x7F  13   0x7F   1

   --Item Stats:  Item No:   6 Query Size 
(Chars):  144
   -- Result Columns:6 Result Rows: 26
   -- VM Work Steps: 472   Rows 
Modified:   0
   -- Full Query Time:   -- --- --- --- --.
   -- Query Result:  Success.
   -- 


UPDATE foo SET b=CAST(b AS INT) WHERE 1;
   -- 2015-03-19 18:25:18.614  |  [ERROR]  UNIQUE constraint failed: 
foo.a, foo.b
   --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.031s
   -- Total Script Query Time: 0d 00h 00m and 
00.004s
   -- Total Database Rows Changed: 6
   -- Total Virtual-Machine Steps: 851
   -- Last executed Item Index:7
   -- Last Script Error: Script Failed in Item 6: UNIQUE 
constraint failed: foo.a, foo.b
   -- 


   -- 2015-03-19 18:25:18.615  |  [Info]   Script failed - Rolling 
back...
   -- 2015-03-19 

[sqlite] Why is empty string not equal to empty blob?

[Paul]

>   On 19 Mar 2015, at 3:19pm, Paul  wrote:
> 
> > This may cause very nasty hard-to-find bugs, since SQLite allows to store 
> > any content inside BLOB field:
> > 
> > sqlite> create table foo(a int, b int, primary key(a, b));
> > sqlite> insert into foo(a, b) VALUES(1, ''), (1, x'');
> > sqlite> select *, length(b) from foo;
> 
> True, but the content which is stored is not of BLOB type unless you stored a 
> BLOB.  Do
> 
> select *, typeof(b), length(b) from foo;
> 
> and you'll find that you get out what you put in.
> 

Yeah, that's true. But still, this can cause nasty bugs.
It feels unnatural, because there is implicit conversion from string to int:

sqlite> select * from foo where a = '1';
a   b 
--  --
1 
1
sqlite> select * from foo where a = '1.0';
a   b 
--  --
1 
1 
sqlite> select * from foo where a = 1.01;
a   b 
--  --
1 
1 

yet not to BLOB.

[sqlite] Why is empty string not equal to empty blob?

[Paul]

>   On 3/19/15, Paul  wrote:
> > Maybe this question was already asked and explained.
> > Or maybe it is documented somewhere (could not fiund it).
> > Sorry, if this is the case, but why does
> >
> > SELECT '' = x'';
> >
> > yields 0?
> >
> 
> Because it has never before occurred to the developers that somebody
> would compare a String to a Blob an expect them to be equal to one
> another.

This may cause very nasty hard-to-find bugs, since SQLite allows to store any 
content inside BLOB field:

sqlite> create table foo(a int, b int, primary key(a, b));
sqlite> insert into foo(a, b) VALUES(1, ''), (1, x'');
sqlite> select *, length(b) from foo;
a   b   length(b) 
--  --  --
1   0 
1   0

And now, using sqlite3_column_blob() + sqlite3_column_bytes() I 'see' two empty 
blobs, kind of...

[sqlite] Why is empty string not equal to empty blob?

[Paul]

Maybe this question was already asked and explained.
Or maybe it is documented somewhere (could not fiund it).
Sorry, if this is the case, but why does

SELECT '' = x'';

yields 0?

Suppose I have data in FOO table. By what means can I tell 
what is inside of my_blob column of FOO, when

SELECT length(my_blob) FROM FOO;

returns 0 for both empty string('') and empty blob (x'') alike? 
And what is the reason behind this difference?

Thanks,
Paul

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Dan Kennedy]

On 03/18/2015 02:39 AM, Bart Smissaert wrote:
> OK, let me try to explain:
>
> Simple table with primary integer key, call the field ID
>
> strSQL = "Insert into table1 values(?)"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
>
> strSQL = "BEGIN TRANSACTION"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
> lReturnValue = sqlite3_step(lStatementHandle)
> lReturnValue = sqlite3_finalize(lStatementHandle)
>
> In a loop, looping through rows of a VBA variant array:
>
> sqlite3_bind_int lStatementHandle, 1, vArray(r)
> sqlite3_step lStatementHandle
> sqlite3_reset lStatementHandle
>
> After this loop:
>
> lReturnValue = sqlite3_finalize(lStatementHandle)   < unexpected
> lReturnValue here
>
> strSQL = "COMMIT TRANSACTION"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
> lReturnValue = sqlite3_step(lStatementHandle)
> lReturnValue = sqlite3_finalize(lStatementHandle)
>
>
>
> That is it.
> The result is fine and no duplicates are inserted, only thing wrong is the
> result the one from last sqlite3_finalize.
> Note that I don't check the return values in the loop, maybe I should, but
> it saves some time.

The last or second last sqlite3_finalize()?

If an error occurs in sqlite3_step(), then the following sqlite3_reset() 
or sqlite3_finalize() also returns an error. So you can write code like 
this:

   while( SQLITE_ROW==sqlite3_step(pStmt) ){
 // process row
   }
   rc = sqlite3_reset(pStmt);

and you are sure to catch any error. Replacing the sqlite3_reset() with 
finalize() would also catch any error code.

However, it is only the first sqlite3_reset() or sqlite3_finalize() 
following the error that also returns an error code. After that call has 
returned, the statement is back in "initial" state, so reset() or 
finalize() returns SQLITE_OK. So, if I do this:

   sqlite3_step(pStmt);
   rc = sqlite3_reset(pStmt);
   rc2 = sqlite3_finalize(pStmt);

and an error occurs in sqlite3_step(), then "rc" will be set to an error 
code, but "rc2" to SQLITE_OK. As by the time sqlite3_finalize() is 
called the statement is already back in "initial - no error has 
occurred" state.

Does that explain what you're seeing?

Dan.





>
>
> RBS
>
>
> On Tue, Mar 17, 2015 at 6:33 PM, Richard Hipp  wrote:
>
>> On 3/17/15, Bart Smissaert  wrote:
>>> Have a simple table with a primary integer key.
>>> Doing an insert (with no insert or ignore or insert or replace) with
>>> duplicate values
>>> for this primary integer key field produces zero on sqlite3_finalize, but
>>> 19 from sqlite3_errorcode.
>> I wrote a test program for this and I get 19 in both cases.  Maybe you
>> could share your test case with us?
>>
>>> I thought that the result value of sqlite3_finalize also should produce a
>>> non-zero value.
>>> This is with the latest version.
>>> Anything I am overlooking here or misunderstanding?
>>>
>>> RBS
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is empty string not equal to empty blob?

[Simon Slavin]

On 19 Mar 2015, at 3:19pm, Paul  wrote:

> This may cause very nasty hard-to-find bugs, since SQLite allows to store any 
> content inside BLOB field:
> 
> sqlite> create table foo(a int, b int, primary key(a, b));
> sqlite> insert into foo(a, b) VALUES(1, ''), (1, x'');
> sqlite> select *, length(b) from foo;

True, but the content which is stored is not of BLOB type unless you stored a 
BLOB.  Do

select *, typeof(b), length(b) from foo;

and you'll find that you get out what you put in.

Simon.

[sqlite] Why is empty string not equal to empty blob?

[Simon Slavin]

On 19 Mar 2015, at 2:56pm, Paul  wrote:

> Maybe this question was already asked and explained.
> Or maybe it is documented somewhere (could not fiund it).
> Sorry, if this is the case, but why does
> 
> SELECT '' = x'';
> 
> yields 0?

One is a string.  The other is a BLOB.  SQLite doesn't even get as far as 
testing the contents, it knows they are of different types.

Although the two types have many things in common and can be treated the same 
for many operations, they're different.  Like INTEGER and REAL.  Though INTEGER 
and REAL fail the '=' test you use above, which I accept makes things confusing.

> Suppose I have data in FOO table. By what means can I tell 
> what is inside of my_blob column of FOO, when
> 
> SELECT length(my_blob) FROM FOO;
> 
> returns 0 for both empty string('') and empty blob (x'') alike? 

SELECT typeof(my_blob),length(my_blob) FROM FOO;

> And what is the reason behind this difference?

Harder for me to answer unless I know why you think they should be the same.

Simon.

[sqlite] Sqlite3 Bug Found

[Simon Davies]

On 6 March 2015 at 08:38, Carabas, Costin  wrote:
> Hello,
>
> I was working on version 3.8.8.2 of sqlite3 and I found a bug that is trace 
> related. It can be reproduced in 3 steps:
>
> 1.   ".t log" - opens log for tracing
>
> 2.   ".t" - no argument. Closes the file descriptor that was previously 
> opened (Problem)

No - it reports the expected usage - see below

> 3.   ".d" - dumps into the file that was previously closed. = > 
> Segmentation Fault
>
> Where:
> .t = .trace
> .d = .debug

I think you mean .dump here

> Best Regards,
> Costin

Works for me:

C:\sqlite>sqlite3_3.8.8.2 new.db
SQLite version 3.8.8.2 2015-01-30 14:30:45
Enter ".help" for usage hints.
sqlite> .t log
sqlite> .t
Usage: .trace FILE|off
sqlite> .d
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> .q


On 19 March 2015 at 12:52, Carabas, Costin  wrote:
> This bug reproduces to the latest Sqlite release (3.8.8.3).
>
> The log from the bug:
> sqlite> .t file
> sqlite> .t
> Usage: .trace FILE|off
> sqlite> .d
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> Segmentation fault
>
> I also found another bug with the same root cause:
> sqlite> .t file
> sqlite> .t
> Usage: .trace FILE|off
> sqlite> .t
> *** Error in `.libs/lt-sqlite3': double free or corruption (top): 
> 0x024ab340 ***
> Aborted

Still works for me:

C:\sqlite>sqlite3_3.8.8.3 new.db
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite>
sqlite> .t log
sqlite> .t
Usage: .trace FILE|off
sqlite> .t
Usage: .trace FILE|off
sqlite>
sqlite> .q

These executables were Win32 downloaded from
https://www.sqlite.org/download.html:
sqlite-shell-win32-x86-3080802.zip
sqlite-shell-win32-x86-3080803.zip

How are you getting your executables?

Regards,
Simon

[sqlite] It might be nice to have sqlite3_attach() and sqlite3_detach().

[Scott Hess]

On Thu, Mar 19, 2015 at 10:30 AM, Richard Hipp  wrote:
> On 3/18/15, Scott Hess  wrote:
>> I'm thinking I could use something like:
>>
>> SQLITE_API int sqlite3_attach(sqlite3* db, const char* zPath, const
>> char* dbname);
>> SQLITE_API int sqlite3_detach(sqlite3* db, const char* dbname);
>>
>> Right now, I have a helper in Chromium which does "ATTACH DATABASE ?
>> AS ?".  This works, but AFAICT this page:
>>https://www.sqlite.org/lang_keywords.html
>> implies that the dbname _probably_ should not be a string literal but
>> instead an identifier.  So it should be "dbname" with embedded "
>> characters doubled (nobody should ever do that, but *shrug*).
>
> Though undocumented, the arguments to ATTACH and DETACH can be
> arbitrary expressions.  For example:
>
>  ATTACH 'xyzzy' || '.txt' AS printf('abc-%d',12345');
>
> So bound parameters are also supported.  We have test cases that
> verify this behavior.

Indeed, that's what I've found, but from the docs it's not entirely
clear what the documented way to construct it would be.  Chromium uses
bind parameters and it works and it has a unit test (well, I hope I
wrote one).  Mostly I added the helper to prevent people from
constructing using incorrect quoting.

>> WDYT?  Mostly this just came up because I saw someone writing code
>> which constructed things sprintf-style, without quoting, which was
>> clearly error-prone.  But in describing the right way to do it, I had
>> to do notable hand-waving.
>
> Seems like an implementation of your requested functions would be fairly 
> simple:
>
> int sqlite3_attach(sqlite3* db, const char* zPath, const char* dbname){
>char *zSql = sqlite3_mprintf("ATTACH %Q AS %Q", zPath, dbname);
>int rc = sqlite3_exec(db, zSql, 0, 0, 0);
>sqlite3_free(zSql);
>return rc;
> }
> int sqlite3_detach(sqlite3* db, const char* dbname){
>char *zSql = sqlite3_mprintf("DETACH %Q", dbname);
>int rc = sqlite3_exec(db, zSql, 0, 0, 0);
>sqlite3_free(zSql);
>return rc;
> }

'%q' seems slightly better because NULL is oddly handled in this case.
"ATTACH '/tmp/db' AS NULL" doesn't seem to throw an error, but it also
doesn't really work.  "ATTACH '/tmp/db' AS ''" (that's a single-quoted
empty string before double quotes) throws "data is already in use",
which I'm not sure how to interpret, but at least it's an error.

I will admit, this is a _very_ weak feature request.  Mostly it seems
like it's on the boundary between "Things to do using SQL code" and
"Things to do using SQLite API".  The main flaw of having a simple
wrapper as described above is that if you want to forbid attach/detach
for customer SQL code by using an authorizer, but you need those
functions in your implementation code, you have to add some support
for temporarily authorizing it.  You probably want that kind of
functionality for PRAGMA anyhow, so maybe not a crushing problem.

-scott

[sqlite] It might be nice to have sqlite3_attach() and sqlite3_detach().

[Richard Hipp]

On 3/18/15, Scott Hess  wrote:
> I'm thinking I could use something like:
>
> SQLITE_API int sqlite3_attach(sqlite3* db, const char* zPath, const
> char* dbname);
> SQLITE_API int sqlite3_detach(sqlite3* db, const char* dbname);
>
> Right now, I have a helper in Chromium which does "ATTACH DATABASE ?
> AS ?".  This works, but AFAICT this page:
>https://www.sqlite.org/lang_keywords.html
> implies that the dbname _probably_ should not be a string literal but
> instead an identifier.  So it should be "dbname" with embedded "
> characters doubled (nobody should ever do that, but *shrug*).

Though undocumented, the arguments to ATTACH and DETACH can be
arbitrary expressions.  For example:

 ATTACH 'xyzzy' || '.txt' AS printf('abc-%d',12345');

So bound parameters are also supported.  We have test cases that
verify this behavior.

>
> WDYT?  Mostly this just came up because I saw someone writing code
> which constructed things sprintf-style, without quoting, which was
> clearly error-prone.  But in describing the right way to do it, I had
> to do notable hand-waving.
>

Seems like an implementation of your requested functions would be fairly simple:

int sqlite3_attach(sqlite3* db, const char* zPath, const char* dbname){
   char *zSql = sqlite3_mprintf("ATTACH %Q AS %Q", zPath, dbname);
   int rc = sqlite3_exec(db, zSql, 0, 0, 0);
   sqlite3_free(zSql);
   return rc;
}
int sqlite3_detach(sqlite3* db, const char* dbname){
   char *zSql = sqlite3_mprintf("DETACH %Q", dbname);
   int rc = sqlite3_exec(db, zSql, 0, 0, 0);
   sqlite3_free(zSql);
   return rc;
}

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Fwd: Building A Windows DLL - missing exports

[Ewald Einwanger]

Hallo,

I had a little hint for "Building A Windows DLL".

Best Regards

Ewald Einwanger


 Weitergeleitete Nachricht 
Betreff:Building A Windows DLL - missing exports
Datum:  Thu, 19 Mar 2015 13:24:18 +0100
Von:Ewald Einwanger 
An: sqlite-users at sqlite.org



Hallo,

on
http://www.sqlite.org/howtocompile.html

you stated:


"Building A Windows DLL

To build a DLL of SQLite for use in Windows, first acquire the 
appropriate amalgamated source code files, sqlite3.c and sqlite3.h. 
These can either be downloaded from the SQLite website 
 or custom generated from sources 
as shown above.

With source code files in the working directory, a DLL can be generated 
using MSVC with the following command:

cl sqlite3.c -link -dll -out:sqlite3.dll

..."

It took me a few hours to get the right setting to get the DLL exports:

cl -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll

Would be good if you can update your documentation.

Best Regards

Ewald Einwanger

[sqlite] Sqlite3 Bug Found

[Carabas, Costin]

This bug reproduces to the latest Sqlite release (3.8.8.3).

The log from the bug:
sqlite> .t file
sqlite> .t
Usage: .trace FILE|off
sqlite> .d
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
Segmentation fault

I also found another bug with the same root cause:
sqlite> .t file
sqlite> .t
Usage: .trace FILE|off
sqlite> .t
*** Error in `.libs/lt-sqlite3': double free or corruption (top): 
0x024ab340 ***
Aborted



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Carabas, 
Costin
Sent: Friday, March 6, 2015 10:39 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] Sqlite3 Bug Found

Hello,

I was working on version 3.8.8.2 of sqlite3 and I found a bug that is trace 
related. It can be reproduced in 3 steps:


1.   ".t log" - opens log for tracing

2.   ".t" - no argument. Closes the file descriptor that was previously 
opened (Problem)

3.   ".d" - dumps into the file that was previously closed. = > 
Segmentation Fault

Where:
.t = .trace
.d = .debug


Best Regards,
Costin
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .import command

[Leonardo Alexandre Ferreira Leite]

Hi,

I would like to continue a discussion started here:
https://www.sqlite.org/src/tktview/c25aab7e7ea55c861313

First I assume the sqlite3 shell still has the related problem (I'm using 
Ubuntu, so not sure if the last version has fixed it).

I would like to desagree of a comment posted on the ticket:

>? Different programs do different
things.  There might be users that depend on the current behavior.  If we
fix this for the OP, it could cause problems for others.  So it is unclear
whether or not we should do anything for this.

In the way sqlite shell currently works, users have to import a very strict 
formatted CSV file: no comma inside quoted string values. Implementing a way to 
allow commas to come inside quoted string values by no means will affect the 
behavior of current users relying on .import. This would be backward compatible 
evolution. Does anyone has an counter example?

Finally, I'm not sure if the contribution sent on the last post was accepted on 
sqlite. Was it accepted? Does it solved the problem?

Thank you so much for your attention,
Leonardo Leite



-


"Esta mensagem do SERVI?O FEDERAL DE PROCESSAMENTO DE DADOS (SERPRO), empresa 
p?blica federal regida pelo disposto na Lei Federal n? 5.615, ? enviada 
exclusivamente a seu destinat?rio e pode conter informa??es confidenciais, 
protegidas por sigilo profissional. Sua utiliza??o desautorizada ? ilegal e 
sujeita o infrator ?s penas da lei. Se voc? a recebeu indevidamente, queira, 
por gentileza, reenvi?-la ao emitente, esclarecendo o equ?voco."

"This message from SERVI?O FEDERAL DE PROCESSAMENTO DE DADOS (SERPRO) -- a 
government company established under Brazilian law (5.615/70) -- is directed 
exclusively to its addressee and may contain confidential data, protected under 
professional secrecy rules. Its unauthorized use is illegal and may subject the 
transgressor to the law's penalties. If you're not the addressee, please send 
it back, elucidating the failure."

[sqlite] Why is empty string not equal to empty blob?

[Adam Devita]

As a general rule of thumb, if things are different type, they can't be equal.

One already knows the column type. To compare apples to apples, one
would cast to convert them.

sqlite> SELECT '' = x'';
0
sqlite> SELECT cast('' as blob) = x'';
1

Perhaps some confusion comes from how numbers are stored and compared?


On Thu, Mar 19, 2015 at 11:19 AM, Paul  wrote:
>>   On 3/19/15, Paul  wrote:
>> > Maybe this question was already asked and explained.
>> > Or maybe it is documented somewhere (could not fiund it).
>> > Sorry, if this is the case, but why does
>> >
>> > SELECT '' = x'';
>> >
>> > yields 0?
>> >
>>
>> Because it has never before occurred to the developers that somebody
>> would compare a String to a Blob an expect them to be equal to one
>> another.
>
> This may cause very nasty hard-to-find bugs, since SQLite allows to store any 
> content inside BLOB field:
>
> sqlite> create table foo(a int, b int, primary key(a, b));
> sqlite> insert into foo(a, b) VALUES(1, ''), (1, x'');
> sqlite> select *, length(b) from foo;
> a   b   length(b)
> --  --  --
> 1   0
> 1   0
>
> And now, using sqlite3_column_blob() + sqlite3_column_bytes() I 'see' two 
> empty blobs, kind of...
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Why is empty string not equal to empty blob?

[Richard Hipp]

On 3/19/15, Paul  wrote:
> Maybe this question was already asked and explained.
> Or maybe it is documented somewhere (could not fiund it).
> Sorry, if this is the case, but why does
>
> SELECT '' = x'';
>
> yields 0?
>

Because it has never before occurred to the developers that somebody
would compare a String to a Blob an expect them to be equal to one
another.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Source code for 3.8.7.4

[Richard Hipp]

On 3/19/15, Saurav Sarkar  wrote:
> Hi All,
>
> Can anyone please tell me how can i get the source code for 3.8.7.4 ?
> In the website of SQLite i could get hold of a amalgamation source code of
> latest release 3.8.8.3 in a zip file.
>
> Can i similarly get for 3.8.7.4 ?
>

Over 14,400 different historical versions of SQLite are available at
https://www.sqlite.org/src/

Why do you want 3.8.7.4 instead of 3.8.8.3?
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] how to determine the status of last statement in sqlite3 shell ?

[Simon Slavin]

On 19 Mar 2015, at 12:29am, Keith Medcalf  wrote:

> A statement may fail, however that does not affect other statements within 
> the transaction.  You still have to end a transaction with either a commit 
> (to commit the changes made by statements WHICH DID NOT FAIL) or rollback to 
> discard the changes made by the statements which did not fail.

Then what is the BEGIN for ?

Hmm.



"a transaction will also ROLLBACK if the database is closed or if an error 
occurs and the ROLLBACK conflict resolution algorithm is specified."

"If certain kinds of errors occur within a transaction, the transaction may or 
may not be rolled back automatically."



"The ON CONFLICT clause is not a separate SQL command. It is a non-standard 
clause that can appear in many other SQL commands. It is given its own section 
in this document because it is not part of standard SQL"

Oh dear.  It's non-standard SQL.  And the answer is that it depends.

The default behaviour is as you wrote, however.  So I was wrong.  Thanks for 
the correction.

Simon.

[sqlite] bug in transactions implementation ?

[R.Smith]

Pardon the long post everyone, but it seems from the other posts there 
is a large misconception to address here and I will attempt to do so as 
briefly as possible (Also, feel free to chime in where I am vague or wrong):

On 2015-03-18 11:24 PM, Jason Vas Dias wrote:
> OK, I discovered the OR clause of the INSERT statement, so I'm trying:
>
> BEGIN TRANSACTION;
> INSERT OR ROLLBACK INTO db VALUES("This breaks a constraint");
> COMMIT;
>
> But now I get another error after the constraint violation :
>'Error: cannot commit - no transaction is active'

This is exactly what should happen and exactly what you've asked for. 
You started a transaction, instructed it to INSERT some values but 
roll-back the transaction if the insert fails. So then the insert fails 
and your transaction got rolled back... and then you told it to commit, 
which can't possibly work because the transaction that was is now no 
more, it got rolled back - remember?

SQL is not a programming language, it doesn't execute lines of code. It 
can follow DATA instructions and you may string those together in a list 
(as you do) for easier usage. If a transaction fails however, this is a 
problem, not an opportunity for more coding, one typically needs to read 
that error code in your program/connector/ODBC/list processor/whatever 
and make a decision on what to do next (such as roll back and re-start 
the transaction or do something else etc.) and then ask SQL to do it 
based on your decision. SQL itself is not a decision-making program, 
it's just a Database engine which tries to do stuff with Data that you 
ask it to, and it will error out very unceremoniously if what you ask is 
bogus.

Your job is to see what went wrong, fix it so that next time you ask it 
to do the thing, it doesn't fail (i.e. you should have a mechanism in 
place to make sure you are not asking it to insert duplicates, and if 
this error still happens, you need to fix your mechanism). There is no 
way to ask via SQL only to try this or that, and if it fails, maybe try 
something else please - this is the decision-making domain of programs. 
(btw: This is true for ALL SQL engines, not just SQLite)

You can however tell it to ignore duplicated requests, such as:
INSERT OR IGNORE INTO "t" (myVal) VALUES ('This is a duplicate');

Which will simply NOT insert the value when the duplication occurs and 
continue without rolling back or moaning about it. You have to be sure 
this is what you want to happen though. If you need it to error out, 
then it will, but you can't ask it to pick another rabbit hole to go 
down whence an error might have occurred.


> This seems very buggy to me.

That's ok - It only seems that way. SQLite is amongst the least-buggy of 
the SQL engines (though not free of it - but the above is not a bug in 
any way).

Also, Feel free to post questions - if you can say exactly what you want 
to happen, lots of people here will gladly help you achieve that in the 
best possible way or offer methods to get to the goal in more and better 
ways than ever seen before.  However, calling "bug" due to 
misunderstanding will probably have the opposite effect.

Best of luck !
Ryan