Asked  7 Months ago    Answers:  5   Viewed   53 times

I upgraded to PHP 5.3, and noticed that php_mssql.dll is missing. After googling a bit, it seems that 5.3 has dropped support for mssql. So I downloaded the drivers from microsoft, but I can't seem to get it to work.

I am on windows. What do I need to do, after I unzip the files in the ext directory of my PHP 5.3?

 Answers

27

Quoting http://php.net/manual/en/intro.mssql.php:

The MSSQL extension is not available anymore on Windows with PHP 5.3 or later. SQLSRV, an alternative driver for MS SQL is available from Microsoft: ยป http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx.

Once you downloaded that, follow the instructions at this page:

  • Loading the Microsoft Drivers for PHP for SQL Server

In a nutshell:

Put the driver file in your PHP extension directory.
Modify the php.ini file to include the driver. For example:

extension=php_sqlsrv_53_nts_vc9.dll  

Restart the Web server.

See Also (copied from that page)

  • System Requirements (Microsoft Drivers for PHP for SQL Server)
  • Getting Started
  • Programming Guide
  • SQLSRV Driver API Reference (Microsoft Drivers for PHP for SQL Server)

The PHP Manual for the SQLSRV extension is located at http://php.net/manual/en/sqlsrv.installation.php and offers the following for Installation:

The SQLSRV extension is enabled by adding appropriate DLL file to your PHP extension directory and the corresponding entry to the php.ini file. The SQLSRV download comes with several driver files. Which driver file you use will depend on 3 factors: the PHP version you are using, whether you are using thread-safe or non-thread-safe PHP, and whether your PHP installation was compiled with the VC6 or VC9 compiler. For example, if you are running PHP 5.3, you are using non-thread-safe PHP, and your PHP installation was compiled with the VC9 compiler, you should use the php_sqlsrv_53_nts_vc9.dll file. (You should use a non-thread-safe version compiled with the VC9 compiler if you are using IIS as your web server). If you are running PHP 5.2, you are using thread-safe PHP, and your PHP installation was compiled with the VC6 compiler, you should use the php_sqlsrv_52_ts_vc6.dll file.

The drivers can also be used with PDO.

Wednesday, March 31, 2021
 
Easen
answered 7 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
57

Note

The answers that suggest using variations of $window.history.back() have all missed a crucial part of the question: How to restore the application's state to the correct state-location as the history jumps (back/forward/refresh). With that in mind; please, read on.


Yes, it is possible to have the browser back/forward (history) and refresh whilst running a pure ui-router state-machine but it takes a bit of doing.

You need several components:

  • Unique URLs. The browser only enables the back/forward buttons when you change urls, so you must generate a unique url per visited state. These urls need not contain any state information though.

  • A Session Service. Each generated url is correlated to a particular state so you need a way to store your url-state pairs so that you can retrieve the state information after your angular app has been restarted by back / forward or refresh clicks.

  • A State History. A simple dictionary of ui-router states keyed by unique url. If you can rely on HTML5 then you can use the HTML5 History API, but if, like me, you can't then you can implement it yourself in a few lines of code (see below).

  • A Location Service. Finally, you need to be able manage both ui-router state changes, triggered internally by your code, and normal browser url changes typically triggered by the user clicking browser buttons or typing stuff into the browser bar. This can all get a bit tricky because it is easy to get confused about what triggered what.

Here is my implementation of each of these requirements. I have bundled everything up into three services:

The Session Service

class SessionService

    setStorage:(key, value) ->
        json =  if value is undefined then null else JSON.stringify value
        sessionStorage.setItem key, json

    getStorage:(key)->
        JSON.parse sessionStorage.getItem key

    clear: ->
        @setStorage(key, null) for key of sessionStorage

    stateHistory:(value=null) ->
        @accessor 'stateHistory', value

    # other properties goes here

    accessor:(name, value)->
        return @getStorage name unless value?
        @setStorage name, value

angular
.module 'app.Services'
.service 'sessionService', SessionService

This is a wrapper for the javascript sessionStorage object. I have cut it down for clarity here. For a full explanation of this please see: How do I handle page refreshing with an AngularJS Single Page Application

The State History Service

class StateHistoryService
    @$inject:['sessionService']
    constructor:(@sessionService) ->

    set:(key, state)->
        history = @sessionService.stateHistory() ? {}
        history[key] = state
        @sessionService.stateHistory history

    get:(key)->
        @sessionService.stateHistory()?[key]

angular
.module 'app.Services'
.service 'stateHistoryService', StateHistoryService

The StateHistoryService looks after the storage and retrieval of historical states keyed by generated, unique urls. It is really just a convenience wrapper for a dictionary style object.

The State Location Service

class StateLocationService
    preventCall:[]
    @$inject:['$location','$state', 'stateHistoryService']
    constructor:(@location, @state, @stateHistoryService) ->

    locationChange: ->
        return if @preventCall.pop('locationChange')?
        entry = @stateHistoryService.get @location.url()
        return unless entry?
        @preventCall.push 'stateChange'
        @state.go entry.name, entry.params, {location:false}

    stateChange: ->
        return if @preventCall.pop('stateChange')?
        entry = {name: @state.current.name, params: @state.params}
        #generate your site specific, unique url here
        url = "/#{@state.params.subscriptionUrl}/#{Math.guid().substr(0,8)}"
        @stateHistoryService.set url, entry
        @preventCall.push 'locationChange'
        @location.url url

angular
.module 'app.Services'
.service 'stateLocationService', StateLocationService

The StateLocationService handles two events:

  • locationChange. This is called when the browsers location is changed, typically when the back/forward/refresh button is pressed or when the app first starts or when the user types in a url. If a state for the current location.url exists in the StateHistoryService then it is used to restore the state via ui-router's $state.go.

  • stateChange. This is called when you move state internally. The current state's name and params are stored in the StateHistoryService keyed by a generated url. This generated url can be anything you want, it may or may not identify the state in a human readable way. In my case I am using a state param plus a randomly generated sequence of digits derived from a guid (see foot for the guid generator snippet). The generated url is displayed in the browser bar and, crucially, added to the browser's internal history stack using @location.url url. Its adding the url to the browser's history stack that enables the forward / back buttons.

The big problem with this technique is that calling @location.url url in the stateChange method will trigger the $locationChangeSuccess event and so call the locationChange method. Equally calling the @state.go from locationChange will trigger the $stateChangeSuccess event and so the stateChange method. This gets very confusing and messes up the browser history no end.

The solution is very simple. You can see the preventCall array being used as a stack (pop and push). Each time one of the methods is called it prevents the other method being called one-time-only. This technique does not interfere with the correct triggering of the $ events and keeps everything straight.

Now all we need to do is call the HistoryService methods at the appropriate time in the state transition life-cycle. This is done in the AngularJS Apps .run method, like this:

Angular app.run

angular
.module 'app', ['ui.router']
.run ($rootScope, stateLocationService) ->

    $rootScope.$on '$stateChangeSuccess', (event, toState, toParams) ->
        stateLocationService.stateChange()

    $rootScope.$on '$locationChangeSuccess', ->
        stateLocationService.locationChange()

Generate a Guid

Math.guid = ->
    s4 = -> Math.floor((1 + Math.random()) * 0x10000).toString(16).substring(1)
    "#{s4()}#{s4()}-#{s4()}-#{s4()}-#{s4()}-#{s4()}#{s4()}#{s4()}"

With all this in place, the forward / back buttons and the refresh button all work as expected.

Wednesday, June 9, 2021
 
petersaints
answered 5 Months ago
63

You've misunderstood the options.

  • tokens=1 means you only want the first token on each line. You want all of the tokens on the line.
  • eol=, means you want to interpret a comma as the beginning of an end of line comment. You want to use delims=, instead to indicate the comma is the delimiter (instead of the default value of whitespace).

FOR /F is primarily for operating on lines in a file. You're not doing that. You're operating on a single string, so Rubens' answer is closer to what you want:

@ECHO OFF
SET test=1,2,3,4
FOR /D %%F IN (%test%) DO (
  ECHO .
  ECHO %%F
)

However, in theory, you should be able to say something like:

FOR /F "usebackq delims=, tokens=1-4" %%f IN ('1^,2^,3^,4') DO (
  ECHO .
  ECHO %%f    
  ECHO .
  ECHO %%g
  ECHO .
  ECHO %%h
  ECHO .
  ECHO %%i
)

This works as well, but probably doesn't scale in the way you want. Note that you have to escape the comma in the string using the ^ character, and you have to specify the tokens you want and then use the subsequent variables %g, %h and %i to get them.

Saturday, July 31, 2021
 
iftheshoefritz
answered 3 Months ago
63

I have found what was the problem.

It seems that I was using microsoft 32-bit sqlsrv driver, which don't work with wampserver 3 64-bit.

Official Microsoft SQL Server Driver for PHP: Here 32 bit - use this if you have a 32bit wampserver.

Unofficial Microsoft SQL Server Driver for PHP: Here 64 bit - use this if you have a 64bit server.

After using the unofficial version, it showed up on phpinfo()!

Anyway, I hope this answer is useful to people who have spend looking around for an answer in hours... or whatever, I hope it was useful.

Monday, August 2, 2021
 
jay.sf
answered 3 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 :