SQL

1
Kap 12
Dynamisk SQL
2
(No Transcript)
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 6
400 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 640
0
5
RowID
Selger (ID SNr)
Selger (ID SNr)
RowID SNr Navn PNr CAAD 5 Nilsen 5002
BACV 2 Olsen 6400 ERCB 1 Hansen 9000
EADD 4 Berg 6400
SNr Navn PNr 5 Nilsen 5002 2 Olsen
6400 1 Hansen 9000 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.
6
(No Transcript)
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
16
(No Transcript)
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 ...
18
(No Transcript)
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
clsPbMdiFirst
clsPbMdiSelect
clsPbMdiPrevious
clsPbMdiNext
clsPbMdiLast
clsPbMdiUpdate
clsPbMdiSave
clsPbMdiInsert
clsPbMdiDelete
clsPbMdiNewRow
clsPbMdiSort
clsPbMdiHelp
clsPbMdiPrint
clsPbMdiClear
21
Klasse-notasjon
ClassName
Class Variables
Instance Variables
Functions
Messages
22
(No Transcript)
23
clsSqlHandleStruct
clsSqlHandleStruct
Instance Variable SqlHandle
iv_hSql bhSqlStatus
True if iv_hSql is connected
24
clsSqlDbAccess
clsSqlDbAccess
InitClass ConnectSqlHandles DisconnectAllSqlHandle
s SetSqlStatement SetIsolationLevel SetParameter P
repare Execute ExecuteSelect FetchRow FetchRow_Thr
ough First Previous Next Last Select Update Insert
Delete RetrieveRow Error MessageBoxFetchError Sel
ect_Inst Update_Inst Insert_Inst Delete_Inst
Initierer sSqlDatabase, sSqlUser, sSqlPassword
sSqlDatabase sSqlUser sSqlPassword cv_hSql1 nC
onnected nMinHandles nMaxHandles sSqlStatement sSq
lStatement_Select
Connect nMin SqlHandles
nResultSetCount nFetchRowNumber
Henter en rad på nytt etter UPDATE
25
clsWnd
clsWnd
sMdiName sTopWndName sSql sColumn sInto sTable sWh
ere sOrder sOrderColumn sUpdateSet sInsertInto sIn
sertValue sDeleteValue sUpdates1 hWndColSort b
Exists sTableArray1 nTableArrayCont sFrom sCon
straints
sItemName sDbTableName sDbColumnName sDbTableColum
nName
SetItemName SetDbTableName Set_TbName_ColName Set_
TableArray Set_From_Constraints
Set the name of an object (frmMain, dfs_Adr_PNr)
dfs_Adr_PNr --gt Adr
dfs_Adr_PNr --gt 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 S
QL_Insert SQL_Update SQL_Delete Clear Help_TopWind
ow
SAM_Create
SAM_Create PAM_SELECT PAM_UPDATE PAM_INSERT PAM_DE
LETE PAM_CLEAR PAM_HELP SAM_Close
27
clsMdi / clsFrm / clsTbl
clsMdi
clsFrm
clsTbl
hSqlDb
hSqlSelect hSqlUpdate hSqlInsert hSqlDelete hSql n
Fetch
hSqlTbl
Set_SqlHandle Get_SqlHandle Select_Inst Update_Ins
t 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_LAS
T
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_TopLevel
clsWnd_Mdi
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
dfs_Selger_RowID
Skjult 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