3

The question is a fairly open one. I've been using Stored Procs with MS SQLServer for some time with classic ASP and ASP.net and love them, lots.

I have a small hobby project I'm working on and for various reasons have gone the LAMP route. Any hints/tricks/traps or good starting points to get into using stored procedures with MySQL and PHP5? My version of MySQL supports Stored Procedures.

halfer
  • 19,824
  • 17
  • 99
  • 186
Jon P
  • 19,442
  • 8
  • 49
  • 72
  • Can you explain why you want to use stored procedures? The conventional wisdom says "use stored procs only when necessary", so I need to understand your motivation to answer. – Alex Weinstein Sep 23 '08 at 02:23
  • @Alex I like the ability to retrieve multiple result sets in terms of selects (reducing db calls), parametisation to help reduce chances of SQL injection. Also with MS SQL Server Stored Procs execution paths are optimised by SQL Server. Also I'm using this little hobby project as a learning tool. – Jon P Sep 23 '08 at 22:19
  • @Alec.continued!I also get better re-use from stored procs. If I have some db functionality that is used frequently I only have to update in the one location.I realise this can also be handled at the PHP level with functions/classes. I've found no reason not to use Stores Procs with MSSQL & ASP/.net – Jon P Sep 23 '08 at 22:31

5 Answers5

7

@michal kralik - unfortunately there's a bug with the MySQL C API that PDO uses which means that running your code as above with some versions of MySQL results in the error:

"Syntax error or access violation: 1414 OUT or INOUT argument $parameter_number for routine $procedure_name is not a variable or NEW pseudo-variable".

You can see the bug report on bugs.mysql.com. It's been fixed for version 5.5.3+ & 6.0.8+.

To workaround the issue, you would need to separate in & out parameters, and use user variables to store the result like this:

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(:in_string, @out_string)");
$stmt->bindParam(':in_string', 'hello'); 

// call the stored procedure
$stmt->execute();

// fetch the output
$outputArray = $this->dbh->query("select @out_string")->fetch(PDO::FETCH_ASSOC);

print "procedure returned " . $outputArray['@out_string'] . "\n";
Dan Straw
  • 221
  • 2
  • 4
4

Forget about mysqli, it's much harder to use than PDO and should have been already removed. It is true that it introduced huge improvements over mysql, but to achieve the same effect in mysqli sometimes requires enormous effort over PDO i.e. associative fetchAll.

Instead, take a look at PDO, specifically prepared statements and stored procedures.

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";
halfer
  • 19,824
  • 17
  • 99
  • 186
mike
  • 5,047
  • 2
  • 26
  • 32
  • 8
    mysqli buggy and should be removed? Utter, utter nonsense. – Polsonby Sep 23 '08 at 21:32
  • Flubba & Michal, could you elaborate your positions? In 300 characters or less! – Jon P Sep 23 '08 at 22:37
  • I agree "buggy" was not a wise choice of words, but I still believe it should have been removed along with introduction of PDO. It is true, mysqli introduced huge improvements over mysql, but to achieve the same effect in mysqli, it sometimes requires enormous effort over PDO.ie associative fetchAll – mike Sep 24 '08 at 07:49
  • Do we need to use prepared statements with stored procedures? Since 1) the stored procedures automatically avoid SQL Injection and 2) I cant see how SQL Injection would be possible through stored procedure –  Jul 14 '20 at 19:58
2

You'll need to use MySQLI (MySQL Improved Extension) to call stored procedures. Here's how you would call an SP:

$mysqli = new MySQLI(user,pass,db);

$result = $mysqli->query("CALL sp_mysp()");

When using SPs you'll need close first resultset or you'll receive an error. Here's some more information :

http://blog.rvdavid.net/using-stored-procedures-mysqli-in-php-5/ (broken link)

Alternatively, you can use Prepared Statements, which I find very straight-forward:

  $stmt = $mysqli->prepare("SELECT Phone FROM MyTable WHERE Name=?");

  $stmt->bind_param("s", $myName);

  $stmt->execute();

MySQLI Documentation: http://no.php.net/manual/en/book.mysqli.php

StepUp
  • 36,391
  • 15
  • 88
  • 148
Gleb Popoff
  • 171
  • 2
  • 7
2

It isn't actually mandatory to use mysqli or PDO to call stored procedures in MySQL 5. You can call them just fine with the old mysql_ functions. The only thing you can't do is return multiple result sets.

I've found that returning multiple result sets is somewhat error prone anyway; it does work in some cases but only if the application remembers to consume them all, otherwise the connection is left in a broken state.

MarkR
  • 62,604
  • 14
  • 116
  • 151
0

I have been using ADODB, which is a great thing for abstracting actual commands to make it portable between different SQL Servers (ie mysql to mssql). However, Stored procedures do not appear to be directly supported. What this means, is that I have run a SQL query as if it is a normal one, but to "call" the SP. An example query:

$query = "Call HeatMatchInsert('$mMatch', '$mOpponent', '$mDate', $mPlayers, $mRound,  '$mMap', '$mServer', '$mPassword', '$mGame', $mSeason, $mMatchType)";

This isn't accounting for returned data,which is important. I'm guessing that this would be done by setting a @Var , that you can select yourself as the return @Variable .

To be Abstract though, although making a first php stored procedure based web app was very difficult to work around (mssql is very well documented, this is not), It's great after its done - changes are very easy to make due to the seperation.

SuperRoach
  • 215
  • 1
  • 2
  • 10