Kap 12 Dynamisk SQL Hi A SQL Hi

Kap 12 Dynamisk SQL Hi. A

SQL Hi. A

Flerbrukersystem Client / Server Client Server DBMS Application_1 Database SQL-Request Data Application_2 SQL Application_3 Hi. A

Resultatsett Client Server DBMS Database SQL-Request Data Tabellen Selger Application Fetch. Through Fetch SELECT SNr, Navn, PNr FROM Selger WHERE PNr = 6400 SQL SNr Navn PNr 5 2 1 4 Nilsen Olsen Hansen Berg 5002 6400 9000 6400 Resultatsett Row. ID SNr Navn PNr 2 4 Olsen Berg 6400 Hi. A

Row. ID Selger (ID = SNr) SNr Navn PNr Row. ID SNr Navn PNr 5 2 1 4 5002 6400 9000 6400 CAAD BACV ERCB EADD 5 2 1 4 5002 6400 9000 6400 Nilsen Olsen Hansen Berg Et eksempel på en 3 NF-tabell Selger med tre kolonner SNr, Navn og PNr SQL Nilsen Olsen Hansen Berg Row. ID er en ekstra kolonne i hver tabell som alltid kommer i tillegg til de kolonnene vi eksplisitt definerer. Row. ID er entydig for hver rad og fungerer som en slags identifikator. Hi. A

SQL Hi. A

Statisk SQL Selger (ID = SNr) SNr. ID Select Navn PNr SNr. ID Navn PNr 5 2 1 4 5002 6400 9000 6400 Nilsen Olsen Hansen Berg SELECT SNr. ID, Navn, PNr FROM Selger Set s. Select = ‘SELECT SNr. ID, Navn, PNr INTO : dfn. SNr. ID, : dfs. Navn, dfn. PNr FROM Selger’ Call Sql. Prepare ( h. Sql, s. Select ) Call Sql. Execute ( h. Sql ) Call Sql. Fetch. Next ( h. Sql, n. Fetch ) SQL Hi. A

Statisk SQL SNr. ID Navn PNr PNr Select SELECT SNr. ID, Navn, PNr FROM Selger SNr. ID Navn 2 Select SELECT Navn, PNr FROM Selger WHERE SNr. ID = : dfn. SNr. ID Nilsen PNr Navn PNr 6400 Select SELECT SNr. ID, Navn FROM Selger WHERE PNr = : dfn. PNr SNr. ID Nilsen 6400 Select SELECT SNr. ID, PNr FROM Selger WHERE Navn = : dfs. Navn SELECT Navn, PNr FROM Selger WHERE Navn = : dfs. Navn AND PNr = : dfn. PNr SQL SNr. ID Navn PNr %sen 6400 Select SELECT SNr. ID, Navn FROM Selger WHERE Navn LIKE ‘ || ‘’’ || dfs. Navn || ‘’’ AND PNr = : dfn. PNr Hi. A

Fra Statisk SQL til Dynamisk SQL frm. Selger SNr. ID Navn PNr 6400 SELECT SNr. ID, Navn, PNr FROM Selger WHERE PNr = : dfn. PNr Select Set s. Select = ‘SELECT SNr. ID, Navn, PNr INTO : dfn. SNr. ID, : dfs. Navn, dfn. PNr FROM Selger WHERE PNr = : dfn. PNr’ Set s. Select = ‘SELECT ‘ || s. Column || ‘ INTO ‘ || s. Into || ‘ FROM ‘ || s. From ‘ WHERE ‘ || s. Where SQL s. Column s. Into s. From s. Where = ‘SNr. ID, Navn, PNr’ = ‘ : dfn. SNr. ID, : dfs. Navn, : dfn. PNr’ = ‘Selger’ = ‘PNr = : dfn. PNr’ Hi. A

Dynamisk SQL - Initier SQL-variable frm. Selger SNr. ID dfn. SNr. ID Navn dfs. Navn PNr 6400 Select dfn. PNr pb. Select 1 pb. Select On SAM_Click Call Sal. Send. Msg ( h. Wnd. Form, PAM_SELECT, 0, 0 ) 2 SQL frm. Selger Message Actions On PAM_SELECT Set s. Column = ‘‘ Set s. Into = ‘‘ Set s. From = ‘‘ Set s. Where = ‘‘ Call Sal. Send. Msg ( h. Wnd. Form, PAM_SQL, 0, 0 ) Call Sal. Send. Msg. To. Children ( h. Wnd. Form, PAM_SQL, 0, 0 ) Hi. A

Dynamisk SQL - Bestem tabell-navn frm. Selger SNr. ID dfn. SNr. ID Navn dfs. Navn PNr 6400 Select dfn. PNr pb. Select frm. Selger Message Actions On PAM_SELECT Set s. Column = ‘‘ Set s. Into = ‘‘ Set s. From = ‘’ Set s. Where = ‘‘ Call Sal. Send. Msg. To ( h. Wnd. Form, PAM_SQL, 0, 0 ) Call Sal. Send. Msg. To. Children ( h. Wnd. Form, PAM_SQL, 0, 0 ). . . On PAM_SQL Call Sal. Get. Window. Text ( h. Wnd. Form, s. WName, 20 ) s. From = Sal. Str. Right ( s. WName, Sal. Str. Length(s. WName) - 3 ) SQL s. WName = ‘frm. Selger’ s. From = ‘Selger’ Hi. A

Dynamisk SQL - Bestem SQL-variable for dfn. SNr. ID frm. Selger SNr. ID dfn. SNr. ID Navn dfs. Navn PNr 6400 Select s. Column = ‘ SNr. ID ’ s. Into = ‘ : dfn. SNr. ID ’ dfn. PNr pb. Select frm. Selger Message Actions On PAM_SELECT Set s. Column = ‘‘ Set s. Into = ‘‘ Set s. From = ‘’ Set s. Where = ‘‘ Call Sal. Send. Msg. To ( h. Wnd. Form, PAM_SQL, 0, 0 ) Call Sal. Send. Msg. To. Children ( h. Wnd. Form, PAM_SQL, 0, 0 ). . . SQL Hi. A

Dynamisk SQL - Bestem SQL-variable for dfs. Navn frm. Selger SNr. ID dfn. SNr. ID Navn dfs. Navn PNr 6400 Select dfn. PNr s. Column = s. Column || ‘, ‘ || ‘ Navn ’ = ‘ SNr. ID, Navn ‘ s. Into pb. Select = s. Into || ‘ : dfs. Navn ’ = ‘ : dfn. SNr. ID, : dfs. Navn ‘ frm. Selger Message Actions On PAM_SELECT Set s. Column = ‘‘ Set s. Into = ‘‘ Set s. From = ‘’ Set s. Where = ‘‘ Call Sal. Send. Msg. To ( h. Wnd. Form, PAM_SQL, 0, 0 ) Call Sal. Send. Msg. To. Children ( h. Wnd. Form, PAM_SQL, 0, 0 ). . . SQL Hi. A

Dynamisk SQL - Bestem SQL-variable for dfn. PNr frm. Selger SNr. ID dfn. SNr. ID Navn dfs. Navn PNr 6400 Select dfn. PNr s. Column = s. Column || ‘, ‘ || ‘ PNr ’ = ‘ SNr. ID, Navn, PNr ‘ s. Into = s. Into || ‘ : dfn. PNr ’ = ‘ : dfn. SNr. ID, : dfs. Navn, : dfn. PNr ‘ s. Where = s. Where || ‘ PNr = ‘ || ‘ : dfn. PNr ‘ = ‘ PNr = 6400 ‘ pb. Select frm. Selger Message Actions On PAM_SELECT Set s. Column = ‘‘ Set s. Into = ‘‘ Set s. From = ‘’ Set s. Where = ‘‘ Call Sal. Send. Msg. To ( h. Wnd. Form, PAM_SQL, 0, 0 ) Call Sal. Send. Msg. To. Children ( h. Wnd. Form, PAM_SQL, 0, 0 ). . . SQL Hi. A

Dynamisk SQL - Bestem SELECT-statement s. Select frm. Selger SNr. ID dfn. SNr. ID Navn dfs. Navn PNr 6400 Select dfn. PNr s. Column = s. Column || ‘, ‘ || ‘ PNr ’ = ‘ SNr. ID, Navn, PNr ‘ s. Into = s. Into || ‘ : dfn. PNr ’ = ‘ : dfn. SNr. ID, : dfs. Navn, : dfn. PNr ‘ s. Where = s. Where || ‘ PNr = ‘ || ‘ : dfn. PNr ‘ = ‘ PNr = 6400 ‘ pb. Select frm. Selger Message Actions On PAM_SELECT. . . Call Sal. Send. Msg. To. Children ( h. Wnd. Form, PAM_SQL, 0, 0 ) Set s. Select = ‘SELECT ‘ || s. Column || ‘ INTO ‘ || s. Into ‘ FROM ‘ || s. From ‘ WHERE ‘ || s. Where. . . s. Select = SELECT SNr. ID, Navn, PNr INTO : dfn. SNr. ID, : dfs. Navn, : dfn. PNr FROM Selger WHERE PNr = 6400 SQL Hi. A

SQL Hi. A

Generering av dynamisk SQL-statement cls. Wnd Class Variables: s. Sql, s. Column, s. Into, s. Table, s. Where, s. Order, . . . Mdi Frm 3 SQL Build PAM_SELECT PAM_UPDATE. . . 1 SQL Build 2 PAM_SELECT PAM_UPDATE. . . SQL Hi. A

SQL Hi. A

Klasser (1) cls. Sql. Handle. Struct cls. Wnd_Mdi cls. Sql. Db. Access cls. Wnd_Top. Level cls. Frm cls. Tbl cls. Df. Row. ID cls. Df. Num cls. Wnd_Child. Object cls. Wnd_Df. Cmb. Mi. Col cls. Cmb cls. Df. Str cls. Cmb. Num_Auto. Select SQL cls. Col cls. Cmb. Str cls. Col. Row. ID cls. Col. Num cls. Ml cls. Col. Str cls. Cmb. Str_Auto. Select Hi. A

Klasser (2) cls. Pb SQL cls. Pb. Mdi. Select cls. Pb. Mdi. First cls. Pb. Mdi. Previous cls. Pb. Mdi. Update cls. Pb. Mdi. Insert cls. Pb. Mdi. Save cls. Pb. Mdi. Delete cls. Pb. Mdi. Sort cls. Pb. Mdi. New. Row cls. Pb. Mdi. Print cls. Pb. Mdi. Clear cls. Pb. Mdi. Next cls. Pb. Mdi. Last cls. Pb. Mdi. Help Hi. A

Klasse-notasjon Class. Name Class Variables Instance Variables Functions Messages SQL Hi. A

SQL Hi. A

cls. Sql. Handle. Struct iv_h. Sql bh. Sql. Status Instance Variable Sql. Handle True if iv_h. Sql is connected SQL Hi. A

cls. Sql. Db. Access s. Sql. Database s. Sql. User s. Sql. Password cv_h. Sql[1: *] n. Connected n. Min. Handles n. Max. Handles s. Sql. Statement_Select n. Result. Set. Count n. Fetch. Row. Number SQL Init. Class Connect. Sql. Handles Disconnect. All. Sql. Handles Set. Sql. Statement Set. Isolation. Level Set. Parameter Prepare Execute. Select Fetch. Row_Through First Previous Next Last Select Update Insert Delete Retrieve. Row Error Message. Box. Fetch. Error Select_Inst Update_Inst Insert_Inst Delete_Inst Initierer s. Sql. Database, s. Sql. User, s. Sql. Password Connect n. Min Sql. Handles Henter en rad på nytt etter UPDATE Hi. A

cls. Wnd s. Mdi. Name s. Top. Wnd. Name s. Sql s. Column s. Into s. Table s. Where s. Order. Column s. Update. Set s. Insert. Into s. Insert. Value s. Delete. Value s. Updates[1: *] h. Wnd. Col. Sort b. Exists s. Table. Array[1: *] n. Table. Array. Cont s. From s. Constraints SQL s. Item. Name s. Db. Table. Name s. Db. Column. Name s. Db. Table. Column. Name Set. Item. Name Set. Db. Table. Name Set_Tb. Name_Col. Name Set_Table. Array Set_From_Constraints SAM_Create Set the name of an object (frm. Main, dfs_Adr_PNr) dfs_Adr_PNr --> Adr dfs_Adr_PNr --> PNr Set the Array-values of different Tables in a Sql. Statement Set the FROM Clause and the Constraint part of a SELECT Sql. Statement Call Set. Item. Name( ) Hi. A

cls. Wnd_Mdi / cls. Wnd_Top. Level cls. Wnd_Mdi SAM_Create cls. Wnd_Top. Level Set. Top. Wnd. Name Get_Sql. Handle SQL_Build SQL_Select SQL_Insert SQL_Update SQL_Delete Clear Help_Top. Window SAM_Create PAM_SELECT PAM_UPDATE PAM_INSERT PAM_DELETE PAM_CLEAR PAM_HELP SAM_Close SQL Hi. A

cls. Mdi / cls. Frm / cls. Tbl cls. Mdi h. Sql. Db SQL cls. Frm cls. Tbl h. Sql. Select h. Sql. Update h. Sql. Insert h. Sql. Delete h. Sql n. Fetch h. Sql. Tbl Set_Sql. Handle Get_Sql. Handle Select_Inst Update_Inst Insert_Inst Delete_Inst Clear Get_Sql. Handle Select_Inst Update_Inst Insert_Inst Delete_Inst SQL_Select. Sort Clear SAM_Create PAM_FIRST PAM_PREVIOUS PAM_NEXT PAM_LAST SAM_Create PAM_SORT PAM_NEWROW Hi. A

cls. Wnd_Child. Object / cls. Wnd_Df. Cmb. Mi. Col cls. Wnd_Child. Object s. Parent. Name cls. Wnd_Df. Cmb. Mi. Col s. Item. Value Sql_Select Sql_Update Sql_Insert Get_Equal Get_My. Value SAM_Create PAM_SQL PAM_CLEAR SQL Hi. A

cls. Df s. Parent. Name Get_My. Value cls. Df. Row. ID Sql_Select Sql_Update Sql_Delete cls. Df. Num cls. Df. Str s. Select s. Item. Value Get_Equal PAM_ROWID SQL Hi. A

cls. Cmb. Num Drop. Down Click Get_My. Value Get_Equal cls. Cmb. Str Get_Equal SAM_Drop. Down SAM_Click cls. Cmb. Num_Auto. Select SQL cls. Cmb. Num_Auto. Select Drop. Down Click SAM_Click Hi. A

cls. Col SAM_Click cls. Col. Row. ID cls. Col. Num cls. Col. Str Sql_Select Sql_Update Sql_Delete SQL Hi. A

cls. Multiline Set. Db. Column. Name SQL Hi. A

Mdi. Window / Form. Window / Table. Window cls. Sql. Db. Access cls. Wnd_Mdi cls. Wnd_Top. Level cls. Mdi cls. Frm cls. Tbl cls. Sql. Db. Access: h. Sql. Db SQL Hi. A

Bruk av virtuelle funksjons-kall f 1 On Msg 1 Call. . f 1(…) f 1 f 1 f 1 Call Sal. Send. Msg(Obj 1, Msg 1…) SQL Hi. A

Navn-setting frm. Selger Skjult Row. ID dfs_Selger_Row. ID cmb_Selger_SNr. ID Combo. Box SQL Tabell-Navn Kolonne-navn Hi. A

UPDATE Selger SET Navn = : cmb_Selger_Navn, PNr = : cmb_Selger_PNr WHERE ROWID = : dfs_Selger_Row. ID cls. Frm Update Call Execute (h. Sql. Update) Call Sql. Set. Parameter ( h. Sql. Select, DBP_FETCHTHROUGH, TRUE, ‘‘) Call Fetch. Row_Through ( h. Sql. Select, n. Fetch. Row. Number, n. Ind) Call Sql. Set. Parameter ( h. Sql. Select, DBP_FETCHTHROUGH, FALSE, ‘‘) SQL Hi. A

End SQL Hi. A