Database on the Internet lazarus / 16-Databases ---------------------------------------------------------------------- cappe · 2015-11-07 14:17 I have to make an application in which the database is on a server on the Internet. Both the client application and server should be written in Lazarus. What do you advise me to use? ---------------------------------------------------------------------- HeavyUser · 2015-11-07 14:19 http://wiki.lazarus.freepascal.org/Web_Service_Toolkit ---------------------------------------------------------------------- shobits1 · 2015-11-07 14:44 take a look at mORMot http://synopse.info/fossil/wiki/Synopse+OpenSource QuoteSynopse mORMot is an Open Source Client-Server ORM SOA MVC framework for Delphi 6 up to Delphi 10 Seattle and FPC, targeting Win/Linux for the server, and any platform for clients (including mobile or AJAX). The main features of mORMot are therefore: ORM/ODM: objects persistence on almost any database (SQL or NoSQL);SOA: organize your business logic into REST services;Clients: consume your data or services from any platform, via ORM classes or SOA interfaces;Web MVC: publish your ORM/SOA process as responsive Web Applications. ---------------------------------------------------------------------- christian1987 · 2015-11-07 15:52 The only database I've got working is FireBird. Lazarus has a great driver for it. Once you get the DLL's right, remember to set network time-outs, because they should be handled. ---------------------------------------------------------------------- cappe · 2015-11-07 17:11 Has anyone ever tried mORMot? ---------------------------------------------------------------------- JD · 2015-11-07 18:11 Quote from: cappe on November 07, 2015, 06:11:21 pmHas anyone ever tried mORMot? I've used parts of mORMot. It is a great tool but If you are not used to the ORM development model, it may frustrate you. That said, you don't need it to do what you want. Your client, server & database can be on the same computer or anywhere in the world. What matters is their IP addresses and user permissions on the computers where they are. You can easily connect to remote databases once you get your connection string & IP addresses right. All modern RDMS have this feature so choose one you are comfortable with and go ahead. You can roll your own solution or use frameworks like Brook, mORMot, CeosMW etc. Take a look at the CeosMW framework's client & server videos to give you some ideas http://jbsolucoes.net/ceosmw/ JD ---------------------------------------------------------------------- Leledumbo · 2015-11-07 18:54 I just suggest writing a webservice over the remote database. Avoid direct connection as much as possible, there are too much security to compromise with it. What to use to implement is up to you, it depends on your needs. For me, a self written one with brook + fpjson is enough. ---------------------------------------------------------------------- DonAlfredo · 2015-11-07 19:15 I am using mORMot for nearly every application I write that needs persistent storage. It is so easy to use. But it needs some extra effort to get used to and to learn the ORM methods. Try it ... its rewarding ! ---------------------------------------------------------------------- Graeme · 2015-11-07 23:26 Quote from: cappe on November 07, 2015, 03:17:15 pmWhat do you advise me to use? Take a look at tiOPF and its Remote Persistence Layer. With tiOPF you have many choices - and switching between the choices doesn't affect your application code. eg: You can choose what database components you want to use. SqlDB, IBX, Zeos etcChoose which database you want to communicate with. Firebird, SQL Server, Oracle, PostgreSQL, MySQL, XML etc.Switching between a Client/Server model (used over LAN's) to 3-tier model (primarily used over WAN, but LAN works too) is simply a change in one compiler define and a recompile of your application. NO other application code needs changing. tiOPF includes the Application Server (with embedded web server built in) for you, which is implemented with Indy and uses HTTP for remote communications. tiOPF has been used in commercial production software since 1999 and is still going strong. It has so many useful features, I even use it for non-Database applications too. There also consists 1900+ unit tests that are run every 3 hours 24/7 to make sure the framework is always in a stable state. It has been tested on Windows, Linux, FreeBSD and OSX. http://tiopf.sourceforge.net http://wiki.freepascal.org/tiOPF ---------------------------------------------------------------------- shobits1 · 2015-11-08 00:10 what's the best tiOPF or mORMot. I know that mORMot has extensive documentation but also there are some features that doesn't run on fpc; but considering performance and feature reach (in fpc) whose the best. ---------------------------------------------------------------------- Graeme · 2015-11-08 01:05 Quote from: shobits1 on November 08, 2015, 01:10:43 amwhat's the best tiOPF or mORMot. To be honest, I've never used mORMot or had the need for it I guess, so I can't comment on a comparison between the two frameworks. I can simply say that tiOPF supports Delphi and FPC. At one point it supported Kylix 3 too. It has some custom tiOPF components for VCL and LCL, but the preferred method of hooking up a graphical user interface is via the Model-GUI-Mediator (MGM) mediators. MGM has support for VCL, LCL, fpGUI and FMX. I can also add that tiOPF also has extensive Class API documentation (see the tiOPF homepage), numerous HOWTO and article pages on it's website. There are some YouTube tutorials too. tiOPF also includes a large amount of demos - tuned to be very specific to what they demonstrate so as to make learning easier. There are some larger "more real-world like" demos too. There is also a separate repository with utility applications for use with tiOPF. eg: a SQLEditor which can generate tiOPF source code based on SQL queries, tiOPFMapper which can generate full Business Object and SQL visitor units based on mappings defined in XML, Binary Deployment/Update for LAN based application deployment say in a large company etc. For tiOPF support, there are NNTP based newsgroups with a HUGE message base since 2001 (messages never expire). This is a gold mine of knowledge. I always recommend developers set their news clients to download the whole history, thus enabling full message body searching. http://tiopf.sourceforge.net/Support.shtml And like I said, we have a massive test suite which runs every 3 hours non-stop. Test results are automatically published to a website for review. http://geldenhuys.co.uk/tiopf/unittests/freebsd64.html When I decide on new technology or frameworks, I like to know all my choices. I then spend some time (2 weeks to a month - time permitting) learning and testing each. I found this the best way to make a decision, and also what I recommend to the original poster. ---------------------------------------------------------------------- Dr.Theopolis · 2015-11-08 01:20 What I have always wanted but have never found is a way to connect my application running at home to a MySQL database running on my shared web hosting server over http with a php script in the middle. That is what I want; it will always work no matter what cheapo web hosting service I use. Does anyone know of an existing solutions for that? ---------------------------------------------------------------------- DonAlfredo · 2015-11-08 06:31 @Dr.Theopolis I do not know if your MySQL server itself is exposed to the outside world, but if so, you can use ZEOS and either tiOPF or mORMot to connect to it very easy. At the moment, I am developing an app that does just that. If your DB is only accessible through PHP, you should use a PHP REST framework like SLIM. After proper setup, you can use any REST client to access your data. This is a necessary setup when you want to connect to a DB server through Delphi Android, because Delphi/Android/FireMonkey itself cannot connect directly with MySQL under Android without the use of external components. REST makes this easy. @shobits1 IMHO, there is no best ORM / Object Persistent framework, there are only best-suited frameworks with more or less features - documentation - learning curves. I personally like mORMot, and did help to port it to FPC. Due to this, I know mORMot very well. And it suits my neeeds. ---------------------------------------------------------------------- valdir.marcos · 2015-11-08 06:48 Quote from: Dr.Theopolis on November 08, 2015, 02:20:07 amWhat I have always wanted but have never found is a way to connect my application running at home to a MySQL database running on my shared web hosting server over http with a php script in the middle. That is what I want; it will always work no matter what cheapo web hosting service I use. Does anyone know of an existing solutions for that? You can easily create webservices on cheap host companies using php and mysql. Then you can consume those webservices using Lazarus. You can start here: http://forum.lazarus.freepascal.org/index.php?topic=24005.0 This book should introduce you in the php webservices world: PHP Web Services - APIs for the Modern Web By Lorna Jane Mitchell Publisher: O'Reilly Media Final Release Date: April 2013 Pages: 118 http://shop.oreilly.com/product/0636920028291.do ---------------------------------------------------------------------- Leledumbo · 2015-11-08 08:30 Quote from: Dr.Theopolis on November 08, 2015, 02:20:07 amWhat I have always wanted but have never found is a way to connect my application running at home to a MySQL database running on my shared web hosting server over http with a php script in the middle. That is what I want; it will always work no matter what cheapo web hosting service I use. Does anyone know of an existing solutions for that? Webservice as I state above. The protocol is up to you, but JSON based REST web services are so damn popular nowadays, though SOAP services still exist alongside. You don't connect directly to the MySQL, but the PHP script will and return data as you requested. If your hosting supports at least CGI, using Pascal on both sides would be easier for maintenance. ---------------------------------------------------------------------- valdir.marcos · 2015-11-08 14:32 Quote from: Leledumbo on November 08, 2015, 09:30:53 amWebservice as I state above. The protocol is up to you, but JSON based REST web services are so damn popular nowadays, though SOAP services still exist alongside. I agree. The book I mentioned above introduces RPC and SOAP, but mainly it introduces JSON, XML and REST. It's a book for beginners on webservices in PHP. Quote from: Leledumbo on November 08, 2015, 09:30:53 amIf your hosting supports at least CGI, using Pascal on both sides would be easier for maintenance. Here comes the problem. Cheap hosting usually does not support CGIs of any kind, that's why we are forced to use a solution outside Lazarus/Free Pascal. This solution usually goes on PHP. ---------------------------------------------------------------------- shobits1 · 2015-11-08 19:06 @DonAlfredo when I said best, I meant the flexibility, performance, crossplatform, features, documentation, design, stability under stress... and anything that can be plus to the framework. Anyway I'm planning to develop a client/server software for the faculty I work in, the main objective is to help administration personnel and teachers to track the scores and records (etc...) of each student and easify the processes of registration and orientation. also there should be access level control (not all users have equal privilege and teachers can only see the students they teach). after that (secondary objective) I plan to deploy web interface which students can access to track their own records and see latest announcement of the faculty or the department they belong to. Ultimately the software would grow to be university management system of some sort. Now based on your experience should I go tiopf or mormot (I didn't use any of them so I can't just decide). also I read that mormot don't handle RTTI under FPC correctly or FPC don't (I don't remember), what are the affected features. since reports will be major part of the software, how easy to use mormot reporting system (pdf generator); is tiopf has similaire feature; and if I want to use say lazreport how do I do it. thanks ---------------------------------------------------------------------- DonAlfredo · 2015-11-08 20:47 AFAIK (but I am also quite sure about it), mORMot only needs (the new) RTTI when using interfaces for client - server communication. My apps use simple CRUD methods, with some specialized SQL. No RTTI is needed in this case. If you want to start with mORMot, just start with the samples. These samples will lead the way. Many users of mORMot start with a sample to build there own app. Samples 1,2 26 and 27 are very informative. And, if you want to use the advanced RTTI, just install FPC with RTTI from the special RTTI branch. You can use fpcup e.g.: fpcup --fpcURL="RTTI" --installdir="C:\fpcrtti" ---------------------------------------------------------------------- Graeme · 2015-11-09 00:45 Quote from: shobits1 on November 08, 2015, 08:06:38 pmNow based on your experience should I go tiopf or mormot (I didn't use any of them so I can't just decide). The software you described is typical admin/schedule type software. I have written three such systems in the past all using tiOPF. One of those are used in a franchisee environment with 2500 franchisees. The system can be deployed in a Client/Server or 3-tier environment - simply by me changing 1 compiler define. Because our business logic and UI layers were clearly separated, we could use the necessary business objects to build a web front-end too. One of tiOPF's larger demos show such a setup. Client/Server and 3-tier. Then also shows the same client application implemented as a Console, VCL, fpGUI and Web app. Quotesince reports will be major part of the software, how easy to use mormot reporting system (pdf generator); is tiopf has similaire feature; and if I want to use say lazreport how do I do it. Reporting is not a problem either. tiOPF includes its own (optional) reporting based or RTF template files. Write the report template using OpenOffice, LibreOffice or MS Word - in a .RTF file. The report engine then translates that into a full report. We used OpenOffice to display the report too - in a read-only mode which was very nice actually, because OpenOffice hides all ending functionality so it looks like a "print preview" screen. You can use other reporting too. After years of using the RTF reporting, we switched to PDF reporting - functionality built into fpGUI. TDataset based reporting tools can also be used, as tiOPF supplies a TtiDataset class which translates Objects into a compatible TDataset. I've never personally used this before, but know of many other that do use this - especially for reporting. ---------------------------------------------------------------------- Dr.Theopolis · 2015-11-09 02:27 Quote from: Dr.Theopolis on November 08, 2015, 02:20:07 amWhat I have always wanted but have never found is a way to connect my application running at home to a MySQL database running on my shared web hosting server over http with a php script in the middle. That is what I want; it will always work no matter what cheapo web hosting service I use. Does anyone know of an existing solutions for that? When I ask about an existing solution, I appreciate the information that was provided about how one would go about making a complete solutions. But what I am asking is if someone has already done it. Year after year I think about sitting down and writing the components and php script that would go together to accomplish what I described. I already know how to do it. It is just hard to believe nobody has already done it. ---------------------------------------------------------------------- valdir.marcos · 2015-11-09 03:08 Quote from: Dr.Theopolis on November 09, 2015, 03:27:29 amWhen I ask about an existing solution, I appreciate the information that was provided about how one would go about making a complete solutions. But what I am asking is if someone has already done it. Year after year I think about sitting down and writing the components and php script that would go together to accomplish what I described. I already know how to do it. It is just hard to believe nobody has already done it. Yes, I have already done it using PHP on the server side (webservices on some hosting companies) and Lazarus (Free Pascal) and Android (Java) on the client side consuming those webservices. ---------------------------------------------------------------------- Leledumbo · 2015-11-09 05:11 Quote from: Dr.Theopolis on November 09, 2015, 03:27:29 amWhen I ask about an existing solution, I appreciate the information that was provided about how one would go about making a complete solutions. But what I am asking is if someone has already done it. Year after year I think about sitting down and writing the components and php script that would go together to accomplish what I described. I already know how to do it. It is just hard to believe nobody has already done it. I believe many of us has already done it, not necessarily Pascal client with PHP server, though. When developing/consuming webservices, protocols matter more than language. I have interfaced Pascal with some web services (Twitter, Plurk, Instagram, etc.) I don't even know what language is behind the service. ---------------------------------------------------------------------- Dr.Theopolis · 2015-11-09 06:44 Quote from: valdir.marcos on November 09, 2015, 04:08:15 amQuote from: Dr.Theopolis on November 09, 2015, 03:27:29 amWhen I ask about an existing solution, I appreciate the information that was provided about how one would go about making a complete solutions. But what I am asking is if someone has already done it. Year after year I think about sitting down and writing the components and php script that would go together to accomplish what I described. I already know how to do it. It is just hard to believe nobody has already done it. Yes, I have already done it using PHP on the server side (webservices on some hosting companies) and Lazarus (Free Pascal) and Android (Java) on the client side consuming those webservices. Did you make TDatabase and TSQLQuery descendents that connect to database on a remote server over http? ---------------------------------------------------------------------- valdir.marcos · 2015-11-09 14:49 Quote from: Dr.Theopolis on November 09, 2015, 07:44:22 amQuote from: valdir.marcos on November 09, 2015, 04:08:15 amYes, I have already done it using PHP on the server side (webservices on some hosting companies) and Lazarus (Free Pascal) and Android (Java) on the client side consuming those webservices. Did you make TDatabase and TSQLQuery descendents that connect to database on a remote server over http? No, it's not possible (or is too effortful) to connect Lazarus' datasets to PDO's PHP. Yes, I have made a straight connection from Lazarus' datasets to the database server via http, but the result was not good: security challenges made the project more expensive, slowness due to high latency of http increased costumers' complains too much and higher network traffic made the project more expensive. That database server (web hosting company) did not accept Pascal CGIs. See the disadvantages of data aware controls on Internet here: http://tiopf.sourceforge.net/Doc/Concepts/1_WhatIsAnOPF.shtml