This is my Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `call`.`sp_login`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_login`(userid varchar(50),pass varchar(50),out chk int(11),out user_alias varchar(50),out user_tariff varchar(50),out c_credit decimal(15,5),out r_reseller varchar(10),out r_level varchar(10),out c_tariff char(2))
BEGIN
select id into chk from cc_reseller  where useralias =userid and uipass=pass and level!=0 and activated!='0';
if(chk!="") then
select id,useralias,tariff,credit,reseller,level,create_tariff into chk,user_alias,user_tariff,c_credit,r_reseller,r_level,c_tariff from cc_reseller where useralias =userid and uipass=pass and activated!='0' and level!=0;
end if;
    END$$

DELIMITER ;

This is my Php Code to call StoreProcedure

<?php
    ob_start();
  include_once('config.php');
  $username = $_POST['username'];
  $password = $_POST['password'];
  
  if (isset($_POST['username']) && $_POST['username']!='')
        {
            $username=$_POST['username'];
            $password=$_POST['password'];        
            $query="call sp_login('$username','$password',@chk,@user_alias,@user_tariff,@c_credit,@r_reseller,@r_level,@c_tariff)";
            $result=mysql_query($query) or die('Query error'.''.mysql_error());
            $count=mysql_fetch_row($result);
           

            $chk = $count[0];
            if($chk !="")
            {
                $n_name = $count[1];
                $n_tariff = $count[2];
                $n_username = $count[3];
                $n_creator = $count[4];
                 $n_credit = $count[5];
                $_SESSION['username'] = $n_username;
                $_SESSION['user_id'] = $chk;
                $_SESSION['tariff'] = $n_tariff;
                $_SESSION['reseller'] = $n_creator;
                $_SESSION['ncredit'] = $n_credit;
                
                header('Location:welcomes.php');
            }
            else
            {
                header("location:index.php?q=-2");
            }    
        }
        else
        {
            if(!isset($_SESSION['username']) || $_SESSION['username']=='')
            {
                header("location:index.php?q=-1");
            }
        } 
?>

Can anyone tell where i m wrong..
is it true that Out parameter doesnt work with mysql..???

Help me out

Recommended Answers

No its not true, what error you get..?

Jump to Post

i still don't understand it, did you tried it.
Check the examples given in the page here .
It might help you.

Jump to Post

great, have a nice time:)

Jump to Post

All 10 Replies

No its not true, what error you get..?

No its not true, what error you get..?

Query errorPROCEDURE call.sp_login can't return a result set in the given context

Well i google little bit..!!

i got this article
http://www.artfulsoftware.com/infotree/tip.php?id=130

in it is said that Mysql Api cannot fetch out parameter..!!!

may be thats why people use mysqli..??

What do u say..?

i still don't understand it, did you tried it.
Check the examples given in the page here .
It might help you.

$con = mysql_connect('localhost','root','',false,65536);

can u show some light over the code..!

last two parameters makes the difference..!!

now my procedure is running..!!!
Thanks for the link

great, have a nice time:)

still i dont understand the use and meaning of last two parameters..??
what is the use of it..!! earlier i never use it..?

Can u tell me that

still i dont understand the use and meaning of last two parameters..??
what is the use of it..!! earlier i never use it..?

Can u tell me that

sure, this last two parameters are optional,
the the but one parameter is called new_link, if its set to true, each time you call mysql_connect() new connection is created and that MYSQL link identifier is returned back.Otherwise no new link will be established, but instead, the link identifier of the already opened link will be returned.
and the last parameter called client_flags is an integer actually, has values like 'MYSQL_CLIENT_IGNORE_SPACE ', 'MYSQL_CLIENT_COMPRESS' and 'MYSQL_CLIENT_INTERACTIVE' etc.
Don't worry much about this last one, if you don't need it at the moment.

sure, this last two parameters are optional,
the the but one parameter is called new_link, if its set to true, each time you call mysql_connect() new connection is created and that MYSQL link identifier is returned back.Otherwise no new link will be established, but instead, the link identifier of the already opened link will be returned.
and the last parameter called client_flags is an integer actually, has values like 'MYSQL_CLIENT_IGNORE_SPACE ', 'MYSQL_CLIENT_COMPRESS' and 'MYSQL_CLIENT_INTERACTIVE' etc.
Don't worry much about this last one, if you don't need it at the moment.

Well i dont think last two parameters are optional in case of stored procedure...!! when i remove those paramemters.. Store Procedure does run at all..!! How these parameters affect the running of store procedures..?

yes, they seems to be important for stored procedures to execute -
When you connect and expect to use a stored procedure,you must pass a special flag to MySQL via the connect command, otherwise you will not get the results returned, and it will result in this error:
PROCEDURE AlexGrim.GetStats_ForumCategories can't return a result set in the given context

To fix this, change you connection string, adding ",false,65536" as the last 2 fields:
$this->con = mysql_connect($this->h,$this->u,$this->p,false,65536);

This is what you did.Because the constant 65536 represents the "CLIENT_MULTI_STATEMENTS", which will enable the support for using multiple statement in the query.
Why to use this, must be obvious now for you, as stored procedures are likely to have multiple statements

more than one constant can be set using pipe in the parameter

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.