Use of %SQL_Diag logging along with all-new LOAD DATA functionality
In this article I will explain the usage of %SQL_Diag.Result and %SQL_Diag.Message table along with all-new LOAD DATA functionality.
It is recommended to go through LOAD DATA documentation first.
After successful operation LOAD DATA insert one record in %SQL_Diag.Result table and details are inserted in %SQL_Diag.Message table
Below is the basic command when table is already created and source file does not contain header row.
LOAD DATA FROM FILE 'C://TEMP/mydata.txt' INTO MyTable
The file name must include a .txt or .csv (comma-separated values) suffix and both source and target have the same sequence of data columns.
Loading from File Source: Header
To specify that the data file has a header row, use the header boolean parameter, as shown in the following example:
LOAD DATA FROM FILE 'C://TEMP/mydata.txt' INTO Sample.Employees USING {"from":{"file":{"header":"1"}}}
I am using same command in my Medical Datasets Application
SET qry = "LOAD DATA FROM FILE '"_filename_"' INTO "_tableName_ " "_"USING {""from"":{""file"":{""header"":""1""}}}"
SET rset = ##class(%SQL.Statement).%ExecDirect(,qry)
Here filename is a complete file path and tableName is a table name where data needed to be loaded.
%SQL_Diag Logging
Please note that after every successful operation LOAD DATA will insert record into %SQL_Diag.Result and %SQL_Diag.Message tables.
%SQL_Diag.Result table
Below is the structure of %SQL_Diag.Result
In order to get the detail we need to get maximum ID from %SQL_Diag.Result table after LOAD DATA operation.
Below is the SQL command to get the Maximum ID:
SET qry = "SELECT id FROM %SQL_Diag.Result WHERE ID = (SELECT MAX(ID) FROM %SQL_Diag.Result )"
Let us suppose select of MAX(ID) return 5 from %SQL_Diag.Result table which I demonstrated in my open exchange application preview.
We can use Management Portal SQL or $SYSTEM.SQL.Shell() to view the details
SELECT * FROM %SQL_Diag.Result WHERE ID = 5
%SQL_Diag.Message table
In case of errors, system is saving number of errors in errorcount column of %SQL.Diag.Result table.
These errors can be viewed in %SQL_Diag.Message table by using %SQL.Diag.Result ID against %SQL_Diag.Message diagResult column.
Below is the structure of %SQL_Diag.Message table
Below is the command to check errors details:
SELECT * FROM %SQL_Diag.Message WHERE severity = 'error' and diagResult = 5
%SQL_Diag.Message shows the details of the errors.
Thanks
Very nicely done!