database 4result issue

[eluser]sdjones[/eluser]
Hi all,

i have a very weird problem that i cant seem to solve, when i run the following i get a result object returned:

Code:

$this->query = $this->cdb->query("SELECT name FROM sys.Tables");
var_dump($this->query->result()); die();

however when i run the following:

Code:

$this->query = $this->cdb->query("SELECT replace(name, ' ', '') FROM sys.Tables");
var_dump($this->query->result()); die();

i only get a result array and the result object is blank. the only difference here is that im doing a replace on the name in the sql string.

I am running CI 2.x and using sqlsvr drivers for connectivity to MSSQL 2008. All other queries seem to be working fine, im just confused as to why this wont work

Any ideas?

[eluser]solid9[/eluser]
try this,

Code:

$this->query = $this->cdb->query("SELECT name FROM sys.Tables");
$tmp = $this->query->result();
var_dump($tmp);
die();

[eluser]sdjones[/eluser]
that's just the same as what i was doing but with additional variables..

from my debugging i can see that the sqlsvr driver is executing the sql and returnign a resourceID (sqlsrv_query returns false on failure or resourceid on success - sqlsrv_driver.php line 149)

im just backtracing the calls to see where this fails..

[eluser]solid9[/eluser]
or you can try this,

Code:

$this->query = $this->cdb->query("SELECT name FROM sys.Tables");
$tmp = $this->query->result_array();
var_dump($tmp);

[eluser]sdjones[/eluser]
agreed that works, but im curious as the why

Code:

$this->query->result_array();

works and

Code:

$this->query->result();

does not, looking at the result function in DB_result.php line 48:

Code:

/**
  * Query result.  Acts as a wrapper function for the following functions.
  *
  * @access public
  * @param string can be "object" or "array"
  * @return mixed either a result object or array
  */
public function result($type = 'object')
{
  if ($type == 'array') return $this->result_array();
  else if ($type == 'object') return $this->result_object();
  else return $this->custom_result_object($type);
}

the result function is just returning either result_object() or result_array() - by default it returns the result_object(). this is the same as calling

Code:

$db->query->result_object();

you can also use:

Code:

$db->query->result('array');

which is the same as

Code:

$db->query->result_array();

still the question stands why the object is not being populated..

[eluser]solid9[/eluser]
because this codes below,

Code:

$this->cdb->query("SELECT name FROM sys.Tables");

generate pure array.

and the codes below,

Code:

result_array()

returns the query result as a pure array, or an empty array when no result is produced.

and the codes below,

Code:

$this->query->result();

This function returns the query result as an array of objects, or an empty array on failure
note: Not pure array.

Everything is in the documentation,
http://ellislab.com/codeigniter/user-gui...sults.html

[eluser]sdjones[/eluser]
interesting, however if i add:

Code:

var_dump($this->result_object);

into DB_result.php line 127 (just before the return) i get an array of objects:

Code:

array
  0 =>
    object(stdClass)[26]
      public 'name' => string 'SecurityDomainObjectRule' (length=24)
  1 =>
    object(stdClass)[24]
      public 'name' => string 'BkgErrata' (length=9)
  2 =>
    object(stdClass)[27]
      public 'name' => string 'CancelledAllocation' (length=19)
  3 =>
    object(stdClass)[28]
      public 'name' => string 'Object' (length=6)
...

is it because the result_object is an array of objects?, if that was the case surely CI could switch that to an object

[eluser]solid9[/eluser]
@sdjones

So the codes below generate objects in an array.

Code:

$this->cdb->query("SELECT name FROM sys.Tables");

cool, that's interesting...

I learned from you also.
That's brand new to me.

[eluser]sdjones[/eluser]
ok a few hours later and i have debugged this issue. it is not a code igniter problem, its a user problem. (i went through loads of core CI code..)

so the issue here is with naming result columns. the following would only return blank:

Code:

$this->query = $this->cdb->query("SELECT replace(name, ' ', '') FROM sys.Tables");
var_dump($this->query->result()); die();

the following would return and array:

Code:

$this->query = $this->cdb->query("SELECT replace(name, ' ', '') FROM sys.Tables");
var_dump($this->query->result_array()); die();

trying to get result() or result_object() to work was proving fruitless, however the following does work:

Code:

$this->query = $this->cdb->query("SELECT replace(name, ' ', '') AS Col_Name FROM sys.Tables");
var_dump($this->query->result()); die();

notice the "AS" clause in the SQL, when tat is missing it returns a blank column name - which is fine in an array as it will display it like so:

Code:

array(
    [0] = array(''=>'some name')
    [1] = array(''=>'some name')
)

notice the blank array keys in the sub array. now with objects you cant do that - each object child must have a name, it cant be blank and thats why this was never returning an object like it should have done.

so remember to check your SQL code and name each column - hope this helps others...