Asked  7 Months ago    Answers:  5   Viewed   50 times

What the question title says. With a query such as SELECT @@IDENTITY AS ins_id, do I need to supply the table name or any other info to specify which table/database i'm talking about?

 Answers

27

@@IDENTITY returns the most recent identity generated in the current session. In most cases you'll probably want to use SCOPE_IDENTITY instead, which returns the most recent identity generated in the current scope.

For example, if you insert a row into table1, but that insert fires a trigger which inserts a row into table2, then @@IDENTITY will return the identity from table2 whereas SCOPE_IDENTITY will return the identity from table1.

INSERT INTO my_table (my_column) VALUES ('test')

-- return the identity of the row you just inserted into my_table
-- regardless of any other inserts made by triggers etc
SELECT SCOPE_IDENTITY() AS ins_id
Wednesday, March 31, 2021
 
PedroKTFC
answered 7 Months ago
57

Subtract the past most one from the future most one and divide by 60.

Times are done in Unix format so they're just a big number showing the number of seconds from January 1, 1970, 00:00:00 GMT

Wednesday, March 31, 2021
 
Gregosaurus
answered 7 Months ago
40

It is implied that you should use another package for reading the .env files. There is a sample on their official site that uses a Dotenv class to load contents of the files into environment:

<?php

require 'vendor/autoload.php';
Dotenv::load(__DIR__);
$sendgrid_apikey = getenv('YOUR_SENDGRID_APIKEY');

Still, they don't even explain where the class comes from. Apparently, they mean that you should install vlucas/phpdotenv package. Note, that in the current version of this package, the load method is non-static (it actually was static in early versions):

$dotenv = new DotenvDotenv(__DIR__);
$dotenv->load();
Saturday, May 29, 2021
 
inieto
answered 5 Months ago
31

1. How to retrieve Date and Time values from SQL Server:

1.1. Retrieve Date and Time values from SQL Server uisng the PDO_SQLSRV version of PHP Driver for SQL Server:

When you use PDO_SQLSRV driver, values from date and time columns are returned as strings before version 5.6.0 of the driver. In this case the only option is to reformat date and time values (from '2019-03-26 00:00:00.000' to '26-03-2019 00:00:00.000' for example). Starting with version 5.6.0, you can change this behavior using PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE connection or statement attribute and retrieve date and time field values as PHP DateTime variable. Then you can easily format this variable as string using DateTime::format.

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

try {
   $conn = new PDO("sqlsrv:server = $server; Database = $database", $username, $password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}

try {
   $query = "SELECT DateTimeColumn FROM OneTable";
   $stmt = $conn->prepare($query);
   $stmt->setAttribute(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE, true);
   $stmt->execute();
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
   echo print_r($row, true);
}

$stmt = null;
$conn = null;
?>

1.2. Retrieve Date and Time values from SQL Server uisng the SQLSRV version of PHP Driver for SQL Server:

By default smalldatetime, datetime, date, time, datetime2, and datetimeoffset types will be returned as PHP DateTime objects, but this behaviour can be changed by setting the 'ReturnDatesAsStrings' option in the connection string or at the statement level:

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

$cinfo = array(
   "Database" => $database, 
   "UID" => $username, 
   "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
   echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
   exit;
}

$query = "SELECT DateTimeColumn FROM OneTable";
$options = array('ReturnDatesAsStrings' => true);
$stmt = sqlsrv_query($conn, $query, null, $options);
if ($stmt === false) {
   echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
   exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
   echo print_r($row, true);
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

1.3. Retrieve Date and Time values from SQL Server uisng MSSQL PHP extension (note, that this extension was removed in PHP 7.0.0):

When you use MSSQL extension, date and time values are returned as text, but the format depends on mssql.datetimeconvert setting in php.ini file. When this options is ON, date and time values are converted based on SQL server settings, and when is OFF date and time values are converted to YYYY-MM-DD hh:mm:ss format.

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

$conn = mssql_connect($server);
if ($conn === false) {
    echo "Error (mssql_connect): ".mssql_get_last_message();
    exit;
}
mssql_select_db($database, $conn);

$query = "SELECT DateTimeColumn FROM OneTable";
$stmt = mssql_query($sql, $conn);
if ($stmt === false) {
    echo "Error (mssql_query): ".mssql_get_last_message();
    exit;
}

while ($row = mssql_fetch_assoc($stmt)) {
   echo print_r($row, true);
}

mssql_free_result($stmt);
mssql_close($conn);
?>

As an additional note, it seems that this setting is ON in your server environment, because you can send dates like '26-03-2019 00:00:00.000' without errors.

2. How to pass Date and Time values to SQL Server:

As a general rule, date and time values can be passed to SQL Server using unambiguous datetime format (yyyymmdd or yyyy-mm-ddThh:mm:ss) and parameterized statement.

2.1. Pass Date and Time values to SQL Server uisng the PDO_SQLSRV version of PHP Driver for SQL Server:

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

try {
   $conn = new PDO("sqlsrv:server = $server; Database = $database", $username, $password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}

try {
   $query = "INSERT INTO OneTable (DateTimeColumn) VALUES (?)";
   $datetime = (new DateTime())->format("Y-m-dTH:i:s");
   $stmt = $conn->prepare($query);
   $stmt->bindParam(1, $datetime);
   $stmt->execute();
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}

$stmt = null;
$conn = null;
?>

2.2. Pass Date and Time values to SQL Server uisng the SQLSRV version of PHP Driver for SQL Server:

With this version of the driver, you may use the extended parameters syntax and pass the datetime value as PHP DateTime object with information about the PHP and SQL Server data types of the parameter.

<?php
$server   = "serverinstanse";
$database = "database";
$username = "username";
$password = "password";

$cinfo = array(
   "Database" => $database, 
   "UID" => $username, 
   "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
   echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
   exit;
}

$query = "INSERT INTO OneTable (DateTimeColumn) VALUES (?)";
$datetime = new DateTime();
$params = array(
   array($datetime, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_DATETIME, SQLSRV_SQLTYPE_DATETIME)
); 
// or as usual, pass datetime values as text
//$params = array($datetime->format("Y-m-dTH:i:s")); 
$stmt = sqlsrv_query($conn, $query, $params);
if ($stmt === false) {
   echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
   exit;
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>
Saturday, May 29, 2021
 
ManojGeek
answered 5 Months ago
82

As far as I am aware there is no 64bit PHP compatible SQL Server library. You will have to install WAMPServer 32bit. This is not a WAMPServer limitation, its just that Microsoft has not yet completed the 64 bit PHP extension.

Probably they were waiting for PHP7 which is the first truly 64bit version of PHP

Thursday, September 2, 2021
 
shx2
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :