Recent

Author Topic: [Solved] Database in The Cloud  (Read 7954 times)

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Database in The Cloud
« Reply #15 on: November 29, 2017, 11:22:54 pm »
Let's say i want to stick to my plan and recreate my database in phpmyadmin.
How can i access this data?
And let's say that this are the names and logins.
Code: [Select]
Database: cloud_referee
Host: localhost
username: cloud_referee
password: JustAWord001
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

lainz

  • Hero Member
  • *****
  • Posts: 3304
    • Lainz
Re: Database in The Cloud
« Reply #16 on: November 29, 2017, 11:40:36 pm »
I have a PHP code like this to access a phpmyadmin database:
Code: PHP  [Select]
  1. <?PHP
  2. $root = $_SERVER['DOCUMENT_ROOT'];
  3. include($root . "/emulators/api/index.php");
  4.  
  5. header('Content-Type: application/json');
  6. $data = array();
  7. $tag = false;
  8. $search = false;
  9.  
  10. // Create connection
  11. $conn = new mysqli($servername, $username, $password, $dbname);
  12. // Check connection
  13. if ($conn->connect_error) {
  14.     die("Connection failed: " . $conn->connect_error);
  15. }
  16.  
  17. parse_str($_SERVER['QUERY_STRING']);
  18.  
  19. if ($tag) {
  20.     // single tag must match
  21.     $sql = "SELECT * FROM emulators WHERE tags LIKE '%" . $tag . "%' ORDER BY downloads DESC";
  22. } else if ($search) {
  23.     // each word must match
  24.     $search = explode(" ", $search);
  25.     $find = "";
  26.     $c = count($search);
  27.     for ($i = 0; $i < $c; $i++) {
  28.         if ($i < $c-1) {
  29.             $find = $find . " (name LIKE '%" . $search[$i] . "%' OR description LIKE '%" . $search[$i] . "%') AND";
  30.         } else {
  31.             $find = $find . " (name LIKE '%" . $search[$i] . "%' OR description LIKE '%" . $search[$i] . "%')";
  32.         }
  33.     }
  34.     $sql = "SELECT * FROM emulators WHERE" . $find;
  35. } else {
  36.     // everything else
  37.     $sql = "SELECT * FROM emulators WHERE 1 ORDER BY downloads DESC";
  38. }
  39.  
  40. $result = $conn->query($sql);
  41.  
  42. if ($result->num_rows > 0) {
  43.     // output data of each row
  44.     while($row = $result->fetch_assoc()) {
  45.         $row["tags"] = explode(",", $row["tags"]);
  46.         $data[] = $row;
  47.     }
  48. }
  49.  
  50. $conn->close();
  51. echo json_encode($data);
  52. ?>

This creates a JSON with the data, so you upload your .php file say to

url/api/getdata

Then you call that from JavaScript with a get('url/api/getdata') and you do whathever you want with the data.
« Last Edit: November 29, 2017, 11:43:52 pm by lainz »

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Database in The Cloud
« Reply #17 on: November 29, 2017, 11:51:37 pm »
Can't i access it directly from Lazarus?

You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Database in The Cloud
« Reply #18 on: November 30, 2017, 12:02:30 am »
Can't i access it directly from Lazarus?
No, it is insecure to do so and all server installation have taken steps to ensure that mysql is only accessible from the LAN that the web server is part of. You need to write some sort of service (or a middle tire if you will) to give access to the database.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

lainz

  • Hero Member
  • *****
  • Posts: 3304
    • Lainz
Re: Database in The Cloud
« Reply #19 on: November 30, 2017, 12:04:47 am »
Can't i access it directly from Lazarus?

Taaz is right
And i already give you th ecode.

Just do a get from Lazarus and parse the JSON.

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Database in The Cloud
« Reply #20 on: November 30, 2017, 12:10:04 am »
hmmm...complicated
...


Back to another thought:
Since my database is stored locally now.
How does lazarus access this database when i open it?
Does it load the complete database into memory?
Does it only load the part that is requested?
In short how does lazarus do it?


In other words what would happen if i put this file (database) on a certain spot on the internet and try to access that?
« Last Edit: November 30, 2017, 12:11:40 am by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Database in The Cloud
« Reply #21 on: November 30, 2017, 12:13:44 am »
hmmm...complicated
...


Back to another thought:
Since my database is stored locally now.
How does lazarus access this database when i open it?
Does it load the complete database into memory?
Does it only load the part that is requested?
In short how does lazarus do it?
It allows the library used for the database to decide that. In general for every sql based database it only loads what ever the sql requests from the server/database (what ever a TSQlquery control has in memory) . The rest are simple locked on disk from access in case of sqlite.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Database in The Cloud
« Reply #22 on: November 30, 2017, 12:20:00 am »
So it would be possible to download the entire database (<200Kb) into memory.
Or to keep the connection to that file open so that TSQLQuery can access it anytime.
Is that recommendable?
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

lainz

  • Hero Member
  • *****
  • Posts: 3304
    • Lainz
Re: Database in The Cloud
« Reply #23 on: November 30, 2017, 12:36:41 am »
So it would be possible to download the entire database (<200Kb) into memory.
Or to keep the connection to that file open so that TSQLQuery can access it anytime.
Is that recommendable?

I think you will get a good response with a good question.

What you want to achieve with all this? What are your goals?

What you will do with the database once it is fully loaded into memory?

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Database in The Cloud
« Reply #24 on: November 30, 2017, 12:59:33 am »
So it would be possible to download the entire database (<200Kb) into memory.
yes you can download the complete database in memory or temp files and 200KB is smaller than most html pages out there.
Or to keep the connection to that file open so that TSQLQuery can access it anytime.
Is that recommendable?
No. SQLite is a local database the file opened must be on the same computer the library is running. Once a connection is made to the database, the database is locked for all other connections. You need a single service that can accept multiple connections. You need to write a middle tire no way around it. The real question is why not share the database with your application or include it in the installation? If needed some kind of update procedure can be coded that will download updates from a server and apply them to a local database.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Database in The Cloud
« Reply #25 on: November 30, 2017, 08:58:47 am »
the purpose of this database is that i want to be able to access it anywhere in the world.
So where ever i am i want to access this wether i am on my laptop or iPad or iPhone.
And if i updated it on my iPad i want to see it on my laptop too.


i have build a referee tracking database that i want to access rink-side.
Discuss the game a referee did and give him or her a report, email it and being able to review it again when i am home.
When i am on the road i want to use my iPad and not my laptop.
When i am at home i want to use my laptop and not my iPad.


So that is why i want to make it 'mobile' by using 'the cloud'.
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Database in The Cloud
« Reply #26 on: November 30, 2017, 09:00:49 am »
Or is there another way to achieve this?
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Database in The Cloud
« Reply #27 on: November 30, 2017, 01:01:12 pm »
Or is there another way to achieve this?
create a small web page to work with. You can use brook or any pascal framework for that if you want, I would go with a more web available software though (php, asp.net etc).
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

lainz

  • Hero Member
  • *****
  • Posts: 3304
    • Lainz
Re: Database in The Cloud
« Reply #28 on: November 30, 2017, 03:04:54 pm »
Or is there another way to achieve this?

A website is the way to go, more easy than building a single app for desktop, another for tablet/mobile. The best is that is cross platform for every single device that can show a website.

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Database in The Cloud
« Reply #29 on: November 30, 2017, 03:47:43 pm »
ok...
So i need to do the following steps:
1. Create the database (almost done)
2. Create a website.
3. Create the interface
4. Create the desktop version
5. Create the iPad version.


Or am i forgetting something?

You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave