sql server

Connect Sql Server 5.6 from Php in Linux CentOS 6

Posted by admin on March 28, 2016
sql server / No Comments

To connect to SQL Server from Linux use this command to install the driver:

$ sudo yum install -y php-mssql

For example, to php5.6:

$ sudo yum install -y php56w-mssql

This command installs freetds , a collection of libraries to connect to SQL Server from Linux. With this library we can not only connect from Php, but from the command line with the tsql program.

After installing, do not forget to restart apache:

$ sudo apachectl restart

Happy coding 🙂

Tags: , , , , , , ,

Sql Server object creation scripts, the right way

Posted by admin on March 28, 2016
sql server / No Comments

The right way to write object creation scripts in Sql Server (create tables, procedures, functions, constraints, etc) is as follows:

  1. First, check is the object exists.
    • If it does, delete it.
  2. Then, create the object
  3. Next, repeat for all the objects that depend upon the object we just create

**Important** Always write “USE Database GO” at the beginning of the script.

**Importante** Always include descriptive comment about the objects, always write your comments as if you were explaining to a kid, make them clear and short.

Let’s see an example, table creation script.

USE
    MyDataBase
GO

/* Object: MyTable */
-- If exists, delete it
IF EXISTS (
    SELECT
        *
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_NAME = 'MyTable'
)
BEGIN
    DROP TABLE
        MiTabla
END
GO

/*
 * MyTable
 *     - Test table, stores testing data
 * Include relevant information as author, date, project name, reason for creation, etc.
 */
CREATE TABLE
    MyTable(
        MyTableId int NOT NULL,
        Description varchar(30) NOT NULL,
    )
GO

/**
 * PK_MiTabla
 *     - Primary key constraint
 */
ALTER TABLE
   MyTable
ADD CONSTRAINT
   PK_MyTable
PRIMARY KEY (
   MyTableId
)
GO

As we can see, the identation is very important for the code to be clear and legible. I like the following format:

COMMAND
    arguments

With this format I can read the instructions vertically with ease. Now will see an example for a stored procedure. It is really important to document the following:

  1. Functionality: What the procedure does
  2. List of parameters, including type and description
  3. Usage example

There are other important data to include, as change history, where at least we must specify:

  1. Who write the change
  2. Why
  3. When

Write our scripts this way, in case we want to run them in another server, they will not cause conflict if they run twice by mistake, and if the execution is made for somebody else, it will very clear for the other person know what is doing and why.

/* Object: myStoredProcedure */

-- If exists, delete it
IF EXISTS (
	SELECT
		*
	FROM
		INFORMATION_SCHEMA.ROUTINES
	WHERE
		ROUTINE_NAME = 'myStoredProcedure'
		AND ROUTINE_TYPE = 'PROCEDURE'
)
BEGIN
	DROP PROCEDURE
		myStoredProcedure
END
GO

/*
	myStoredProcedure:
	-	Select all records of my table using the key @myParameter
	Parameters:
		@myParameter INT - The table id
			
	Usage:
		EXEC myStoredProcedure 1001
	
	// Other relevant information
        Luis Espino, March 27, 2016, procedure creation
            - Example of creation script for the blog
*/
CREATE PROCEDURE
	myStoredProcedure (
		@myParameter INT
	)
AS
BEGIN
	--	Logic brief description
	--	    Select the records using the parameters
	SELECT
		*
	FROM
		MiTabla
	WHERE
		MiTablaId = @miParametro
END
GO

-- Grant permissions to users that can use the procedure, public in this case
GRANT EXECUTE ON
	myStoredProcedure
TO
	PUBLIC
GO

It’s really easy, the important thing is to keep order and structure in the code.