¶ How-tos and Tips & Tricks
Often it is very annoying that the datetime columns in the database are just shown as floating point numbers in the SQL commander. This is how to display them as a formatted date for MS-SQL databases.
Code...Code...
SELECT DATA_ID, convert(DateTime, MODIFIED_AT-2) FROM Database;
When working at a live production database, one should usually not insert breakpoints since they will interrupt the code processing for the regular users as well. To circumvent the problem use this code.
Code...Code...
if CIP_GetUserName()="Ewe, Hendrik" then cip_breakpoint("");
There is no possibility to search all formulas in cockpit easily. The following SQL-statements might help. In the example, I search for any occurance of "_Grund".
With the help of this statement, one can get a first idea where relevant locations might be.
Code...Code...
To get more detailed information, one can use the appropriate one of the following statements to further investigate.
Disclaimer: I don't know if I consider all relevant tables! Also, I provide no SQL-statements for the tables
- CUSTOMCALC_PACKS
- OEE_OBJECT_TRANSFORMATION_ENTRY_FORMULAS
- OEE_OBJECT_TRANSFORMATIONS
- OEE_SQL_IMPORTS (are contained in OEE_FORMULAS)
- OEECALCITEM
- STATPACK_ITEMS
as they were all empty or seemed to be irrelevant to me.
Code...Code...
It can be very user-friendly to offer certain functions in the popup menu which opens when the user right-clicks in the data grid. This example shows how a function can then be applied to all selected data sets. The code has to be added to the object as a user script in the object manager.
Code...Code...
for i:=0 to Argument_SelectedIDCount-1 do
begin
dataID:=CIP_ExtractIDFromList(Argument_SelectedIDStr, i);
DoSomethingWithThisDataSet(dataID);
end;
Usually CIP_SQL returns the first column of the first record. But one can also make it return the first column of all records by initializing the target array. Before iterating over the results one should check if really more than one result was returned, since otherwise it is not stored as an one-element array but as a single value.
Code...Code...
ids:=VarArrayCreate(0,0,2); // 2=integer
ids:=cip_sql("","select data_id from db where persNumber > 12345");
if VarIsArray(ids) then
begin...
One can not see in the object manager which entry uses a validation formula. But you can use this SQL statement to find out. All you need is the object_id, which you can see on the overview page of the object manager (in this example, it is 136).
Code...Code...
select e.entry_id, entryname, validformula from entrylist el
join entry e on el.entry_id=e.entry_id
where el.object_id=136 and CalcValid>0 and len(validformula)>0
The following script may be helpful to determine whether a filter is still in use somewhere.
Code...Code...
SELECT filter.Filter_ID,filter.Name,Description,Object_Id,GROUP_NAME
, occurances.type as 'occuranceType (unused if empty)', occurances.id1, occurances.id2, occurances.name as occuranceName
FROM [dbo].[FILTER]
LEFT JOIN
(
SELECT 'CUSTOMCALC_ITEMS' as Type, CUSTOMCALC_ITEM_ID as ID1 ,CUSTOMCALC_PACK_ID as ID2, CUSTOMCALC_ITEM_NAME as name, FILTER_ID
FROM [dbo].[CUSTOMCALC_ITEMS]
WHERE not FILTER_ID is null AND FILTER_ID<>-1
UNION ALL
SELECT 'DBREPORTS' as Type, DBREPORT_OBJECT_ID as ID1 ,null as ID2, DBREPORT_NAME as name, DBREPORT_FILTER_ID as FILTER_ID
FROM [dbo].[DBREPORTS]
WHERE not DBREPORT_FILTER_ID is null AND DBREPORT_FILTER_ID<>-1
UNION ALL
SELECT 'DOWNTIMESTAT_PACKS' as Type, DOWNTIMESTAT_PACK_ID as ID1 ,null as ID2, DOWNTIMESTAT_PACK_NAME as name, FILTER_ID
FROM [dbo].[DOWNTIMESTAT_PACKS]
WHERE not FILTER_ID is null AND FILTER_ID<>-1
UNION ALL
SELECT 'EXCELEXPORT_ITEM' as Type, EXCELEXPORT_ITEM_ID as ID1 ,EXCELEXPORT_PACK_ID as ID2, EXCELEXPORT_ITEM_NAME as name, EXCELEXPORT_ITEM_FILTERID as FILTER_ID
FROM [dbo].[EXCELEXPORT_ITEM]
WHERE not EXCELEXPORT_ITEM_FILTERID is null AND EXCELEXPORT_ITEM_FILTERID<>-1
UNION ALL
SELECT 'EXCELEXPORT_PACK' as Type, EXCELEXPORT_PACK_ID as ID1 ,EXCELEXPORT_PACK_OBJECTID as ID2, EXCELEXPORT_PACK_NAME as name, EXCELEXPORT_PACK_FILTERID as FILTER_ID
FROM [dbo].[EXCELEXPORT_PACK]
WHERE not EXCELEXPORT_PACK_FILTERID is null AND EXCELEXPORT_PACK_FILTERID<>-1
UNION ALL
SELECT 'GRAPH_ITEMS' as Type, GRAPH_ITEM_ID as ID1 ,GRAPH_PACK_ID as ID2, GRAPH_ITEM_NAME as name, FILTER_ID
FROM [dbo].[GRAPH_ITEMS]
WHERE not FILTER_ID is null AND FILTER_ID<>-1
UNION ALL
SELECT 'OEE_OBJECT_TRANSFORMATIONS' as Type, OBJECT_TRANSFORMATION_ID as ID1 ,null as ID2, TRANSFORMATION_NAME as name, FILTER_ID
FROM [dbo].[OEE_OBJECT_TRANSFORMATIONS]
WHERE not FILTER_ID is null AND FILTER_ID<>-1
UNION ALL
SELECT 'SCHEDULES' as Type, SCHEDULE_ID as ID1 ,OBJECT_ID as ID2, NAME as name, FILTER_ID
FROM [dbo].[SCHEDULES]
WHERE not FILTER_ID is null AND FILTER_ID<>-1
UNION ALL
SELECT 'DBFORM_COMPONENT_PARAMETERS' as Type, param.DBFORM_INSTANCE_ID as ID1, DBFORM_COMPONENT_ID as ID2
, CONCAT(param.PARAMETER_NAME, ' (', PARAMETER_NAME, ')') as name, PARAMETER_VALUE as FILTER_ID
FROM [dbo].[DBFORM_COMPONENT_PARAMETERS] param
WHERE PARAMETER_NAME like '%filter%' AND PARAMETER_VALUE<>-1 AND NOT PARAMETER_VALUE is null AND PARAMETER_VALUE<>''
UNION ALL
SELECT 'GRAPH_PACK_PARAMETERS' as Type, GRAPH_PACK_ID as ID1, null as ID2, PARAMETER_NAME as name, PARAMETER_VALUE as FILTER_ID
FROM [dbo].[GRAPH_PACK_PARAMETERS]
WHERE PARAMETER_NAME like '%filter%' AND PARAMETER_VALUE<>-1 AND NOT PARAMETER_VALUE is null AND PARAMETER_VALUE<>''
UNION ALL
SELECT 'OBJECT_PARAMETERS' as Type, OBJECT_ID as ID1, null as ID2, PARAMETER_NAME as name, PARAMETER_VALUE as FILTER_ID
FROM [dbo].[OBJECT_PARAMETERS]
WHERE PARAMETER_NAME like 'DEFAULT_FILTER' AND PARAMETER_VALUE<>-1 AND NOT PARAMETER_VALUE is null AND PARAMETER_VALUE<>''
UNION ALL
SELECT 'STATPACK_PARAMETERS' as Type, STATPACK_ID as ID1, null as ID2, PARAMETER_NAME as name, PARAMETER_VALUE as FILTER_ID
FROM [dbo].[STATPACK_PARAMETERS]
WHERE PARAMETER_NAME like '%filter%' AND PARAMETER_VALUE<>-1 AND NOT PARAMETER_VALUE is null AND PARAMETER_VALUE<>''
) occurances
on filter.filter_id = occurances.filter_id
ORDER BY filter.filter_ID
The following script may be helpful to determine whether a data table of the cockpit database is still in use somewhere. It lists all tables and checks whether they are assigned to any (CIP-)object.
Code...Code...
SELECT * FROM
(
SELECT
lower(QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name)) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
) tables
LEFT JOIN
(
SELECT object_id, objectname, concat('[dbo].[', lower(data_table_name), ']') as data_table_name
FROM dbo.OBJECTS
where not data_table_name is null AND data_table_name<>''
UNION ALL
SELECT object_id, objectname, concat('[dbo].[', lower(REASON_TABLE_NAME), ']') as REASON_TABLE_NAME
FROM dbo.OBJECTS
where not REASON_TABLE_NAME is null AND REASON_TABLE_NAME<>''
) dataTables on tables.TableName = dataTables.DATA_TABLE_NAME
--WHERE dataTables.Object_Id is null
ORDER BY tables.TableName
The following script may be helpful to determine whether a graph pack of the cockpit database is used on any DBForm.
Code...Code...
SELECT gp.GRAPH_PACK_NAME, gp.GROUP_NAME
, c.DBFORM_COMPONENT_ID,c.DBFORM_INSTANCE_ID,c.DBFORM_COMPONENT_TYPE_ID,c.CONNECTED_OBJECT_ID,c.LABEL_CAPTION
, c.PARAMETER_NAME, c.PARAMETER_VALUE
FROM [dbo].[GRAPH_PACKS] gp
LEFT JOIN
(
SELECT c.DBFORM_COMPONENT_ID,c.DBFORM_INSTANCE_ID,c.DBFORM_COMPONENT_TYPE_ID,c.CONNECTED_OBJECT_ID,c.LABEL_CAPTION
, cp.PARAMETER_NAME, cp.PARAMETER_VALUE
FROM [dbo].[DBFORM_COMPONENTS] c
LEFT JOIN [dbo].[DBFORM_COMPONENT_PARAMETERS] cp on c.DBFORM_COMPONENT_ID = cp.DBFORM_COMPONENT_ID
WHERE c.DBFORM_COMPONENT_TYPE_ID=11
AND cp.PARAMETER_NAME = 'GRAPH_PACK_ID'
) c on c.PARAMETER_VALUE = gp.GRAPH_PACK_ID
ORDER BY gp.GROUP_NAME, gp.GRAPH_PACK_NAME
The following script may be helpful to determine whether a custom calc pack of the cockpit database is used on any DBForm.
Code...Code...
SELECT cp.CUSTOMCALC_PACK_NAME, cp.GROUP_NAME
, c.DBFORM_COMPONENT_ID,c.DBFORM_INSTANCE_ID,c.DBFORM_COMPONENT_TYPE_ID,c.CONNECTED_OBJECT_ID,c.LABEL_CAPTION
, c.PARAMETER_NAME, c.PARAMETER_VALUE
FROM [dbo].[CUSTOMCALC_PACKS] cp
LEFT JOIN
(
SELECT c.DBFORM_COMPONENT_ID,c.DBFORM_INSTANCE_ID,c.DBFORM_COMPONENT_TYPE_ID,c.CONNECTED_OBJECT_ID,c.LABEL_CAPTION
, cp.PARAMETER_NAME, cp.PARAMETER_VALUE
FROM [dbo].[DBFORM_COMPONENTS] c
LEFT JOIN [dbo].[DBFORM_COMPONENT_PARAMETERS] cp on c.DBFORM_COMPONENT_ID = cp.DBFORM_COMPONENT_ID
WHERE c.DBFORM_COMPONENT_TYPE_ID=11
AND cp.PARAMETER_NAME = 'CUSTOM_CALC_ID'
) c on c.PARAMETER_VALUE = cp.CUSTOMCALC_PACK_ID
ORDER BY cp.GROUP_NAME, cp.CUSTOMCALC_PACK_NAME
The following script may be helpful to determine whether a object calculations is still used. Older versions of the cockpit did not delete object calculations when the object got deleted.
Code...Code...
SELECT * FROM [dbo].[OEECALC]
WHERE NOT EXISTS (SELECT ObjectName from [dbo].[Objects] o where OEECALC.Object_Id = o.Object_Id)
¶ Show original Data, and Block Recalc with UserScript
The following script may be helpful if You want to se the original (formated) data without recalculation
Code...Code...
CIP_CallDBForm("","AutoIncTest","",Argument_SelectedIDs[0],False,False,True);