Help for new user

Sapiem · 2017-12-08 03:00:37

Hello:

I would like to learn how to use this framewok in my applications.

I have always used SQLite3 in my applications and now I need to use the databases in a network (not exactly as a client server, but as applications that share the same database from a network shared folder)

My applications use sqlite + Zeoslib.

I have read a lot about mORMot, but I can not understand how I can apply it to my interests, using already existing databases, which are also encrypted AES.

I ask for a simple example in which I can open an encrypted Sqlite3 database, which can be used with INSERT, UPDATE, SELECT, etc. and that can obtain a Query that allows me to link it to a dataset so that the results are published in grids . Mainly if this I can achieve using the Zeos components already.

I ask a lot, I know, but surely you can advise me with some existing example or directly through this way.

Thank you...

ab · 2017-12-08 08:02:53

Ensure you start by reading the FAQ of the doc.
https://synopse.info/files/html/Synopse … l#TITL_123

esmondb · 2017-12-08 10:27:11

rather ironically the link above hangs on my iphone5 chrome browser when I scroll down to 'Too much documentation can kill the documentation!'.
It's really convenient to have online documentation but would there be any way to divide up the html pages to make it more responsive?

Sapiem · 2017-12-08 13:03:04

Yes, I read the documentation, but everybody know that with an example you (an application developer) can start faster with new codes...

ab · 2017-12-08 15:28:08

esmondb wrote:

would there be any way to divide up the html pages to make it more responsive?

The html is generated, and the SynProject tool need to be modified to split the big page into smaller - but it is not so easy, since there are thousands of hyperlinks within this page and with other html pages.

Sapiem · 2017-12-08 19:45:12

Please, any help with example?

jaclas · 2017-12-08 20:32:47

It looks like you do not understand what a mORMot is. And maybe you do not need it.

Sapiem · 2017-12-08 20:42:46

Well, I need access to the same Sqlite3 database stored in a shared folder. Just that. When I know mORmot, it said: "you can use with multiple access". Maybe is not directly the multi access to a Slite3 database that mORmot give.

ab · 2017-12-09 14:53:35

mORMot allows multiple access to a single SQLite3 database by using its client/server feature.
You don't access directly the SQLite3 database, but use a remote connection, via a mORMot server.

You have two ways:
- either via the mORMot ORM - defining TSQLRecord classes and a TSQLModel
- or via SynDBRemote over SynDBSQLite - able to run any SQL statement remotely

Sapiem · 2017-12-09 16:49:08

I think that 2nd way is the best for me. Where can I found any example, very simple, that implement it? Sorry if I insist in an example. I need start from any....

Thank a lot.

pd. Next, in future app I'll try with first method

mpv · 2017-12-10 00:26:09

Did you try SQLite3/Samples/12 - SynDB Explorer ?

Sapiem · 2017-12-10 04:08:03

YES, I just try it now and it's exactly what I was loocking for and I need..!!!! How I couldn't see it before..?

Please, now I have another question: mORmot only works with DrawGrids?

I need use QuickReport component for my reports, so I need a dataset for that.

Thanks again, thank you very much...

mpv · 2017-12-10 07:57:27

You can convert result to data set descendants. See 8.1.9. TDataset and SynDB docs article

Last edited by mpv (2017-12-10 07:58:03)

Sapiem · 2017-12-11 01:29:02

Thanks, I'll try after read it

Sapiem · 2017-12-11 03:57:17

Ok, now I can load table inside TDBGrid, but data is not ok. Only symbols appear. I try with my real database, with internal (statically linked) and external sqlite3.dll, and with the "test.db3" database that came with mORmot. Any of this options show real data when use other dataset.

DataGrid show real data from any database, without problem.

Why?

How can I open my real database with the password that I usually use?

Thanks

ab · 2017-12-11 08:08:11

Which encryption are you using?

The encryption implemented by SynSQLite3.pas is proprietary, and not compatible with others.

But it is weird that you get any data, since an encrypted database should just be rejected by the SQlite3 engine itself.

Sapiem · 2017-12-11 12:49:31

I use the same sqlite3.dll from long time, that's why I use it externally.

The database that I'm testing is from my app, but to try, I uncrypt it. So, must work without problem.

The DataSet load the fields names wothout problem, but data in cells is wrong.... just show empty, or "X", or "?", etc...

Sapiem · 2017-12-12 00:59:29

This is the code that I try...

procedure TForm1.Button1Click(Sender: TObject);
const
SQL_PEOPLE = 'SELECT Nombre, HC, CI FROM Pacientes LIMIT 100';
begin
FreeAndNil(sqlite3);
sqlite3:= TSQLite3LibraryDynamic.Create('sqlite3.dll'); // sqlite3.dll in app root path, loaded ok
fDBFileName:= 'Pacientes.db3'; // This database was exported from example 12, using to file Btn
fProps := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(fDBFileName),'','','');

ds1.DataSet.Free;

ds1.DataSet:= TSynDBDataSet.Create(self);
TSynDBDataSet(ds1.DataSet).Connection := fProps;
TSynDBDataSet(ds1.DataSet).CommandText := SQL_PEOPLE;
TSynDBDataSet(ds1.DataSet).IgnoreColumnDataSize := true;
ds1.DataSet.Open;

end;

ab · 2017-12-12 01:49:10

I see you looked closer at the sample "17 - TClientDataset use".

Your code is using the TClientDataSet inherited class.

Could you try with TSynDBDataSet instead?

Or, even better, try with stmt.Execute + ds1.DataSet = ToDataSet(self, stmt).

Sapiem · 2017-12-12 02:35:11

This is the code with 3 variants (read every head comment)

procedure TForm1.Button1Click(Sender: TObject);
const
SQL_PEOPLE = 'SELECT Nombre, HC, CI FROM Pacientes LIMIT 100';
var
proxy: TSQLDBConnectionProperties;
stmt: TSQLDBStatement;
begin
FreeAndNil(sqlite3); // release any previous instance (e.g. static)
sqlite3:= TSQLite3LibraryDynamic.Create('sqlite3.dll');
fDBFileName:= 'Pacientes.db3';
fProps := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(fDBFileName),'','','');

ds1.DataSet.Free;

// Variant 1 - returns differents values, all symbols...
ds1.DataSet:= TSynDBDataSet.Create(self);
TSynDBDataSet(ds1.DataSet).Connection := fProps;
TSynDBDataSet(ds1.DataSet).CommandText := SQL_PEOPLE;
TSynDBDataSet(ds1.DataSet).IgnoreColumnDataSize := true;
ds1.DataSet.Open;

// Variant 2 - This return oleaut32.dll error
ds1.DataSet := TSynDBSQLDataSet.Create(self);
TSynDBSQLDataSet(ds1.DataSet).Connection := fProps;
TSynDBSQLDataSet(ds1.DataSet).CommandText := SQL_PEOPLE;
ds1.DataSet.Open;

// Variant 3 - No error, but return all values like: Nombre as "??", HC and CI as (BLOB), but not show the values
// Nombre field is TEXT, HC and CI are BLOB fields

proxy := fProps;
stmt := proxy.NewThreadSafeStatement;
try
stmt.Execute(SQL_PEOPLE,true);
ds1.DataSet := ToClientDataSet(self,stmt);
// ds1.DataSet := ToDataSet(self,stmt); ------> This generates the same ole error
finally
stmt.Free;
end;
end;

Sapiem · 2017-12-12 03:09:48

Could I send you by email a rar file with database + code?

ab · 2017-12-12 08:56:06

And if you use the static SQlite3 engine shipped with the framework (SynSQLite3Static) instead of your DLL?

Does sample "17 - TClientDataset use" work on your PC?

Sapiem · 2017-12-12 11:17:10

Even using it. Example 17 show error. First siad json error trying load onCreate code. Raise error and never enter in test.db3 loading.

Changing this part, it load, but show ole error too.

ab · 2017-12-12 11:58:53

I've no problem running sample 17 here.

Are you sure you are running latest mORMot version from trunk?
Current version is 1.18.4071.

Sapiem · 2017-12-12 18:13:52

Yes I download the latest. I always download mORMotNightlyBuild.zip and sqlite3obj.7z. I's that ok?

Last edited by Sapiem (2017-12-13 18:38:08)

Sapiem · 2017-12-14 00:15:57

Well, I try now the same code but in Delphi 10 Berlin and it work perfectly... I use Delphi 2006. I will try to migrate all my code to Delphi Berlin (unicode)

So, the problem is (almost) in Delphi 2006

Sapiem · 2017-12-20 17:53:05

Hello:

Well, now I can use mORMot even with FastReport, but I've another trouble that I can't solve yet. My old database was encoded with UTF8 and I have been using Delphi 2006. Now I want to upgrade all code to Delphi 10.1 Berlin, and when I query some data, mORMot it responds with incomplete text. Trying it the DB with ZeosLib as before in Delphi 2006, I can see that the problems is that controls (TDataSet, TDBMemo, etc) in Delphi 10.1 load data in UTF16, when mORMot work with UTF8, right?

How can I fix this?

Thanks

ab · 2017-12-20 18:30:50

Since mORMot is UTF8-based, it works with Unicode content.
There is no problem to work with UTF-16 string and mORMot: either you use UnicodeString, or you convert RawUTF8 to/from UnicodeString.

The same apply to Zeos (if you have a recent version).
Just check the encoding you are using for your TDatasets.

If your controls don't have the expected content, it is because you are missing some implicit conversions in your own code.
First, ensure there is NO warning at compilation in your code: no implicit conversion should happen.

Sapiem · 2017-12-20 18:39:11

My database is UTF8. When I load with example # 17, it shows in DataSet incomplete text. NO warning in compilation.

I have ZeosLib 7.2 version. Any recent version?

Recreate the database?

ab · 2017-12-20 20:29:33

Try to debug with your sample where the data is not correct.
I guess there is something wrong with the Zeos connection definition.

Sapiem · 2017-12-20 20:45:51

I don't use Zeos connection. I use code TSQLDBSQLite3ConnectionProperties (example 17 - TClientDataSet use)

I only change database name with my DB name, uncrypted.

George · 2017-12-28 07:49:01

I'm looking for predefined functions for RawUTF8, similar to "LeftStr(str, count)", "Copy(str, start, len)".
Seems SynCommons does not contain what I'm looking for.
Am i wrong?

Last edited by George (2017-12-28 08:58:10)

ab · 2017-12-28 09:30:19

You can use plain old copy() with RawUTF8, since it is an ansi string.

Then you have dedicated versions, faster than Delphi RTL, e.g. like Trim/Pos/PosEx/U-Int32-64ToUTF8/Extended-Double-CurrencyToStr and other new functions like IdemPChar/IdemPropNameU/EndWith-Array and a lot of low-level PUTF8Char functions for very efficient process directly on the UTF-8 buffer, with no RawUTF8 allocation.

Sapiem · 2017-12-28 17:10:32

With ZeosLib I hve this:

Table Persons -> Name TEXT field (only this)

SQL:= SELECT 'Blue' Color, Name FROM Persons;

Return

Color Name
Blue Wilson
Blue Patric

etc

Ok, I create a new Field by SQL in the fly, but with mORMot this return:
Color Name
??Blue?(+ symols: arrows, squares etc) Wilson
Blue(+ symols) Patric

Why?

Sapiem · 2018-01-04 19:45:43

Any answer? Please....

mORMot Open Source

#1 2017-12-08 03:00:37

Help for new user

#2 2017-12-08 08:02:53

Re: Help for new user

#3 2017-12-08 10:27:11

Re: Help for new user

#4 2017-12-08 13:03:04

Re: Help for new user

#5 2017-12-08 15:28:08

Re: Help for new user

#6 2017-12-08 19:45:12

Re: Help for new user

#7 2017-12-08 20:32:47

Re: Help for new user

#8 2017-12-08 20:42:46

Re: Help for new user

#9 2017-12-09 14:53:35

Re: Help for new user

#10 2017-12-09 16:49:08

Re: Help for new user

#11 2017-12-10 00:26:09

Re: Help for new user

#12 2017-12-10 04:08:03

Re: Help for new user

#13 2017-12-10 07:57:27

Re: Help for new user

#14 2017-12-11 01:29:02

Re: Help for new user

#15 2017-12-11 03:57:17

Re: Help for new user

#16 2017-12-11 08:08:11

Re: Help for new user

#17 2017-12-11 12:49:31

Re: Help for new user

#18 2017-12-12 00:59:29

Re: Help for new user

#19 2017-12-12 01:49:10

Re: Help for new user

#20 2017-12-12 02:35:11

Re: Help for new user

#21 2017-12-12 03:09:48

Re: Help for new user

#22 2017-12-12 08:56:06

Re: Help for new user

#23 2017-12-12 11:17:10

Re: Help for new user

#24 2017-12-12 11:58:53

Re: Help for new user

#25 2017-12-12 18:13:52

Re: Help for new user

#26 2017-12-14 00:15:57

Re: Help for new user

#27 2017-12-20 17:53:05

Re: Help for new user

#28 2017-12-20 18:30:50

Re: Help for new user

#29 2017-12-20 18:39:11

Re: Help for new user

#30 2017-12-20 20:29:33

Re: Help for new user

#31 2017-12-20 20:45:51

Re: Help for new user

#32 2017-12-28 07:49:01

Re: Help for new user

#33 2017-12-28 09:30:19

Re: Help for new user

#34 2017-12-28 17:10:32

Re: Help for new user

#35 2018-01-04 19:45:43

Re: Help for new user

Board footer