Computing: Website and Database Programming

Web/Database environment using IIS and SQL Server.

4. Accessing SQL Server from ASP.NET, PHP, Perl and Free Pascal.
  In part 2 of my IIS and SQL Server tutorial, I showed how to run scripts written in ASP.NET, PHP and Perl on IIS. In this part of the tutorial, we will see how to access SQL server from scripts written in these languages. It is supposed that you have IIS and SQL Server installed and properly running...
  ASP.NET.
  Running ASP.NET scripts on IIS should work "out of the box", I said in part 2 of the tutorial. This stays true for ASP.NET scripts that access SQL Server. Thus, nothing else to do as to write your code and save the file into some directory accessible by the web server.
  Here is the code of a simple ASP.NET script, that displays the number of customers in the BikeStores database.
      <% @Page Language="C#" %>
    <% @Import Namespace="System.Data.SqlClient" %>
    <script runat="server">
        private void getCount(object sender, EventArgs e) {
            const string cs = @"Server=localhost;Database=BikeStores;UID=nemo;Password=nemo";
            var con = new SqlConnection(cs);
            con.Open();
            string sql = "SELECT COUNT(*) FROM sales.customers";
            var cmd = new SqlCommand(sql, con);
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read()) {
                count.InnerHtml = Convert.ToString(rdr.GetInt32(0));
            }
            con.Close();
        }
    </script>
    <html>
        <head>
            <title>Test MS SQL Server connection</title>
        </head>
        <body>
            <p style="font-weight:bold">Test MS SQL Server connection.</p>
            <form runat="server">
                <p>Number of customers in the BikeStores database = <span runat="server" id="count"/></p>
                <input runat="server" id="button1" type="submit" value="Query" OnServerClick="getCount"/>
            </form>
        </body>
    </html>
  I named the script mssql.aspx and copied it to the directory \test of my ISS. To run the script, I typed the following in the address bar of my web browser:
    localhost/test/mssql.aspx
  The screenshot shows the result (after the button has been pushed).
 
IIS/SQL Server on Windows 8.1: Running an ASP.NET script
  PHP
  You can download the PHP extensions for SQL Server from the Microsoft website. The file, that I downloaded, is called SQLSRV510.ZIP, and contains the DLLs for several versions of PHP (7.4, 8.0, 8.1). You have to copy the required DLLs to the PHP extensions directory (C:\Progs\php\ext, on my system). I actually copied all DLLs included in the archive. With PHP 7.4 64-bit (actually used on my Windows 8.1), it's the DLLs php_sqlsrv_74_ts_x64.dll and php_pdo_sqlsrv_74_ts_x64.dll that are needed.
  To enable these extensions, you have to edit the PHP configuration file php.ini. Add the following two lines to the part describing the enabled extensions:
    extension=php_sqlsrv_74_ts_x64.dll
    extension=php_pdo_sqlsrv_74_ts_x64.dll
  Do not forget to restart IIS after the modifications have been done!
  Here is the code of a PHP script that displays a list of the brands in the BikeStores database.
      <html>
        <head>
            <title>Test SQL Server connection with PHP.</title>
        </head>
        <body>
            <h1>List of brands in BikeStores database.
            <?php
                $server = "127.0.0.1";
                $database = "BikeStores";
                $user = "nemo";
                $pwd = "nemo";
                $connectionInfo = array("UID"=>$user, "PWD"=>$pwd, "Database"=>$database);
                // Connect using SQL Server authentication
                $conn = sqlsrv_connect($server, $connectionInfo);
                if($conn) {
                    // Read brands from database
                    $sql = 'SELECT brand_id AS "id", brand_name AS "name" FROM production.brands';
                    $result = sqlsrv_query($conn, $sql);
                    echo '<table border="1"><th>Id</th><th>Name</th></tr>';
                    while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
                        echo '<tr><td>' . $row["id"] . '</td><td>' . $row["name"] . '</td></tr>';
                    }
                    echo '</table>';
                }
                else {
                    echo "Unable to connect to BikeStores database.<br/>";
                    die(print_r(sqlsrv_errors(), true));
                }
                sqlsrv_close($conn);
            ?>
        </body>
    </html>
  I named the script mssql.php and copied it to the directory \test of my ISS. To run the script, I typed the following in the address bar of my web browser:
    localhost/test/mssql.php
  The screenshot shows the script output.
 
IIS/SQL Server on Windows 8.1: Running a PHP script
  Perl
  The easiest way to access SQL Server from Perl is to use the DBI:ADO driver, that is included by default with the Strawberry Perl installation files.
  Here is the code of a Perl CGI script that displays a list of the brands in the BikeStores database.
      #!C:/Progs/Strawberry/perl/bin/perl.exe
    use strict; use warnings;
    use CGI qw(:standard);
    use CGI::Carp qw(fatalsToBrowser);
    use DBI;
    print header;
    print "<html>\n";
    print "<head><title>SQL Server test</title></head>\n";
    print "<body>\n";
    print "<h2>Test Perl connection to Microsoft SQL Server.</h2>\n";
    print "<p>List of all brands in the production.brands table of the BikeStores database.</p>";
    my $host = '192.168.40.102,1433';
    my $database = 'BikeStores';
    my $user = 'nemo';
    my $pw = 'nemo';
    # Connect to the BikeStores database
    my $dsn = "PROVIDER=SQLOLEDB; Trusted connection=yes;server=$host;Database=$database";
    my $dbh = DBI->connect ("DBI:ADO: $dsn", $user, $pw, {raiseerror => 1, autocommit => 1})
        or die "Database connection failed: $DBI::errstr";
    #Read brand ids and names from the production.brands table
    my $sql = 'SELECT brand_id, brand_name FROM production.brands';
    my $sth = $dbh->prepare ($sql);
    $sth->execute ();
    # Bind the query results to local variables
    my ($id, $name);
    $sth->bind_columns (undef, \$id, \$name);
    # Display brand ids and names
    print '<table border="1">';
    while ($sth->fetch ()) {
        print "<tr><<td>$id</td><td>$name</td></tr>";
    }
    print "</table>\n";
    print "</body>\n";
    print "</html>";
    #Close the database connection
    $sth->finish ();
    $dbh->disconnect ();
  I named the script mssql.cgi.pl and copied it to the directory \test of my ISS. To run the script, I typed the following in the address bar of my web browser:
    localhost/test/mssql.cgi.pl
  The screenshot shows the script output.
 
IIS/SQL Server on Windows 8.1: Running a Perl CGI script
  Lazarus/Free Pascal.
  Lazarus/Free Pascal provides a specific component for connecting to SQL Server: TMSSQLConnection (connecting using ODBC would of course also be possible). For details, please have a look at my tutorial Using Microsoft SQL Server databases with Lazarus/Free Pascal.

If you find this text helpful, please, support me and this website by signing my guestbook.