KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Connecting to Postgresql and using a secondary schema. #2528

Open John Kawas opened this topic on on Jan 10, 2022 - 13 comments

John Kawas commented on Jan 10, 2022

I have this database (pgsql) with multiple schema in use. How can i switch between schema's?

This is the structure:

I will use shared 90% of the time but need to access the others occasionally for reports.

Sebastian Morales commented on Jan 10, 2022

In your report setup(), before you use another schema other than the default one pls add this command:

$this->src("psql")
->query("SET search_path TO yourschema")
->pipe($this->dataStore("setSchemaResult"));

Let us know if this works for you. Tks,

John Kawas commented on Jan 16, 2022

I am not exactly sure where to put this code? Any example?

John Kawas commented on Jan 16, 2022

Hey Sebastian,

I have tried and tried every variation that I can think of and it does not work?

Here is the connection and set up.. can you please tell me what I am doing wrong?

When I run the pages from the tutorial i get this error: This page isn’t working18.192.23.48 is currently unable to handle this request. HTTP ERROR 500

<?php

require_once "../../../load.koolreport.php";

use \koolreport\KoolReport; use \koolreport\processes\Filter; use \koolreport\processes\TimeBucket; use \koolreport\processes\Group; use \koolreport\processes\Limit;

class SakilaRental extends KoolReport {

function settings()
{
    return array(
        "dataSources"=>array(
            "sakila_rental"=>array(
                "connectionString"=>"pgsql:host=XXX.XX.XX.46;port=5432;dbname=xxxxxxxxxxxxxDB",
                "username"=>"xxxxxx",
                "password"=>"xxxxxxxxx"
            ),
        )
    );

__ $this->src("sakila_rental")

    ->query("SET search_path TO Shared");
    ->pipe($this->dataStore('sale_by_month'));__        

//Should the ->pipe (above) have a unique name or the same name like "sales_by_month"???// // Also, is this the correct place for the Set search_path TO Shared to go?//

}
protected function setup()
{
  $this->src("sakila_rental")
  ->query("SET search_path TO Shared");
  $this->src("sakila_rental")
  ->query("SELECT * FROM "Patients"");

//OR would the "SET search_path TO Shared" go above?//

    ->pipe(new TimeBucket(array(
        "BirthDate"=>"month"
    )))
    ->pipe(new Group(array(
        "by"=>"BirthDate",
        "sum"=>"Id"
    )))
    ->pipe($this->dataStore('sale_by_month'));
}

}

Sebastian Morales commented on Jan 17, 2022

Pls try this:

        $this->src("pdoPostgresql")
        ->query("SET search_path TO my_schema_2")
        ->pipe($this->dataStore("setSchemaResult"));

        $this->src("pdoPostgresql")
        ->query("select * from customers2") // assuming customers2 is in schema my_schema_2
        ->pipe($this->dataStore("customers2")); 


        $this->src("pdoPostgresql")
        ->query("SET search_path TO public")
        ->pipe($this->dataStore("setSchemaResult"));

        $this->src("pdoPostgresql")
        ->query("select * from customers") // assuming customers2 is in schema public
        ->pipe($this->dataStore("customers")); 

Another option:

        $this->src("pdoPostgresql")
        ->query("select * from my_schema_2.customers2") // use schema directly before table
        ->pipe($this->dataStore("customers2")); 

        $this->src("pdoPostgresql")
        ->query("select * from public.customers") // use schema directly before table
        ->pipe($this->dataStore("customers")); 

Let us know if there's any error. Tks,

John Kawas commented on Jan 17, 2022

Neither worked. This database is read only and I am able to log into it with other products... (DBFace and Dashboard builder) with my credentials and pull data without any issues.

I am curious why i can't make a connection with Koolreport???

I have been trying now for 3 days to sort this connection out. I have made the most simple table reports and all I seem to be able to generate is a server error.

This page isn’t working 4X.192.XX.48 is currently unable to handle this request. HTTP ERROR 500

Any suggestions, ideas or thoughts (prayers?) would be greatly appreciated...

Sebastian Morales commented on Jan 17, 2022

I think first we must make sure a successful psql or pdo psql connection is created. The example code works well for us with the following connection setting:

    'postgresql' => [
        'host' => 'localhost',
        'username' => 'root',
        'password' => 'root',
        'dbname' => 'testdb',
        'class' => '\koolreport\datasources\PostgreSQLDataSource',
    ],
    'pdoPostgresql' => [
        'connectionString' => 'pgsql:host=localhost;dbname=testdb',
        'username' => 'root',
        'password' => 'root',
        'class' => "\koolreport\datasources\PdoDataSource",
    ],

Pls try these tests in a simple php file (says, index.php):

        //Create psql connection
            $connString = "host=$host port=$port dbname=$dbname user=$username password=$password"; // default $port = 5432
            $conn = pg_connect($connString);
            if ($conn) {
                echo "psql connection created successfully.";
            } else {
                throw new \Exception("Could not connect to database");
            }
        //Create pdo psql connection
            $conn = new PDO(
                'pgsql:host=localhost;dbname=testdb', //change to your host and dbname
                $username,
                $password
            );
            if ($conn) {
                echo "pdo psql connection created successfully.";
            } else {
                throw new \Exception("Could not connect to database");
            }
John Kawas commented on Jan 17, 2022

Thank you, I will try it and get back to you John

John Kawas commented on Jan 18, 2022

This one works:

//Create pdo psql connection

        $conn = new PDO(
            'pgsql:host=localhost;dbname=testdb', //change to your host and dbname
            $username,
            $password
        );
        if ($conn) {
            echo "pdo psql connection created successfully.";
        } else {
            throw new \Exception("Could not connect to database");
        }
Sebastian Morales commented on Jan 18, 2022

Pls create a simple report with pdo datasource connecting to your postgresql. Then query a simple select query and pipe to a datastore to see if it works.

John Kawas commented on Jan 19, 2022

I did as you suggested.... Not working.

Index.php <?php require_once "MyReport.php";

$report = new MyReport; $report->run()->render();

MyReport.php

<?php

require_once "../../../load.koolreport.php";

class MyReport extends \koolreport\KoolReport { protected function settings() {

     return array(
         "dataSources"=>array(
             "db"=>array(
                 "connectionString" => "pgsql:host=xxx.xx.xx.46;dbname=xxxxxxxxxxxDB",
                 "username" => "xxxxxx",
                 "password" => "xxxxxx,29!"

             ),
          )
     );
 }
protected function setup()

{

  $this->src("db")
       ->query("SET search_path TO Shared")
       ->pipe($this->dataStore("setSchemaResult"))
     },

{
       $this->src("db")
       ->query("SELECT * from Patients")
       ->pipe($this->dataStore("db"));
   }

}

MyReport.view.php

<?php

use \koolreport\widgets\koolphp\Table;

?> <div class="report-content">

<div class="text-center">
    <h1>Minimum Settings</h1>
    <p class="lead">Minimum settings to get KoolPHP Table working</p>
</div>
<?php
Table::create(array(
    "dataSource"=>$this->dataStore("db")
));
?>

</div>

John Kawas commented on Jan 19, 2022

I used the simple "minimal settings" table report as a template

John Kawas commented on Jan 19, 2022

Still get the following 500 error:

This page isn’t working xx.xxx.xx.48 is currently unable to handle this request. HTTP ERROR 500

John Kawas commented on Jan 19, 2022

Database connection works

pdo psql connection created successfully.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

None