Optimalizace Sestav a Tiskových formulářů pro MSSQL

1) Tiskové formuláře a SQL Server: 

Tiskové formuláře lze jednoduše optimalizovat pro SQL server. Následující text by měl optimalizaci usnadnit. Optimalizace spočívá ve vytvoření předávacích dotazů, které budou vyhodnocovány přímo SQL serverem. Zdrojem tiskových formulářů (dále jen TF) budou potom právě tyto dotazy. V databázi, ve které je TF uložen je potřeba vytvořit tabulku Dotazy_SQL s následující strukturou (jako vzor lze naimportovat tabulku Dotazy_SQL ze souboru Vario.mda):

Tabulka Dotazy_SQL:

Pole Typ MSSQL Typ MS Access Popis pole
Dotaz varchar(255) text(255) jméno dotazu, který se vytvoří v databázi s TF (Cilova_databaze)
SQL text memo definice dotazu (SQL string) v syntaxi Transact SQL
Parametry text memo deklarace parametrů ve formátu: 
parametr;datovy_typ_parametru_VBA;......  (např. parCisloDokladu;string;parCelkem;currency)
povolené datové typy jsou: Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, String, Nahrada.
Predavaci YesNo Ano/Ne pokud je TRUE(-1), pak je předávací (je zpracován SQL serverem),
pokud je FALSE(0), jedná se o běžný dotaz Accessu
Cilova_databaze varchar(128) text(128) databáze, ve které se bude předávací dotaz spouštět. Není-li dotaz předávací (Predavaci = 0), je hodnota ignorována. (Například Data, DMaj ....). Je-li hodnota v poli Cilova_databaze tabulky Dotazy_SQL ukončena znakem '\' (zpětné lomítko), není databáze číslována (např. hodnota 'Firmy\' zaručí, že předávací dotaz bude spuštěn nad databází Firmy a ne Firmy0001)
ODBCTimeout int long ODBC Timeout dotazu v sekundách (výchozí 600)


V tabulce je potřeba vyplnit definice dotazů, které chceme použít.


Příklad pro tiskový formulář Pohledávky zpětně ke dni SQL:

Dotaz Sestava Pohledavky zpetne ke dni_SQL
SQL SELECT Doklady.*,
      isnull(Souvisejici_doklady.Uhrazeno_po*PV,0) AS Uhrazeno_pozdeji,
      (Zbyva_uhradit*PV) + isnull(Souvisejici_doklady.Uhrazeno_po*PV,0) AS Saldo,
      Zbyva_uhradit*PV AS ZbyvaUhradit,
      Celkem*PV AS CelkemPV,
      uhrazeno*PV - isnull(Souvisejici_doklady.Uhrazeno_po*PV,0) AS UhrazenoPV
      FROM
      Doklady
      INNER JOIN Knihy
      ON Doklady.Kniha = Knihy.Kniha
      LEFT JOIN
       (
       SELECT convert(varchar(30), Souvisejici_doklady.Cislo_dokladu) AS Cislo_dokladu,
        convert(money, Sum(Souvisejici_doklady.Castka_platby)) AS Uhrazeno_po
       FROM Souvisejici_doklady
       WHERE Souvisejici_doklady.Datum>parDatum
        AND Souvisejici_doklady.Typ_souvislosti = 'PU'
       GROUP BY Souvisejici_doklady.Cislo_dokladu
       )
      AS Souvisejici_doklady
      ON Doklady.Cislo_dokladu = Souvisejici_doklady.Cislo_dokladu
      WHERE (Zbyva_uhradit*PV + isnull(Souvisejici_doklady.Uhrazeno_po*PV,0)<>0)
      AND (Doklady.Datum<=parDatum)
      AND (Doklady.PV<>0)
      AND (Knihy.Agenda='Vydané doklady')
      AND (Doklady.Typ_dokladu IN('FV','DV'))
      ORDER BY Doklady.Cislo_dokladu
Parametry parDatum;date
Predavaci -1
Cilova_databaze Data
ODBC Timeout 600

Je-li definice dotazu vytvořena, zbývá zavolat z vhodného místa funkci mSQL.PripravDotazSQL:

*** ZAČÁTEK ***

Sub SetZdroj_1()
    Dim Parametry As String
'Ve formuláři potřebuji zobrazit hodnotu proměnné Datum
    Datum = CDate(InputBox("Zadejte datum do:"))
'na SQL serveru je vhodné používat datum v takzvaném univerzálním formátu 'YYYYMMDD', o to se postará funkce mSQL.DateSQL()
    Parametry = "parDatum;'" & msql.DateSQL(Datum) & "'"
    msql.PripravDotazSQL "Sestava Pohledavky zpetne ke dni_SQL", _
      app.AktualniData, CodeDb, CodeDb, firmy.Item(0).server, Parametry
End Sub

*** KONEC ***

Toto volání vytvoří v databázi codedb předávací dotaz s připojením na cílovou databázi a s daným SQL stringem. Existuje-li již v databázi dotaz daného jména, je pouze nastaven jeho připojovací řetězec a SQL string.

Celý příklad je uveden v tiskovém formuláři Pohledavky_zpetne_ke_dni_SQL.mda umístěném v adresáři SDK\tiskove_vystupy\Vystupy_SQL instalačního CD Varia.

Popis parametrů funkce PripravDotazSQL:

Function PripravDotazSQL(Dotaz As String, CisloDat As Integer, DDefinic As Database, _
DDotazu As Database, Server As String, Optional retHodnotyParametru As String) As Boolean

Dotaz - jméno SQL Dotazu v tabulce definic (pole Dotaz)
CisloDat - číslo dat nad kterými se má dotaz spouštět
DDefinic - databáze, ve které je umístěna tabulka Dotazy_SQL
DDotazu - databáze, ve které se dotaz vytvoří/nastaví
Server - jméno SQL Serveru
retHodnotyParametru - seznam parametrů oddělených středníkem, kde n-tá hodnota je jméno parametru a n+1-ní hodnota je hodnota, která se má místo parametru dosadit. Je-li retHodnotyParametru = "" (řetězec nulové délky), potom je proměnná retHodnotyParametru nastavena volanou funkcí DejDotazSQL a uživatel bude dotázán na každý z parametrů uložených v definici dotazů. Proměnná retHodnotyParametru nabývá potom hodnoty v tomto formátu: "JmenoParametru_1; HodnotaParametru_1;JmenoParametru_2; HodnotaParametru_2;........JmenoParametru_n; HodnotaParametru_n;

Díky tomu, že uživatel bude při absenci parametru retHodnotyParametru dotázán na hodnoty parametrů dotazu, můžeme předělat volání funkce takto:

*** ZAČÁTEK ***
Sub SetZdroj_3()
    Dim Parametry As String
    Dim PoleParametru() As String

'Ve formuláři potřebuji zobrazit hodnotu proměnné Datum, ale její hodnotu
'načtu z parametrů, které mi vrátí volaná funkce PripravDotazSQL
    msql.PripravDotazSQL "Sestava Pohledavky zpetne ke dni_SQL", _
      app.AktualniData, CodeDb, CodeDb, firmy.Item(0).server, Parametry
    PoleParametru = Split(Parametry, ";")
'znám pořadí, počet a výstupní formát parametrů
    Datum = CDate(Mid$(PoleParametru(1), 8, 2) & "." & _
        Mid$(PoleParametru(1), 6, 2) & "." & _
        Mid$(PoleParametru(1), 2, 4))
End Sub
*** KONEC ***

Nebudeme-li potřebovat v TF pracovat s hodnotami parametrů, můžeme nakonec předělat volání funkce takto:

*** ZAČÁTEK ***
Sub SetZdroj_2()
'Ve formuláři nepotřebuji zobrazit hodnotu proměnné Datum
    msql.PripravDotazSQL "Sestava Pohledavky zpetne ke dni_SQL", _
      app.AktualniData, CodeDb, CodeDb, firmy.Item(0).server
End Sub
*** KONEC ***

Pokud není v TF nastaven zdroj, nastavte jej buď v návrhovém zobrazení nebo kódem na hodnotu uvedenou v poli Dotaz tabulky Dotazy_SQL.


2) Sestavy, Grafy a SQL Server:

  Optimalizace sestav a grafů je velmi jednoduchá. Spočívá pouze v definici SQL dotazů v tabulkách Sestavy nebo Grafy. O zbytek se postará Vario. Ve zmíněných tabulkách přibyla nová pole ne nepodobná polím v tabulce Dotazy_SQL.

Nová pole:

Pole Typ MSSQL Typ MS Access Popis pole
SQL_Serveru text memo definice dotazu (SQL string) v syntaxi Transact SQL
Parametry text memo deklarace parametrů ve formátu: 
parametr;datovy_typ_parametru_VBA;......  (např. parCisloDokladu;string;parCelkem;currency)
povolené datové typy jsou: Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, String, Nahrada.
Cilova_databaze varchar(128) text(128) SQL databáze, ve které se bude předávací dotaz spouštět.
ODBC Timeout int long       ODBC Timeout dotazu v sekundách (výchozí 600) platný i pro běžnou (ne SQL) sestavu spuštěnou nad SQL databází
Pouze_SQL YesNo Ano/Ne Sestava nebo graf lze otevřít pouze nad SQL serverem (není  alternativa pro data na fileserveru)


Všechna tato pole jsou přístupná prostřednictvím dialogu Možností výstupu. Do pole SQL_serveru zapíšete znění dotazu v syntaxi Transact SQL, do pole parametry zapíšete parametry stejným způsobem, jako v případě Tiskového formuláře a do pole Cilova_databaze zapíšete jméno databáze, ve které se bude dotaz spouštět (např. Data, Dmaj, .....). Je-li hodnota pole Pouze_SQL rovna (-1), bude při spuštění sestavy nad fileserverovými daty zobrazena hláška informující o tom, že sestava je určena pouze pro SQL server a sestava nebude zobrazena.

Jako příklad uvádím sestavy 'Firmy podle kategorie - SQL' a 'Seznam uživatelů serveru SQL'. Obě sestavy lze naimportovat z importního souboru Sestavy_SQL.mdb umístěného v adresáři SDK\tiskove_vystupy\Vystupy_SQL instalačního CD Varia.


Pro sestavu Firmy podle kategorie - SQL jsou nastaveny následující hodnoty:

SQL_Serveru SELECT * FROM Firmy
WHERE Kniha = TiskKniha("Adresář")
and (Kategorie like '%' + [Zadejte kategorii:] + ';%'
 or Kategorie like [Zadejte kategorii:] + ';%')
Parametry [Zadejte kategorii:];string
Cilova_databaze Data
ODBC_Timeout 600
Pouze_SQL -1 (zaškrtnuto)


Uživatel bude při otevření sestavy požádán o zadání kategorie.


Pro sestavu Seznam uživatelů SQL jsou nastaveny následující hodnoty:

SQL_Serveru select * from syslogins order by loginname
Parametry
Cilova_databaze master\
ODBC_Timeout 600
Pouze_SQL -1 (zaškrtnuto)


Sestava nemá žádné parametry a cílová databáze bude nezávislá na číslu dat aktuální firmy. \ (zpětné lomítko) za jménem databáze říká Variu, aby nepřipojovalo číslo databáze za její jméno).


3) Společné vlastnosti

a) Zadám-li za jméno cílové databáze znak \ (zpětné lomítko), nebude za jméno databáze doplněno číslo firmy. Zadám-li do pole Cilova_databaze hodnotu 'Firmy\', bude dotaz spuštěn nad databází Firmy. Zadám-li hodnotu 'Data', bude dotaz spuštěn nad databází DataXXXX, kde XXXX je číslo firmy.

b) Zadám-li jako kritérium dotazu (např. podmínka: WHERE Pole = Kriterium) jednu z funkcí:

TiskDatabazeSQL (JmenoDB As String, Optional Archiv As Byte)
TiskAgenda ()
TiskKniha (Optional Agenda As String)
TiskRok (Optional Agenda As String)
TiskObdobi (Optional Agenda As String)
AktualniAgenda ()
AktualniKniha (Optional Agenda As String, Optional Opravneni As Byte)
AktualniRok (Optional Agenda As String)
AktualniObdobi (Optional Agenda As String)
AktualniData ()
HospodarskyRok (Datum As Date)
HospodarskyRokZacatek (Rok As Integer)
HospodarskyRokKonec (Rok As Integer)
HospodarskyRokObdobi (Datum As Date)

budou tyto vyhodnoceny standardním způsobem a místo nich bude do SQL výrazu dosazena jejich návratová hodnota uvozená v závislosti na datovém typu návratové hodnoty znakem ' (apostrof) v případě datového typu String. Např. funkce TiskKniha() vrátí hodnotu 'Adresář'. To znamená, že není potřeba se starat uvození řetězcových proměnných v SQL výrazu.

Funkce TiskDatabazeSQL vrátí jméno databáze včetně číselného suffixu závislého na parametru Archiv funkce.

Archiv= 0, zajímá-li mě číslo aktuální databáze právě otevřené firmy
         = 1, zajímá-li mě číslo pracovní databáze právě otevřené firmy
         = 2, zajímá-li mě číslo archivní databáze právě otevřené firmy

Například volání TiskDatabazeSQL("Data", 2) vrátí řetězec Data5001, TiskDatabazeSQL("Data", 1) vrátí řetězec Data0001 a TiskDatabazeSQL("Data", 0) vrátí řetězec Data0001 nebo Data5001 v závislosti na tom, zda mám otevřená pracovní nebo archivní data. Jako jméno databáze lze použít i jména zákaznických databází. Funkci lze využít zejména v okamžiku, kdy potřebuji načíst data jak z pracovních tak z archivních dat.

Mám-li následující definici sestavy(nebo TF) a číslo aktuální firmy je 1

SQL_Serveru select * from doklady 
union
select * from TiskDatabazeSQL("Data", 2).dbo.doklady
Parametry
Cilova_databaze Data
ODBC_Timeout 600
Pouze_SQL -1 (zaškrtnuto)


bude nad databází Data0001 spuštěn následující dotaz:

select * from doklady
union
select * from Data5001.dbo.doklady

Cílem dotazu je vrátit všechny doklady z pracovních i archivních dat.

c) Deklarace datového typu parametru dotazu je využívána pouze v případě Sestav a Grafů a v případě, že v tiskovém formuláři nezadáme volané funkci PripravDotazSQL() hodnotu parametru retHodnotyParametru (nebo retHodnotyParametru = "" - prázdný řetězec). Uživatel je dotázán na každý z parametrů a hodnota jeho odpovědi je zkonvertována následovně:

V případě typů Byte, Boolean, Integer, Long, Single, Double, Currency a Decimal bude v hodnotě, na kterou byl uživatel dotázán, nahrazen znak '.' (tečka) znakem ',' (čárka). Do SQL stringu pak bude vložena tato modifikovaná hodnota. Zadá-li například uživatel číslo 5.5 , bude do definice SQL vložena namísto deklarovaného parametru hodnota 5,5 .

V případě typu Date bude zadaná hodnota zkonvertována na univerzální formát data ve formátu 'YYYYMMDD'. Zadá-li uživatel hodnotu 21/2/2002, bude do SQL stringu namísto parametru vložena hodnota '20020221' včetně znaku ' (apostrof) na konci a na začátku.

V případě typu String, bude parametr nahrazen řetězcem 'ŘETĚZEC' včetně znaku ' (apostrof) na konci a na začátku.

V případě pseudotypu Nahrada, bude parametr nahrazen řetězcem ŘETĚZEC bez znaku ' (apostrof).