How to call a PostgreSQL stored function with doctrine

Question

I try to call some custom function of our PostgreSQL database with doctrine but it doesn't work.

$query = $this->_em->getConnection()
           ->prepare('SELECT security.check_login($1, $2, $3, $4, 1, $5, 1, $6, false)');
$query->bindValue(1, 'myname', \Doctrine\DBAL\Types\Type::STRING);
$query->bindValue(2, 'mypass', \Doctrine\DBAL\Types\Type::STRING);
$query->bindValue(3, '35we74', \Doctrine\DBAL\Types\Type::STRING);
$query->bindValue(4, 'a', \Doctrine\DBAL\Types\Type::STRING);
$query->bindValue(5, 'b', \Doctrine\DBAL\Types\Type::STRING);
$query->bindValue(6, '5fuf6d32qbm0hivj739vi8r0t3', \Doctrine\DBAL\Types\Type::STRING);
var_dump($query->execute()); // return false
var_dump($query->fetchColumn()); // return false

Does anyone know how to make such query?

"It doesn't work". What, exactly, does not work? What happens if you do the same thing with psql? Since execute returns false, what's the error from the database? — Craig Ringer
– Craig Ringer, Commented Feb 21, 2014 at 9:50
The same request in pgAdmin return 1, that's mean success. SELECT security.check_login('myname', 'mypass', '35we74', 'a', 1, 'b', 1, '5fuf6d32qbm0hivj739vi8r0t3', false); — Alexandre
– Alexandre, Commented Feb 21, 2014 at 10:40

Alexandre · Accepted Answer · 2014-02-24 09:28:38Z

2

I finally found the answer to my problem on this thread http://postgresql.1045698.n5.nabble.com/Stored-procedures-PDO-and-PHP-issue-td2211491.html

"You can not use the question mark syntax and the cast. Instead you have to use the named parameters with the cast."

$query = $this->_em->getConnection()->prepare(
        'SELECT security.check_login(:user::VARCHAR, :pass::VARCHAR, :tan::VARCHAR, :tan_h1::VARCHAR, 1, :tan_h2::VARCHAR, 1, :session::VARCHAR, false)');
$query->bindValue(':user', 'myname');
$query->bindValue(':pass', 'mypass');
$query->bindValue(':tan', '35we74');
$query->bindValue(':tan_h1', 'a');
$query->bindValue(':tan_h2', 'b');
$query->bindValue(':session', '5fuf6d32qbm0hivj739vi8r0t3');

answered Feb 24, 2014 at 9:28

Alexandre

3,2005 gold badges41 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

Wow, really? And PDO doesn't fail with an error, it just silently butchers the query? Lovely.

Collectives™ on Stack Overflow

How to call a PostgreSQL stored function with doctrine

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related