Quantcast

Producing SQLITE_MISMATCH

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Producing SQLITE_MISMATCH

J. King-3
I'm trying to write a minimal test case to produce an SQLITE_MISMATCH
response.  The documentation suggests the following should be
sufficient:

CREATE TABLE test(id integer primary key) WITHOUT ROWID;
INSERT INTO test(id) VALUES('ook');

However, SQLite happily accepts the second statement. Is there some
trick to it that's not obvious?

--
J. King

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Producing SQLITE_MISMATCH

Dan Kennedy-4
On 03/09/2017 09:23 PM, J. King wrote:
> I'm trying to write a minimal test case to produce an SQLITE_MISMATCH
> response.  The documentation suggests the following should be sufficient:
>
> CREATE TABLE test(id integer primary key) WITHOUT ROWID;
> INSERT INTO test(id) VALUES('ook');
>
> However, SQLite happily accepts the second statement. Is there some
> trick to it that's not obvious?

Leave out the WITHOUT ROWID.

Dan.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Producing SQLITE_MISMATCH

J. King-3
On 2017-03-09 09:35:00, "Dan Kennedy" <[hidden email]> wrote:

>On 03/09/2017 09:23 PM, J. King wrote:
>>I'm trying to write a minimal test case to produce an SQLITE_MISMATCH
>>response.  The documentation suggests the following should be
>>sufficient:
>>
>>CREATE TABLE test(id integer primary key) WITHOUT ROWID;
>>INSERT INTO test(id) VALUES('ook');
>>
>>However, SQLite happily accepts the second statement. Is there some
>>trick to it that's not obvious?
>
>Leave out the WITHOUT ROWID.
>
>Dan.

Ah, exactly what I needed.  Thanks!

Out of curiosity, can you provide some insight as to why it does not
produce a mismatch for a WITHOUT ROWID table?

(apologies if my messages come out looking yucky; I'm still looking a
mail client that isn't awful dealing with mailing lists. :( )

--
J. King

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Producing SQLITE_MISMATCH

Dan Kennedy-4
On 03/09/2017 09:39 PM, J. King wrote:

> On 2017-03-09 09:35:00, "Dan Kennedy" <[hidden email]> wrote:
>
>> On 03/09/2017 09:23 PM, J. King wrote:
>>> I'm trying to write a minimal test case to produce an
>>> SQLITE_MISMATCH response.  The documentation suggests the following
>>> should be sufficient:
>>>
>>> CREATE TABLE test(id integer primary key) WITHOUT ROWID;
>>> INSERT INTO test(id) VALUES('ook');
>>>
>>> However, SQLite happily accepts the second statement. Is there some
>>> trick to it that's not obvious?
>>
>> Leave out the WITHOUT ROWID.
>>
>> Dan.
>
> Ah, exactly what I needed.  Thanks!
>
> Out of curiosity, can you provide some insight as to why it does not
> produce a mismatch for a WITHOUT ROWID table?

The main b-tree in which data for a "rowid table" - any table that is
not a WITHOUT ROWID table or a virtual table - is stored uses 64-bit
integers for keys. If you declare a column "INTEGER PRIMARY KEY", it
becomes an alias for this underlying integer key. So in this case the
field must be set to an integer value - SQLite could not store it otherwise.

With a WITHOUT ROWID table, the main b-tree used to store data is more
flexible - it can have any type of key. So in this case there is no
restriction.

https://www.sqlite.org/fileformat.html#representation_of_sql_tables

and:

   https://www.sqlite.org/rowidtable.html
   https://www.sqlite.org/withoutrowid.html

Dan.



>
> (apologies if my messages come out looking yucky; I'm still looking a
> mail client that isn't awful dealing with mailing lists. :( )
>
> --
> J. King
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Producing SQLITE_MISMATCH

J. King-3
On 2017-03-09 09:59:25, "Dan Kennedy" <[hidden email]> wrote:

>On 03/09/2017 09:39 PM, J. King wrote:
>>Out of curiosity, can you provide some insight as to why it does not
>>produce a mismatch for a WITHOUT ROWID table?
>
>The main b-tree in which data for a "rowid table" - any table that is
>not a WITHOUT ROWID table or a virtual table - is stored uses 64-bit
>integers for keys. If you declare a column "INTEGER PRIMARY KEY", it
>becomes an alias for this underlying integer key. So in this case the
>field must be set to an integer value - SQLite could not store it
>otherwise.
>
>With a WITHOUT ROWID table, the main b-tree used to store data is more
>flexible - it can have any type of key. So in this case there is no
>restriction.
>
>https://www.sqlite.org/fileformat.html#representation_of_sql_tables
>
>and:
>
>   https://www.sqlite.org/rowidtable.html
>   https://www.sqlite.org/withoutrowid.html

Again, thank you.  I was completely misreading the documentation with
regard to WITHOUT ROWID. Now a) it makes much more sense and b) I
understand when it might actually come in handy.

--
J. King

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...