Visual FoxPro & Věštec
přístup na data Oracle a specifické problémy s tím spojené...
Připojení na Oracle je přizpůsobeno na ORCL=ORCL1 a uživatele test s heslem test.
Pokud si budete příklady zkoušet nezapomeňte opravit jak přihlašovací řetězce v ukázkách tak i ve spojeních MS_ORA a ORA_ORA ve VFP databázi ora.dbc.
Soubor Start.sql obsahuje vytvoření tabulek, indexů, pohledů, cizích klíčů, triggerů, sekvence a balíčku s procedurami.
Části tohoto souboru se můžou nacházet v tomto textu jako ukázky.
- ODBC
-
- Microsoft ODBC driver for Oracle (2.0)
Microsoft ODBC for Oracle (2.5)
- Za určitých podmínek může být otevírání spojení pomalejší.
Byť je to v MSDN popsáno i s návodem na nápravu (Q289280 - týká se MDAC 2.5/2.6), bohužel ne vždy je to funkční.
- Oracle ODBC driver
Oracle73
- Spojovací řetězec; Rozdíl mezi ovladači MS Oracle : CONNECTSTRING/SERVER
ORACLE: DBQ
- OLE-DB/ADO
- OLE-DB provider
SPT má jednu nevýhodu, nelze provést přemapování položek a počet desetinných míst se řídí dle nastavení SET DECIMAL TO.
RV zase neumožňují jednoduše měnit výběrová kritéria.
CA umožňují oboje, ale až ve VFP 9.0 lze kombinovat mapovaní položek a občerstvení věty pomocí metody RecordRefresh() (ve VFP 8.0 je vyvolána chyba Cx0000005 při kombinaci mapování položek a volání funkce REFRESH()).
Výchozí instalace Oracle Net Clienta nenastavuje desetiný oddělovač pro numerické hodnoty.
Sice lze pro každé spojení nastavit tento oddělovač
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.'
, ale pro aplikace umožňující mít data na více platformách to je poněkud nevhodné.
Vychozí nastavení Oracle Net Clienta, je uloženo v registry a lze ho změnit (viz. soubor ora_num.reg).
Pokud tento oddělovač není definován, pak ORACLE odmítne vykonat SQL příkaz a vrací chybu číslo: ORA-01722.
- Number (SPT)
- MS drivery nemají s čtením položek typu Number problémy, ale Oracle drivery ano, dochází totiž ke skrývání desetinných hodnot.
Browse/grid sice zobrazí číslo i se desetinnou hodnotou, ale při jakémkoliv čtení této hodnoty, je desetiná hodnota odřezána.
Ovšem pokud je v SELECT SQL v seznamu položek výraz (pole*hodnota) pak dochází k automatické konverzi na typ FLOAT a následné ztrátě desetinných míst.
U Oracle ODBC driveru a SPT nebo RV se ztrací desetinná místa.
- Float (SPT)
- Vždy přetypovat na double, když se použije float, dochází ke ztrátě desetinných míst (MS/ORA).
A použít jen tam kde je to opravdu nutné, na finanční sloupce by tento typ nikdy neměl být použit.
- Numerické obecně
- Při matematických operací numerických sloupců s desetinnými místy by se výsledný sloupec měl konvertovat na typ double s přískušným počtem desetinných míst.
Jinak se opět ztrácejí desetinná místa.
- General - LongRaw/Blob
- Typ sloupce Blob zavedla až 8.x verze, nižší verze znají pouze LongRaw.
Vzhledem k tomu , že Oracle Net Clientu pro 8.x, přesněji OCI vrstva, nemá plnou podporu typu (x)BLOB a tak s ní nelze z VFP pracovat přímo.
Proto se musí použít typ LongRaw.
Tento typ je však zastaralý a hodnota je navíc uložena ve stejné oblasti jako hodnoty ostatní sloupců (BLOB je ukládán mimo data tabulky).
Tato "vlastnost" způsobuje, že při větších objemech dat klesá výkon s jakoukoliv operací nad tabulkou se sloupcem LongRaw - je to znát již při 10000 záznamech v tabulce.
Kopie věty pomocí INSERT ... SELECT ... není možná.
- Memo - Varchar2(4000)
- Oracle má typ long, ale vzhledem k tomu, že s tímto typem byly nějaké problémy může se použít typ Varchar2 o délce 4000 znaků.
Když už použijete typ Long a ORA drivery, pak neposilejte Oracle hodnotu nulové délky ale minimálně jednu mezeru, jinak se ozve chyba z ODBC driveru "No data at execution values pending" s následnoun Cx0000005 při jakékoliv akci.
- Logical - number(1)
- Oracle nezná typ logical/boolean, proto se emuluje pomocí typu number(1).
VFP s tím nemá problém, protože třída checkbox podporuje i numerické hodnoty, včetně .NULL. hodnoty.
- Date/DateTime
- Oracle má pouze jeden datumový typ - DATE, lze do něj uložit jak datum, tak i datum a čas.
Tento typ by měl mít vždy povolenou .NULL. hodnotu ne proto že Oracle nezná prázdný datum, ale protože ODBC drivery převádí prázdný datum na .NULL. hodnotu a tudíž by se nepodařilo změny uložit nebo mají s převodem problém (OLE DB provider for Oracle by MS).
Pokud vkládáte datumovou hodnotu přímo do SQL příkazu, musíte repspektovat nastavení serveru, když však použijete bindovaný parameter nebo escape sekvenci (ODBC), pak toto omezení obejdete.
*** Date/Datetime
lihdbcMS=SQLSTRINGCONNECT("driver={microsoft odbc for oracle};"+;
" connectstring=orcl1; uid=test; pwd=test;")
lcXX005=REPL("X",255)
ldXX006=DATE()
=SQLEXE(lihdbcMS,"INSERT INTO TEST (XX001,XX002,XX003,XX004,XX005,XX006)"+;
" VALUES (1.888,1.888,3.444,3.333,?lcXX005,?ldXX006)")
=SQLEXE(lihdbcMS,"INSERT INTO TEST (XX001,XX002,XX003,XX004,XX005,XX006)"+;
" VALUES (1.888,1.888,3.444,3.333,?lcXX005,{d '2004-01-30'})")
=SQLEXE(lihdbcMS,"INSERT INTO TEST (XX001,XX002,XX003,XX004,XX005,XX006)"+;
" VALUES "+;
" (1.888,1.888,3.444,3.333,?lcXX005,{ts '2004-01-30 11:11:11'})")
?SQLDISCONNECT(lihdbcMS)
- Řetězce
- Řetězec nulové délky se automaticky konvertuje na .NULL. hodnotu - týká se to SPT či ADOCommand.
-- řetězcové hodnoty se uvozují vždy pomocí znaku '
SELECT * FROM TEST WHERE RIGHT(XX009,1)='A'
- NULL hodnoty
- Oracle ODBC driver; tento driver trpí jednou závažnou chybou - invertuje příznak povolení .NULL. hodnoty na sloupci.
Ostatní ODBC drivery (MS 2.0,2.5 Oracle73) tuto chybu nemají.
Oracle outer joiny zná velmi dlouho, ale až od verze 9i podporuje zápis dle normy ANSI SQL 92.
V předchozích verzí se používá zápis:
| ANSI | ORACLE <=8.x |
| LEFT JOIN | tab1.field1=tab2.field1(+) |
| RIGHT JOIN | tab1.field1(+)=tab2.field1 |
| FULL OUTER JOIN | tab1.field1(+)=tab2.field1(+) |
Dále jsou problémy se složenými výrazy v podmínce outer joinu kdy ORACLE to nezvládne (8.x).
Dá se to obejít pomocí poddotazu v klauzuli FROM.
*** Výrazy v outer join
lihdbcORA=SQLSTRINGCONNECT("driver={oracle odbc driver};dbq=ORCL1; uid=test; pwd=test;")
* chyba
?SQLEXEC(lihdbcORA,"select aa.xx000,aa.xx008,aa.xx009,bb.xx008,bb.xx009"+;
" from test aa, test bb where 'A'||aa.xx009=substr(bb.xx008,1,1)||bb.xx009(+)")
* řešení pomocí pododtazu v klauzuli FROM
?SQLEXEC(lihdbcORA,"select aa.xx000,aa.xx008,aa.xx009,bb.xx008,bb.xx009"+;
" from test aa, (select xx008,xx009 from test where substr(xx008,1,1)='A')"+;
" bb where aa.xx009=bb.xx009(+)")
* řešení pomocí view a SELECT SQL mezi tabulkou a dotazem
* CREATE VIEW .... AS select xx008,xx009 from test where substr(xx008,1,1)='A'
?SQLEXEC(lihdbcORA,"select aa.xx000,aa.xx008,aa.xx009,bb.xx008,bb.xx009 "+;
" from test aa, any_view bb where aa.xx009=bb.xx009(+)")
=SQLDISCONNECT(lihdbcORA)
Nechte si zajít chuť, ale...
Oracle sice nemá datový typ AutoIcrement, ale má takzvané sekvence.
Sekvence je číselná řada s několika atributy: maximální hodnota, minimální hodnota, počáteční hodnota, přírůstek a atd.
Operace s ní spojené (vrácení nové hodnoty) jsou zcela vyjmuty z transakcí a nelze tedy sekvence použít pro generování řad účetních dokladů nebo dokladů ve skladovém hospodářství protože by mohla vzniknout přerušená řada.
To, že jí lze změnit jakýkoliv atribut je samozřejmé.
Tím, že sekvence není vázána na tabulku je výhoda, protože pak lze použít jednu sekvenci pro více tabulek.
Lze ji volat kdykoliv - může být součástí SQL příkazu nebo uložené procedury (triggeru).
-- Vytvoření sekvence
CREATE SEQUENCE test_id INCREMENT BY 1 START WITH -2000000000 MINVALUE -2000000000
/
-- Použití v příkazu INSERT
INSERT INTO TEST (XX000,XX001,XX002,XX003,XX004)
VALUES
(test_id.nextval,2.521,2.521,3.512,3.512)
/
COMMIT
/
-- Vytvoření triggeru pro nastavení
create or replace trigger test_bfi before insert on test
for each row
DECLARE
nKey NUMBER;
begin
-- Pokud je potřeba generovat id jen pokud není definováno
IF :NEW.XX000 IS NULL THEN
SELECT test_id.NEXTVAL INTO nKey FROM dual;
:NEW.XX000 := nKey;
END IF;
end;
/
-- automatické vložení hodnoty do položky XX000 pomocí triggeru
INSERT INTO TEST (XX001,XX002,XX003,XX004,XX005)
VALUES
(1.888,1.888,3.444,3.333,'aaaaaaaaaaaaaaa-ssssssssss-ssssssssss')
/
COMMIT
/
Také je to pěkně rozebráno v článku
Tipy a triky pro Oracle I. – jak na automatické přidělování ID? a jeho diskuzi.
Oracle v tomto případě nemá rád klíčové slovo AS:
table AS alias
Oracle neumí použít znaménko + na sečtení řetězců, ale má vlastní náhradu: ||.
V případě nutnosti univerzálního SQL příkazu se dá použít escape sekvence:
-- použití znaků ||
SELECT XX008||XX009 AS XX008_9 FROM TEST
-- funkce CONCAT
SELECT {fn CONCAT(XX008||XX009)} AS XX008_9 FROM TEST
Rozhodně se tomu vyhněte, neboť výkon SQL příkazů ve většině případů klesá o několik řádů.
SELECT * FROM TEST WHERE RIGHT(XX009,1)='A'
Na Oracle se indexy definují jako seznam sloupců, nelze použít funkce.
Na druhou stranu je téměř jedno jakého typu jsou sloupce - nelze indexovat sloupce typu long, longraw, blob atd.
Unikátní index na Oracle odpovídá kandidátnímu indexu ve VFP.
-- unikátní
CREATE UNIQUE INDEX TESTI01 ON TEST (XX008)
-- běžný
CREATE INDEX TESTI01 ON TEST (XX009)
Krom problému s typem BLOB, nic dalšího.
*** Kopie věty - longraw (nefunguje)
lihdbcMS=SQLSTRINGCONNECT("driver={microsoft odbc for oracle};"+;
"connectstring=ORCL1; uid=test; pwd=test;")
?SQLEXE(lihdbcMS,"INSERT INTO TEST2 (XX000,XX010)"+;
" SELECT 2,XX010 FROM TEST2 WHERE XX000=-2000000000")
AERROR(xxa)
?xxa(2)
=SQLDISCONNECT(lihdbcMS)
UPDATE-SQL příkaz nijak neliší od mutace v MS SQL pokud ovšem nechceme provést opravu sloupce na základě výběru z jiné tabulky.
-- MS SQL
-- UPDATE TEST AA SET AA.XX001=TEST.XX001 WHERE TEST.XX000=1 AND AA.XX000=5
-- GO
-- ORACLE
UPDATE TEST AA set XX001=(select XX001 from TEST where TEST.XX000=1) WHERE XX000=5
/
COMMIT
/
Oracle zná pohledy.
Rozdíl mezi pohledy Oracle a MS SQL je ten že u definice pohledu v Oracle se musí definovat seznam položek aby pohled umožňoval zpětnou aktualizaci dat.
V případě, že potřebujete aby nešlo data aktualizovat pak stačí přidat do definice pohledu: WITH READ ONLY.
Složité dotazy - spojení mezi více tabulkami, vazby 1-n - mohou způsobit že nepůjdou aktualizovat data.
-- Aktualizovatelný pohled
CREATE OR REPLACE VIEW TESTX (XX000,
XX001,XX002,XX003,XX004,XX005,XX006,XX007,XX010) AS
SELECT TEST.XX000,TEST.XX001,TEST.XX002,TEST.XX003,TEST.XX004,TEST.XX005,
TEST.XX006,TEST.XX007,TEST2.XX010
FROM TEST, TEST2
WHERE TEST.XX000 = TEST2.XX000(+)
/
-- Pohled jen ke čtení
CREATE OR REPLACE VIEW TESTXRO (XX000,
XX001,XX002,XX003,XX004,XX005,XX006,XX007,XX010) AS
SELECT TEST.XX000,TEST.XX001,TEST.XX002,TEST.XX003,TEST.XX004,TEST.XX005,
TEST.XX006,TEST.XX007,TEST2.XX010
FROM TEST, TEST2
WHERE TEST.XX000 = TEST2.XX000(+)
WITH READ ONLY
/
Článek
Tipy a triky pro Oracle V. – změna dat v pohledu? popisuje aktualizaci dat v readonly pohledu pomocí triggerů nad pohledem.
Uložených procedur a funkcí se dá využít k mnoha věcem:
Přenesení části aplikační logiky do DB
Odříznutí klientských aplikací od tabulek a pohledů (bezpečnost)
Možnost volat je z jobu (automatické činnosti)
...
Mohou být samostatné nebo součástí balíčků.
Do procedur a funkcí lze předat základní typy parametrů jako čísla, řetězce a datumy.
Jako návratové hodnoty lze použít čísla, řetězce, datumy, tabulky a kurzory.
Typy "tabulky" a "kurzory" lze vrátit pomocí escapovací funkce resultset() a typ "kurzor" pouze přes OLE-DB.
Funkce se dají volat pouze přes OLE-DB, protože VFP považuje znak "?" za indikaci parametru, přesněji VFP považuje znaky následující za "?" za název VFP proměnné a snaží na její místo vložit hodnotu.
***************************************
** Vrátí počet záznamů
***************************************
lihdbcMS=SQLSTRINGCONNECT("driver={microsoft odbc for oracle};"+;
"connectstring=orcl1; uid=test; pwd=test;")
lcExec="{call FoxPackage.GetRecordsCount( 1,{resultset 1, piXX000 })}" && MS
?SQLEXEC(lihdbcMS,lcExec)
=SQLDISCONNECT(lihdbcMS)
***************************************
** Vrátí result set
***************************************
lihdbcMS=SQLSTRINGCONNECT("driver={microsoft odbc for oracle};"+;
"connectstring=orcl1; uid=test; pwd=test;")
lcExec="{call FoxPackage.GetResult( 1,{resultset 7, piXX000 , pnXX001})}" && MS
?SQLEXEC(lihdbcMS,lcExec)
=SQLDISCONNECT(lihdbcMS)
***************************************
** První 5 je maximální počet vět, druhá 5 je pojistka pro resultset
***************************************
lihdbcMS=SQLSTRINGCONNECT("driver={microsoft odbc for oracle};"+;
"connectstring=orcl1; uid=test; pwd=test;")
lcExec="{call FoxPackage.GetResultX( 1, 5, {resultset 5, piXX000 , pnXX001})}" && MS
?SQLEXEC(lihdbcMS,lcExec)
=SQLDISCONNECT(lihdbcMS)
***************************************
** Oprava hodnot v tabulce
***************************************
lihdbcMS=SQLSTRINGCONNECT("driver={microsoft odbc for oracle};"+;
"connectstring=orcl1; uid=test; pwd=test;")
lcExec="{call FoxPackage.UpdateTable( 1, 'G')}" && MS/ORA
?SQLEXEC(lihdbcMS,lcExec)
=SQLDISCONNECT(lihdbcMS)
***************************************
** Update Table
***************************************
#DEFINE adInteger 3
#DEFINE adChar 129
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#DEFINE adParamInputOutput 3
loADO=CREATEOBJECT("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loCmd=CREATEOBJECT("ADODB.Command")
loCmd.ActiveConnection=loADO
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 5
loCmd.Parameters.Append(loPar)
loPar = loCmd.CreateParameter(, adChar, adParamInput, 1)
loPar.Value = "F"
loCmd.Parameters.Append(loPar)
loCmd.CommandText="{call FoxPackage.UpdateTable( ?,?)}"
=loCMD.Execute()
***************************************
** Recalc
***************************************
#DEFINE adInteger 3
#DEFINE adChar 129
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#DEFINE adParamInputOutput 3
loADO=CREATEOBJECT("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loCmd=CREATEOBJECT("ADODB.Command")
loCmd.ActiveConnection=loADO
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 1
loCmd.Parameters.Append(loPar)
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 2
loCmd.Parameters.Append(loPar)
loPar = loCmd.CreateParameter("poiZZ002", adInteger, adParamOutput, 5)
loCmd.Parameters.Append(loPar)
loCmd.CommandText="{call FoxPackage.Recalc( ?,?,?)}"
=loCmd.Execute()
?loCmd.Parameters.Item(2).value
***************************************
** GetList
***************************************
#DEFINE adInteger 3
#DEFINE adChar 129
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#DEFINE adParamInputOutput 3
loADO=CREATEOBJECT("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loCmd=CREATEOBJECT("ADODB.Command")
loCmd.ActiveConnection=loADO
loCmd.CommandText="{call FoxPackage.GetList( {resultset 2, pnYY000 , pcYY001 , pcYY002})}"
loRS=loCmd.Execute()
?loRS.Fields.Item(0).Value
***************************************
** GetRecordsCount
***************************************
#DEFINE adInteger 3
#DEFINE adChar 129
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#DEFINE adParamInputOutput 3
loADO=CREATEOBJECT("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loCmd=CREATEOBJECT("ADODB.Command")
loCmd.ActiveConnection=loADO
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 2
loCmd.Parameters.Append(loPar)
loCmd.CommandText="{call FoxPackage.GetRecordsCount( 1,{resultset 1, piXX000 })}"
loRS=loCmd.Execute()
?loRS.Fields.Item(0).Value
***************************************
** GetResult
***************************************
#DEFINE adInteger 3
#DEFINE adChar 129
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#DEFINE adParamInputOutput 3
loADO=CREATEOBJECT("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loCmd=CREATEOBJECT("ADODB.Command")
loCmd.ActiveConnection=loADO
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 1
loCmd.Parameters.Append(loPar)
loCmd.CommandText="{call FoxPackage.GetResult( ?,{resultset 7, piXX000 , pnXX001})}"
loRS=loCmd.Execute()
?loRS.Fields.Item(0).Value
***************************************
** GetResultX
***************************************
#DEFINE adInteger 3
#DEFINE adChar 129
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#DEFINE adParamInputOutput 3
loADO=CREATEOBJECT("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loCmd=CREATEOBJECT("ADODB.Command")
loCmd.ActiveConnection=loADO
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 1
loCmd.Parameters.Append(loPar)
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 7
loCmd.Parameters.Append(loPar)
loCmd.CommandText="{call FoxPackage.GetResultX( ?,?,{resultset 7, piXX000 , pnXX001})}"
loRS=loCmd.Execute()
?loRS.Fields.Item(0).Value
***************************************
** GetResultY
***************************************
#DEFINE adInteger 3
#DEFINE adChar 129
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#DEFINE adParamInputOutput 3
loADO=CREATEOBJECT("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loCmd=CREATEOBJECT("ADODB.Command")
loCmd.ActiveConnection=loADO
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 2
loCmd.Parameters.Append(loPar)
loCmd.CommandText="{call FoxPackage.GetResultY( 1,{resultset 0, io_cursor})}"
loRS=loCmd.Execute()
loRS.MoveFirst()
DO WHILE !loRS.EOF
?loRS.Fields.Item(0).Value
loRS.MoveNext()
ENDDO
***************************************
** Func3
***************************************
#DEFINE adInteger 3
#DEFINE adChar 129
#DEFINE adParamInput 1
#DEFINE adParamOutput 2
#DEFINE adParamInputOutput 3
loADO=CREATEOBJECT("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loCmd=CREATEOBJECT("ADODB.Command")
loCmd.ActiveConnection=loADO
loPar = loCmd.CreateParameter("", adInteger, adParamOutput, 5)
loCmd.Parameters.Append(loPar)
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 1
loCmd.Parameters.Append(loPar)
loPar = loCmd.CreateParameter(, adInteger, adParamInput, 5)
loPar.Value = 2
loCmd.Parameters.Append(loPar)
loCmd.CommandText="{?=call FoxPackage.Func3( ?,?)}"
=loCmd.Execute()
?loCmd.Parameters.Item(0).value,loCmd.Parameters.Item(1).value,loCmd.Parameters.Item(2).value
Triggery, neboli spouště, mají stejnou fukci jako u MS SQL nebo VFP - možnost ovlivnit nebo zamítnout změny.
Vykonání triggeru se dá navázat bud na řádek nebo na blok změn a kdy se má vykonat - BEFORE/AFTER DELETE/UPDATE/INSERT.
Pomocí triggerů se dá realizovat refereční integrita, ale nelze realizovat typ CASCADE UPDATE, tj. že při změně primárního klíče se opraví primární klíče v podřazených tabulkách.
ORACLE nesnáší, když se musí odkazovat na tabulky jejichž obsah se mění.
Pokud však nutně potřebujete CASCADE UPDATE, lze na internetu najít několik řešení jak jej realizovat.
Mně osobně to připomíná drbání pravou rukou za levým uchem.
-- Jednoduchá ukázka triggeru ke vložení datetime hodnoty do položky
CREATE OR REPLACE TRIGGER TEST_UPD_TS BEFORE INSERT OR UPDATE
ON TEST FOR EACH ROW
DECLARE
INTEGRITY_ERROR EXCEPTION;
ERRNO INTEGER;
ERRMSG CHAR(200);
DUMMY INTEGER;
FOUND BOOLEAN;
CARDINALITY INTEGER;
BEGIN
INTEGRITYPACKAGE.NEXTNESTLEVEL;
:NEW.XX007 := SYSDATE;
INTEGRITYPACKAGE.PREVIOUSNESTLEVEL;
-- ERRORS HANDLING
EXCEPTION
WHEN INTEGRITY_ERROR THEN
BEGIN
INTEGRITYPACKAGE.INITNESTLEVEL;
RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
END;
/
Cizí klíče se dají použít pro kontrolu dat z cizích tabulek a jsou nutné pro pohledy na straně Oracle pokud chcete aktualizovat data z více tabulek.
Použití cizího kliče lze realizovat kontrolu nad tabulkou sama sebou.
Typickým příkladem je tabulka reprezentující strom kde jeden sloupec je ID věty a druhý ID nadřazené věty.
-- kontrola nadřazeného záznamu v téže tabulce
ALTER TABLE TabTree ADD CONSTRAINT FK_TabTree_1
FOREIGN KEY (XX000IP)
REFERENCES TabTree (XX000I) ON DELETE CASCADE
/
-- kontrola záznamu v nadřazené tabulce
ALTER TABLE TEST2 ADD CONSTRAINT FK_TEST_1
FOREIGN KEY (XX000)
REFERENCES TEST (XX000) ON DELETE CASCADE
/
Oracle umožňuje vkládat optimalizační příkazy v SQL příkazech a jsou zapouzdřeny v komentáři.
Toto se hodí, když Oracle odmítá použít správný index pro optimalizaci výrazu a trvá na nějakém indexu na konci seznamu.
Příklad vynuceného použití konkrétního indexu:
/*+ INDEX(patients sex_index) Use SEX_INDEX, since there are few male patients */
Stačí když si v helpu vyhledáte: Star Queries
Tvoří se jednoduše a dá se říci jaké sloupce se mají aktualizovat
U RV přes SPT/CA je nutné potřebné vlastnosti nastavit ručně, nicméně platí jedna základní podmínka - je nutné definovat do seznamu mapování položek z VFP na datovou stranu všechny sloupce které vrací RV, včetně těch které jsou tvořeny funkcemi.
Ukázka provnání výsledků mezi
SPT a
CA
** otevření spojení
lihdbcMS=SQLSTRINGCONNECT("driver={microsoft odbc for oracle};"+;
"connectstring=orcl1; uid=test; pwd=test;")
lihdbcORA=SQLSTRINGCONNECT("driver={oracle odbc driver};dbq=orcl1;"+;
" uid=test; pwd=test;")
loADO=createobject("ADODB.Connection")
loADO.Open("Provider=MSDAORA.1;Data Source=orcl1; user id=test; password=test;")
loRS=CREATEOBJECT("ADODB.Recordset")
loRS.ActiveConnection = loADO
loRS.CursorType= 3 && adOpenStatic
loRS.CursorLocation = 3 && adUseClient
loRS.LockType= 3 && adLockOptimistic
lcAlias=SYS(2015)
lcAlias2=SYS(2015)
** CursorAdapter
loCAx=CREATEOBJECT("cursoradapter")
loCAx.FetchSize=100
loCAx.FetchMemo=.T.
loCAx.CompareMemo=.F.
loCAx.UseMemoSize=255
loCAx.FetchAsNeeded=.F.
loCAx.UpdateNameList=;
"XX000 TEST.XX000, XX001 TEST.XX001, XX002 TEST.XX002, XX003 TEST.XX003,"+;
" XX004 TEST.XX004, XX007 TEST.XX007, XX008 TEST.XX008,"+;
" XX001_2 TEST.XX001, XX003_4 TEST.XX003"
loCAx.UpdatableFieldList=;
"XX000, XX001, XX002, XX003, XX004, XX007, XX008" && updatování
loCAx.KeyFieldList="XX000" && primární klíč
loCAx.Tables="TEST" && tabulka
loCAx.WhereType=3
loCAx.BatchUpdateCount=1
loCAx.SendUpdates=.T.
loCAx.UpdateType=1
loCAx.Prepared=.F.
loCAx.CursorSchema="XX000 I, XX001 N(14,3), XX002 N(14,3), XX003 B(3), XX004 B(3),"+;
" XX007 T, XX008 C(5), XX001_2 F, XX003_4 F"
loCAx.SelectCmd="SELECT XX000, XX001, XX002, XX003, XX004, XX007,"+;
" XX008, XX001*XX002 AS XX001_2,XX003*XX004 AS XX003_4 FROM TEST"
loCAx.DataSourceType="ODBC"
loCAx.DataSource=lihdbcMS
loCAx.Alias=lcAlias
loCAx.MaxRecords=-1
lll=loCAx.CursorFill(.T.)
SELE (lcAlias)
BROW NOWAIT
SELE 0
** SPT
=SQLEXE(lihdbcMS,"SELECT XX000, XX001, XX002, XX003, XX004,"+;
" XX001*XX002 AS XX001_2,XX003*XX004 AS XX003_4 FROM TEST",lcAlias2)
SELE (lcAlias2)
BROW NOWAIT
SUSPEND
* ORACLE
loCAx.DataSource=lihdbcORA
lll=loCAx.CursorFill(.T.)
SELE (lcAlias)
BROW NOWAIT
=SQLEXE(lihdbcORA,"SELECT XX000, XX001, XX002, XX003, XX004,"+;
" XX001*XX002 AS XX001_2,XX003*XX004 AS XX003_4 FROM TEST",lcAlias2)
SELE (lcAlias2)
BROW NOWAIT
* ADO
loCAx.DataSourceType="ADO"
loCAx.DataSource=loRS
lll=loCAx.CursorFill(.F.)
SELE (lcAlias)
BROW NOWAIT
SUSP
=SQLDISCONNECT(lihdbcMS)
=SQLDISCONNECT(lihdbcORA)
Ukázková databáze ora.dbc obsahuje tyto vzdálené pohledy:
| Název | Popis |
| test_ms | Pohled na tabulku test přes MS ODBC driver. |
| test2_ms | Pohled na tabulku test2 přes MS ODBC driver. |
| v_test_ms | Pohled na pohled testx přes MS ODBC driver. |
| v_testro_ms | Pohled na pohled testxro přes MS ODBC driver. |
| test_ora | Pohled na tabulku test přes Oracle ODBC driver. |
| test2_ora | Pohled na tabulku test2 přes Oracle ODBC driver. |
| v_test_ora | Pohled na pohled testx přes Oracle ODBC driver. |
| v_testro_ora | Pohled na pohled testxro přes Oracle ODBC driver. |
| v_test_test2 | Pohled mezi dvěmi tabulkami test a test2 přes MS ODBC driver. |
Aktulizační pohledy nad více tabulkami
Jsou čtyři způsoby:
Mít dva oddělené RV.
Mít RV ve VFP kde jsou tabulky provázány a pro všechny zůčastněné tabulky se musí dotahovat položky tvořící primární klíče.
Mít pohled na straně Oracle a tabulky musí být provázené cizím klíčem.
Pokud tabulky nemají definovány cizí klíče, nebo do RV se nanatahují všechny sloupce tvořící primární klíče, pak můžeme aktualizovat pouze hlavní tabulku.
To znamená, že k odeslání hodnot na server označíme pouze ty sloupce jež patří do hlavní tabulky.
Tvorba RV
- Spojování tabulek na VFP straně
- Ve View designeru lze nastavit typ spojení i jako LEFT JOIN.
Bohužel když jde o Oracle, je takové spojení definováno obráceně: test2.xx000(+)=test.xx000
Ve VFP 7.0 takový RV lze spustit, ale modifikovat už ne (VFP 8.0 je na tom možná podobě).
Musel by se použít přímý zápis na opravu tohoto spojení.
VFP 9.0 sice zahlásí chybu, ale je možné v okně SQL tuto podmínku opravit (test.xx000=test2.xx000(+)) a view designer se dle toho opraví sám.
- Změna datové struktury
- Změna datové struktury na straně Oracle dokáže View designer rozhodit.
Po otevření takového RV se zobrazí hlášení o změně struktury, přičemž se kompletně ztratí všechny nastavené vlastnosti položek u pohledu.
- Typy Memo a General
- Nezapomeňte ve rozšiřujících vlastnostech odznačit "Include Memo fields in WHERE clause".
Pokuď tomu tak neučiníte, pak při nastavení vlastnosti WhereType={2,3} budou(2)/můžou být(3) tyto pole pro porovnání zda došlo ke změně záznamu jiným uživatelem.
Což u těchto polí může být problém s přenosem většího, množství dat (10 KB, 100KB až MB).
- Typ Float
- Je převáděn jako double, ale doopravdy záleží na ODBC driveru jak typ položky mapuje a na VFP, jak provede namapování z typu ODBC na VFP typ.
Proto je vhodné si nastavit vlastní/správný počet desetinných míst.
Indexy, procedury, funkce - toto jsou objekty a názvy objektů jsou jedinečné v rámci databáze, tudíž nelze mít dva indexy s názvem "OBJECTID" u více tabulek.
Pokud driver vrátí chybu, je jedno jestli je od MS nebo Oracle, že selhalo volání OCI (Oracle Call Interface), pak můžete udělat pouze jedno - doporučit klientovi ať se obrátí na podporu Oracle, má-li ji zaplacenou, nebo ať nainstaluje jinou verzi Oracle Net Clienta.
Také můžete použít Google a výsledky hledání použít jako páku.
Při použití MS driveru může nastat chyba ještě v jedné vrstvě, která je mezi ODBC driverem a OCI.
Tuto vrstvu tvoří mtxoci.dll a je zdrojem mnoha chyb, viz MSDN.