Level 53
@basho What do you want to do exactly? Describe the use case in order to get an advice.
Jan 29, 2015
6
Level 1
get statement variable for prepared pdo calls
Hi, i just started playing around with laravel and i wanted to check something about prepared statements.
What i want to do is get the $statement variable before fetch is called so that i can run it again with differen parameters. I have a select statement like this : Select * from table where primary_key = :var_primary_key so i bind the parameters like this
array(
"var_primary_key" => 1
);
and after that i want to run the same prepared statement again with another value for var_primary_key
But in the Connection.php File in the select function the $statement isn't returned
$statement->execute($me->prepareBindings($bindings));
return $statement->fetchAll($me->getFetchMode());
instead the fetchAll command is called.
So is there no support in laravel for the rerun of a prepared statement with different parameters?
My Solution at the moment is another function in the Connection.php Class But i'm not so keen to modify Core Classes, can i extend them in an easy way?
And of course it would be great to use this with the query builder like
$user = DB::table('users')->where('name', ':var_name')->returnPrepared();
Here is the function i use, would be cleaner to have fourth parameter at the select Function to return the statement.
public function selectPrepared($query, $bindings = array(), $useReadPdo = true)
{
return $this->run($query, $bindings, function($me, $query, $bindings) use ($useReadPdo)
{
if ($me->pretending()) return array();
// For select statements, we'll simply execute the query and return an array
// of the database result set. Each element in the array will be a single
// row from the database table, and will either be an array or objects.
$statement = $this->getPdoForSelect($useReadPdo)->prepare($query);
$statement->execute($me->prepareBindings($bindings));
return $statement;
});
}
Did i overlook something?
Level 1
In my opinion thats one of the main things why pdo is so strong. For Example at runtime u have to query your database for the rights of an user which are stored in one table. And you have to query them multiple times becauese there are rights for different kind of actions read | write | delete and so on, so normaly it would look something like this
Select right_status from rights where right='delete' and user=1
Select right_status from rights where right='read' and user=1
Select right_status from rights where right='write' and user=1
with a prepared statement u would only have one query
Select right_status from rights where right=:var_right and user=:user_id
which is stored by pdo (it will also only show once in the query logs as one call to the server) the rest is the magic from pdo and u only have to bind new parameters to the query and fetch the result.
I hope this helps to understand the question i have a little bit better.
greets
Level 53
@basho Well, you can't do that with the query builder of course, as you already noticed. I never needed that so far, but it seems to be useful in fact, so a PR maybe? :)
Anyway, to override Connection and Builder classes you need to bind the custom connection class in a non-deferred service provider. Then you will be able to use both Eloquent and the Query Builder without any adjustments, but also will you have your custom methods.
Here's an example, how you do it: http://softonsofa.com/laravel-query-builder-global-scope-how-to-use-custom-connection-and-query-builder-in-laravel-4/
1 like
Level 1
Thanks for your reply and the link, i will have to look into this. In the meantime I played around a litte more and found a simple (i think it's simple) solution i just wrote these changes into the Illuminate\Database\Query\Builder.php I think that would be the fastest way to achieve this, I'm thinking of doing the same for the insert and the update command because there is where prepared statements really start to shine.
/**
* Indicates whether the statement will be fetched from the connection or returned
*
* @var string|bool
*/
protected $return_statement = false;
/**
* instead of returning the result the satetement of the pdo class wll be returned
*
* @return $this
*/
public function returnStatement()
{
$this->return_statement = true;
return $this;
}
/**
* Run the query as a "select" statement against the connection.
*
* @return array
*/
protected function runSelect()
{
if($this->return_statement)
{
return $this->connection->selectPrepared($this->toSql(), $this->getBindings());
}else
{
return $this->connection->select($this->toSql(), $this->getBindings());
}
}
and i call the query now like this
$statement= $dbase::table('table')->returnStatement()->get();
and after that u have the statement and can call fethAll or whatever pdo has to offer
Level 53
@basho Mind that every composer update will overwrite your changes. I wouldn't change anything directly in the /vendor/* classes.
Level 1
Thanks i will have to remember that, and i'm also going to post a PR in the future. In my opinion it's a feature thats easy to realise and pretty useful with handling a complex framework
Please or to participate in this conversation.