Mostly Bollogs, I'm afraid

But occasionally, a glimmer of truth.
If you find one, please let me know.



Friday 14 February 2014

SQL - the final solution

Two blogs ago, there was a problem. Now there is not. Thanks to Clive Eisen for telling me I was being a wanker.

Here is said solution:

Table:

DROP TABLE MAPELEMENTS;
CREATE TABLE MapElements (Auto int, Element int);
Insert into MapElements values(1,2);
Insert into MapElements values(1,3);
Insert into MapElements values(1,4);
Insert into MapElements values(1,5);
Insert into MapElements values(1,6);
Insert into MapElements values(2,7);
Insert into MapElements values(2,8);
Insert into MapElements values(2,9);
Insert into MapElements values(7,10);
Insert into MapElements values(7,11);
Insert into MapElements values(10,12);
Insert into MapElements values(10,13);
Insert into MapElements values(4,12);
Insert into MapElements values(5,13);

Procedure:

DROP PROCEDURE AlarmHierarchy
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <14>
-- Description:
-- =============================================
CREATE PROCEDURE AlarmHierarchy
-- Add the parameters for the stored procedure here
@p1 int
AS
BEGIN
SET NOCOUNT ON;

with cte as (

select auto, element from MapElements where
element = @p1 
union all
select a.auto, a.element from MapElements a
INNER JOIN cte r
on a.Element = r.Auto 
)
select * from cte order by auto, element;
END
GO

C Code (to get hierarchy containing Element 12):

SQLINTEGER iAuto, iElement, dw;
sqlRet=SQLAllocStmt(hDBC, &hStmt);
if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)
SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);
swprintf_s(TSQL, L"alarmHierarchy 12");
sqlRet=SQLPrepare(hStmt, TSQL, SQL_NTS);
if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)
SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);
sqlRet=SQLBindCol(hStmt, 1, SQL_C_LONG, &iAuto, sizeof(iAuto), &dw);
if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)
SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);
sqlRet=SQLBindCol(hStmt, 2, SQL_C_LONG, &iElement, sizeof(iElement), &dw);
if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)
SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);
sqlRet=SQLExecute(hStmt);
if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)
SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);
while(true)
{
sqlRet=SQLFetch(hStmt);
if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO&&sqlRet!=SQL_NO_DATA)
{
SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);
break;
}
if (sqlRet==SQL_NO_DATA)
break;
}
SQLFreeStmt(hStmt, SQL_DROP);

Gratuitous picture of Norks :


No comments: