problem in the login (mssql)

[eluser]huangxiao[/eluser]
Hi, I have problem,why is it my query failed if i connect to MSSQL server..and it raises an error "Fatal error: Call to a member function num_rows() on a non-object "...pointing to

if($query->num_rows() == 1)

Code:

function login_check(){

       $this->db->where('username',$this->input->post('username'));
       $this->db->where('password',$this->input->post('password'));

       $query = $this->db->get('user');


       if($query->num_rows() == 1){//Here i get error.
           foreach($query->result() as $row){
               $this->session->set_userdata('empcode',$row->emp_code);
           }

           return true;
       }
       else {
           return false;
       }


   }

Thank you in advance.

[eluser]Tim Brownlaw[/eluser]
Well things to check...

Is the table name actually user? Does it have a prefix?

Have you checked the generated sql - you can do that using echo $this->db->last_query(); after the db get...

Do you have any other queries like this that work?

What you'll probably see if you were to do a var_dump($query) is bool(false) or something similar...

So for whatever reason it's not getting back a valid result object... The trick is to dig back and see whats going on!

Cheers
Tim

[eluser]huangxiao[/eluser]
This is the result of echo $this->db->last_query();

SELECT * FROM user WHERE username = 'admin' AND password = 'admin'

var_dump($query)

is false

[eluser]Tim Brownlaw[/eluser]
Well let's just see what's not happening!

First, have you been able to connect and run that query via a php script? - Not Using Codeigniter!

This is just to establish that the system is configured where you can get a connection to the DB and run a query on it.

[eluser]huangxiao[/eluser]
Hi,
I put this in database.php

Code:

$server = 'XIAO-PC\SQLEXPRESS2012';
$connectionInfo = array( "Database"=>"msdb", "UID"=>"admin", "PWD"=>"admin");
$conn = sqlsrv_connect( $server, $connectionInfo);


if (!$conn) {
    die( print_r( sqlsrv_errors(), true));
}
else{
    echo "connected";
}

and it says connected.

[eluser]Tim Brownlaw[/eluser]
[quote author="huangxiao" date="1398822254"]Hi,
I put this in database.php

Code:

$server = 'XIAO-PC\SQLEXPRESS2012';
$connectionInfo = array( "Database"=>"msdb", "UID"=>"admin", "PWD"=>"admin");
$conn = sqlsrv_connect( $server, $connectionInfo);

<snip>

and it says connected.[/quote]

That's good, as it means its all setup for PHP to play with.

Ok so you can dig down into the sqlsrv driver under /system/database/drivers/sqlsrv_driver.php and check out the db_connect method...

In the /config/database.php you would have...

Code:

...
$db['default']['hostname'] = 'XIAO-PC\SQLEXPRESS2012';
$db['default']['username'] = 'admin';
$db['default']['password'] = 'admin';
$db['default']['database'] = 'msdb';
$db['default']['dbdriver'] = 'sqlsrv';
$db['default']['dbprefix'] = '';       // If you have one set...
$db['default']['pconnect'] = TRUE;     // If you want a persistent connection
...

Now I've never used or intend to use a MS anything... so I'm only working back from what info you have provided and what a little digging has come up with...

See how that flies but there's no guarantees with this...


Cheers
Tim

[eluser]huangxiao[/eluser]
Hi,

This is set up in my database.php

Code:

$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] =  'XIAO-PC\SQLEXPRESS2012';
$db['default']['username'] = 'admin';
$db['default']['password'] = 'admin';
$db['default']['database'] = 'msdb';
$db['default']['dbdriver'] = 'sqlsrv';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Here is the sqlsrv_driver.php "db_connect()"

Code:

/**
  * Non-persistent database connection
  *
  * @access private called by the base class
  * @return resource
  */
function db_connect($pooling = false)
{
  // Check for a UTF-8 charset being passed as CI's default 'utf8'.
  $character_set = (0 === strcasecmp('utf8', $this->char_set)) ? 'UTF-8' : $this->char_set;

  $connection = array(
   'UID'    => empty($this->username) ? '' : $this->username,
   'PWD'    => empty($this->password) ? '' : $this->password,
   'Database'   => $this->database,
   'ConnectionPooling' => $pooling ? 1 : 0,
   'CharacterSet'  => $character_set,
   'ReturnDatesAsStrings' => 1
  );
  
  // If the username and password are both empty, assume this is a
  // 'Windows Authentication Mode' connection.
  if(empty($connection['UID']) && empty($connection['PWD'])) {
   unset($connection['UID'], $connection['PWD']);
  }

  return sqlsrv_connect($this->hostname, $connection);
}

is there anything else to look up?

[eluser]Tim Brownlaw[/eluser]
Ok, so back in your test php connection code...

Can you run a query on SELECT * FROM user WHERE username = ‘admin’ AND password = ‘admin’
and see what that says...

I'm guessing others have had this same issue in the past, and there seems to be something funky when it comes to mssql... but you're the guinea pig as I dont have a setup to try things quickly here...

So let's see if it likes that query that CI created!

[eluser]huangxiao[/eluser]
Hi Tim,

you mean to put this "SELECT * FROM user WHERE username = ‘admin’ AND password = ‘admin’"

in database.php ?

[eluser]huangxiao[/eluser]
[quote author="Tim Brownlaw" date="1398855601"]Ok, so back in your test php connection code...

Can you run a query on SELECT * FROM user WHERE username = ‘admin’ AND password = ‘admin’
and see what that says...

I'm guessing others have had this same issue in the past, and there seems to be something funky when it comes to mssql... but you're the guinea pig as I dont have a setup to try things quickly here...

So let's see if it likes that query that CI created!

[/quote]


Hi, Tim
I think i got it now.I successfully login,...my question is why is it that i need to do like this in my query

Code:

$this->db->where('username',$this->input->post('username'));
       $this->db->where('password',$this->input->post('password'));

       $query = $this->db->get('[msdb].[dbo].[user]');<--------This is the code it works [msdb].[dbo].[user]

I see some codes working without using my approach...please enlighten my mind.