|
|
Posted by Jerry Stuckle on 04/03/07 22:32
eholz1 wrote:
> On Apr 2, 5:51 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> eholz1wrote:
>>> On Apr 2, 9:10 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> eholz1wrote:
>>>>> Hello PHP group,
>>>>> Could someone help me out? I have PHP 5.2, Apache 2.0, and MySQL 5.0
>>>>> running on Linux (Redhat Fedora Core 6).
>>>>> All that works fine. I would like to be able to "call" a stored
>>>>> procedure from a PHP program, and
>>>>> run a stored procedure. I have yet to figure out the proper way to do
>>>>> this. My stored procedures work fine from the mysql command line
>>>>> using syntax: "call sp_min_record (101);"
>>>>> But when I try to do the same thing within my PHP page - no luck I get
>>>>> an error.
>>>>> Here is my stored procedure code:
>>>>> DELIMITER $$
>>>>> DROP PROCEDURE IF EXISTS `portfolios`.`sp_min_record` $$
>>>>> CREATE DEFINER=`ewh`@`localhost` PROCEDURE
>>>>> `portfolios`.`sp_min_record`
>>>>> (IN folio INT)
>>>>> BEGIN
>>>>> SELECT MIN(id) INTO minr FROM images3 WHERE p = folio;
>>>>> END$$
>>>>> DELIMITER ;
>>>>> and here is how I am trying to call it from php:
>>>>> $dbconnect = db_connect('portfolios') or trigger_error("Error
>>>>> Connecting to Database: "
>>>>> . mysql_error(), E_USER_ERROR);
>>>>> $help = @mysql_query("call sp_min_record ($folio)");
>>>>> $rowx = @mysql_fetch_assoc($help);
>>>>> $minrec = $rowx[0];
>>>>> but I get no value returned. How am I supposed to do this
>>>>> (correctly :))?
>>>>> Thanks,
>>>>> eholz1
>>>> First of all, get rid of the '@' characters. Then check the results of
>>>> mysql_query to see if you got an error. If so, print the error and see
>>>> what's actually happening.
>>>> Pet Peeve: Why do people disable error reporting with '@', don't check
>>>> the results of their calls, then come asking why it isn't working?
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstuck...@attglobal.net
>>>> ==================
>>> Hello Jerry et al,
>>> removed the offending @'s!! No error messages. No results either!
>>> It seems that my query (stored procedure is not really being called
>>> correctly from my php code. As I mentioned earlier, it produces a
>>> result from the mysql command line. I could cheat, and use the old
>>> "SELECT MIN(id) from table where val = 101;", but a stored procedure
>>> seems much cooler.
>> And what about the second half of my question - what is the result of
>> the query? Is mysql_query returning false? Or is it returning a result
>> set? And if the former, what's the error (hint: mysql_error()).
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> The query is returning false. Here is the error (sorry for not doing
> this FIRST!!!)
> Fatal Error: Stored Procedure Error: PROCEDURE
> portfolios.sp_min_record can't return a result set in the given
> context (# 256).
>
> I seem to have this error everytime I get the bright idea to use a
> stored procedure that takes data or returns data. I am currently
> using the "SELECT min(id) INTO minval FROM xxx" as above, if I call
> this sp from the command line I get no result! So I am guessing wrong
> syntax??? If I take the "INTO" out of the and use this SELECT MIN(id)
> FROM images3 WHERE p = pval; I get a result from the command line, and
> the error above when run from the PHP page.
>
> Thanks for the info on this (as always)
> eholz1
>
>
Well, it looks like a problem with your SP. I'd recommend following up
in comp.databases.mysql. Try to get it to work from the command line first.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|