/** * Will grab the auto incremented value from the last query (if one exists) * * @param fResult $result The result object for the query * @param mixed $resource Only applicable for `pdo`, `oci8` and `sqlsrv` extentions or `mysqli` prepared statements - this is either the `PDOStatement` object, `mysqli_stmt` object or the `oci8` or `sqlsrv` resource * @return void */ private function handleAutoIncrementedValue($result, $resource = NULL) { if (!preg_match('#^\\s*INSERT\\s+(?:INTO\\s+)?(?:`|"|\\[)?(["\\w.]+)(?:`|"|\\])?#i', $result->getSQL(), $table_match)) { $result->setAutoIncrementedValue(NULL); return; } $quoted_table = $table_match[1]; $table = str_replace('"', '', strtolower($table_match[1])); $insert_id = NULL; if ($this->type == 'oracle') { if (!isset($this->schema_info['sequences'])) { $sql = "SELECT\n\t\t\t\t\t\t\t\tLOWER(OWNER) AS \"SCHEMA\",\n\t\t\t\t\t\t\t\tLOWER(TABLE_NAME) AS \"TABLE\",\n\t\t\t\t\t\t\t\tTRIGGER_BODY\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tALL_TRIGGERS\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tTRIGGERING_EVENT LIKE 'INSERT%' AND\n\t\t\t\t\t\t\t\tSTATUS = 'ENABLED' AND\n\t\t\t\t\t\t\t\tTRIGGER_NAME NOT LIKE 'BIN\$%' AND\n\t\t\t\t\t\t\t\tOWNER NOT IN (\n\t\t\t\t\t\t\t\t\t'SYS',\n\t\t\t\t\t\t\t\t\t'SYSTEM',\n\t\t\t\t\t\t\t\t\t'OUTLN',\n\t\t\t\t\t\t\t\t\t'ANONYMOUS',\n\t\t\t\t\t\t\t\t\t'AURORA\$ORB\$UNAUTHENTICATED',\n\t\t\t\t\t\t\t\t\t'AWR_STAGE',\n\t\t\t\t\t\t\t\t\t'CSMIG',\n\t\t\t\t\t\t\t\t\t'CTXSYS',\n\t\t\t\t\t\t\t\t\t'DBSNMP',\n\t\t\t\t\t\t\t\t\t'DIP',\n\t\t\t\t\t\t\t\t\t'DMSYS',\n\t\t\t\t\t\t\t\t\t'DSSYS',\n\t\t\t\t\t\t\t\t\t'EXFSYS',\n\t\t\t\t\t\t\t\t\t'FLOWS_020100',\n\t\t\t\t\t\t\t\t\t'FLOWS_FILES',\n\t\t\t\t\t\t\t\t\t'LBACSYS',\n\t\t\t\t\t\t\t\t\t'MDSYS',\n\t\t\t\t\t\t\t\t\t'ORACLE_OCM',\n\t\t\t\t\t\t\t\t\t'ORDPLUGINS',\n\t\t\t\t\t\t\t\t\t'ORDSYS',\n\t\t\t\t\t\t\t\t\t'PERFSTAT',\n\t\t\t\t\t\t\t\t\t'TRACESVR',\n\t\t\t\t\t\t\t\t\t'TSMSYS',\n\t\t\t\t\t\t\t\t\t'XDB'\n\t\t\t\t\t\t\t\t)"; $this->schema_info['sequences'] = array(); foreach ($this->query($sql) as $row) { if (preg_match('#SELECT\\s+(["\\w.]+).nextval\\s+INTO\\s+:new\\.(\\w+)\\s+FROM\\s+dual#i', $row['trigger_body'], $matches)) { $table_name = $row['table']; if ($row['schema'] != strtolower($this->username)) { $table_name = $row['schema'] . '.' . $table_name; } $this->schema_info['sequences'][$table_name] = array('sequence' => $matches[1], 'column' => str_replace('"', '', $matches[2])); } } if ($this->cache) { $this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info); } } if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+"?' . preg_quote($quoted_table, '#') . '"?\\s+\\([^\\)]*?(\\b|")' . preg_quote($this->schema_info['sequences'][$table]['column'], '#') . '(\\b|")#i', $result->getSQL())) { return; } $insert_id_sql = "SELECT " . $this->schema_info['sequences'][$table]['sequence'] . ".currval AS INSERT_ID FROM dual"; } if ($this->type == 'postgresql') { if (!isset($this->schema_info['sequences'])) { $sql = "SELECT\n\t\t\t\t\t\t\t\tpg_namespace.nspname AS \"schema\",\n\t\t\t\t\t\t\t\tpg_class.relname AS \"table\",\n\t\t\t\t\t\t\t\tpg_attribute.attname AS column\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tpg_attribute INNER JOIN\n\t\t\t\t\t\t\t\tpg_class ON pg_attribute.attrelid = pg_class.oid INNER JOIN\n\t\t\t\t\t\t\t\tpg_namespace ON pg_class.relnamespace = pg_namespace.oid INNER JOIN\n\t\t\t\t\t\t\t\tpg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tNOT pg_attribute.attisdropped AND\n\t\t\t\t\t\t\t\tpg_attrdef.adsrc LIKE 'nextval(%'"; $this->schema_info['sequences'] = array(); foreach ($this->query($sql) as $row) { $table_name = strtolower($row['table']); if ($row['schema'] != 'public') { $table_name = $row['schema'] . '.' . $table_name; } $this->schema_info['sequences'][$table_name] = $row['column']; } if ($this->cache) { $this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info); } } if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+"?' . preg_quote($quoted_table, '#') . '"?\\s+\\([^\\)]*?(\\b|")' . preg_quote($this->schema_info['sequences'][$table], '#') . '(\\b|")#i', $result->getSQL())) { return; } } if ($this->extension == 'ibm_db2') { $insert_id_res = db2_exec($this->connection, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1"); $insert_id_row = db2_fetch_assoc($insert_id_res); $insert_id = current($insert_id_row); db2_free_result($insert_id_res); } elseif ($this->extension == 'mssql') { $insert_id_res = mssql_query("SELECT @@IDENTITY AS insert_id", $this->connection); $insert_id = mssql_result($insert_id_res, 0, 'insert_id'); mssql_free_result($insert_id_res); } elseif ($this->extension == 'mysql') { $insert_id = mysql_insert_id($this->connection); } elseif ($this->extension == 'mysqli') { if (is_object($resource)) { $insert_id = mysqli_stmt_insert_id($resource); } else { $insert_id = mysqli_insert_id($this->connection); } } elseif ($this->extension == 'oci8') { $oci_statement = oci_parse($this->connection, $insert_id_sql); oci_execute($oci_statement, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS); $insert_id_row = oci_fetch_array($oci_statement, OCI_ASSOC); $insert_id = $insert_id_row['INSERT_ID']; oci_free_statement($oci_statement); } elseif ($this->extension == 'pgsql') { $insert_id_res = pg_query($this->connection, "SELECT lastval()"); $insert_id_row = pg_fetch_assoc($insert_id_res); $insert_id = array_shift($insert_id_row); pg_free_result($insert_id_res); } elseif ($this->extension == 'sqlite') { $insert_id = sqlite_last_insert_rowid($this->connection); } elseif ($this->extension == 'sqlsrv') { $insert_id_res = sqlsrv_query($this->connection, "SELECT @@IDENTITY AS insert_id"); $insert_id_row = sqlsrv_fetch_array($insert_id_res, SQLSRV_FETCH_ASSOC); $insert_id = $insert_id_row['insert_id']; sqlsrv_free_stmt($insert_id_res); } elseif ($this->extension == 'pdo') { switch ($this->type) { case 'db2': $insert_id_statement = $this->connection->query("SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1"); $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC); $insert_id = array_shift($insert_id_row); $insert_id_statement->closeCursor(); unset($insert_id_statement); break; case 'mssql': try { $insert_id_statement = $this->connection->query("SELECT @@IDENTITY AS insert_id"); if (!$insert_id_statement) { throw new Exception(); } $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC); $insert_id = array_shift($insert_id_row); } catch (Exception $e) { // If there was an error we don't have an insert id } break; case 'oracle': try { $insert_id_statement = $this->connection->query($insert_id_sql); if (!$insert_id_statement) { throw new Exception(); } $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC); $insert_id = array_shift($insert_id_row); } catch (Exception $e) { // If there was an error we don't have an insert id } break; case 'postgresql': $insert_id_statement = $this->connection->query("SELECT lastval()"); $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC); $insert_id = array_shift($insert_id_row); $insert_id_statement->closeCursor(); unset($insert_id_statement); break; case 'mysql': $insert_id = $this->connection->lastInsertId(); break; case 'sqlite': $insert_id = $this->connection->lastInsertId(); break; } } $result->setAutoIncrementedValue($insert_id); }
/** * Will grab the auto incremented value from the last query (if one exists) * * @param fResult $result The result object for the query * @return void */ private function handleAutoIncrementedValue($result) { if (!preg_match('#^\\s*INSERT\\s+INTO\\s+(?:`|"|\\[)?(\\w+)(?:`|"|\\])?#i', $result->getSQL(), $table_match)) { $result->setAutoIncrementedValue(NULL); return; } $table = strtolower($table_match[1]); $insert_id = NULL; if ($this->type == 'oracle') { if (!isset($this->schema_info['sequences'])) { $sql = "SELECT\r\n\t\t\t\t\t\t\t\tTABLE_NAME,\r\n\t\t\t\t\t\t\t\tTRIGGER_BODY\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tUSER_TRIGGERS\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tTRIGGERING_EVENT = 'INSERT' AND\r\n\t\t\t\t\t\t\t\tSTATUS = 'ENABLED' AND\r\n\t\t\t\t\t\t\t\tTRIGGER_NAME NOT LIKE 'BIN\$%'"; $this->schema_info['sequences'] = array(); foreach ($this->query($sql) as $row) { if (preg_match('#SELECT\\s+(\\w+).nextval\\s+INTO\\s+:new\\.(\\w+)\\s+FROM\\s+dual#i', $row['trigger_body'], $matches)) { $this->schema_info['sequences'][strtolower($row['table_name'])] = array('sequence' => $matches[1], 'column' => $matches[2]); } } if ($this->cache) { $this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info); } } if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+' . preg_quote($table, '#') . '\\s+\\([^\\)]*?\\b' . preg_quote($this->schema_info['sequences'][$table]['column'], '#') . '\\b#i', $result->getSQL())) { return; } $insert_id_sql = "SELECT " . $this->schema_info['sequences'][$table]['sequence'] . ".currval AS INSERT_ID FROM dual"; } if ($this->type == 'postgresql') { if (!isset($this->schema_info['sequences'])) { $sql = "SELECT\r\n\t\t\t\t\t\t\t\tpg_class.relname AS table_name,\r\n\t\t\t\t\t\t\t\tpg_attribute.attname AS column\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tpg_attribute INNER JOIN\r\n\t\t\t\t\t\t\t\tpg_class ON pg_attribute.attrelid = pg_class.oid INNER JOIN\r\n\t\t\t\t\t\t\t\tpg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tNOT pg_attribute.attisdropped AND\r\n\t\t\t\t\t\t\t\tpg_attrdef.adsrc LIKE 'nextval(%'"; $this->schema_info['sequences'] = array(); foreach ($this->query($sql) as $row) { $this->schema_info['sequences'][strtolower($row['table_name'])] = $row['column']; } if ($this->cache) { $this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info); } } if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+' . preg_quote($table, '#') . '\\s+\\([^\\)]*?\\b' . preg_quote($this->schema_info['sequences'][$table], '#') . '\\b#i', $result->getSQL())) { return; } } if ($this->extension == 'mssql') { $insert_id_res = mssql_query("SELECT @@IDENTITY AS insert_id", $this->connection); $insert_id = mssql_result($insert_id_res, 0, 'insert_id'); mssql_free_result($insert_id_res); } elseif ($this->extension == 'mysql') { $insert_id = mysql_insert_id($this->connection); } elseif ($this->extension == 'mysqli') { $insert_id = mysqli_insert_id($this->connection); } elseif ($this->extension == 'oci8') { $oci_statement = oci_parse($this->connection, $insert_id_sql); oci_execute($oci_statement); $insert_id_row = oci_fetch_array($oci_statement, OCI_ASSOC); $insert_id = $insert_id_row['INSERT_ID']; oci_free_statement($oci_statement); } elseif ($this->extension == 'odbc' && $this->type == 'mssql') { $insert_id_res = odbc_exec($this->connection, "SELECT @@IDENTITY AS insert_id"); $insert_id = odbc_result($insert_id_res, 'insert_id'); odbc_free_result($insert_id_res); } elseif ($this->extension == 'odbc' && $this->type == 'oracle') { $insert_id_res = odbc_exec($this->connection, $insert_id_sql); $insert_id = odbc_result($insert_id_res, 'insert_id'); odbc_free_result($insert_id_res); } elseif ($this->extension == 'pgsql') { $insert_id_res = pg_query($this->connection, "SELECT lastval()"); $insert_id_row = pg_fetch_assoc($insert_id_res); $insert_id = array_shift($insert_id_row); pg_free_result($insert_id_res); } elseif ($this->extension == 'sqlite') { $insert_id = sqlite_last_insert_rowid($this->connection); } elseif ($this->extension == 'sqlsrv') { $insert_id_res = sqlsrv_query($this->connection, "SELECT @@IDENTITY AS insert_id"); $insert_id_row = sqlsrv_fetch_array($insert_id_res, SQLSRV_FETCH_ASSOC); $insert_id = $insert_id_row['insert_id']; sqlsrv_free_stmt($insert_id_res); } elseif ($this->extension == 'pdo') { switch ($this->type) { case 'mssql': try { $insert_id_statement = $this->connection->query("SELECT @@IDENTITY AS insert_id"); if (!$insert_id_statement) { throw new Exception(); } $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC); $insert_id = array_shift($insert_id_row); } catch (Exception $e) { // If there was an error we don't have an insert id } break; case 'oracle': try { $insert_id_statement = $this->connection->query($insert_id_sql); if (!$insert_id_statement) { throw new Exception(); } $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC); $insert_id = array_shift($insert_id_row); } catch (Exception $e) { // If there was an error we don't have an insert id } break; case 'postgresql': $insert_id_statement = $this->connection->query("SELECT lastval()"); $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC); $insert_id = array_shift($insert_id_row); $insert_id_statement->closeCursor(); unset($insert_id_statement); break; case 'mysql': $insert_id = $this->connection->lastInsertId(); break; case 'sqlite': $insert_id = $this->connection->lastInsertId(); break; } } $result->setAutoIncrementedValue($insert_id); }