9

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?

2
  • 1
    Have you searched the web? Google returns some results for "drupal stored procedures" that seem interesting. Did you try the code? Can you tell us what did work, and what didn't? May 30 '12 at 15:10
  • 1
    Yes, I did search in google. There seems to need of few lines of code to execute a single stored procedure with parameters. Is there any helper function in Drupal API to execute stored procedure? May 31 '12 at 4:17
12

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.

1
  • thanks for culling it! the page referenced with "this article" doesn't resolve.
    – cdmo
    Aug 21 '17 at 12:45
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.