mORMot Open Source

ODBC is proving a pain to set up. Is what I've done below correct because my mORMot server refuses to start after I compile?

    // https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
    // Driver={PostgreSQL UNICODE};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
    fDbProps := TODBCConnectionProperties.Create('','Driver=PostgreSQL Unicode'+
        {$ifdef CPU64}'(x64)'+{$endif}';Database=testdb;'+
        'Server=localhost;Port=5432;UID=testdb;Pwd=testpwd','','');
    // To prevent PostgreSQL from hitting the default maximum connection limit of 100
    // and after rejecting subsequent connections
    TODBCConnectionProperties(fDbProps).ThreadingMode := tmMainConnection;

Thanks

JD

PostgreSQL 11.7 and I have not tried ODBC yet. I will do so and let you know my findings.

Hi there ab,

Sorry for the late reply. I have to apologize because I found the cause of the problem. It was a compiler directive that I disabled and had forgotten about in one of the source files. The end result was that the client and server were not using the same protocols. Everything now works perfectly.

Thank you very much for your time and for the suggestions you gave me.

JD

I just tried it. It did not work either. Can I upload the Heaptrc dump images? There are just 2 of them.

JD

Hi there ab,

I ran TestSQL3 and some assertions failed. Here are the relevant messages:

 1.2. Low level types:
  - RTTI: 1,340 assertions passed  1.71ms
  - Url encoding: 200 assertions passed  1.31ms
  - Encode decode JSON: 429,322 assertions passed  2.47s
  - Wiki markdown to html: 56 assertions passed  492us
  - Variants: 88 assertions passed  386us
  - Mustache renderer: 153 assertions passed  1.03s
  - TDocVariant: 91,785 assertions passed  235.03ms
!  - TDecimal128: 22 / 17,446 FAILED  21.26ms
  - BSON: 245,068 assertions passed  18.98ms
     100000 TBSONObjectID.ComputeNew in 8.79ms i.e. 11,372,682/s, aver. 0us
  - TSynTableStatement: 221 assertions passed  8.40ms
  - TSynMonitorUsage: 1,202 assertions passed  3.26ms
  Total failed: 22 / 786,881  - Low level types FAILED  3.82s
.........
.........
Windows 10 64bit (10.0.18363) (cp1252)
    4 x Intel(R) Core(TM) i3-3240 CPU @ 3.40GHz (x86)
Using mORMot 1.18.5960
    TSQLite3LibraryStatic 3.31.0 with internal MM
Generated with: Free Pascal 3.2 32 bit compiler

Time elapsed for all tests: 3m25
Performed 2020-04-23 00:26:57 by JD on DESKTOP-NPLKN1D

Total assertions failed for all test suits:  22 / 44,408,725
! Some tests FAILED: please correct the code.

JD

Thanks a lot everyone. It worked.

JD

Thanks a lot for the tip Esteban. It works properly now.

JD

Because at one time, the values sent by the client did not seem to be getting to the server. Why? I don't know. So I inserted all the UrlDecodeValue code to test it and I kind of left it like that. It works OK now though.

JD

Thanks for the tip. I was wondering why it was no longer possible to compile the sample as it was in the past versions of mORMot.

I had the same problem. I keep two versions of Lazarus: NewPascal (with FPC 3.1.1) and Lazarus 1.8.4/FPC 3.0.4 stable on my system. This was OK for me until the Lazarus 1.8.4/FPC 3.0.4 could no longer handle the new TypeInfo changes in the latest version of mORMot (SynFPCTypInfo.pas).

Luckily, I saw on the Lazarus forum a new package Lazarus 1.9/FPC 3.2.0 Beta.

http://forum.lazarus-ide.org/index.php/ … ajilg0#new

https://sourceforge.net/projects/lazaru … ndow%2032/
https://sourceforge.net/projects/lazaru … ndow%2064/

I installed this and it compiled the latest mORMot releases flawlessly; I guess because the FPC compiler is more recent and so has the RTTI changes that NewPascal was created to handle. It also compiled the old code I was keeping Lazarus 1.8.4/FPC 3.0.4 around for.

So it looks like I may move ALL my projects to Lazarus 1.9/FPC 3.2.0 Beta and get rid of my existing NewPascal & Lazarus 1.8.4/FPC 3.0.4 combo.

I encourage you to try it. It may be the solution to your problem.

I can confirm that the line

Line 56267:             ParamName := @VMP^.Name;  

is compiled properly

Cheers,

JD

I fully understand where you all are coming from. There is so much to learn in mORMot and I started toying with it in 2015. mORMot has the largest user documentation I've ever encountered outside of PostgreSQL user documentation. It took me over a year before I was able to build a mORMot application. I only know about 40% of mORMot. I completely ignored the ORM/SQLite part and my mORMot server talks to a PostgreSQL database using Zeos.

I was finally able to get going thanks to George's Third Party demo
https://github.com/synopse/mORMot/tree/ … EST-tester

That in addition to the demos referenced by edwinsn in earlier in this thread helped me build my application. My requirements were simple; I wanted

a) an interface based application
b) service oriented architecture (SOA)
b) working over HTTP
c) using JSON to move data from the server to the clients
d) my PostgreSQL database handles user authentication (I don't use the ORM authentication part of mORMot OR any ORM features at all)

These requirements helped me streamline my research and I now have a working application thanks also to the wonderful advice I got from this forum. I still have work to do because I have not succeeded in encrypting the JSON data in transit. I can compress it but I have not yet fathomed how to move from HTTP to WebSockets.

So I would advice you to start small, streamline your needs and let them guide your research. You'll discover that it will be worth your efforts in the long run because I've discovered that using SOA with mORMot allows me to add new functionalities/services very quickly without breaking existing services. That is a very important issue for me. So, all the best.

Cheers,

JD

I read what you posted on the Lazarus forum and I was not happy. We are a small community and we need each other so that the project can move forward and stay alive.

Maybe you should accept Thaddy's offer of mediation so that we can get over this speedbump and move forward.

JD

There is still a problem somewhere. I did as you suggested and got Zeos from the link you provided but the result was the same.

I got the following error message as before in SynDBZeos.pas:

SynDBZeos.pas(1248,14) Error: identifier idents no member "ColumnsToJSON"

The line with the problem is as follows:

  {$if defined(ZEOS73UP) and defined(USE_SYNCOMMONS)}
  fResultSet.ColumnsToJSON(WR);
  {$ELSE}

However, when I edited line 61 of Zeos.inc and undefined USE_SYNCOMMONS like this:

{.$DEFINE USE_SYNCOMMONS} //enable JSON content support by using SynCommons.pas from Synopse project

I was then able to compile my project. So it looks like the problem comes from how Zeos 7.3 and above are supposed to use SynCommons.pas.

I was unable to locate the method ColumnsToJSON in SynCommons.pas so where is this method located?

Ideally, I would prefer to be able to export resultsets to JSON using ColumnsToJSON as expected.

While this "workaround" enabled me to compile my project, what are the side-effects in terms of speed of the REST server's
handling of queries?

Cheers,

JD

Ah! that is the problem. My mORMotWrappers.pas line 724 is different from what AOG posted.

Mine looks like this:

    FillDescriptionFromSource(fDescriptions,fSourcePath[i]+unitName+'.pas');

In addition, my project refers to mORMot stored in its own directory somewhere where it can be shared between NewPascal, Lazarus & Delphi.

So I guess the way forward would be to remove this external references/configuration and rely solely on the preconfigured NewPascal version (at least for future NewPascal projects).

JD

Hi there Don Alfredo. I have an older version of mORMot on my system. Does this mean I have to update mORMot too?

Hi there ab,

Thanks for your suggestions. I'm afraid it still does not work. I now keep getting this error
SQL Error: ERROR: invalid input syntax for type timestamp: “ ”

The input is still the same, it is a timestamp like the following

2017-12-11T14:00:00
2017-12-11T16:00:00

I have even replaced the 'T' with a blank space, to no avail. I keep getting the same error.

Please help!!!

JD

Where is the Iso8601ToDateTime() function? It does not seem to be in SynCommons.pas. It is only mentioned on line 357 in the statement below:

  - new DateToSQL(), DateTimeToSQL() and Iso8601ToSQL() functions, returning
    a string with a JSON_SQLDATE_MAGIC prefix and proper UTF-8/ISO-8601 encoding
    to be inlined as ? bound parameter in any SQL query (allow binding of
    date/time parameters as request by some external database engine
    which does not accept ISO-8601 text in this case)

Thanks,

JD

The Atozed forum has been offline for more than a year.

The active Indy forum is in the Winsock section of the Embarcadero forum at https://forums.embarcadero.com/forum.jspa?forumID=74

JD

I fully agree and I would love to see this functionality

Eureka! That's it. Thanks a lot ab. That was what I was looking for.

JD

I already have a working client. I am porting a large application to mORMot. I'm not using the ORM part of mORMot. I've decided to start with rewriting the existing Indy server using mORMot interface based services and that is moving along fine. On the client side, there is a GUI and a transport layer. The transport layer is Indy based and is what sends and receives data from the server. I don't intend to change the client side GUI since it is agnostic (it does not know how data is sent/received). It is the transport layer that I'm trying to rewrite using mORMot. At present, the following have been implemented client-side

a) sending requests (querying PostgreSQL tables) to the new mORMot server
b) modifying data in the remote tables (the catch here is that the data is sent in the URI respecting the limits of URI length)

The mORMot documentation says the following

Note that there is a known size limitation when passing some data with the URI over HTTP. Official RFC 
2616 standard advices to limit the URI size to 255 characters, whereas in practice, it sounds safe to 
transmit up to 2048 characters within the URI. If you want to get rid of this limitation, just use the 
default transmission of a JSON array as request body.

This is the catch ..... just use the default transmission of a JSON array as request body.

My BIG TO-DO
- some requests are to be made on some large tables. SELECT * will waste bandwidth and will be slow. Selecting ONLY the needed columns is the best strategy but the URI will be too long. So I HAVE to send the SQL via the request body like I've already done with Indy.

The problem is I do not know how to read/write to the URI request body, and I've not seen an example of it yet though I'm still searching Section 16.8.6.1.1. REST mode in the documentation is not clear to me as to how it can be done

The phrase request body occurs only 2 times in the mORMot documentation. The first one is cited above while the second one is

procedure ProcessRequest; virtual; 
Method triggered to calculate the response 
- expect fRequestHeaders, fRequestMethod, fRequestBody and fRequestURL properties as input 
- update fResponseHeaders and fResponseContent properties as output 

Conclusion
I am hoping to use solely a service interface based mORMot server. Is it possible to pass parameters to the interfaces via the request body? If so how do I do it? Or must I mix service methods with service interfaces and then use the Ctxt parameter of service methods to access the message body?

Thanks a lot,

JD

Thanks for your reply ab. I've modified the method and removed its dependence on REST/HTTP verbs. Now it works as it should.

However since URI size is limited to 255 characters, can you please show me how I can send parameters as a JSON object or array in the request body. 16.8.6.1.1.4? Sending a JSON object in the documentation does not provide an example.

Thanks a lot,

JD