116 lines
4.0 KiB
Plaintext
116 lines
4.0 KiB
Plaintext
Compile subroutine Test_Clean_SQL_Table(VOID)
|
|
|
|
|
|
Declare function Copy_Record_To_SQL, GetTickCount, Get.RecCount, GetASyncKeyState, SQL_Format, SQL_Delete, SRP_Com
|
|
Declare subroutine SRP_Com
|
|
|
|
Equ Update_Interval$ to 1000
|
|
|
|
Table = "WO_MAT"
|
|
Keys = "WO_NO":@FM:"CASS_NO"
|
|
KeyTypes = "INT":@FM:"INT"
|
|
NumKeys = Count(Keys, @FM) + 1
|
|
|
|
Errors = ""
|
|
|
|
// Make sure table is uppercase
|
|
Convert @LOWER_CASE to @UPPER_CASE in Table
|
|
|
|
// Create the SYSLIST key
|
|
ErrorKey = "CLEAN_SQL_TABLE_ERRORS*":Table
|
|
|
|
NumCopied = 0
|
|
|
|
StartTime = GetTickCount()
|
|
BeginTime = StartTime
|
|
|
|
// Open table
|
|
Open Table to hTable then
|
|
|
|
// Connect to the ODBC
|
|
If SRP_Com(Connection, "CREATE", "ADODB.Connection") then
|
|
|
|
// Connect to the database via ODBC
|
|
SRP_Com(Connection, "SET", "Mode", adModeShareDenyNone)
|
|
SRP_Com(Connection, "SET", "CursorLocation", adUseClient)
|
|
If SRP_Com(Connection, "CALL", "Open", "LSL2SQL", "srpadmin", "0okm9ijn") EQ "" then
|
|
|
|
// Create the where clause expression from the array of keys
|
|
KeyExpression = Keys
|
|
Swap @VM with " = " in KeyExpression
|
|
Swap @FM with " AND " in KeyExpression
|
|
|
|
// Create the list of
|
|
KeyList = ""
|
|
KeyExpression = ""
|
|
For iKey = 1 to NumKeys
|
|
KeyList<-1> = Keys<iKey>
|
|
KeyExpression<-1> = Keys<iKey>:" = ":KeyTypes<iKey>
|
|
next iKey
|
|
Swap @FM with ", " in KeyList
|
|
Swap @FM with " AND " in KeyExpression
|
|
|
|
// Script to get all SQL record keys
|
|
Script = "Select ":KeyList:" from ":Table
|
|
RecordSetResult = SRP_Com(Connection, "CALL", "Execute", Script)
|
|
|
|
// Get the whole thing
|
|
Data = SRP_Com(RecordSetResult, "CALL", "GetString", adClipString, 1, @VM, @FM)
|
|
Convert \0D\ to @FM in Data
|
|
Convert \09\ to '*' in Data
|
|
|
|
// For each key that doesn't exist, delete it
|
|
LenData = Len(Data)
|
|
Pos = 1
|
|
Loop
|
|
Key = Data[Pos, @FM]
|
|
Pos = Col2() + 1
|
|
ReadV Temp from hTable, Key, 1 else
|
|
|
|
// Create the where clause expression from the array of keys
|
|
KeyExpression = Keys
|
|
Swap @VM with " = " in KeyExpression
|
|
Swap @FM with " AND " in KeyExpression
|
|
|
|
// Script to delete the record
|
|
Script = "Delete from ":TableName:" where ":KeyExpression
|
|
|
|
// Run the script
|
|
RecordSetResult = SRP_Com(Connection, "CALL", "Execute", Script)
|
|
If RecordSetResult NE 0 then
|
|
SRP_Com(RecordSetResult, "RELEASE")
|
|
end else
|
|
Ans = "Error deleting from ":TableName:" where ":KeyExpression:@TM:@TM:SRP_Com(Connection, "ERROR")
|
|
end
|
|
|
|
end
|
|
Until Pos GE LenData
|
|
Repeat
|
|
|
|
end else
|
|
Call Msg(@Window, SRP_Com(Connection, "ERROR"):@FM:@FM:@FM:"!")
|
|
end
|
|
|
|
SRP_Com(Connection, "CALL", "Close")
|
|
SRP_Com(Connection, "RELEASE")
|
|
|
|
end else
|
|
Call Msg(@Window, "Unable to create ADO Object.":@FM:@FM:@FM:"!")
|
|
end
|
|
|
|
Open "SYSLISTS" to hSYSLISTS then
|
|
If Errors[-1, 1] EQ @FM then Errors[-1, 1] = ""
|
|
If Len(Errors) then
|
|
Write Errors to hSYSLISTS, ErrorKey
|
|
Call SRP_Editor_Open("Record", "SYSLISTS":@FM:ErrorKey)
|
|
end else
|
|
Delete hSYSLISTS, ErrorKey else null
|
|
Call Msg(@Window, "No Errors")
|
|
end
|
|
end
|
|
end else
|
|
Call Msg(@Window, "Unable to open table ":Table:".":@FM:@FM:@FM:"!")
|
|
end
|
|
|
|
Return
|