Reading from a PostgreSQL DB

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
cmhjon
Posts: 94
Joined: Tue Aug 04, 2015 11:55 am

Reading from a PostgreSQL DB

Post by cmhjon » Thu Feb 18, 2021 2:01 pm

Hi everyone,

We purchased a system that uses a PostgreSQL DB. I would like to connect to it so that I can read data from it in order to populate LC text fields in my app with the data read from the DB. I have established an ODBC connection to the DB using the revOpenDatabase command which works ok but that's as far as I've got.

Here's what I am looking to do:

1. Type a job ID into an LC text field and click a button.
2. The button script would connect to the DB, read from various tables/fields associated with the job ID entered in step 1.
3. Using the data read from the DB in step 2, populate various LC text fields.
4. Close the DB connection.

As I have no experience with this. Can someone provide some script examples of how to achieve what I am looking for?

Thank you,
Jon :)

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 8537
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Reading from a PostgreSQL DB

Post by FourthWorld » Thu Feb 18, 2021 6:10 pm

LiveCode includes a PostgreSQL which should perform a little better than ODBC.

Is the DB server outside your office? That is, do you connect to it through the open Internet?
Richard Gaskin
LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

cmhjon
Posts: 94
Joined: Tue Aug 04, 2015 11:55 am

Re: Reading from a PostgreSQL DB

Post by cmhjon » Thu Feb 18, 2021 6:24 pm

Hi FourthWorld,

The DB is on an in-house server so no internet necessary.

Side question: when you said, "LiveCode includes a PostgreSQL...", does this mean that I don't have to use the 3rd party ODBC driver I installed? If so, that would be awesome.

Thank you,
Jon :)

liveme
Posts: 215
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: Reading from a PostgreSQL DB

Post by liveme » Thu Feb 18, 2021 6:46 pm

Oh, so all the jobs DB content are stored locally in one server of your company private network .?
I'm not sure why you did need to buy it, Lamp/Xamp allows you to install Mysql/PG for free in most case...
They can be used as free ,(I guess) but you would need to get the dedicated server hardware for sure. :mrgreen:

Do you really need to use Postgresql ?
*Mysql can be much simpler to set up.
What is the maximum number of users that could connect to your DB to run searches in an hour or one day ?
Are your users all connected in the same local network or they are spread in various locations ?
Is your company network well secured ? You could run
some direct access queries to the DB with a bit of little encryption on top, that is a superfast method. Few ms.
(OBDC is okay on intra network though)

cmhjon
Posts: 94
Joined: Tue Aug 04, 2015 11:55 am

Re: Reading from a PostgreSQL DB

Post by cmhjon » Thu Feb 18, 2021 6:56 pm

Hi liveme,

Yes, everything is stored on a local server in-house. Unfortunately, we have no control over which DB technology the software developer uses. This was the DB of their choice. We bought their system, the DB it uses was part of their designed configuration.

Thank you,
Jon :)

Klaus
Posts: 12285
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Reading from a PostgreSQL DB

Post by Klaus » Thu Feb 18, 2021 6:57 pm

Hi Jon,
cmhjon wrote:
Thu Feb 18, 2021 6:24 pm
...
Side question: when you said, "LiveCode includes a PostgreSQL...", does this mean that I don't have to use the 3rd party ODBC driver I installed?
yes, check the "revopendatabase" entry in the dictionary!

Code: Select all

...
put revOpenDatabase("postgresql", more params here...) into tConnID
...
The dictionary is better than its reputation, believe me! 8)


Best

Klaus

liveme
Posts: 215
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: Reading from a PostgreSQL DB

Post by liveme » Thu Feb 18, 2021 6:59 pm


Klaus
Posts: 12285
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Reading from a PostgreSQL DB

Post by Klaus » Thu Feb 18, 2021 7:05 pm

liveme wrote:
Thu Feb 18, 2021 6:59 pm
Have you read this yet ?
https://lessons.livecode.com/m/4071/l/7 ... l-database
Have you read this?
cmhjon wrote:
Thu Feb 18, 2021 6:56 pm
Yes, everything is stored on a local server in-house. Unfortunately, we have no control over which DB technology the software developer uses. This was the DB of their choice. We bought their system, the DB it uses was part of their designed configuration.

liveme
Posts: 215
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: Reading from a PostgreSQL DB

Post by liveme » Thu Feb 18, 2021 7:12 pm

I'm running queries against a Mysql DB (as a start) though Postgres is my final choice too...so i havent modified my queries yet to work on PG.
Since I'm relying on SQL to build those queries, etc..its going to be quite strait forward anyway...

Would you go for a web browsing solution - build once, works everywhere ? or for a desktop app Mac) Windows Desk version, more coding ?

cmhjon
Posts: 94
Joined: Tue Aug 04, 2015 11:55 am

Re: Reading from a PostgreSQL DB

Post by cmhjon » Thu Feb 18, 2021 8:20 pm

Hi liveme,

This will be built for Mac/Windows and never for a web browser or mobile device. Theoretically, once built, it should work anywhere in the building provided that it can "see" the server where the DB resides. I intend to add a ping routine to a preOpenStack handler and if the server can't be found, notify the user and close down.

Best regards,
Jon :)

liveme
Posts: 215
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: Reading from a PostgreSQL DB

Post by liveme » Thu Feb 18, 2021 8:58 pm

sounds fair, a great idea about the ping !
sent you a PM. ready to run right now for the first script.

cmhjon
Posts: 94
Joined: Tue Aug 04, 2015 11:55 am

Re: Reading from a PostgreSQL DB

Post by cmhjon » Wed Mar 03, 2021 10:43 pm

A quick thank you to @liveme for his help! He provided the SQL statements I needed to get my app working which it is now.

Best regards,
Jon :)

liveme
Posts: 215
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: Reading from a PostgreSQL DB

Post by liveme » Wed Mar 03, 2021 11:09 pm

Thanks to some good samples out there about SQL- already using that team search result !

Code: Select all

put "SELECT CompanyBrand FROM Clients LEFT JOIN Task ON Client.MId = Task.MId WHERE ClientID=" & tSearchClientID & "LIMIT 1" into tSQL 
Glad it worked ! :wink:
i.e. : https://kb objectrocket com/postgresql/postgresql-natural-join-operation-1249
*add dots

Post Reply

Return to “Databases”