1 / 37

Kap 12

Kap 12. Dynamisk SQL. Flerbrukersystem Client / Server. Client. Server. Database. DBMS. Application_1. SQL-Request. Data. Application_2. Application_3. Resultatsett. Client. Server. Database. DBMS. SQL-Request. Data. Tabellen Selger. Application. SNr Navn PNr

jalen
Download Presentation

Kap 12

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Kap 12 Dynamisk SQL

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

  3. 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

  4. 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.

  5. 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 )

  6. 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

  7. 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’

  8. 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 )

  9. 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’

  10. 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 ) ...

  11. 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 ) ...

  12. 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 ) ...

  13. 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

  14. 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 ...

  15. 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

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

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

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

  19. 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

  20. 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( )

  21. 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

  22. 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

  23. 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

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

  25. 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

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

  27. clsMultiline clsMultiline SetDbColumnName

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

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

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

  31. 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, ‘‘)

  32. End

More Related