ODBC and complex joins

[eluser]auximage[/eluser]
I've run into an issue when using ODBC driver to connect to MSSQL. When my query has a series of complex joins, specifically joins on two different tables that share a field name (eg. orders.id and customers.id) but are not associated with each other, ODBC likes to 'lose' one of those fields. I've tried putting this complex SQL statement in to a stored procedure in hopes that it would mask the fact that these fields have the same name, even through they're in seperate tables. This trick, however, didn't work for me.

Example:

Code:

$sql = "select so.id as so_number, so.account_id, c.id as circuit_id
from serviceorders as so
left join circuits as c on so.ADDRESS_ID = c.ADDRESS_Id
where c.id = '12345'";

$query = $this->db->query($sql);

A dump of the query object looks like this:

Code:

Array
(
    [0] => stdClass Object
        (
            [Id] => 211614
            [account_id] => 10126204
        )

    [1] => stdClass Object
        (
            [Id] => 211614
            [account_id] => 10126204
        )
)

Note that so_number doesn't exist here as a returned field, instead it's only showing Id (from c.id). Before using CI, I had this issue but was able to pull the fields using odbc_result() and defining which field to pull (a very manual process).

Is there something in the CI ODBC bridge that would fix this problem and I've just been unable to find it? Any help would be appreciated as I've searched around a bit, from using sqlsrv driver (which I could not get to work properly) to trying using CI's mssql driver (no love there either).

I should note that this is a 'slimmed down' sql statment w/ only a single join here, the actual query is much more complex, but this demonstrates the error that I'm encountering.

[eluser]WanWizard[/eluser]
The column name is used as fieldname in the resultset. If your query contains duplicate column names, only the last one is stored. This is not specific to the ODBC driver, this is how all drivers work.

I think the question should be: why doesn't 'AS' in the select work as intended? Because the result says 'Id', it should have shown 'so_number' and 'circuit_id'. No answer to that I'm afraid. You could dive into the driver, and do a var_dump of the raw result of odbc_result(). Then at least we'll know if it's a CI issue or a PHP / ODBC / Driver / MSSQL issue...

[eluser]auximage[/eluser]
From within DB_result.php::result_object()
var_dump($this->result_object);
yields:

Code:

array(2)
{
  [0]=>  object(stdClass)#16 (2)
    {
      ["Id"]=>  string(6) "211614"
      ["account_id"]=>  string(8) "10126204"
    }
  [1]=>  object(stdClass)#17 (2)
    {
      ["Id"]=>  string(6) "211614"
      ["account_id"]=>  string(8) "10126204"
    }
}

So that answers that question.. So I'm leaning toward ODBC at this point. Same issue with the num_rows functions when dealing w/ ODBC and them always returning -1.

Doing the following shows me that all the data is indeed being returned back, just getting lost in the mix:

Code:

$sql = "select so.id as so_number, so.account_id, c.id as circuit_id
from omniasojoin as so
left join circuitjoin as c on so.ADDRESS_ID = c.ADDRESS_Id
where c.id = '211614'";
        
        
        $query = $this->db->query($sql);
        
        echo "field count: " . $query->num_fields() . "<BR>";
        echo "raw field count: " . odbc_num_fields($query->result_id) . "<BR>";
        echo "Field 1: " . odbc_result($query->result_id, 1) . "<BR>";
        echo "Field 2: " . odbc_result($query->result_id, 2) . "<BR>";
        echo "Field 3: " . odbc_result($query->result_id, 3) . "<BR>";

OUTPUT:

Code:

field count: 3
raw field count: 3
Field 1: 1083671
Field 2: 10126204
Field 3: 211614

So I am getting the result back, I'm just not able to access any other method than via odbc_result(). Looks like I'll be writing a wrapper.. Any suggestions as to the best practice for that? Should I explore a hook somehow (do they even work w/ models/db?) or just dive straight into the core CI DB files and hope I remember the method I add before I upgrade.. ((c:

[eluser]tonanbarbarian[/eluser]
try change from this
select so.id as so_number, so.account_id, c.id as circuit_id

to this
select so.id as `so_number`, so.account_id, c.id as `circuit_id`

[eluser]auximage[/eluser]
Backtic's produced an error:

Code:

Error Number: 37000

[unixODBC][FreeTDS][SQL Server]Incorrect syntax near '`'.

I'll just stick with writing a new method or two and putting it in the db_result file to handle this, unless someone else out there has another idea.

[eluser]auximage[/eluser]
So I whipped up a quick hack for this. You'll have to specify in an array what field names you are expecting back and pass that array into this method, but it 'should' spit back what you need. Any suggestions for better execution is welcome as I'm no PHP ninja. Just thought I would post this for the person 2 years from now that will encounter the same issue. Disclaimer, this is a total hack and is not fully tested and may not even work for you.. YMMV

Code:

/**
     *  Return back array of field names and values.  For use when using joins
     *    across tables that return field names that are the same.
     * @access public
     * @param array $field_array Array of field names in proper order from query
     * @return array
     */
    function my_result_array($field_array) {
        
        $record = 0;  //Set a counter for recrods
        $field_count = $this->num_fields(); //Do a quick sanity check for field counts
        $this->_data_seek(0);  //set it back to 0
        
        if (count($field_array) != $field_count) {
            echo "Count mismatch!";
            return 0; //Probably would want to throw an exception here
        } else {
            
            while ($temp = odbc_fetch_into($this->result_id, $counter)) {
                $i = 1;
                foreach ($field_array as $field_name) {
                    $this->result_array[$record][$field_name] = odbc_result($this->result_id, $i);
                    $i++;
                    
                }
                $record++;
            }
        }
        
        return $this->result_array;
        
    }