Re: Calling Stored Procedures from PHP
Filipe Freitas wrote:
Hi,
This is not a mysql thing but maybe you can help me.
I want to call a stored procedure from PHP, so I tried it like normal
querys: mysql_query(CALL mySP();); with no success.
thx
I'm not PHP expert but I've managed to do this in perl using DBI (in
spite of all docs saying there are no such thing as OUT params). Perhaps
you could try s.th. alike:
# perl DBI with firebird: stored proc has 3 IN and 4 OUT params as you
can see...
my $s = SELECT field1, field2, field3, CAST(field4 AS VARCHAR(32)).
FROM S_GET_NAL_WEB('12-31-05', 4201, '2051600');
my $sth = $dbh-prepare($s) or die $dbh-errstr();
$sth-execute() or die $sth-errstr();
while (@data = $sth-fetchrow_array()) {
print join(BR, @data).BRBR;
}
...
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
On Monday 27 November 2006 09:12, Filipe Freitas wrote: CREATE PROCEDURE `getListaDeNewsflashes`(in quantidade smallint) COMMENT 'Devolve uma tabela com um número limite de newsflashes' begin PREPARE statement FROM SELECT * FROM newsflashes ORDER BY RAND() LIMIT ?; SET @limit=quantidade; EXECUTE statement USING @limit; end You seem to be mimicing the prepared query feature of mysqli in PHP5. Do you have the mysqli extension avaliable? If so, you can use things like: http://www.php.net/manual/en/function.mysqli-stmt-bind-param.php which has an example as to how to utilize a prepared query. -- Chris White PHP Programmer Interfuel 805.642.2200 x110 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Chris White wrote: On Monday 27 November 2006 09:12, Filipe Freitas wrote: CREATE PROCEDURE `getListaDeNewsflashes`(in quantidade smallint) COMMENT 'Devolve uma tabela com um número limite de newsflashes' begin PREPARE statement FROM SELECT * FROM newsflashes ORDER BY RAND() LIMIT ?; SET @limit=quantidade; EXECUTE statement USING @limit; end You seem to be mimicing the prepared query feature of mysqli in PHP5. Do you have the mysqli extension avaliable? If so, you can use things like: http://www.php.net/manual/en/function.mysqli-stmt-bind-param.php which has an example as to how to utilize a prepared query. I never really looked into mysqli, so far I only used the mysql extention in PHP. So maybe that will help. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
You seem to be mimicking the prepared query feature of mysqli in PHP5.
Do you
have the mysqli extension available? If so, you can use things like:
http://www.php.net/manual/en/function.mysqli-stmt-bind-param.php
which has an example as to how to utilize a prepared query.
I never really looked into mysqli, so far I only used the mysql
extension in PHP.
So maybe that will help.
thanks
Filipe,
As someone else suggested, the mysql extension also works just fine:
?php
// StoredProc(@param1, param2) sums a table Id int column and divides by param2.
// The result is stuffed into @param1
$query1 = call StoredProc(@param1, param2);
$result1 = mysql_query($query1) or die('Query failed: ' . mysql_error());
$query2 = select @param1;
$result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
echo table\n;
while ($line = mysql_fetch_array($result2, MYSQL_ASSOC)) {
echo \ttr\n;
foreach ($line as $col_value) {
echo \t\ttd$col_value/td\n;
}
echo \t/tr\n;
}
echo /table\n;
?
David
Calling Stored Procedures from PHP
Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Remove the Semicolon mysql_query(CALL mySP()); Give it a try !!! - Original Message - From: Filipe Freitas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 27, 2006 10:59:09 AM GMT-0500 US/Eastern Subject: Calling Stored Procedures from PHP Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
On Monday 27 November 2006 07:59, Filipe Freitas wrote: Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. thx No success how? Generally with stored procedures and returning values, you use a session variable and utilize it as OUT like so: DROP PROCEDURE IF EXISTS CURVAL $ CREATE PROCEDURE CURVAL (OUT current INT) BEGIN SELECT value INTO current FROM sequence; END $ DELIMITER ; so when I go to call curval: CALL CURVAL(@cur) and the value can be received by: SELECT @cur; -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Chris White wrote: On Monday 27 November 2006 07:59, Filipe Freitas wrote: Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. thx No success how? Generally with stored procedures and returning values, you use a session variable and utilize it as OUT like so: DROP PROCEDURE IF EXISTS CURVAL $ CREATE PROCEDURE CURVAL (OUT current INT) BEGIN SELECT value INTO current FROM sequence; END $ DELIMITER ; so when I go to call curval: CALL CURVAL(@cur) and the value can be received by: SELECT @cur; My stored procedure is the following: CREATE PROCEDURE `getListaDeNewsflashes`(in quantidade smallint) COMMENT 'Devolve uma tabela com um número limite de newsflashes' begin PREPARE statement FROM SELECT * FROM newsflashes ORDER BY RAND() LIMIT ?; SET @limit=quantidade; EXECUTE statement USING @limit; end it works when I execute it on mysql monitor: call getListaDeNewsflashes(10); in php it doesn't. I think I need the out session variable like you said. But how? I will need a variable for every column? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Filipe Freitas wrote: Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. What is happening when you make the call? That might help get some more precise advice. My understanding of using PHP with MySQL stored procedures is that you have to call them through the mysqli extension. It doesn't appear that you are using them. With them your would be using the mysqli_query() function. I don't do a lot of PHP, but I stumbled into a similar problem and using the mysqli extensions solved the issue. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
