How to get plain PHP connection from Phalcon?

Dear experts,

I want to port this plain PHP code to Phalcon's code

$con = pg_connect("host=localhost port=5432 dbname=mydb user=postgres password=mypass");
if (!$con) {
    die("Error: Could not establish a connection!");
}
$res = pg_copy_from($con, 'productsTemp', $data, '|');

pg_close($con);
// This is FAILED
// $this->db is DI for db adapter
$res = pg_copy_from($this->db, 'productsTemp', $data, '|');

How to get $con from Phalcon's API? Is there any object that I can used?

Thanks

Jim

edited Oct '14

You can use the Postgresql PDO adapter: http://docs.phalconphp.com/en/latest/api/Phalcon%5Db%5Adapter%5Pdo%5Postgresql.html

 $config = array(
  "host" => "192.168.0.11",
  "dbname" => "blog",
  "username" => "postgres",
  "password" => ""
 );

 $connection = new Phalcon\Db\Adapter\Pdo\Postgresql($config);

Or with a better structure if you have something like this:

├── app
│   └── config
│       └── config.ini
└── public
    └── index.php

You can add this settings to your app/config/config.ini

[database]
username        = db_user
password        = db_password
dbname          = db_database
host            = 127.0.0.1
encoding        = UTF8

And you can use this following code in your public/index.php to load your DB settings:

    //Set the database service
    $di->set('db', function() use ($config){
        return new \Phalcon\Db\Adapter\Pdo\Postgresql(array(
            "host"      => $config->database->host,
            "username"  => $config->database->username,
            "password"  => $config->database->password,
            "dbname"    => $config->database->dbname,
            "charset"   => $config->database->encoding
        ));
    });

Vasco,

Thank you for comment but unfortunately pgcopyfrom can not accept $db (Phalcon's DB Adapter) as correct parameter input.

How to cast the Phalcon's DB Adapter into raw pgconnect (which I need for correct parameter input for pgcopy_from function)?

Don't know if you can do that. Phalcon uses PDO to interface to RDBMS so you will always have to use one PDO adapter, and thus a PDO DB connection object. If you want raw Pg connections you should do it like you are doing with pain PHP. Or you can change your application flow, and use phalcon models and PDO connections the have the same behavior that pgcopyfrom does. I would go with the last one, to use the most phalcon offers.

@Vasco Pinheiro Can you provide me a example to do that (using PDO connections) for my pgcopyfrom?

Sorry, I don't have access to Postgres, but I will try with mysql. mysqli have dual interface, OO and procedural paradigm. In DI, you can set like this:

$di = new \Phalcon\DI\FactoryDefault();
$di->set('mydb_con', function()  {
        $host = '127.0.0.1';
        $username = 'myuser';
        $password = 'mypass';
        $dbname = 'mydbname';
        return mysqli_connect($host,$username,$password,$dbname);

    });

In phalcon componet that can access DI (like extending \Phalcon\Mvc\Controller or \Phalcon\CLI\Task), you use:

$con = $this->di->get('mydb_con');
$sql = "select * from my_column limit 1";
$res = mysqli_query($con,$sql);
$row = mysqli_fetch_assoc($res);
echo $row['my_field'];

or if your method from your library that not extending like thow above need direct access, you can call:

$con = \Phalcon\DI::getDefault()->get('mydb_con');
// sql...


84.1k

mysql/mysqli/pg are incompatible interfaces with PDO, Phalcon does use PDO so you cannot pass an internal PDO handler to mysql/mysqli/pg resources.

@Phalcon Any suggestions to solve this kind of problem?

This is a huge problem! Can you tell us how to solve it?

You can use PDO

edited May '15

All my CMS uses PDO.... But the Adapter! As i mentioned you it doesn't have a lot of functions!