Help Required! Stored Procedures and MySQL Functions in CI.

[eluser]Falney[/eluser]
Hello

For a couple of days now I have been bashing my head off the keyboard trying to get some stored procedures from an already developed system to work with code igniter.

They are pretty simple tasks and I would much prefer to use the Active Record class for them but alas it is not possible. I MUST use the stored procedures.

I have read through several topics on the matter and I have failed thus far to get any of the solutions to work for me.

I need to be able to either call
$this->db->query("call acquireuserpasswd('$uname', @id,@passwd);");
Or
$this->db->query("SELECT fn_varbintohexsubstring (1,'$passwd',1,0) AS result");

Like I have said. I have been at this problem for about 2 days now. I would rather not have to create a work around outside CI or even build the site and php from scratch without CI. So, any help you guys can provide would be much appreciated.


Fal

[eluser]obiron2[/eluser]
This is just a guess but if you pass $uname inside single quotes I don't think it is parsed. I.e. you are passing $uname into the procedure, NOT the value of $uname.
try

Code:

$this->db->query(“call acquireuserpasswd(’".$uname."’, @id,@passwd);”);

[eluser]TheFuzzy0ne[/eluser]
Please could you explain the result you get when you use your queries? Are they automatically being escaped when you don't want them to be, or is CodeIgniter doing something else to your query?

[eluser]Falney[/eluser]
There are no errors popping up. Even if I populate the variables as constants, i.e. an actual username that I know for a fact exists, the query comes back with no results in any way shape or form.

If I go

Code:

$query = $this->db->query(“call acquireuserpasswd(’$uname’, @id,@passwd);”);
$return = $this->db->query("SELECT @passwd");
foreach($return->result() as $row){
   $password = $row->passwd;
}

Nothing happens.

if I try debugging with

Code:

echo $return;
echo "testline<br>";
print_r($return, TRUE);

it comes up with an understandable error about the $return object but the print_r() function yields no text.

I am at a loss as to what I need to do. I really wish I could just do in with the stored procedure but it really is not an option.

[eluser]TheFuzzy0ne[/eluser]
Are you sure your stored procedure is working correctly?

You can enable the [url="http://ellislab.com/codeigniter/user-guide/general/profiling.html"]profiler[/url] with:

Code:

$this->output->enable_profiler(TRUE); // Either at the top of your controller method, or in your constructor.

and that will show you what queries are being made.