Phalcon\Db insert that returns inserted id

Question

Phalcon\Db insert that returns inserted id

Chesney Aug '14

Created Aug '14	Last Reply Aug '14	Replies 1	Views 1485	Votes 0

Chesney 22.8k

Aug '14

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)
{
        $this->db->insert(
            $this->tables['oauth_sessions'],
            [$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.

Thanks!

Phalcon · Answer 1 · 2014-08-02T06:22:16-07:00

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];
}

Chesney · Answer 2 · 2014-08-04T02:22:16-07:00

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.

Thanks

Chesney · Answer 3 · 2014-08-04T03:06:50-07:00

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 https://docs.phalcon.io/en/latest/reference/phql.html

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();
 $stmt->nextRowset();
 $rowset2 = $stmt->fetchAll();
 var_dump( $rowset1 );
 var_dump( $rowset2 );

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