Posted By: Jovo () on 'CZdatabases'
Title:     Jovo 5
Date:      Mon Dec 11 20:45:23 2000

Z Jovova zapisniku - 5:"Spousteni zkonstruovanych selectu"
==========================================================

  Sem tam se stava, ze potrebujeme neco spocitat a select nemame
primo v kodu, ale zkonstruovany. Typicky ho dostaneme v nejakem 
retezci. 
  Selectem pro pocitani se zde mysli select, co poskytuje jednu 
hodnotu, napriklad SELECT SUM(... , SELECT MAX(....  a podobne.
  Jak ho potom spustit, aby to nezhavarovalo a dostali jsme 
vysledek do promenne (zde ji nazveme AAA) ? 

1] udelame si funkci pro vypocet.

     // funkce vypoctu
     function spust_select(retezec str_select,
                           retezec hlaska_chyby,   
                           retezec hlaska_chyby_nenalezeno,   
                           retezec pro_nenalezeno)  {
       // deklarace lokalnich promennych
       DECLARE AAA            ???????                   // (X0)
       DECLARE status_operace Integer

       LET status_operace = 0
       LET AAA            = NULL
       PREPARE prep_cursor FROM str_select
           <osetreni pripadne SQL chyby>                // (X1)
       DECLARE c_cursor CURSOR FOR prep_cursor
           <osetreni pripadne SQL chyby>                
         OPEN    c_cursor                               // (X5)
           <osetreni pripadne SQL chyby>
         FETCH   c_cursor INTO AAA
           <osetreni pripadne SQL chyby>
           IF nic_nenalezl THEN   
             LET status_operace = NENALEZENO            // (X2)
             LET AAA = pro_nenalezeno                   // (X3)
             vyhozeni hlasky s textem "CHYBA " + 
               hlaska_chyby_nenalezeno + "pri vypoctu"
           END IF
         CLOSE   c_cursor
           <osetreni pripadne SQL chyby>
       FREE    c_cursor
           <osetreni pripadne SQL chyby>
       RETURN AAA, status_operace
     // konec bloku vypoctu
     }
  Pozn.:
   - <osetreni pripadne SQL chyby> =
       IF byla chyba THEN 
         vyhozeni hlasky s textem "CHYBA " + hlaska_chyby + 
         "pri " + <kde se to stalo (Zdeclare/fetch/...)>
         LET status_operace = kod SQL chyby
         RETURN AAA, status_operace
       END IF
     Tedy kdyz to vyhuci provedeme hned navrat (zkracene programovani -
     nemusime psat spoustu IF THEN ELSE END IF - zalezi na stylu 
     programovani, popripade firemni/skolni metodice).
       "byla chyba" jde poznat u kazde databaze a prekladace jinak. 
     Muze se pouzit standardni promenna SQLCA.SQLCODE, nektere databaze
     deklaruji i promennou STATUS, nebo SQL_ERRNO, zalezi na konkretni 
     situaci.
   - v miste oznacenem (X0) si doplnte typ, ktery vam vyhovuje (INT,
     DECIMAL,STRING,...). Pokud ma vas prekladac k dispozici makra, pak
     je vhodne celou funkci "spust_select" definovat jako makro a pro 
     (X0) definovat i typ. Kdo nema makra - musi odladit, a pro kazdy 
     typ vytvorit samostatnou funkci (COPY/PASTE)
   - Vsimnete si, ze promenna "pro_nenalezeno" je definovana jako 
     retezec. Tento postup predpoklada automatickou konverzi mezi 
     typem retezec a int.
   - v (X2) se prirazuje konstanta NENALEZENO. Ta muze byt pro kazdy 
     system jina (napr. pro INFORMIX NENALEZENO=100)

  Ziskame vysledek (AAA) a jestli nenastala chyba (status_operace). 
Pak uz si muzeme vyskakovat :-)  

Proc to ale obcas pada :
  Pokud nastane chyba, tak podle mych zkusenosti to vyhuci na 95% v 
miste (X1) - to znamena, ze mame spatne zkonstruovany retezec selectu. 
  Nejcastejsi chyby jsou v tomto pripade :
    - spatne napsany select :-)  Zkontrolujte si preklepy, seznam 
      tabulek a jestli je mezi podminkami where "AND". A co zavorky, 
      mate je vsechny uzavrene ? Ne, tak pro jistotu vsechno 
      ozavorkujte - to byste se divili, jak chyby rychle mizi :-)
        Pro uzivatele editoru vi - protejsek zavorek se hleda pres 
      prikaz "%", u editoru joe CTRL+G, u Borland-style editoru
      CTRL+Q+]
        Zde hodne napovi vraceny kod SQL chyby (vetsinou chyba syntaxe, 
      spatne jmeno sloupce/tabulky, tabulka nen9 v from sekci,...)

    - je li retezec skladany z vice casti 
        LET str_select = str_sel + str_from + str_where        (X4)
      a kdyz bude 
        str_sel   = "SELECT SUM(sloupec)"
        str_from  = "FROM tabulka"
        str_where = "WHERE podminka"
      a jste si NAPROSTO jisti, ze ten select je dobre, tak je chyba ve
      skladani, prikaz (X4) udela 
         str_select = "SELECT SUM(sloupec)FROM tabulkaWHERE podminka"
                                          ^           ^ 
                                      a pak tady chybi mezery. 
      Pro jistotu tedy (X4) piste jako 
        LET str_select = str_sel + " " + str_from + " " + str_where

    - v retezci chybi klicova slova, velmi casto se stane, ze programator 
      napise 
        LET str_from  = "tabulka"

  V 2% to vyhuci na miste oznacenem (X2). V podstate to neni chyba, jenom
to nic nenalezlo. Zde doporucuji pouzivani isnull (ne SELECT SUM(...), ale
SELECT isnull(SUM(...),0) - viz take dil 4 :"Pouziti isnull jako IF")
    Tak ve 2% pripadu to spadne v (X5). Mame spatne napsany select - viz 
PS na konci tohoto textu.
  To 1% si nechavam pro pripadne nestandardni situace (spadne nam spojeni, 
pad DB stroje, nekdo nam zamkne exclusivne tabulku, uz jsem zazil i 
vymaz -dropnuti- databaze uprostre prace :-)

2] jeste tu muze nastat situace, ze zkonstruovany select vybere vic 
   hodnot. Nekdo (ve velke vetsine pripadu my samotni) proste napsal 
   select sice formalne dobre, ale dela nam pitomosti. Anebo zkousi, jestli 
   jsou v DB korektni data (napriklad vhodne zvolenym GROUP BY).
     Reseni je v prepsani funkce tak, aby vzala za vysledek treba 
   jen prvni (nebo jen posledni, druhy, treti .... jak si to kdo napise) 
   hodnotu a vracela i pocet zaznamu. Pak to muze vypadat i takto :

   Zde je jednodussi varianta - vraci posledni nalezenou hodnotu. 
   Vyhoda je, ze je to jednoduche, ale neefektivni.

     function spust_select2(retezec str_select,
                           retezec hlaska_chyby,   
                           retezec hlaska_chyby_nenalezeno,   
                           retezec hlaska_chyby_nalezeno_vice,   
                           retezec pro_enalezeno_vice,   
                           retezec pro_nenalezeno)  {
       // deklarace lokalnich promennych
       DECLARE AAA            ???????                  
       DECLARE status_operace Integer
       DECLARE pocet_radku    Integer

       // (Y0)

       LET status_operace = 0
       LET AAA            = NULL
       LET pocet_radku    = 0
       PREPARE prep_cursor FROM str_select
           <osetreni pripadne SQL chyby>               
       DECLARE c_cursor CURSOR FOR prep_cursor
           <osetreni pripadne SQL chyby>                
       // (Y1)
         FOREACH c_cursor INTO AAA
           LET pocet_radku    = pocet_radku + 1
         END FOREACH 
       // (Y2)
   
         IF nebyla SQL chyba THEN
           IF pocet_radku = 0 THEN
             LET status_operace = NENALEZENO           
             LET AAA = pro_nenalezeno                  
             vyhozeni hlasky s textem "CHYBA " + 
               hlaska_chyby_nenalezeno + "pri vypoctu"
           ELSE
             IF pocet_radku = 0 THEN
               LET status_operace = NALEZENO_VICE
               LET AAA = pro_nalezeno_vice                
               vyhozeni hlasky s textem "CHYBA " + 
                 hlaska_chyby_nalezeno_vice + "pri vypoctu"
             END IF
           END IF 
         ELSE
           <osetreni pripadne SQL chyby>
         END IF

       RETURN AAA, pocet_radku, status_operace
     // konec bloku vypoctu
     }
  Pozn.: 
    - nevyhoda : kdyz select vybira postupne milion zaznamu, tak se 
      pekne nacekame. Pokud chceme OPRAVDU posledni hodnotu, pak se s tim
      bud musime smirit, nebo pouzit jiny mechanismus : 
        - zjistit kolik toho vybere (viz nize)
          - kdyz jen jeden zaznam, pak to rozjet - vse OK
          - kdyz vybere vic zaznamu je nekolik reseni :
            - bud zatnout zuby a pretrpet cely cyklus foreach
            - pouzit nestandardni mechanismus - nektere DB stroje poskytuji
              promennou, ve ktere je rowid naposled prochazeneho radku, 
              takze muzeme si dat COUNT(*), a pak vybrat jen radek s 
              odpovidajicim rowid. Nelze pouzit vzdy (treba ne na SUM, ...)
              a ne na vsech strojich.
      Zjistete si, o kolik je COUNT(*) rychlejsi, nez projizdeni kurzorem 
      pres celou tabulku !!! Budete nemile prekvapeni.
    - NALEZENO_VICE je zase nejaka vhodne definovana konstanta :-) 
      U INFORMIXu lze pouzit NENALEZENO+1
    - kdyby to melo vybirat jen prvni hodnotu, pak musime prepsat:
        -  (Y0)      : 
           DECLARE predesle_AAA            ???????                  
        -  (Y1)-(Y2) : 
         FOREACH c_cursor INTO predesle_AAA
           LET pocet_radku    = pocet_radku + 1
           IF  pocet_radku = 1 THEN 
             LET AAA = predesle_AAA
           ELSE 
             // (Y3)
             EXIT FOREACH // ukonci cyklus, neco jako prikaz break v C/C++
           END IF
         END FOREACH 
      To ma vyhodu, ze se vypocet hned zarazi a nevime kolik radku to 
    vybere. Pokud pocet radku nepotrebujeme, je tento zpusob efektivnejsi,
    Pokud ho ale chceme, lze pouzit pekny trik :
      V retezci str_select nahradime cast oznacenou sipkami
         SELECT ... FROM ....                             
                ^^^  za "COUNT(*)" 
    a v (Y3) provedeme jeste jeden vypocet. Proc jen nezavolat tutez funkci
    rekurzivne ? Nektere prekladace/implementace maji potize s kurzory 
    (zavolame proceduru jednou, ta si nadeklaruje kurzor, neco do nej 
    zacne strkat, pak zavola sama sebe, zjisti, ze kurzor je nadeklarovany,
    tak do nej zacne rovnou neco strkat a program spadne.) 
      Velmi casto se stava v nasledujici situaci, 

           funkce1                        -->  funkce2
             deklarace kurzoru1 (Y4)     /       deklarace kurzoru2
               foreach          (Y5)    /           prace
                  Volani jine funkce ---         zavreni kurzoru2 (Y6)
                                    <--------- navrat
               end foreach

    kdyz volana sub-funkce (funkce2) zavre kurzor (Y6), tak volajici 
    funkce (funkce1) zhavaruje v miste (Y5) s hlaskou, ze se pokusila 
    udelat fetch na neotevreny kurzor. Na miste (Y6) se nezavre jen 
    kurzor2, ale i kurzor1 !  Na pytel, ze ? A navic, kdo ma na 
    takovou nelogicnost prijit :-) 
      Odstraneni teto chyby je jednoduche : v miste (Y4) misto 
        DECLARE kurzor1 CURSOR ....
      dejte 
        DECLARE kurzor1 CURSOR ... WITH HOLD
   Tak to alespon funguje na databazi INFORMIX v7 :-)

Tak jsem se v tom trochu zahrabal. Kdo nevi kudy kam, at si prohledne
jen funkci "spust_select2", to je celkem pouzitelny mustr.

Vas Jovo.
PS:
    - pro oziveni - spousteni prikazu, ktere jen neco delaji a zaroven
      nemaji vystup : 
       PREPARE prep_cursor FROM str_select
           <osetreni pripadne SQL chyby>                
       EXECUTE prep_cursor
           <osetreni pripadne SQL chyby>                

       str_select muze byt napriklad 
           "DELETE FROM tab WHERE podminka", nebo "UPDATE .....", ...
     Velmi casto se tento postup pouziva pri zadavani kterii uzivatelem,
     Treba na formulari uzivatel rekne, ze maji byt do tabulky T1 zarazena
     data z tabulky T2, ktere maji sloupec SSSS treba vetsi nez omezeni, 
     jinak aby tam pridal vsechno
      LET str_select =
        "INSERT INTO T2" + 
          "SELECT <seznam sloupcu z T1>"    +
          "FROM T1 "     +
          "WHERE 1=1 "
      IF uzivatel zadal omezeni THEN
        LET str_select = str_select + "AND SSSS >" + omezeni
      END IF
      PREPARE prep_cursor FROM str_select
      EXECUTE prep_cursor
                                                            
     

Search the boards