Posts by mike76

mike76 · mORMot 1

ab, As you already know we are using TSynDBDataSet in our project

We are experiencing a quite odd issue when using TSynDBDataSet(ds1.DataSet).ApplyUpdates(0), the changes are not applied if the row contains a column with empty string, but it's not NULL.

For example we are inserting rows:

    fProps.ExecuteNoResult(
      'INSERT INTO MyData (column1, column2, column3) '+
      'VALUES (?,?,?)',
      [WideStringToUTF8(myvalue1),
       WideStringToUTF8(myvalue2),
       WideStringToUTF8(myvalue3)
      ]);

If myvalue3='' (empty string), when we made changes to the row and TSynDBDataSet(ds1.DataSet).ApplyUpdates(0), the changed don't commit. There is no error message.
If we change the value of the column3, to not be empty, commit will work.

The only way to fix this issue is to not add the column3 to the INSERT statement if myvalue3 is empty.

Is there any other way we could fix it?
When the string it's empty, insert to set NULL value for that column.

Thanks

mike76 · mORMot 1

Yes, this seems to fix the issue. Thanks

mike76 · mORMot 1

Hi,

Using Delphi 7, added SynCommons to our uses and all of a sudden our widestring labels were not displaying correctly.
Had to track the problem for 30 mins to the TRIM function defined in SynCommons. After comment it, everything is OK again.

Is TRIM and POS functions extensively used in the sqlite3 db?
Is it possible to fix it for Delphi 7 or is OK just to comment it out?

Thanks.

mike76 · mORMot 1

Hi ab,

Did you had time to look into this? Do you think it's hard to fix ?

Thanks.

mike76 · mORMot 1

ab wrote:

Why do you need encryption at this level?

We store information that should not be available to view without our software.

ab wrote:

You may use an external sqlite3.dll which supports encryption, instead of the built-in "statically linked" version, in the meanwhile.

Yes, but the no dll feature of mORMot was one of the reasons we chose it.

mike76 · mORMot 1

ab wrote:

Yes, but I did not have time investigating on it yet.

Ok. Hope you'll have time to check it soon. At the moment this is the only issue stopping us for using mORMot in our project.

Thanks

mike76 · mORMot 1

Hi ab,

I've sent you a few days ago by email a sample project which demonstrates the error. Have you received it?

Thanks

mike76 · mORMot 1

Seems I found how to replicate the issue:
It happens if the database is encrypted, has more than 60.000+ records (probably depends on table structure) and you add "Order By" to the SQL statement. No error happens if I don't use Order By.

TSynDBDataSet(ds1.DataSet).CommandText := 'select * from MyTable Order by MyColumn ASC';

Note that without encryption I can load the database with more than 500.000+ records using Order by.

Hope you can help me find the problem.

mike76 · mORMot 1

Do you refer to this ?

sqlite3_key() expects PRAGMA mmap_size=0 write(off=0 mmapsize=9662464 bufflen 1024)

mike76 · mORMot 1

sqlite3_key() expects PRAGMA mmap_size=0 write(off=0 mmapsize=9662464 bufflen 1024)

Here is the output from TestSQL3:

   Synopse mORMot Framework Automated tests
  ------------------------------------------


1. Synopse libraries

 1.1. Low level common:
  - System copy record: 22 assertions passed  181us
  - TRawUTF8List: 130,009 assertions passed  123.13ms
  - TDynArray: 1,079,722 assertions passed  157.43ms
  - TDynArrayHashed: 1,200,629 assertions passed  124.14ms
  - TObjectListHashed: 999,885 assertions passed  308.72ms
  - TObjectDynArrayWrapper: 167,501 assertions passed  35.21ms
  - TObjArray: 2,016 assertions passed  1.37ms
  - Fast string compare: 7 assertions passed  116us
  - IdemPropName: 138 assertions passed  166us
  - Url encoding: 150 assertions passed  799us
  - GUID: 9,005 assertions passed  1.39ms
  - IsMatch: 599 assertions passed  227us
  - Soundex: 35 assertions passed  114us
  - Numerical conversions: 1,117,321 assertions passed  429.02ms
  - crc32c: 20,020 assertions passed  55.96ms
      pas 19.54ms 305.3 MB/s fast 3.34ms 1.7 GB/s
  - Curr 64: 20,053 assertions passed  1.94ms
  - CamelCase: 11 assertions passed  97us
  - Bits: 4,774 assertions passed  117us
  - Ini files: 7,004 assertions passed  20.58ms
  - UTF8: 80,127 assertions passed  735.80ms
  - Iso 8601 date and time: 36,015 assertions passed  4.55ms
  - Time zones: 407 assertions passed  14.27ms
  - Url decoding: 1,100 assertions passed  278us
  - Mime types: 23 assertions passed  119us
!  - TSynTable: 93 / 875 FAILED  6.73ms
  - TSynCache: 404 assertions passed  176us
  - TSynFilter: 1,005 assertions passed  2.01ms
  - TSynValidate: 677 assertions passed  576us
  - TSynLogFile: 42 assertions passed  522us
  Total failed: 93 / 4,879,576  - Low level common FAILED  2.02s

 1.2. Low level types:
  - RTTI: 61 assertions passed  145us
  - Url encoding: 200 assertions passed  585us
  - Encode decode JSON: 283,483 assertions passed  141.13ms
  - Variants: 45 assertions passed  118us
  - Mustache renderer: 144 assertions passed  1.93ms
  - TDocVariant: 71,712 assertions passed  137.91ms
  - BSON: 245,048 assertions passed  3.32ms
  - TSynTableStatement: 205 assertions passed  172us
  Total failed: 0 / 600,898  - Low level types PASSED  285.91ms

 1.3. Big table:
  - TSynBigTable: 19,126 assertions passed  123.57ms
  - TSynBigTableString: 16,104 assertions passed  25.29ms
  - TSynBigTableMetaData: 475,563 assertions passed  1.17s
  - TSynBigTableRecord: 375,563 assertions passed  1.57s
  Total failed: 0 / 886,356  - Big table PASSED  2.90s

 1.4. Synopse PDF:
!  - TPdfDocument: 1 / 2 FAILED  5.29ms
  - TPdfDocumentGDI: 5 assertions passed  52.54ms
  Total failed: 1 / 7  - Synopse PDF FAILED  58.05ms

 1.5. Cryptographic routines:
  - Adler32: 1 assertion passed  541us
  - MD5: 1 assertion passed  110us
  - SHA1: 5 assertions passed  112us
  - SHA256: 5 assertions passed  114us
  - AES256: 16,816 assertions passed  715.07ms
  - RC4: 1 assertion passed  109us
  - Base64: 11,994 assertions passed  87.55ms
  - CompressShaAes: 1,683 assertions passed  3.87ms
  Total failed: 0 / 30,506  - Cryptographic routines PASSED  812.44ms

 1.6. Compression:
  - In memory compression: 12 assertions passed  237.39ms
  - GZIP format: 19 assertions passed  456.34ms
  - ZIP format: 54 assertions passed  1.38s
  - SynLZO: 3,006 assertions passed  85.02ms
  - SynLZ: 32,221 assertions passed  591.65ms
  Total failed: 0 / 35,312  - Compression PASSED  2.75s


2. mORMot

 2.1. File based:
  - Database direct access: 20,248 assertions passed  259.60ms
  - Virtual table direct access: 12 assertions passed  1.53ms
  - TSQLTableJSON: 144,070 assertions passed  122.67ms
  - TSQLRestClientDB: 608,193 assertions passed  4.18s
  - TRecordVersion: 20,060 assertions passed  185.35ms
  Total failed: 0 / 792,583  - File based PASSED  4.75s

 2.2. File based memory map:
  - Database direct access: 20,246 assertions passed  186.49ms
  - Virtual table direct access: 12 assertions passed  1.03ms
  - TSQLTableJSON: 144,070 assertions passed  111.66ms
  - TSQLRestClientDB: 608,192 assertions passed  4.10s
  - TRecordVersion: 20,060 assertions passed  188.31ms
  Total failed: 0 / 792,580  - File based memory map PASSED  4.59s

 2.3. File based WAL:
  - Database direct access: 20,248 assertions passed  216.55ms
  - Virtual table direct access: 12 assertions passed  948us
  - TSQLTableJSON: 144,070 assertions passed  120.42ms
  - TSQLRestClientDB: 608,193 assertions passed  4.30s
  - TRecordVersion: 20,060 assertions passed  165.41ms
  Total failed: 0 / 792,583  - File based WAL PASSED  4.80s

 2.4. Memory based:
  - Database direct access: 20,246 assertions passed  182.93ms
  - Virtual table direct access: 12 assertions passed  839us
  - TSQLTableJSON: 144,070 assertions passed  114.38ms
  - TSQLRestClientDB: 676,500 assertions passed  5.18s
  - TRecordVersion: 20,060 assertions passed  187.59ms
  - RTree: 140,000 assertions passed  1.27s
  Total failed: 0 / 1,000,888  - Memory based PASSED  6.95s

 2.5. Basic classes:
  - TSQLRecord: 80 assertions passed  582us
  - TSQLRecordSigned: 200 assertions passed  4.09ms
  - TSQLModel: 16 assertions passed  126us
  - TSQLRestServerFullMemory: 562,310 assertions passed  745.05ms
  Total failed: 0 / 562,606  - Basic classes PASSED  752.45ms

 2.6. Client server access:
  - TSQLHttpServer: 2 assertions passed  10.42ms
     using WinSock 2.0.514
  - TSQLHttpClient: 3 assertions passed  27.60ms
     using TSQLHttpClientWinHTTP
  - HTTP client keep alive: 3,087 assertions passed  230.43ms
     4818 B, first 4.46ms, done 214.04ms i.e. 4671/s, aver. 214us, 21.8 MB/s
  - HTTP client multi connect: 3,087 assertions passed  225.52ms
     4818 B, first 374us, done 213.69ms i.e. 4679/s, aver. 213us, 21.8 MB/s
  - HTTP client encrypted: 3,087 assertions passed  282.90ms
     4818 B, first 466us, done 270.18ms i.e. 3701/s, aver. 270us, 17.3 MB/s
  - Named pipe access: 3,089 assertions passed  624.58ms
     4818 B, first 271.33ms, done 127.69ms i.e. 7831/s, aver. 127us, 36.6 MB/s
  - Local window messages: 3,088 assertions passed  64.95ms
     4818 B, first 131us, done 54.20ms i.e. 18448/s, aver. 54us, 86.2 MB/s
  - Direct in process access: 3,056 assertions passed  42.04ms
     4818 B, first 33us, done 30.61ms i.e. 32664/s, aver. 30us, 152.7 MB/s
  - HTTP several DB servers: 9,604 assertions passed  997.49ms
     4818 B, first 483us, done 238.05ms i.e. 4200/s, aver. 238us, 19.6 MB/s
     4818 B, first 467us, done 238.12ms i.e. 4199/s, aver. 238us, 19.6 MB/s
     4818 B, first 459us, done 237.50ms i.e. 4210/s, aver. 237us, 19.6 MB/s
  Total failed: 0 / 28,103  - Client server access PASSED  2.51s

 2.7. Service oriented architecture:
  - Weak interfaces: 56 assertions passed  704us
  - Service initialization: 255 assertions passed  3.46ms
  - Direct call: 579,186 assertions passed  55.18ms
  - Server side: 579,206 assertions passed  39.67ms
  - Client side REST: 772,274 assertions passed  938.15ms
  - Client side REST result as object: 772,268 assertions passed  950.33ms
  - Client side REST sessions stats: 772,268 assertions passed  996.39ms
  - Client side REST locked: 772,272 assertions passed  933.21ms
  - Client side REST main thread: 772,272 assertions passed  1.90s
  - Client side REST background thread: 772,272 assertions passed  1.63s
  - Client side REST weak authentication: 772,268 assertions passed  908.90ms
  - Client side REST basic authentication: 772,268 assertions passed  1.60s
  - Client side REST custom record layout: 772,268 assertions passed  997.75ms
  - Client side JSONRPC: 772,268 assertions passed  1.03s
  - Test over HTTP: 9,661 assertions passed  583.58ms
  - Security: 135 assertions passed  2.65ms
  - Mocks and stubs: 30,031 assertions passed  66.23ms
  Total failed: 0 / 8,921,228  - Service oriented architecture PASSED  12.65s

 2.8. Bidirectional remote connection:
  - Websockets JSON protocol: 60 assertions passed  175us
  - Websockets binary protocol: 240 assertions passed  303us
  - Run http server: 2 assertions passed  1.40ms
  - SOA callback on server side: 4,452 assertions passed  2.44ms
  - SOA callback via JSON websockets: 4,455 assertions passed  197.88ms
  - SOA callback via binary websockets: 4,455 assertions passed  218.65ms
  - TRecordVersion: 20,068 assertions passed  234.33ms
  Total failed: 0 / 33,732  - Bidirectional remote connection PASSED  655.92ms

 2.9. External database:
  - TQuery: 2,003 assertions passed  12.44ms
! External database - SynDBRemote
! Exception ESQLDBRemote raised with messsage:
!  TSQLDBServerHttpApi.Create: administrator rights needed to register URI root
on port 888
  - DB properties persistence: 7 assertions passed  874us
  - External records: 2 assertions passed  347us
  - Auto adapt SQL: 708 assertions passed  73.84ms
  - Crypted database: 253,275 assertions passed  231.68ms
  - External via REST: 170,354 assertions passed  1.26s
  - External via virtual table: 170,354 assertions passed  2.28s
  - External via REST with change tracking: 180,454 assertions passed  3.37s
  - JET database: 7,007 assertions passed  874.47ms
  Total failed: 0 / 799,211  - External database PASSED  8.38s

 2.10. Multi thread process:
  - Create thread pool: 1 assertion passed  22.22ms
  - TSQLRestServerDB: 4,822 assertions passed  132.30ms
     1=17591/s  2=23098/s  5=22444/s  10=21160/s  30=24642/s  50=22295/s
  - TSQLRestClientDB: 4,822 assertions passed  1.46s
     1=18683/s  2=12276/s  5=7147/s  10=2889/s  30=886/s  50=533/s
  - TSQLRestClientURINamedPipe: 2,412 assertions passed  1.36s
     1=1213/s  2=1407/s  5=774/s
  - TSQLRestClientURIMessage: 3,222 assertions passed  185.92ms
     1=7682/s  2=12056/s  5=10171/s  10=7960/s
  - Windows API: 4,822 assertions passed  1.12s
     1=4474/s  2=4913/s  5=4224/s  10=2463/s  30=1369/s  50=1513/s
  - Socket API: 4,822 assertions passed  856.96ms
     1=7483/s  2=6952/s  5=5141/s  10=2948/s  30=1860/s  50=1514/s
  - Websockets: 4,822 assertions passed  1.15s
     1=7117/s  2=6414/s  5=3661/s  10=1970/s  30=1113/s  50=1158/s
  - Locked: 4,822 assertions passed  1.21s
     1=18640/s  2=12317/s  5=7136/s  10=2887/s  30=977/s  50=747/s
  - Unlocked: 4,822 assertions passed  123.86ms
     1=18639/s  2=22607/s  5=20222/s  10=21601/s  30=24115/s  50=21763/s
  - Main thread: 4,822 assertions passed  201.83ms
     1=14278/s  2=12859/s  5=12868/s  10=12865/s  30=12877/s  50=12979/s
  - Background thread: 4,822 assertions passed  139.50ms
     1=16884/s  2=20996/s  5=20512/s  10=20468/s  30=22246/s  50=19531/s
  Total failed: 0 / 49,033  - Multi thread process PASSED  8.00s

 2.11. DDD shared units:
  - User model: 3,276 assertions passed  734us
  - Authentication model: 20,006 assertions passed  132.93ms
  - Email validation process: 57 assertions passed  93.21ms
  - User CQRS repository: 57,106 assertions passed  562.15ms
  Total failed: 0 / 80,445  - DDD shared units PASSED  791.76ms


Using mORMot 1.18.1518 FTS3
Running on wEight_64 with code page 1250
TSQLite3LibraryStatic 3.8.10.2 with internal MM
Generated with: Delphi 7 compiler

Time elapsed for all tests: 63.86s
Tests performed at 21.06.2015 23:43:29

Total assertions failed for all test suits:  94 / 20,285,647
! Some tests FAILED: please correct the code.

Done - Press ENTER to Exit

Thanks.

mike76 · mORMot 1

The ones from your link, dated 28.05.2015 19:55

mike76 · mORMot 1

I don't use TSQLDataBase.MemoryMappedMB.

I'm using your 17 sample with Dataset and modified it with encryption. A database with more than 65.000 records fails to load, actually dataset open just freezes.

var
   fProps: TSQLDBSQLite3ConnectionProperties;
begin
.......
    fProps := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(fDBFileName),'','',StringToUTF8('mypassword'));
......

So what should I change to fix this ?

mike76 · mORMot 1

Hi,

I have a sql3 database on which I made test of speed, adding batch 10.000 rows. It worked nice.
Now, I've tried to use the encrypted option and create a database with password.
When trying to add the 60.000+ rows I get this error message:
sqlite3_key() expects PRAGMA mmap_size=0

And when I try to open the database again, nothing happens.
Note, I'm using TSynDBDataSet to load the DB into a DBGrid and latest nightly.

Hope you can help me find the problem on this.
Thanks

mike76 · mORMot 1

Did you had time to check the field size issue?

Also in this project I use the :
TSynDBDataSet(ds1.DataSet).CommandText which is defined as string, so under Delphi 7 does not support UTF8 statements. I've tried to change the source to widestring, but have problem with PSSetCommandText which on compilation triggers an error. Is there a way to fix this to support UTF8 under Delphi 7 ?

Thanks

mike76 · mORMot 1

Thanks ab, for your reply. Unfortunately this still doesn't work.

I've just compiled the project (under Delphi 7) and use the default database backupbackground.db3. I select "SQLite3 SQL TDataset" and "check Via TClientDataset". I can enter only 24 chars in FirstName column and 12 in Last Name.

I've tested another partial free/commercial component that uses a UniDirQuery which had an event (InitFieldDef) where you can set/override the default values of a field before creating the dataset. There I could set the width to 300 (or other value) for ftWideString and change datatype if I want for other fields.

mike76 · mORMot 1

Hi,

First thanks for this powerful framework, just found it yesterday.

I've found a possible bug or maybe you can let me know what I'm doing wrong, steps to reproduce the problem:
run the application and enter a Firstname longer than 24 chars. Once you are exiting the editor the text gets cut off after 24 chars. Same happens to the other TEXT field LastName, it has a different maximum length.
What I discovered is that DBGrid somehow sets the length of the field based on the longest string for that column, when loading the database and everything that is larger is cut off.
The only way to fix this is to enter long strings in some dummy row and all dbgrid columns will have "unlimited" length.

Could you please look into this.
Thanks

mORMot Open Source

#1 mORMot 1 » TSynDBDataSet(ds1.DataSet).ApplyUpdates issue » 2015-07-17 15:24:11

#2 Re: mORMot 1 » SynCommons TRIM breaks widestring under Delphi 7 » 2015-07-16 18:23:57

#3 mORMot 1 » SynCommons TRIM breaks widestring under Delphi 7 » 2015-07-16 09:03:56

#4 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-07-09 21:30:20

#5 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-07-02 20:42:14

#6 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-07-02 13:49:23

#7 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-06-29 08:40:50

#8 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-06-23 09:44:46

#9 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-06-21 22:02:22

#10 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-06-21 20:44:58

#11 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-06-21 19:04:43

#12 Re: mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-06-21 18:44:50

#13 mORMot 1 » sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB » 2015-06-21 16:06:30

#14 Re: mORMot 1 » Field size issue DBGrid in 17 - TClientDataset use sample » 2015-06-18 16:16:09

#15 Re: mORMot 1 » Field size issue DBGrid in 17 - TClientDataset use sample » 2015-06-17 14:33:51

#16 mORMot 1 » Field size issue DBGrid in 17 - TClientDataset use sample » 2015-06-16 22:06:43

Board footer