Calling procedure from Web application, won't work.

[derrickorama]

I'm trying to call a procedure I made from a PHP page I made running on Apache server. Here is the snippet of PHP that I'm working with:

$query = "CALL insertTotals($cash20,$cash10,$cash5,$cash1,$cash025,$cash010,$cash005,$cash001,$other,$checks,$card,$invoice,$cardtotal,$checkinsales,$totalz,$date)";
mysql_query($query);

Here is the procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `register`.`insertTotals` $$
CREATE PROCEDURE `insertTotals`(IN cash20V DECIMAL(6,2),IN cash10V DECIMAL(6,2),IN cash5V DECIMAL(6,2),IN cash1V DECIMAL(6,2),IN cash025V DECIMAL(6,2),IN cash010V DECIMAL(6,2),IN cash005V DECIMAL(6,2),IN casH001V DECIMAL(6,2),IN otherV DECIMAL(6,2),IN checksV DECIMAL(6,2),IN cardV DECIMAL(6,2),IN invoiceV DECIMAL(6,2),IN cardtotalV DECIMAL(6,2),IN checkinsalesV DECIMAL(6,2),IN TotalzV DECIMAL(6,2),IN dateV DATETIME)
BEGIN
INSERT INTO Totals Values (
ROUND(cash20V),
ROUND(cash10V),
ROUND(cash5V),
ROUND(cash1V),
ROUND(cash025V),
ROUND(cash010V),
ROUND(cash005V),
ROUND(cash001V),
ROUND(otherV),
ROUND(checksV),
ROUND(cardV),
ROUND(invoiceV),
ROUND(cardtotalV),
ROUND(checkinsalesV),
ROUND(totalzV),
dateV
);
SELECT * FROM Totals WHERE Date = dateV;

END $$

DELIMITER ;

For some reason it won't actually insert any of the data when I try calling it through PHP, but it works just fine when I run it through MySQL Query Browser. I know for a fact that it works for the user I'm connecting with. For some odd reason, it will run the SELECT statement in the procedure, but still fail at running the INSERT statement. Again, the procedure, in all it's entirety, works when I call it through the query browser, but not through PHP. What could the problem be???

 

[lgarner]

Just a thought, but PHP uses the $ to designate variables. First, try echoing $query to see what's really being passed by mysql_query()

 

[vacunita]

Can you print out the query once all the PHP variables have been replaced by their values so we know exactly what PHP is sending to mysql?

echo $query; before making the mysql_query() call.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[derrickorama]

Here's what it is after the variables have their values:

CALL insertTotals(120.00,30.00,15.00,12.00,6.50,3.90,1.95,0.39,0.00,3.75,84.29,0.00,84.29,3.75,151.48,20060704)

Now the thing that I don't understand is why I can copy and paste this exact query, and it works in the Query Browser, but it won't work with just the PHP.

 

[vacunita]

Everything seems right, ca you add the following to the mysql_query call:

mysql_query($query)[red]or die(mysql_error())[/red];

It might give us an idea of what's wrong.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[derrickorama]

Hm... Well here is the alleged error that's occurring:

PROCEDURE register.insertTotals can't return a result set in the given context

 

[derrickorama]

I think I found my problem...

Statements that return a result set cannot be used within a stored function. This includes SELECT statements that do not use INTO to fetch column values into variables, SHOW statements, and other statements such as EXPLAIN. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET_IN_FUNC). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT).

 

[vacunita]

Glad you found out what was wrong. Sorry I wasn't much help.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.