165 lines
5.7 KiB
Plaintext
165 lines
5.7 KiB
Plaintext
Compile subroutine Test_Update_SQL(VOID)
|
|
|
|
Equ Update_Interval$ to 1000
|
|
|
|
Equ ErrorsOnly$ to 1
|
|
Equ NewRecordsOnly$ to 2
|
|
Equ CalledFromCopyAllTables$ to 3
|
|
|
|
$insert REACTOR_LOG_EQUATES
|
|
Table = "REACTOR_LOG"
|
|
OperationCode = 0
|
|
NumToCopy = 0
|
|
KeysToCopy = ""
|
|
Errors = ""
|
|
|
|
Declare function Copy_Record_To_SQL, GetTickCount, Get.RecCount, GetASyncKeyState, SQL_Format, SQL_Insert, SQL_Write, SQL_Write_MV, SRP_Com
|
|
Declare subroutine SRP_Com
|
|
|
|
// Make sure table is uppercase
|
|
Convert @LOWER_CASE to @UPPER_CASE in Table
|
|
|
|
// Create the SYSLIST key
|
|
ErrorKey = "COPY_TABLE_TO_SQL_ERRORS*":Table
|
|
|
|
NumCopied = 0
|
|
|
|
StartTime = GetTickCount()
|
|
BeginTime = StartTime
|
|
|
|
// 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
|
|
|
|
Open "SQL_BACKLOG" to hSqlBacklog then
|
|
If OperationCode EQ ErrorsOnly$ then
|
|
Open Table to hTable then
|
|
OldErrors = Xlate("SYSLISTS", ErrorKey, "", "X")
|
|
NumOldErrors = Count(OldErrors, @FM) + (OldErrors NE "")
|
|
RecCount = NumOldErrors
|
|
For iOldError = 1 to NumOldErrors
|
|
Key = OldErrors<iOldError, 1>
|
|
GoSub Copy_Key
|
|
Until (GetASyncKeyState(27) LT 0)
|
|
Next iOldError
|
|
end
|
|
end else
|
|
Open Table to hTable then
|
|
If Len(KeysToCopy) then
|
|
RecCount = Count(KeysToCopy, ",") + 1
|
|
For iKey = 1 to RecCount
|
|
Key = Field(KeysToCopy, ",", iKey)
|
|
GoSub Copy_Key
|
|
Next iKey
|
|
end else
|
|
ClearSelect
|
|
Select hTable
|
|
RecCount = Get.RecCount(hTable, Status, 0)
|
|
Done = 0
|
|
Loop
|
|
ReadNext Key else Done = 1
|
|
Until Done OR (GetASyncKeyState(27) LT 0)
|
|
GoSub Copy_Key
|
|
If NumToCopy GT 0 AND NumCopied GE NumToCopy then Done = 1
|
|
Repeat
|
|
end
|
|
end
|
|
end
|
|
end
|
|
|
|
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
|
|
If OperationCode NE CalledFromCopyAllTables$ then
|
|
Call Msg(@Window, "No Errors")
|
|
end
|
|
end
|
|
end
|
|
|
|
Return
|
|
|
|
Copy_Key:
|
|
|
|
DateCutoff = IConv("01/01/14", "F")
|
|
|
|
// Read the record and call the handler
|
|
Error = ""
|
|
Read @RECORD from hTable, Key then
|
|
If Key NE "" then
|
|
Open "DICT.":Table to @DICT then null
|
|
@ID = Key
|
|
|
|
// Parse record into a dimensioned array for speed
|
|
Dim Rec(71)
|
|
MatParse @Record into Rec
|
|
|
|
// List of key names and their values
|
|
Keys = "SEQ":@VM:SQL_Format(Key, "INT")
|
|
|
|
//-------------------------------------------------------------------------------------------------
|
|
// Multi-valued Fields
|
|
|
|
// REACTOR_LOG_ITEMS
|
|
If Rec(10) GE DateCutoff AND Rec(15) NE "" then
|
|
|
|
// List of data fields and their values
|
|
MvFields = "REACT_ITEM_IDS":@FM:SQL_Format(Rec(15), "STR")
|
|
* MvFields := "REACT_SERV_ID":@FM:SQL_Format(Rec(18), "INT"):@RM
|
|
* MvFields := "REACT_ITEM_QTY":@FM:SQL_Format(Rec(19), "INT"):@RM
|
|
* MvFields := "SCHEDULED":@FM:SQL_Format(Rec(26), "BIT"):@RM
|
|
* MvFields := "REACT_SERV_CAT_ID":@FM:SQL_Format(Rec(27), "INT"):@RM
|
|
* MvFields := "REACT_PROB_CAT_ID":@FM:SQL_Format(Rec(28), "INT"):@RM
|
|
* MvFields := "REACT_PROB_CAT_DESC":@FM:SQL_Format({REACT_PROB_CAT_DESC}, "STR"):@RM
|
|
* MvFields := "REACT_SERV_DESC":@FM:SQL_Format({REACT_SERV_DESC}, "STR")
|
|
|
|
RepeatFields = "REACTOR":@VM:SQL_Format(Rec(5), "STR"):@FM
|
|
RepeatFields := "ENTRY_DATE":@VM:SQL_Format(Rec(10), "DATE")
|
|
|
|
// Write the data to the SQL database
|
|
Error = SQL_Write_MV(Connection, "REACTOR_LOG_ITEMS", Keys, MvFields, RepeatFields);
|
|
|
|
end
|
|
|
|
end
|
|
end
|
|
|
|
If Len(Error) then
|
|
Errors := Key:@VM:Error:@FM
|
|
end else
|
|
Delete hSqlBacklog, Table:"*":Key then null
|
|
end
|
|
|
|
NumCopied += 1
|
|
CurrTime = GetTickCount()
|
|
If CurrTime - StartTime GE Update_Interval$ then
|
|
TotalToCopy = If NumToCopy GT 0 then NumToCopy else RecCount
|
|
AveTime = Int((CurrTime - BeginTime) / NumCopied)
|
|
TimeRemaining = INT((AveTime * (TotalToCopy - NumCopied)) / 1000)
|
|
Hours = Int(TimeRemaining / 3600) ; TimeRemaining = Mod(TimeRemaining, 3600)
|
|
Minutes = Int(TimeRemaining / 60) ; TimeRemaining = Mod(TimeRemaining, 60)
|
|
Countdown = Hours:"h ":Minutes:"m ":TimeRemaining:"s"
|
|
Call SRP_Editor_Send_Info(Table:": ":NumCopied:" / ":TotalToCopy:" -- Time Remaining: ":Countdown)
|
|
StartTime = GetTickCount()
|
|
end
|
|
|
|
return
|