Syntaks: (Tabelnavne skal have leading spce, så MYSQ fkt. kan kvalificere dem) SQL expresions '***********Udfør en SQL som action query. RUNSQL behøver ikke parameter substitution Call sqlRecset(8, "", "", "", "") ' delete * from TempObjektID_T; MsgBox "Action Query udført. Tjek at TempObjektID_T er tømt" '************Hent et recordset fra en selct sætning. Recordst .open kræver parameter substitution. Set st = sqlRecset(6, "", [Forms]![soeg_f]![Karakteristika], [Forms]![soeg_f]![fritekst], [Forms]![soeg_f]![ObjID]) 'BEMÆRK ObjID skal omdannes til tal med VAL(string) i SQL udtrykket 'omskrevet:sqlRecset(6, "", "*", "*", str(102)) eller uden VAL i SQL: sqlRecset(6, "", "*", "*", "#" & str(102)) st.MoveFirst MsgBox "enkrlt felt i første post: " & st(0) '******************Visning af SQLstringReturn: MsgBox "Visning af SQLstringReturn: " & SQLstringReturn '***************Hent en enkelt værdi******** stri = sqlRecset(37, "", str(410), "", "")(0) MsgBox "Enkelt værdi til string eller number: " & (stri) '****************Erstat SQL udtryk; tabeller select, from og where clause***** 'Call sqlRecset(x, "=eventueltNavn", "", "", "") '= foran naet eventuelt navn i navnefeltet, medfører at kun SQL teksten returneres 'eksempel: 'Call sqlRecset(114, "=", ToBase, "", "") 'SQL streng returneres med Param1 substitueret med værdien af ToBase 'Me.RecordSource = SQLstringReturn --en Formular får åbnings sql 'Call sqlRecset(x, "eventueltNavn", "#Tabelnavn", "", "","" ,"", "") '# foran parameter strengen, fortæller at det er et udtryk der skal substitueres uden citationstegn. 'F.eks. et tal eller ' f.eks et tabelnavn ID SQL udtryk Ver.:20120808 SQL type 1 Evt. navn Forklaring Report created: 14-01-2015 Benyttet hvor ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 5 SELECT * from facttable1_t, gereba_node1.tempobjektid_t as t where t.objektid=&Param1& and t.objektid=fremsoegtid; SELECT TEST sql til strenge null null 6 SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2 where f1.MedKarakteristika Like &Param1& and f2.DATA like &Param2& and f1.FremsoegtID= f2.FremsoegtID UNION SELECT f1.FremsoegtID as id FROM Facttable1_t as f1 where f1.fremsoegtID=val(&Param3&) and &Param2&="0" SELECT Test_select Test på substitution af parametre som kræves i SELECT udførsler Test på kald af Function sqlRecset, kaldt fra Sub Test_sqlRecs et() 8 delete from TempObjektID_T; DELETE SletTempObj null Før nye søgninger med ACCESS, se også 301 for PHP. 8 bruges også i PHP ved cleaning fra Builtfacttable 9 insert into TempObjektID_T (ObjektID) select id from TEMP2_q INSERT ResultIntoTemp null Søgeformen 14 select def1, def2, deftext from gereba_node1.Mandatory_T where def1=&Param1& SELECT Test null null 19 INSERT into StaggingPD2_T (Action,FromBase, ToBase, Karakteristika, KarakType, DATA, ObjektType, INSERT StaggingPD2_Q Param1 er basenavn null Ver.:20120808 2 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor LocalTransOK, GlobalTransOK,LocalID, NewID) SELECT &Param4&, &Param1&, &Param2&, c.fieldx, f.definition , f.data, c.ObjektTypeX, 1, 0, t.objektid, 0 FROM facttable1_t AS f, convert_t AS c, tempobjektid_t AS t WHERE c.field=f.medkarakteristika and c.databasex=&Param2& and t.objektid=&Param3& and t.objektid=f.fremsoegtid and f.def=c.ObjektType; 22 SELECT oo.objekt1, f1.fremsoegtid, f1.medkarakteristika, f1.DATA, f2.fremsoegtid FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo WHERE f2.def="ProcObj" and oo.objekt2=f2.fremsoegtID and oo.relationstype="ActivateDok" and oo.objekt1=f1.fremsoegtID and f2.medkarakteristika="Alert" and format( f2.DATA,"yyyymmddhhmm")<=Format(Now, "yyyymmddhhmm") ; SELECT Alert_Q Bemærk Alert null karakteristika og tidspunkt skal være på ProcObj eRelationstypen sættes eller slettes ? 23 INSERT into TempObjektID_T (ObjektID,user) SELECT oo.objekt1,"admin" FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo WHERE f2.def="ProcObj" and oo.objekt2=f2.fremsoegtID and oo.relationstype="ActivateDok" and f1.fremsoegtID=oo.objekt1 and f1.medkarakteristika="Alert" and f2.medkarakteristika="Alert" and format(f1.DATA, "yyyymmdd")<=format(Now, "yyyymmdd"); INSERT null null Ver.:20120808 3 Report created: null 14-01-2015 ID 24 SQL udtryk SELECT o.Def, "noconvert" AS Udtryk1, o.id FROM objdeclare_t AS o, TempObjektID_T AS toi WHERE (o.ID=toi.objektid) AND not Exists (SELECT null FROM facttable1_t AS f, convert_t AS c WHERE c.field=f.medkarakteristika and c.databasex=&Param1& and f.fremsoegtid=toi.objektid and o.def=c.ObjektType ) AND not Exists (SELECT null FROM convert_t AS c WHERE c.field="copy" and c.databasex=&Param1& ) union SELECT o.Def, "convertok" AS Udtryk1 , o.id FROM objdeclare_t AS o, TempObjektID_T AS toi WHERE (o.ID=toi.objektid) AND Exists (SELECT null FROM facttable1_t AS f, convert_t AS c WHERE c.field=f.medkarakteristika and c.databasex=&Param1& and f.fremsoegtid=toi.objektid and o.def=c.ObjektType ) union SELECT "copy", "copy", 0 FROM convert_t AS c WHERE c.field="copy" and c.databasex=&Param1& ; Ver.:20120808 4 SQL type Evt. navn Forklaring SELECT TjekObjektConve Tjekker at der fineds null rt en type konverterings angivelse i convert_t og om der er konvertering af felter. Bemærk den tjekker kun om der eksistere mindst ét felt, ELLER databasen der eksporteres til er en base med samme objekt typer COPY Report created: 14-01-2015 Benyttet hvor ID 25 SQL udtryk SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as f4 where f1.MedKarakteristika Like [forms]![Soeg_F]![Karakteristika] and f1.definition="DATA" and f2.MedKarakteristika Like [forms]![Soeg_F]![thesaurus_felt] and f3.DATA like [Forms]![soeg_f]![fritekst] and f4.Def Like [forms]![Soeg_F]![combo12] and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID and f3.fremsoegtID=f4.fremsoegtID UNION ALL SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3 where (f1.DATA = [forms]![Soeg_F]![Karakteristika] or f1.data=[forms]![Soeg_F]![thesaurus_felt]) and f1.definition="EndDef" and f2.DATA like [Forms]![soeg_f]![fritekst] and f3.Def Like [forms]![Soeg_F]![combo12] and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID UNION all SELECT ObjDeclare_T.id FROM ObjRelationObj_T, ObjDeclare_T where (ObjDeclare_T.id=ObjRelationObj_T.objekt1 or ObjDeclare_T.id=ObjRelationObj_T.objekt2) and ObjRelationObj_T.relationstype= [forms]![Soeg_F]![combo2] and [Forms]![soeg_f]![fritekst]="0" UNION all select oro.objekt2 From objrelationobj_t as oro, facttable1_t as f1, facttable1_t as f2 where f1.data like [Forms]![soeg_f]![fritekst] and f1.fremsoegtid=oro.objekt2 Ver.:20120808 5 SQL type Evt. navn Forklaring SELECT VIEW Temp2_Q Fremsøger med omskrevet til enkeltfelt fritekst, uden SQL 230, Og, Eller. 231, 232 BENYTTES IKKE Søgning Karakteristika eller thesaurus med endDef Søgning på relations typer Thesaurus søgning Report created: 14-01-2015 Benyttet hvor ID SQL udtryk SQL type Evt. navn and oro.objekt1=f2.fremsoegtid and [forms]![Soeg_F]![thesaurus_felt]<>"*" and [forms]![Soeg_F]![fritekst]<>"*" and f2.data like [forms]![Soeg_F]![thesaurus_felt] UNION all SELECT f1.FremsoegtID as id FROM Facttable1_t as f1 where f1.fremsoegtID=val([forms]![Soeg_F]![ObjID]) and [forms]![Soeg_F]![fritekst]="0" ; 26 Benyttet hvor ID søgning INSERT into TempObjektID_T (user, ObjektID) select "admin", u.id from (SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as f4,Facttable1_t as f5 where f1.MedKarakteristika Like [forms]![Soeg_F]![Karakteristika] and f2.MedKarakteristika Like [forms]![Soeg_F]![thesaurus_felt] and f3.DATA like [Forms]![soeg_f]![fritekst] and f5.DATA like [Forms]![soeg_f]![Fritekst2] and f4.Def Like [forms]![Soeg_F]![combo12] and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID and f3.fremsoegtID=f4.fremsoegtID and f4.fremsoegtID=f5.fremsoegtID UNION ALL SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3 where (f1.DATA = [forms]![Soeg_F]![Karakteristika] or f1.data=[forms]![Soeg_F]![thesaurus_felt]) and f1.definition="EndDef" and f2.DATA like [Forms]![soeg_f]![fritekst] and f3.Def Like [forms]![Soeg_F]![combo12] and f1.FremsoegtID= f2.FremsoegtID Ver.:20120808 Forklaring 6 INSERT TEMP2_q_and Fremsøger hvor Og operator er brugt. Report created: 14-01-2015 null ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor INSERT TEMP2_q_or null null and f2.FremsoegtID= f3.FremsoegtID UNION ALL SELECT ObjDeclare_T.id FROM ObjRelationObj_T, ObjDeclare_T where (ObjDeclare_T.id=ObjRelationObj_T.objekt1 or ObjDeclare_T.id=ObjRelationObj_T.objekt2) and ObjRelationObj_T.relationstype= [forms]![Soeg_F]![combo2] and [Forms]![soeg_f]![fritekst]="0" UNION ALL select f1.fremsoegtid From objrelationobj_t as oro, facttable1_t as f1, facttable1_t as f2, facttable1_t as f3 where f1.data like [Forms]![soeg_f]![fritekst] and f1.fremsoegtid=oro.objekt2 and f2.data like [Forms]![soeg_f]![fritekst2] and f1.fremsoegtid=f2.fremsoegtid and oro.objekt1=f3.fremsoegtid and [forms]![Soeg_F]![thesaurus_felt]<>"*" and [forms]![Soeg_F]![fritekst]<>"*" and [forms]![Soeg_F]![fritekst2]<>"*" and f3.data like [forms]![Soeg_F]![thesaurus_felt] UNION SELECT f1.FremsoegtID as id FROM Facttable1_t as f1 where f1.fremsoegtID=[forms]![Soeg_F]![ObjID] and [forms]![Soeg_F]![fritekst]="0") as u; 27 INSERT into TempObjektID_T (user, ObjektID) select "admin", u.id from (SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as f4,Facttable1_t as f5 where f1.MedKarakteristika Like [forms]![Soeg_F]![Karakteristika] and f2.MedKarakteristika Like [forms]![Soeg_F]![thesaurus_felt] and (f3.DATA like [Forms]![soeg_f]![fritekst] or f5.DATA like [Forms]![soeg_f]![Fritekst2]) Ver.:20120808 7 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring and f4.Def Like [forms]![Soeg_F]![combo12] and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID and f3.fremsoegtID=f4.fremsoegtID and f4.fremsoegtID=f5.fremsoegtID UNION ALL SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3 where (f1.DATA = [forms]![Soeg_F]![Karakteristika] or f1.data=[forms]![Soeg_F]![thesaurus_felt]) and f1.definition="EndDef" and f2.DATA like [Forms]![soeg_f]![fritekst] and f3.Def Like [forms]![Soeg_F]![combo12] and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID UNION ALL SELECT ObjDeclare_T.id FROM ObjRelationObj_T, ObjDeclare_T where (ObjDeclare_T.id=ObjRelationObj_T.objekt1 or ObjDeclare_T.id=ObjRelationObj_T.objekt2) and ObjRelationObj_T.relationstype= [forms]![Soeg_F]![combo2] and [Forms]![soeg_f]![fritekst]="0" UNION ALL select f1.fremsoegtid From objrelationobj_t as oro, facttable1_t as f1, facttable1_t as f2, facttable1_t as f3 where f1.data like [Forms]![soeg_f]![fritekst] and f1.fremsoegtid=oro.objekt2 and f2.data like [Forms]![soeg_f]![fritekst2] and f1.fremsoegtid=f2.fremsoegtid and oro.objekt1=f3.fremsoegtid and [forms]![Soeg_F]![thesaurus_felt]<>"*" and [forms]![Soeg_F]![fritekst]<>"*" and [forms]![Soeg_F]![fritekst2]<>"*" and f3.data like [forms]![Soeg_F]![thesaurus_felt] Ver.:20120808 8 Report created: 14-01-2015 Benyttet hvor ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor INSERT CreateFactTable Opbygger facttable1_t., før prioritet indsættes med sql 88. DENNE SQL virker ikke med UNION i ODBC, derfor er den opsplittet i 3 INSERT (212,213,214) Nu bygget til PHP Alle kald til BuildFactTab le(ObjID As Long) med 0 som argument, hvorved hele tabellen gendannes UNION ALL SELECT f1.FremsoegtID as id FROM Facttable1_t as f1 where f1.fremsoegtID=[forms]![Soeg_F]![ObjID] and [forms]![Soeg_F]![fritekst]="0") as u; 28 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id as ID, "U2" as unionnr, o.def, data_t.type as medkarakteristika, data_t.data, ucase("DATA") as definition from objdeclare_t o, data_t, (select data_id, objekt_id from relationdata_t) ra where o.id=&Param1& and ra.objekt_id=o.id and ra.data_id=data_t.data_id and data_t.data<>data_t.type UNION select o.id as ID, "U1" as unionnr, o.def, data_t.type as karakteristika, data_t.data, "EndDef" as definition from objdeclare_t o, data_t, (select data_id, objekt_id from relationdata_t) ra where o.id=&Param1& and ra.objekt_id=o.id and ra.data_id=data_t.data_id and data_t.data=data_t.type UNION select o.id, "U3", Ver.:20120808 9 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor o.def, &Param2&, concat(dbi.BasisArkiv, d2.data, d.data) as sti, &Param3& from objdeclare_t o, relationData_t rd, relationData_t rd2, data_t d, data_t d2, DBInstans_t as dbi where o.id=&Param1& and o.id=rd.objekt_id and o.id=rd2.objekt_id and o.def="Dokument" and rd.data_id=d.data_id and rd2.data_id=d2.data_id and d.type="Filnavn" and d2.type="FilKatalog" and dbi.id= &Param4&; 30 select fremsoegtid, def, medkarakteristika, data from facttable1_t order by fremsoegtid desc SELECT null null null 31 select fremsoegtid, def, medkarakteristika, data from facttable1_t, TempObjektID_T where fremsoegtID=[TempObjektID_T].[objektid] SELECT null null null 33 SELECT dr.ObjektKlasse, a.def1, dr.TilladtKarakRela, x.def2, dr.Obligatorisk, prioritet from Dataregler_T dr, definitionshiraki_T a, definitionshiraki_T x where dr.ObjektKlasse= a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1 UNION SELECT dr.ObjektKlasse, a.def1, dr.TilladtKarakRela, x.def2, dr.Obligatorisk,prioritet from Dataregler_t dr, definitionshiraki_t b, definitionshiraki_T a, SELECT VIEW selectDataRegler Danner krævede KarakRela_Q /ønskede karakteristika udfra et vilkårligt niveau i objekt definitions hirakiet. Ver.:20120808 10 Report created: 14-01-2015 Bruges ikke som select, men som INSERT i 34 ID SQL udtryk SQL type Evt. navn Forklaring SELECT SelectDataRegle Indsætter krævede rKarak_Q /ønskede Benyttet hvor definitionshiraki_T x where dr.ObjektKlasse= b.def1 and b.def2=a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1 UNION SELECT dr.ObjektKlasse, a.def1, dr.TilladtKarakRela, x.def2, dr.Obligatorisk,prioritet from Dataregler_t dr, definitionshiraki_t b, definitionshiraki_T a, definitionshiraki_T c, definitionshiraki_T x where dr.ObjektKlasse= c.def1 and c.def2=b.def1 and b.def2=a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1 UNION SELECT dr.ObjektKlasse, a.def1, dr.TilladtKarakRela, x.def2, dr.Obligatorisk,prioritet from Dataregler_t dr, definitionshiraki_t b, definitionshiraki_T a, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T x where dr.ObjektKlasse= d.def1 and d.def2=c.def1 and c.def2=b.def1 and b.def2=a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1; 34 SELECT "-", "-", "-", u.adef1, u.drt, u.xdef2, "", convert(&Param1&,signed), 0 FROM (SELECT dr.ObjektKlasse as drobj, a.def1 as adef1, Ver.:20120808 11 Report created: 14-01-2015 null ID SQL udtryk SQL type dr.TilladtKarakRela as drt, x.def2 as xdef2, "", convert(&Param1&,signed) Dataregler_T dr, definitionshiraki_T a, definitionshiraki_T x where dr.ObjektKlasse= a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1 UNION SELECT dr.ObjektKlasse, a.def1 as adef1, dr.TilladtKarakRela as drt, x.def2 as xdef2, "", convert(&Param1&,signed) from Dataregler_t dr, definitionshiraki_t b, definitionshiraki_T a, definitionshiraki_T x where dr.ObjektKlasse= b.def1 and b.def2=a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1 UNION SELECT dr.ObjektKlasse, a.def1 as adef1, dr.TilladtKarakRela as drt, x.def2 as xdef2, "", convert(&Param1&,signed) from Dataregler_t dr, definitionshiraki_t b, definitionshiraki_T a, definitionshiraki_T c, definitionshiraki_T x where dr.ObjektKlasse= c.def1 and c.def2=b.def1 and b.def2=a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1 UNION SELECT dr.ObjektKlasse, a.def1 as adef1, dr.TilladtKarakRela as drt, x.def2 as xdef2, "", convert(&Param1&,signed) from Ver.:20120808 12 Evt. navn Forklaring karakteristika udfra et vilkårligt niveau i objekt definitions hirakiet. Med Val(param) er det i access sql, her i MYSQL med convert (er opspliitet i sql 216 ff, for at køre med odbc) Report created: 14-01-2015 Benyttet hvor ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor from Dataregler_t dr, definitionshiraki_t b, definitionshiraki_T a, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T x where dr.ObjektKlasse= d.def1 and d.def2=c.def1 and c.def2=b.def1 and b.def2=a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1 ) AS u WHERE u.adef1=&Param2&; 35 INSERT into StaggingPD2_T (Action,FromBase, ToBase, Karakteristika, KarakType, DATA, ObjektType, LocalTransOK, LocalID) SELECT "EditObject" ,&Param2&,&Param3&, f.medkarakteristika, f.definition, f.data, f.def, false, f.fremsoegtid FROM facttable1_t AS f WHERE f.fremsoegtid=&Param1& and UnionNr<>"U3" and f.definition="DATA" ; INSERT RedigPrepare1_ Q Indsættelse af DATA null hvor medkarakteristika=DA TA. Hvis EndDef er det nødvendigt at insætte MedKarakteristika = DATA, fordi facttablen nu viser et højere niveau af enddef karakteristika. (se 185) 37 select def from ObjDeclare_T where id=&Param1& SELECT FindDef null 38 select ObjektType, Karakteristika, KarakType, DATA, NewID, LocalID, transactID SELECT GetRedig_Q Henter data fra null stagging, hvor data er, eller ikke er testede, Ver.:20120808 13 Report created: null 14-01-2015 ID SQL udtryk SQL type Evt. navn From StaggingPD2_T where GlobaltransOK=FALSE and action =&Param2& and tobase=&Param1&; Forklaring Benyttet hvor og endnu ikke indsat i basen 40 INSERT into StaggingPD2_T (FromBase, Tobase, Action, ObjektType, Karakteristika, KarakType, DATA, LocalID, NewID) select &Param3&, &Param3&, "EditObject", &Param2&,"RegistDato", "DATA",&Param4& ,&Param1&,&Param1&; INSERT InsertDato_Q Indsætter dags dato, null BEMÆRK det er en fejl at RegistDato er hårdtkodet, og at ordre også må indsættes hårdtkodet p.g.af mangel på parametre 45 insert into temptemp2 (last) SELECT distinct oo.objekt2 from ObjRelationObj_T as oo, TempObjektID_T where oo.Objekt1=TempObjektID_T.objektid; INSERT RightObjects null null 46 insert into TempObjektID_T (objektID, user) select last, "admin" from temptemp2; INSERT null null null 47 delete from temptemp2; DELETE null null null 48 insert into ObjRelationObj_T (Objekt1, Relationstype, Objekt2) select val(&Param1&) , "ActivateDok", FremsoegtID from Facttable1_t where MedKarakteristika="Alert" and def="ProcObj"; INSERT InsertAlert null null 49 SELECT f1.FremsoegtID, f1.MedKarakteristika, f1.Def, "Alert Eksisterer, OK" FROM FactTable1_T AS f1 WHERE f1.Def="ProcObj" and f1.medkarakteristika="Alert"; union SELECT "", "","", "Alert Eksisterer ikke, Advarsel!" FROM FactTable1_T AS f1 WHERE not exists ( select null from FactTable1_T AS f1 where f1.Def="ProcObj" and SELECT TestForEksistens null ALERT null Ver.:20120808 14 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor f1.medkarakteristika="Alert") SELECT "", "","", "NyopretObj Eksisterer ikke, Advarsel!" FROM FactTable1_T AS f1 WHERE not exists ( select null from FactTable1_T AS f1 where f1.Def="ProcObj" and f1.medkarakteristika="NyopretObj") ; 50 Delete * from ObjRelationObj_T where objekt1=val(&Param1&) and relationstype='ActivateDok' and objekt2=(select f.fremsoegtID from facttable1_t as f where f.def='ProcObj' and Medkarakteristika='Alert'); DELETE null null null 52 INSERT into TempObjektID_T (ObjektID,user) SELECT distinct oo.objekt1,"admin" FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo WHERE f2.def="ProcObj" and oo.objekt2=f2.fremsoegtID and oo.relationstype="ActivateDok" and f1.fremsoegtID=oo.objekt1 and f2.medkarakteristika="Alert" and format(f2.DATA, "yyyymmdd")>format(Now, "yyyymmdd"); INSERT ShowAlerts_Q viser alle Alerts der har forekommet null 53 INSERT into temptemp2 (def2, first) select str(u.id), left(u.dat,10) from ( SELECT FactTable1_T.FremsoegtID as id, FactTable1_T.DATA as dat FROM TempObjektID_T, FactTable1_T where TempObjektID_T.ObjektID = FactTable1_T.FremsoegtID and str(FactTable1_T.prioritet)=str(TempObjektID_T.sort) union SELECT FactTable1_T.FremsoegtID ,FactTable1_T.DATA FROM TempObjektID_T , FactTable1_T INSERT TempSort_Q indsætter id som string Sortering er ligegyldig her., foregår i fremsøgnings rapporterne Fra sorterings knappen i søg Ver.:20120808 15 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor where TempObjektID_T.ObjektID = FactTable1_T.FremsoegtID and FactTable1_T.prioritet = (select min(prioritet) from FactTable1_T as f1 where f1.fremsoegtID=TempObjektID_T.ObjektID ) and not exists (select null from FactTable1_T as f2 where str( f2.prioritet)=str(TempObjektID_T.sort) and f2.fremsoegtID=TempObjektID_T.ObjektID ) order by 2 ) as u ; 54 INSERT into tempobjektid_T (user, ObjektID, sort) select "admin", def2, first from temptemp2 ; INSERT null Sletter poster hvor der null ikke er sorteret efter ny sorterede poster er indsat. 62 update staggingPD2_T set DATA=Right(DATA, len(DATA)-val(&Param1&)) where karakteristika="FilKatalog" and Mid(DATA, 2, 1)=":" and LocaltransOK=false; UPDATE FjernDrev_Q Fjerne specifikke drev null og kataloger for FilKatalog, bemærk at det er nødvendigt at sikre at et : er angivet, for ikke at ødelægge katalogangivelser uden specifik henvisning til drev. 63 insert into temptemp2 (last) SELECT distinct oo.objekt1 from ObjRelationObj_T as oo, TempObjektID_T where oo.Objekt2=TempObjektID_T.objektid; INSERT LeftObjects null 64 update TempObjektID_T set sort =&Param1& UPDATE SortPrio_Q indsætter den priorotet Sortering af der ønskes sesorteret fremviste efter data Ver.:20120808 16 Report created: null 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 65 SELECT Definitionshiraki_T.def1, Definitionshiraki_T.def2, Definitionshiraki_T_1.def2, SELECT Definitionshiraki_T_2.def2, Definitionshiraki_T_3.def2, Definitionshiraki_T_4.def2, Definitionshiraki_T_5.def2, Definitionshiraki_T_6.def2 FROM (((((Definitionshiraki_T LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_1 ON Definitionshiraki_T.def2 = Definitionshiraki_T_1.def1) LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_2 ON Definitionshiraki_T_1.def2 = Definitionshiraki_T_2.def1) LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_3 ON Definitionshiraki_T_2.def2 = Definitionshiraki_T_3.def1) LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_4 ON Definitionshiraki_T_3.def2 = Definitionshiraki_T_4.def1) LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_5 ON Definitionshiraki_T_4.def2 = Definitionshiraki_T_5.def1) LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_6 ON Definitionshiraki_T_5.def2 = Definitionshiraki_T_6.def1 WHERE (((Definitionshiraki_T.def1)="BasisObj" Or (Definitionshiraki_T.def1)="Karakter" Or (Definitionshiraki_T.def1)="Relation" Or (Definitionshiraki_T.def1)="Handling")) ORDER BY Definitionshiraki_T.def1, Definitionshiraki_T.def2, Definitionshiraki_T_1.def2, Definitionshiraki_T_2.def2, Definitionshiraki_T_3.def2, Definitionshiraki_T_4.def2, Definitionshiraki_T_5.def2; DefinitionsHiraki_ Dette view viser op til Q 7 niveauer i et definitionshiraki dannet udfra tabellen Declare_T. Her med ODBC syntaks for outer joins 66 select Definitionshiraki_ ORACLE syntaks med null Oracle outer join tilsvarende 65. Her vist med outer joins i ORACLE syntaks, hvorfor en oracle tabel ed dannet i SCOTTS skema. d1.def1, d1.def2, d2.def2, nvl(d3.def2,'-'), nvl(d4.def2,'-'), nvl(d5.def2,'-') from scott.definitionshiraki_T d1, (select d2.def1, d2.def2 from scott.definitionshiraki_T d2, scott.definitionshiraki_T d1 where d1.def1 in ('BasisObj', 'Karakter', 'Relation') and d2.def1=d1.def2 ) d2, (select d3.def1, d3.def2 from scott.definitionshiraki_T d3, scott.definitionshiraki_T d2, Ver.:20120808 SELECT 17 Report created: 14-01-2015 View der aktiveres fra meuen over hjælpe dokumenter. ID SQL udtryk SQL type Evt. navn Forklaring SELECT HovedRapport_Q Optæller antal fremsøgte poster, gruperet efter Benyttet hvor scott.definitionshiraki_T d1 where d1.def1 in ('BasisObj', 'Karakter', 'Relation') and d2.def1=d1.def2 and d3.def1=d2.def2) d3, (select d4.def1, d4.def2 from scott.definitionshiraki_T d4, scott.definitionshiraki_T d3, scott.definitionshiraki_T d2, scott.definitionshiraki_T d1 where d1.def1 in ('BasisObj', 'Karakter', 'Relation') and d2.def1=d1.def2 and d3.def1=d2.def2 and d4.def1=d3.def2) d4, (select d5.def1, d5.def2 from scott.definitionshiraki_T d5, scott.definitionshiraki_T d4, scott.definitionshiraki_T d3, scott.definitionshiraki_T d2, scott.definitionshiraki_T d1 where d1.def1 in ('BasisObj', 'Karakter', 'Relation') and d2.def1=d1.def2 and d3.def1=d2.def2 and d4.def1=d3.def2 and d5.def1=d4.def2) d5 where d1.def1 in ('BasisObj', 'Karakter', 'Relation') and d2.def1 (+) =d1.def2 and d3.def1 (+) =d2.def2 and d4.def1 (+) =d3.def2 and d5.def1 (+) =d4.def2 order by 1,2,3; 67 select sort, objektID, count(ObjRelationObj_T.objekt2) as objekt2 from TempObjektID_T, ObjRelationObj_T where TempObjektID_T.objektid= ObjRelationObj_T.objekt1 Ver.:20120808 18 Report created: 14-01-2015 Bruges som hovedrapport af ID SQL udtryk SQL type Evt. navn group by sort, objektid union select sort, objektID,0 from TempObjektID_T where not exists (select null from ObjRelationObj_T where objrelationobj_t.objekt1=TempObjektID_T.objektid) Forklaring Benyttet hvor sorteringskriterier blandt de fremsøgte poster i TempObjektID_T CrystalRepor ts 68 DELETE from data_t WHERE (((Exists (select null from RelationData_T where data_t.data_id=relationdata_t.data_id))=False)); DELETE DeleteLonlyData _Q Sletter data uden relationer til objekter null 69 DELETE from ObjrelationObj_T where objekt1=&Param1& or objekt2=&Param1&; DELETE DelOrO_Q null SLET Objekt proceduren 71 SELECT (f2.DATA & f1.DATA) AS Udtryk1 FROM Facttable1_T AS f1, Facttable1_T AS f2 WHERE (((f1.FremsoegtID)=&Param1&) AND ((f1.MedKarakteristika)="Filnavn") AND ((f2.FremsoegtID)=&Param2&) AND ((f2.MedKarakteristika)="FilKatalog")) and f2.data is not null UNION select "NONE" from facttable1_t as f1 where not exists (select null from facttable1_t as f2 where ((f2.FremsoegtID)=&Param1&) AND ((f2.MedKarakteristika)="Filnavn")); SELECT null null null 73 select max(ID) from Objdeclare_t SELECT null null null 74 select DATA_ID from DATA_t where Type=&Param2& SELECT WIEW and data = (select data from gereba_node1.staggingpd2_t where transactid= &Param1&); testofdata_q optæller om data findes i forvejen BEMÆRK denne sql er en pass thrugh, hvorfor den skal kvlificeres med gereba_node1, hvis den benyttes fra VB null Ver.:20120808 19 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 75 SELECT "Indsættelse, redigering og sletning af poster" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup from sqlexpresions_t as s where functiongroup=10 UNION ALL SELECT "Import, Export og stagging" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup from sqlexpresions_t as s where functiongroup=11 UNION ALL SELECT "Søgninger" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup from sqlexpresions_t as s where functiongroup=20 UNION ALL SELECT "Sikkerhed" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup from sqlexpresions_t as s where functiongroup=25 UNION ALL SELECT "Struktur" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup from sqlexpresions_t as s where functiongroup=30 or functiongroup=31 UNION ALL SELECT "Handlings objekter" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup from sqlexpresions_t as s where functiongroup=40 UNION ALL SELECT "xxxx" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup from sqlexpresions_t as s where functiongroup not in (10,11,20,30,31,40) or isnull(functiongroup); SELECT VIEW null Danner rapport over Bruges til benyttede SQL udtryk i Access GEREBA rapport samt crystal Reports. BEMÆRK: Uden dobbelt plinger i ODBC databaser 76 INSERT into DATA_T (Type, DATA) values ( &Param1& , &Param2&) ; INSERT null null Ver.:20120808 20 Report created: null 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 77 insert into relationdata_T (objekt_ID, data_ID) values(&Param1&, &Param2&) INSERT insertreladata_q null null 78 DELETE FROM RelationData_T WHERE objekt_ID=&Param1& ; DELETE null null null 79 DELETE FROM ObjDeclare_T WHERE ID=&Param1&; DELETE null null null 80 delete from ObjrelationObj_T where objekt1 in (select TempObjektID_T.ObjektID from DELETE tempObjektID_T) or objekt2 in (select TempObjektID_T.ObjektID from tempObjektID_T); null null null 81 DELETE FROM RelationData_T WHERE objekt_ID in (select TempObjektID_T.ObjektID from tempObjektID_T) ; DELETE null null null 82 DELETE FROM ObjDeclare_T WHERE ID in (select TempObjektID_T.ObjektID from tempObjektID_T); DELETE null null null 83 insert into temptemp2 (last, def1, def2, first) select lastx, typex, firstx, endtype from karakterfirstandlast_q INSERT FindKarakter_Q Finder første og sidste erklæring efter KARAKTER, samt om det er DATA eller EndDef der afslutter. Bruges til at indsætte EndDef karakteristka i DATA_T tabellen (Kan ikke køre som select into recordset, fejl i microsoft?) Nødvendigt at indsætte før den Ver.:20120808 21 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor faktisk bruges? 84 SELECT a.def1 as field1, b.def1 as field2 ,a.def2 as field3 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="BasisObj" and a.def2="EndDef" union SELECT a.def1, c.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="BasisObj" and a.def2="EndDef" union SELECT a.def1,d.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="BasisObj" and a.def2="EndDef" union SELECT a.def1,e.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="BasisObj" and a.def2="EndDef" UNION SELECT a.def1,f.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="BasisObj" and a.def2="EndDef" order by 1; Ver.:20120808 22 SELECT VIEW Objekt_Q Finder første erklæring Tester om efter BasisObj. def er et erklæret objekt (fra Export?) Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 85 SELECT f1.fremsoegtID,f1.Def, f1.MedKarakteristika,f1.DATA, f2.fremsoegtID, f2.def FROM FactTable1_T AS f1, FactTable1_T AS f2,TempObjektID_T,ObjrelationObj_t as oro WHERE f1.def="Person" and f1.medKarakteristika in ("User", "Credit") and f2.fremsoegtID=TempObjektID_T.objektID and f2.fremsoegtID=oro.objekt1 and oro.relationstype="MedSikkerhed" and oro.objekt2=f1.fremsoegtID UNION SELECT f1.fremsoegtID,f1.Def, f1.MedKarakteristika,f1.DATA, f2.fremsoegtID, f2.def FROM FactTable1_T AS f1, FactTable1_T AS f2,TempObjektID_T,ObjrelationObj_t as oro WHERE f1.def="GroupUnit" and f1.medKarakteristika in ("ObjGroupUnit", "Demand") and f2.fremsoegtID=TempObjektID_T.objektID and f2.fremsoegtID=oro.objekt1 and oro.relationstype="MedSikkerhed" and oro.objekt2=f1.fremsoegtID ; SELECT null Starten på et null sikkerheds view med USER sammenholdt til Objekt 87 insert into objrelationobj_T (objekt1, relationstype, objekt2) values(&Param1&,&Param2&, &Param3& ); INSERT Addrelation_Q Insætter relationer mellem objekter null 88 update facttable1_t inner join dataregler_t on dataregler_t.tilladtkarakrela=facttable1_t.medkarakteristika set facttable1_t.prioritet=dataregler_t.prioritet where dataregler_t.tilladtkarakrela=facttable1_t.medkarakteristika and facttable1_t.def=dataregler_t.objektklasse; UPDATE null Indsætter proritet i facttable1_t null 90 UPDATE StaggingPD2_T set LocalTransOK= 1 where tobase=&Param1& and Action=&Param2& and LocalTransOK=FALSE UPDATE null Sætter Kun LOCALTRANS, Ikke Global null 91 select * into OrderTable_T from (SELECT FremsoegtID, Def, MedKarakteristika, data from facttable1_t INSERT null Skal indsætte proc objekter der benyttes null Ver.:20120808 23 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn where def="ProcObj" and fremsoegtID not in( select fremsoegtID from facttable1_t as f2 where f2.def="ProcObj" and f2.medkarakteristika="Alert") and medkarakteristika<>"RegistDato") order by fremsoegtID ; Forklaring Benyttet hvor til data manipulation (exp-import, editering mv.) som videregives som proces ordrer til den modtagende gereba base. 92 select fremsoegtid from facttable1_t as f where DATA = ¶m1& SELECT GetIDfromDocum Finder ID til et null ent registreret filnavn, som forberedelse til objekt relationer 93 INSERT into TempObjektID_T (user,ObjektID) values (¶m2&, &Param1&); INSERT null null 94 INSERT into ObjrelationObj_T (objekt1,relationstype,objekt2) select &Param1& , &Param2&, objektID from TempObjektID_T where user =&Param3& INSERT null Indsætter flerværier fra ObjRelation_ temp tabellen til given F objekt1 ID og med given relationstype 98 SELECT FromBase, karakteristika, KarakType, DATA, newID, LocalID, transactID From StaggingPD2_t Where ToBase=&Param1& and Action=&Param2& and GlobalTransOK=FALSE SELECT null Finder samtlige poster null der deltager fra Staggnpd2_t 99 UPDATE staggingPD2_T set GlobalTransOK=1 WHERE transactid=&Param1&; UPDATE globalok_q Alle der opfylder betingelserne sættes true her null 102 SELECT x.field1 from Objekt_Q as x where x.field2="BasisObj" and x.field1=&Param1& SELECT null Undersøger om basisobjektet er erklæret ( se SQL84) null Ver.:20120808 24 Report created: null 14-01-2015 ID 110 SQL udtryk SELECT "Midtstillet henvisning til eksternt objekt", temp.objektid, f.fremsoegtid, f.def, f.medkarakteristika SQL type Evt. navn Forklaring SELECT null Kan finde henvisnings Bruges p.t objekter med ikke karakteristika og relationer SELECT Substitueringstes null t FROM TempObjektID_T temp, facttable1_t f Benyttet hvor WHERE f.fremsoegtid=temp.objektid and f.def="ProcObj" and f.MedKarakteristika="Henvisning" UNION SELECT "Venstrestillet henvisning til eksternt objekt", temp.objektid, f.fremsoegtid, f.def, f.medkarakteristika FROM TempObjektID_T temp, facttable1_t f, ObjRelationObj_T oro WHERE temp.objektid= oro.objekt2 and oro.objekt1=f.fremsoegtid and f.def="ProcObj" and f.MedKarakteristika="Henvisning" UNION SELECT "Højrestillet henvisning til eksternt objekt", temp.objektid, f.fremsoegtid, f.def, f.medkarakteristika FROM TempObjektID_T temp, facttable1_t f, ObjRelationObj_T oro WHERE temp.objektid= oro.objekt1 and oro.objekt2=f.fremsoegtid and f.def="ProcObj" and f.MedKarakteristika="Henvisning; 111 SELECT * from &Param1& Ver.:20120808 25 Report created: null 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 112 SELECT x.def from Relationer_Q as x where x.deftype="Relation" and x.def=&Param1& SELECT null Undersøger om null relationen er erklæret 113 SELECT firstx, lastx from KarakterFirstAndLast_Q where lastx = &Param1& SELECT null Undersøger om Karakteritika er erklæret, ogviser om det er DATA eller EndDef null 114 SELECT StaggingPD2_T.Action, StaggingPD2_T.ToBase, StaggingPD2_T.ObjektType, StaggingPD2_T.Karakteristika, StaggingPD2_T.KarakType, StaggingPD2_T.LocalID, StaggingPD2_T.NewID, StaggingPD2_T.DATA, StaggingPD2_T.GlobalTransOK FROM StaggingPD2_T WHERE StaggingPD2_T.ToBase=&Param1& AND StaggingPD2_T.GlobalTransOK=False; SELECT null Redigerings betingelser null 115 SELECT * from StaggingPD2_T where not exists (Select null from Objekt_Q as x where x.b.def1="BasisObj" and x.a.def1=StaggingPD2_T.objekttype ) and StaggingPD2_T.ToBase=&Param1& SELECT null Finder ALLE objekter fra StaggingPD2_T der skal importeres som er ugyldige null 116 UPDATE staggingPD2_T set Action= &Param2&, NewID=&Param1& , LocalTransOK=1 where LocalID=&Param1& UPDATE null Udfylder Stagging med null 'yderligere værdier', skal være sidste eftersom TranLocal sættes OK 117 UPDATE staggingPD2_T set Action= "NewObjNewID", NewID=0 where LocalID=val(&Param1&) and Action="?" UPDATE null Mangel på parametre null har hindret at Action ble sat korrekt tidligere Ver.:20120808 26 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 118 UPDATE staggingPD2_T set Action= "NewObjectWithID", NewID=val(&Param1&) where UPDATE LocalID=val(&Param1&) null UDGÅR, NewObjectWithID er ikke en intern funktion!!! null 119 INSERT into ObjDeclare_T (def,ID) values (&Param1&, &Param2&); INSERT null null null 121 UPDATE StaggingPD2_T set NewID=&Param1& where LocalID=&Param3& and globalTransOK=FALSE and ToBase=&Param2& UPDATE null null null 122 SELECT a.def1 as Lastx, b.def1 as typex, a.def2 as firstx, a.def1 as endtype from definitionshiraki_T as a, definitionshiraki_T as b where a.def1=b.def2 and b.def1="Karakter" and a.def2 in ("EndDef","DATA") union SELECT a.def1, c.def1,a.def2,c.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter" and a.def2 in ("EndDef","DATA") union SELECT a.def1,d.def1,a.def2,d.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter" and a.def2 in ("EndDef","DATA") union SELECT a.def1,e.def1,a.def2, e.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Karakter" and a.def2 in ("EndDef","DATA") SELECT KarakterFirstAnd null Last_Q null Ver.:20120808 27 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor UNION SELECT a.def1,f.def1,a.def2,f.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="Karakter" and a.def2 in ("EndDef","DATA"); 124 UPDATE staggingPD2_T set Action="ActiveAborted", LocaltransOK=1 where LocalID=0 and NewID=0 and LocalTransOK=FALSE and ToBase=&Param1& and FromBase=&Param1& UPDATE null Hvis en post aktivt null forkastes f.eks hvis filanvet eksisterer og der ikke ønsks dubletter, skal posten eksistere i stagging, men må ikke genediteres 125 INSERT into StaggingPD2_T (FromBase, Tobase, Action, ObjektType, Karakteristika, KarakType, DATA, LocalID, NewID) select &Param3&, &Param3&, "NewObjNewID", &Param2&,"RegistDato", "DATA", str(Now()), &Param1&, &Param1&; INSERT null Indsætter dags dato, null BEMÆRK det er en fejl at RegistDato er hårdtkodet, og at ordre også må indsættes hårdtkodet p.g.af mangel på parametre (som 40) 126 DELETE from StaggingPD2_T where DATA ="" or isnull(DATA); DELETE null Rydder op i Staggin tabellen hvad der er tilovers fra EDIT og NewID 128 select distinct LocalID,ObjektType, NewID From StaggingPD2_T where GlobaltransOK=FALSE SELECT null Udvidet med felterne null karakteristika og data, for at kunne bruges i PHP (déden Ver.:20120808 28 Report created: 14-01-2015 null ID SQL udtryk SQL type Evt. navn and action =&Param2& and tobase=&Param1& Forklaring Benyttet hvor redundant at kalde 38) 129 INSERT into StaggingPD2_T (FromBase, Tobase, Action, ObjektType, Karakteristika, KarakType, DATA, LocalID, NewID,LocalTransOK,GlobalTransOK) values( &Param2&, &Param2&, "DeleteObject", &Param3&,"-", "-", "-", &Param1&, &Param1&, 1, FALSE); INSERT null null 130 SELECT FactTable1_T.MedKarakteristika, FactTable1_T.DATA as titel, FactTable1_T_1.MedKarakteristika, FactTable1_T_1.DATA as Instruk, FactTable1_T_2.MedKarakteristika, FactTable1_T_2.DATA as Skuespiller, FactTable1_T_3.MedKarakteristika, FactTable1_T_3.DATA as Beskrivelse FROM ( ( FactTable1_T LEFT JOIN (select FremsoegtID, medkarakteristika, data from FactTable1_T as fb where MedKarakteristika='Instruktør') AS FactTable1_T_1 ON FactTable1_T.FremsoegtID = FactTable1_T_1.FremsoegtID ) LEFT JOIN (select FremsoegtID, medkarakteristika, data from FactTable1_T as fc where MedKarakteristika='Skuespiller') AS FactTable1_T_2 ON FactTable1_T.FremsoegtID = FactTable1_T_2.FremsoegtID ) LEFT JOIN (select FremsoegtID, medkarakteristika, data from FactTable1_T as fd where MedKarakteristika='Beskrivelse') AS FactTable1_T_3 ON FactTable1_T.FremsoegtID = FactTable1_T_3.FremsoegtID WHERE FactTable1_T.MedKarakteristika='Filmtitel' SELECT null Fremsøger specifikke Benyttes i felter med givne JASPER karakteristika fra DATAfeltet og præsenterer dem som en post. Her med outer jooin i from sætningen, til access og MYSQL? 132 UPDATE staggingPD2_T set GlobalTransOK=1 WHERE NewID=&Param1& and action=&Param2& and globalTransOK=0 UPDATE null Sætter GlobalTrans=ok Ver.:20120808 29 Report created: null 14-01-2015 fra delete objekt ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 133 insert into StaggingRelation_T (action, frombase, tobase, newid1, localid1, Relationstype, localid2, newid2, localtransok, globaltransok) values ( &Param1&, &Param2&, &Param3&, 0, 0, "?", 0, 0, false, false); INSERT null Indsætter relationer i Fra den Stagging Relations, se relationsskab også 287 ende form 134 select * from staggingRelation_T where globaltransOK=false and tobase=&Param1& SELECT null null null 135 update staggingrelation_t set NewID1=&Param1&, LocalID1=&Param2&, relationstype =¶m3& where localid1=0 and NewID1=0 and relationstype ="?" and LocalTransOK=FALSE UPDATE null null null 136 update staggingrelation_t set LocalTransOK=1, globaltransOK=1 where newid1=&Param1& and relationstype=&Param2& UPDATE null null I selve CreateRelati Ver.:20120808 30 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring and newid2=&Param3&; Benyttet hvor ons proceduren (Snyd!) 140 update StaggingPD2_T set Action=&Param1& where globalTransOK =FALSE and Action="?" UPDATE null sætter ordren i stagging tabellen, eftersom redigerings formen ikke kender den 142 INSERT into StaggingPD2_T (FromBase, Tobase, Action, ObjektType, Karakteristika, KarakType, DATA, LocalID, NewID, LocalTransOK, GlobalTransOK) values( &Param1&, &Param2&, &Param3&, "?", "?", "?", "?", 0, 0, FALSE, FALSE); INSERT null Generaliseret null indsættelse i Stagging, af DATA hvor FromBase=Tobase og LocalID og NewID er ukendte (0) Ver.:20120808 31 Report created: 14-01-2015 RedigPrepar e ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 143 UPDATE StaggingPD2_T set ObjektType=&Param1&, Karakteristika=&Param2&, KarakType=&Param3& where ObjektType="?" and Karakteristika="?" and Karaktype="?" and LocalTransOK=false UPDATE null null null 144 UPDATE StaggingPD2_T set DATA=&Param1&, LocalID=&Param2&, NewID=&Param3&, LocalTransOK=1 where DATA="?" and LocalID=0 and NewID=0 and LocalTransOK=false UPDATE null Bemærk LocalTrans=TRUE null 145 update staggingrelation_t set LocalID2=&Param1&, NewID2=&Param2& where localID2=0 and NewID2=0 and LocalTransOK=FALSE; UPDATE null null null 147 select * from TempObjektID_T SELECT null null null 148 insert into TempObjektID_T (user, ObjektID) values (&Param1&, &Param2&); INSERT null null null 149 SELECT c.ObjektTypeX FROM convert_t AS c SELECT null Finder den konverterede null Ver.:20120808 32 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn WHERE c.objektType=&Param1& and c.databasex=&Param2& ; Forklaring Benyttet hvor objekttype til en eksportbase 150 SELECT * from facttable1_t where fremsoegtID=&Param1& and MedKarakteristika<>"Åbn program" SELECT null null null 152 SELECT * FROM StaggingPD2_T AS s1 where s1.ToBase=&Param1& and s1.Action=&Param2& and s1.TransactID=&Param3& SELECT null null null 153 select * from DBInstans_t where navnid=&Param1& and jaspermysqlserver=&Param2& SELECT null Henter link adresser openreportvi ew 155 SELECT * from DBInstans_T where visible=1 and lastused=1; SELECT null finder de databaser null der skal kunne vælges, samt den sidt åbnede, som der sorteres efter og derved vises i Form1 156 select * from staggingPD2_T where globaltransOK=FALSE order by transactID SELECT null null null 157 INSERT into StaggingPD2_T (Action,FromBase, ToBase, Karakteristika, KarakType, DATA, ObjektType, LocalTransOK, GlobalTransOK,LocalID, NewID) SELECT "NewObjNewID", &Param1&, &Param2&, f.medkarakteristika, f.definition , f.data, f.def, 1, false, t.objektid, 0 FROM facttable1_t AS f, tempobjektid_t AS t WHERE t.objektid=&Param3& INSERT null Svarer til 19, men indsætter de værdier udenom convert_t null Ver.:20120808 33 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor null and t.objektid=f.fremsoegtid ; 158 Delete from FactTable1_t; DELETE null null 159 update staggingRelation_t set NewID2=val(&Param1&) where RelationsType="ActivateDok" and tobase=&Param2& and Action=&Param3& UPDATE null sen opdatering af ALERT fra stagging relation, redigMange_ eftersom F redigmange_F poster slettes ved tidligere opdatering 160 SELECT * from staggingPD2_T SELECT null null null 161 update facttable1_t set DATA=replace(DATA, "\", "/") where medkarakteristika="FilKatalog" or medkarakteristika="Åbn program" UPDATE null Burde gøres i Jasperreports, men ODBC problemer hindrer det i Jasper SQL null 162 SELECT a.def1 as ObjType, a.def2 as First, '--' as second, '---' as third,'----' as fourth, b.def1 as Last,b.def2 as endtype, a.mandatory, b.deftext from definitionshiraki_T a, definitionshiraki_T b where a.def1 in ('BasisObj','Karakter','Relation') and a.def2=b.def1 and b.def2 in ('EndDef', 'DATA') union SELECT a.def1 as Type, a.def2 as First, b.def2 as second, '---' as third, '----' as fourth, c.def1 as Last,c.def2 as endtype, c.mandatory, c.deftext from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1 in ('BasisObj','Karakter','Relation') and a.def2=b.def1 and b.def2=c.def1 SELECT VIEW DefinitionHiraki_ Q View der viser samtlige poster fra Definitionshitraki samt mandatory, vist hirakisk. Dbinstans_T viser den aktuelle base. Benyttes af Form1_F(ude n aktuel basevisning) Ver.:20120808 34 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor DELETE null Sletter alle mandatory Build fra hirakiet, som faccttable og forberedelse til Hiraki indsættelse af nye, og fjernelse af permanent slettede. Kører sammen med 164 og 165 and c.def2 in ('EndDef', 'DATA') union SELECT a.def1 as Type, a.def2 as First, b.def2 as second,c.def2 as third, '----' as fourth, d.def1 as Last,d.def2 as endtype, d.mandatory, d.deftext from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1 in ('BasisObj','Karakter','Relation') and a.def2=b.def1 and b.def2=c.def1 and c.def2=d.def1 and d.def2 in ('EndDef', 'DATA') union SELECT a.def1 as Type, a.def2 as First, b.def2 as second,c.def2 as third,d.def2, e.def1 as Last,d.def2 as endtype, e.mandatory, e.deftext from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1 in ('BasisObj','Karakter','Relation') and a.def2=b.def1 and b.def2=c.def1 and c.def2=d.def1 and d.def2=e.def1 and e.def2 in ('EndDef', 'DATA') order by 1,2 ; 163 delete * from DefinitionsHiraki_T where mandatory =1; Ver.:20120808 35 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 164 delete from DefinitionsHiraki_T as d where exists (select null from mandatory_t as m where m.def1=d.def1 and m.def2=d.def2); DELETE null Renser Hiraki for mandatory (se også 163 og 165) Erstatter 163??? null 165 insert into DefinitionsHiraki_T (Def1,Def2, mandatory, deftext, DATA_ID) select def1, def2 , 1, deftext,data_id from mandatory_T; INSERT null Opbygger et samlet hiraki (163,164,165) null 166 delete * from temptemp2 DELETE null null null 167 update dbinstans_t set lastused=FALSE UPDATE null Forbereder 168 null 168 update dbinstans_t set lastused=1 where id like '*01' and gereba_type =&Param1& and NavnID = &Param2& UPDATE null null null 169 DELETE from facttable1_t1 DELETE null Sletter MYSQL vebsitets facttabel null 170 INSERT into facttable1_t1 select * from facttable1_t INSERT null Overfører samtlige facttable data til MYSQL website null 171 DELETE * from dbinstans_t1 DELETE null SKAL SLETTES null 172 INSERT into dbinstans_t1 select * from dbinstans_t; INSERT null SKAL SLETTES null 173 delete * from staggingpd2local_t DELETE null Skal ændres til at overføre Mysql til access null Ver.:20120808 36 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 174 insert into staggingpd2_t select FromBase , ToBase , Action , ObjektType , Karakteristika , KarakType , DATA , LocalID, NewID, LocalTransOK, GlobalTransOK from staggingpd2local_t INSERT null Alt UNDTAGEN transactid indsættes null 175 delete * from objrelationobj_t1 DELETE null null null 176 insert into objrelationobj_t1 select * from objrelationobj_t INSERT null null null 177 Delete * from Definitionshiraki_t1 DELETE null null null 178 INSERT into Definitionshiraki_t1 select * from Definitionshiraki_t INSERT null null null 179 update staggingrelation_T inner join staggingPD2_T on staggingPD2_T.localID =staggingrelation_T.localid1 set staggingrelation_T.NewID1=staggingPD2_T.NewID where staggingrelation_T.toBase=staggingPD2_T.ToBase and staggingrelation_T.NewID1=0 and staggingPD2_T.GlobalTransOK=1 and staggingrelation_T.globaltransOK=FALSE and staggingPD2_T.ToBase=&Param1& ; UPDATE null Hvis objekter er lagt i staggingpd2_t samtidig med at de er lagt i staggingrelation_t, kendes ID, som skal indgå i ralationen ikke.. Derfor skal der være sat nogle temporære ID der relaterer til stagging_t. BEMÆRK: disse sættes ikke fra gereba, her dannes objekter altid før de indgår i relationer, men fra udefra kommende Udføres ummidelbar før relationer indsættes. Ver.:20120808 37 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor import. Se også 182 180 insert into staggingrelation_t select Action , FromBase , ToBase , NewID1, LocalID1, RelationsType , NewID2 , LocalID2, LocalTransOK, GlobalTransOK from staggingrelationlocal_t; INSERT null Alt UNDTAGEN transactid indsættes null 181 delete * from staggingrelationlocal_t; DELETE null null null 182 update staggingrelation_T inner join staggingPD2_T on staggingPD2_T.localID =staggingrelation_T.localid2 set staggingrelation_T.NewID2=staggingPD2_T.NewID where staggingrelation_T.toBase=staggingPD2_T.ToBase and staggingrelation_T.NewID2=0 and staggingPD2_T.GlobalTransOK=1 and staggingrelation_T.globaltransOK=FALSE and staggingPD2_T.ToBase=&Param1& ; UPDATE null se 179 null 183 select t.objektid, f.data, f2.data from facttable1_t as f, facttable1_t as f2, tempobjektid_t as t where f.fremsoegtid=t.objektid and f2.fremsoegtid=t.objektid and f2.medkarakteristika='Filnavn' and f.medkarakteristika='Åbn program' and f.def='Dokument' and f2.def='Dokument'; SELECT null null null 184 update facttable1_t inner join definitionshiraki_t on definitionshiraki_t.def2 = facttable1_t.medkarakteristika set medkarakteristika=definitionshiraki_t.def1 where facttable1_t.definition="EndDef"; UPDATE null Ved typen EndDef er BuildFacttabl medkarakteristika=DA e TA, derfor er definitionen forud for Ver.:20120808 38 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor medkarakteristika mere sigende 185 INSERT into StaggingPD2_T (Action,FromBase, ToBase, Karakteristika, KarakType, DATA, ObjektType, LocalTransOK, LocalID) SELECT "EditObject" ,&Param2&,&Param3&, f.DATA, f.definition, f.data, f.def, false, f.fremsoegtid FROM facttable1_t AS f WHERE f.fremsoegtid=&Param1& and UnionNr<>"U3" and f.definition="EndDef" ; INSERT null Indsættelse af DATA null hvor medkarakteristika=End Def. Hvis EndDef er det nødvendigt at insætte MedKarakteristika = DATA, fordi facttablen nu viser et højere niveau af enddef karakteristika. (se 35) 186 insert into StaggingRelation_T (action, frombase, tobase, newid1, localid1, Relationstype, localid2, newid2, localtransok, globaltransok) select "ExpWithID", &Param1&, &Param2&, &Param3&, &Param3&, oro.Relationstype, oro.objekt2, oro.objekt2, INSERT null null Ver.:20120808 39 Report created: null 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor false, false From ObjRelationObj_T as oro where oro.objekt1=&Param3& 189 select Newid1, newid2 from staggingrelation_t where localid1=&Param1& or localid2=&Param1& SELECT null null null 190 update dbinstans_t1 set lastused=1 where lastused=1 UPDATE null Updater på dbinstans_t for at udløser trigger en gang (INSERT udløste mange) hvor tigger viderefører til respektive gerebabaser. ved overførsel til MYSL test, hvor tigger viderefører til respektive gerebabaser. 191 select 'Rapport aktiveret' as EVENT, FromBase, ToBase,Action,ObjektType,Karakteristika,KarakType, DATA,LocalID,NewID,LocalTransOK,GlobalTransOK,TransactID from staggingpd2_t where ToBase='xLog' and objektType='LogInfo' union select 'Indførsel/redigering af data' as EVENT, FromBase, ToBase,Action,ObjektType,Karakteristika,KarakType, DATA,LocalID,NewID,LocalTransOK,GlobalTransOK,TransactID from staggingpd2_t where objektType <> 'LogInfo' union select 'DB overført til server' as EVENT, FromBase, ToBase,Action,ObjektType,Karakteristika,KarakType, DATA,LocalID,NewID,LocalTransOK,GlobalTransOK,TransactID from staggingpd2_t where ToBase<>'xLog' and objektType='LogInfo' order by 1,9 SELECT VIEW null null Rapporten stagging, der viser log oplysninger om aktiverede rapporter, overførsler melem Access og MYSQL, samt øvrige redigeringer Ver.:20120808 40 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 192 CREATE TABLE `convert_t` ( `ObjektType` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL, `Field` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL, `FieldX` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT NULL, `ObjektTypeX` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT NULL, `DatabaseX` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL, PRIMARY KEY (`Field`,`ObjektType`,`DatabaseX`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci CREATE TABLE null MYSQL null 195 CREATE TABLE `definitionshiraki_t` ( `DATA_ID` int(11) DEFAULT NULL, `DEF1` varchar(255) NOT NULL, `DEF2` varchar(255) NOT NULL, `Mandatory` tinyint(1) NOT NULL DEFAULT FALSE, `DEFTEXT` text, PRIMARY KEY (`DEF1`,`DEF2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; CREATE TABLE null MYSQL null 196 CREATE TABLE `facttable1_t` ( `FREMSOEGTID` int(11) NOT NULL, `UNIONNR` varchar(10) COLLATE utf8_danish_ci DEFAULT NULL, `DEF` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL, `MEDKARAKTERISTIKA` varchar(255) COLLATE utf8_danish_ci NOT NULL, `DATA` text COLLATE utf8_danish_ci, `DEFINITION` varchar(255) COLLATE utf8_danish_ci NOT NULL, `PRIORITET` int(11) DEFAULT NULL, `KEY` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`KEY`), KEY `ind_karakteristika` (`MEDKARAKTERISTIKA`) CREATE TABLE null MYSQL, DEFAULT null CHARSET=utf8 BEMÆRK det vigtige index der sikrer view kan udføres!! SE SQL 311 Ver.:20120808 41 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor ) ENGINE=InnoDB AUTO_INCREMENT=4953 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; 197 CREATE TABLE `mandatory_t` ( `DATA_ID` int(11) DEFAULT NULL, `DEF1` varchar(255) NOT NULL, `DEF2` varchar(255) NOT NULL, `DEFTEXT` text, PRIMARY KEY (`DEF1`,`DEF2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; CREATE TABLE null MYSQL null 198 CREATE TABLE `objdeclare_t` ( `Def` varchar(100) DEFAULT NULL, `ID` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE null MYSQL null 199 CREATE TABLE `objrelationobj_t` ( `OBJEKT1` int(11) NOT NULL, `RELATIONSTYPE` varchar(255) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL, `OBJEKT2` int(11) NOT NULL, PRIMARY KEY (`OBJEKT1`,`RELATIONSTYPE`,`OBJEKT2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE null MYSQL null 200 CREATE TABLE `relationdata_t` ( `Objekt_id` int(11) NOT NULL, `Data_id` int(11) NOT NULL, PRIMARY KEY (`Objekt_id`,`Data_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; CREATE TABLE null MYSQL null 201 CREATE TABLE `sqlexpresions_t` ( `SqlID` int(11) NOT NULL, `SQL_exp` mediumtext CHARACTER SET utf8, CREATE TABLE null MYSQL utf8 null Ver.:20120808 42 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor `Type` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `Navn` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `Explain` mediumtext CHARACTER SET utf8, `Hvor` mediumtext CHARACTER SET utf8, `FunctionGroup` int(11) DEFAULT NULL, PRIMARY KEY (`SqlID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; 202 CREATE TABLE `staggingpd2local_t` ( `FromBase` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `ToBase` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `Action` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `ObjektType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `Karakteristika` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `KarakType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `DATA` text COLLATE utf8_danish_ci, `LocalID` int(11) DEFAULT NULL, `NewID` int(11) DEFAULT NULL, `LocalTransOK` tinyint(1) DEFAULT '0', `GlobalTransOK` tinyint(1) DEFAULT '0', `TransactID` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`TransactID`) ) ENGINE=InnoDB AUTO_INCREMENT=6388 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci CREATE TABLE null MYSQL null 203 CREATE TABLE `staggingrelation_t` ( `Action` varchar(15) COLLATE latin1_danish_ci DEFAULT NULL, `FromBase` varchar(100) COLLATE latin1_danish_ci DEFAULT NULL, `ToBase` varchar(100) COLLATE latin1_danish_ci DEFAULT NULL, `NewID1` int(11) DEFAULT NULL, `LocalId1` int(11) DEFAULT NULL, `RelationsType` varchar(50) COLLATE latin1_danish_ci DEFAULT NULL, `LocalId2` int(11) DEFAULT NULL, `NewID2` int(11) DEFAULT NULL, CREATE TABLE null MYSQL null Ver.:20120808 43 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor `LocalTransOK` tinyint(1) DEFAULT '0', `GlobalTransOK` tinyint(1) DEFAULT '0', `RowID` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`RowID`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; 204 CREATE TABLE `tempobjektid_t` ( `USER` varchar(50) NOT NULL, `OBJEKTID` int(11) NOT NULL, `SORT` int(11) DEFAULT NULL, PRIMARY KEY (`OBJEKTID`,`USER`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; CREATE TABLE null MYSQL null 205 CREATE TABLE `temptemp2` ( `Last` varchar(50) NOT NULL, `def1` varchar(50) DEFAULT NULL, `def2` varchar(50) DEFAULT NULL, `First` varchar(50) DEFAULT NULL, PRIMARY KEY (`Last`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; CREATE TABLE null MYSQL null 206 CREATE TABLE `changelog` ( `Dato` varchar(40) NOT NULL, `Tekst` mediumtext, `Hovedversion` varchar(100) DEFAULT NULL, PRIMARY KEY (`Dato`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; CREATE TABLE null null null 207 CREATE TABLE `staggingpd2_t` ( `FromBase` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `ToBase` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `Action` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `ObjektType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, CREATE TABLE null som staggingpd2_T null men beregnet for COPY og CDAT baser Ver.:20120808 44 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor `Karakteristika` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `KarakType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `DATA` text COLLATE utf8_danish_ci, `LocalID` int(11) DEFAULT NULL, `NewID` int(11) DEFAULT NULL, `LocalTransOK` tinyint(1) DEFAULT '0', `GlobalTransOK` tinyint(1) DEFAULT '0', `TransactID` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`TransactID`) ) ENGINE=InnoDB AUTO_INCREMENT=6388 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci 208 CREATE TABLE `staggingrelationlocal_t` ( `Action` varchar(15) COLLATE utf8_danish_ci DEFAULT NULL, `FromBase` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, `ToBase` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, `NewID1` int(11) DEFAULT NULL, `LocalId1` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `RelationsType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `LocalId2` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `NewID2` int(11) DEFAULT NULL, `LocalTransOK` varchar(1) COLLATE utf8_danish_ci DEFAULT NULL, `GlobalTransOK` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT NULL, `RowID` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`RowID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci CREATE TABLE null som null staggingrelation_T men beregnet for COPY og CDAT baser 210 CREATE TABLE `data_t` ( `DATA` mediumtext, `Type` varchar(100) DEFAULT NULL, `DATA_ID` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`DATA_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=9633 DEFAULT CHARSET=utf8 CREATE TABLE null null Ver.:20120808 45 Report created: null 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 211 CREATE TABLE `dataregler_t` ( `ObjektKlasse` varchar(100) NOT NULL DEFAULT '', `TilladtKarakRela` varchar(100) NOT NULL DEFAULT '', `Obligatorisk` tinyint(1) DEFAULT '0', `Prioritet` int(11) DEFAULT NULL, PRIMARY KEY (`ObjektKlasse`,`TilladtKarakRela`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci; CREATE null null null 212 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id as ID, ucase("U2") as unionnr, o.def, data_t.type as medkarakteristika, data_t.data, ucase("DATA") as definition from objdeclare_t o, data_t, (select data_id, objekt_id from relationdata_t) ra where ra.objekt_id=o.id and ra.data_id=data_t.data_id and data_t.data<>data_t.type; INSERT null Opsplittet SQL 28 fordi null ODBC til MYSQL ikke virker med UNION 213 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id as ID, ucase("U1") as unionnr, o.def, data_t.type as karakteristika, data_t.data, "EndDef" as definition from objdeclare_t o, INSERT null Opsplittet SQL 28 fordi null ODBC til MYSQL ikke virker med UNION COLLATE=utf8_danish_ci; Ver.:20120808 46 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor data_t, (select data_id, objekt_id from relationdata_t) ra where ra.objekt_id=o.id and ra.data_id=data_t.data_id and data_t.data=data_t.type; 214 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id, "U3", o.def, "Åbn program", Dbinstans_T.BasisArkiv & d2.data & d.data as sti, "Fremsøgt dok" from objdeclare_t o, relationData_t rd, relationData_t rd2, data_t d, data_t d2, DBInstans_t where o.id=rd.objekt_id and o.id=rd2.objekt_id and o.def="Dokument" and rd.data_id=d.data_id and rd2.data_id=d2.data_id and d.type="Filnavn" and d2.type="FilKatalog" and DBInstans_t.lastused=1; INSERT null Opsplittet SQL 28 fordi dan ODBC til MYSQL ikke facttable1_t virker med UNION OMSKREVET til 263 til PHP-MYSQL 216 INSERT INTO StaggingPD2_T ( FromBase, Tobase, action , ObjektType, Karakteristika, KarakType, DATA, LocalID, NewID ) SELECT &Param3&, &Param3&, "?", a.def1, dr.TilladtKarakRela, x.def2, "", &Param1&, 0 FROM Dataregler_T AS dr, definitionshiraki_T AS a, definitionshiraki_T AS x WHERE dr.ObjektKlasse=a.def1 And a.def2="EndDef" And dr.TilladtKarakRela=x.def1 INSERT null opsplittet sql 34 Ver.:20120808 47 Report created: 14-01-2015 null ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor And a.def1=&Param2&; 217 INSERT INTO StaggingPD2_T ( FromBase, Tobase, action , ObjektType, Karakteristika, KarakType, DATA, LocalID, NewID ) SELECT &Param3&, &Param3&, "?", a.def1, dr.TilladtKarakRela, x.def2, "", &Param1&, 0 FROM Dataregler_T AS dr, definitionshiraki_t b, definitionshiraki_T AS a, definitionshiraki_T AS x WHERE dr.ObjektKlasse=b.def1 and b.def2=a.def1 And a.def2="EndDef" And dr.TilladtKarakRela=x.def1 And a.def1=&Param2&; INSERT null null null 218 INSERT INTO StaggingPD2_T ( FromBase, Tobase, action , ObjektType, Karakteristika, KarakType, DATA, LocalID, NewID ) SELECT &Param3&, &Param3&, "?", a.def1, dr.TilladtKarakRela, x.def2, "", &Param1&, 0 FROM Dataregler_T AS dr, definitionshiraki_t b, definitionshiraki_T AS a, definitionshiraki_T c, definitionshiraki_T AS x WHERE dr.ObjektKlasse= c.def1 and c.def2=b.def1 and b.def2=a.def1 And a.def2="EndDef" And dr.TilladtKarakRela=x.def1 And a.def1=&Param2&; INSERT null null null 219 INSERT INTO StaggingPD2_T ( FromBase, Tobase, action , ObjektType, Karakteristika, INSERT null null null Ver.:20120808 48 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor SELECT VIEW KarakOgRela_Q null null KarakType, DATA, LocalID, NewID ) SELECT &Param3&, &Param3&, "?", a.def1, dr.TilladtKarakRela, x.def2, "", &Param1&, 0 FROM Dataregler_T AS dr, definitionshiraki_t b, definitionshiraki_T AS a, definitionshiraki_T c, definitionshiraki_T AS x, definitionshiraki_T d where dr.ObjektKlasse= d.def1 and d.def2=c.def1 and c.def2=b.def1 and b.def2=a.def1 and a.def2= "EndDef" and dr.TilladtKarakRela=x.def1 And a.def1=&Param2&; 220 SELECT a.def1 as field1, b.def1 as field2,b.def2 as field3,a.def2 as field4 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1, c.def1,c.def2,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,d.def1,d.def2,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,e.def1,e.def2,a.def2 Ver.:20120808 49 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") UNION SELECT a.def1,f.def1,f.def2,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1, b.def1,b.def2, a.def2 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="Relation" and a.def2="EndDef" union SELECT a.def1, c.def1,c.def2,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Relation" and a.def2="EndDef" union SELECT a.def1,d.def1,d.def2,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Relation" and a.def2="EndDef" union SELECT a.def1,e.def1,e.def2,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Relation" Ver.:20120808 50 Report created: 14-01-2015 Benyttet hvor ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor SELECT VIEW Basisarv_Q null null and a.def2="EndDef" UNION SELECT a.def1,f.def1,f.def2,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="Relation" and a.def2="EndDef"; 221 SELECT a.def1, b.def1, b.def2 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="BasisObj" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1, c.def1, c.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="BasisObj" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,d.def1, d.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="BasisObj" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,e.def1, e.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="BasisObj" and (a.def2="EndDef" or a.def2="DATA") UNION SELECT a.def1,f.def1, f.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="BasisObj" and (a.def2="EndDef" or a.def2="DATA") ORDER BY 1; Ver.:20120808 51 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 223 SELECT a.def1 as Lastx, b.def1 as typex, a.def2 as firstx, a.def1 as endtype from definitionshiraki_T as a, definitionshiraki_T as b where a.def1=b.def2 and b.def1="Karakter" and a.def2 in ("EndDef","DATA") union SELECT a.def1, c.def1,a.def2,c.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter" and a.def2 in ("EndDef","DATA") union SELECT a.def1,d.def1,a.def2,d.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter" and a.def2 in ("EndDef","DATA") union SELECT a.def1,e.def1,a.def2, e.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Karakter" and a.def2 in ("EndDef","DATA") UNION SELECT a.def1,f.def1,a.def2,f.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="Karakter" and a.def2 in ("EndDef","DATA") order by 1; SELECT VIEW KarakterFirstand Last_q null null 224 SELECT a.def1 as def, b.def1 as deftype, a.def2 as endtype from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="Relation" SELECT VIEW Relationer_Q null rowsource for combo2, relationer på Ver.:20120808 52 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring and a.def2="EndDef" union SELECT a.def1, c.def1, a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Relation" and a.def2="EndDef" union SELECT a.def1, d.def1, a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Relation" and a.def2="EndDef" union SELECT a.def1, e.def1, a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Relation" and a.def2="EndDef" UNION SELECT a.def1, f.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="Relation" and a.def2="EndDef"; 225 SELECT a.def1, b.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1, c.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter" Ver.:20120808 53 Benyttet hvor soeg_f SELECT VIEW Karakteristika_Q null Report created: rowsource på combo Karakteristika få Soeg_F 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor SELECT VIEW Thesaurus_Q null rowsource combo thesaurus_fel t på Soeg_F and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,d.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,e.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") UNION SELECT a.def1,f.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="Karakter" and (a.def2="EndDef" or a.def2="DATA") order by 1; 226 SELECT a.def1, b.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="Thesaurus" and a.def2="EndDef" union SELECT a.def1, c.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Thesaurus" and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,d.def1,a.def2 Ver.:20120808 54 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Thesaurus" and a.def2="EndDef" union SELECT a.def1,e.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Thesaurus" and a.def2="EndDef" UNION SELECT a.def1,f.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1="Thesaurus" and a.def2="EndDef" order by 1; 227 SELECT DBInstans_T.NavnID FROM DBInstans_T WHERE DBInstans_T.visible=True and gereba_type='CPROD'; SELECT null Implementeret direkte i rowsource for egenskabsark valg af produktions database form1 228 select db2.navnid, db2.beskrivelse, db2.gereba_type , db2.dsn from dbinstans_t as db1, dbinstans_t as db2 where db1.navnid=db2.navnid and db1.lastused=1 and db2.gereba_type<>'CPROD'; SELECT null null Ver.:20120808 55 Report created: combo ved database valg <> produktionsb ase, men samme base navn 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 229 SELECT KarakOgRela_Q.field1, KarakOgRela_Q.field2, KarakOgRela_Q.field3, KarakOgRela_Q.field4 FROM KarakOgRela_Q WHERE KarakOgRela_Q.field2="Karakter" ORDER BY KarakOgRela_Q.field1; SELECT null null combo på opdateringskr av_f 230 INSERT into TempObjektID_T (ObjektID,user) select u.id, "xx" from (SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as f4 where f1.MedKarakteristika Like &Param1& and f1.definition="DATA" and f2.MedKarakteristika like &Param4& and f3.DATA like &Param2& and f4.Def Like &Param3& and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID and f3.fremsoegtID=f4.fremsoegtID UNION SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3 where (f1.DATA = &Param1& or f1.data=&Param4&) and f1.definition="EndDef" and f2.DATA like &Param2& and f3.Def Like &Param3& and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID ) as u; INSERT null enkelt fritekst, samt karakteristika null 231 INSERT into TempObjektID_T (ObjektID,user) select u.id, "xx" from (SELECT ObjDeclare_T.id as id FROM ObjRelationObj_T, ObjDeclare_T where (ObjDeclare_T.id=ObjRelationObj_T.objekt1 or ObjDeclare_T. INSERT null ombygget til 292 null Ver.:20120808 56 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor INSERT null Og operator null id=ObjRelationObj_T.objekt2) and ObjRelationObj_T.relationstype= &Param5& and &Param2&="0" UNION SELECT f1.FremsoegtID as id FROM Facttable1_t as f1 where f1.fremsoegtID=val(&Param6&) and &Param2& ="0") as u; 232 INSERT into TempObjektID_T (ObjektID,user) select u.id, "xx" from (SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as f4, Facttable1_t as f5 where f1.MedKarakteristika Like &Param1& and f1.definition="DATA" and f2.MedKarakteristika like &Param4& and f3.DATA like &Param2& and f5.DATA like &Param5& and f4.Def Like &Param3& and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID and f3.fremsoegtID=f4.fremsoegtID and f4.fremsoegtID=f5.fremsoegtID UNION SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3 where (f1.DATA = &Param1& or f1.data=&Param4&) and f1.definition="EndDef" and f2.DATA like &Param2& and f3.Def Like &Param3& and f1.FremsoegtID= f2.FremsoegtID and f2.FremsoegtID= f3.FremsoegtID ) as u; Ver.:20120808 57 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 235 DELETE * from convert_t1 DELETE null null null 236 insert into convert_t1 select * from convert_t INSERT null null null 237 select max(data_id) from data_t SELECT null se også ombygget 253 null PHP 238 select * from temptemp2; SELECT null null null 239 select data_id from data_t where data=&Param1& and type=&Param1&; SELECT null null null 240 INSERT into TempObjektID_T (ObjektID,user) select u.id, "xx" from (SELECT distinct f3.FremsoegtID as id FROM Facttable1_t as f3, Facttable1_t as f5 where (f3.DATA like &Param3& or f5.DATA like &Param5&) and f3.FremsoegtID= f5.FremsoegtID ) as u; INSERT null søgning med OR operator, kun de 2 friteksfelter kan benyttes null 241 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id as ID, "U2" as unionnr, o.def, data_t.type as medkarakteristika, data_t.data, "DATA" as definition from objdeclare_t o, data_t, (select data_id, objekt_id from relationdata_t) ra where o.id=&Param1& and ra.objekt_id=o.id INSERT null som 212 men med givent objid null Ver.:20120808 58 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor and ra.data_id=data_t.data_id and data_t.data<>data_t.type; 242 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id as ID, "U1" as unionnr, o.def, data_t.type as karakteristika, data_t.data, "EndDef" as definition from objdeclare_t o, data_t, (select data_id, objekt_id from relationdata_t) ra where o.id=&Param1& and ra.objekt_id=o.id and ra.data_id=data_t.data_id and data_t.data=data_t.type; INSERT null som 213 med givent objid null 243 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id, "U3", o.def, "Åbn program", Dbinstans_T.BasisArkiv & d2.data & d.data as sti, "Fremsøgt dok" from objdeclare_t o, relationData_t rd, relationData_t rd2, data_t d, data_t d2, DBInstans_t where o.id=&Param1& and o.id=rd.objekt_id and o.id=rd2.objekt_id and o.def="Dokument" and rd.data_id=d.data_id and rd2.data_id=d2.data_id INSERT null som 214 med givent objid null Ver.:20120808 59 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor and d.type="Filnavn" and d2.type="FilKatalog" and DBInstans_t.lastused=1; 244 Delete from FactTable1_t where fremsoegtid= &Param1&; DELETE null null null 245 update facttable1_t set DATA=replace(DATA, "\", "/") where ( medkarakteristika="FilKatalog" or medkarakteristika="Åbn program") and fremsoegtid=¶m1& UPDATE null null null 246 update facttable1_t inner join dataregler_t on dataregler_t.tilladtkarakrela=facttable1_t.medkarakteristika set facttable1_t.prioritet=dataregler_t.prioritet where dataregler_t.tilladtkarakrela=facttable1_t.medkarakteristika and fremsoegtid=&Param1& and facttable1_t.def=dataregler_t.objektklasse; UPDATE null null null 247 select data from facttable1_t as f, tempobjektid_t as t where f.fremsoegtid= t.objektid and medkarakteristika='Åbn program' and user like &Param1& SELECT null null null 249 update staggingpd2_t set DATA=replace(DATA, "/", "\") where globaltransok=0 and (karakteristika="FilKatalog" or karakteristika="Åbn program" ); UPDATE null For at sikre ensartyethed i sti angivelser, BEMÆRK dette er hårdtkodet til et WINDOW filsystem Fra RedigMange _F ved indsættelse at karakteristika i flere objekter 250 SELECT a.def1 as field1, b.def1 as field2 ,a.def2 as field3 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1 in ("BasisObj","system") SELECT null Erstatter Benyttes til kombinationen af 102 PHP og 84. tjekker Ver.:20120808 60 Report created: 14-01-2015 ID SQL udtryk SQL type and a.def2="EndDef" and a.def1=&Param1& union SELECT a.def1, c.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1 in ("BasisObj","system") and a.def2="EndDef" and a.def1=&Param1& union SELECT a.def1,d.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1 in ("BasisObj","system") and a.def2="EndDef" and a.def1=&Param1& union SELECT a.def1,e.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1 in ("BasisObj","system") and a.def2="EndDef" and a.def1=&Param1& UNION SELECT a.def1,f.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1 in ("BasisObj","system") and a.def2="EndDef" and a.def1=&Param1& order by 1; Ver.:20120808 61 Evt. navn Forklaring existensen af objekter, i PHP Report created: 14-01-2015 Benyttet hvor ID 251 SQL udtryk SELECT a.def1 as last, b.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="Karakter" and a.def1=&Param1& and a.def2=&Param2& union SELECT a.def1, c.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter" and a.def1=&Param1& and a.def2=&Param2& union SELECT a.def1,d.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter" and a.def1=&Param1& and a.def2=&Param2& union SELECT a.def1,e.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Karakter" and a.def1=&Param1& and a.def2=&Param2& UNION SELECT a.def1,f.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=&Param1& and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 Ver.:20120808 62 SQL type Evt. navn Forklaring SELECT KarakteristikaRel KarakteristikaRela_Q null a_Q tjekker existensen af karakteristika og relationer, i PHP, samt at karaktype er korrekt Report created: 14-01-2015 Benyttet hvor ID SQL udtryk SQL type Evt. navn Forklaring and e.def1=f.def2 and f.def1="Karakter" and a.def2=&Param2& union SELECT a.def1, b.def1, a.def2 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1="Relation" and a.def1=&Param1& and a.def2=&Param2& union SELECT a.def1, c.def1, a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1="Relation" and a.def1=&Param1& and a.def2=&Param2& union SELECT a.def1, d.def1, a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Relation" and a.def1=&Param1& and a.def2=&Param2& union SELECT a.def1, e.def1, a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1="Relation" and a.def1=&Param1& and a.def2=&Param2& UNION SELECT a.def1, f.def1,a.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e. Ver.:20120808 63 Report created: 14-01-2015 Benyttet hvor ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor def1=f.def2 and f.def1="Relation" and a.def1=&Param1& and a.def2=&Param2& ORDER BY 1; 252 INSERT into ObjDeclare_T (Def, id ) select (&Param1&), (max(id)+1) from ObjDeclare_T; INSERT null Erstatter 72 efter null sletning af objdeclare , local, _t 253 select data_id from data_t where type=&Param1& and DATA=&Param2& SELECT null 237 ombygget til PHP null 254 update staggingrelation_T inner join staggingPD2_T on staggingPD2_T.localID =staggingrelation_T.localid1 set staggingrelation_T.NewID1=staggingPD2_T.NewID where staggingrelation_T.toBase=staggingPD2_T.ToBase and staggingrelation_T.NewID2=0 and staggingrelation_T.RowID=&Param1& ; UPDATE null erstatter 179 for PHP, null 134 har sikret øvrige betingelser 255 update staggingrelation_T inner join staggingPD2_T on staggingPD2_T.localID =staggingrelation_T.localid2 set staggingrelation_T.NewID2=staggingPD2_T.NewID where staggingrelation_T.toBase=staggingPD2_T.ToBase and staggingrelation_T.NewID2=0 and staggingrelation_T.RowID=&Param1& ; UPDATE null erstatter 182 for PHP, null 134 har sikret øvrige betingelser 256 insert into objrelationobj_T (objekt1, relationstype, objekt2) values(&Param1&,&Param2&, &Param3& ) ; INSERT null null null 257 select Newid1, newid2 from staggingrelation_t where rowid=&Param1& ; SELECT null 189 til PHP null Ver.:20120808 64 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 258 update staggingrelation_t set LocalTransOK=1, globaltransOK=1 where newid1=&Param1& and relationstype=&Param2& and newid2=&Param3&; UPDATE null 87 ombygget til PHP null 259 update facttable1_t set DATA=replace(DATA, '\\', '/') where (medkarakteristika=&Param1& or medkarakteristika=&Param2&); UPDATE null PHP syntaks (161) null 260 update FactTable1_t set prioritet=99 where (isnull(prioritet) or prioritet=0); UPDATE null null Buildfacttable 261 INSERT into StaggingPD2_T (FromBase, Tobase, Action, ObjektType, Karakteristika, KarakType, DATA, LocalID, NewID, LocalTransOK, GlobalTransOK) values( &Param1&, &Param2&, &Param3&, &Param4&, &Param5&, '?', "?", &Param6&, 0, FALSE, FALSE); INSERT null 142 omskrevet Mærkelig forhold ved insættelse af felter nødvendiggør at DATA skal tvinges til UPPER case, for at testen på data exist virker null Ver.:20120808 65 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 262 UPDATE StaggingPD2_T set DATA=&Param1&, karaktype=&Param2&, newid= &Param4&, LocalTransOK=true where localid=&Param3& and LocalTransOK=false and DATA='?' and karaktype='?' UPDATE null 143 omskrevet null 263 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id, ucase("U3"), o.def, &Param1&, concat(Dbinstans_T.BasisArkiv, d2.data, d.data) as sti, &Param2& from objdeclare_t o, relationData_t rd, relationData_t rd2, data_t d, data_t d2, DBInstans_t where o.id=rd.objekt_id and o.id=rd2.objekt_id and o.def="Dokument" and rd.data_id=d.data_id and rd2.data_id=d2.data_id and d.type="Filnavn" and d2.type="FilKatalog" and DBInstans_t.lastused=1; INSERT null se 333 til PHP multiuser null 264 update facttable1_t set medkarakteristika=&Param1& where medkarakteristika=&Param2&; UPDATE null Updater felt der fejlagtigt er blevet lower case felt. (se 263 der er ændret) null Ver.:20120808 66 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 265 select distinct objektklasse from dataregler_t SELECT null PHP null 266 select distinct tilladtkarakrela from dataregler_t where objektklasse=&Param1&; SELECT null PHP null 267 CREATE TABLE `session_var` ( `session_id` varchar(50) COLLATE utf8_danish_ci NOT NULL, `user` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `password` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `action` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `frombase` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `tobase` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `php_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, `db_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, `report_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, `db_type` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `debuglevel` int(11) DEFAULT '0', `param_num1` int(11) DEFAULT '0', `param_str1` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `param_str2` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `param_str3` varchar(245) COLLATE utf8_danish_ci DEFAULT NULL, `last_timestamp` int(11) DEFAULT '0', PRIMARY KEY (`session_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci CREATE null Bruges af PHP til at gemme parametre null 268 delete from gereba_node1.session_var where session_id= &Param1&; DELETE null Benyttes ved PHP program. null 269 insert into gereba_node1.session_var (session_id, user, password, action, frombase, tobase, php_server, db_server, db_type, debuglevel) values (&Param1&,&Param2&, &Param3&, &Param4&, &Param5&,&Param6&,'xx','xx','yy','00'); INSERT null PHP program. null Localhost opdateres til 127.0.0.1 i 288. ebuglevel skal sættes 00 i strengen for at Ver.:20120808 67 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor køre under php windows. 270 select * from gereba_node1.session_var as s where s.session_id=&Param1&; SELECT null null null 273 update gereba_node1.session_var set param_num1=&Param1& where session_var.session_id=&Param2&; UPDATE null PHP null 274 update gereba_node1.session_var set param_str1=&Param1& where session_var.session_id=&Param2&; UPDATE null PHP null 275 update gereba_node1.session_var set param_str2=&Param1& where session_id=&Param2&; UPDATE null null null 276 INSERT into StaggingPD2_T (ObjektType,FromBase, Tobase, Action, Karakteristika, KarakType, DATA, LocalID, NewID, LocalTransOK) select distinct DEF, &Param2&, &Param2&, &Param3&, &Param4&, ucase("DATA"),&Param5& ,&Param1&,&Param1&,0 from facttable1_t where fremsoegtid=&Param1&; INSERT null SQL 40 omskrevet til null PHP, kan sætte hele posten ind fra facttable 277 update facttable1_t set DATA=concat('http:', DATA) where medkarakteristika="Åbn program" and instr(DATA, 'file:')=0; UPDATE null null 278 update facttable1_t set DATA='http:' & DATA where medkarakteristika="Åbn program" and fremsoegtid=&Param1&; UPDATE null UDGÅR i PHP null sammenhæng se 294 279 select localid, newid, action, min(transactid) from staggingpd2_t where globaltransok=false and action like &Param1&; SELECT null finder distincte id og ordre hvis argument er % PHP Ver.:20120808 68 Report created: null 14-01-2015 HVOR: funktion findaction() i main ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 280 update staggingpd2_t set LocalTransOK=&Param4&, globaltransOK=&Param5& where newid like &Param1& and action like &Param2& and transactid like &Param3& ; UPDATE null null null 281 SELECT StaggingPD2_T.Karakteristika, StaggingPD2_T.KarakType, StaggingPD2_T.LocalID, StaggingPD2_T.NewID, StaggingPD2_T.DATA, StaggingPD2_T.GlobalTransOK, transactid FROM StaggingPD2_T WHERE StaggingPD2_T.newid=&Param1& AND StaggingPD2_T.GlobalTransOK=False; SELECT null null null 282 UPDATE StaggingPD2_T set StaggingPD2_T.karakteristika=&Param1& , StaggingPD2_T.DATA=&Param2& where StaggingPD2_T.transactid= &Param3& AND StaggingPD2_T.GlobalTransOK=False; UPDATE null null null 283 select * from staggingpd2_t where transactid=&Param1& SELECT null null null 284 select '1. DATA uden relationer: ' as tekst, data, type, data_id, NULL as objid, null as Antal from data_t WHERE not Exists (select null from RelationData_T where data_t.data_id=relationdata_t.data_id) UNION select '2. Objekter uden data: ' as tekst, null, null, null, objdeclare_t.id as objid,null from objdeclare_t WHERE not Exists (select null from RelationData_T where objdeclare_t.id=relationdata_t.objekt_id) UNION select '3. Redundante data i data_t: ', data, type, null , null, count(data) from data_t group by data,type having count(data)>=2 SELECT null Analyse af fejl mellem data_t, relationdata_t, objdeclare_t og objrelationobj_t Benyttes af rapport: gereba_DAT Acontrol2.jrx ml Ver.:20120808 69 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor union select '4. Relationdata med DATA ikke eksisternde: ',null, null,relationdata_t.data_id,relationdata_t.objekt_id as objid , null from relationdata_t WHERE not Exists (select null from Data_T where data_id=relationdata_t.data_id) UNION select '5. Relationdata med Objekter ikke eksisternede: ',null,null, relationdata_t.data_id,relationdata_t.objekt_id as objid , null from relationdata_t WHERE not Exists (select null from objdeclare_T where id=relationdata_t.objekt_id) union select '6. Relationer mellem objekt(er) der ikke eksisterer: ', null, relationstype, objekt2,objekt1,null FROM `objrelationobj_t` where not exists (select null from objdeclare_t where objrelationobj_t.objekt2=objdeclare_t.id ) or not exists (select null from objdeclare_t where objrelationobj_t.objekt1=objdeclare_t.id ) order by 1; 285 delete from relationdata_t where objekt_id=0 DELETE null Rensning af data, dette er nr.1n nr 2 er SQL 68, nr 3: 286 null 286 delete from relationdata_t WHERE not Exists (select null from Data_T where data_id=relationdata_t.data_id) DELETE null Rensning af data, dette er nr.3 nr 2 er SQL 68, nr 1: 285 null 287 insert into StaggingRelation_T (action, frombase, tobase, newid1, SELECT null erstatter 133 null Ver.:20120808 70 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor localid1, Relationstype, localid2, newid2, localtransok, globaltransok) values ( &Param1&, &Param2&, &Param3&, &Param4&, &Param5&, &Param6&, 0, 0, false, false); 288 update gereba_node1.session_var set php_server=&Param1&, db_server=&Param6&,param_num1=&Param2&,db_type=&Param3&, debuglevel=&Param4& UPDATE null null null 289 delete from objrelationobj_t where objekt1=&Param1& and relationstype=&Param2& and DELETE objekt2=&Param3&; null PHP null 290 update gereba_node1.session_var set action=&Param1& where session_var.session_id=&Param2&; UPDATE null null null 291 delete from temptemp2; DELETE null null null 292 INSERT into TempObjektID_T (ObjektID,user) SELECT distinct FremsoegtID, &Param2& FROM Facttable1_t where fremsoegtID in(&Param1&); INSERT null erstatter 231, se ogå 316 null 293 update session_var set last_timestamp= &Param1& where session_var.session_id=&Param2&; UPDATE null null null Ver.:20120808 71 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 294 update facttable1_t set DATA=concat('http:', DATA) where medkarakteristika=&Param2& and fremsoegtid=&Param1& and instr(DATA, 'file:')=0; UPDATE null 278 omskrevet til PHP null 295 INSERT into TempObjektID_T (ObjektID,user) SELECT distinct oo.objekt1,"admin" FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo WHERE f2.def="ProcObj" and oo.objekt2=f2.fremsoegtID and oo.relationstype="ActivateDok" and f1.fremsoegtID=oo.objekt1 and f2.medkarakteristika="Alert" and datediff(f2.data, curdate())<=&Param1& and datediff(f2.data, curdate())>=0 union SELECT distinct oo.objekt1,"admin" FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo WHERE f2.def="ProcObj" and oo.objekt2=f2.fremsoegtID and oo.relationstype="ActivateDok" and f1.fremsoegtID=oo.objekt1 and f2.medkarakteristika="Alert" and datediff(f2.data, curdate())>=&Param1& and datediff(f2.data, curdate())<=0; INSERT null 52 generaliseret for PHP null 296 INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from (SELECT distinct f2.FremsoegtID as id FROM Facttable1_t as f2, Facttable1_t as f4 where f2.definition="DATA" and f2.MedKarakteristika like &Param1& and f4.Def Like &Param2& and f2.FremsoegtID= f4.FremsoegtID INSERT null søgning hvor der ikke er tastet data (ikke et stort problem med ODBC??) ombygget 230 PHP, igen ombygget med sikkerheds view yil 315 Ver.:20120808 72 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor UNION SELECT distinct f1.FremsoegtID as id FROM Facttable1_t as f1, Facttable1_t as f3 where f1.DATA = &Param1& and f1.definition="EndDef" and f3.Def Like &Param2& and f1.FremsoegtID= f3.FremsoegtID ) as u; 297 select count(*) from tempobjektid_t as te where te.user=&Param1&; SELECT null null null 298 INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param4& from (SELECT distinct t1.FremsoegtID as id from FactTable1_T t1, FactTable1_T t2, FactTable1_T t3 where t2.data like &Param1& and t2.def like &Param3& and t1.FremsoegtID=t2.Fremsoegtid and t2.Fremsoegtid=t3.Fremsoegtid and t3.medkarakteristika like &Param2& and t3.definition='DATA' union select distinct t1.FremsoegtID from FactTable1_T t1, FactTable1_T t2, FactTable1_T t3 where t2.data like &Param1& and t2.def like &Param3& and t1.FremsoegtID=t2.Fremsoegtid and t2.Fremsoegtid=t3.Fremsoegtid and t3.data like &Param2& and t3.definition='EndDef') as u; INSERT null Hvis fritekst IKKE er tom (PHP) og def ubestemt. Erstatter 230, se 317 null Ver.:20120808 73 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 299 insert into staggingpd2local_t select * from staggingpd2_t where globaltransok=0; INSERT null null null 300 insert into staggingrelationlocal_t select * from staggingrelation_t where globaltransok=0; INSERT null null null 301 delete from TempObjektID_T where user like &Param1&; DELETE null SQL 8 omskrevet med null session_id til PHP 302 delete from gereba_node1.session_var; DELETE null null null 303 update gereba_node1.session_var set param_str3=&Param1& where session_var.session_id=&Param2&; UPDATE null null null 304 delete from gereba_node1.tempobjektid_t where user not in (select distinct session_id from gereba_node1.session_var); DELETE null Oprydning i multiuser modellen null 305 delete FROM gereba_node1.session_var where &Param1& - last_timestamp > &Param2&; DELETE null Oprydning i multiuser modellen null 306 INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from ( SELECT distinct t1.FremsoegtID as id from FactTable1_T t1, FactTable1_T t2, FactTable1_T t3 where t2.data like &Param1& and t1.FremsoegtID=t2.Fremsoegtid and t2.Fremsoegtid=t3.Fremsoegtid and t3.data like &Param2& ) as u; INSERT null søge sql med AND operator, se 318 null 307 INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from ( SELECT distinct t1.FremsoegtID as id from FactTable1_T t1 INSERT null søge sql med OR operator null Ver.:20120808 74 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor where t1.data like &Param1& or (t1.data like &Param2&) order by 1 ) as u; 308 update gereba_node1.session_var set report_server=&Param1& where session_var.session_id=&Param2&; UPDATE null null null 309 CREATE TABLE `userpasswcredit_t` ( `base` varchar(45) COLLATE utf8_danish_ci NOT NULL, `username` varchar(45) COLLATE utf8_danish_ci NOT NULL DEFAULT 'nn', `password` varchar(100) COLLATE utf8_danish_ci NOT NULL, `credit` int(11) DEFAULT '0', `name` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, `comment` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL, PRIMARY KEY (`base`,`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; CREATE null null null 310 set @basename=&Param1&; set @username=&Param2&; set @credit=&Param3&; SET null Bruges til fine grain null (single row security) på MYSQL base benyttet af PHP sammen med view og funktioner. MEN ikke som denne SQL, men direktei PHP 311 create index ind_karakteristika on facttable1_t (medkarakteristika); CREATE INDEX null Danner index på null fattable1_t til brug ved fine grain view, vigtig tilføjelse til SQL 196 312 select * from gereba_node1.userpasswcredit_t where BINARY username=&Param1& and BINARY password =&Param2& and &Param3& like base; SELECT null null Ver.:20120808 75 Report created: null 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring CREATE VIEW null Her eksemplificeret på null pim 314 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` CREATE VIEW SQL SECURITY DEFINER VIEW `objrelationobj_v` AS select distinct `ob`.`OBJEKT1` AS `objekt1`,`ob`.`RELATIONSTYPE` AS `relationstype`, `ob`.`OBJEKT2` AS `objekt2` from (`objrelationobj_t` `ob` join `facttable1_t` `fa`) where ((`ob`.`OBJEKT2` = `fa`.`FREMSOEGTID`) and (`fa`.`MEDKARAKTERISTIKA` = 'Credit') and (`fa`.`DATA` <= `pim`.`getcredit`())) union select distinct `ob`.`OBJEKT1` AS `objekt1`, `ob`.`RELATIONSTYPE` AS `relationstype`,`ob`.`OBJEKT2` AS `objekt2` from (`objrelationobj_t` `ob` join `facttable1_t` `fa`) where ((`ob`.`OBJEKT1` = `fa`.`FREMSOEGTID`) and (`fa`.`MEDKARAKTERISTIKA` = 'Credit') and (`fa`.`DATA` <= `pim`.`getcredit`())) null BRUGES IKKE MERE null ? Her kun vist for pim basen, skal dannes på samtlige shemas. 315 INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from (SELECT null Ombygget 296 med 313 CREATE OR REPLACE VIEW facttable1_v AS select * from pim.facttable1_t as fa Benyttet hvor and fa.medkarakteristika='credit' and fa.data <=&Param1& union select * from pim.facttable1_t as fa where fremsoegtid not in (select fremsoegtid from pim.facttable1_t as fa2 where fa2.medkarakteristika='credit') ; Ver.:20120808 76 INSERT Report created: 14-01-2015 null ID SQL udtryk SQL type Evt. navn distinct f2.FremsoegtID as id FROM Facttable1_v as f2, Facttable1_v as f4 where f2.definition="DATA" and f2.MedKarakteristika like &Param1& and f4.Def Like &Param2& and f2.FremsoegtID= f4.FremsoegtID UNION SELECT distinct f1.FremsoegtID as id FROM Facttable1_v as f1, Facttable1_v as f3 where f1.DATA = &Param1& and f1.definition="EndDef" and f3.Def Like &Param2& and f1.FremsoegtID= f3.FremsoegtID ) as u; Forklaring Benyttet hvor sikkerheds view 316 INSERT into TempObjektID_T (ObjektID,user) SELECT distinct FremsoegtID, &Param2& FROM Facttable1_v where fremsoegtID in(&Param1&); INSERT null se 292 null 317 INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param4& from (SELECT distinct t1.FremsoegtID as id from FactTable1_v t1, FactTable1_v t2, FactTable1_v t3 where t2.data like &Param1& and t2.def like &Param3& and t1.FremsoegtID=t2.Fremsoegtid and t2.Fremsoegtid=t3.Fremsoegtid and t3.medkarakteristika like &Param2& and t3.definition='DATA' union select distinct t1.FremsoegtID INSERT null se 298 null Ver.:20120808 77 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor from FactTable1_v t1, FactTable1_v t2, FactTable1_v t3 where t2.data like &Param1& and t2.def like &Param3& and t1.FremsoegtID=t2.Fremsoegtid and t2.Fremsoegtid=t3.Fremsoegtid and t3.data like &Param2& and t3.definition='EndDef') as u; 318 INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from ( SELECT distinct t1.FremsoegtID as id from FactTable1_v t1, FactTable1_v t2, FactTable1_v t3 where t2.data like &Param1& and t1.FremsoegtID=t2.Fremsoegtid and t2.Fremsoegtid=t3.Fremsoegtid and t3.data like &Param2& ) as u; INSERT null se 306 null 319 INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from ( SELECT distinct t1.FremsoegtID as id from FactTable1_T t1 where t1.data like &Param1& or (t1.data like &Param2&) order by 1 ) as u; INSERT null se 307 null 320 select 'connection_id: ',connection_id(), ' fkt.getcredit: ', getcredit(); SELECT null Test af mysql connection_id og funktion null 321 select getcredit() ; SELECT null null null Ver.:20120808 78 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 322 insert into gereba_node1.temprelaobj_t (sessionid, obj1,relation,obj2) select &Param1&, objekt1, relationstype,objekt2 from objrelationobj_t where objekt1 in (select objektid from gereba_node1.tempobjektid_t) or objekt2 in (select objektid from gereba_node1.tempobjektid_t) INSERT null UDGÅR til fordel for 329 null 323 delete from gereba_node1.temprelaobj_t where sessionid like &Param1& DELETE null like operator for at kunne slette alt fra tabellen med % null 324 CREATE TABLE `temprelaobj_t` ( `sessionid` varchar(60) COLLATE utf8_danish_ci NOT NULL, `obj1` int(11) NOT NULL, `relation` varchar(45) COLLATE utf8_danish_ci NOT NULL, `obj2` int(11) NOT NULL, PRIMARY KEY (`obj1`,`relation`,`obj2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci; CREATE TABLE null null null 325 select count(9) FROM gereba_node1.temprelaobj_t where sessionid =&Param1&; SELECT null null null 326 insert into gereba_node1.tempobjektid_t (objektid, user) select distinct obj2, sessionid from gereba_node1.temprelaobj_t where sessionid=&Param1&; INSERT null obj kan erstattes Venstrestillet, afParm her eller i 327 højrestillet 327 insert into gereba_node1.tempobjektid_t (objektid, user) select distinct obj1, sessionid from gereba_node1.temprelaobj_t where sessionid=&Param1&; INSERT null obj kan erstattes Venstrestillet, afParm her eller i 326 højrestillet 328 delete from gereba_node1.tempobjektid_t where objektid not in (select fremsoegtid from facttable1_v) and user = &Param1&; DELETE null null null 329 insert into gereba_node1.temprelaobj_t ( obj1, relation, obj2, sessionid) select ob.OBJEKT1 AS obj1,ob.RELATIONSTYPE AS relation, ob.OBJEKT2 AS obj2,t.user as sessionid INSERT null null null Ver.:20120808 79 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor create table null 194 bør slettes null eftersom triggeren ikke mere benyttes from objrelationobj_t as ob, gereba_node1.tempobjektid_t as t where t.user=&Param1& and ob.OBJEKT1 =t.objektid union select ob.OBJEKT1 AS obj1,ob.RELATIONSTYPE AS relation, ob.OBJEKT2 AS obj2,t.user as sessionid from objrelationobj_t as ob, gereba_node1.tempobjektid_t as t where t.user=&Param1& and ob.OBJEKT2 =t.objektid ; 330 CREATE TABLE `dbinstans_t` ( `StiOgNavn` varchar(250) COLLATE utf8_danish_ci DEFAULT NULL, `Dato` varchar(40) COLLATE utf8_danish_ci DEFAULT NULL, `Beskrivelse` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL, `NavnID` varchar(50) COLLATE utf8_danish_ci DEFAULT '', `visible` tinyint(1) DEFAULT '0', `LastUsed` tinyint(1) DEFAULT '0', `AutoOpretDato` tinyint(1) DEFAULT '1', `BasisArkiv` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `tema_katalog` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL, `gereba_type` varchar(255) COLLATE utf8_danish_ci DEFAULT '?', `SpecialForms` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL, `DocumentCatalog` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL, `DefaultUser` varchar(20) COLLATE utf8_danish_ci DEFAULT NULL, `UserValue` int(11) DEFAULT NULL, `BenyttesCrystal` bit(1) DEFAULT NULL, `JasperMainReport` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL, `JasperCatalog` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL, `JasperMYSQLserver` varchar(100) COLLATE utf8_danish_ci DEFAULT '', `Comment` mediumtext COLLATE utf8_danish_ci, `conector` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `conuserpasw` varchar(80) COLLATE utf8_danish_ci DEFAULT NULL, `driver` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, Ver.:20120808 80 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor `dsn` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL, `port` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL, `id` varchar(45) COLLATE utf8_danish_ci NOT NULL DEFAULT '', `visnings_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, `database_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, `program_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci 331 delete from gereba_node1.temprelaobj_t where temprelaobj_t.sessionid=&Param1& and (temprelaobj_t.obj1 in ( select fa.fremsoegtid from facttable1_t fa where `fa`.`MEDKARAKTERISTIKA` ="Credit" and `fa`.`DATA` > `pim`.`getcredit`())) or (temprelaobj_t.obj2 in ( select fa.fremsoegtid from facttable1_t fa where `fa`.`MEDKARAKTERISTIKA` = "Credit" and `fa`.`DATA` > `pim`.`getcredit`())); DELETE null null null 332 select * from gereba_node1.dbinstans_t where id =&Param1& SELECT null null null 333 insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition) select o.id, ucase("U3"), o.def, &Param1&, concat(Dbinstans_T.BasisArkiv, d2.data, d.data) as sti, &Param2& from objdeclare_t o, relationData_t rd, relationData_t rd2, data_t d, data_t d2, DBInstans_t where o.id=rd.objekt_id and o.id=rd2.objekt_id and o.def="Dokument" and rd.data_id=d.data_id and rd2.data_id=d2.data_id and d.type="Filnavn" INSERT null 263 ændret til PHP multiuser null Ver.:20120808 81 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor and d2.type="FilKatalog" and DBInstans_t.id= &Param3&; 334 select ObjektType, Karakteristika, KarakType, DATA, NewID, LocalID, transactID From StaggingPD2_T where GlobaltransOK=FALSE and tobase=&Param1& and action =&Param2& and LocaltransOK=&Param3& ; SELECT null Udvidelse af SQL 38 til null PHP 335 select ObjektType, Karakteristika, KarakType, DATA, NewID, LocalID, transactID From StaggingPD2_T where GlobaltransOK=FALSE and tobase=&Param1& and action =&Param2& and LocaltransOK=&Param3& and newid=&Param4& ; SELECT null som 334, men null beregnet til indsættelse af data_id med specifikt newid 336 select distinct LocalID,ObjektType, NewID From StaggingPD2_T where GlobaltransOK=FALSE and tobase=&Param1& and action =&Param2& and localid=&Param3& ; SELECT null null Ver.:20120808 82 Report created: datainsert PHP 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor 337 UPDATE staggingPD2_T set Action= &Param2&, NewID=&Param1& , LocalTransOK=0 where LocalID=&Param1& UPDATE null 116 ombygget til at kunne sætte localtrans=FALSE null 338 SELECT a.def1, b.def1, b.def2 from definitionshiraki_T a, definitionshiraki_T b where a.def1=b.def2 and b.def1 in("BasisObj","system") and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1, c.def1, c.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c where a.def1=b.def2 and b.def1=c.def2 and c.def1 in("BasisObj","system") and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,d.def1, d.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1 in("BasisObj","system") and (a.def2="EndDef" or a.def2="DATA") union SELECT a.def1,e.def1, e.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1 in("BasisObj","system") and (a.def2="EndDef" or a.def2="DATA") UNION SELECT a.def1,f.def1, f.def2 from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1=f.def2 and f.def1 in("BasisObj","system") and (a.def2="EndDef" or a.def2="DATA") ORDER BY 2,1; SELECT null null null 339 select f1.fremsoegtid, f1.medkarakteristika as tekst, f2.medkarakteristika as jrxml,f1.data as dtekst,f2.data as djrxml SELECT null null null Ver.:20120808 83 Report created: 14-01-2015 ID SQL udtryk SQL type Evt. navn Forklaring Benyttet hvor from facttable1_t as f1,facttable1_t as f2 where f1.medkarakteristika in('rapport_tekst') and f2.medkarakteristika in('rapport_jrxml') and f1.fremsoegtid=f2.fremsoegtid 340 delete from gereba_node1.staggingpd2_t where (localtransok=0 or globaltransok=0) DELETE null null null 341 select count(*) from gereba_node1.staggingpd2_t where (localtransok=0 or globaltransok=0) SELECT null null null 342 delete from gereba_node1.staggingrelation_t where (localtransok=0 or globaltransok=0) DELETE null null null Total number of SQL expressions Ver.:20120808 84 272 Report created: 14-01-2015
© Copyright 2025