This section describes the typical SQL statements that can be used to retrieve, insert and modify the information inside the jLogBook application tables. The descriptions have used the Oracle form of SQL so may require minor adjustments to work on other kinds of databases.
You can insert a record into the LogTable table using the following command:
Insert into LOGTABLE ( (List of custom fields), Username, Console, LogDateTime, Dt, Tm, Shift, AmPm, LogID ) values ( (list of custom values), UsernameValue, LogDateTimeValue, DtValue, TmValue, ShiftValue, AMPMValue, LogIDValue)
Where:
(List of custom fields) is a comma separated list of the additional fields configured into the system that appear as columns in the logtable.
(List of custom values) is a comma separated list of values for the custom fields.
The values of the fields should take on values that are defined in the categoryLists table if they dictate (drive) a color for the grid.
Username is the name of the user to which the log will be assigned.
Console is the name of the console associated with this log. (Not obligatory)
Logdatetime is the Oracle datetime field for this log.
Dt is the date portion of Logdatetime
Tm is the time portion of Logdatetime
Shift is the shift number calculated according to the Logdatetime.
AmPm is either AM or PM.
LogID is a unique Log Identifier. To be safe, you should create this as to_char(sysdate, "yyyymmddhh24miss") || "ext" (where ext can be anything, but should be there to prevent this record being confused with records created natively at exactly the same time that a log is added).
insert into Logtable (Area, Stream, Message, Priority, Username, Console, LogDateTime, Dt, Tm, Shift, AmPm )
values ( 'dig', 1, 'We need to urgently shut down the main line', 1, 'BRubble', 'Main', sysdate, to_char(sysdate,'DD/MM/RR'), to_char(sysdate,'HH24:Mi:SS'),'1', to_char(sysdate,'AM'),
to_char(sysdate, "yyyymmddhh24miss") || "ext")
It is important to be cautious when deleting a log message from outside the application since, once deleted, that information may be irretrievable. To delete a log message or a group of messages, you need to know the criterion to be used for the delete. A typical statement will take the form:
Delete from LOGTABLE where <criteria for deletion>
Typically, the criteria for deletion could be a time period. E.g.
delete from logtable where logdatetime = to_date('19/11/02 10:02:55', 'DD/MM/RR HH24:Mi:SS')
This query will delete the single record that has this precise timestamp.
You can modify a record in the LogTable using the following command:
Update LOGTABLE set customfield1 = customfieldvalue1, customfield2 = customfieldvalue2, ……., Username = Usernamevalue, Console = Consolevalue, LogDateTime = LogDateTimeValue, Dt = DtValue, Tm = TmValue, Shift = ShiftValue, AmPm = AmPmValue Where LogDateTime = <LogDateTime of Specific Record to be Modified>
Where:
Customfieldn is the name of an additional field configured into the system that appears as a column in the logtable.
The values of the fields should take on values that are defined in the categoryLists table if they dictate (drive) a color for the grid.
Username is the name of the user to which the log will be assigned.
Console is the name of the console associated with this log. (Not obligatory)
Logdatetime is the Oracle datetime field for this log.
Dt is the date portion of Logdatetime
Tm is the time portion of Logdatetime
Shift is the shift number calculated according to the Logdatetime.
AmPm is either AM or PM.
Note: Obviously, not all the fields need to be updated.
update Logtable set Priority = 2, Message = 'We need to urgently shut down the main line: This has now been completed'
where logdatetime = to_date('26/02/03 17:00:10', 'DD/MM/RR HH24:Mi:SS'