SQLite Forum

Timeline
Login

50 most recent events by user anonymous occurring on or after 2021-11-02 20:40:24.

More ↑
2021-11-17
00:52 Reply: Hot backup database in WAL mode by coping artifact: 6ec17f4e51 user: anonymous

Ok, I was almost suspecting that this would be the case.

As far as I can tell its no big deal: Even if a write sneaks in between PRAGMA wal_checkpoint(TRUNCATE) and BEGIN IMMEDIATE, the database will be coherent as long as we save the database file and the -wal. The -wal will just not be 0 bytes/restarted really, but that is merely intended as an optimization step before the backup.

00:27 Reply: Hot backup database in WAL mode by coping artifact: feb1cc5d05 user: anonymous
  1. Process 1 is used solely for the purpose stated and nothing else.

So process 1 is a Python process and uses the database before and after. Before the Backup start it would execute BEGIN IMMEDIATE and hold that connection open while Process 2 is backing up the files. Process 2 continues to query the database during the backup.

Sorry, I meant "Process 1 continues to query the database during the backup."

Which word of not used for any other purpose [stated] is causing confusion?

I guess the confusion stems from what "any other purpose [stated]" exactly refers to.

Of course I want to be able to continue doing work in Process 1. From what I understand, the whole idea of an Online Backup is being able to at least read from the database while doing the backup... Otherwise, what is the purpose of that whole endeavor? I could just close the database connection.

The linked Documentation says:

This procedure works well in many scenarios and is usually very fast. However, this technique has the following shortcomings:

  • Any database clients wishing to write to the database file while a backup is being created must wait until the shared lock is relinquished.

That is a clear statement about writes. Reading the database works during holding that lock, and I don't see a reason why it should affect the Backup Process 2 is doing during that time.

2021-11-16
21:03 Reply: SQLite + TCL on-line conference starts in less than 2 days artifact: 5fe02fb88d user: anonymous

I've registered (main interest your speech)

I expected to be notified of (in Zoom parlance) of a Meeting ID or Personal Link Name The email confirming my registration provides neither; it states:

For details, go here:

https://thesqliteandtclconference2021.eventbrite.com

To access the online event page, go here:

https://www.eventbrite.com/x/the-sqlite-tcl-conference-st-2021-registration-*

  • = a large integer, which I've suppressed.

How would I join?

21:02 Reply: Hot backup database in WAL mode by coping artifact: 6f05d17008 user: anonymous

Just realized that executing 2 statements at a time is only really a CLI feature. Is there a way using the API to guarantee the two statements are executed in sequence?

20:36 Reply: Hot backup database in WAL mode by coping artifact: 2ea989bbe9 user: anonymous

Thanks for your clarification, very much appriciated!

Your "historical" method will work just fine

It is not my method, that is a quote from the Documentation about the Backup API :)

  1. Process 1 is used solely for the purpose stated and nothing else.

So process 1 is a Python process and uses the database before and after. Before the Backup start it would execute BEGIN IMMEDIATE and hold that connection open while Process 2 is backing up the files. Process 2 continues to query the database during the backup.

"5." No "frikking about" is undertaken against the database files (which must consist of at least the main database file and any log files) such as deleting the log.

Database file and -wal get stored straight into a tar file, and, if restored, both are placed back to the original place. Restoring is only done while Process 1 is shutdown.

My second concern: Does executing PRAGMA wal_checkpoint(TRUNCATE); BEGIN IMMEDIATE; in a single statement make sure that the -wal file is fully committed, restarted and truncated when taking the backup? My concern are threads: While we use the database in Serialized mode, I am not sure if the internals of SQLite guarantee that the above two commands are executed in sequence or if they potentially could be separated (e.g. by another statement from another thread).

18:56 Reply: Hot backup database in WAL mode by coping artifact: dc8799bd0c user: anonymous

Don't do that. Opening SQLite files using anything but the SQLite API leads to unexpected locks and errors. Plus you don't know how to ensure that a SQLite database file and a WAL file are 'compatible'.

If you're trying to capture the changes to a file, you might find this useful: https://www.sqlite.org/sessionintro.html

No, it is not the intention to capture the changes only.

The goal is to make a complete database backup using tar & compression. We would like to avoid making a local copy first...

We of course would backup always both files, the main db file and -wal file. So we always would have a matching pair.

Don't do that. Opening SQLite files using anything but the SQLite API leads to unexpected locks and errors.

What kind of problematic locks is a simple copy or tar creating? Does that mean that the method described in https://www.sqlite.org/backup.html is no longer safe to use?

Historically, backups (copies) of SQLite databases have been created using the following method:

  1. Establish a shared lock on the database file using the SQLite API (i.e. the shell tool).
  2. Copy the database file using an external tool (for example the unix 'cp' utility or the DOS 'copy' command).
  3. Relinquish the shared lock on the database file obtained in step 1.
14:19 Reply: Tcl interface : pb with "rollback_hook" method artifact: f81630fafb user: anonymous
concrete example of what I mean :

package require sqlite3
sqlite3 db1 :memory:

db1 eval {CREATE TABLE t1(a TEXT NOT NULL);
          INSERT INTO t1 VALUES('first')}

# 1) Use of SQL keywords BEGIN + ROLLBACK + "errorcode" method

  catch {
     db1 eval {BEGIN}
     db1 eval {UPDATE t1 SET a = NULL}
  }
  puts [db1 errorcode]     ;# returns 19 (constraint failed)
  db1 eval {ROLLBACK}
  puts [db1 errorcode]     ;# returns 0
      
# 2) Use of SQL keywords BEGIN + ROLLBACK + "rollback_hook" method (callback) + "errorcode" method

  # callback intended to be invoked *just before* SQLite tries to do a rollback
  db1 rollback_hook {
     puts [db1 errorcode]     ;# returns 19 (constraint failed)
  }

  catch {
     db1 eval {BEGIN}
     db1 eval {UPDATE t1 SET a = NULL}
  }
  db1 eval {ROLLBACK}

# 3) Use of "transaction" method + "errorcode" method

  catch {
     db1 transaction {
        db1 eval {UPDATE t1 SET a = NULL}
     }
  }
  puts [db1 errorcode]     ;# returns 0 (instead of 19 (constraint failed))

# 4) Use of "transaction" method + "rollback_hook" method (callback) + "errorcode" method

  # callback intended to be invoked *just before* SQLite tries to do a rollback
  db1 rollback_hook {
     puts [db1 errorcode]     ;# returns 0 (instead of 19 (constraint failed))
  }

  catch {
     db1 transaction {
        db1 eval {UPDATE t1 SET a = NULL}
     }
  }


There is another problem, in a succession of transactions of SQL statements, the first call to rollback_hook doesn't behave correctly, however the folowing calls seem to do "the right thing" :

sqlite3 db1 :memory:

db1 rollback_hook {
   puts [db1 errorcode]
}

db1 eval {CREATE TABLE t1(a TEXT NOT NULL);
          INSERT INTO t1 VALUES('first')}

catch {
   db1 eval {BEGIN}
   db1 eval {UPDATE t1 SET a = NULL}
}
db1 eval "ROLLBACK"     ;# first call to rollback_hook -> returns 0 here

catch {
   db1 eval {BEGIN}
   db1 eval {UPDATE t1 SET a = NULL}
}
db1 eval "ROLLBACK"     ;# second call to rollback_hook -> returns 19 here
11:00 Post: Hot backup database in WAL mode by coping artifact: 905eb5e564 user: anonymous

Hello,

Since our database can grow quite a bit, we'd like to avoid an extra copy of the main database file. Hence we like to avoid using the SQLite Online Backup API. The Backup documentation page mentions the old option of coping the SQLite database while open and holding a read lock. Is that method safe when using the WAL mode?

From what I understand, BEGIN IMMEDIATE should be sufficient to get the lock, then copying the database file as well as the -wal file should lead to a coherent backup, is this correct?

As an optimization, we were planning on using PRAGMA wal_checkpoint(TRUNCATE); before taking the lock. In this case we could even omit the -wal file. Afaict, on restore we'll have to make sure that a -wal file will be removed (if present) to avoid having a non-matching -wal file.

There is one small concern: From what I understand this does not guarantee that another transaction sneaks in between creating the checkpoint and taking the shared lock. Is there a way to make sure that the two operations are not interrupted by another command?

Best regards, Stefan

2021-11-14
05:51 Reply: A newbie's question about database structure design artifact: 503af5959b user: anonymous

Just build one database of one table. Each directory level just adds one column to your seven cab columns.

2021-11-13
18:42 Reply: Usage of application_id and magic.txt artifact: 8b9e507a01 user: anonymous

How is this the responsibility of the SQLite team ? I see something that the TeXnicard team might want registered in the magic database. Surely it's their responsibility to do so.

I am the TeXnicard team.

2021-11-12
20:29 Reply: Making the SQL standard public artifact: 136f1cc7f6 user: anonymous

The document does not display on my computer, but I agree that the ISO standards should be made publicly available for free (in a simple and common format, such as plain ASCII text), because the standards will be much more useful that way. (They should probably still sell printed copies though, in case you do not have a computer, or if you do have a computer but cannot download them from ISO for whatever reason.)

20:22 Reply: Usage of application_id and magic.txt artifact: 7d12846c85 user: anonymous

Then I will repeat it, in the format of the magic.txt file, in case that makes it understandable:

>68  belong  =0x6a035744  TeXnicard card database -

11:16 Reply: Do I need SQLite.Interop.dll with System.Data.SQLite.Core.1.0.113.1.nuget? artifact: e491ca3ad8 user: anonymous

I found I don't need the interop dll when I updated SQLite to 1.0.115

09:21 Reply: Usage of application_id and magic.txt artifact: ad9c8602fe user: anonymous

Sorry, but i do not unterstand what the request is

2021-11-11
02:45 Reply: Usage of application_id and magic.txt artifact: 8ec88a37df user: anonymous

I also wanted to add thing into there but don't know how to mention, so I try mention on this forum and on the mailing list but it doesn't help. Here is my request again:

The application ID number of a TeXnicard card database is 1778603844. It contains tables named "CARDS", "SYSTEM_VARS", and "USER_VARS" (and possibly additional tables, views, triggers, and/or indexes).

There are others who had also made similar requests and nothing seems to have been done about them (no official replies either, neither accepting nor rejecting them, as far as I could tell; maybe I am wrong).

2021-11-10
18:56 Post: Minor typo in copyright statement artifact: 931e3b7ed4 user: anonymous

There is a minor typo in: https://www.sqlite.org/copyright.html .. an important place. See "All contibutors".

09:25 Reply: GetDataTypeName broken? artifact: 97aeb803c8 user: anonymous

1.0.92.0 I mean.

07:26 Reply: GetDataTypeName broken? artifact: 01f1b37787 user: anonymous

Yes, but in 1.0.35.0 it still worked. Which means the versions are not backwards compatible.

Therefore is this broken or is this intended?

2021-11-09
22:14 Reply: GetDataTypeName broken? artifact: 99f8d61c19 user: anonymous

I'm not looking for a alternative, this is used in a more complex code.

This was just a basic example to show it's broken. In 35 it still worked

15:35 Reply: GetDataTypeName broken? artifact: 9602fac2d8 user: anonymous

First Create a database with:

CREATE TABLE test( a INTEGER)

then execute the following c# code:

using System; using System.Data.SQLite;

namespace SQLiteDataTypeTest { class Program { static void Main(string[] args) { SQLiteConnection Connection = new SQLiteConnection(@"Data Source=<THE_DATABASE>;"); Connection.Open();

        var sqlCommand = Connection.CreateCommand();
        var sqlQuery = "pragma table_info(test)";

        sqlCommand.CommandText = sqlQuery;

        var reader = sqlCommand.ExecuteReader();

        Console.WriteLine("Column name = " + reader.GetName(5)); //PK column
        Console.WriteLine("Datatype = " + reader.GetDataTypeName(5)); //PK column
        Console.ReadKey();
        reader.Close();
        Connection.Close();
    }
}

}

This will result in the output: Column name = pk Datatype =

Datatype should here be integer since that's the column it's type.

14:30 Post: Tcl interface : pb with "rollback_hook" method artifact: b9a2fd5d18 user: anonymous
SQLite 3.35.5
Tcl 8.6.11

I want to monitor the error code when a transaction is rolled back after a SQL statement fails.

sqlite3 db1 filedb

proc callBack {} {
  puts [db1 errorcode]
}

db1 rollback_hook callBack

db1 transaction {
   db1 eval "UPDATE table SET ..."   -> statement fails
}


The "errorcode" method (executed in callBack proc) returns always 0 (SQLITE_OK)
instead of the error code of the statement.

I don't understand what is hapenning, I need some help please.

Thanks
10:28 Reply: ExecuteNonQuery returns wrong number of affected elements artifact: da2d76744f user: anonymous

Hi Gunter!

First of all thanks for the quick answer:)

I have been using system.data.sqlite, version 1.0.115.0. I have just switched from 1.0.113.0 and with the old version it was good.

I wrote here because this forum is linked from that page.

Unfortunately I have got syntax error after the second select in your example:

sqlite> BEGIN;

sqlite> SELECT count() FROM table1;

1004

sqlite> SELECT count() from (DELETE FROM table1 RETURNING 1);

Error: near "DELETE": syntax error

sqlite>

But when I executed only the delete part without "SELECT count() from" I got back '1' 1004 times:)

It also works from DB Browser for SQLite.

Based on this I think there is no problem with sqlite, rather with the wrapper. Therefore my new task is to find the proper forum or simple debug it :)

In the meantime I have found that if I add any condition to the delete expression for example "where 1=1" I get back the proper value from ExecuteNonQuery.

Regards, BB

04:21 Reply: Hide rows in table1 if same value found in table2 ? artifact: 50bc2236b7 user: anonymous

Dear Ryan,

Thank you so much for this! That is exactly what I wanted. You've just made my job about 10x easier & faster. Virtual beers to you!

Krgds. Rob

2021-11-08
21:31 Reply: Feature request: Stored Procedures artifact: e23409ce3e user: anonymous

Is there something like that that will work at run time (parsing the procedural SQL code at run time, and then preparing a procedural SQL object which can be executed later) instead of at compile time?

19:30 Reply: Lemon generates code that does not build with NDEBUG defined. artifact: cbb86f00f8 user: anonymous

PS, I ended up patching it like this:

diff --git a/lempar.c b/lempar.c
index d5ebe69..0e7175a 100644
--- a/lempar.c
+++ b/lempar.c
@@ -230,6 +230,10 @@ static FILE *yyTraceFILE = 0;
 static char *yyTracePrompt = 0;
 #endif /* NDEBUG */

+#ifndef assert
+#define assert(x)
+#endif
+
 #ifndef NDEBUG
 /* 
 ** Turn parser tracing on by giving a stream to which to write the trace
@@ -882,8 +886,8 @@ void Parse(
     yyact = yy_find_shift_action((YYCODETYPE)yymajor,yyact);
     if( yyact >= YY_MIN_REDUCE ){
       unsigned int yyruleno = yyact - YY_MIN_REDUCE; /* Reduce by this rule */
-      assert( yyruleno<(int)(sizeof(yyRuleName)/sizeof(yyRuleName[0])) );
 #ifndef NDEBUG
+      assert( yyruleno<(int)(sizeof(yyRuleName)/sizeof(yyRuleName[0])) );
       if( yyTraceFILE ){
         int yysize = yyRuleInfoNRhs[yyruleno];
         if( yysize ){

Christian

18:58 Post: Lemon generates code that does not build with NDEBUG defined. artifact: f331adca0b user: anonymous

Hi, not sure where to report this so I am trying it here.

The current version of lemon from fossil trunk generates code that does not build with NDEBUG defined (the version packaged in Ubuntu 18.04 has similar problems, which is why I turned to the source in the first place).

assert.h is only included in lempar.c if NDEBUG is not defined, but there are assert statements outside of #ifndef NDEBUG blocks.

Similarly, yyRuleName is only declared if NDEBUG is not defined, but it is referenced outside of an #ifndef NDEBUG block.

Greetings, Christian Henz

15:04 Post: INTEGRITY OS support for WINDOWS artifact: bc4850fe74 user: anonymous
Hi,

I am compiling Qt6 source code with CMake 3.22 -DNinja and GHS compiler on WINDOWS OS.

Qt is recommending to use Ninja build system for building Qt6 source code (officially supported).

But there is an issue connected with following problem: "ar.exe can't handle backslashes in rsp files"

It is already fixed for GCC on WINDOWS (like here https://gitlab.kitware.com/cmake/cmake/-/blob/v3.18.1/Source/cmNinjaNormalTargetGenerator.cxx#L724-728)

But GHS compiler for WINDOWS has the same trouble in case of using Ninja build system and CMake's Ninja generator.

Generated paths inside *.rsp file have backslashes which cannot be handled by ar.exe:

[284/1083] Linking CXX static library qtbase\lib\libQt6Core.a
FAILED: qtbase/lib/libQt6Core.a qtbase/src/corelib/Core.version C:/Users/taboriso/targetbuild/qtbase/src/corelib/Core.version
cmd.exe /C "cmd.exe /C "cd /D C:\Users\taboriso\qt5\qtbase\src\corelib && "C:\Program Files\Git\usr\bin\perl.exe" C:/Users/taboriso/qt5/qtbase/mkspecs/features/data/unix/findclasslist.pl < C:/Users/taboriso/targetbuild/qtbase/src/corelib/Core.version.in > C:/Users/taboriso/targetbuild/qtbase/src/corelib/Core.version  && cd C:\Users\taboriso\targetbuild" && "C:\Program Files\CMake\bin\cmake.exe" -E rm -f qtbase\lib\libQt6Core.a && C:\Qt\Tools\mingw810_64\bin\ar.exe qc qtbase\lib\libQt6Core.a  @CMakeFiles\Core.rsp && C:\Qt\Tools\mingw810_64\bin\ranlib.exe qtbase\lib\libQt6Core.a && cd ."
C:\Qt\Tools\mingw810_64\bin\ar.exe: qtbasesrccorelibCMakeFilesCore.dirCore_autogenmocs_compilation.cpp.o: No such file or directory
ninja: build stopped: subcommand failed.


But GHS compiler problem can be easy fixed in the same way like it was fixed for GCC on WIN:

diff --git a/Source/cmGlobalNinjaGenerator.cxx b/Source/cmGlobalNinjaGenerator.cxx
index d5b5eb0e4c..38f6a971e7 100644
--- a/Source/cmGlobalNinjaGenerator.cxx
+++ b/Source/cmGlobalNinjaGenerator.cxx
@@ -180,7 +180,7 @@ std::string cmGlobalNinjaGenerator::EncodePath(const std::string& path)
 {
   std::string result = path;
 #ifdef _WIN32
-  if (this->IsGCCOnWindows())
+  if (this->IsGCCOnWindows() || this->UsingGHSOnWindows)
     std::replace(result.begin(), result.end(), '\\', '/');
   else
     std::replace(result.begin(), result.end(), '/', '\\');
@@ -941,6 +941,9 @@ void cmGlobalNinjaGenerator::EnableLanguage(
           cmHasLiteralSuffix(compilerId, "Clang")))) {
       this->UsingGCCOnWindows = true;
     }
+    else if(compilerId == "GHS") {
+      this->UsingGHSOnWindows = true;
+    }
 #endif
   }
 }
diff --git a/Source/cmGlobalNinjaGenerator.h b/Source/cmGlobalNinjaGenerator.h
index ec73475db5..a8b5f47f1a 100644
--- a/Source/cmGlobalNinjaGenerator.h
+++ b/Source/cmGlobalNinjaGenerator.h
@@ -540,6 +540,7 @@ private:
   std::unordered_map<std::string, int> RuleCmdLength;

   bool UsingGCCOnWindows = false;
+  bool UsingGHSOnWindows = false;

   /// The set of custom command outputs we have seen.
   std::set<std::string> CustomCommandOutputs;

   

Maybe fix can be used?

Just wish to notify about problem existence and propose a fix as soon as CMake + Ninja + GHS + WIN failing for now.


Thank you,
anonymous :-)
13:11 Reply: Hide rows in table1 if same value found in table2 ? artifact: 8d1f34ddbd user: anonymous

Thanks again Gunter. I'm sorry but all that has gone right over my head. Maybe I can try explaining it with pictures what I want to do ?

Please see https://imgur.com/a/d3nORtD.

The top pic is an example of my table1.

The middle pic is table2 and an example of what I want to do. By entering ABC123 and FGH000 I want those 5 rows (3x ABC123 and 2x FGH000) to be hidden in table1 view. The schema for both is TEXT for hex and callsign, date for date. No PK set. I can add additional stuff to table2 layout/schema if needed, but not to table1.

The 3rd pic is asking for what code I need in the box to make it happen.

I hope that is clearer?

Many thanks. Rob

09:18 Reply: Hide rows in table1 if same value found in table2 ? artifact: c1442eba94 user: anonymous

Hi Gunter, many thanks.

Just to be clear as I'm a complete noob at this stuff, do you mean the schema of table2 or table1?

Table1 can't be changed as I import new data dailt in csv format as needs to match the table format exactly otherwise DB4S will allow the import.

And do I replace the word 'field' with column name?

To put it very simply, if I add a single column in table2 and put a row with ABC123 in the cell, I want all rows in table1 with ABC123 to disappear from view (the column names will match). Sorry for any confusion!

Thanks & rgds. Rob

02:39 Post: Hide rows in table1 if same value found in table2 ? artifact: 6cae3fdf90 user: anonymous
Hello.
I am using DB4S.

In table1 one I have rows like this:

ABC123 12345
ABC123 67890
ABC123 98765
ABC123 99999
DEF123 12345
DEF123 55555
XYZ123 01234

etc

I want to retain the data in the table rather than deleting it, but after processing it I want to hide it from view based on the value in col1.  So, I want to add ABC123 to table2 and by doing this it hides all ABC123 rows in table1 'browse data' view.  I can't use either column as primary key as both columns have instances of the same value.

Can anyone help me with the code for table1 "if value from table1 col1 found in table2 col1, don't show those rows in table1" (basically!) ?

Thank you.
Rgds.
Rob
2021-11-07
19:22 Post: ExecuteNonQuery returns wrong number of affected elements artifact: 30c0ef8e72 user: anonymous

When a simple table contains more the one hundred rows truncate operation (delete from tableName) called from ExecuteNonQuery returns more than 100. If it contains more rows the difference is bigger and bigger (in case of 200 rows I have got back 203, in case of 5015 it returns with 5399).

Could you please investigate it?

Thanks!

2021-11-05
19:23 Reply: Insert operation leads to exception on Windows when installed using msi artifact: d7d81fb1b6 user: anonymous

Had to rule out application creating the DB file as there is lot of data which needs to be preloaded, and do it on the first time application launch might slow down the application launch and also increase the code management. So packaging it with the required metadata sets. Sure will check in another forum. Just wanted to check if SQLite has any such permission restrictions. Thanks for the support.

18:20 Reply: 'values' with no args : has to be syntax error? artifact: 255e8f2654 user: anonymous

I disagree. I think that maybe it ought to be allowed, but if so, it should not be the equivalent of select 1 where 0. Instead, what I think would make sense, should be:

VALUES; -- zero rows, zero columns
VALUES(); -- one row, zero columns
SELECT 1 WHERE 0; -- zero rows, one column
INSERT INTO X() VALUES(); -- same as INSERT INTO X DEFAULT VALUES;

07:59 Reply: Insert operation leads to exception on Windows when installed using msi artifact: d1f71da6ba user: anonymous

Basically I pack the db file in the installer as a content file, and the application reads and writes to this file. So how do you suggest I package this in the installer, so that its not restricted to admin permission

Below is the properties for this content file

Dependies -none

exclude - false

excludefilter- none

FOlder - application folder

hidden - true

keyOutput - none

outputs - outputs

packageAs - vsdpaDefault

permanent - false

readonly- false

register - vsdrpDoNotRegister

SharedLegacy - false

System - true

Transitive - false

vital - true

I have used the "setup project" project type from VS Profession 2019 package

Also one thing I would like to point out is that the application runs normally in OS build - 19042.1237, but is asking for administrator permission in 19042.1288 +

2021-11-04
19:53 Reply: Is the order conserved in a table from a VALUES clause? artifact: ae7777d6d9 user: anonymous

That's not what the documentation says multi-row VALUES means.

14:20 Post: sqlite3ext.h missing "/" in comment "* Version 3.37.0 and later */" near line 656 artifact: 63802db4ce user: anonymous

Introduced here.

07:33 Post: Is this inherent casting by design? artifact: 3e49db22ff user: anonymous

It appears that when multiplying a string that starts with numbers by 1 (and, presumably, any other math operation would also work) you effectively convert it to a number.

Multiplying by 1 (for example) has the welcome side effect of extracting just the number and/or allowing numeric ordering even when the field does not contain a pure number:

create table t(v);

insert into t values
  ('300 EUR'),
  ('30 EUR'),
  ('300 USD'),
  ('30 USD'),
  ('5 USD'),
  ('5 EUR');

select v normal from t order by v; --normal string ordering
select v numeric,v*1 from t order by v*1; --numeric ordering

I'd like to know if

  1. this is by design (and, therefore, can be relied upon to not change in the future), and

  2. if (by design) it could be expanded to work with the first number regardless of position within the string, so that:

insert into t values
  ('EUR 300'),
  ('EUR 30'),
  ('USD 300'),
  ('USD 30'),
  ('USD 5'),
  ('EUR 5');

select v numeric,v*1 from t order by v*1;

would also extract the number (v*1) and come out sorted numerically.

Thank you

2021-11-03
23:33 Reply: Getting 'default' values: for a view? artifact: 5c3c6c5aaf user: anonymous

One way is:

begin; insert into people default values returning *; rollback;

20:57 Reply: Get SQLITE_READONLY when using UPDATE command artifact: cabdc9cd27 user: anonymous

The problem is solved. I was able to create the database, read from it, and write back to it in the App Data local stare folder where MS recommends it be stored.

20:05 Reply: Get SQLITE_READONLY when using UPDATE command artifact: 8cac0cefca user: anonymous

I think I have solved the problem. It is a WinRT problem with how SQLite writes a file. See this url - https://www.codeproject.com/Articles/879846/Using-SQLite-Databases-in-Any-Folder-in-WinRT

This explains what the problem is and gives a solution to writing database files anywhere using VFS in SQLite. I am going to try putting my database in the app's local storage folder and see if it works. I bet it does. Thanks and I will post how this works out for me.

19:27 Reply: Is the order conserved in a table from a VALUES clause? artifact: ba238d772f user: anonymous

You can't use ORDER BY with VALUES, although I think that if the outer statement is purely VALUES (and not a compound statement, etc) then the order ought to be guaranteed in that case (if it isn't, this is a feature suggestion to change this).

But in the case you have, that is not needed since you can instead write: VALUES (1,5), (2,6), (3,1)

But as far as I know, column names are not guaranteed for VALUES (and you cannot specify column names with VALUES nor can they be specified in a table alias), so would instead need a more complicated code.

I do think that WITH ORDINALITY would be useful though, mainly for use with recursive CTEs.

18:49 Reply: Get SQLITE_READONLY when using UPDATE command artifact: c02cedf9e5 user: anonymous

Just so you know, I was getting a hex E from the IDE when I hovered over the result of the function. The function returns an int which means the value was 14. It was just easier to type an E.

The error was SQLITE_CANTOPEN.

18:44 Reply: Get SQLITE_READONLY when using UPDATE command artifact: 3e30e8a998 user: anonymous

I forgot to show you how I declared my database objects. sqlite3* ptrMain_Db = nullptr; char* sqlite3_temp_directory;

Here are my changes. sqlite3_temp_directory = "C:UsersDavid" Database file path = "file://Users/David/test.db" (m_Main_Db_File)

open function is now. result = sqlite3_open_v2(m_Main_Db_File, &ptrMain_Db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL);

and now I get a result of 1.

16:34 Reply: Get SQLITE_READONLY when using UPDATE command artifact: 9a9930a28e user: anonymous
I will try all the things you have suggested. I do wonder if my problem is mostly caused by not using sqlite3_open_v2(). I will try it, but may have questions if this solves the problem.

As for the double \\. For strings that contain a single \, it must be escaped or when printed, a string like "i\t" would be printed as i followed by a tab. I don't think this is the problem. I do understand this, I just stated it poorly. 

But I did discover why when I moved the database to my user folder, it did not work. First, I did not have the file Included in the project, and second, it was not marked as Content for its properties in VS. Once I did both of those I did not get the E when I opened the database. I got a 0.

So I will make the temp folder my project folder, and try using sqlite3_open_v2() and see where that gets me. Thanks. I will post my results, good or bad.
15:53 Post: c# nuget package with aes256 artifact: a1d7414bfb user: anonymous

Is there a way to encrypt a database with AES256 encryption using the c# nuget package? I guess default is AES128?

14:45 Reply: Get SQLITE_READONLY when using UPDATE command artifact: 7220c211be user: anonymous
Here is some code I have before I ever call sqlite3_open(). It is to show you the pieces I have or don't have.

database.h file:
extern sqlite3* ptrMain_Db;
SQLITE_EXTERN char* sqlite3_temp_directory;

const char* m_Main_Db_File ;

database.cpp file:(in constructor)
m_Main_Db_File = "file:\\Users\\David\\test.db";

// Assert the database versions.
assert(sqlite3_libversion_number() == SQLITE_VERSION_NUMBER);
assert(strncmp(sqlite3_sourceid(), SQLITE_SOURCE_ID, 80) == 0);
assert(strcmp(sqlite3_libversion(), SQLITE_VERSION) == 0);

// Make the sqlite3 temp directory.
LPCWSTR zPath = ApplicationData::Current().TemporaryFolder().Path().data();
char zPathBuf[MAX_PATH + 1];
memset(zPathBuf, 0, sizeof(zPathBuf));
WideCharToMultiByte(CP_UTF8, 0, zPath, -1, zPathBuf, sizeof(zPathBuf), NULL, NULL);
sqlite3_temp_directory = sqlite3_mprintf("%s", zPathBuf);

This is how I use sqlite3_open().
int result = sqlite3_open(m_Main_Db_File, &ptrMain_Db);

I moved the database file to my user folder which I should have full permission rights to. Now I get a hex E from result. I tried all the possible ways for my file string. Without the file:, single back slashes which Windows did not like, and forward slashes. I got the same result, E. This is different from when the database was in my project folder.

In VS I have installed SQLite/SQL Server Compact Toolbox. From this too I can always access and modify any SQLite database. This is how I have been making changes to the database so far. Is this tool causing my problems?

Thanks.
2021-11-02
22:55 Reply: Get SQLITE_READONLY when using UPDATE command artifact: 616599aed7 user: anonymous

result of the sqlite3_open() was 0.

result of the sqlite3_exec() was 8. sqlite3() errorMsg = "attempt to write a readonly database" result of sqlite3_errcode() was 8. result of sqlite3_extended_errcode() was 8.

Here is my query string: "update Users set Username='hidden', Password='hidden' where Id='1';"

Not sure what the problem is. I can't seem to decipher the extended code.

Thanks.

21:29 Reply: sqlite3_threadsafe() return value artifact: ef1c0f7d28 user: anonymous

Yes, that is a great improvement. Thank you so much, both of you.

21:19 Post: Get SQLITE_READONLY when using UPDATE command artifact: 4b5e6c8fd1 user: anonymous

Hi,

I am using UWP C++/WinRT in VS 2019 along with winsqlite3.h, .lib, and .dll files provided by MS. The OS is Win10 and the SDK number is 10.0.19041.0 which is just a little behind the latest. The SQLite3 version is SQLITE_VERSION "3.29.0".

From what I have read, it seems to be a permissions problem. I made sure my user has permissions on the database file, and all folders in the path to the file, but since it is in the Public Users folder, I have a problem with it being some folder permission. I could be wrong.

I seem to have a vague memory of reading somewhere that WinRT needs a list of files that it can modify. The database file is in the same folder with all my source code. I may change that later, but for now it is fine where it is at, unless that violates some rule I am not aware of. I will go check if my memory was right after this post. Other than that, I have no other ideas to try. I welcome any other ideas or things to try. Thanks.

20:40 Reply: sqlite3_threadsafe() return value artifact: 16f72c0bae user: anonymous

That would be an improvement.

More ↓