Embed Size (px)
DESCRIPTION
Kap 12. Dynamisk SQL. FlerbrukersystemClient / 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. SNrNavnPNr - PowerPoint PPT Presentation
Citation preview
SQL HiA
Kap 12
Dynamisk SQL
SQL HiA
SQL HiA
Flerbrukersystem Client / Server
DatabaseDBMS
Application_2
SQL-Request
Data
Application_3
Application_1
Client Server
SQL HiA
Resultatsett
DatabaseDBMS
Application
SQL-Request
Data
SELECT SNr, Navn, PNrFROM SelgerWHERE PNr = 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Tabellen Selger
SNr Navn PNr
2 Olsen 64004 Berg 6400
Resultatsett
Client Server
Fetch
RowID
FetchThrough
SQL HiA
RowID
RowID SNr Navn PNr
CAAD 5 Nilsen 5002BACV 2 Olsen 6400ERCB 1 Hansen 9000EADD 4 Berg 6400
Selger (ID = SNr)
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
Et eksempel på en 3NF-tabell Selgermed tre kolonnerSNr, Navn og PNr RowID er en ekstra kolonne i hver tabell
som alltid kommer i tillegg til de kolonnenevi eksplisitt definerer.RowID er entydig for hver radog fungerer som en slags identifikator.
SQL HiA
SQL HiA
Statisk SQL
SNrID Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SNrID
Navn
PNr
Select
SELECT SNrID, Navn, PNrFROM Selger
Set sSelect = ‘SELECT SNrID, Navn, PNr INTO :dfnSNrID, :dfsNavn, dfnPNrFROM Selger’
Call SqlPrepare ( hSql, sSelect )Call SqlExecute ( hSql )Call SqlFetchNext ( hSql, nFetch )
SQL HiA
Statisk SQL
SNrID
Navn
PNr
Select
SELECT SNrID, Navn, PNrFROM Selger
SNrID
Navn
PNr
2
Select
SELECT Navn, PNrFROM SelgerWHERE SNrID = :dfnSNrID
SNrID
Navn
PNr 6400
Select
SELECT SNrID, NavnFROM SelgerWHERE PNr = :dfnPNr
SNrID
Navn
PNrNilsen
Select
SELECT SNrID, PNrFROM SelgerWHERE Navn = :dfsNavn
SNrID
Navn
PNrNilsen
6400
Select
SELECT Navn, PNrFROM SelgerWHERE Navn = :dfsNavn
AND PNr = : dfnPNr
SNrID
Navn
PNr%sen
6400
Select
SELECT SNrID, NavnFROM SelgerWHERE Navn LIKE ‘ || ‘\’’ ||
dfsNavn || ‘\’’AND PNr = :dfnPNr
SQL HiA
Fra Statisk SQL til Dynamisk SQL
SNrID
Navn
PNr 6400
Select
SELECT SNrID, Navn, PNrFROM SelgerWHERE PNr = :dfnPNr
Set sSelect = ‘SELECT SNrID, Navn, PNr INTO :dfnSNrID, :dfsNavn, dfnPNrFROM SelgerWHERE PNr = :dfnPNr’
Set sSelect = ‘SELECT ‘ || sColumn || ‘ INTO ‘ || sInto ||‘ FROM ‘ || sFrom‘ WHERE ‘ || sWhere
frmSelger
sColumn = ‘SNrID, Navn, PNr’sInto = ‘ :dfnSNrID, :dfsNavn, :dfnPNr’sFrom = ‘Selger’sWhere = ‘PNr = :dfnPNr’
SQL HiA
Dynamisk SQL - Initier SQL-variable
SNrID
Navn
PNr 6400
Select
frmSelger
dfnSNrID
dfsNavn
dfnPNr
pbSelect
pbSelectOn SAM_Click
Call SalSendMsg ( hWndForm, PAM_SELECT, 0, 0 )
frmSelgerMessage Actions
On PAM_SELECTSet sColumn = ‘‘Set sInto = ‘‘Set sFrom = ‘‘Set sWhere = ‘‘Call SalSendMsg ( hWndForm, PAM_SQL, 0, 0 )Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 )
1
2
SQL HiA
Dynamisk SQL - Bestem tabell-navn
SNrID
Navn
PNr 6400
Select
frmSelger
dfnSNrID
dfsNavn
dfnPNr
pbSelect
frmSelgerMessage Actions
On PAM_SELECTSet sColumn = ‘‘Set sInto = ‘‘Set sFrom = ‘’Set sWhere = ‘‘Call SalSendMsgTo ( hWndForm, PAM_SQL, 0, 0 )Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 )...
On PAM_SQLCall SalGetWindowText ( hWndForm, sWName, 20 )sFrom = SalStrRight ( sWName, SalStrLength(sWName) - 3 )
sWName = ‘frmSelger’
sFrom = ‘Selger’
SQL HiA
Dynamisk SQL - Bestem SQL-variable for dfnSNrID
SNrID
Navn
PNr 6400
Select
frmSelger
dfnSNrID
dfsNavn
dfnPNr
pbSelect
frmSelgerMessage Actions
On PAM_SELECTSet sColumn = ‘‘Set sInto = ‘‘Set sFrom = ‘’Set sWhere = ‘‘Call SalSendMsgTo ( hWndForm, PAM_SQL, 0, 0 )Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 )...
sColumn = ‘ SNrID ’sInto = ‘ :dfnSNrID ’
SQL HiA
Dynamisk SQL - Bestem SQL-variable for dfsNavn
SNrID
Navn
PNr 6400
Select
frmSelger
dfnSNrID
dfsNavn
dfnPNr
pbSelect
frmSelgerMessage Actions
On PAM_SELECTSet sColumn = ‘‘Set sInto = ‘‘Set sFrom = ‘’Set sWhere = ‘‘Call SalSendMsgTo ( hWndForm, PAM_SQL, 0, 0 )Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 )...
sColumn = sColumn || ‘, ‘ || ‘ Navn ’= ‘ SNrID, Navn ‘
sInto = sInto || ‘ :dfsNavn ’= ‘ :dfnSNrID, :dfsNavn ‘
SQL HiA
Dynamisk SQL - Bestem SQL-variable for dfnPNr
SNrID
Navn
PNr 6400
Select
frmSelger
dfnSNrID
dfsNavn
dfnPNr
pbSelect
frmSelgerMessage Actions
On PAM_SELECTSet sColumn = ‘‘Set sInto = ‘‘Set sFrom = ‘’Set sWhere = ‘‘Call SalSendMsgTo ( hWndForm, PAM_SQL, 0, 0 )Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 )...
sColumn = sColumn || ‘, ‘ || ‘ PNr ’= ‘ SNrID, Navn, PNr ‘
sInto = sInto || ‘ :dfnPNr ’= ‘ :dfnSNrID, :dfsNavn, :dfnPNr ‘
sWhere = sWhere || ‘ PNr = ‘ || ‘ :dfnPNr ‘= ‘ PNr = :dfnPNr ‘= ‘ PNr = 6400 ‘
SQL HiA
Dynamisk SQL - Bestem SELECT-statement sSelect
SNrID
Navn
PNr 6400
Select
frmSelger
dfnSNrID
dfsNavn
dfnPNr
pbSelect
frmSelgerMessage Actions
On PAM_SELECT...Call SalSendMsgToChildren ( hWndForm, PAM_SQL, 0, 0 )Set sSelect = ‘SELECT ‘ || sColumn || ‘ INTO ‘ || sInto
‘ FROM ‘ || sFrom‘ WHERE ‘ || sWhere
...
sColumn = sColumn || ‘, ‘ || ‘ PNr ’= ‘ SNrID, Navn, PNr ‘
sInto = sInto || ‘ :dfnPNr ’= ‘ :dfnSNrID, :dfsNavn, :dfnPNr ‘
sWhere = sWhere || ‘ PNr = ‘ || ‘ :dfnPNr ‘= ‘ PNr = :dfnPNr ‘= ‘ PNr = 6400 ‘
sSelect = SELECT SNrID, Navn, PNr INTO :dfnSNrID, :dfsNavn, :dfnPNrFROM SelgerWHERE PNr = 6400
SQL HiA
SQL HiA
Generering av dynamisk SQL-statement
PAM_SELECTPAM_UPDATE...
SQL BuildSQL Build
PAM_SELECTPAM_UPDATE...
12
3
clsWnd Class Variables: sSql, sColumn, sInto, sTable, sWhere, sOrder, ...
Mdi
Frm
SQL HiA
SQL HiA
Klasser (1)
clsSqlHandleStruct clsSqlDbAccess clsWnd
clsWnd_Mdi clsWnd_TopLevel
clsMdi clsFrm clsTbl
clsWnd_ChildObject
clsWnd_DfCmbMiCol
clsDf
clsDfRowID clsDfNum clsDfStr
clsCmb
clsCmbNum clsCmbStr
clsCol
clsCmbNum_AutoSelect clsCmbStr_AutoSelect
clsColRowID clsColNum clsColStr
clsMl
SQL HiA
Klasser (2)
clsPb
clsPbMdiSelect clsPbMdiFirstclsPbMdiSelect clsPbMdiPrevious clsPbMdiNext clsPbMdiLast
clsPbMdiUpdate clsPbMdiSaveclsPbMdiInsert clsPbMdiDelete
clsPbMdiNewRowclsPbMdiSort
clsPbMdiHelpclsPbMdiPrint clsPbMdiClear
SQL HiA
Klasse-notasjon
ClassName
Class Variables
Instance Variables
Functions
Messages
SQL HiA
SQL HiA
clsSqlHandleStruct
iv_hSqlbhSqlStatus
clsSqlHandleStruct
Instance Variable SqlHandle
True if iv_hSql is connected
SQL HiA
clsSqlDbAccess
sSqlDatabasesSqlUser
sSqlPasswordcv_hSql[1:*]nConnected
nMinHandlesnMaxHandlessSqlStatement
sSqlStatement_Select
nResultSetCountnFetchRowNumber
InitClassConnectSqlHandles
DisconnectAllSqlHandlesSetSqlStatementSetIsolationLevel
SetParameterPrepareExecute
ExecuteSelectFetchRow
FetchRow_ThroughFirst
PreviousNextLast
SelectUpdateInsertDelete
RetrieveRowError
MessageBoxFetchErrorSelect_Inst
Update_InstInsert_InstDelete_Inst
clsSqlDbAccess Initierer sSqlDatabase, sSqlUser, sSqlPassword
Connect nMin SqlHandles
Henter en rad på nytt etter UPDATE
SQL HiA
clsWnd
sMdiNamesTopWndName
sSqlsColumn
sIntosTablesWheresOrder
sOrderColumnsUpdateSetsInsertInto
sInsertValuesDeleteValuesUpdates[1:*]hWndColSort
bExistssTableArray[1:*]nTableArrayCont
sFromsConstraints
sItemNamesDbTableName
sDbColumnNamesDbTableColumnName
SetItemNameSetDbTableName
Set_TbName_ColNameSet_TableArray
Set_From_Constraints
SAM_Create
clsWnd
Set the name of an object (frmMain, dfs_Adr_PNr)dfs_Adr_PNr --> Adrdfs_Adr_PNr --> PNrSet the Array-values of different Tables in a SqlStatementSet the FROM Clause and the Constraint part of aSELECT SqlStatement
Call SetItemName( )
SQL HiA
clsWnd_Mdi / clsWnd_TopLevel
SAM_Create
clsWnd_Mdi
SetTopWndNameGet_SqlHandle
SQL_BuildSQL_SelectSQL_Insert
SQL_UpdateSQL_Delete
ClearHelp_TopWindow
SAM_CreatePAM_SELECTPAM_UPDATEPAM_INSERTPAM_DELETEPAM_CLEARPAM_HELPSAM_Close
clsWnd_TopLevel
SQL HiA
clsMdi / clsFrm / clsTbl
hSqlDb
clsMdi
hSqlSelecthSqlUpdatehSqlInserthSqlDelete
hSqlnFetch
Set_SqlHandleGet_SqlHandle
Select_InstUpdate_InstInsert_InstDelete_Inst
Clear
SAM_CreatePAM_FIRST
PAM_PREVIOUSPAM_NEXTPAM_LAST
clsFrm
hSqlTbl
Get_SqlHandleSelect_Inst
Update_InstInsert_InstDelete_Inst
SQL_SelectSortClear
SAM_CreatePAM_SORT
PAM_NEWROW
clsTbl
SQL HiA
clsWnd_ChildObject / clsWnd_DfCmbMiCol
sParentName
clsWnd_ChildObject
sItemValue
SqlSql_Select
Sql_UpdateSql_InsertGet_Equal
Get_MyValue
SAM_CreatePAM_SQL
PAM_CLEAR
clsWnd_DfCmbMiCol
SQL HiA
clsDf
sParentName
Get_MyValue
clsDf
SqlSql_Select
Sql_UpdateSql_Delete
PAM_ROWID
clsDfRowID
sSelect
Get_Equal
clsDfNum
sItemValue
Get_Equal
clsDfStr
SQL HiA
clsCmb
DropDownClick
Get_MyValue
SAM_DropDownSAM_Click
clsCmb
Get_Equal
clsCmbNum
Get_Equal
clsCmbStr
DropDownClick
SAM_Click
clsCmbNum_AutoSelect
DropDownClick
SAM_Click
clsCmbNum_AutoSelect
SQL HiA
clsCol
SAM_Click
clsCol
SqlSql_Select
Sql_UpdateSql_Delete
clsColRowID clsColNum clsColStr
SQL HiA
clsMultiline
SetDbColumnName
clsMultiline
SQL HiA
MdiWindow / FormWindow / TableWindow
clsSqlDbAccess: hSqlDb
clsSqlDbAccess
clsWnd_TopLevel
clsMdi
clsWnd_Mdi
clsFrm clsTbl
SQL HiA
Bruk av virtuelle funksjons-kall
f1
f1 f1
f1 f1 f1 f1
Call SalSendMsg(Obj1, Msg1…)
On Msg1 Call ..f1(…)
SQL HiA
Navn-setting
frmSelger
cmb_Selger_SNrID
ComboBox Tabell-Navn Kolonne-navn
dfs_Selger_RowIDSkjult RowID
SQL HiA
UPDATE
clsFrm
Update Call Execute (hSqlUpdate) Call SqlSetParameter ( hSqlSelect, DBP_FETCHTHROUGH, TRUE, ‘‘) Call FetchRow_Through ( hSqlSelect, nFetchRowNumber, nInd) Call SqlSetParameter ( hSqlSelect, DBP_FETCHTHROUGH, FALSE, ‘‘)
UPDATE Selger SET Navn = :cmb_Selger_Navn, PNr = :cmb_Selger_PNr WHERE ROWID = :dfs_Selger_RowID
SQL HiA
End
