T-SQL multi OUTPUT when using sp_executesql

One of my current professional dev project is to write an ETL from scratch using a lot of technologies. I had to play a lot with T-SQL (Transact-SQL) which was quite interesting to work with.

The code I will share today (not about RIA at all) is a cool trick when using sp_executesql. In some case, when you want to retreive only one record from a db, it could be interesting to have the result (the fields) or a part (some fields) of the result directly as variables instead of using another table to store the result and then re query to retreive the fields you want.

Imagine you have a Table called myContacts that have 6 columns:

id|Field1|Field2|Field3|Field4|Field5       <id as a primary key

And you want to query this table to retreive the record where id=4 and directly set some fields as variables, so you will be able to use them right after in you code.

Here is the trick :

declare
--The variables that will house the fields
   @myField1   int,
   @myField3   nvarchar(max),
   @myField5   nvarchar(max),

--query variables
   @sql						nvarchar(max),
   @ParamDef					nvarchar(max)

--The query
select @sql = 'select @myField1=Field1, @myField3=Field3, @myField5=Field5 FROM myContacts WHERE id = 4'

--The parameters
select @ParamDef = '@myField1 int OUTPUT, @myField3 nvarchar(max) OUTPUT, @myField5 nvarchar(max) OUTPUT'

--Execute de sql statement
exec sp_executesql @sql, @paramDef, @myField1 OUTPUT, @myField3 OUTPUT, @myField5 OUTPUT

Print 'Field #1 = '+convert(nvarchar,@myField1)+ ' Fields 3 & 5 : '+@myField3+',' +@myField5

OUTPUT : Field #1 = 1 Fields 3 & 5 : This is the Content 3, And this is Content 5

That’s an easy way to initiate variable on fly with from a sp_executesql result.
 

Advertisements

NURVE – Public alpha release

I’m pretty excited today since I’m releasing the first public alpha version of a project I’m working on, based on Adobe AIR/FLEX and Papervision3D , called NURVE.

NURVE is 3D, film trailers browser with an iTunes coverflow like navigation experience. NURVE allows you to browse the newest films trailers and watch them in HD quality on any, internet connected, OS that have AIR installed

Preview:

 

As my first shot with both FLEX and Papervision3D and since NURVE is still under development you may find some bugs/glitches or suggestions so please drop me an email or leave a comment here.

=> Download NURVE  (don’t forget to install Adobe AIR  )

Expected in the new release:

  • more 3D animations
  • film information displayed
  • some fix 
  • new icons
  • more films source

 

Like most of my works, I will release le source code as soon as most of the bugs are fixed;)

How to Create/Read HTTP POST request

This post is actually a follow-up to my last one called http post streaming where I tried to create a php class that provides a way to send a HTTP POST request. I recoded the class and added new features (e.g. a way to read the body of an HTTP request, a dynamic way of setting the headers fields…etc.).

The body of an HTTP request can be easily read from php by using php://input stream. This stream return the raw POST data.

The phpWebRequest class is still under developpment but I post it because it may help some of you. (Of course anytime I add a feature I will update this post)

TO DO:

  • Finish the DataReaderFactory so we can use either read from an url or file to create the body of the request.
  • Create an HTTPs support

 

phpWebRequest
 

<?php
/*
PhpWebRequest objects provide methods to send or read HTTP requests
Date: June-08
Author: Guillaume Nachury
Version: 0.1

*/

class PhpWebRequest{
//VERSION
var $version = "0.1";

//HTTP
var $boundary;
var $method;
var $header;
var $body;
var $protocol;
var $headerElements; 

//SERVER
var $address;
var $path;
var $port;

var $isOverSSL = false;

//FEED
var $xml;

/**
* InitServer()
* $a = server address
* $p = path to the ressource
* $prt = port (by default 80)
*/
function InitServer($a,$p,$prt=80){
$this->address = $a;
$this->path = $p;
$this->port = $prt;
}

/**
* InitRequest()
* $m = the HTTP method you wanna use
* $secured = in case we use HTTPS
* $bounds = a string to create a boundary (unused since v 0.1)
*/
function InitRequest($m, $secured = false, $bounds="PhpWebRequest"){
$this->method = $m;
$this->boundary = md5($bounds);
$this->isOverSSL = $secured;
if($this->isOverSSL === false){
$this->protocol = "HTTP/1.1";
}
else{
$this->protocol = "HTTPS";
}
}

/**
* DataReaderFactory()
* a factory that can read data from either a file, an URL or a String to be used as the content of the request
* $u = the path to the data ( for a file start with file://
* for an URL start with http://
* for string just enter chars)
*/
function DataReaderFactory($u){
//if we wanna read from a file
if(strpos($u,'file://' ) === true){

}
//or from a remote web page
elseif(strpos($u,'http://' ) === true){

}
//a string have been passed
else{
$this->xml=$u;
return strlen($this->xml);
}
}

/**
* CfgHeader()
* Create the HTTP header fields
* $attributesArray = an array of headers that MUST have keys
* keys = field name
* value = field calue
*/
function CfgHeader($attributesArray){
while(list($k,$v) = each($attributesArray)){
$this->headerElements .= $k.": ".$v."\n";
}
}

/**
* CraftRequest()
* Create a HTTP request
*/
function CraftRequest(){
$this->header = $this->method." ".$this->path." ".$this->protocol. "\n".$this->headerElements."\n";
$this->body = $this->xml."\n";
}

/**
* SendRequest()
* Send the request, return true if successfully sent
*/
function SendRequest(){
$this->CraftRequest();
$request = $this->header.$this->body;

$fs = fsockopen($this->address,$this->port);
if(!$fs){
return false;
}
else{
fputs($fs,$request);
fflush($fs);
fclose($fs);
return true;
}

}

/**
* readHTTPRequest()
* return the HTTP request BODY
*/
function readHTTPRequest(){
return @file_get_contents('php://input');
}

}
?>

Usage :

<?php
require_once('phpWebRequest.class.php');

$rq = new PhpWebRequest();
$rq->InitServer("127.0.0.1","/aPath/test.php");
$strSize = $rq->DataReaderFactory("the body of the request");
$rq->InitRequest("POST");

//header fields
$headerElements = array("Host" => "127.0.0.1",
"Connection" => "Close",
"Content-type" => "multipart/mixed",
"Content-length" => $strSize
);
//load the headers
$rq->CfgHeader($headerElements);

//send it
if($rq->SendRequest()){
echo "Request sent !";
}
else{
echo "ERROR";
}

?>

If you have any question feel free to send me an email.

HTTP POST Streaming

Yesterday I’ve been asked by a friend how we can easily http stream data from php. That was a really intersting question because it can be very useful in some case (e.g. when working with EDI). So I just made him a proof of concept.
I used socket to generate a POST HTTP request 

<?php
class PostRequest{
	//SERVER
	var $address;
	var $path;
	var $port;
	
	//XML
	var $xmlFile;
	
	//HTTP 
	var $boundary;
	
	
	
	function initRequest($addr, $p, $prt=80){
		$this->address = $addr;
		$this->path = $p;	
		$this->port = $prt;
		$this->boundary = md5("myString");
		
	}
	
	function setXML($x){
		$this->xmlFile = $x;
	}
	
	function send(){
	$fs = fsockopen($this->address, $this->port);
	if(!$fs){
		return false;	
		}
	else{
		$header ="POST ".$this->path." HTTP/1.0 200\r\nMIME-Version: 1.0\r\nContent-Type: multipart/mixed; boundary=".$this->boundary."\r\n\n";
		$this->body ="--".$this->boundary."\n
						Content-type: text/plain\n\n".$this->xmlFile."\n--".$this->boundary."\n";
						
		$request = $header.$this->body;
		
		
		fputs($fs,$request);
		fflush();
	fclose();
		return true;	
	
		}
	
	}



?>

I post the code but I will update it very soon since I’ve only made few test and I think it must be tweaked

Big Buck Bunny

As the first post of this blog, I should explain the goals of this blog, what you gonna find …etc. But instead I will talk about Big Buck Bunny

Big Buck Bunny is an amazing open movie created with open source tools (such as Blender) and licensed under the Creative Commons Attribution 3.0

So that means you can freely : Share and Remix the work. That’s really a good news to see that open source tools can provide such a good results.

Must be seen : Elephants Dream (previous project)