1. Kap 12 Dynamisk SQL

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

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

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

7. Statisk SQL Selger (ID = SNr) SNrID Navn PNr 5 Nilsen 5002 2 Olsen 6400 1 Hansen 9000 4 Berg 6400 SNrID Select Navn PNr SELECT SNrID, Navn, PNr FROM Selger Set sSelect = ‘SELECT SNrID, Navn, PNr INTO :dfnSNrID, :dfsNavn, dfnPNr FROM Selger’ Call SqlPrepare ( hSql, sSelect ) Call SqlExecute ( hSql ) Call SqlFetchNext ( hSql, nFetch )

8. Statisk SQL SNrID SNrID SNrID 2 Navn Navn Navn PNr PNr PNr 6400 Select Select Select SELECT SNrID, Navn, PNr FROM Selger SELECT Navn, PNr FROM Selger WHERE SNrID = :dfnSNrID SELECT SNrID, Navn FROM Selger WHERE PNr = :dfnPNr SNrID SNrID SNrID Navn Navn Navn Nilsen Nilsen %sen PNr PNr PNr 6400 6400 Select Select Select SELECT SNrID, PNr FROM Selger WHERE Navn = :dfsNavn SELECT Navn, PNr FROM Selger WHERE Navn = :dfsNavn AND PNr = : dfnPNr SELECT SNrID, Navn FROM Selger WHERE Navn LIKE ‘ || ‘\’’ || dfsNavn || ‘\’’ AND PNr = :dfnPNr

9. Fra Statisk SQL til Dynamisk SQL frmSelger SNrID SELECT SNrID, Navn, PNr FROM Selger WHERE PNr = :dfnPNr Navn PNr 6400 Select Set sSelect = ‘SELECT SNrID, Navn, PNr INTO :dfnSNrID, :dfsNavn, dfnPNr FROM Selger WHERE PNr = :dfnPNr’ Set sSelect = ‘SELECT ‘ || sColumn || ‘ INTO ‘ || sInto || ‘ FROM ‘ || sFrom ‘ WHERE ‘ || sWhere sColumn = ‘SNrID, Navn, PNr’ sInto = ‘ :dfnSNrID, :dfsNavn, :dfnPNr’ sFrom = ‘Selger’ sWhere = ‘PNr = :dfnPNr’

10. Dynamisk SQL - Initier SQL-variable frmSelger SNrID dfnSNrID Navn dfsNavn PNr dfnPNr 6400 pbSelect Select 1 pbSelect On SAM_Click Call SalSendMsg ( hWndForm, PAM_SELECT, 0, 0 ) 2 frmSelger Message Actions On PAM_SELECT Set sColumn = ‘‘ Set sInto = ‘‘ Set sFrom = ‘‘ Set sWhere = ‘‘ Call SalSendMsg ( hWndForm, PAM_SQL, 0, 0 ) Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 )

11. Dynamisk SQL - Bestem tabell-navn frmSelger SNrID dfnSNrID Navn dfsNavn PNr dfnPNr 6400 pbSelect Select frmSelger Message Actions On PAM_SELECT Set sColumn = ‘‘ Set sInto = ‘‘ Set sFrom = ‘’ Set sWhere = ‘‘ Call SalSendMsgTo ( hWndForm, PAM_SQL, 0, 0 ) Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 ) ... On PAM_SQL Call SalGetWindowText ( hWndForm, sWName, 20 ) sFrom = SalStrRight ( sWName, SalStrLength(sWName) - 3 ) sWName = ‘frmSelger’ sFrom = ‘Selger’

12. Dynamisk SQL - Bestem SQL-variable for dfnSNrID frmSelger SNrID dfnSNrID sColumn = ‘ SNrID ’ sInto = ‘ :dfnSNrID ’ Navn dfsNavn PNr dfnPNr 6400 pbSelect Select frmSelger Message Actions On PAM_SELECT Set sColumn = ‘‘ Set sInto = ‘‘ Set sFrom = ‘’ Set sWhere = ‘‘ Call SalSendMsgTo ( hWndForm, PAM_SQL, 0, 0 ) Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 ) ...

13. Dynamisk SQL - Bestem SQL-variable for dfsNavn frmSelger SNrID dfnSNrID sColumn = sColumn || ‘, ‘ || ‘ Navn ’ = ‘ SNrID, Navn ‘ sInto = sInto || ‘ :dfsNavn ’ = ‘ :dfnSNrID, :dfsNavn ‘ Navn dfsNavn PNr dfnPNr 6400 pbSelect Select frmSelger Message Actions On PAM_SELECT Set sColumn = ‘‘ Set sInto = ‘‘ Set sFrom = ‘’ Set sWhere = ‘‘ Call SalSendMsgTo ( hWndForm, PAM_SQL, 0, 0 ) Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 ) ...

14. Dynamisk SQL - Bestem SQL-variable for dfnPNr frmSelger sColumn = sColumn || ‘, ‘ || ‘ PNr ’ = ‘ SNrID, Navn, PNr ‘ sInto = sInto || ‘ :dfnPNr ’ = ‘ :dfnSNrID, :dfsNavn, :dfnPNr ‘ sWhere = sWhere || ‘ PNr = ‘ || ‘ :dfnPNr ‘ = ‘ PNr = :dfnPNr ‘ = ‘ PNr = 6400 ‘ SNrID dfnSNrID Navn dfsNavn PNr dfnPNr 6400 pbSelect Select frmSelger Message Actions On PAM_SELECT Set sColumn = ‘‘ Set sInto = ‘‘ Set sFrom = ‘’ Set sWhere = ‘‘ Call SalSendMsgTo ( hWndForm, PAM_SQL, 0, 0 ) Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 ) ...

15. Dynamisk SQL - Bestem SELECT-statement sSelect frmSelger sColumn = sColumn || ‘, ‘ || ‘ PNr ’ = ‘ SNrID, Navn, PNr ‘ sInto = sInto || ‘ :dfnPNr ’ = ‘ :dfnSNrID, :dfsNavn, :dfnPNr ‘ sWhere = sWhere || ‘ PNr = ‘ || ‘ :dfnPNr ‘ = ‘ PNr = :dfnPNr ‘ = ‘ PNr = 6400 ‘ SNrID dfnSNrID Navn dfsNavn PNr dfnPNr 6400 pbSelect Select frmSelger Message Actions On PAM_SELECT ... Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 ) Set sSelect = ‘SELECT ‘ || sColumn || ‘ INTO ‘ || sInto ‘ FROM ‘ || sFrom ‘ WHERE ‘ || sWhere ... sSelect = SELECT SNrID, Navn, PNr INTO :dfnSNrID, :dfsNavn, :dfnPNr FROM Selger WHERE PNr = 6400

17. Generering av dynamisk SQL-statement clsWnd Class Variables: sSql, sColumn, sInto, sTable, sWhere, sOrder, ... Mdi Frm 3 SQL Build SQL Build PAM_SELECT PAM_UPDATE ... 2 1 PAM_SELECT PAM_UPDATE ...

19. Klasser (1) clsSqlHandleStruct clsSqlDbAccess clsWnd clsWnd_Mdi clsWnd_TopLevel clsWnd_ChildObject clsMdi clsFrm clsTbl clsWnd_DfCmbMiCol clsDf clsCmb clsCol clsMl clsDfRowID clsDfNum clsDfStr clsCmbNum clsCmbStr clsColRowID clsColNum clsColStr clsCmbNum_AutoSelect clsCmbStr_AutoSelect

20. Klasser (2) clsPb clsPbMdiSelect clsPbMdiSelect clsPbMdiFirst clsPbMdiPrevious clsPbMdiNext clsPbMdiLast clsPbMdiUpdate clsPbMdiInsert clsPbMdiSave clsPbMdiDelete clsPbMdiSort clsPbMdiNewRow clsPbMdiPrint clsPbMdiClear clsPbMdiHelp

21. Klasse-notasjon ClassName Class Variables Instance Variables Functions Messages

23. clsSqlHandleStruct clsSqlHandleStruct Instance Variable SqlHandle iv_hSql bhSqlStatus True if iv_hSql is connected

24. clsSqlDbAccess clsSqlDbAccess InitClass ConnectSqlHandles DisconnectAllSqlHandles SetSqlStatement SetIsolationLevel SetParameter Prepare Execute ExecuteSelect FetchRow FetchRow_Through First Previous Next Last Select Update Insert Delete RetrieveRow Error MessageBoxFetchError Select_Inst Update_Inst Insert_Inst Delete_Inst Initierer sSqlDatabase, sSqlUser, sSqlPassword sSqlDatabase sSqlUser sSqlPassword cv_hSql[1:*] nConnected nMinHandles nMaxHandles sSqlStatement sSqlStatement_Select Connect nMin SqlHandles nResultSetCount nFetchRowNumber Henter en rad på nytt etter UPDATE

25. clsWnd clsWnd sMdiName sTopWndName sSql sColumn sInto sTable sWhere sOrder sOrderColumn sUpdateSet sInsertInto sInsertValue sDeleteValue sUpdates[1:*] hWndColSort bExists sTableArray[1:*] nTableArrayCont sFrom sConstraints sItemName sDbTableName sDbColumnName sDbTableColumnName SetItemName SetDbTableName Set_TbName_ColName Set_TableArray Set_From_Constraints Set the name of an object (frmMain, dfs_Adr_PNr) dfs_Adr_PNr --> Adr dfs_Adr_PNr --> PNr Set the Array-values of different Tables in a SqlStatement Set the FROM Clause and the Constraint part of a SELECT SqlStatement SAM_Create Call SetItemName( )

26. clsWnd_Mdi / clsWnd_TopLevel clsWnd_TopLevel clsWnd_Mdi SetTopWndName Get_SqlHandle SQL_Build SQL_Select SQL_Insert SQL_Update SQL_Delete Clear Help_TopWindow SAM_Create SAM_Create PAM_SELECT PAM_UPDATE PAM_INSERT PAM_DELETE PAM_CLEAR PAM_HELP SAM_Close

27. clsMdi / clsFrm / clsTbl clsMdi clsFrm clsTbl hSqlDb hSqlSelect hSqlUpdate hSqlInsert hSqlDelete hSql nFetch hSqlTbl Set_SqlHandle Get_SqlHandle Select_Inst Update_Inst Insert_Inst Delete_Inst Clear Get_SqlHandle Select_Inst Update_Inst Insert_Inst Delete_Inst SQL_SelectSort Clear SAM_Create PAM_FIRST PAM_PREVIOUS PAM_NEXT PAM_LAST SAM_Create PAM_SORT PAM_NEWROW

28. clsWnd_ChildObject / clsWnd_DfCmbMiCol clsWnd_ChildObject sParentName clsWnd_DfCmbMiCol sItemValue Sql Sql_Select Sql_Update Sql_Insert Get_Equal Get_MyValue SAM_Create PAM_SQL PAM_CLEAR

29. clsDf clsDf sParentName Get_MyValue clsDfRowID clsDfNum clsDfStr sSelect sItemValue Sql Sql_Select Sql_Update Sql_Delete Get_Equal Get_Equal PAM_ROWID

30. clsCmb clsCmb clsCmbNum clsCmbStr DropDown Click Get_MyValue Get_Equal Get_Equal SAM_DropDown SAM_Click clsCmbNum_AutoSelect clsCmbNum_AutoSelect DropDown Click DropDown Click SAM_Click SAM_Click

31. clsCol clsCol SAM_Click clsColRowID clsColNum clsColStr Sql Sql_Select Sql_Update Sql_Delete

32. clsMultiline clsMultiline SetDbColumnName

33. MdiWindow / FormWindow / TableWindow clsSqlDbAccess clsWnd_Mdi clsWnd_TopLevel clsMdi clsFrm clsTbl clsSqlDbAccess: hSqlDb

34. Bruk av virtuelle funksjons-kall f1 On Msg1 Call ..f1(…) f1 f1 f1 f1 f1 f1 Call SalSendMsg(Obj1, Msg1…)

35. Navn-setting frmSelger Skjult RowID dfs_Selger_RowID cmb_Selger_SNrID ComboBox Tabell-Navn Kolonne-navn

36. UPDATE UPDATE Selger SET Navn = :cmb_Selger_Navn, PNr = :cmb_Selger_PNr WHERE ROWID = :dfs_Selger_RowID clsFrm Update Call Execute (hSqlUpdate) Call SqlSetParameter ( hSqlSelect, DBP_FETCHTHROUGH, TRUE, ‘‘) Call FetchRow_Through ( hSqlSelect, nFetchRowNumber, nInd) Call SqlSetParameter ( hSqlSelect, DBP_FETCHTHROUGH, FALSE, ‘‘)

37. End