Hasznos script-ek MS SQL Server-ben

A példához szükséges a SampleDB adatbázis, melyet a mellékelt Run.cmd BATCH állomány lefuttatásával hozhatunk létre. Csak arra kell ügyelni, hogy a SampleDB.sql parancsállomány a BATCH állománnyal azonos mappában legyen. A SampleDB.sql parancsállomány 5. sorában adja meg helyesen a létrehozandó adatbázisfájlok mappájának nevét és elérési útvonalát. A mellékelt példa használata előtt importálnia kell a Microsoft XML komponenst. Ennek módja a következők: a Project menüből válasszuk ki az Import Type Library menüpontot. A listában keressük meg a Microsoft XML elemet. A Create Unit gombbal hozzuk létre a unit-ot, amelyre a programban a uses kulcsszó után hivatkozunk.

Üzenetküldés tárolt eljárásból

Az xp_cmdshell beépített tárolt eljárás sokak számára lehet ismerős, használatának lehetőségei széles palettán mozognak. A tárolt eljárással tetszőleges parancs végrehajtható, mintha csak a parancssorba írnánk azokat. Használata igen egyszerű, csak meg kell adni egy karakterlánc formájában a kívánt parancsot a tárolt eljárás paramétereként és lefuttatni azt. Szintaxisa:

xp_cmdshell <parancs>

A bemenő paramétere varchar(255) vagy nvarchar(4000) lehet, alapértelmezett értéke nincs.

A mellékelt alkalmazásban a SendMessage tárolt eljárásban valósítottuk meg az üzenetküldést a fenti ismeretek birtokában. Az eljárás kódja a következő:

create procedure SendMessage (@target varchar(100), @message varchar(500))
as
  declare @m varchar(500)
  set @m = 'net send ' + @target + ' "' + @message + '"'
  exec master.dbo.xp_cmdshell @m
GO

Az eljárás két paramétert vár: az első a cél számítógép IP-címét vagy nevét tartalmazza, míg a másik paraméter az üzenet szövegét. Látható, hogy az üzenet a NET SEND parancsokkal történik, melyet a parancssorokban is használunk a Windows rendszerben.

A parancs után kell megadni a célcímet, és azt követően – idézőjelek közt – az üzenet szövegét.

A TARGET nevű számítógépre így a következő módon küldhető üzenet:

net send TARGET ”Minta üzenet...”

Az üzenet egy kis üzenetablakban jelenik meg.

OPENXML metódus

Az OPENXML metódus segítségével egy szöveges formában megadott XML állományból nyerhetők ki adatok, melyeket a metódus egy úgynevezett ROWSET formájában visszaad. A kinyert adatok azt követően tetszőleges további SQL utasítások INPUT-jai lehetnek.

A metódus szintaxisa a leggyakrabban használt argumentumokkal a következő:

OPENXML
  (idoc int [in],
   rowpattern nvarchar[in],
   [flags byte[in]])
   [WITH (sémadeklaráció | táblanév)]

Az argumentumok listája pedig az alábbi – számunkra most fontos – elemeket tartalmazhatja:

A SampleDb.sql parancsállomány lefuttatása után létrejött tárolt eljárás neve GetDataFromXML, melynek első paramétere az XML állományt karakterláncként tartalmazó string.

Az Root.xml állományt az alkalmazásban beolvassuk egy karakterláncba, melyet a tárolt eljárás megkap. Az XML szerkezete a következő:

<?xml version="1.0" ?>
<ROOT>
<Customer CustomerID="1001" ContactName="Paul Henriot">
   <Order OrderID="10101" CustomerID="1001" EmployeeID="5" OrderDate="1996-07-04">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
...
</ROOT>

Az információ az XML állomány tagjainak attribútumaiban található, innen kell kiolvasnunk a metódussal. Látható, hogy az XML három szint mélységű, vagyis Customers, Orders és OrderDetails elemeket találunk a Root tagban.

A mellékelt példában az OPENXML fül alatt elegendő kiválasztanunk egy OrderID értéket, mely a középső szinten van, és a metódus visszaadja a megrendeléshez kapcsolódó megrendelő kapcsolattartójának nevét.

Lássuk, hogyan is történik ez a tárolt eljárásban.

Create procedure GetDataFromXML(@xmldoc varchar(1000),@oid int,@cname varchar(30) output)
AS
  DECLARE @idoc int
  DECLARE @name varchar(30)

Az említett módon generáljuk a kapott dokumentum azonosítóját:

  EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

Majd egy SELECT utasítás részeként meghívva a metódust lekérdezzük a ROWSET-ből a kért információt. Ehhez a WITH kulcsszóra van szükségünk, melyben megadjuk a műveletben résztvevő változókat.

  SELECT @name = ContactName
  FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
  WITH (ContactName varchar(30) '../../@ContactName',
        OrderID       int         '../@OrderID')
  WHERE OrderID = @oid
  Set @cname = @name
GO

A hívó alkalmazásban az XML állomány információit egy StringBuilder objektumba olvassuk be, és azt követően adjuk át a tárolt eljárás első paraméterének.

Az alkalmazásban a LEKÉRDEZÉS gombra kattintva indíthatjuk a műveletet.