date:20190412


On Friday, 12 April, 2019 14:48, Jim Dossey  wrote:

>On Apr 12, 2019, at 3:27 PM, Keith Medcalf  wrote:

>>> To be a little more specific, the problem happens when I try to do
>>> sqlite3_bind_int() on the prepared statement using the new rowid.
>It
>>> doesn't use the rowid it uses NULL.
>>>
>>> The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>>> Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>>> SELECT command is
>>> SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>>> Which is obtained by calling sqlite3_expanded_sql().
>>
>> This does not make sense.  It indicates that you did not actually
>bind a value to the parameter in question
>>
>
>It may not make sense, but that is what happened.  I tried it
>repeatedly with different values for rowid.  In every case, if the
>rowid did not exist in the table, sqlite3_bind_int() would insert
>NULL in place of the '?' Instead of the rowid.  The rowid's I was
>using were in the range of 10 to 25, so there were no extreme values.

Interesting because it works for me.  Everytime.  And there does not even need 
to be any records in the table at all (you just need a table definition that 
allows the prepare to succeed):


#include 
#include 
#include 
#include 

int main(int argc, char **argv)
{
sqlite3 *db;
sqlite3_stmt * stmt;
char *exp;

int rc;

rc = sqlite3_open("test.db", );
if (rc != SQLITE_OK) return 1;
rc = sqlite3_prepare_v2(db, "select * from t where rowid=?;", -1, , 0);
if (rc != SQLITE_OK) return 1;
rc = sqlite3_bind_int(stmt, 1, atoi(argv[1]));
if (rc != SQLITE_OK) return 1;
exp = sqlite3_expanded_sql(stmt);
printf("%s\n", exp);
return 0;
}

>test 1
select * from t where rowid=1;

>test 42
select * from t where rowid=42;

>test 57
select * from t where rowid=57;

>test 0
select * from t where rowid=0;

>test -5
select * from t where rowid=-5;

The only way I can get "where rowid=NULL" is if I do not bind anything at all 
to parameter 1 ...

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




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

Re: [sqlite] Problem with SELECT by rowid after INSERT

On Apr 12, 2019, at 3:27 PM, Keith Medcalf  wrote:
> 
> 
>> To be a little more specific, the problem happens when I try to do
>> sqlite3_bind_int() on the prepared statement using the new rowid.  It
>> doesn't use the rowid it uses NULL.
>> 
>> The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>> Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>> SELECT command is
>> SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>> Which is obtained by calling sqlite3_expanded_sql().
> 
> This does not make sense.  It indicates that you did not actually bind a 
> value to the parameter in question
> 

It may not make sense, but that is what happened.  I tried it repeatedly with 
different values for rowid.  In every case, if the rowid did not exist in the 
table, sqlite3_bind_int() would insert NULL in place of the '?' Instead of the 
rowid.  The rowid's I was using were in the range of 10 to 25, so there were no 
extreme values.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] finalize or reset? (was Problem with SELECT by rowid after INSERT)

On 4/12/19, Keith Medcalf  wrote:
>
> On Friday, 12 April, 2019 12:36, Richard Hipp  wrote:
>
>>Perhaps the SELECT is running inside of a transaction that was
>>started
>>before you did the INSERT.  For example, perhaps you didn't
>>sqlite3_finalize() the previous SELECT, which caused it to hold the
>>read transaction open.
>
> Is it not sqlite3_reset that clears the transaction held by the VDBE code as
> part of resetting the context back to the unexecuted state and that you do
> not have to actually finalize (release) the statement

That is correct.  I wrote hastily.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

2019-04-12 Thread Leland Helgerson

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Scott Robison
Sent: Friday, April 12, 2019 2:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text

On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:

>
> Actually you would have to convert the strings to UCS-4.  UTF-16 is a 
> variable-length encoding.  An actual "unicode character" is (at this 
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>

That is some impressive compression! :)

Regardless, even if you use UCS-4, you still have the issue of combining 
characters. Unicode is complex as had been observed.
___
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] finalize or reset? (was Problem with SELECT by rowid after INSERT)

On Friday, 12 April, 2019 12:36, Richard Hipp  wrote:

>Perhaps the SELECT is running inside of a transaction that was
>started
>before you did the INSERT.  For example, perhaps you didn't
>sqlite3_finalize() the previous SELECT, which caused it to hold the
>read transaction open.

Is it not sqlite3_reset that clears the transaction held by the VDBE code as 
part of resetting the context back to the unexecuted state and that you do not 
have to actually finalize (release) the statement (that is, the VDBE program 
must be "in progress" in order to hold a transaction).  Granted, finalize does 
a reset before de-allocating the VDBE resources, but is not resetting the 
statement (or allowing it to run to completion (SQLITE_DONE)) sufficient?

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

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

Re: [sqlite] Help with sqlite3_value_text

2019-04-12 Thread Scott Robison

On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:

>
> Actually you would have to convert the strings to UCS-4.  UTF-16 is a
> variable-length encoding.  An actual "unicode character" is (at this
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>

That is some impressive compression! :)

Regardless, even if you use UCS-4, you still have the issue of combining
characters. Unicode is complex as had been observed.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

That was it.  There was another SELECT going on that had not been finalized.

Thank you Richard, Simon, and Graham for you help on this.

> On Apr 12, 2019, at 2:36 PM, Richard Hipp  wrote:
> 
> On 4/12/19, Jim Dossey  wrote:
>> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm
>> doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make
>> the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around
>> the INSERT?
> 
> Perhaps the SELECT is running inside of a transaction that was started
> before you did the INSERT.  For example, perhaps you didn't
> sqlite3_finalize() the previous SELECT, which caused it to hold the
> read transaction open.
> 
> -- 
> 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] Problem with SELECT by rowid after INSERT


On Friday, 12 April, 2019 09:40, Jim Dossey  wrote"

This does not really make a lot of sense at all for the following reasons:


>I have a table define like this:

>CREATE TABLE "sessiond" (
>"journal" VARCHAR(4) DEFAULT '' NOT NULL,
>"session" VARCHAR(16) DEFAULT '' NOT NULL,
>"pid" INTEGER DEFAULT 0 NOT NULL,
>rowid INTEGER PRIMARY KEY
>);

>In my application I open 2 connections to this table, one for reading
>and one for writing.  I do that because sometimes we do a SELECT, and
>while reading through those rows we'll UPDATE them.  With only 1
>connection the SELECT will block the UPDATE.

This (the above) is incorrect.  The single connection will be upgraded to a 
writer when the update is step'ed on that connection.  It will not block.  
Whether transmogification of the database underneath an "in progress" read is a 
good idea or not is a completely separate issue.

>I also have WAL mode turned on.

>The problem is when I do an INSERT and then try to SELECT that record
>by rowid it doesn't find it.  The INSERT is done on the write
>connection and the SELECT is done on the read connection.  However,
>if I open a new connection and do the SELECT, the new row is there.

What is the transaction state of the "read connection"?  Did you remember to 
RESET all prior statements that were "reading" from that connection?

WAL mode creates transactions so that readers are in a mode called 
REPEATABLE-READ.  They will not see any changes COMMIT'ed by other connections 
until all statements are completed and reset and the read transaction 
commit'ed.  This is so that a crapload of overlapping statements reading data 
through the same connection will always see THE SAME IDENTICAL AND UNCHANGING 
VIEW of the database until they are all RESET and FINALIZED (for implicit 
transactions, for explicit transactions you have to end the transaction as 
well, since it is the "opening" of the transaction that determines the 
REPEATABLE-READ state).

>To be a little more specific, the problem happens when I try to do
>sqlite3_bind_int() on the prepared statement using the new rowid.  It
>doesn't use the rowid it uses NULL.
>
>The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>SELECT command is
>SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>Which is obtained by calling sqlite3_expanded_sql().

This does not make sense.  It indicates that you did not actually bind a value 
to the parameter in question

>If I use an older rowid in the SELECT, it works just fine.  It just
>doesn't work when using the rowid that was just created.

Define "older rowid".  a rowid is just a number.  Therefore is impossible for a 
rowid to be new or old as all numbers have been in use for thousands of years.


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




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

Re: [sqlite] Help with sqlite3_value_text


Actually you would have to convert the strings to UCS-4.  UTF-16 is a 
variable-length encoding.  An actual "unicode character" is (at this present 
moment in time, though perhaps not tomorrow) 4 bytes (64-bits).

---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Warren Young
>Sent: Friday, 12 April, 2019 09:45
>To: SQLite mailing list
>Subject: Re: [sqlite] Help with sqlite3_value_text
>
>On Apr 12, 2019, at 8:51 AM, x  wrote:
>>
>> How do I do the same thing if the string param is a utf-8 or utf-16
>string and the SearchChar is a Unicode character?
>
>Convert the characters to 32-bit wide characters first, then iterate
>over the array of uint32_t or similar.
>
>One method is shown by the SQLite-internal function
>sqlite3Utf8Read().  It’s static in the amalgamation build, but since
>SQLite is public domain, you can just copy that function’s text out
>into your program and use it there or modify it to suit your
>purposes.
>
>Your platform libraries may have UTF-8 to UTF-32 or similar
>mechanisms.
>
>On POSIX platforms, the most common of these is iconv(3).
>
>On Windows, the legacy of UCS-2 and UTF-16 makes this difficult, but
>if you can stick to the Basic Multilingual Plane, converting UTF-8 to
>UCS-2 gives the same effect.  See MultiByteToWideChar(…, CP_UTF8, …):
>
>https://docs.microsoft.com/windows/desktop/api/stringapiset/nf-
>stringapiset-multibytetowidechar
>
>For all platforms, there’s ICU.  That’s of particular interest with
>SQLite since there’s an included ICU extension you can include to get
>more Unicode power in SQLite:
>
>   https://www.sqlite.org/compile.html#enable_icu
>
>There are complications that your questions don’t push into, but
>beware that if you start getting beyond simple “character in string”
>questions, you’ll eventually have to confront them: combining
>characters, etc.
>___
>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] Problem with SELECT by rowid after INSERT

2019-04-12 Thread Graham Holden

Friday, April 12, 2019, 7:23:31 PM, Jim Dossey  wrote:

> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction,
> then I'm doing the SELECT.  Shouldn't the INSERT do it's own COMMIT
> which should make the new row visible to the SELECT?   Should I add
> a BEGIN ... COMMIT around the INSERT?

Have you "finished" executing the SELECT statement? If you're not
using explicit BEGIN...COMMIT, then each statement runs in its own
transaction. However, the implicit transaction started with a SELECT
does not complete until either sqlite3_step() returns SQLITE_DONE or
you explicitly call sqlite3_finalize(). As I understand it, if, for
example, you only expect one row from your SELECT statement and execute
sqlite3_step() once to retrieve that row, then the SELECT statement
(and the implicit transaction it is running in) is still active. As
such, any NEW SELECT statements you prepare/step will still be in the
same transaction and so wouldn't see the result of any INSERTs.

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

Re: [sqlite] VFS implementation for SoC

Thank you.  It appears that the POSIX function fsync of the SoC has some 
problem syncing to the local spiffs, but the whole thing works for an external 
SD filesystem.

Regards
Arun

  On Fri, 12 Apr 2019 18:58:36 +0530 Richard Hipp  wrote 

 > On 4/12/19, Arun - Siara Logics (cc)  wrote:
 > > Hi,
 > >
 > > Please ignore the earlier emails.  The extended error code when trying
 > > CREATE TABLE is 1034 and when trying INSERT is 266.
 > 
 > You can now visit https://www.sqlite.org/rescode.html and search for
 > "1034" and "266" to find out what those errors mean.
 > 
 > 
 > -- 
 > 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] Problem with SELECT by rowid after INSERT

On 4/12/19, Jim Dossey  wrote:
> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm
> doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make
> the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around
> the INSERT?

Perhaps the SELECT is running inside of a transaction that was started
before you did the INSERT.  For example, perhaps you didn't
sqlite3_finalize() the previous SELECT, which caused it to hold the
read transaction open.

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

Re: [sqlite] Problem with SELECT by rowid after INSERT

I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm 
doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make 
the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around the 
INSERT?

The INSERT is done with sqlite3_exec().  Do I need to call any other functions 
after that to make the new row visible to other connections?

> On Apr 12, 2019, at 2:15 PM, Richard Hipp  wrote:
> 
> On 4/12/19, Jim Dossey  wrote:
>> 
>> The problem is when I do an INSERT and then try to SELECT that record by
>> rowid it doesn't find it.
> 
> Yes, because the SELECT is working inside a single transaction, but
> the INSERT is adding content in a separate, subsequent transaction
> which the SELECT never sees.  This is by design.
> 
> -- 
> 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] Help with sqlite3_value_text

2019-04-12 Thread Richard Damon

> On Apr 12, 2019, at 12:58 PM, x  wrote:
> 
> I’ve been asking myself if I could have done the above more efficiently as 
> sqlite’s converting the original string then I’m converting it and copying 
> it. While thinking about that I started to wonder how c++ handled utf8/16. 
> E.g. To access the i’th character does it have to rattle through all previous 
> I-1 characters to find the start of character i, how pointer arithmetic was 
> handled when pointing to utf8/16 chars etc.
> 

Basically, if you are dealing with a variable width encoding (UTF-8/UTF-16), 
then finding the nth character requires scanning the string counting beginning 
of characters. If this is an important operation, you pay the cost of 
conversion and work in UCS-4. On the other hand, UTF-8 has a lot of nice 
properties such that it can be a fairly seamless upgrade for processing plain 
ASCII text, and if reasonably efficient for typical text. (There are a number 
of complications if you try to support ALL of Unicode, like the composed 
characters, where you use several code-point together to define a single 
character), where you need to decide how you want to normalize and need some 
big character tables for the instructions of how to do this.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 4/12/19, Jim Dossey  wrote:
>
> The problem is when I do an INSERT and then try to SELECT that record by
> rowid it doesn't find it.

Yes, because the SELECT is working inside a single transaction, but
the INSERT is adding content in a separate, subsequent transaction
which the SELECT never sees.  This is by design.

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

Re: [sqlite] Problem with SELECT by rowid after INSERT

I do not have shared-cache mode enabled, or anything else that I can see, other 
than WAL mode.  I am not doing any transactions, so the INSERT and SELECT are 
self contained transactions.  This particular database file is opened using 
ATTACH rather than sqlite3_open(), and it is attached to the 2 database 
connections that I have open.

I did another test where I removed the '?' Parameter and the call to 
seqlite3_bind_int() and just created a SELECT statement with the rowid 
hard-coded in, like 'SELECT * FROM sessiond WHERE rowid=20;', and that also did 
not work.  So it isn't the sqlite3_bind_int() function that is causing the 
problem.

As as side note, apparently sqlite3_bind_int() inserts a NULL when doing 'WHERE 
rowid=?" If the rowid you are looking for doesn't exist in the table.  I tried 
several SELECTs (using the same code) with various rowid values, some existent 
and some not, and for the non existent rowid's sqlite3_bind_int() always 
inserts a NULL.

> On Apr 12, 2019, at 1:38 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 6:32pm, Jim Dossey  wrote:
> 
>> It's just when I use 2 different connections that the second connection does 
>> not see the rowid that was just added. 
> 
> Okay, I see you're using WAL mode, and two connections.  Have you set up 
> Shared-Cache Mode or anything else that might 'merge' the two connections ?
> 
> Have you defined any transactions, or are both the INSERT and the SELECT 
> creating their own transactions ?
> ___
> 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] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 6:32pm, Jim Dossey  wrote:

> It's just when I use 2 different connections that the second connection does 
> not see the rowid that was just added. 

Okay, I see you're using WAL mode, and two connections.  Have you set up 
Shared-Cache Mode or anything else that might 'merge' the two connections ?

Have you defined any transactions, or are both the INSERT and the SELECT 
creating their own transactions ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

Yes, I've done extensive debugging to make sure the rowid is valid.

I even did a test where I did the INSERT and the SELECT on the same connection 
and that works okay.  It's just when I use 2 different connections that the 
second connection does not see the rowid that was just added.  I ran the CLI on 
the database file and did a SELECT there and I know that the record exists and 
the rowid that I'm searching for does exist.  But sqlite3_bind_int() inserts a 
NULL instead of the rowid.

> On Apr 12, 2019, at 1:26 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 6:23pm, Jim Dossey  wrote:
> 
>> I did use sqlite3_last_insert_rowid() to get the last rowid. But I used 
>> sqlite3_expanded_sql to get the actual SQL statement that was processed to 
>> find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I 
>> was looking for.
> 
> Sorry I misunderstood your post.  As a debugging test, have you 
> printed/logged the value received from sqlite3_last_insert_rowid() to make 
> sure it's the expected value ?
> ___
> 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] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 6:23pm, Jim Dossey  wrote:

> I did use sqlite3_last_insert_rowid() to get the last rowid. But I used 
> sqlite3_expanded_sql to get the actual SQL statement that was processed to 
> find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I 
> was looking for.

Sorry I misunderstood your post.  As a debugging test, have you printed/logged 
the value received from sqlite3_last_insert_rowid() to make sure it's the 
expected value ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

I did use sqlite3_last_insert_rowid() to get the last rowid.  But I used 
sqlite3_expanded_sql to get the actual SQL statement that was processed to find 
out that sqlite3_bind_int() had inserted a NULL instead of the rowid I was 
looking for.

> On Apr 12, 2019, at 12:09 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 4:40pm, Jim Dossey  wrote:
> 
>> Which is obtained by calling sqlite3_expanded_sql().
> 
> Was that a copy-paste error or do you actually want
> 
> 
> ___
> 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] Help with sqlite3_value_text

2019-04-12 Thread x

Thanks for the replies. There’s plenty for me to look at there.

I’ve been in poor health the last 5 years and after almost a year’s break I’m 
trying to get back into sqlite to preserve my sanity. I’m so rusty my opening 
post is riddled with errors.

I’ve just realised that, before my break, I was dealing with this as follows

#define VALTXT(i) String((wchar_t*)sqlite3_value_text16(values[i]))

and inside the function I’d access the parameter with

String S = VALTXT(0);

The String type is an embarcadero c++ builder typedef which I think is a wide 
string. I then accessed the i’th character using S[I]. The above involves 
copying the parameter to another string.

In an old post I made on this forum someone told me I should be using 
sqlite3_value_text( as that was sqlite’s default storage and would save sqlite 
having to convert it to utf16.

I’ve been asking myself if I could have done the above more efficiently as 
sqlite’s converting the original string then I’m converting it and copying it. 
While thinking about that I started to wonder how c++ handled utf8/16. E.g. To 
access the i’th character does it have to rattle through all previous I-1 
characters to find the start of character i, how pointer arithmetic was handled 
when pointing to utf8/16 chars etc.

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

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

2019-04-12 Thread Tony Papadimitriou

True, but SQLite3 is known to provide several conveniences that are not 
necessarily standard SQL.


-Original Message- 
From: Simon Slavin

Sent: Friday, April 12, 2019 7:11 PM

On 12 Apr 2019, at 5:00pm, Tony Papadimitriou  wrote:


update t set s = replace(s, 'USA', '___'),
s = replace(s,'US','USA'),
s = replace(s,'___','USA');


To add to the answers other people gave, there's no set order for SQL to 
process these changes.  The SQL definition doesn't specify that they'll be 
done in any particular order.


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

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

On 12 Apr 2019, at 5:00pm, Tony Papadimitriou  wrote:

> update t set s = replace(s, 'USA', '___'),
> s = replace(s,'US','USA'),
> s = replace(s,'___','USA');

To add to the answers other people gave, there's no set order for SQL to 
process these changes.  The SQL definition doesn't specify that they'll be done 
in any particular order.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

2019-04-12 Thread Tony Papadimitriou

I know this, thanks.  I simply made a test case that can be run in MySQL, 
Postgreq and SQLite3.


-Original Message- 
From: Chris Locke



create table t(s varchar(5));


Also note that SQLite doesn't 'understand' varchar (it uses text) and it
doesn't limit the entry to 5 characters.
This doesn't help your issue directly, but does highlight that you've not
read the SQLite documentation, and aren't creating tables properly.

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

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 4:40pm, Jim Dossey  wrote:

> Which is obtained by calling sqlite3_expanded_sql().

Was that a copy-paste error or do you actually want


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

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

2019-04-12 Thread Chris Locke

> create table t(s varchar(5));

Also note that SQLite doesn't 'understand' varchar (it uses text) and it
doesn't limit the entry to 5 characters.
This doesn't help your issue directly, but does highlight that you've not
read the SQLite documentation, and aren't creating tables properly.


On Fri, Apr 12, 2019 at 5:06 PM Shawn Wagner 
wrote:

> From the documentation (https://www.sqlite.org/lang_update.html)
>
> If a single column-name appears more than once in the list of assignment
> expressions, all but the rightmost occurrence is ignored.
>
>
> On Fri, Apr 12, 2019, 9:00 AM Tony Papadimitriou  wrote:
>
> > create table t(s varchar(5));
> >
> > insert into t values('US'),('USA');
> >
> > update t set s = replace(s, 'USA', '___'),
> >  s = replace(s,'US','USA'),
> >  s = replace(s,'___','USA');
> >
> > select * from t;
> >
> > -- Expected answer:
> > -- USA
> > -- USA
> > --
> > -- MySQL gets it right
> > -- Postgres prints error about setting the same column multiple times
> > -- SQLite3 (latest and older) no changes or wrong result but no
> > error/warning
> > ___
> > 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] Unexpected or wrong result and no warning/error, bug?

2019-04-12 Thread Shawn Wagner

From the documentation (https://www.sqlite.org/lang_update.html)

If a single column-name appears more than once in the list of assignment
expressions, all but the rightmost occurrence is ignored.


On Fri, Apr 12, 2019, 9:00 AM Tony Papadimitriou  wrote:

> create table t(s varchar(5));
>
> insert into t values('US'),('USA');
>
> update t set s = replace(s, 'USA', '___'),
>  s = replace(s,'US','USA'),
>  s = replace(s,'___','USA');
>
> select * from t;
>
> -- Expected answer:
> -- USA
> -- USA
> --
> -- MySQL gets it right
> -- Postgres prints error about setting the same column multiple times
> -- SQLite3 (latest and older) no changes or wrong result but no
> error/warning
> ___
> 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] Unexpected or wrong result and no warning/error, bug?

2019-04-12 Thread Tony Papadimitriou

create table t(s varchar(5));

insert into t values('US'),('USA');

update t set s = replace(s, 'USA', '___'),
 s = replace(s,'US','USA'),
 s = replace(s,'___','USA');

select * from t;

-- Expected answer:
-- USA
-- USA
--
-- MySQL gets it right
-- Postgres prints error about setting the same column multiple times
-- SQLite3 (latest and older) no changes or wrong result but no error/warning
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

2019-04-12 Thread Warren Young

On Apr 12, 2019, at 8:51 AM, x  wrote:
> 
> How do I do the same thing if the string param is a utf-8 or utf-16 string 
> and the SearchChar is a Unicode character?

Convert the characters to 32-bit wide characters first, then iterate over the 
array of uint32_t or similar.

One method is shown by the SQLite-internal function sqlite3Utf8Read().  It’s 
static in the amalgamation build, but since SQLite is public domain, you can 
just copy that function’s text out into your program and use it there or modify 
it to suit your purposes.

Your platform libraries may have UTF-8 to UTF-32 or similar mechanisms.

On POSIX platforms, the most common of these is iconv(3).

On Windows, the legacy of UCS-2 and UTF-16 makes this difficult, but if you can 
stick to the Basic Multilingual Plane, converting UTF-8 to UCS-2 gives the same 
effect.  See MultiByteToWideChar(…, CP_UTF8, …):

https://docs.microsoft.com/windows/desktop/api/stringapiset/nf-stringapiset-multibytetowidechar

For all platforms, there’s ICU.  That’s of particular interest with SQLite 
since there’s an included ICU extension you can include to get more Unicode 
power in SQLite:

   https://www.sqlite.org/compile.html#enable_icu

There are complications that your questions don’t push into, but beware that if 
you start getting beyond simple “character in string” questions, you’ll 
eventually have to confront them: combining characters, etc.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with SELECT by rowid after INSERT

I have a table define like this:

CREATE TABLE "sessiond" (
"journal" VARCHAR(4) DEFAULT '' NOT NULL,
"session" VARCHAR(16) DEFAULT '' NOT NULL,
"pid" INTEGER DEFAULT 0 NOT NULL,
rowid INTEGER PRIMARY KEY
);

In my application I open 2 connections to this table, one for reading and one 
for writing.  I do that because sometimes we do a SELECT, and while reading 
through those rows we'll UPDATE them.  With only 1 connection the SELECT will 
block the UPDATE.  I also have WAL mode turned on.

The problem is when I do an INSERT and then try to SELECT that record by rowid 
it doesn't find it.  The INSERT is done on the write connection and the SELECT 
is done on the read connection.  However, if I open a new connection and do the 
SELECT, the new row is there.

To be a little more specific, the problem happens when I try to do 
sqlite3_bind_int() on the prepared statement using the new rowid.  It doesn't 
use the rowid it uses NULL.

The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting SELECT command 
is
SELECT * FROM "sessiond" WHERE "rowid"=NULL;
Which is obtained by calling sqlite3_expanded_sql().

If I use an older rowid in the SELECT, it works just fine.  It just doesn't 
work when using the rowid that was just created.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

2019-04-12 Thread Shawn Wagner

Welcome to the wonderful world of multibyte encodings, and Unicode in
particular.

Unless you're looking for an ASCII character, you're looking for a
substring, not a character. And you're really looking for what's called a
codepoint (The entire concept of character gets kind of fuzzy with
Unicode). If you're not careful, looking for 'a' (U+0061 LATIN SMALL LETTER
A) will match the start of á, which is actually a two codepoint grapheme
(U+0061 and U+0301 COMBINING ACUTE ACCENT) that renders as a single entity.
And if you're okay with matching that, what about á (U+00E1 LATIN SMALL
LETTER A WITH ACUTE), the single codepoint composed version?

Unicode is hard. There are libraries like ICU and libunistring which help a
bit. I have a bunch of sqlite extensions at
https://github.com/shawnw/useful_sqlite_extensions (That I really need to
polish up for an actual release) including a string library that expands a
lot on the build in ICU extension to make working with graphemes and
unicode in general in sqlite a lot easier.

On Fri, Apr 12, 2019 at 7:51 AM x  wrote:

> I’m still confused by utf strings. For simplicity, suppose I set up an
> sqlite function that takes a single string parameter and I want to scan the
> string to count the number of occurrences of a certain character . If I
> knew the string was made up entirely of ascii chars I’d do this
>
> char *c = _value_text(0)[0];
> int count=0;
> while (*c) if (*c++ == SearchChar) count++;
>
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode character?
>
> I’m confused by the fact that Unicode characters are not a fixed number of
> bytes so if I do this e.g.
>
> wchar_t *c = (wchar_t*) sqlite3_value_text(0);
>
> does this mean a complete temporary copy of the value of
> sqlite3_value_text(0) has to be constructed by the compiler such that all
> characters of the newly constructed string are fixed width? If so, I’m just
> wanting to check if there’s a way  of avoiding this overhead.
>
> ___
> 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] Help with sqlite3_value_text

2019-04-12 Thread J Decker

http://utf8everywhere.org/


On Fri, Apr 12, 2019 at 7:51 AM x  wrote:

> I’m still confused by utf strings. For simplicity, suppose I set up an
> sqlite function that takes a single string parameter and I want to scan the
> string to count the number of occurrences of a certain character . If I
> knew the string was made up entirely of ascii chars I’d do this
>
> char *c = _value_text(0)[0];
> int count=0;
> while (*c) if (*c++ == SearchChar) count++;
>
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode character?
>

You'd decide on the common operating mode of your system for 1... there are
of course middleware libraries that need to cater to both and consume
databases instead of just writing it.


>
> I’m confused by the fact that Unicode characters are not a fixed number of
> bytes so if I do this e.g.
>
> wchar_t *c = (wchar_t*) sqlite3_value_text(0);
>
>
https://www.sqlite.org/c3ref/value_blob.html

const void *sqlite3_value_text16(

returns wchar data, converting if nessecary from utf8 if it was stored as
utf8.

value_text only returns utf8 (char* really, but suffices for storage and
retreival of utf8 phrases)

for utf8 it would just be matter of matching 1,2,3, or 4 bytes (depending
on the codepoint you were matching).



does this mean a complete temporary copy of the value of
> sqlite3_value_text(0) has to be constructed by the compiler such that all
> characters of the newly constructed string are fixed width? If so, I’m just
> wanting to check if there’s a way  of avoiding this overhead.
>
>
If it is converted, a temporary value is allocated by the library (at
runtime, well beyond the compiler), ...


> ___
> 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] Help with sqlite3_value_text

2019-04-12 Thread Dominique Devienne

On Fri, Apr 12, 2019 at 4:51 PM x  wrote:

> I’m still confused by utf strings. [...  I want to scan the string to
> count the number of occurrences of a certain character. [...]
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode character?
>
> I’m confused by the fact that Unicode characters are not a fixed number of
> bytes so if I do this e.g.
>
> wchar_t *c = (wchar_t*) sqlite3_value_text(0);

That's very wrong. _text() always returns UTF8. the _text16*() variants
return UTF16.

As to how many bytes a UTF8-encoded "codepoint" takes, it's well documented
on Wikipedia.
Based on the leading bits, one can know unambiguously whether this is the
1st, 2nd, 3rd, or 4th
byte of a 1 to 4 multi-byte sequence.

Even UTF16 can lead to "surrogate pairs" for codepoints beyond the
so-called "CMP".

And that's not even getting into the fact the encoding may not be "unique",
and Unicode "normalization".
This is not an easy subject...

You can play with the char() built-in SQL function to see how different
code point values are encoded in UTF8. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

2019-04-12 Thread Igor Tandetnik

On 4/12/2019 10:51 AM, x wrote:

I’m still confused by utf strings. For simplicity, suppose I set up an sqlite
function that takes a single string parameter and I want to scan the string to
count the number of occurrences of a certain character . If I knew the string
was made up entirely of ascii chars I’d do this

char *c = _value_text(0)[0];
int count=0;
while (*c) if (*c++ == SearchChar) count++;

How do I do the same thing if the string param is a utf-8 or utf-16 string and
the SearchChar is a Unicode character?

The problem you need to solve is "count occurrences of a substring in a
string". The substring in question could consist of one byte representing a single
ASCII character, or a sequence of bytes comprising a UTF-8 encoding of one Unicode
character. This really has nothing to do with SQLite.

I’m confused by the fact that Unicode characters are not a fixed number of
bytes so if I do this e.g.

wchar_t *c = (wchar_t*) sqlite3_value_text(0);

That's just wrong. sqlite3_value_text does *not* return a pointer to a sequence
of wchar_t. Any attempt to actually use `c` pointer would exhibit undefined
behavior.

does this mean a complete temporary copy of the value of sqlite3_value_text(0)
has to be constructed by the compiler such that all characters of the newly
constructed string are fixed width? If so, I’m just wanting to check if there’s
a way of avoiding this overhead.

You seem to ascribe some magical properties to a cast. Nothing is "constructed" by it -
it simply tells the compiler "take this pointer to a memory block, and believe that it
contains something different than what the type of the original pointer suggests; trust me, I know
what I'm doing".

If you prefer UTF-16 encoding over UTF-8, there's sqlite3_value_text16 for that.

If you are unsure what UTF-8 and UTF-16 mean, see

https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/

--
Igor Tandetnik

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

[sqlite] Help with sqlite3_value_text

2019-04-12 Thread x

I’m still confused by utf strings. For simplicity, suppose I set up an sqlite 
function that takes a single string parameter and I want to scan the string to 
count the number of occurrences of a certain character . If I knew the string 
was made up entirely of ascii chars I’d do this

char *c = _value_text(0)[0];
int count=0;
while (*c) if (*c++ == SearchChar) count++;

How do I do the same thing if the string param is a utf-8 or utf-16 string and 
the SearchChar is a Unicode character?

I’m confused by the fact that Unicode characters are not a fixed number of 
bytes so if I do this e.g.

wchar_t *c = (wchar_t*) sqlite3_value_text(0);

does this mean a complete temporary copy of the value of sqlite3_value_text(0) 
has to be constructed by the compiler such that all characters of the newly 
constructed string are fixed width? If so, I’m just wanting to check if there’s 
a way  of avoiding this overhead.

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

Re: [sqlite] VFS implementation for SoC

On 4/12/19, Arun - Siara Logics (cc)  wrote:
> Hi,
>
> Please ignore the earlier emails.  The extended error code when trying
> CREATE TABLE is 1034 and when trying INSERT is 266.

You can now visit https://www.sqlite.org/rescode.html and search for
"1034" and "266" to find out what those errors mean.


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

Re: [sqlite] VFS implementation for SoC

Hi,

Please ignore the earlier emails.  The extended error code when trying CREATE 
TABLE is 1034 and when trying INSERT is 266.

I have given below the correct log generated during INSERT.  Thanks.

Regards
Arun

Enter file name: 
/spiffs/test.db
fn: FullPathNamefn:Fullpathname:Success
fn: Open
/spiffs/test.db
fn:Open:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
Opened database successfully

Welcome to SQLite console!!
---

Database file: /spiffs/test.db

1. Open database
2. Execute SQL
3. Execute Multiple SQL
4. Close database
5. List folder contents
6. Rename file
7. Delete file

Enter choice: 2
Enter SQL (max 500 characters):
INSERT INTO test VALUES (shox96_0_2c('This wont get inserted'))
fn: Access
fn:Access:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Open
/spiffs/test.db-journal
fn:Open:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Write:Success
fn: Write
fn:Write:Success
fn: Read
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Write
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Write:Success
fn: Sync
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Sync:Success
SQL error: 266 disk I/O error


  On Fri, 12 Apr 2019 17:47:21 +0530 Arun - Siara Logics (cc) 
 wrote 
 > I also tried INSERT on an existing database.  This time the extended error 
 > is 266.  I am giving below the log.
 > Also, there are two warnings printed during open:
 > (21) API call with invalid database connection pointer
 > (21) misuse at line 152855 of [fb90e7189a]
 > 
 > Regards
 > Arun
 > 
 > Enter file name: 
 > /spiffs/test.db
 > (21) API call with invalid database connection pointer
 > (21) misuse at line 152855 of [fb90e7189a]
 > fn: FullPathNamefn:Fullpathname:Success
 > fn: Open
 > /spiffs/test.db
 > fn:Open:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > Opened database successfully
 > 
 > Welcome to SQLite console!!
 > ---
 > 
 > Database file: /spiffs/test.db
 > 
 > 1. Open database
 > 2. Execute SQL
 > 3. Execute Multiple SQL
 > 4. Close database
 > 5. List folder contents
 > 6. Rename file
 > 7. Delete file
 > 
 > Enter choice: 2
 > Enter SQL (max 500 characters):
 > INSERT INTO test VALUES ('This wont get inserted')
 > fn: Access
 > fn:Access:Success
 > fn: FileSize
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn:FileSize:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Open
 > /spiffs/test.db-journal
 > Create mode
 > fn:Open:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn: FlushBuffer
 > fn: DirectWrite:
 > fn:DirectWrite:Success
 > fn:FlushBuffer:Success
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Read
 > fn: FlushBuffer
 > fn: DirectWrite:
 > fn:DirectWrite:Success
 > fn:FlushBuffer:Success
 > fn: FileSize
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn:FileSize:Success
 > fn: FileSize
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn:FileSize:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 >

Re: [sqlite] VFS implementation for SoC

I also tried INSERT on an existing database.  This time the extended error is 
266.  I am giving below the log.
Also, there are two warnings printed during open:
(21) API call with invalid database connection pointer
(21) misuse at line 152855 of [fb90e7189a]

Regards
Arun

Enter file name: 
/spiffs/test.db
(21) API call with invalid database connection pointer
(21) misuse at line 152855 of [fb90e7189a]
fn: FullPathNamefn:Fullpathname:Success
fn: Open
/spiffs/test.db
fn:Open:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
Opened database successfully

Welcome to SQLite console!!
---

Database file: /spiffs/test.db

1. Open database
2. Execute SQL
3. Execute Multiple SQL
4. Close database
5. List folder contents
6. Rename file
7. Delete file

Enter choice: 2
Enter SQL (max 500 characters):
INSERT INTO test VALUES ('This wont get inserted')
fn: Access
fn:Access:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Open
/spiffs/test.db-journal
Create mode
fn:Open:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Write:Success
fn: Write
fn:Write:Success
fn: Read
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Write
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Write:Success
fn: Sync
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Sync:Success
SQL error: 266 disk I/O error
Time taken:310381 us


  On Fri, 12 Apr 2019 17:30:14 +0530 Arun - Siara Logics (cc) 
 wrote 
 > Hi, Thank you for the suggestion.  The sqlite3_extended_errcode() is 1034 
 > disk I/O error.
 > Regards
 > Arun
 > 
 >   On Fri, 12 Apr 2019 17:06:00 +0530 Richard Hipp  
 > wrote 
 >  > On 4/12/19, Arun - Siara Logics (cc)  wrote:
 >  > > fn:DirectWrite:Success
 >  > > fn:FlushBuffer:Success
 >  > > fn:Sync:Success
 >  > > SQL error: disk I/O error
 >  > >
 >  > > At the end, there are two files on disk: vfs_test.db (0 bytes) and
 >  > > vfs_test.db-journal (512 bytes).  There is no problem reading a 
 > database.
 >  > > But when CREATE or INSERT is involved, it gives disk I/O error.
 >  > >
 >  > > Any idea why it is throwing disk I/O error, inspite of the previous sync
 >  > > success?  Any suggestions on how I could figure it out?
 >  > 
 >  > Please tell us the sqlite3_extended_errcode().  Also, consider
 >  > enabling the error and warning log
 >  > (https://www.sqlite.org/errlog.html)
 >  > 
 >  > 
 >  > >
 >  > > Regards
 >  > > Arun
 >  > >
 >  > >
 >  > > ___
 >  > > sqlite-users mailing list
 >  > > sqlite-users@mailinglists.sqlite.org
 >  > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 >  > >
 >  > 
 >  > 
 >  > -- 
 >  > D. Richard Hipp
 >  > d...@sqlite.org
 >  > ___
 >  > sqlite-users mailing list
 >  > sqlite-users@mailinglists.sqlite.org
 >  > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 >  > 
 > 
 > ___
 > sqlite-users mailing list
 > sqlite-users@mailinglists.sqlite.org
 > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 > 

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

Re: [sqlite] VFS implementation for SoC

Hi, Thank you for the suggestion.  The sqlite3_extended_errcode() is 1034 disk 
I/O error.
Regards
Arun

  On Fri, 12 Apr 2019 17:06:00 +0530 Richard Hipp  wrote 

 > On 4/12/19, Arun - Siara Logics (cc)  wrote:
 > > fn:DirectWrite:Success
 > > fn:FlushBuffer:Success
 > > fn:Sync:Success
 > > SQL error: disk I/O error
 > >
 > > At the end, there are two files on disk: vfs_test.db (0 bytes) and
 > > vfs_test.db-journal (512 bytes).  There is no problem reading a database.
 > > But when CREATE or INSERT is involved, it gives disk I/O error.
 > >
 > > Any idea why it is throwing disk I/O error, inspite of the previous sync
 > > success?  Any suggestions on how I could figure it out?
 > 
 > Please tell us the sqlite3_extended_errcode().  Also, consider
 > enabling the error and warning log
 > (https://www.sqlite.org/errlog.html)
 > 
 > 
 > >
 > > Regards
 > > Arun
 > >
 > >
 > > ___
 > > sqlite-users mailing list
 > > sqlite-users@mailinglists.sqlite.org
 > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 > >
 > 
 > 
 > -- 
 > 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] VFS implementation for SoC

On 4/12/19, Arun - Siara Logics (cc)  wrote:
> fn:DirectWrite:Success
> fn:FlushBuffer:Success
> fn:Sync:Success
> SQL error: disk I/O error
>
> At the end, there are two files on disk: vfs_test.db (0 bytes) and
> vfs_test.db-journal (512 bytes).  There is no problem reading a database.
> But when CREATE or INSERT is involved, it gives disk I/O error.
>
> Any idea why it is throwing disk I/O error, inspite of the previous sync
> success?  Any suggestions on how I could figure it out?

Please tell us the sqlite3_extended_errcode().  Also, consider
enabling the error and warning log
(https://www.sqlite.org/errlog.html)


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


-- 
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] VFS implementation for SoC