::. Moleculax - Programming free online - Data Base NoSQL - OOP - Deep Web- Dark Web - BigData - PostgreSQL - MySQL - Programming - OOP freebitcoin.:: Moleculax - TECHNOLOGY AND KNOWLEDGE Information technology, Structured programming

Step 1.1 Install SQL Server

Note: To ensure optimal performance of SQL Server, your machine should have at least 4 GB of memory. If you need to get Ubuntu, check out the Ubuntu Downloads website.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list |

 sudo tee /etc/apt/sources.list.d/mssql-server-2017.list

Install SQL Server

Terminal

sudo apt-get update
sudo apt-get install mssql-server
Copy

Results

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
  mssql-server
...
Unpacking mssql-server ...
Setting up mssql-server ...

Setup your SQL Server

Terminal

sudo /opt/mssql/bin/mssql-conf setup
Copy

Results

Microsoft(R) SQL Server(R) Setup

To abort setup at anytime, press Ctrl-C.

The license terms for this product can be downloaded from

http://go.microsoft.com/fwlink/?LinkId=746388 and
found in /usr/share/doc/mssql-server/LICENSE.TXT.

Do you accept the license terms? If so, please type YES:
Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

You now have SQL Server running locally on your Ubuntu machine! Check out the next section to continue installing prerequisites.

Step 1.2 Install PHP and other required packages

Terminal

sudo apt-get -y install php7.0 libapache2-mod-php7.0 mcrypt php7.0-mcrypt

php-mbstring php-pear php7.0-dev apache2

You have successfully installed PHP on your Ubuntu machine!

Step 1.3 Install the ODBC Driver and SQL Command Line Utility for SQL Server

SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.

Terminal

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list |

sudo tee /etc/apt/sources.list.d/mssql-tools.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install mssql-tools
sudo apt-get install unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

After installing SQLCMD, you can connect to SQL Server using the following command:

Terminal

sqlcmd -S localhost -U sa -P yourpassword
1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to

execute each batch of statements.

This how to run a basic inline query. The results will be printed to the STDOUT.

Terminal

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"

Results

--------------------------------------------------------
Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)
 Apr 13 2017 11:44:40
 Copyright (c) Microsoft Corporation
    on Linux (Ubuntu 16.04)

1 rows(s) returned

Executed in 1 ns

You have successfully installed SQL Server Command Line Utilities on your Ubuntu machine!

In this section you will create a simple PHP app. The PHP app will perform basic Insert, Update, Delete, and Select.

Step 2.1 Install the PHP Driver for SQL Server

Terminal

sudo pecl install sqlsrv pdo_sqlsrv
sudo echo "extension= pdo_sqlsrv.so" >> `php --ini | grep "Loaded Configuration" |

  sed -e "s|.*:\s*||"`
sudo echo "extension= sqlsrv.so" >> `php --ini | grep "Loaded Configuration" |

sed -e "s|.*:\s*||"`

Step 2.2 Create a database for your application

Create the database using sqlcmd

Terminal

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;"

Step 2.3 Create a PHP app that connects to SQL Server and executes queries

Terminal

mkdir SqlServerSample
cd SqlServerSample

Using your favorite text editor, create a new file called connect.php in the SqlServerSample folder. Paste the code below inside into the new file.

PHP


    $serverName = "localhost";
    $connectionOptions = array(
        "Database" => "SampleDB",
        "Uid" => "sa",
        "PWD" => "your_password"
    );
    //Establishes the connection
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    if($conn)
        echo "Connected!"
?>

Run your PHP script from the terminal.

Terminal

php connect.php

Results

Connected!

Execute the T-SQL scripts below in the terminal with sqlcmd to create a schema, table, and insert a few rows.

Terminal

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE SCHEMA TestSchema;"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE TABLE TestSchema.Employees

(Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "INSERT INTO TestSchema.Employees

(Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "SELECT * FROM TestSchema.Employees;"

Using your favorite text editor, create a new file called crud.php in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.

PHP


$serverName = "localhost";
$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "sa",
    "PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Insert Query
echo ("Inserting a new row into table" . PHP_EOL);
$tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);";
$params = array('Jake','United States');
$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);

sqlsrv_free_stmt($getResults);

//Update Query

$userToUpdate = 'Nikita';
$tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?";
$params = array('Sweden', $userToUpdate);
echo("Updating Location for user " . $userToUpdate . PHP_EOL);

$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) updated: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

//Delete Query
$userToDelete = 'Jared';
$tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?";
$params = array($userToDelete);
$getResults= sqlsrv_query($conn, $tsql, $params);
echo("Deleting user " . $userToDelete . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);
sqlsrv_free_stmt($getResults);


//Read Query
$tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL);

}
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
    /* Display errors. */
    echo "Error information: ";

    foreach ( $errors as $error )
    {
        echo "SQLSTATE: ".$error['SQLSTATE']."";
        echo "Code: ".$error['code']."";
        echo "Message: ".$error['message']."";
    }
}
?>

Run your PHP script from the terminal.

Terminal

php crud.php
Copy

Results

Inserting a new row into table
1 row(s) inserted:
Updating Location for user Nikita
1 row(s) updated:
Deleting user Jared
1 row(s) deleted:
Reading data from table
2 Nikita Sweden
3 Tom Germany
4 Jake United States

Congrats you created your first PHP app with SQL Server! Check out the next section to learn about how you can make your PHP faster with SQL Server’s Columnstore feature.

Now that you have explored the basics, you are ready to see how you can make your app better with SQL Server. In this module we will show you a simple example of Columnstore Indexes and how they can improve data processing speeds. Columnstore Indexes can achieve up to 100x better performance on analytical workloads and up to 10x better data compression than traditional rowstore indexes.

Step 3.1 Create a new table with 5 million using sqlcmd

Terminal

sqlcmd -S localhost -U sa -P your_password -d SampleDB -t 60000 -Q "WITH a AS

(SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
SELECT TOP(5000000)
ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a * 10 AS Price
,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a)

 AS ProductName
INTO Table_with_5M_rows
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;"

Step 3.2 Create a PHP app that queries this tables and measures the time taken

Terminal

cd ~/
mkdir SqlServerColumnstoreSample
cd SqlServerColumnstoreSample

Using your favorite text editor, create a new file called columnstore.php in the SqlServerColumnstoreSample folder. Paste the following code inside it.

PHP


$time_start = microtime(true);

$serverName = "localhost";
$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "sa",
    "PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Read Query
$tsql= "SELECT SUM(Price) as sum FROM Table_with_5M_rows";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Sum: ");
if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['sum'] . PHP_EOL);

}
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
    /* Display errors. */
    echo "Error information: ";

    foreach ( $errors as $error )
    {
        echo "SQLSTATE: ".$error['SQLSTATE']."";
        echo "Code: ".$error['code']."";
        echo "Message: ".$error['message']."";
    }
}
$time_end = microtime(true);
$execution_time = round((($time_end - $time_start)*1000),2);
echo 'QueryTime: '.$execution_time.' ms';


?>

Step 3.3 Measure how long it takes to run the query

Run your PHP script from the terminal.

Terminal

php columnstore.php
Copy

Results

Sum: 50000000
QueryTime: 363ms

Step 3.4 Add a columnstore index to your table.

Terminal

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE CLUSTERED

COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"

Step 3.5 Measure how long it takes to run the query with a columnstore index

Terminal

php columnstore.php
Copy

Results

Sum: 50000000
QueryTime: 5ms

Congrats you just made your PHP app faster using Columnstore Indexes!

https://www.microsoft.com/en-us/sql-server/developer-get-started/php/ubuntu/

Buscar en moleculax

Create PHP apps using SQL Server on Ubuntu

Step 1.1 Install SQL Server

Step 1.2 Install PHP and other required packages

Step 1.3 Install the ODBC Driver and SQL Command Line Utility for SQL Server

Step 2.1 Install the PHP Driver for SQL Server

Step 2.2 Create a database for your application

Step 2.3 Create a PHP app that connects to SQL Server and executes queries

Step 3.1 Create a new table with 5 million using sqlcmd

Step 3.2 Create a PHP app that queries this tables and measures the time taken

Step 3.3 Measure how long it takes to run the query

Step 3.4 Add a columnstore index to your table.

Step 3.5 Measure how long it takes to run the query with a columnstore index