Lazarus

Programming => Databases => Topic started by: madref on July 08, 2018, 08:32:13 am

Title: Noob and JSON
Post by: madref on July 08, 2018, 08:32:13 am
I am trying a new concept. JSON in combination with a database in the cloud.
But I haven't got a clue how to start.


Can anyone help me on the way on how to start?
Title: Re: Noob and JSON
Post by: taazz on July 08, 2018, 09:56:01 am
Do you control the database and the json generator? If yes are asking for help on json export json import and json update cycle or you have something else in mind? It this a direct access to the database or through some services (rest or otherwise?) Are you going to implement both service and consumer? Those are the first question that need answering. 
Title: Re: Noob and JSON
Post by: madref on July 08, 2018, 10:12:57 am
This is what I want to do.
I have this local database on my laptop and make it available for 5 users.
So i 'uploaded' the structure and data to my own webserver/webspace.
Now I need to make it so that everyone can access the data.


I had this discussion lately http://forum.lazarus.freepascal.org/index.php/topic,39107.0.html
Title: Re: Noob and JSON
Post by: taazz on July 08, 2018, 10:49:02 am
OK which CMS you have? Does it support SQLite? Do you want people to download the database or import new data only? Or to put it in an other light does the database contain user data as well as your data that you want to preserve or a simple replace with the new database is acceptable? Does you server support cgi applications? Notice I did not ask for cgi scripts because since php is a scripting language and the interpreter is a cgi application every one assume that php is a cgi script. So can you upload a cgi application on your server and call it through some url? If yes try to write a simple cgi application in lazarus that will return a string to the browser and see how that works.

Then you can decide if you wish to use mormot, brook or some otehre framework to speed up your development or you want to stick with simple cgi scripts and sqldb to export/import data.
Please note the complete absence of reference to what OS bitness the server requires, that is because for those first steps you find and install a webserver in your computer preferably the same weeb server your site uses (apache most probably) set it up to support cgi application and experiment there with your cgi and return string before targeting your web server.
Title: Re: Noob and JSON
Post by: madref on July 08, 2018, 11:31:59 am
As discussed before I want to ONLY access the data.
All input and output is done locally on a laptop and is NOT through any website.
I just use the space (see attachment) These are my tables and views

Title: Re: Noob and JSON
Post by: taazz on July 08, 2018, 11:36:34 am
As discussed before I want to ONLY access the data.
sorry that is to broad a specification access the data from where? how? ARe you talking about seeing the data in a browser aka a dynamic web site? or you have a client application that your customers use on theyr computers that handles the view and you need to pass them the data for viewing?
All input and output is done locally on a laptop and is NOT through any website.
I just use the space (see attachment) These are my tables and views
irrelevant. where the input is done only how the data are viewed. In any case my previous advice still stand for both application that handles the view and dynamic web site.
Title: Re: Noob and JSON
Post by: madref on July 08, 2018, 01:22:03 pm
Let me try to simplify it
I have a database which is stored on a webserver. The space I rent for my website (http://www.nursingwithhumour.com)
I want to create an app that can access this database on my webserver and want to make it for multiple users
Title: Re: Noob and JSON
Post by: madref on July 08, 2018, 01:30:40 pm
So I have to create an interfase between the app and the web bases database
Title: Re: Noob and JSON
Post by: madref on July 10, 2018, 06:34:29 pm
But how can I make that interface?
Title: Re: Noob and JSON
Post by: Trenatos on July 10, 2018, 08:24:41 pm
I'm not sure what your goal is.

Are you trying to create a desktop application which can access your cloud-based database?

Are you creating a server-side application to serve as an interface to the database?

What's your end goal? Why are you creating this application/What problem are you trying to solve?
Title: Re: Noob and JSON
Post by: mangakissa on July 11, 2018, 08:36:47 am
For my webchat I had a MySQLserver where I do a request. The result was a xml file which I could parse into my chat. The chat was an browser made app with javascript. The same thing I did with an application on a desktop. With indy I did a request and result was formatted.

For Mormot / Tiopf you need a serverside / client side. Now you compile with lazarus, so unix should not be difficult. The problem relies on the port you have  to open on the server. Mostly cloud servers are limited by open ports.
For SOAP / Rest server the same as above with ports.
Title: Re: Noob and JSON
Post by: madref on July 13, 2018, 12:54:24 am
Are you trying to create a desktop application which can access your cloud-based database?
YES..
What's your end goal? Why are you creating this application/What problem are you trying to solve?

My end goal is to be able to make it for multiple user (max 5).
Title: Re: Noob and JSON
Post by: denver on July 13, 2018, 08:38:26 am
Are you trying to create a desktop application which can access your cloud-based database?
YES..
What's your end goal? Why are you creating this application/What problem are you trying to solve?

My end goal is to be able to make it for multiple user (max 5).


In the Server Side :

you can use php to format the result set and which can be json or xml or CSV format just up to yoy.

for example : ( CSV format )
http://yoursite/query/gettable_yourtable.php

id,name,code
1, David,A003
2, Joe,A005
3, Peter,A007

In the Client Side application, you can use Lazarus with indy or synapse to get the url content :
http://yoursite/query/gettable_yourtable.php

and extract the data from the CSV CsvDocument and display to the client.

CsvDocument is a component for lazarus to format and extract data    http://wiki.freepascal.org/CSV 

Title: Re: Noob and JSON
Post by: Leledumbo on July 13, 2018, 11:27:12 am
Server side:
Code: Pascal  [Select][+][-]
  1. {$mode objfpc}
  2.  
  3. uses
  4.   classes, fphttpapp, httpdefs, httproute, fpjson;
  5.  
  6. procedure GimmeMaJSON(ARequest:TRequest; AResponse : TResponse);
  7. begin
  8. with TJSONObject.Create do
  9.   try
  10.     // change these to take data from db or whatever source you have
  11.     Floats['f'] := 1.234;
  12.     Integers['i'] := 5678;
  13.     Strings['s'] := 'oh yes';
  14.     Arrays['a'] := CreateJSONArray([9,1,1]);
  15.     Objects['o'] := CreateJSONObject(['key','value','key again',12345]);
  16.     AResponse.Content := FormatJSON([foSingleLineArray,foSkipWhiteSpace]); // or simply AsJSON if beautiful formatting doesn't matter to you
  17.   finally
  18.     Free;
  19.   end;
  20. end;
  21.  
  22. begin
  23.    HTTPRouter.RegisterRoute('/myjsondata',@GimmeMaJSON);
  24.    Application.Port := 9000;
  25.    Application.Initialize;
  26.    Application.Run;
  27. end.
  28.  
Client side (running on the same machine as the server):
Code: Pascal  [Select][+][-]
  1. {$mode objfpc}
  2.  
  3. uses
  4.   fphttpclient, fpjson, jsonparser;
  5.  
  6. const
  7.   ServerBaseURL = 'http://localhost:9000';
  8. var
  9.   JSONStr: String;
  10.   JSONObj: TJSONObject;
  11.   e: TJSONEnum;
  12. begin
  13.   JSONStr := TFPHTTPClient.SimpleGet(ServerBaseURL + '/myjsondata');
  14.   JSONObj := TJSONObject(GetJSON(JSONStr));
  15.   try
  16.     WriteLn(JSONObj.Floats['f']:1:6);
  17.     WriteLn(JSONObj.Integers['i']);
  18.     WriteLn(JSONObj.Strings['s']);
  19.     for e in JSONObj.Arrays['a'] do WriteLn(e.Value.AsInteger);
  20.     WriteLn(JSONObj.Objects['o'].Strings['key']);
  21.     WriteLn(JSONObj.FindPath('o.key again').AsInteger);
  22.   finally
  23.     JSONObj.Free;
  24.   end;
  25. end.
  26.  
Title: Re: Noob and JSON
Post by: Almir.Bispo on July 13, 2018, 02:04:40 pm
Forget Json.Is a bed format to handle. You can do it With CSV Comp DB (CGI versio) like this:
https://www.youtube.com/watch?v=7DSTotVI1_I (https://www.youtube.com/watch?v=7DSTotVI1_I)

You must install CSV Comp DB (CGI version on Windows + Apache).
create your csv table
create a Lazarus application like the DB Admin and handle remote DB
*DB Admin has documentation e free sample to use

This exemple has source code :
part 1 https://www.youtube.com/watch?v=2XytLSkgyQw (https://www.youtube.com/watch?v=2XytLSkgyQw)
part 2https://www.youtube.com/watch?v=QmI3kIdPVK4 (https://www.youtube.com/watch?v=QmI3kIdPVK4)

All docs and software on my blog for free http://adltecnologia.blogspot.com.br (http://adltecnologia.blogspot.com.br)
Title: Re: Noob and JSON
Post by: madref on July 13, 2018, 03:24:08 pm
This is still Abracadabra for me.


Isn't their an easier way?
Title: Re: Noob and JSON
Post by: mangakissa on July 14, 2018, 08:59:30 am
You can never figure it out if you don't try.
Title: Re: Noob and JSON
Post by: sash on July 14, 2018, 10:08:26 am
Why do you ever need a JSON (3-tiered application I guess)?
You said your requirements is simple 5 users application.

In your place (being novice), I would go for a good old simple direct SQL connection.
In case of security concerns (you think one provided by the SQL engine is not enough), use firewalls, ssh tunnels or other admin measures.


Title: Re: Noob and JSON
Post by: Leledumbo on July 14, 2018, 10:54:05 am
This is still Abracadabra for me.


Isn't their an easier way?
It can't get any easier, after all you're combining multiple concepts together: database access, JSON, client-server through HTTP. Don't expect there will ever be a one liner for all of them, even if it's probably possible.
The hard part of making them available as libraries is already done, using those libraries is the easy part.
Title: Re: Noob and JSON
Post by: mangakissa on July 16, 2018, 09:01:59 am
Why do you ever need a JSON (3-tiered application I guess)?
You said your requirements is simple 5 users application.

In your place (being novice), I would go for a good old simple direct SQL connection.
In case of security concerns (you think one provided by the SQL engine is not enough), use firewalls, ssh tunnels or other admin measures.
The problem relies on the cloud database.
Title: Re: Noob and JSON
Post by: BeanzMaster on July 16, 2018, 10:20:05 am
Hi
This scheme is call "NoSQL Database" you can find many reference on web.
Title: Re: Noob and JSON
Post by: sash on July 16, 2018, 11:34:14 am
The problem relies on the cloud database.

What's problem? Isn't TCP connection available? Even cheapest hosting providers allows that.
Title: Re: Noob and JSON
Post by: taazz on July 16, 2018, 11:39:29 am
This is still Abracadabra for me.


Isn't their an easier way?
Sure just make a post on the job section of the forums and pay some one else to do the work for you.
Title: Re: Noob and JSON
Post by: mangakissa on July 16, 2018, 04:33:13 pm
Code: Pascal  [Select][+][-]
  1. function GetURLAsString(const aURL: string): string;
  2. var
  3.   lHTTP: TIdHTTP;
  4. begin
  5.   lHTTP := TIdHTTP.Create;
  6.   try
  7.     Result := lHTTP.Get(aURL);
  8.   finally
  9.     lHTTP.Free;
  10.   end;
  11. end;
  12.  
your result from de webserver could be JSON /XML /CSV or whatever you put in your html/php page.
Title: Re: Noob and JSON
Post by: madref on July 17, 2018, 01:09:38 am
The problem relies on the cloud database.

What's problem? Isn't TCP connection available? Even cheapest hosting providers allows that.
Can you elaborate?
Title: Re: Noob and JSON
Post by: sash on July 17, 2018, 10:05:07 am
Can you elaborate?
What exactly?
Title: Re: Noob and JSON
Post by: JD on July 17, 2018, 12:18:50 pm
This is still Abracadabra for me.


Isn't their an easier way?

Like Leledumbo said earlier, it is just a simple 3 tier application: database access, a server with JSON serialization over HTTP/TCP and a desktop client to talk to the server. It is relatively easy to do this with Indy (using IdHTTP or IdTCP transport), mORMot (REST & HTTP) or even FPC's built in default HTTP/TCP units.

If you want something simpler (drag and drop), I suggest you look at REST Dataware drag and drop components. Even if the author is Brazilian and his YouTube videos are in Portuguese, the images speak for themselves. That is the simplest method I know of.

REST Dataware links
REST Dataware Componentes: http://forum.lazarus-ide.org/index.php/topic,38281.msg259512.html#msg259512
My New Project Lazarus REST/JSON Server/Client: http://forum.lazarus-ide.org/index.php/topic,36290.msg250356.html#msg250356
Youtube videos: https://www.youtube.com/watch?v=6FVXT36j-7U
Sourceforge: https://sourceforge.net/projects/rest-dataware-componentes/?source=directory

Alternatively, you can download and install REST Dataware using the Lazarus Online Package Manager. See screenshot.

Cheers,

JD
Title: Re: Noob and JSON
Post by: madref on July 17, 2018, 12:33:35 pm
Can you elaborate?
What exactly?
That TCP-connection
Title: Re: Noob and JSON
Post by: sash on July 17, 2018, 01:44:47 pm
That TCP-connection

Well, I don't know your exact configuration, nor your server provider's specifications. "Cloud" database is a marketing, not a strict technical term.

Personally, for a similar tasks I used plain-simple VPS, or even managed LAMP hosting services. In first case I just (installed and) configured (by myself) database server to accept specific external connections. In second case, such feature (if supported by provider) is available in their "Control Panel" or with explicit request to their support. Thus I was able to connect directly (see my above notes about vpn, ssh) from my favorite desktop database tool (or whichever application) to database server on remote host.
TinyPortal © 2005-2018