New here. Question about possible internet db updates?

Everything else that doesn't fall into one of the other PB categories.
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

New here. Question about possible internet db updates?

Post by HoosierDaddy »

Hi everyone.
I'm new here and haven't purchased PB yet, but have ordered a new computer / laptop and will buy PB when I receive the laptop if PB can fulfill my needs. Hence why I have some questions.

I have a database and website being hosted online.

I also have a need to update the database / Crud, but I want to use a windows app to do so via http. Is there a control as in VB to connect to the web via http?.

It's just a crud app. Is it possible to develop a window or windows that capture user input and then send it to the database by connecting to the database such as SQL lite or postgress or any DB via odbc if need be to update records using tcpip?


So, I'm imagining it as the user opens the compiled app, windows appear and the database info is fetched from online via http and some number of records populates controls on the app form and the user either updates, deletes, or inserts new records, clicks submit and the same is sent to the web database via http, and then the user can close the program.

I want to do it this way because I don't want to use PHP or anything else with a web front end for this.

Hope that was explained ok..

Thanks so much in advance, and best regards all.
Tim. AKA hoosierDaddy.
The heartland. Indiana. USA.
User avatar
the.weavster
Addict
Addict
Posts: 1531
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: New here. Question about possible internet db updates?

Post by the.weavster »

HoosierDaddy wrote: Wed Nov 23, 2022 1:02 am I also have a need to update the database / Crud, but I want to use a windows app to do so via http. Is there a control as in VB to connect to the web via http?.
There's not a control but there is a command, see HTTPRequest()
HoosierDaddy wrote: Wed Nov 23, 2022 1:02 am So, I'm imagining it as the user opens the compiled app, windows appear and the database info is fetched from online via http and some number of records populates controls on the app form and the user either updates, deletes, or inserts new records, clicks submit and the same is sent to the web database via http, and then the user can close the program.

I want to do it this way because I don't want to use PHP or anything else with a web front end for this.
I'd suggest JSON-RPC rather than accessing the database directly over the internet, database reads/writes would be handled server side. You could use PHP for that or PureBasic's CGI/FastCGI commands. Client side could still be a compiled app rather than a web front end (although you could use SpiderBasic for a web front end instead) but it would communicate with the server using JSON over https.
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: New here. Question about possible internet db updates?

Post by HoosierDaddy »

Hi.

Thank you for the reply. I know PHP enough I could do the front end in that langauge, but the small user base hates the internet front ends, and wants to insure no one else, especially in the web has access to an app or program that would alter the DB. Otherwise I would just wrote the whole thing in PHP and use sqlLite or postgress as the backend.
I don't know enough about Json. I'm retired now and used to be an ASP / VB SQL server developer, so hence another reason to use purebasic. 👍
So I take it there are ways to use CGI and PB as the front end for crud apps to a web hosted database via http? If I understand what you were suggesting? Is there an internet controls or some code examples somewhere? I didn't see any with the online docs, but I may have just missed them?
Thanks for the reply. It's much appreciated.
Best regards
Tim
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: New here. Question about possible internet db updates?

Post by HoosierDaddy »

Hi. Yeah. I know the request server object. I used to be a classic asp programmer for a web and media company.
I just don't know how to comment using it in PB. I take it there is documentation on the same?
I was always told chi is bad back in the day because at least using IIS on windows server it requires a lot of server resources for each user, so I never used chi. I know it was popular on Linux with perl back in the day, etc.
I looked at the PB online docs and didn't see anything about fast chi. Is there such a critter in the docs?
I will check out spider basic. Does it use a JavaScript backend framework to do server side DB work?
Thanks so much in advance.


the.weavster wrote: Wed Nov 23, 2022 10:15 pm
HoosierDaddy wrote: Wed Nov 23, 2022 1:02 am I also have a need to update the database / Crud, but I want to use a windows app to do so via http. Is there a control as in VB to connect to the web via http?.
There's not a control but there is a command, see HTTPRequest()
HoosierDaddy wrote: Wed Nov 23, 2022 1:02 am So, I'm imagining it as the user opens the compiled app, windows appear and the database info is fetched from online via http and some number of records populates controls on the app form and the user either updates, deletes, or inserts new records, clicks submit and the same is sent to the web database via http, and then the user can close the program.

I want to do it this way because I don't want to use PHP or anything else with a web front end for this.
I'd suggest JSON-RPC rather than accessing the database directly over the internet, database reads/writes would be handled server side. You could use PHP for that or PureBasic's CGI/FastCGI commands. Client side could still be a compiled app rather than a web front end (although you could use SpiderBasic for a web front end instead) but it would communicate with the server using JSON over https.
User avatar
mk-soft
Always Here
Always Here
Posts: 5313
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: New here. Question about possible internet db updates?

Post by mk-soft »

I haven't done much with CGI yet.
But Purebasic supports CGI and FastCGI. Have a look at the Purebasic help.

The web server must support CGI. Also, the CGI program must be compiled under the appropriate OS. Window, Linux x86 or x64.

Since Purebasic now also supports Raspberry PI, Linux Arm32 and Arm64. I can try this out on my NAS.
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: New here. Question about possible internet db updates?

Post by HoosierDaddy »

Thanks. I will check out the docs again. .
User avatar
the.weavster
Addict
Addict
Posts: 1531
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: New here. Question about possible internet db updates?

Post by the.weavster »

HoosierDaddy wrote: Fri Nov 25, 2022 12:35 pmI know PHP enough I could do the front end in that langauge
PHP is fine for the back end but I'd suggest not using it to generate HTML but rather to send data that's been serialized as JSON.
HoosierDaddy wrote: Fri Nov 25, 2022 12:35 pmI don't know enough about Json.
JSON isn't scary, you could think of it as a textual representation of a structure.

Personally I like JSON-RPC for an API because coming from a desktop programming background it feels much more like a standard function call. So let's have a look how that works ...

On the client side you create a JSON object which specifies the API method you'd like to call and the parameters you wish to pass to it, then POST that to your PHP script. You can use either PureBasic or SpiderBasic to do this:

Code: Select all

{
"method": "multiplyNumbers",
"params": [2, 2],
"id": "ABC123" // can be used to match requests to responses
}
.
Then on the server side you have a router script that makes sure the requested method gets called. This PHP code is straight off the top of my head and is untested but even if I've made an error it should still give you the idea. I have omitted any exception checking as I'm just demonstrating the concept:

Code: Select all

<?php

function sendError($code, $msg, $id) {
    $err = array();
    $err['origin'] = 1;
    $err['code'] = $code;
    $err['message'] = $msg;
    $resp = array();
    $resp['result'] = null;
    $resp['error'] = $err;
    $resp['id'] = $id;
    exit(json_encode($resp));
}

function sendResponse($response, $id) {
    $resp = array();
    $resp['result'] = $response;
    $resp['error'] = null;
    $resp['id'] = $id;
    exit(json_encode($resp));
}

function multiplyNumbers($params, $id) {
    sendResponse($params[0] * $params[1], $id);
}

$rqb = file_get_contents('php://input');
$rqj = json_decode($rqb, true);
switch($rqj['method']) {
    case 'multiplyNumbers': multiplyNumbers($rqj['params'], $rqj['id']); break;
    default:
        sendError(4, 'method not found: '.$rqj['method'], $rqj['id']);
}

?>
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: New here. Question about possible internet db updates?

Post by HoosierDaddy »

Thank you so much. I love it.
Really appreciate it. Thanks again. Big help. I will check out more JSON tutorials because you've peaked my interest. 🤘♥️🤘
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: New here. Question about possible internet db updates?

Post by TI-994A »

HoosierDaddy wrote: Wed Nov 23, 2022 1:02 amI have a database and website being hosted online.

I also have a need to update the database / Crud, but I want to use a windows app to do so via http. Is there a control as in VB to connect to the web via http?
Welcome to PureBasic, Tim. Perhaps, take a look at this:

> Remote MySql
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: New here. Question about possible internet db updates?

Post by HoosierDaddy »

Thank you. I will check it out. Really appreciate it.
Tim
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: New here. Question about possible internet db updates?

Post by HoosierDaddy »

I looked at the examples and they were very good, especially the mySql example. I didn't see how to connect to an existing sqlLite database remotely though in that example from the link provided on the mySql example page. I assume one could connect to any database remotely supported by PB and ODBC or a DSN? I'm considering using SQL Lite because this app I am going to build will never have more than 2 connections or two users, so SQL lite should be sufficient. Plus, it's fast and dependable in my experience with it..
Thanks again for all the help. Really appreciated.
Tim..
lesserpanda
User
User
Posts: 65
Joined: Tue Feb 11, 2020 7:50 am

Re: New here. Question about possible internet db updates?

Post by lesserpanda »

OK, here's how I wrote an app to deal with remote MYSQL DBs without mucking around with making HTTP calls and having another app in the backend proxying calls from front end app.

2 ways I did it without writing server side app
1. One is to have VM like right next to my DB and in the same network. So my PB app will just connect easily.
2. Either do a VPN lan to lan or else use a ssh client like bitvise to create port forwardings to the mysql server. So to the Mysql server, it's just a local app connecting. Don't even need to do grants and remote user etc.

If you want to do it the normal way, then connectivity either with HTTP as above or Websocket to a PB backend running either CGI, or acting as a Websocket server.

Front end, PB just makes the UI part easier than writing all the HTML and JS code with React/Angular etc.

Just my 2 cents and hope it helps.
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: New here. Question about possible internet db updates?

Post by HoosierDaddy »

Thanks. I really appreciate it
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: New here. Question about possible internet db updates?

Post by Marc56us »

HoosierDaddy wrote: Mon Nov 28, 2022 2:09 pm ... I'm considering using SQL Lite because this app I am going to build will never have more than 2 connections or two users, ...
Many concurrent writers? → choose client/server

If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.

SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency than many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will.
Source: https://www.sqlite.org/whentouse.html
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: New here. Question about possible internet db updates?

Post by HoosierDaddy »

Hi. Thanks. Yeah. There are only 2 people who will use the app and even then only 1 at a time . I guess both could possibly use it at the same time, but I highly doubt it and therefore I suspect SQL lite will work for what I need. The DB will only have about 8 ton10 tables at most.
I might end up using mySql or Postgress since those are also available at my webhostinh company for free.
Thanks so much.
Post Reply