Phalcon\Db insert that returns inserted id

Hi All,

I have a poblem with the Phalcon\Db insert method. I want to return the current inserted record's id. Is this possible?

public function createSession($clientId, $ownerType, $ownerId)
            [$clientId, $ownerType, $ownerId],
            ['client_id', 'owner_type', 'owner_id']
        return $this->db->lastInsertId();                        

// Here is the lastInsertID function    
function lastInsertId(){
        $sql = 'SELECT CAST(SCOPE_IDENTITY() AS int) AS ID';               
        $this->db->fetchOne($sql, Db::FETCH_ASSOC);        
        return $id;

I use a custom mssql adapter to connect my SQLSRV database and it works fine but I cannot call a method ( SELECT SCOPE_IDENTITY() ) to return the inserted ID. It just returns null.

Please help.



I think you have to return the fetchOne into one variable and then return the value:

function lastInsertId(){
        $sql = 'SELECT CAST(SCOPE_IDENTITY() AS int) AS ID';               
        $id = $this->db->fetchOne($sql, Db::FETCH_NUM);        
        return $id[0];

edited Aug '14

Hi @Phalcon, I still cant get this to work. So it looks like this is a PHP MSSQL Driver issue. I can get around this problem by doing something like this :

    $tsql = "INSERT INTO oauth_sessions(client_id,owner_type,owner_id) VALUES (?,?,?); SELECT SCOPE_IDENTITY() AS NewUserId";
    $params = array('aaa','user',3);
    $id = $this->db->query($tsql,$params);// Db::FETCH_ASSOC,         

But now I get this MSSQL Error : PDOException: SQLSTATE[IMSSP]: The active result for the query contains no fields.

Any ideas what I can do about this?

My other workaround is just to return the MAX id of the table then.



Oops, just read now that PHQL only supports one statement at a time and not composite statements ( to avoid SQL Injection ) Reference : PHQL only allows one SQL statement to be executed per call preventing injections

So something like this wont work :

 $query1 = "select * from Person.Address where City = 'Bothell';";
 $query2 = "select * from Person.ContactType;";

 $stmt = $conn->query( $query1 . $query2 );

 $rowset1 = $stmt->fetchAll();
 $rowset2 = $stmt->fetchAll();
 var_dump( $rowset1 );
 var_dump( $rowset2 );

So for now i'm sticking to returning the MAX id of the table.