VCD VCD - 5 months ago 16
SQL Question

Using CakePHP to insert rows into a table with one IDENTITY column only

In an existing post (link below) I already know how to insert a row into a table with one identity column for Microsoft SQL Server.

Inserting rows into a table with one IDENTITY column only

and now I want to use CakePHP to do the same. However when I try the following, the record will not be saved.

$this -> MyModel -> create();
if ($this -> MyModel -> save()) {
$this -> log('Record is saved', 'debug');
} else {
// Always run the following
$this -> log('Record is not saved', 'debug');
}


What would be the CakePHP way to save such record into the table?

The table schema for MyModel is below:

CREATE TABLE [dbo].[my_models] (
[id] INT NOT NULL PRIMARY KEY IDENTITY
)

VCD VCD
Answer

This is how I ended up in my code. In addition to the created row this also retrieve the generated value for the identity column. I would prefer using $this -> MyModel -> save(); but since I can't figure out how to do so I can only archive the result using direct SQL execution.

If someone can suggest a way to answer my question $this -> MyModel -> save();, I would be happy to mark that as answer.

Note: The SQL might not work on some older version of MS SQL Server.

$sql = 'INSERT INTO ' . $this -> MyModel -> table . ' OUTPUT INSERTED.' . $this -> MyModel -> primaryKey . ' DEFAULT VALUES;';
$dbh = $this -> MyModel -> getDataSource() -> getConnection();
$stmt = $dbh -> prepare($sql);
$stmt -> execute();
$sqlResult = $stmt -> fetch(PDO::FETCH_NUM);
$stmt -> closeCursor();