1084

Connection with POSTGRESQL

Showing 1 to 10 of 16 comments.

Comments for “Connection with POSTGRESQL”
 

Posted by MFahad on Monday 26th September 2022 at 19:17 GMT

Hi there!
can anyone please help me how can I connect a trongate web app with postgresql instead of MySQL ?

MFahad

User Level: Guest

Date Joined: 30/07/2022

Posted by djnordeen on Monday 26th September 2022 at 19:37 GMT

Hello,
I believe that you have to create your own module for postgresql.
Here is the a link to php manual.
https://www.php.net/manual/en/pgsql.examples-basic.php
Early Adopter

djnordeen

User Level: Early Adopter

Date Joined: 20/08/2021

Posted by MFahad on Wednesday 28th September 2022 at 07:01 GMT

I have figured out a way to connect my web app with postgresql.
I had to simply changed the the driver name from my sql to pgsql in Model.php file
        $dsn = 'mysql:host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->dbname;

        $dsn = 'pgsql:host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->dbname;

MFahad

User Level: Guest

Date Joined: 30/07/2022

Posted by djnordeen on Wednesday 28th September 2022 at 09:08 GMT

That's great!
Just remember that when you update your trongate app, The model file will revert back to mysql.
So save a copy of your changes.
Dan
Early Adopter

djnordeen

User Level: Early Adopter

Date Joined: 20/08/2021

Posted by DaFa on Thursday 29th September 2022 at 08:06 GMT

** edited
I would suggest creating a pull request so Trongate can switch database use but it's not as easy as just creating a connection. There would have to be a lot of work put in to make this work in the framework and also the Desktop app...

for example, in the database.php:
<?php
//Database settings
define('HOST', '127.0.0.1');
define('PORT', '3306');
define('USER', 'root');
define('PASSWORD', '');
define('DATABASE', 'a1');
/**
 * Defining the database type.
 * 
 * Allowed types
 * 'mysql', 'pgsql', etc...
 * 
 */
define('DB_TYPE', 'mysql');


then in model.php
<?php
class Model {

    private $host = HOST;
    private $port = PORT;
    private $user = USER;
    private $pass = PASSWORD;
    private $dbname = DATABASE;
    private $dbtype = DB_TYPE;

    private $dbh;
    private $stmt;
    private $error;
    private $debug = false;
    private $query_caveat = 'The query shown above is how the query would look before binding.';
    private $current_module;

    public function __construct($current_module = NULL) {

        if (DATABASE == '') {
            return;
        }

        $this->port = (defined('PORT') ? PORT : '3306');
        $this->current_module = $current_module;

        $dsn = DB_TYPE . ':host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->dbname;
        $options = array(
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        );

        try{
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
        } catch(PDOException $e){
            $this->error = $e->getMessage();
            echo $this->error; die();
        }

    }


Obviously, you would add the logic to include SQLlite as that one is
$dsn = new PDO('sqlite:' . APPPATH . 'example.db');

PDO supports 12 different databases:

MySQL
PostgreSQL
Oracle
Firebird
MS SQL Server
Sybase
Informix
IBM
FreeTDS
SQLite
Cubrid
4D

This comment was edited by DaFa on Sunday 24th March 2024 at 00:48 GMT

Founding Member

DaFa

User Level: Founding Member

Date Joined: 30/11/2018

Posted by MFahad on Tuesday 4th October 2022 at 17:16 GMT

Thanks for your suggestion!I will do it as God willing.

MFahad

User Level: Guest

Date Joined: 30/07/2022

Posted by Andrew on Saturday 20th May 2023 at 03:54 GMT

Hello,

Was a pull request done for this? Thanks.
Early Adopter

Andrew

User Level: Early Adopter

Date Joined: 23/10/2019

Posted by DaFa on Saturday 20th May 2023 at 06:47 GMT

Yeah kinda, MFahad did make a pull request>
https://github.com/trongate/trongate-framework/pull/125

but it wasn't the detailed one I suggested and he didn't explain anything, so DC would have looked at this wondering WTF😵‍💫

He just added a defined constant 'DRIVER' as a db type, with no explanation on its use in database.php:
$dsn = DRIVER.':host=' . $this->host . ';dbname=' . $this->dbname;

There is no extra logic for SQLite or any of the other supported db types that can be used with PDO as I mentioned...

There would also have to be some changes in the Desktop app to accommodate different database types for this addition to be bulletproof.

This comment was edited by DaFa on Saturday 20th May 2023 at 06:51 GMT

Founding Member

DaFa

User Level: Founding Member

Date Joined: 30/11/2018

Posted by Andrew on Saturday 20th May 2023 at 07:39 GMT

I've posted a pull request for a quick change to SQLite . Hope it gets accepted.
Early Adopter

Andrew

User Level: Early Adopter

Date Joined: 23/10/2019

Posted by Andrew on Saturday 20th May 2023 at 08:19 GMT

I am able to read from the sqlite db at this link

http://localhost/sqlite/

At this link I see this error. Any idea on why the error appears? Works fine if its a mysql db.
http://localhost/sqlite/contents/show/1

Fatal error: Uncaught Error: Call to a member function bindValue() on bool in \engine\Model.php on line 77
( ! ) Error: Call to a member function bindValue() on bool in \engine\Model.php on line 77

function prepare_and_execute($sql, $data) {

$this->stmt = $this->dbh->prepare($sql);

if (isset($data[0])) { //unnamaed data
return $this->stmt->execute($data);
} else {

foreach ($data as $key => $value) {
$type = $this->get_param_type($value);
$this->stmt->bindValue(":$key", $value, $type); // THIS triggers the error
}

return $this->stmt->execute();
}
}


Maybe a problem with bindValue and sqlite?
https://stackoverflow.com/questions/48514115/bindparam-bindvalue-not-working-with-my-sqlite3-prepared-statements

This comment was edited by Andrew on Saturday 20th May 2023 at 08:45 GMT

Early Adopter

Andrew

User Level: Early Adopter

Date Joined: 23/10/2019

×