Archive

Archive for the ‘SQL Server’ Category

Microsoft (SQL Server 2.0) Drivers for PHP!! using SQLSRV

December 9, 2011 2 comments

STEPS:
1. First we need to install SQL Server Native Client before installing SQL driver for PHP  in your System.
My System Configuration is,
Operating System: Windows 7
System Type: 32-bit Operating System

Install from the http://technet.microsoft.com/en-us/library/cc296170.aspx

— We will get a page titles System Requirements
— We can see a link “Download the X86 package” (If 64-bit OS download X64 package)
— Click the link will download the required file.
— Install it into our system.

2. Download SQLSRV30.EXE OR SQLSRV20.EXE  from http://www.microsoft.com/download/en/details.aspx?id=17308
as per our system requirements.
Check php.ini – If

Compiler MSVC9 (Visual C++ 2008)

Then download php_sqlsrv_53_ts_vc9.dll and include in php.ini as STEP 5.

3.Run the SQLSRV30.EXE OR SQLSRV20.EXE file will ask path for placing the files. Select the PHP extension Directory.
Ex: D:\wamp\bin\php\php5.3.8\ext

4. Also check in php.ini
extension_dir = “D:/wamp/bin/php/php5.3.8/ext/”

5. Include in php.ini as,
extension=php_sqlsrv_53_ts.dll

OR

extension=php_sqlsrv_53_ts_vc6.dll
OR

extension=php_sqlsrv_53_ts_vc9.dll

See the configuration of your system and enable it in php.ini

6. Restart the wampp Server. If not reflected in phpinfo(), restart the sytem.

PHP Code to Connect
=================

<?php//phpinfo();/*Connect to the local server using Windows Authentication and specifythe AdventureWorks database as the database in use. To connect usingSQL Server Authentication, set values for the “UID” and “PWD”attributes in the $connectionInfo parameter. For example:$connectionInfo = array(“UID” => $uid, “PWD” => $pwd, “Database”=>”AdventureWorks”);*/$serverName = “10.18.1.254\SQL2000”;

$connectionInfo = array( “Database”=>”MCMS”, “UID”=>”sa”, “PWD”=>”Defiance123”);

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn )

{

//echo “Connection established.\n”;

/* Get the product picture for a given product ID. */

$tsql = “SELECT  [BlobData] FROM [MCMS].[dbo].[BlobTable] WHERE BlobId = ?”;

$params = array(24);

/* Execute the query. */

$stmt = sqlsrv_query($conn, $tsql, $params);

if( $stmt === false )

{

echo “Error in statement execution.</br>”;

die( print_r( sqlsrv_errors(), true));

}

/* Retrieve the image as a binary stream. */

$getAsType = SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY);

if ( sqlsrv_fetch( $stmt ) )

{

$image = sqlsrv_get_field( $stmt, 0, $getAsType);

fpassthru($image);

}

else

{

echo “Error in retrieving data.</br>”;

die(print_r( sqlsrv_errors(), true));

}

/* Free the statement and connectin resources. */

sqlsrv_free_stmt( $stmt );

sqlsrv_close( $conn );

}

else

{

echo “Connection could not be established.\n”;

die( print_r( sqlsrv_errors(), true));

}

?>

Categories: PHP, SQL Server

Microsoft SQL Server ODBC Driver Configuration

Steps:
1. Click Control Panel -> All Control Panel Items -> Administrative Tools -> Data Sources (ODBC)

Click Data Sources (ODBC)

Click Data Sources (ODBC)

2. Opens the ODBC Data Source Administrator Pop-up.

3. Select the User DSN -> Click the ADD button to add new DSN

Click the ADD Button

Click the ADD Button

4. Opens a Create New Data Source Pop-up. Select SQL Server and click Finish.

5. Now opens another Pop-up for Database Connection
6. Enter Name, Description for the connection.
7. In SERVER: field provide the server name/ IP and Instance Name

Ex:   10.10.10.20/SQL2005

SQLExpress/SQL2005

Click Next

Click Next

8. Click Next will ask for DB Mapping.

Check the Change the default database to.

In the Change the default database to box, use the pull down list to select the SQL database name that you created using SQL SERVER 2005 MANAGEMENT STUDIO.

Select the required DB.

Select the reruired DB

Select the reruired DB

9. Check Use ANSI quoted identifiers.

Check Use ANSI nulls, paddings and warnings.

   Click Next.

10.  Select Use regional settings…… and click Finish.

11. Click the Test Data Source and Test it.

12. We will get the following successful message dialog box.

13.   Select OK 3 times (one for each dialog box) to exit ODBC configuration.

If it did not Test Successfully.

Try these steps:

–  If the SQL Server is a remote node, verify that TCP port 1433 is open on the SQL Server Node (i.e. check any firewalls or Windows Firewall).

–  Verify the SA Login Password or whatever Login ID you used to configure this DSN.

– Verify the Database name.

– Verify that SQL Server is running.

13. Now we will get the connection Name in the User DSN.

We can see the created DSN name here

We can see the created DSN name here

10. Thats It. Connection has been successfully created for SQL Server through ODBC

We will see how we can access through PHP,

<?php/*

PHP MSSQL Example

Replace data_source_name with the name of your data source.

Replace database_username and database_password

with the SQL Server database username and password.

*/

$data_source=”Driver={SQL Server};Server=10.10.10.20\SQL2005;Database=Name of the DB;”;

$user=’sa’; // Username of the SQL server

$password=’Xxxxxx456′; // Password of the SQL server

// Connect to the data source and get a handle for that connection.

$conn=odbc_connect($data_source,$user,$password);

if($conn)

echo ‘success’;

else

echo ‘failed’;

?>

Categories: PHP, SQL Server