Archive

Archive for the ‘SQL Server’ Category

PHP and SQL Server Stored Procedures, how to insert a new record under 4 steps

November 11, 2008 3 comments

Having SQL Server storing data for your PHP web application isn’t a common combination but is a far better option than having MySQL when it comes to larger database. I wont brag about why choose SQL Server over MySQL instead will quickly demonstrate in few steps how to execute stored proc in PHP using mssql database extension library.

We will be using mssql_query to execute the stored procedure and return records added using SCOPE_IDENTITY(). Please accept my appologies for coding indentation and lack colouring! @@

Let’s take a task list scenario where we manage our to-do tasks in a table having an ID, name, date added, priority task, and active fields.

First step, create your table in SQL server like the following:

tbltask

Step 2, Create a stored procedure to insert records:

CREATE proc [dbo].[usp_InsertTask]
(
@TaskName varchar(255),
@TaskDateAdded datetime,
@TaskPriority int,
@TaskActive bit
)
as
insert into tblTask(TaskName, TaskDateAdded, TaskPriority, TaskActive)
values (@TaskName, @TaskDateAdded, @TaskPriority, @TaskActive)
SELECT SCOPE_IDENTITY() as id
RETURN

Note “SELECT SCOPE_IDENTITY() as id” will return the inserted record TaskID set as auto increment.

Check out Pinal Dave, SQL Server MVP, blog on the differenced between SCOPE_IDENTITY() vs @@IDENTITY vs IDENT_CURRENT to retrieve the last inserted record.

Step3, back to PHP to create your mssql_connect connection and execute stored procedure

<?php
$myServer = “10.64.0.7”;
$myUser = “mydevuser”;
$myPass = “mydevpassword”;
$myDB = “devdatabase”;

// connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die(“Couldn’t connect to SQL Server on $myServer”);

// select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die(“Couldn’t open database $myDB”);

//setup variable data
$TaskName = ‘Configure SQL Server’;
$TaskDateAdded = ’12/22/2008′;
$TaskPriority = 2;
$TaskActive = true;

$result = mssql_query(“usp_InsertTask ‘$TaskName’, ‘$TaskDateAdded’, ‘$TaskPriority’, ‘$TaskActive'”, $dbhandle);
OR
$result = mssql_query(“EXEC dbo.usp_InsertTask @TaskPriority = ‘$TaskPriority’, @TaskName = ‘$TaskName’, @TaskDateAdded = ‘$TaskDateAdded’, @TaskActive = ‘$TaskActive'”, $dbhandle);
$result = mssql_fetch_object($result);
echo “Record ID = “.$result->id;

// close the connection
mssql_close($dbhandle);
?>

Step 4, DONE!

Most blog posts found when google-ing php and stored procedures SQL Server will go on about using mssql_execute function instead of mssql_query directly. But you will find much more posts about how it actually doesn’t work as php.net/mssql manual suggested. Probably because of new version of SQL server? PHP? or wrong mssql driver or configuration?

The steps i have provided is working under PHP5 on a Debian dev server and SQL Server 2008 CTP.

Microsoft HTTPAPI/2.0 disabling Apache

November 7, 2008 209 comments

I’be been working on php for 2 months now and developing on my laptop with WAMP installed before uploading to work’s dev server. Now working with SQL Server instead of MySQL, I’ve installed SQL SERVER 2008 CTP for test and suddently Apache went down as port 80 was used by Microsoft HTTPAPI/2.0.

I then found SSRS (SQL Server Reporting Services) was still running after uninstalling SQL Server 2008 as it features a web service even though IIS is not installed according to wiki.

I couldn’t find any info on google relating to this small issue that puzzled me for a short while. i hope this helps finding ppl stupid like me sometimes to solve their problem

SQL Server + pizza = excited QUT students

May 10, 2007 3 comments

Peter Ward and Charles Sturling have driven excitement to about 25 QUT students during the SQL Server Campus Event. We have seen SQL Server Business Intelligence Technologies, especially SQL Server 2005 Reporting Services

 img_3381.jpg

Students warming up of excitements for a friday afternoon 🙂

img_3379.jpg

Students that wish to further their knowledge in SQL Server, the next QSSUG is on Thurs, 24 May 2007

Venue: Microsoft Brisbane Office, Level 9, Waterfront Place, 1 Eagle Street

Duration : Catering from 5:30PM for a 6:00PM Start

Please check http://www.qssug.org/ and RSVP to peter@wardyit.com will help with catering

Peter Ward presents SQL Server 2005 to QUT students

April 24, 2007 Leave a comment

It is with great pleasure to have Peter Ward presenting SQL Server 2005 at the Queensland University of Technology on May the 4th.

“Peter Ward is WARDY IT Solutions Chief Technical Architect. Peter is an active member in the Australian SQL Server community and President of the Queensland SQL Server User Group. Peter is a highly regarded speaker at SQL Server events throughout Australia and is a sought after SQL Server consultant and trainer, providing solutions for some of the largest SQL Server sites in Australia. Peter is a regular author for several SQL Server websites and has published numerous articles in the monthly SQL Server newsletter that he produces along with the highly acclaimed WARDY IT Solutions SQL Server Blog. Peter has been selected as a Spotlight speaker for the 2007 SQL Pass Community Summit, the largest SQL Server event in the world and has recently been awarded as a Microsoft Most Valuable Professional for his technical excellence and commitment to the SQL Server Community” – http://wardyit.com/about.aspx

Students will have the privilege to understand the importance of SQL Server in the industry and tackle the best features of SQL Server 2005. They will also be introduced the SQL Server community based in Brisbane, the Queensland SQL Server User Group.

I find this event a great opportunity for QUT students to hear from a professional user of SQL Server sharing his valuable knowledge. It is also encouraging and motivating students starving of technology.

Invitation to SQL Server talk @ QUT

April 24, 2007 1 comment

 untitled-2.jpg

The Faculty of Information Technology invites you to the Microsoft SQL Server 2005 campus event.

In this event, you will be presented with key SQL Server 2005 features and understand the importance of SQL Server in the industry.  Free Pizzas and drinks will be provided and Visual Studio Express Editions including SQL Server 2005 Express. 

Event details:

  • Speaker: Peter Ward (Solution Chief Technical Architect and MVP Windows Server System – SQL Server)
  • Date: 04-May-2007
  • Time: 15:00 – 17:00
  • Venue: S405a, S block, GP
  • Admission: FREE! 

Agenda:

  • 15:00 – 15:30 – Pizza and soft drinks
  • 15:30 – 15:40 – Introduction
  • 15:40 – 16:00 – Importance of SQL Server in the industry
  • 16:00 – 16:50 – SQL Server 2005 Features
  • 16:50 – 17:00 – Locknote and QSSUG info 

To help organize with seats and pizzas & drinks, please RSVP to g.tchenpan@qut.edu.au

Hope to see you there!