Creating reports and dashboards | Stimulsoft community forum

Posted: **Thu Jul 28, 2011 5:48 pm**

I have been trying to get the PHP code to work with a MSSQL database using a new installation of PHP 5.3, PHP MSSQL Driver and IIS7.5 and had to convert a handful of calls inside the database_mssql.php file to get it working properly.

PHP no longer uses the mssql_connect or mssql_* anything commands, they have replaced it with sqlsrv_connect or sqlsrv_* sometimes changing many of them entirely.

I managed to make the changes and tested the reports and it looks like it went through okay.

This is the database_mssql.php file, updated to use sqlsrv instead of mssql.

Code: Select all

 "",
			"database" => "",
			"user_id" => "",
			"password" => "",
		);

		$parameters = explode(";",$connection_string);
		foreach($parameters as $parameter)
		{
			if (strpos($parameter, "=")  1) $value = $parts[1];
			
			if (isset($value))
			{
				switch (strtolower($name))
				{
					case "data source":
					case "server":
						$info["host"] = $value;
						break;

					case "database":
					case "initial catalog":
						$info["database"] = $value;
						break;
						
					case "uid":
					case "user":
					case "user id":
						$info["user_id"] = $value;
						break;

					case "password":
					case "pwd":
						$info["password"] = $value;
						break;
				}
			}
		}

		return $info;
	}

	function sti_mssql_get_column_type($type)
	{
		switch($type)
		{
			case 2:
			case 4:
				return "int";
			
			case 93:
			case 91:
				return "dateTime";
			
			case 3:
			case 7:
				return "decimal";
		}
		
		return "string";
	}
	
	function sti_mssql_test_connection($connection_string)
	{
		$info = sti_mssql_parse_connection_string($connection_string);
		$link = sqlsrv_connect($info["host"], array("UID" => $info["user_id"], "PWD" => $info["password"], "Database" => $info["database"])) or die("ServerError:Could not connect to host '".$info["host"]."'");
		sqlsrv_close($link);
		
		return "Successfull";
	}

	function sti_mssql_get_columns($connection_string, $query)
	{
		$info = sti_mssql_parse_connection_string($connection_string);
		$link = sqlsrv_connect($info["host"], array("UID" => $info["user_id"], "PWD" => $info["password"], "Database" => $info["database"])) or die("ServerError:Could not connect to host '".$info["host"]."'");
		
		$query = sti_parse_query_parameters($query);
		$result = sqlsrv_query($link,$query) or die("ServerError:Data not found" );

		$xml_output = "\n\n  \n";

		foreach(sqlsrv_field_metadata($result) as $field_metadata)
		{
			$column_type = sti_mssql_get_column_type($field_metadata["Type"]);
			$xml_output .= "    \n";
		}

		$xml_output .= "  \n";

		sqlsrv_free_stmt($result);
		sqlsrv_close($link);

		return $xml_output;
	}

	function sti_mssql_get_data($connection_string, $data_source_name, $query)
	{
		$info = sti_mssql_parse_connection_string($connection_string);
		$link = sqlsrv_connect($info["host"], array("UID" => $info["user_id"], "PWD" => $info["password"], "Database" => $info["database"],"ReturnDatesAsStrings" => true)) or die( print_r( sqlsrv_errors(), true));
		
		$query = sti_parse_query_parameters($query);
		$result = sqlsrv_query($link,$query) or die("ServerError:Data not found");

		$xml_output = "\n\n";

		$columns = Array();
		foreach(sqlsrv_field_metadata($result) as $field_metadata)
		{
			array_push($columns, $field_metadata);
		}

		while ($row = sqlsrv_fetch_array($result))
		{
			$xml_output .= "  \n";
			foreach($columns as $column)
			{
				$value = $row[$column["Name"]];
				$value = str_replace("&", "&", $value);
				$value = str_replace("", ">", $value);

				$xml_output .= "    $value\n";
			}
			$xml_output .= "  \n";
		}
		
		$xml_output .= "";

		sqlsrv_free_stmt($result);
		sqlsrv_close($link);

		return $xml_output;
	}

?>

Posted: **Fri Jul 29, 2011 8:52 am**

Hello,

Thank you very much for the improved script. We joined together two versions of the script and send you the finished version. Please try it out.

Thank you.

Posted: **Fri Jul 29, 2011 5:13 pm**

While testing I made the following changes.

Line 81 typo (fixed below in entire function)

Code: Select all

if (function_exists("mysql_set_charset"))

I also (for some reason) had to change the return structure of the sti_mssql_connect function. It was not returning a resource on the sqlsrv_connect (i think the or die was making it return TRUE instead of a resource). Also had to include the "ReturnDatesAsStrings" => true to the connection string, as it could not convert from dateTime (again, not sure why it is such a big change).

Code: Select all

	function sti_mssql_connect($connection_string)
	{
		$info = sti_mssql_parse_connection_string($connection_string);
		if (function_exists("mssql_connect"))
		{
			$link = mssql_connect($info["host"], $info["user_id"], $info["password"]) or die("ServerError:Could not connect to host '".$info["host"]."'");
			mssql_select_db($info["database"], $link) or die("ServerError:Could not find database '".$info["database"]."'");
		} 
		else 
		{
			$link = sqlsrv_connect($info["host"], array("UID" => $info["user_id"], "PWD" => $info["password"], "Database" => $info["database"],"ReturnDatesAsStrings" => true)) or die("ServerError:Could not connect to host '".$info["host"]."'");
		}
		return $link;
	}

Posted: **Mon Aug 01, 2011 1:02 am**

Hello,

We have made these changes, thank you very much.

Creating reports and dashboards | Stimulsoft community forum

PHP sqlsrv connection

PHP sqlsrv connection

PHP sqlsrv connection

PHP sqlsrv connection

PHP sqlsrv connection