Reading from a PostgreSQL DB

[cmhjon]

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]

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?

[cmhjon]

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]

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)

[cmhjon]

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]

Hi Jon,

...
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!


Best

Klaus

[liveme]

Have you read this yet ?
https://lessons.livecode.com/m/4071/l/7 ... l-database

[Klaus]

Have you read this yet ?
https://lessons.livecode.com/m/4071/l/7 ... l-database

Have you read this?

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]

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]

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]

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

[cmhjon]

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]

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 !

i.e. : https://kb objectrocket com/postgresql/postgresql-natural-join-operation-1249

*add dots