mssql $database->id() is not work.

[avavolcano]

$database->insert("account", [ "user_name" => "foo", "email" => "foo@bar.com", "age" => 25 ]);

$account_id = $database->id(); var_dump($account_id ); //this is null!

[catfan]

Did you check out the id column is correctly configured?

[bradleymyers]

catfan, What do you mean by id column correctly configured? My id column is integer as PK w/ auto Increment. Using mssql. Having same problem. Want to say this was working at some point. Returns null.

[bradleymyers]

Catfan, here is a bit more information. The record gets created in the db fine. Just no id.

ARRAY $dataArray=array("mtyId"=>$_SESSION["store"],"typeId"=>null,"type"=>"photo","subType"=>$_POST["type"],"title"=>null,"description"=>$_POST["description"],"postDate"=>null,"date"=>null,"endDate"=>null,"status"=>"pending","lastModified"=>$database->dateFormatDb(),"lastModifiedBy"=>$_SESSION["user"]["Username"]);

Function public function genericInsert($dataArray, $table){ $this->debug(false); $this->insert($table,$dataArray); $error = $this->error(); if(!empty($error[2])){ $return["id"] = ""; $return["msg"] = $error[2]; $return["msgType"] = "light-blue accent-2"; }else{ $return["id"] = $this->id(); $return["msg"] = $this->tablePhrase($table)." item created."; $return["msgType"] = "green"; } return $return; }

Function Call $data = $database->genericInsert($dataArray, "approvalQ");

[bradleymyers]

I believe I have figured this out.

` public function id() { $type = $this->type;

    if ($type === 'oracle')
    {
        return 0;
    }
    elseif ($type === 'mssql')
    {
        //return $this->pdo->query('SELECT SCOPE_IDENTITY()')->fetchColumn();
        return $this->pdo->lastInsertId();
    }
    elseif ($type === 'pgsql')
    {
        return $this->pdo->query('SELECT LASTVAL()')->fetchColumn();
    }

    return $this->pdo->lastInsertId();
}

`

[philwu]

Thank you, @bradleymyers . It works for me on: SQLServer 2008R2 with PHP7.1.1 SQLServer 2016 with PHP7.2.12

Hope @catfan can pull this.

[bradleymyers]

I ended up just commenting out mssql case and used the default return.

[catfan]

Well, getting the inserted id for MSSQL is not just easy. There are a lot of problem and compatibility with this. I will test more version and find a better way for this database.

[bradleymyers]

Yeah I get that. I was playing around and weighing my options. The safest way I think is to use an application id that is generated and use that to select the record back. But that's not something that should be included in your code I don't think. Thanks for your help.

On Sat, Nov 24, 2018, 11:44 AM Catfan <notifications@github.com wrote:

Well, getting the inserted id for MSSQL is not just easy. There are a lot of problem and compatibility with this. I will test more version and find a better way for this database.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/catfan/Medoo/issues/683#issuecomment-441387620, or mute the thread https://github.com/notifications/unsubscribe-auth/ATFFDlCzgygzFn4rOk8DaVB39qXdldXWks5uyZNwgaJpZM4Q9zkG .

[catfan]

I fixed that for MSSQL 89f06bb87179fe5d7fbbc8dbdf2ed87b9cf98e5a.

PS: We will drop support using dblib or odbc driver for MSSQL.

pdo_sqlsrv will be the better for all platform from now.