Reading from a PostgreSQL DB
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
Reading from a PostgreSQL DB
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
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
-
- VIP Livecode Opensource Backer
- Posts: 9852
- Joined: Sat Apr 08, 2006 7:05 am
- Location: Los Angeles
- Contact:
Re: Reading from a PostgreSQL DB
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?
Is the DB server outside your office? That is, do you connect to it through the open Internet?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
Re: Reading from a PostgreSQL DB
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
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
Re: Reading from a PostgreSQL DB
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.
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)
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.
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)
Re: Reading from a PostgreSQL DB
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
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
Re: Reading from a PostgreSQL DB
Hi Jon,
The dictionary is better than its reputation, believe me!
Best
Klaus
yes, check the "revopendatabase" entry in the dictionary!
Code: Select all
...
put revOpenDatabase("postgresql", more params here...) into tConnID
...
Best
Klaus
Re: Reading from a PostgreSQL DB
Have you read this yet ?
https://lessons.livecode.com/m/4071/l/7 ... l-database
https://lessons.livecode.com/m/4071/l/7 ... l-database
Re: Reading from a PostgreSQL DB
Have you read this?liveme wrote: ↑Thu Feb 18, 2021 6:59 pmHave you read this yet ?
https://lessons.livecode.com/m/4071/l/7 ... l-database
Re: Reading from a PostgreSQL DB
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 ?
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 ?
Re: Reading from a PostgreSQL DB
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
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
Re: Reading from a PostgreSQL DB
sounds fair, a great idea about the ping !
sent you a PM. ready to run right now for the first script.
sent you a PM. ready to run right now for the first script.
Re: Reading from a PostgreSQL DB
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
Best regards,
Jon
Re: Reading from a PostgreSQL DB
Thanks to some good samples out there about SQL- already using that team search result !
Glad it worked !
Code: Select all
put "SELECT CompanyBrand FROM Clients LEFT JOIN Task ON Client.MId = Task.MId WHERE ClientID=" & tSearchClientID & "LIMIT 1" into tSQL
*add dotsi.e. : https://kb objectrocket com/postgresql/postgresql-natural-join-operation-1249