Category Archives: SQL Server

PHP without WAMP or XAMPP

I am big fan of WAMP. The reason – I tell every newbie that you can learn PHP in very short span. This is all possible because of a ONE click installation of WAMP and providing you the all the necessary packages to run your PHP and the back end code.

Well – here was the latest requirement from our client – Develop a web application using PHP and SQL Server. Naturally I need to play away with WAMP, as I don’t want to make the client box heavier using two database servers – the existing SQL server and the new MySQL because of WAMP.

Okay – so it’s a high time to install & configure the PHP & the Web Server (Apache or IIS). It was bit of headache, but no other option.

Let’s go. Here is the complete guideline on working with PHP 5.6.x with Apache server without WAMP or without XAMPP.

To be noted these versions are well compatible with each other and hence don’t go with any other major versions unless you are confident in conjuring it by yourself. I will assume that your system has SQL server already installed to it.

 

Here are the major steps:

A.      Download the packages:

  1. Apache 2.4.x httpd web server
  2. PHP 5.6.x – The PHP engine
  3. Eclipse Helios Editor (Optional)

B.      Running Apache(localhost) for the test page

C.      Configure PHP

D.      Configure Apache for PHP

E.       Running the first PHP code

A) Download the packages:

1. Apache httpd-2.4.25 – http://www.apachelounge.com/download/VC14/binaries/httpd-2.4.25-win32-VC14.zip

2. PHP-5.6.30 – http://windows.php.net/downloads/releases/php-5.6.30-Win32-VC11-x86.zip

3. Eclipse Helioss R2 – http://www.eclipse.org/downloads/packages/eclipse-php-developers/heliossr2 This is a PHP compatible Eclipse IDE; I would insist you to go ahead with your known editor. This is not at all mandatory.

B) Running Apache(localhost) for the test page

You need to extract the downloaded package above #1. Copy to your drive (say C: or D). Rename the big named folder name to a shorted one – say its Apache24.

Go to C:\Apache24\bin

Do a double click on httpd.exe or from command prompt – C:\Apache24\bin>start httpd.exe

This should pop up a small prompt.

Open a browser and launch the URL  – http://localhost or http://localhost:80 – This should bring you a tiny output stating that – “It works!”. Means it is configured correctly.

Possible Issue #1: By somehow if your port 80 has been taken by other application like IIS or Skype, please change this into the file C:\Apache24\conf\httpd.conf from Listen 80 to Listen <YOUR_PORT>

Possible Issue #2: You may get a prompt like VCRUNTIME140.dll is missing while trying to run the httpd service. Just download this file from internet. This may come as a zip file. Extract it and copy the VCRUNTIME140.dll file to C:\Apache24\bin folder.

C) Configure PHP

Extract the #2 download and rename the big named folder to php5 or php56 or just php.

Copy the c:\php\php.ini-development to the same folder with  a rename as php.ini. After copy you should see it as c:\php\php.ini

Make sure that you should be able to locate the dll file – php5apache2_4.dll to C:/php folder. This dll is the one who help the Apache to communicate with PHP interpreter.

D) Configure Apache for PHP

i) Add these file to your httpd.conf file. You can add them anywhere but I would prefer to add where all these #LoadModule types of lines end with.

LoadModule php5_module "C:/php/php5apache2_4.dll"
AddHandler application/x-httpd-php .php
# configure the path to php.ini
PHPIniDir "C:/php"

Caution : php5apache2_4.dll is the same into your PHP directory.

ii) Modify the value of the DocumentRoot to your folder htdocs or similar folder. Whatever you specify here, that should contain your project folders/subfolders and PHP files.

DocumentRoot "c:/Apache24/htdocs"
<Directory "c:/Apache24/htdocs">

iii)Optionally add/modify the DirectoryIndex. Just add the main PHP file or welcome page of your project. Ideally if somebody just types http://localhost – without anything else – the mentioned page will act as a default page view at the browser.

<IfModule dir_module>
    DirectoryIndex index.php index.html
</IfModule>

E) Running the first PHP code

After adding those above lines to http.conf file, you must create index.php file. Let us add these PHP codes to your  c:/Apache24/htdocs/index.php file

<?php
   echo “PHP is one of the sweet language!”;
   echo phpinfo();
?>

Now launch the browser like http://localhost/index.php – The output should make you happy.

I will be writing another article very soon on major parameters those need to be changed in http.conf file.

Undefined function sqlsrv_connect()

MySQL is more like a girl friend and cozy with WAMP.
MS SQL Server becomes the arrogent gal for WAMP. She does all the drama before falling in love with WAMP.

I know WAMP should not be preferred if you want to be away with MySQL. But many of us prefer in installing WAMP cause it comes with

  1. The desired PHP version
  2. Apache webserver is automatically taken care as compatible with your PHP 5.x.x version (You just cant put any Apache httpd with your PHP X)
  3. You get many required DLL files with installation to /ext folder like – C:\wamp\bin\php\php5.5.12\ext. Need not to download them from here and there.
  4. Ofcourse the GUI is so quick and easy to
    a) run/stop the services,    b) look for the PHP.ini for any changes,   c) look for the httpd.conf for any changes

See what happens when you try to work with SQL server into WAMP’s PHP
1. You need to download the DLL for SQL server supported by PHP
2. Make a change to php.ini file for this dll

Here is the step by step guideline from installation to troubleshoot:
A. Download the DLL file
1) Complete this table from your installed environment (done so far)
a) PHP Version – Something like 5.5.12 or 5.3.4 or ?
b) PHP dll –  Like php5ts.dll or php5.dll or ?  [ Look to the directory – C:\wamp\bin\php\php5.5.12]
c) Microsoft Driver Version -> ? ( We will do it in next few minutes)
d) Required SQL Server DLL file -> ? ( We will do it in next few minutes)

To get the 1c & 1d, launch the website – https://docs.microsoft.com/en-us/sql/connect/php/system-requirements-for-the-php-sql-driver

Your 1a & 1b are the key to find the c & d from this page and that is too critical to get the correct one. DONT BE IN HURRY!

2) Time to download the 1d) now from https://www.microsoft.com/en-us/download/details.aspx?id=20098. In the next screen after pressing “DOWNLOAD”,
select the right exe SQLSRV30.EXE/SQLSRV31.EXE/SQLSRV32.EXE/SQLSRV40.EXE
Those 30, 31, 32 or 40 are nothing but the 1c- Microsoft Driver Version

B. Changes to PHP.ini file
Look for the php.ini file into Apache folder, not the one at PHP folder – like C:\wamp\bin\apache\apache2.4.9\bin
Add these two lines at related place into the ini file as per your downloaded dll
    extension=php_sqlsrv_55_ts.dll
    extension=php_pdo_sqlsrv_55_ts.dll 
C. Restart the server
Are you still getting an error while running your PHP code – “Unable to find sqlsrv_connect()”

D. You are happy to GO – May be or No No No!
If so, issue could be with the dll and that is something more your OS as 32 bit or 64 bit.
Let us try to overcome this:
1. Download this Microsoft ODBC Driver 11 for SQL Sever
https://www.microsoft.com/en-us/download/details.aspx?id=36434
ENU\x64\msodbcsql.msi  -> 64 bit


Restart the WAMP and see if this brings a good luck to you. IF NOT
2. Here is the ROB’s page http://robsphp.blogspot.in/2012/06/unofficial-microsoft-sql-server-driver.html
which may bring a smile to your face.
Download the Unofficial Microsoft SQL Server Driver for PHP – https://onedrive.live.com/?id=669EE24817961774%21123&cid=669EE24817961774
and hope this should work for you.

Since this is unofficial, hence I am instructing to try into steps till A to D1. If fails then only go for D2.

mysqldump equivalent in SQL Server

This is frustrating for a mysql user to take a database dump when they try to do the same in sql server. Here is the one of the optimal way for a mysql user working for sql server.

Right Click on database -> Tasks -> Generate Scripts..
Next -> Select the database name from a list
Under advanced scripting option-> Types of data to Script
You have 3 options to be selected here

  1. Schema only
  2. Data only
  3. Schema and data

sqldump
Later on give the path and file name for generating the script.

Now question is how to run this script?

Two ways:
1. run the below command at DOS command prompt

OSQL -U <userName> -P <password> -S <serverName> -i  <completepathSQLFile>

OR
2. Open the generated script file in a query window and just run them in one go. Just like you run a simple query. But you are advised to do provided its a small sized file.

Live Situation #3 : Clearing the transaction log in SQL Server 2005

5 months ago – in one of my project, Mirth integration server was linked to SQL Server to store the processing data. One of the channel in Mirth has a flat file reader, parses it and at  every major point it inserts the data to a table.
With a live demand, Mirth was asked to process a file of more than 2lacs of lines. This resulted a large log file in SQL server.
Situation came to somehow empty the content of this log file(.ldf) without affecting the actual database file(.mdf).
One of the frequent answer you get after Googling by several bloggers – Detach the database, rename the log file and again attach the database. But please don’t do this. This can damage your database so badly that you cant retrieve it even. Yes I have struggled with one of the database badly.

Here is the way that sounds pretty smooth toward the solution:
This is very quick and reduced the log file size from 1 GB to 1MB in just 10 seconds. Amazing!!!

1. Select a database.
Right Click -> Properties -> Options -> Database Recovery Model -> Simple [change it to Simple from anything else]

2. Right Click on database -> Tasks -> Shrink -> Files
From dropdown select file type as Log
Release unused space
or Shrink to file size – Give your own choice e.g 1 MB

3. Right Click -> Properties -> Options -> Database Recovery Model -> Full [change it to Full or whatever you had initially from Simple]