How do I execute stored procedures? [closed]

Question

I have a website which does lot of work on financial data. It involves operations on large volumes of data most of the time. Hence I find it useful to do those operations in MySQL stored procedures. I have stored procedures in my database. I want to know how can I execute stored procedures in Drupal? Is there any good method to execute stored procedures in Drupal? How does Drupal handle stored procedures in general? Or we just have to use the PHP to execute stored procedures?

Answer 1

Assuming you're using Drupal 7 you can use code like the following:

// Get the Drupal database connection and change the statement class to PDOStatement.
// Save the current class for cleanup later.
$conn = Database::getConnection();
$saved_class = $conn->getAttribute(PDO::ATTR_STATEMENT_CLASS);
$conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('PDOStatement'));

// Prepare the statement and bind params
$statement = $conn->prepare("Call GetNodeList(?,?)");

$op_status = $statement->bindParam(1, $node_type, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 25);
$op_status = $statement->bindParam(2, $publish_state, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT);

// Execute the statement and reset the connection's statement class to the original.
$exec_result = $statement->execute();
$conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, $saved_class);

// Get your data
while ($row = $statement->fetchColumn(0)) {
  // ...
}

That method is completely culled from this article and has worked for me well in the past.

Answer 2

Not sure whether this is the correct way to this, but it worked for me. I have a legacy system which shares the same Postgres database server that Drupal does.

In a submit handler I needed to send data to this legacy system which had a stored procedure (Postgres calls them functions) to handle the data:

// Get legacy database connection set-up in settings.php
Database::getConnection('default', 'legacy')
  ->query('SELECT * FROM stored_procedure(:named_parameter_1, :named_parameter_2, ...);', [
    ':named_parameter_1' => $value_1,
    ':named_parameter_1' => $value_2,
    ...
  ]);

This got the data successfully into my legacy system