 Start
|
| Unternehmen |
| ERP / PPS / Prozesse |
| Business Intelligence |
| Server-Technologien |
| Software-Technologien |
| Technologie-Beratung |
| Individual-Software |
| Produkte |
Comelio GmbH
Rellinghauser Straße 10
D-45128 Essen
Deutschland
Fon: 0201-437517-0
Fax: 0201-437517-10
info@comelio.com
Comelio GmbH
Goethestraße 34
D-13086 Berlin
Deutschland
info@comelio.com
Comelio GmbH (Ecos)
Glockengießerwall 17
D-20095 Hamburg
Deutschland
info@comelio.com
Comelio GmbH (Ecos)
Mainzer Landstraße 27-31
D-60329 Frankfurt
Deutschland
info@comelio.com
Comelio GmbH (Ecos)
Stiglmaierplatz/Dachauer Str. 37
D-80335 München
Deutschland
info@comelio.com
Comelio GmbH (Ecos)
Liebknechtstr. 33
D-70565 Stuttgart
Deutschland
info@comelio.com
Comelio GmbH
Nevinghoff 16
D-48147 Münster
Deutschland
Comelio GmbH
Friedrich - List - Platz 1
D-04103 Leipzig
Deutschland
Comelio GmbH
St. Johanner Strasse 41-43
D-66111 Saarbrücken
Deutschland
Comelio GmbH
Kaiser-Wilhem-Ring 27–29
D-50672 Köln
Deutschland
Comelio GmbH
Münsterstraße 248
D-40470 Düsseldorf
Deutschland
Comelio GmbH
Fürther Strasse
D-90429 Nürnberg
Deutschland
Comelio GmbH
Bremen
Deutschland
Comelio-Blog > PHP > MS SQL Server
PHP mit MS SQL Server
|
|
 | Kontakt |
Im Microsoft-Umfeld dürfte man als Großdatenbank konstant auf den MS SQL Server stoßen. Er ist ähnlich umfangreich wie Oracle, aber durch eine entsprechende Microsoft-GUI einfacher zu bedienen. Dennoch übertrifft er MySQL hinsichtlich seiner Funktionalitäten um ein Vielfaches und ist natürlich sehr mit Oracle zu vergleichen. Dieser Artikel zeigt die PHP-Funktionen und wie in Transact-SQL geschriebene Prozeduren und Funktionen aus einer PHP-Umgebug heraus aufgerufen werden können.MS SQL Server und PHP
MS SQL Server: Im Microsoft-Umfeld dürfte man als Großdatenbank konstant auf den MS SQL Server stoßen. Er ist ähnlich umfangreich wie Oracle, aber durch eine entsprechende Microsoft-GUI einfacher zu bedienen. Dennoch übertrifft er MySQL hinsichtlich seiner Funktionalitäten um ein Vielfaches und ist natürlich sehr mit Oracle zu vergleichen. Für die Verwendung dieses Systems benötigt man folgende Schlüssel:
- PHP-Funktionen. Wie für allen Datenbanken besitzt PHP auch eine Vielzahl an Funktionen für die Zusammenarbeit mit dem MS SQL Server. Alternativ lassen sich auch die ODBC-Funktionen nutzen, die z.B. auch für die Arbeit mit MS Access nützlich sind. Da allerdings ODBC als Standard nicht mehr von Microsoft favorisiert wird und eigene Funktionen bereit stehen, die alle ODBC-Schwierigkeiten vermeiden, werden in den Rezepten diese spezielleren Funktionen vorgestellt.
- Transact-SQL: Mit der SQL-Erweiterung bzw. dem SQL-Dialekt vom MS SQL Server hält man ein ähnlich mächtiges Werkzeug in der Hand wie mit SQL und PL/SQL von Oracle. Allerdings stellt es weitestgehend nur eine Spracherweiterung von SQL um Schlüsselwörter für Fallunterscheidungen, Schleifen und natürlich die Erstellung von Funktionen und Prozeduren dar. Es bieten sich dennoch die gleichen Vorteile, die durch die datenbankbnahe Programmierung erreicht werden, was in einem Beispiel gezeigt wird, in dem eine im MS SQL Server gespeicherte Prozedur aufgerufen wird. T-SQL beinhaltet auch eine Vielzahl an statistischen Funktionen, die für die Arbeit mit der DB überaus wichtig sind, da viele Berechnungen und Verarbeitungsschritte, die mit MySQL in der Anwendung erledigt werden müssen und zu langen Quelltexten führen, in einer raffinierten SQL-Abfrage komplett abgearbeitet werden können.
Wie stelle ich eine Verbindung her?
Für die Verwendung von Großdatenbanken benötigt die php.ini-Datei Zusatzeinstellungen. Im Zusammenhang mit dem MS SQL Server ist dies nur die Angabe, wo die Erweiterung bzw. das Modul php_mssql.dll gefunden werden kann.
; Directory in which the loadable extensions (modules) reside. ; extension_dir = "./" extension_dir = "C:\php\extensions" ;Windows Extensions extension=php_mssql.dllVorgaben für die Verwendung des MS SQL Servers unter PHP
Zusätzlich muss im SQL Server für den Datenbankserver (nicht für die Datenbank!) die Einstellung getroffen werden, dass die Authentifizierung über Windows und die Datenbank selbst erfolgt.
Ausgabe einer Tabelle
Während sich einige Einstellungen zur DB-Benutzung über selbst erklärende Funktionen aus dem PHP-Sprachschatz einstellen lassen, gibt es diverse Möglichkeiten, die Verwendung mit PHP auch in der php.ini-Datei zu konfigurieren. Dies ist insbesondere notwendig, wenn größere Datenmengen, d.h. lange Abfragen zu verarbeiten sind oder viele Benutzer gleichzeitig auf dem Server angemeldet sein sollen.
[MSSQL] ; Allow or prevent persistent links. mssql.allow_persistent = On ; Maximum number of persistent links. -1 means no limit. mssql.max_persistent = -1 ; Maximum number of links (persistent+non persistent). -1 means no limit. mssql.max_links = -1 ; Minimum error severity to display. mssql.min_error_severity = 10 ; Minimum message severity to display. mssql.min_message_severity = 10 ; Compatability mode with old versions of PHP 3.0. mssql.compatability_mode = Off ; Connect timeout ;mssql.connect_timeout = 5 ; Query timeout ;mssql.timeout = 60 ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textlimit = 4096 ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textsize = 4096 ; Limits the number of records in each batch. 0 = all records in one batch. ;mssql.batchsize = 0 ; Specify how datetime and datetim4 columns are returned ; On => Returns data converted to SQL server settings ; Off => Returns values as YYYY-MM-DD hh:mm:ss ;mssql.datetimeconvert = On ; Use NT authentication when connecting to the server mssql.secure_connection = Off ; Specify max number of processes. Default = 25 ;mssql.max_procs = 25Konfigurationseinstellungen in der php.ini
Die folgende Klasse setzt einige der MS SQL Server-Funktionen von PHP ein, um eine Datenbankverbindung einzurichten und ein gültiges Verbindungshandle zurückzugeben, welches wiederum von anderen Funktionen oder eigenen Methoden genutzt werden kann.
class DB_MSS{
// Connection
var $User;
var $Password;
var $Server;
var $Database;
// Open connection
function Connect()
{
$DB["Con"] = mssql_connect($this->Server,
$this->User,
$this->Password)
or die("Connection failed.");
mssql_select_db($this->Database, $DB["Con"]);
return $DB["Con"];
}
// Report errors
function Report_Errors(){
// Retrieve messages
$Error = mssql_get_last_message();
// Return value
return $Error;
}
// Close connection
function Close($Connection){
$DB["Con"] = mssql_close($Connection);
return $DB["Con"];
}
}
mssqlserver_output.php: Verbindung zur DB herstellen
Bei der Verwendung dieser Klasse genügt es, die Eigenschaften der Klasse mit geeigneten Werten aufzufüllen. Im Beispiel handelt es sich dabei um Werte des Standardbenutzers sa auf einem Test-Server. Dies ist nicht der Name der Datenbank, welcher in einer eigenen Eigenschaft erfasst wird. Hier ist wichtig, dass bei Namen mit Leerzeichen noch zusätzliche eckige Klammern verwendet werden müssen.
// Include classes
include("mssqlserver_output.php");
// Use and test connection
$DB["MSS"] = new DB_MSS();
$DB["MSS"] -> User = "sa";
$DB["MSS"] -> Password = "sa";
$DB["MSS"] -> Server = "test";
$DB["MSS"] -> Database = "[Kurse NRW]";
$DB["Con"] = $DB["MSS"] -> Connect();
// Close connection
$DB["Con"] = $DB["MSS"] -> Close($DB["Con"]);
mssqlserver_output_test.php: Verbindung zur DB herstellen
Wie gebe ich eine Tabelle aus?
Um eine Tabelle komplett auszugeben, lässt sich die nachfolgende Methode einsetzen. Sie erwartet eine für die automatische Komplettverarbeitung vorbereitete Abfrage und ein Verbindungshandle. Die SQL-Abfrage sollte eine „perfekte“ Abfrage ein, in der Spaltennamen für die Ausgabe sowie Formatierungen von Zeichenketten oder sonstige Ausgabebestandteile wie HTML-Elemente über Konkatenierung mit Spaltenergebnissen ebenfalls enthalten sind. Die Verarbeitung ähnelt – im Vergleich zu den Oracle-Funktionen – sehr den MySQL-Funktionen.
function Make_Table($SQL, $Connection){
// Parse SQL-statement
$Result = mssql_query($SQL, $Connection);
// Analyze result structure
$Columns = mssql_num_fields($Result);
$Rows = mssql_num_rows($Result);
// Prepare output
echo "\n<table border=\"0\">\n"
// Output table heading
."\t<tr>\n";
for ($i=0; $i<$Columns; $i++) {
echo "\t<th>"
.mssql_field_name($Result, $i)
."</th>\n";
}
echo "</tr>\n";
// Output data
while($Row = mssql_fetch_row($Result)){
echo "<tr>\n";
for($i=0; $i < $Columns; $i++){
$Field = $Row[$i];
// Switch NULL-fields to non-breaking-space
if($Field =="") $Field = " ";
// Data-cell
echo"\t<td>$Field</td>\n";
}
echo "</tr>\n";
}
// Finish output
echo "</table>\n";
// Free Resultset
$Result = mssql_free_result($Result);
}
mssqlserver_output.php: Ausgabe einer Tabelle
Diese Methode lässt sich mit einer für die Ausgabe vorbereiteten SQL-Abfrage testen und erlaubt eine einfache Ausgabe der Ergebnisse in Tabellenform.
// SQL-query
$SQL = "SELECT YEAR(T_Beginn) AS \"Zeit\",
SUBSTRING(K_Bereich,1,20) AS \"Bereich\",
COUNT(termin.T_Nr) AS \"Termine\",
COUNT(TN_Nr) AS \"Teilnehmer\",
SUM(B_Preis) AS \"Summe\"
FROM termin INNER JOIN buchung
ON termin.T_Nr = buchung.T_Nr
INNER JOIN kurs
ON kurs.K_Nr = termin.K_Nr
WHERE K_Bereich IN ('Programmierung', 'Datenbanken')
AND T_Beginn > '01.01.2001'
GROUP BY YEAR(T_Beginn), K_Bereich WITH ROLLUP";
// Table output
$DB["MSS"] -> Make_Table($SQL, $DB["Con"]);
mssqlserver_output_test.php: Ausgabe einer Tabelle
In der HTML-Ausgabe erhält man die Spaltennamen bzw. deren Aliasnamen als Spaltenköpfe in <th>-Elementen und die Tabellendaten in <td>-Elementen mit korrekter Quelltextformatierung in Form von Zeilenumbrüchen und Einrückungen.
Ausgabe einer Tabelle
Wie gebe ich eine Liste aus?
Um eine nummerierte oder nicht-nummerierte Liste komplett auszugeben, lässt sich die nachfolgende Methode einsetzen. Sie erwartet eine für die automatische Komplettverarbeitung vorbereitete Abfrage und ein Verbindungshandle sowie verschiedene Formatierungsparameter für die Darstellung der Liste. Die SQL-Abfrage sollte eine „perfekte“ Abfrage ein, in der Formatierungen von Zeichenketten oder sonstige Ausgabebestandteile wie HTML-Elemente über Konkatenierung mit Spaltenergebnissen ebenfalls enthalten sind. Für die Erzeugung von nicht-nummerierten Listen steht die Methode Make_UL_List() bereit, während die Methode Make_OL_List() nummerierte Listen generiert.
// Print out a UL-List
protected function Print_List_Start($Title){
// Prepare output list
if ($Title !== ""){
echo "<h1>"
.$Title
."</h1>\n";
}
}
protected function Print_List_End($Line){
// Finish output list
if ($Line !== ""){
echo "<hr align=\"left\" noshade width=\""
.$Line
."\"><br>\n";
}
}
protected function Print_List_Items($SQL){
// Get data
$Result = mssql_query($SQL);
// Process data
while ($Row = mssql_fetch_row($Result)){
foreach ($Row as $Field){
if ($Field !="") echo "\t<li>$Field</li>\n";
}
}
// Free result
mssql_free_result($Result);
}
// Prints out a UL-list
function Make_UL_List($SQL, $Connection, $Type, $Title, $Line){
// Prepare output list (1)
DB_MSS::Print_List_Start($Title);
// Prepare output list (2)
echo "<ul type=\""
.$Type
."\">\n";
// Output list (3)
DB_MSS::Print_List_Items($SQL, $Connection);
// Finish output list (2)
echo "</ul>\n";
// Finish output list (1)
DB_MSS::Print_List_End($Line);
}
// Prints out a OL-list
function Make_OL_List($SQL, $Connection, $Type, $Title, $Line){
// Prepare output list (1)
DB_MSS::Print_List_Start($Title);
// Prepare output list (2)
echo "<ol type=\""
.$Type
."\">\n";
// Output list (3)
DB_MSS::Print_List_Items($SQL, $Connection);
// Finish output list (2)
echo "</ol>\n";
// Finish output list (1)
DB_MSS::Print_List_End($Line);
}
mssqlserver_output.php: Ausgabe einer Liste
Für den Test soll einmal ein Beispiel für die „perfekte“ Abfrage bemüht werden, in welcher über einfache Zeichenketten-Verknüpfung ein Link zu einer anderen PHP-Seite erzeugt wird, auf der Detail-Informationen zu dem per DISTINCT angegebenen Punkt angezeigt werden. Auch eine solche Liste lässt sich über die Methoden Make_UL_List() oder Make_OL_List() erzeugen, weil das spezielle Ausgabeformat direkt in der Abfrage vorgegeben wird.
// Use and test list-function
$SQL = "SELECT DISTINCT '<a href=\"bereich.php?bereich='
+ K_Bereich
+ '\">'
+ K_Bereich
+ '</a>' AS Bereich
FROM kurs";
$DB["MSS"] -> Make_UL_List($SQL,
$DB["Con"],
"square",
"Bereichübersicht",
"350");
$DB["MSS"] -> Make_OL_List($SQL,
$DB["Con"],
"a",
"Bereichübersicht",
"350");
mssqlserver_output_test.php: Ausgabe einer Liste
Ausgabe einer nicht-nummerierten Liste
Ausgabe einer nummerierten Liste
Im HTML-Ergebnis erhält man tatsächlich eine Liste der durch Duplikatausblendung erzeugten Daten, verbunden mit einem Link zu einer Detailseite, auf welcher ein entsprechender Parameter für mehr Information zum ausgewählten Bereich sorgt.
<ol type="a">
...
<li><a href="bereich.php?bereich=Grafik">Grafik</a></li>
<li><a href="bereich.php?bereich=Office">Office</a></li>
…
<li><a href="bereich.php?bereich=Server">Server</a></li>
<li><a href="bereich.php?bereich=Webdesign">Webdesign</a></li>
</ol>
Ausgabe von Links
Wie speichere ich Feldinformationen in einem Array?
Sofern man für Feldinformationen nicht den einfachen DESC tabellenname | viewname-Befehl benutzen möchte oder auch nicht auf die unterschiedlichen Data-Dictionary-Sichten zurückgreifen will, die natürlich alle wesentlich umfangreichere Ergebnisse liefern, kann man auch für eine Abfrage über die in der folgenden Methode vorgestellten PHP-Funktionen Informationen über Felder erhalten. Sie sind nicht so umfangreich wie die Informationen von MySQL, bieten aber zumindest Zugriff auf die wichtigsten Daten. Die Daten werden von der Methode in Form eines Arrays mit durch ein Trennzeichen getrennten Werten zurückgeliefert, das sich nachher jeweils über die explode()-Funktion in einzelne Array zerteilen lässt.
function Get_Field_Props_Array($Table,
$Connection,
$Separator){
// SQL-query
$SQL = "SELECT TOP 1 * FROM "
.$Table;
// Get field information
$Result = mssql_query($SQL, $Connection);
// Report errors
echo mssql_get_last_message();
// Prepare data structure for processing
$Fields = array();
$i = 0;
$FieldsCount = mssql_num_fields($Result);
// Process field properties
while ($i < $FieldsCount){
// Extract Properties
$Name = mssql_field_name($Result, $i);
$Type = mssql_field_type($Result, $i);
$Length = mssql_field_length($Result, $i);
// Combine properties in output stream
$Fields[$i] = $Table
.$Separator
.$Name
.$Separator
.$Type
.$Separator
.$Length;
// Incrementation
$i++;
}
// Free result
mssql_free_result($Result);
// Return value
return $Fields;
}
mssqlserver_output.php: Ermittlung von Feldinformationen
Für den Aufruf ist ein passendes Verbindungshandle, die Angabe einer Tabelle und natürlich ein Trennzeichen wichtig, von dem bekannt ist, dass es nicht als Bestandteile eines Spaltennamens auftritt. Über eine foreach-Schleife lassen sich dann die ermittelten Werte in jedem Array-Eintrag verarbeiten.
$Fields = array();
$Fields = $DB["MSS"] -> Get_Field_Props_Array("unternehmen",
$DB["Con"],
"|");
echo "<ul>\n";
foreach ($Fields as $Value){
echo "\t<li>"
.$Value
."</li>\n";
}
echo "</ul>\n";
mssqlserver_output.php: Ermittlung von Feldinformationen
Man erhält eine durch das Trennzeichen getrennte Zeichenkette, in welcher die Eigenschaften in der vorgegebenen und bekannten Form untergebracht sind. Mit Hilfe der explode()-Funktion ließen sich jetzt diese Werte wiederum in ein Array transformieren und einzeln verarbeiten.
<ul>
<li>unternehmen|U_NR|numeric|19</li>
<li>unternehmen|U_NAME|char|255</li>
...
<li>unternehmen|U_PLZ|numeric|19</li>
<li>unternehmen|U_STADT|char|60</li>
<li>unternehmen|U_TELART|char|20</li>
<li>unternehmen|U_VORWAHL|numeric|19</li>
<li>unternehmen|U_TELNR|numeric|19</li>
</ul>
Ausgabe von Feldinformationen
Wie übergebe ich Parameter an SELECT?
Einer der Vorteile, die bei der Verwendung von Großdatenbanken genutzt werden können, ist der Einsatz der in der Datenbank gespeicherten Funktionen und Prozeduren. Mit ihnen lassen sich alle Anwendungsbereiche, die direkt mit der Datenbank verwoben sind und Daten beschaffen, auswerten oder verarbeiten, in der Datenbank abarbeiten und außen nur noch die Ergebnisse berücksichtigen. Folgende Funktion in Transact SQL, dem SQL-Dialekt vom MS SQL Server - von Oracle, ermittelt für eine gegebene Kursnummer und eine gegebene Teilnehmeranzahl den Preis pro Person aus den entsprechenden Tabellen. Sie liefert einen Wert, nämlich genau den Preis pro Person, zurück.
DROP FUNCTION fct_getprice
GO
CREATE FUNCTION fct_getprice
(@TrainingNr INT,
@Participants INT)
RETURNS INT
AS
BEGIN
DECLARE @Price INT
IF (@Participants > 5 OR @Participants = 5)
BEGIN
SELECT @Price = P_TN5
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
IF (@Participants <= 0 OR @Participants = 1)
BEGIN
SELECT @Price = P_TN1
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
IF (@Participants = 2)
BEGIN
SELECT @Price = P_TN2
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
IF (@Participants = 3)
BEGIN
SELECT @Price = P_TN3
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
IF (@Participants = 4)
BEGIN
SELECT @Price = P_TN4
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
RETURN @Price
END
getprice.sql: Funktion für die Preisabfrage
Für die Übergabe von Parametern an Funktionen und Prozeduren muss man zunächst die entsprechende Funktion/Prozedur mit der Funktion mssql_init() initialisisieren und das entstehende Handle in den Funktionen mssql_bind() und mssql_execute() übergeben. Mit Hilfe der Funktion mssql_bind() bindet man Parameter an die Funktion/Prozedur, wobei zum einen ein passender Datentyp übergeben werden muss und die Angabe erwartet wird, ob es sich um einen Ausgabeparameter handelt. Dies ist der Wert TRUE für Ausgabe- und FALSE für Eingabeparameter. Wird ein Rückgabewert verwendet, gibt es die spezielle Angabe RETVAL.
// Parameters for function
$TrainingNr = 1025096;
$Participants = 3;
$TrainingPrice = "";
// Execute query
$STMT = mssql_init("fct_getprice",$DB["Con"]);
mssql_bind($STMT,"RETVAL",&$TrainingPrice,SQLFLT8);
mssql_bind($STMT,"@TrainingNr",&$TrainingNr,SQLINT4,FALSE);
mssql_bind($STMT,"@Participants",&$Participants,SQLINT4,FALSE);
$Result=mssql_execute($STMT, TRUE);
// Look up result-variable
echo "Course "
.$TrainingNr
." costs "
.$TrainingPrice
." €"
." for each of "
.$Participants
." participants.";
mssqlserver_parameters.php: Übergabe von Parametern an Funktion
Folgende Datentypen für die Verwendung in der Funktion mssql_bind() stehen bereit:
- Zeichenketten: SQLTEXT, SQLVARCHAR, SQLCHAR
- Zahlen: SQLINT1, SQLINT2, SQLINT4, SQLBIT, SQLFLT8
Ausgabe im Browser
Wie übergebe ich Parameter an Prozeduren?
Einer der Vorteile, die bei der Verwendung von Großdatenbanken genutzt werden können, ist der Einsatz der in der Datenbank gespeicherten Funktionen und Prozeduren. Mit ihnen lassen sich alle Anwendungsbereiche, die direkt mit der Datenbank verwoben sind und Daten beschaffen, auswerten oder verarbeiten, in der Datenbank abarbeiten und außen nur noch die Ergebnisse berücksichtigen. Folgende Funktion in Transact SQL, dem SQL-Dialekt vom MS SQL Server - von Oracle, ermittelt für eine gegebene Kursnummer und eine gegebene Teilnehmeranzahl den Preis pro Person aus den entsprechenden Tabellen. Sie liefert einen Wert, nämlich genau den Preis pro Person, zurück.
DROP PROCEDURE proc_getprice
GO
CREATE PROCEDURE proc_getprice
@TrainingNr INT,
@Participants INT,
@Price NUMERIC OUTPUT
AS
DECLARE @Price INT
IF (@Participants > 5 OR @Participants = 5)
BEGIN
SELECT @Price = P_TN5
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
IF (@Participants <= 0 OR @Participants = 1)
BEGIN
SELECT @Price = P_TN1
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
IF (@Participants = 2)
BEGIN
SELECT @Price = P_TN2
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
IF (@Participants = 3)
BEGIN
SELECT @Price = P_TN3
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
IF (@Participants = 4)
BEGIN
SELECT @Price = P_TN4
FROM kurs INNER JOIN preis
ON kurs.P_Nr = preis.P_Nr
WHERE K_Nr = @TrainingNr
END
getprice.sql: Prozedur zur Preisermittlung
Für die Übergabe von Parametern an Funktionen und Prozeduren muss man zunächst die entsprechende Funktion/Prozedur mit der Funktion mssql_init() initialisisieren und das entstehende Handle in den Funktionen mssql_bind() und mssql_execute() übergeben. Mit Hilfe der Funktion mssql_bind() bindet man Parameter an die Funktion/Prozedur, wobei zum einen ein passender Datentyp übergeben werden muss und die Angabe erwartet wird, ob es sich um einen Ausgabeparameter handelt. Dies ist der Wert TRUE für Ausgabe- und FALSE für Eingabeparameter. Wird ein Rückgabewert verwendet, gibt es die spezielle Angabe RETVAL.
Folgende Datentypen für die Verwendung in der Funktion mssql_bind() stehen bereit:
// Parameters for procedure
$TrainingNr = 1015024;
$Participants = 6;
$TrainingPrice = "";
// Execute query
$STMT = mssql_init("proc_getprice",$DB["Con"]);
mssql_bind($STMT,"@Price",&$TrainingPrice,SQLFLT8, TRUE);
mssql_bind($STMT,"@TrainingNr",&$TrainingNr,SQLINT4,FALSE);
mssql_bind($STMT,"@Participants",&$Participants,SQLINT4,FALSE);
$Result=mssql_execute($STMT, TRUE);
// Look up result-variable
echo "Course "
.$TrainingNr
." costs "
.$TrainingPrice
." €"
." for each of "
.$Participants
." participants.";
mssqlserver_parameters.php: Übergabe von Parametern an Prozeduren
- Zeichenketten: SQLTEXT, SQLVARCHAR, SQLCHAR
- Zahlen: SQLINT1, SQLINT2, SQLINT4, SQLBIT, SQLFLT8
Ausgabe im Browser
Wie verwende ich Data-Warehouse-Abfragen?
Die Großdatenbanken stellen einen Mechanismus bereit, der umfangreiche Abfrage ermöglicht, welche zu Berichten führen, die mit kleineren Datenbanken ohne die entsprechenden Funktionalitäten nur schwer zu realisieren sind. Für Abfragen, welche nicht auf statistische Funktionen zurückgreifen bzw. in einer üblichen Data-Warehouse-Abfrageform ausgegeben werden, lässt sich wünschen, dass die Gruppierungen, die in Form einer großen Ergebnismenge zurückgegeben werden, in einzelne Tabellen aufgeteilt und mit passenden Tabellentiteln versehen werden. Zusätzlich ist zu wünschen, dass die automatisch entstehenden NULL-Felder, welche fehlende Daten in der Ergebnismenge (und nicht etwa in der Datenbank!) kennzeichnen, ebenfalls so berücksichtigt werden, dass die entstehenden Tabellen immer nur eine Minimalspannweite an Spalten aufweisen.
Eine Abfrage in der folgenden Form weist folgende Eigenschaften auf:
- Über die Gruppierungserweiterungen wie CUBE, ROLLUP oder auch GROUPING SETS (nur Oracle) richtet man unterschiedliche Dimensionen ein, unter deren Blickwinkel die Daten präsentiert werden. Mit CUBE richtet man sogar einen Würfel ein, der alle möglichen Kombinationen der vorgegebenen Spalten und damit die maximale Anzahl an Kombinationsmöglichkeiten erzeugt.
- Über die Funktion GROUPING, welche für die Dimensionen eingesetzt wird, erhält man genau dann den Wert 1, wenn die Spalte in der jeweiligen Betrachtung einen NULL-Wert aufweist, der durch die Abfrage hervorgerufen wurde, und den Wert 0, wenn die Spalte einen Wert aus der Datenbank aufweist.
SELECT SUBSTRING(K_Bereich,1,15) AS "Bereich",
SUBSTRING(K_Titel,1,15) AS "Titel",
YEAR(T_Beginn) AS "Jahr",
COUNT(B_Nr) AS "Buchungen",
GROUPING(K_Bereich) AS "B",
GROUPING(K_Titel) AS "T",
GROUPING(YEAR(T_Beginn)) AS "J"
FROM kurs INNER JOIN termin
ON kurs.K_Nr = termin.K_Nr
INNER JOIN buchung
ON termin.T_Nr = buchung.T_Nr
GROUP BY K_Bereich, K_Titel, YEAR(T_Beginn) WITH CUBE
ORDER BY B, T, J
dw_query.sql: Abfrage eines Würfels
In der Ergebnismenge entstehen die unterschiedlichen Kombinationen und Zuordnungen in den Dimensionen bzw. Schnitten durch den Würfel entlang der diversen Achsen, sofern CUBE eingesetzt wird. Wie man unschwer erkennen kann, bietet die Spalten, welche mit Spaltenfunktion GROUPING erzeugt wurden, eine hervorragende Möglichkeit, die verschiedenen Gruppen, welche das Abfrageergebnis bilden, hervorzuheben und sie voneinander zu unterscheiden.
Bereich Titel Jahr Buchungen B T J --------------- --------------- ----------- ----------- ---- ---- -- Datenbanken Access 2000 44 0 0 0 Datenbanken Access 2001 22 0 0 0 Datenbanken Access 2002 40 0 0 0 Datenbanken Access 2003 27 0 0 0 … Webdesign HTML NULL 58 0 0 1 Webdesign GoLive NULL 37 0 0 1 Webdesign Dreamweaver NULL 145 0 0 1 Server Windows 2000 Se NULL 56 0 0 1 Server TCP IP NULL 6 0 0 1 Server MS Exchange NULL 29 0 0 1 … Datenbanken NULL 2000 95 0 1 0 Grafik NULL 2000 84 0 1 0 Office NULL 2000 46 0 1 0 Programmierung NULL 2000 278 0 1 0 … Datenbanken NULL NULL 557 0 1 1 Grafik NULL NULL 384 0 1 1 Office NULL NULL 146 0 1 1 … NULL Access 2000 44 1 0 0 NULL Access 2001 22 1 0 0 NULL Access 2002 40 1 0 0 NULL Access 2003 27 1 0 0 … NULL Word NULL 30 1 0 1 NULL Windows 2000 Se NULL 56 1 0 1 NULL VisualBasic NULL 136 1 0 1 … NULL NULL 2002 632 1 1 0 NULL NULL 2001 295 1 1 0 NULL NULL 2003 1313 1 1 0 … NULL NULL NULL 2835 1 1 1Ergebnisse mit unterschiedlichen Schnitten durch den Würfel / Dimensionierungen
Die folgende Klasse erzeugt eine Ausgabe, in der jede einzelne der durch die GROUPING-Funktion identifizierbaren Gruppen in einer einzelnen Tabelle ausgegeben wird, welche auf die NULL-Felder und ihre jeweiligen Spalten verzichtet. Dadurch erhält man eine übersichtliche und kompakte Darstellung für Schnitt und Dimension. Die verschiedenen Eigenschaften haben folgende Bedeutung:
- SQL speichert die eigentliche SQL-Abfrage in einer geeigneten Form, welche dem Beispiel ähneln sollte
- Connection erwartet ein gültiges Verbindungshandle
- Grouping erwartet in einer einfachen Zeichenkette die durch Komma getrennten Namen der GROUPING-Spalten
- Statistics erwartet mit den beiden möglichen Werten top oder bottom die Angabe, wo – wenn gewünscht – eine kleine Statistik zur Abfrage ausgegeben werden soll
- Headings speichert in der erwarteten Reihenfolge (Abfrage vorher testen!) die Titel der Tabellen
- Separator speichert das Trennzeichen zwischen den in Headings eingegebenen Titeln
- HeadingsTags speichert den Tag ohne spitze Klammern, der für die Tabellentitel verwendet werden soll
Der grundlegende Algorithmus sieht so aus, dass die Dimensionen, welche durch die GROUPING-Funktion bestimmt werden und über der Eigenschaft GROUPING gemeldet werden, in ein Array zerlegt werden, das die Spaltennamen in der Ergebnismenge identifiziert und ihre Werte zu einer Zahl zusammenfasst. Über zwei Merker-Variablen lässt sich dann der Wechsel von einer Gruppe zur anderen identifizieren, sodass hier eine neue Tabelle ausgegeben werden kann. Über die Dimensionen und die NULL-Felder regelt man schließlich, dass nur die Spalten von nicht-NULL-Feldern und auch nur nicht-NULL-Felder ausgegeben werden sollen.
class DW_Query_MSS{
// Connection
var $SQL;
var $Connection;
var $Grouping;
var $Statistics;
var $Headings;
var $Separator;
var $HeadingsTag = "h1";
protected function Split_Grouping(){
$Dimensions = array();
$Dimensions = explode(",", str_replace(" ", "", $this->Grouping));
return $Dimensions;
}
protected function Split_Headings(){
$Titles = array();
$Titles = explode("|", $this->Headings);
return $Titles;
}
protected function Make_Title($k, $Titles){
echo "<"
.$this->HeadingsTag
.">"
.$Titles[$k]
."</"
.$this->HeadingsTag
.">";
}
protected function Make_Statistics($Result){
// Analyse result-set
$Rows = mssql_num_rows($Result);
$Dimensions = array();
$Dimensions = DW_Query_MSS::Split_Grouping($this->Grouping);
// Output statistics
echo "<br>"
.$Rows
." Rows"
." | "
.count($Dimensions)
." Dimensions";
}
function Make_Table(){
// Prepare dimensions-array
$Dimensions = array();
$Dimensions = DW_Query_MSS::Split_Grouping();
// Parse SQL-statement
$Result = mssql_query($this->SQL, $this->Connection);
// Analyze result structure
$Columns = mssql_num_fields($Result);
$Rows = mssql_num_rows($Result);
// Analysze dimensions
$NumberDim = array();
for ($i=0; $i<$Columns; $i++) {
// Omit non-GROUPING-columns
if (! is_bool(array_search(mssql_field_name($Result,
$i),$Dimensions))){
$NumberDim[] = $i;
}
}
// Help-variables
$FieldOld = ""; // GROUPING-values of last fetch
$FieldNew = ""; // GROUPING-valies of new fetch
$j = 0; // counter for fetching
$k = 0; // counter for table-titles
// Output statistics
if ($this->Statistics == "top"){
DW_Query_MSS::Make_Statistics($Result);
}
// Output data
$Fields = array();
while($Fields = mssql_fetch_row($Result)){
// Initialize new set of GROUPING-values
$FieldNew = "";
// Setup of GROUPING-value
for ($i = 0; $i < count($NumberDim); $i++){
$FieldNew .= $Fields[$NumberDim[$i]];
}
// Output table-heading if change in GROUPING-values
if ($FieldNew != $FieldOld){
// End table
if ($FieldOld != "") echo "</table>\n";
// Output table title
if (! empty($this->Headings)){
if ($k == 0){
$Titles = array();
$Titles = DW_Query_MSS::Split_Headings();
}
DW_Query_MSS::Make_Title($k, $Titles);
$k++;
}
// Start table
echo "\n<table border=\"0\">\n"
// Output table heading
."\t<tr>\n";
if ($j <= $Rows){
// Only not-NULL-fields need to be printed
mssql_data_seek($Result, $j);
$Fields = mssql_fetch_row($Result);
for ($i=0; $i<$Columns; $i++) {
if (is_bool(array_search(mssql_field_name($Result,
$i),$Dimensions))){
// Print heading of not-NULL-fields
if ($Fields[$i] != ""){
echo "\t<th>"
.mssql_field_name($Result, $i)
."</th>\n";
}
}
}
}
// End table heading
echo "</tr>\n";
}
// Output data-row
echo "<tr>\n";
for($i=0; $i < $Columns; $i++){
// Omit GROUPING-columns
if (is_bool(array_search(mssql_field_name($Result, $i),$Dimensions))){
$Field = $Fields[$i];
// Data cell, Output if not NULL
if($Field !="") echo"\t<td>$Field</td>\n";
}
}
// End data-row
echo "</tr>\n";
// Incrementation
$j++;
$FieldOld = $FieldNew;
}
// Output end-table for last table
if ($j == $Rows){
echo "</table>\n";
}
// Output statistics
if ($this->Statistics == "bottom"){
DW_Query_MSS::Make_Statistics($Result);
}
// Free Resultset
$Result = mssql_free_result($Result);
}
}mssqlserver_DWquery.php: Verarbeitung einer DW-Abfrage
Bei der Verwendung ist nur darauf zu achten, dass die Abfrage für die Klasse geeignet ist und die notwendigen Eigenschaften vorgegeben werden. Für die optionalen Eigenschaften lässt sich individuell ihre Angabe und ihre Wertzuweisung überlegen. Die Titel für die einzelnen Tabellen entsprechen für das Beispiel den entstehenden Spaltennamen, sodass man die Funktionsweise und die Ergebnismenge noch einmal verstehen kann.
// Include classes
include_once("mssqlserver_output.php");
include("mssqlserver_dwquery.php");
// Use and test connection
$DB["MSS"] = new DB_MSS();
$DB["MSS"] -> User = "sa";
$DB["MSS"] -> Password = "sa";
$DB["MSS"] -> Server = "DOZENT";
$DB["MSS"] -> Database = "[Kurse NRW]";
$DB["Con"] = $DB["MSS"] -> Connect();
// SQL-query
$SQL = "SELECT SUBSTRING(K_Bereich,1,30) AS \"Bereich\",
SUBSTRING(K_Titel,1,20) AS \"Titel\",
YEAR(T_Beginn) AS \"Jahr\",
COUNT(B_Nr) AS \"Buchungen\",
GROUPING(K_Bereich) AS \"B\",
GROUPING(K_Titel) AS \"T\",
GROUPING(YEAR(T_Beginn)) AS \"J\"
FROM kurs INNER JOIN termin
ON kurs.K_Nr = termin.K_Nr
INNER JOIN buchung
ON termin.T_Nr = buchung.T_Nr
GROUP BY K_Bereich, K_Titel, YEAR(T_Beginn) WITH CUBE
ORDER BY B, T, J";
// Table output
$DB["DW"] = new DW_Query_MSS();
$DB["DW"] -> Grouping = "B, T, J";
$DB["DW"] -> SQL = $SQL;
$DB["DW"] -> Connection = $DB["Con"];
$DB["DW"] -> Statistics = "bottom";
$DB["DW"] -> Headings = "Bereich - Titel - Jahr - Buchungen | "
."Bereich - Titel - Buchungen | "
."Bereich - Jahr - Buchungen | "
."Bereich - Buchungen | "
."Titel - Jahr - Buchungen | "
."Titel - Buchungen | "
."Jahr - Buchungen | "
."Buchungen";
$DB["DW"] -> Separator = "|";
$DB["DW"] -> Make_Table();
mssqlserver_dwquery_test.php: Ausgabe einer DW-Abfrage
Ausgabe einer Dimension in einer eigenen Tabelle
Ausgabe einer Dimension in einer eigenen Tabelle
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln DuisburgComelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln DuisburgComelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln DuisburgComelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln DuisburgComelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln DuisburgComelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln DuisburgComelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln DuisburgComelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln DuisburgComelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Münster Essen Bochum Bonn Frankfurt Köln Duisburg
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü
- Comelio GmbH PHP: Verwendung von MS SQL Server Tutorial XML Anleitung SQL LDAP MySQL Handbuch PHP SQLJ Hamburg Berlin Dortmund Wuppertal Düsseldorf Mü