Noob and JSON lazarus / 16-Databases ---------------------------------------------------------------------- madref · 2018-07-08 06:32 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? ---------------------------------------------------------------------- taazz · 2018-07-08 07:56 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. ---------------------------------------------------------------------- madref · 2018-07-08 08:12 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 ---------------------------------------------------------------------- taazz · 2018-07-08 08:49 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. ---------------------------------------------------------------------- madref · 2018-07-08 09:31 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 ---------------------------------------------------------------------- taazz · 2018-07-08 09:36 Quote from: madref on July 08, 2018, 11:31:59 amAs 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? Quote from: madref on July 08, 2018, 11:31:59 amAll 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. ---------------------------------------------------------------------- madref · 2018-07-08 11:22 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 ---------------------------------------------------------------------- madref · 2018-07-08 11:30 So I have to create an interfase between the app and the web bases database ---------------------------------------------------------------------- madref · 2018-07-10 16:34 But how can I make that interface? ---------------------------------------------------------------------- Trenatos · 2018-07-10 18:24 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? ---------------------------------------------------------------------- mangakissa · 2018-07-11 06:36 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. ---------------------------------------------------------------------- madref · 2018-07-12 22:54 Quote from: Trenatos on July 10, 2018, 08:24:41 pmAre you trying to create a desktop application which can access your cloud-based database? YES.. Quote from: Trenatos on July 10, 2018, 08:24:41 pmWhat'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). ---------------------------------------------------------------------- denver · 2018-07-13 06:38 Quote from: madref on July 13, 2018, 12:54:24 amQuote from: Trenatos on July 10, 2018, 08:24:41 pmAre you trying to create a desktop application which can access your cloud-based database? YES.. Quote from: Trenatos on July 10, 2018, 08:24:41 pmWhat'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 ---------------------------------------------------------------------- Leledumbo · 2018-07-13 09:27 Server side: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i {$mode objfpc} uses  classes, fphttpapp, httpdefs, httproute, fpjson; procedure GimmeMaJSON(ARequest:TRequest; AResponse : TResponse);beginwith TJSONObject.Create do  try    // change these to take data from db or whatever source you have    Floats['f'] := 1.234;    Integers['i'] := 5678;    Strings['s'] := 'oh yes';    Arrays['a'] := CreateJSONArray([9,1,1]);    Objects['o'] := CreateJSONObject(['key','value','key again',12345]);    AResponse.Content := FormatJSON([foSingleLineArray,foSkipWhiteSpace]); // or simply AsJSON if beautiful formatting doesn't matter to you  finally    Free;  end;end; begin   HTTPRouter.RegisterRoute('/myjsondata',@GimmeMaJSON);   Application.Port := 9000;   Application.Initialize;   Application.Run;end. Client side (running on the same machine as the server): Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i {$mode objfpc} uses  fphttpclient, fpjson, jsonparser; const  ServerBaseURL = 'http://localhost:9000';var  JSONStr: String;  JSONObj: TJSONObject;  e: TJSONEnum;begin  JSONStr := TFPHTTPClient.SimpleGet(ServerBaseURL + '/myjsondata');  JSONObj := TJSONObject(GetJSON(JSONStr));  try    WriteLn(JSONObj.Floats['f']:1:6);    WriteLn(JSONObj.Integers['i']);  WriteLn(JSONObj.Strings['s']);    for e in JSONObj.Arrays['a'] do WriteLn(e.Value.AsInteger);    WriteLn(JSONObj.Objects['o'].Strings['key']);    WriteLn(JSONObj.FindPath('o.key again').AsInteger);  finally    JSONObj.Free;  end;end. ---------------------------------------------------------------------- Almir.Bispo · 2018-07-13 12:04 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 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 part 2https://www.youtube.com/watch?v=QmI3kIdPVK4 All docs and software on my blog for free http://adltecnologia.blogspot.com.br ---------------------------------------------------------------------- madref · 2018-07-13 13:24 This is still Abracadabra for me. Isn't their an easier way? ---------------------------------------------------------------------- mangakissa · 2018-07-14 06:59 You can never figure it out if you don't try. ---------------------------------------------------------------------- sash · 2018-07-14 08:08 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. ---------------------------------------------------------------------- Leledumbo · 2018-07-14 08:54 Quote from: madref on July 13, 2018, 03:24:08 pmThis 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. ---------------------------------------------------------------------- mangakissa · 2018-07-16 07:01 Quote from: sash on July 14, 2018, 10:08:26 amWhy 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. ---------------------------------------------------------------------- BeanzMaster · 2018-07-16 08:20 Hi This scheme is call "NoSQL Database" you can find many reference on web. ---------------------------------------------------------------------- sash · 2018-07-16 09:34 Quote from: mangakissa on July 16, 2018, 09:01:59 amThe problem relies on the cloud database. What's problem? Isn't TCP connection available? Even cheapest hosting providers allows that. ---------------------------------------------------------------------- taazz · 2018-07-16 09:39 Quote from: madref on July 13, 2018, 03:24:08 pmThis 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. ---------------------------------------------------------------------- mangakissa · 2018-07-16 14:33 Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function GetURLAsString(const aURL: string): string;var  lHTTP: TIdHTTP;begin  lHTTP := TIdHTTP.Create;  try    Result := lHTTP.Get(aURL);  finally  lHTTP.Free;  end;end; your result from de webserver could be JSON /XML /CSV or whatever you put in your html/php page. ---------------------------------------------------------------------- madref · 2018-07-16 23:09 Quote from: sash on July 16, 2018, 11:34:14 amQuote from: mangakissa on July 16, 2018, 09:01:59 amThe problem relies on the cloud database. What's problem? Isn't TCP connection available? Even cheapest hosting providers allows that. Can you elaborate? ---------------------------------------------------------------------- sash · 2018-07-17 08:05 Quote from: madref on July 17, 2018, 01:09:38 amCan you elaborate? What exactly? ---------------------------------------------------------------------- JD · 2018-07-17 10:18 Quote from: madref on July 13, 2018, 03:24:08 pmThis 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 ---------------------------------------------------------------------- madref · 2018-07-17 10:33 Quote from: sash on July 17, 2018, 10:05:07 amQuote from: madref on July 17, 2018, 01:09:38 amCan you elaborate? What exactly? That TCP-connection ---------------------------------------------------------------------- sash · 2018-07-17 11:44 Quote from: madref on July 17, 2018, 12:33:35 pmThat 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.