MSSQL Stored Procedure & Active Record

[eluser]davidk43[/eluser]
I'm wondering what is the best way to execute a stored procedure from Codeigniter. I have an application that is running on MSSQL2008 (for its XQuery support) and would like to know what is the best way to comply and use codeigniters active record database class without completely rolling my own solution.

Outside of CI I'd normally write something native like this:

Code:

$stmt=mssql_init("myprocedure", $conn);

/* now bind the parameters to it */
mssql_bind($stmt, "@id",    $id,    SQLINT4,    FALSE);
mssql_bind($stmt, "@name",  $name,  SQLVARCHAR, FALSE);
mssql_bind($stmt, "@email", $email, SQLVARCHAR, FALSE);    
        
/* now execute the procedure */
$result = mssql_execute($stmt);

Is it best I keep it separate and roll my own or is there a nifty accepted way to do this within CI's mssql driver?

thanks for any thoughts.
David

[eluser]titandj[/eluser]
Hi!

I have your same issue, need a execute sp but i can not use the DB class.

One half solution is run a query thus:

$result = $this->db->query('exec sp_procedure');

I do not know how to handle the passing of parameters

Do you have a best solution for this?

[eluser]davidk43[/eluser]
Yeah, I've come to the same conclusion. There is no way of using Active Record and Stored Procedures so a custom query it is. I suppose at the end of the day, the security and verification I wanted to harness from CI on the way in to the database can be done at the Stored Procedure level anyway. Just would have been nice for completeness.

[eluser]InsiteFX[/eluser]
Read this thread!

Stored Procedures

InsiteFX

SEARCH IS YOUR FRIEND!

[eluser]davidk43[/eluser]
Thanks InsiteFX but I have read that thread and its very specific to the mysqli driver. This issue is specifically for the mssql driver and binding variables to the execute statement suitably.

[eluser]Kamarg[/eluser]
I also use stored procedures with MSSQL I still haven't found a way to do what you want. I have to manually append my parameters to the query string (and make sure I escape them properly). Not really helpful but as far as I can tell that functionality doesn't exist.

[eluser]imorris[/eluser]
[quote author="Kamarg" date="1284490834"]I also use stored procedures with MSSQL I still haven't found a way to do what you want. I have to manually append my parameters to the query string (and make sure I escape them properly). Not really helpful but as far as I can tell that functionality doesn't exist.[/quote]

Can you provide an example of how to manually append parameters to the query string and make sure escape them properly?

[eluser]Kamarg[/eluser]
I do something similar to this. It's written from memory since I don't have access to my code at the moment but it should be close enough that you can change it to suit your needs.

Code:

$sql = 'exec stored_proc_name ' . implode(', ', escape($parameter_array));
$return = $this->db->query($sql);

function escape(&$fields, $db = null) {
    $db = is_null($db) ? $this->db : $db;
    $fields = (array) $fields;
        
    if(is_array($fields)) {
        foreach($fields as $k => $v) {
            if(is_numeric($v)) {
                $fields[$k] = $v;
            } else if(is_bool($v)) {
                $fields[$k] = $db->escape($v ? 'True' : 'False');
            } else if(is_null($v)) {
                $fields[$k] = 'NULL';
            } else if(is_string($v)) {
                $fields[$k] = $db->escape($v);
            } else {
                $fields[$k] = $db->escape($v);
            }
        }
    } else {
        return FALSE;
    }
        
    return $fields;
}