Using PHP With SQL Server

In our last technical post, I covered using Visual Studio Code to debug PHP code. Today, I’m going to cover integrating with SQL Server.

Step One: Get Drivers

Assuming that you followed the instructions for setting up PHP in the link above, you have a version of PHP running in C:\xampp\php. From there, you will want to download the Microsoft Drivers for PHP for SQL Server from Microsoft. The latest version of the drivers as of this post is 5.3, which covers PHP versions 7.0 through 7.2. This means that if you are using PHP 7.3, your version of PHP does not have official Microsoft support, so stick with 7.2 if this is a greenfield project.

Download the right version of the drivers given your operating system. For this post, I’ll assume that you’re running this on a Windows development box.

After you download the drivers, run the downloaded executable and extract to some temp directory. Then, copy the php_sqlsrv_72_ts_x86.dll file to C:\xampp\php\ext\ (assuming you installed Xampp to that directory and are running PHP 7.2) and modify your php.ini file to add the following at the bottom:

extension=php_sqlsrv_72_ts_x86.dll

Once you do that, restart Apache and you should be able to access SQL Server.

Step Two: Write Code

Querying SQL Server in PHP is pretty easy. Here is some overly simplistic code to execute a stored procedure. I’m going to give you bad PHP code (seriously, who takes PHP development advice from a database guy hasn’t really written PHP in a decade?) but at least I’m not going to make it SQL injectable…

private function executeQuery($query, $returnResult){
	$dbconnection = null;
		try {
			$serverName = "MYSERVER";
			$databaseName = "MYDATABASE";
			$username = "MYUSERNAME";
			$password = "MYPASSWORD";
			$connectionOptions = array("Database" => $databaseName, "UID" => $username, "PWD" => $password);
			$dbconnection = sqlsrv_connect($serverName, $connectionOptions);

			if($dbconnection){
				$preparedStatement = sqlsrv_prepare($dbconnection, $query['query'], $query['parameters']);
				$isSuccessful = sqlsrv_execute($preparedStatement);
				
				if($returnResult &&  isset($preparedStatement) && $isSuccessful){
					$finalResults = array();
					while($row = sqlsrv_fetch_array($preparedStatement, SQLSRV_FETCH_ASSOC))  
					{  
						$finalResults[] = $row;
					}  
					return $finalResults;
				}

				return null;
			} else {
				throw new Exception('Unable to connect to database');
			}
		} catch(Exception $error){
			throw new Exception($error);
		}
		$dbconnection = null;
}

In the code above, we take in a query, which has the query text and a set of parameters. We connect to SQL Server given various parameters, which you’d want to store in a secure location so please don’t copy my code verbatim; do it the right way.

If we want a result set back, set $returnResult to true. Otherwise, we don’t expect a result set back.

Given that structure, here’s how you’d call it.

private function createInsertActionLogDBQuery($logLevelID, $stackTrace, $errorMessage){
	return array("query" => "EXECUTE dbo.ActionLog_InsertActionLog
								@LogLevelID = ?,
								@StackTrace = ?,
								@ErrorMessage = ?;",
				"parameters" => array(
					array($logLevelID, SQLSRV_PARAM_IN),
					array($stackTrace, SQLSRV_PARAM_IN),
					array($errorMessage, SQLSRV_PARAM_IN)
				));
}

public function sendActionLog($logLevelID, $stackTrace, $errorMessage){
	$this->executeQuery($this->createInsertActionLogDBQuery($logLevelID, $stackTrace, $errorMessage), false);
}

We have one function call to build up our query object and another function which acts as the intermediary. That way, if we need to translate data from our app to SQL Server, we can do it in the public function without exposing that we’re sending data to SQL Server or the mechanisms behind the insert operation.

Step Three: Disclaim Knowledge

It was nice to take a jaunt back into a language I hadn’t seen in a very long time. I am by no means a fan of PHP and do not want to develop in it, but it was worthwhile spending some time to understand how things work in a non-.NET space. As I mentioned, I am absolutely not an expert in PHP, so you don’t want to take my advice on it seriously, but this process did end up working out, so I’m pretty happy about it.

Advertisements

PolyBase Revealed + Azure Data Studio

As I work on PolyBase Revealed, I made a conscious decision to prefer Azure Data Studio for my demos over SQL Server Management Studio. Because I don’t plan to go into much detail on why in the book (after all, the book is about PolyBase, not SQL Server clients), I wanted to take a little time and walk through some of my reasoning for this.

A screenshot I guarantee you’ll never see in the book. Those timings, however, might well show up.

It’s Cross-Platform

One of the main use cases for PolyBase is to integrate with all kinds of data sources, including Hadoop, Oracle, Teradata, and the like. And on many of those platforms, Windows is a second-class citizen. The ability for me to give a native Linux or Mac user a useful platform helps with bringing them into the fold.

SSMS Has Limited PolyBase Support

SQL Server Management Studio does have some PolyBase-related functionality: you can see external tables in their own folder; you can right-click and get templates for new external data sources, file formats, and tables; and you can see the members of your PolyBase Scale-Out Group. If you are primarily focused on PolyBase, there’s nothing in that list which pushes me to say that you absolutely need SSMS.

Tables Are Tables

The Azure Data Studio team made a decision which I think is the right one: external tables are just tables. In SSMS, all external tables show up in their own folder, hidden away from everything else. But the idea is that PolyBase allows for data virtualization, meaning I don’t care where the data lives as long as it gets to me.

Tables are tables.

Azure Data Studio is New

This is a little bit of a stretch, but not as much as you might think. Azure Data Studio is a new product, so it’s easier to influence the product team. If the tens of millions of eventual readers of the book use Azure Data Studio for some of their work, that makes it easier to vote up PolyBase-related issues and feature requests.

Using Visual Studio Code For PHP Development

For a consulting engagement, I needed to be able to run a debugger against a PHP codebase as part of a migration project. Because my last experience with PHP was sometime last decade, I had no clue how to do this. Now I have a slight clue, so I figured I’d share that slight clue. Note that this is for installing PHP on Windows; the Linux experience is a bit more refined.

Step One: Install xampp

Grab the latest version of xampp at https://www.apachefriends.org/download.html.  As of the time of writing, the latest PHP version is 7.3, but grab the version for PHP 7.2 because the debugger DLL we will use does not currently support PHP 7.3.

Run the xampp installer and install in C:\xampp or a similar directory.  The only services we need are Apache httpd and PHP; you do not need to install MySQL, Tomcat, or any of the other services or administration tools.

Download xdebug

Xdebug is a debugger DLL for PHP, which you can download from http://xdebug.org.  We want to get the latest 32-bit, thread-safe, stable version which supports PHP 7.2 and is built using the Visual C++ 2017 redistributable.  As of 2018-12-28, that is http://xdebug.org/files/php_xdebug-2.6.1-7.2-vc15.dll.

The DLL we want.

After you have downloaded the appropriate DLL, copy it into C:\xampp\php\ext\ (assuming you installed to C:\xampp).  Then, open C:\xampp\php\php.ini and add the following lines to the bottom of the file:

[XDebug]
zend_extension = "C:\xampp\php\ext\php_xdebug-2.7.0beta1-7.3-vc15-nts-x86_64.dll"
xdebug.remote_enable = 1
xdebug.remote_autostart = 1

Configure Visual Studio Code

Visual Studio Code has built-in PHP support, but there are a couple of plugins which I like, specifically, PHP Intelephense and PHP Debug.

The two extensions I used for PHP debugging.

You can install these extensions by opening up Visual Studio, clicking the Extensions option (highlighted in the image above), and typing the name of the extension into the search box.  Click on the “Install” button next to each extension and when all extensions are installed, close and re-open Visual Studio Code.

Then, press the F1 key and type “User” into the console. This will give you a few options:

Computer: read my mind.

From that list, you want to select “Preferences: Open User Settings.” That will pop up the following window:

A whole bundle of things you can change to break your Visual Studio Code installation.

There are a number of settings you might want to configure here, but for now, type “PHP” into the search settings box above.  We need to validate the PHP executable path, so click the “Edit in settings.json” link:

PHP-specific user settings.

Clicking on the settings.json link will bring you to a side-by-side editor.  The left-hand side is immutable, but you can override its values on the right-hand side.  Set php.validate.executablePath to your installed PHP executable, making sure to double up any backslashes. In my case, it is:

"php.validate.executablePath": "C:\\xampp\\php\\php.exe"
Setting an external path.

Configure httpd

Navigate to C:\xampp\apache\conf (assuming you installed Xampp in C:\xampp) and open the httpd.conf file in a text editor.  In the “DocumentRoot” section, you will want to replace whatever is there with the root directory for your PHP code.

After you do that, open up the Xampp control panel and start the Apache service:

The Xampp control panel running Apache + PHP.

Configure VS Code For Debugging

In Visual Studio code, go to the Debug tab and ensure that you have two Debug configuration options: “Listen for XDebug” and “Launch currently open script.”

Two debugging configurations should be available.

If you do not have these two, click on the “Add Config (workspace)” and add the following JSON:

{
    // Use IntelliSense to learn about possible attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
        {
            "name": "Listen for XDebug",
            "type": "php",
            "request": "launch",
            "port": 9000
        },
        {
            "name": "Launch currently open script",
            "type": "php",
            "request": "launch",
            "program": "${file}",
            "cwd": "${fileDirname}",
            "port": 9000
        }
    ]
}

Then, select Listen for XDebug and click the Play button.  This will create a hook between Visual Studio Code and your debugger over port 9000.  You might need to create a firewall rule to allow inbound traffic on port 9000.

From here, you can put add and remove breakpoints and by navigating to http://127.0.0.1/{mypage.php}, you can trigger the debugger because we have the XDebug DLL installed.

The Visual Studio Code documentation for debugging includes detail on how to navigate the debugger.

SQL Saturday Raleigh

SQL Saturday Raleigh 2019 is now live. It will take place on Saturday, April 27th at Wake Technical Community College’s RTP campus.

For the past three events, we hosted at William Peace University, which I absolutely enjoyed having as a venue. But with Wake Tech opening a campus just a couple of miles from the center of the Triangle, we wanted to make the trip more convenient for people living out in Chapel Hill and Durham.

The venue is gorgeous, a brand new building which looks fantastic. You should definitely submit a talk or four and come to the best SQL Saturday in the United States…that day…unless someone else launches one after us…

Course Reviews: Apache Spark 2 With Scala

This is a review for Frank Kane’s Udemy course entitled Apache Spark 2 with Scala — Hands On with Big Data!

All in all, this was a fine course. Frank aims the course at people new to Spark. In my case, I have experience but wanted to start at the beginning to fill in the gaps in my self-education. To that extent, the course covers the basics of Spark, starting with installation and a primer on Scala. From there, we get to see Spark in action, starting with the “Spark 1.0” concept of Resilient Distributed Datasets and the functional approach to problem-solving, and then eventually covering “Spark 2.0” with DataFrames and Spark SQL. Frank also covers interesting features like broadcast variables and accumulators, things I didn’t have any experience when I started the course.

Frank uses a couple of interesting examples in his lessons: a network graph of Marvel character interactions in comics and the MovieLens data set. These data sets are large enough that processing is not trivial but not so large that you’d need a 20-node cluster to complete the task today. The examples were also fun, which made it easier to follow along.

The one area where I think this course wasn’t that great was in the “what’s next” sections, starting with MLLib and including Spark Streaming and GraphX. Frank has a Spark Streaming course which is on my to-watch list, so I can give that part a pass. With MLLib, it seemed that “This barely works” was the subtext, with weird results coming out of the tests. And GraphX just touched on the topic, though I get the feeling that GraphX will not be long for this world.

In short, if you want to learn the basics of Spark, this is a great introductory course. I’ve recommended it to several people looking to learn the product and I think Frank keeps the content at the right level for new people.

I’m Writing A Book

Due to having far too much free time (I mean, there are hours a day where I’m doing nothing but sleeping! More than one!), I’ve taken to the idea of writing a book on PolyBase. The working title is PolyBase Revealed and has a scheduled release date of “That’s way in the future so I don’t have to worry…wait, what month is it now? Oh crap!”

Long-time readers may recall my series on PolyBase (I still prefer “Polybase” and so does about a quarter of the Microsoft documentation, but I’ll capitalize the middle B for marketing purposes) focusing on SQL Server 2016. Well, SQL Server 2019 is coming and with it, we have a lot of new PolyBase-related functionality, including external connections to a slew of new resources. The book will focus on SQL Server 2019 with a chapter on Azure SQL Data Warehouse. My goal is to make it easy for someone getting started with data virtualization and with a T-SQL background to integrate with the different supported systems, pointing out bugaboos along the way. Because there are plenty of bugaboos.

As I write the book, I intend on adding to this blog the odds and ends that I find during research but which don’t make their way into the book. I’ll probably spam my blog with way too much info about the book because I expect to build a house made out of gold using my royalties. A house made out of gold is utterly impractical, but how else could I spend that much in royalty checks?