SQLite3 64bit no such savepoint error when using ORDER BY

dexter · 2022-12-13 22:33:14

Hi,

Below small code raises following error when compiled in 64bit Delphi 10.1:

ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":"secERROR","Message":"Error SQLITE_ERROR (1) [Step] using 3.40.0 - no such savepoint: , extended_errcode=1"} [] at 01635014 SynSQLite3.sqlite3_check (5412)

When compiled in 32bit platform - it works as expected.
When ORDER BY clause is removed - it works as expected in 64bit as well.

The small db3 file is saved here: https://drive.google.com/file/d/1UfJ_HV … sp=sharing

procedure Test;
var
  Con1: TSQLDBSQLite3Connection;
  Prop1: TSQLDBSQLite3ConnectionProperties;
  Stmt1: TSQLDBStatement;
begin
    Prop1 := TSQLDBSQLite3ConnectionProperties.Create('data.db3', '', '', '');
    Con1 := TSQLDBSQLite3Connection.Create(Prop1);
    try
      Con1.Connect;
      Stmt1 := Con1.NewStatement;
      Stmt1.Execute('select * from Data order by _$Index$_, _$Index2$_', true);
      Stmt1.Step();
    finally
      Stmt1.Free;
      Con1.Free;
      Prop1.Free;
    end;
end;

ab · 2022-12-13 23:02:19

You should not use synDB classes directly but follow the interface way as documented.
That is, use ISynDBStatement.

dexter · 2022-12-14 18:58:09

OK, but it doesn't solve the problem, see code below

procedure Test;
var
  Prop1: TSQLDBSQLite3ConnectionProperties;
  Stmt1: ISQLDBRows;
begin
    Prop1 := TSQLDBSQLite3ConnectionProperties.Create('z:\data.db3', '', '', '');
    try
      Stmt1 := Prop1.Execute('select * from Data order by _$Index$_, _$Index2$_', []);
//      Stmt1 := Prop1.Execute('select * from Data', []); <-- this one works ok
//      Stmt1 := Prop1.ExecuteInlined('select * from Data order by _$Index$_, _$Index2$_', true); <-- fails as well
      Stmt1.Step;
      Stmt1.ReleaseRows;
      Stmt1 := nil;
    finally
      Prop1.Free;
    end;
end;

tbo · 2022-12-14 21:04:34

In Delphi 11.2 the following source code works for both 32/64 bit in release/debug mode. The program was tested outside the IDE in each case. mORMot2, GitHub commit 4452

var
  n: Integer;
  prop: TSQLDBSQLite3ConnectionProperties;
  stmt: ISQLDBRows;
begin
  prop := TSQLDBSQLite3ConnectionProperties.Create('data.db3', '', '', '');
  try
    stmt := prop.Execute('select * from Data order by _$Index$_, _$Index2$_', []);
    if stmt <> Nil then
    begin
      n := 0;
      while stmt.Step do
        Inc(n);

      stmt.ReleaseRows;
      ShowMessage(n.ToString);
    end;
  finally
    prop.Free;
  end;

With best regards
Thomas

dexter · 2022-12-14 21:38:34

I am using Delphi 10.1 Update 2, console app, mORMot 1, latest commit (aad97c6d418ee57af92acd500ead298832ddfc68)

Last edited by dexter (2022-12-14 21:38:56)

ab · 2022-12-15 10:26:18

I was able to reproduce it.
With Delphi Win64 with mORMot 1: exception.
But
With Delphi Win64 on mORMot 2, no problem.
With FPC Win64, on mORMot 1 Win64; no problem.
With Delphi Win64 and external sqlite3-64.dll, no problem.

After an half day investigation, I was not able to find the root cause.
Both mORMot 1 and mORMot 2 share the very same static sqlite3.o file.
I tried to use the same code in mORMot 1 as with mORMOt 2 for the static function resolutions or the FPU mask, but with no success.
It has to be something very low-level, probably a small difference/mistake in mORMot 1, but I was not able to find out what...
mORMot 2 is clearly better organized than mORMOt 1 in this respect, and it should make something cleaner... which I was not able to identify.

So consider defining NOSQLITE3STATIC conditional for your project on Win64, and use the external .dll.
Or switch to mORMOt 2 if you can.

dexter · 2022-12-15 13:44:03

Thanks Arnaud,
will consider using external .dll initially.

Will source code require a lot of change when switching to mORMot 2?

ab · 2022-12-15 14:09:36

You have to switch everything to mORMOt 2 (you cannot mix units).
But usually, it is only a matter of changing the unit names in the uses clause: SynCommons and mORMot to mormot.* units.
So, some copy & paste in the source, and change the folder search in the project options.

There are compatibility classes and types to reuse the old naming, if it changed (e.g. TSQLRecord = TOrm).

Hint: start from the most simple project you have.

tbo · 2022-12-15 17:32:58

dexter wrote:

Will source code require a lot of change when switching to mORMot 2?

I immediately switched completely to mORMot2 {$define PUREMORMOT2}. First, I created a text document where the old and new class names were listed. Also a prototype with the new unit names for the uses part of the units as a copy&paste template. It took me a few hours to get through the source code and copy&paste the changes (not very enjoyable).

Conclusion: The source code of mORMot2 is much better structured and easier to read. After mORMot1 source code study I always had some knots in my brain. What I liked best, mORMot2 is again significantly faster. For me, the quick switch was worth it.

As an example in contrast to mORMot1, a piece of the uses part of a Model Service Unit:

{$I mormot.defines.inc}

interface

uses
  Windows, SysUtils, Classes,
  mormot.core.base,
  mormot.core.text,
  mormot.core.rtti,
  mormot.core.json,
  mormot.core.unicode,
  mormot.core.datetime,
  mormot.core.interfaces,
  mormot.soa.server,
  mormot.orm.base,
  mormot.orm.core,
  mormot.rest.core,
  mormot.rest.server,
  u_VGSharedModel ...

With best regards
Thomas

ab · 2022-12-16 08:01:50

@Thomas

Thanks for the feedback!

If Delphi/FPC would be able to write

uses
  mormot.core.*,
  ...

it would have helped for sure.

dexter · 2022-12-17 16:58:40

What about the JSON/RPC calls?
Is JSON structure the same?
I'm asking because I have created a java mORMot client, calling my Delphi mORMot server.

ab · 2022-12-17 18:52:04

The json format is exactly the same between mormot 1 and 2.

edwinsn · 2022-12-18 03:18:04

ab wrote:

The json format is exactly the same between mormot 1 and 2.

@ab, can we say "mORMot 1 and 2 are binary-compatible"?

ab · 2022-12-19 09:09:22

There are no explicit automated tests, but the JSON should be the same.
Perhaps some low-level details may change, e.g. the contract tag.

But for a public API, i.e. without exact contract validation, it would work with good compatibility.

dexter · 2023-01-18 20:59:07

ab wrote:

So consider defining NOSQLITE3STATIC conditional for your project on Win64, and use the external .dll.

I can see the sqlite3-64.dll only for win64. Is there a sqlite3.dll for win32 as well?

edwinsn · 2023-01-19 03:33:09

dexter wrote:

ab wrote:
So consider defining NOSQLITE3STATIC conditional for your project on Win64, and use the external .dll.
I can see the sqlite3-64.dll only for win64. Is there a sqlite3.dll for win32 as well?

You are supposed to use static linking for Win32.

ab · 2023-01-19 07:36:34

You can get the official Win32 static DLL from https://www.sqlite.org/download.html if you really need.

mORMot Open Source

#1 2022-12-13 22:33:14

SQLite3 64bit no such savepoint error when using ORDER BY

#2 2022-12-13 23:02:19

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#3 2022-12-14 18:58:09

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#4 2022-12-14 21:04:34

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#5 2022-12-14 21:38:34

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#6 2022-12-15 10:26:18

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#7 2022-12-15 13:44:03

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#8 2022-12-15 14:09:36

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#9 2022-12-15 17:32:58

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#10 2022-12-16 08:01:50

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#11 2022-12-17 16:58:40

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#12 2022-12-17 18:52:04

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#13 2022-12-18 03:18:04

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#14 2022-12-19 09:09:22

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#15 2023-01-18 20:59:07

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#16 2023-01-19 03:33:09

Re: SQLite3 64bit no such savepoint error when using ORDER BY

#17 2023-01-19 07:36:34

Re: SQLite3 64bit no such savepoint error when using ORDER BY

Board footer