insert_id() returns blank array with sqlsrv

[eluser]JoJo17[/eluser]
Hi

I'm using Codeigniter 2.1.4 and Ion Auth 2 and when a new user is created the ID doesn't get returned, instead a blank array is returned. I'm having to use MSSQL so am using the sqlsrv driver. In sqlsrv_driver.php the code looks like:

Code:

function insert_id()
{
  return $this->query('select @@IDENTITY as insert_id')->row('insert_id');
}

If I echo what this function returns it returns a blank array. Does anyone have a fix for this as nothing I have tried so far has worked and I haven't found a solution yet on Google!

Unfortunately the client is insisting on using MSSQL so I am stuck with this. Using MSSQL driver didn't work hence why I'm using sqlsrv. The code all worked perfectly when it was developed on MySQL, it's only since the switch to using MSSQL that I've encountered this problem (amongst others!).

[eluser]Massaki[/eluser]
Shoudn't it be $this->db->query() instead of $this->query()?

[eluser]JoJo17[/eluser]
No I wouldn't have thought so - the code example is from a core Codeigniter system file - sqlsrv_driver.php. The reason I have included the code snippet is because this is where I have traced the problem to be. It seems there are a few bugs in the sqlsrv_driver.php!

[eluser]Massaki[/eluser]
Then try this (I know it's boring, but works):

SELECT id_field FROM your_table ORDER BY id_field DESC LIMIT 1

[eluser]JoJo17[/eluser]
Yep I know I can do it that way, was looking to find a fix for the insert_id() core function so I don't have to go around re-coding everywhere that uses it.

[eluser]CroNiX[/eluser]
You might check the development branch on github as it might be fixed. If it is you could see what they altered to make it work.

[eluser]CroNiX[/eluser]
They did change it to:

Code:

/**
* Insert ID
*
* Returns the last id created in the Identity column.
*
* @return        string
*/
public function insert_id()
{
       $query = version_compare($this->version(), '8', '>=')
               ? 'SELECT SCOPE_IDENTITY() AS last_id'
               : 'SELECT @@IDENTITY AS last_id';

       $query = $this->query($query);
       $query = $query->row();
       return $query->last_id;
}

https://github.com/EllisLab/CodeIgniter/...driver.php

I don't use MSSQL so not sure if it works.

[eluser]JoJo17[/eluser]
Thanks CroNiX. That's for the mssql_driver.php and I'm using sqlsrv_driver.php. I did try it anyway and also look up the sqlsrv version but alas no joy!