+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Stored Procedure is not executed (/showthread.php?tid=43938)
Stored Procedure is not executed - El Forum - 07-28-2011
[eluser]Rahul gamit[/eluser]
Hii
I am trying to call my stored procedure for inserting the values into the table.
when i am calling the storedprocedure it won't give any error but the data is not inserted in the table.
here is my code,
Code:
CREATE DEFINER=`root`@`localhost` PROCEDURE `tblaccountmaster_add`(
IN p_company_name VARCHAR(250),
IN p_first_name VARCHAR(250),
IN p_last_name VARCHAR(250),
IN p_billing_address VARCHAR(500),
IN p_city VARCHAR(100),
IN p_state VARCHAR(100),
IN p_country_id INT,
IN p_postal_code VARCHAR(10),
IN p_moible_no VARCHAR(20),
IN p_primary_phone_no VARCHAR(30),
IN p_secondary_phone_no VARCHAR(30),
IN p_fax_no VARCHAR(30),
IN p_email VARCHAR(50)
)
BEGIN
INSERT INTO tblaccountmaster
(
company_name,
first_name,
last_name,
billing_address,
city,
state,
country_id,
postal_code,
mobile_no,
primary_phone_no,
secondary_phone_no,
fax_no,
email
)
VALUES
(
p_company_name,
p_first_name,
p_last_name,
p_billing_address,
p_city,
p_state,
p_country_id,
p_postal_code,
p_mobile_no,
p_primary_phone_no,
p_secondary_phone_no,
p_fax_no,
p_email
);
ENDand this is how i am calling my stored procedure from the model
Code:
$sql = "call tblaccountmaster_add
(?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
)";
$this->db->query($sql,array($this->company_name,
$this->first_name,
$this->last_name,
$this->billing_address,
$this->city,
$this->state,
$this->country_id,
$this->postal_code,
$this->mobile_no,
$this->primary_phone_no,
$this->secondary_phone_no,
$this->fax_no,
$this->email));thanks in advance ..
Stored Procedure is not executed - El Forum - 07-29-2011
[eluser]Rahul gamit[/eluser]
Code:
$sql= "call tblaccountmaster_add(".
$this->company_name.",".
$this->first_name.",".
$this->last_name.",".
$this->billing_address.",".
$this->city.",".
$this->state.",".
$this->state.",".
$this->country_id.",".
$this->postal_code.",".
$this->mobile_no.",".
$this->primary_phone_no.",".
$this->secondary_phone_no.",".
$this->fax_no.")";
if($this->db->query($sql,$data)==TRUE)
{
return TRUE;
}
else
{
return FALSE;
}help me.
thanks in advance
Stored Procedure is not executed - El Forum - 07-29-2011
[eluser]jmadsen[/eluser]
run
Code:
$this->db->last_query();Stored Procedure is not executed - El Forum - 07-29-2011
[eluser]Rahul gamit[/eluser]
Hey Jmadsen i have try this
Code:
$this->db->last_query[/code]CALL tblaccountmaster_add('indian team management','ravi','prakash','access point ','surat','gujarat','1','395004','26529','26529','2652929','2659','[email protected]') [/code]
tblaccountmaster is my table in the database and these are the values which i am passing in the stored procedure for inserting the data. but the data is not inserted in the table.
Stored Procedure is not executed - El Forum - 07-29-2011
[eluser]jmadsen[/eluser]
yes, but can you run that line in your db client tool and insert?
Stored Procedure is not executed - El Forum - 07-29-2011
[eluser]Rahul gamit[/eluser]
Hey jmadsen, the output using the $this->db->last_query() gives me this CALL tblaccountmaster_add(‘management’,‘ravi’,‘prakash’,‘access point ‘,‘surat’,‘gujarat’,‘1’,‘395004’,‘26529’,‘26529’,‘2652929’,‘2659’,‘[email protected]’).
And when i have checked this in db client tool it wont't work, there is problem related to one column that is the column in invalid, so i just checked and solved.
so my stored procedure is working..
Thank you so much for the help :-)
Stored Procedure is not executed - El Forum - 07-30-2011
[eluser]Rahul gamit[/eluser]
Hey thanks for the help this is working now,
but i am facing another problem which is as below
I am trying to edit my records using the storedprocedure of mysql.
The problem is that , when i am tracing the lastquery which has been called very last using $this->db->last_query() it gives the called storedprocedure with parameters but it wont able to update any records in database.
here is my code
My StoredProcedure
Code:
CREATE DEFINER=`root`@`localhost` PROCEDURE `tblaccountmaster_edit`(
IN p_account_id int,
IN p_company_name VARCHAR(250),
IN p_first_name VARCHAR(250),
IN p_last_name VARCHAR(250),
IN p_billing_address VARCHAR(500),
IN p_city VARCHAR(100),
IN p_state VARCHAR(100),
IN p_country_id INT,
IN p_postal_code VARCHAR(10),
IN p_mobile_no VARCHAR(20),
IN p_primary_phone_no VARCHAR(30),
IN p_secondary_phone_no VARCHAR(30),
IN p_fax_no VARCHAR(30),
IN p_email VARCHAR(50)
)
BEGIN
UPDATE tblaccountmaster
SET
company_name = p_company_name,
first_name = p_first_name,
last_name = p_last_name,
billing_address = p_billing_address,
city = p_city,
state = p_state,
country_id = p_country_id,
postal_code = p_postal_code,
mobile_no = p_mobile_no,
primary_phone_no = p_primary_phone_no,
secondary_phone_no = p_secondary_phone_no,
fax_no = p_fax_no,
email = p_email
WHERE account_id = p_account_id;
select last_insert_id() as account_id;
ENDCode:
$query= "CALL tblaccountmaster_edit(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
$result = $this->db->query($query,array(
$id,
$this->company_name,
$this->first_name,
$this->last_name,
$this->billing_address,
$this->city,
$this->state,
'1',
$this->postal_code,
$this->mobile_no,
$this->primary_phone_no,
$this->secondary_phone_no,
$this->fax_no,
$this->email));
$str = $this->db->last_query();
echo $str;Code:
CALL tblaccountmaster_edit('19','test infotech','test first name','test last name','surat','surat','gujarat','1','394210','979797979','1321321','123','265629','[email protected]')and when i am executing this procedure in my client db tool it gives me the correct result, but when i am calling from codeigniter it won’t work..
thanks in advance