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

Question

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
)

That seems to be the correct way to save in CakePHP, although, I dont see what you are saving. You are not passing anything into your save method/action, such as `$this->MyModel->save($this->request->data);`. That is if the data is being passed to the model in that manner. — AKKAweb, Jan 27 '14 at 04:45
@AKKA-Web Thanks. actually the only data to save is the value for the identity column which I want it to be auto assigned by the database. You can check the table schema above in my edited post above. — VCD, Jan 27 '14 at 06:32
@Anubhav Thanks for reminding. I have included the schema in my edited post above. — VCD, Jan 27 '14 at 06:33
change property of [id] column as [id] INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT, your code is correct. — Anubhav, Jan 27 '14 at 06:43
@Anubhav I am working on MS SQL actually, not MySQL. AUTO_INCREMENT is only valid on MySQL but not MS SQL. — VCD, Jan 27 '14 at 06:48
http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012 — Anubhav, Jan 27 '14 at 06:54
@Anubhav The link you shared is mentioned that I should use IDENTITY instead of AUTO_INCREMENT that you have suggested — VCD, Jan 27 '14 at 07:02
Yes and it has one solution also, ie. you have make identity auto increment int IDENTITY(1,1) for reference http://msdn.microsoft.com/en-us/library/ms186775.aspx — Anubhav, Jan 27 '14 at 07:05
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/46136/discussion-between-vcd-and-anubhav) — VCD, Jan 27 '14 at 07:07

VCD · Answer 1 · 2014-01-30T01:28:28.010

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();

score 0 · Accepted Answer · answered Jun 29 '16 at 17:26

For demonstration purposes the current model is written as '__THE_CURRENT_MODEL__' which is, of course, not a proper CakePHP model name. Duh!

    public function add() {
        if( $this->request->is('post') ){
            $this->__THE_CURRENT_MODEL__->create();
            if( $this->__THE_CURRENT_MODEL__->save(
                array(
                    '__THE_CURRENT_MODEL__' => array( 'id' => 'NULL' )
                )
            ) ){
                $this->Flash->success(__('The __current_model__ has been saved.'));
                return $this->redirect( array( 'action' => 'index' ) );
            }else{
                $this->Flash->error(__('The __current_model__ could not be saved. Please, try again.'));
            }
        }
    }

As you should note - the value 'NULL' is not NULL .. which would result in no query at all - the id column is numeric.

I no longer have access to the environment and I am too lazy to setup one from scratch to test it. Anyway I will still mark this as answer. — VCD, Jul 02 '16 at 11:38

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

2 Answers2

Linked