Wednesday, December 9, 2015

DataBase Driven Blender: Creating Objects in Blender from DB Data

Introduction

Currently I am working in a project team installing a particle accelerator. Like with every other complex machinery this involves a large amount of cabling works to connect all kinds of devices with all kinds of cables and cable types. These cable types can roughly be grouped according to their function: power cables, Ethernet cables, interlock cables, etc. Quite often these different groups of cables are laid by different teams or even different companies along the same cable paths in a short period of time. Hence, it is crucial that these teams work coordinately and according to a transparen plan so they do not interfere with each other during their works.
As I am a big fan of 3D graphics I wanted to create 3D visualizations of the cable paths assigned to the various cable types and because I am a big fan of Blender, I wanted to create the required images there. The output of my efforts look like this:


What you see here is a look into the "cave" (a room created by large concrete blocks shielding radiation) where the accelerator will be installed. The 3D-Model data of the concrete blocks and the cable support structures is coming from CATIA data provided by our mechanical engineering deprtments - another article of mine describes the export from CATIA and import into Blender. The grating-like structures carried by orange support structures are cable trays where the cables are to be laid, the colored tube structures are the cable paths for the different able groups. The dimension along the horizontal axis is roughly 20m.

Environment

Initially, the data on cable trays and routes came from 2D Autocad drawings provided by engineers. Based on these drawings I created a MS SQL Server database model holding data on cable routes, including the x,y,z node coordinates of the cable paths and the definition of path segments which can be added to a end-to-end cable path from start device to an end device.
The database model has the following structure:


This database structure can be accessed via a view combining the data:

SELECT        dbo.CableRoutePathIDs.RoutePathID, dbo.CableRoutePaths.SegmentSortOrder, dbo.CableRoutePaths.RouteSegmentID, dbo.CableRoutePathIDs.RoutePathUser,                        dbo.CableRoutePathIDs.Description, dbo.CableRouteSegments.Start_NodeID,
                         dbo.CableRouteNodes.GsiQuadrantX * 720 + dbo.CableRouteNodes.DistToGsiQuadrantX_cm AS StartX,
                         dbo.CableRouteNodes.GsiQuadrantY * 720 + dbo.CableRouteNodes.DistToGsiQuadrantY_cm AS StartY, dbo.CableRouteNodes.HeightOverFloor_cm AS StartZ,
                         dbo.CableRouteSegments.End_NodeID, CableRouteNodes_1.GsiQuadrantX * 720 + CableRouteNodes_1.DistToGsiQuadrantX_cm AS EndX,
                         CableRouteNodes_1.GsiQuadrantY * 720 + CableRouteNodes_1.DistToGsiQuadrantY_cm AS EndY, CableRouteNodes_1.HeightOverFloor_cm AS EndZ,
                         dbo.CableRouteNodes.GSICoordX_m AS StartX2, dbo.CableRouteNodes.GSICoordY_m AS StartY2, CableRouteNodes_1.GSICoordX_m AS EndX2,
                         CableRouteNodes_1.GSICoordY_m AS EndY2
FROM            dbo.CableRouteNodes INNER JOIN
                         dbo.CableRouteSegments ON dbo.CableRouteNodes.NodeID = dbo.CableRouteSegments.Start_NodeID INNER JOIN
                         dbo.CableRouteNodes AS CableRouteNodes_1 ON dbo.CableRouteSegments.End_NodeID = CableRouteNodes_1.NodeID RIGHT OUTER JOIN
                         dbo.CableRoutePathIDs INNER JOIN
                         dbo.CableRoutePaths ON dbo.CableRoutePathIDs.RoutePathID = dbo.CableRoutePaths.RoutePathID ON
                         dbo.CableRouteSegments.PathSegmentID = dbo.CableRoutePaths.RouteSegmentID
Last but not least I needed a python script accessing the database and creating the tube-shaped objects along the paths given by the definitions of the cable paths. The view provides the following data:

  • [0] RoutePathID: the identifier of the cable route path (1 per cable group)
  • [1] SegmentSortOrder: the cable follows the path along a certain chain of path segments
  • [2] RouteSegmentID: identifier of current path segment
  • [3] RoutePathUser: as described above, each of the path is assigned to different cable groups which are under the supervision of a team
  • [4] Description: long text description of the path segment
  • [5] StartNodeID: Start node of path segment
  • [6,7;10,11] Start/End GSIQuadrantX/Y: the accelerator facility area is segmented in to squares of 720 x 720 cm providing a coordinate system with specific 0-point
  • [9] EndNodeID: End node of path segment
  • [8;12] Start/End HeightOverFloor_cm: z-coordinate
  • [13-16]Start/End  GSICoordX/Y_m: absolute distance to 0-point in x-direction


Blender Script

One challenge I had to overcome during the development of the script was the access to the python module PYODBC which provides an easy to use API to access databases, among which the MS SQL Server. I documented the solution I found - and which works pretty well in my case - in another blog post.
The Blender script implementation looks as follows:
import sys
pyodbcPath = 'E:\\Progs\\Anaconda\\envs\\python342\\Lib\\site-packages'
systemPaths = sys.path
checkResult = [s for s in systemPaths if pyodbcPath in s]
#check if path to pyodbc exists in sys and add if needed
if (checkResult == []):
    sys.path.append(pyodbcPath)
   
import pyodbc
import bpy
from mathutils import Vector
import bmesh
def createBevelObject(parentID):
    bpy.ops.curve.primitive_nurbs_circle_add(radius=0.05,layers=(False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,True))
    bevelObj = bpy.context.selected_objects[0]
    return (bevelObj)
dbConnection = pyodbc.connect('DRIVER={SQL Server};SERVER=<DBSERVERNAME>;DATABASE=>DBNAME>;UID=<DBUSERNAME>;PWD=<DBPASSWORD>')
dbCursor = dbConnection.cursor()
queryStatement = "SELECT * FROM dbo.CablesPathsDefinitionsWithOwners ORDER BY RoutePathID, SegmentSortOrder"
splineCounter = 0
oldPathID = ""
vertexList = []
scaleFactor = 100.0
outText = ""
bevelObject = createBevelObject(oldPathID)
for dbRow in dbCursor.execute(queryStatement):
    currentPathID = dbRow[0]
    if (oldPathID != currentPathID):
        if (len(vertexList) != 0): #vertexList must not be empty
            # Finalize curve creation with given list of vertices
            bpy.context.scene.objects.link(objectData)
            polyline = curveData.splines.new('POLY')
            polyline.points.add(len(vertexList)-1)
            for vertexCounter in range(len(vertexList)):
                x,y,z = vertexList[vertexCounter]
                outText = outText + oldPathID + ";" + str(vertexCounter) + ";x:" + str(x) + ";y:" + str(y) + ";z:" + str(z) + ";" + "\n"
                polyline.points[vertexCounter].co = (x,y,z,1)          
        vertexList = []
        if (dbRow[6] != None and dbRow[7] != None and dbRow[8] != None and dbRow[10] != None and dbRow[11] != None and dbRow[12] != None):
            # if oldPath != newPath create new curve object and fill with vertices afterwards
            # Prepare curve creation
            curveData = bpy.data.curves.new(currentPathID, type='CURVE')
            curveData.dimensions = '3D'          
            #curveData.bevel_depth = 1 / scaleFactor
            # Create new curve
            objectData = bpy.data.objects.new(currentPathID, curveData)
            curveData.bevel_object = bevelObject
            objectData.location = (0,0,0)
            oldPathID = currentPathID
            # add coordinates to vertexList
            if (dbRow[13] != None and dbRow[14] != None):
                x = float(dbRow[13])
                y = float(dbRow[14])        
            else:  
                x = float(dbRow[6]) / scaleFactor
                y = float(dbRow[7]) / scaleFactor
            z = float(dbRow[8]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            vertexList.append(Vector((x,y,z)))
            if (dbRow[16] != None and dbRow[15] != None):
                x = float(dbRow[15])
                y = float(dbRow[16])              
            else:
                x = float(dbRow[10]) / scaleFactor
                y = float(dbRow[11]) / scaleFactor
            z = float(dbRow[12]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)          
            vertexList.append(Vector((x,y,z)))
    else:
        if (dbRow[6] != None and dbRow[7] != None and dbRow[8] != None and dbRow[10] != None and dbRow[11] != None and dbRow[12] != None):
            # add coordinates to vertexList - coordinates are not neccesarily well aligned in polyline
            # First set of coordinates from DB
            if (dbRow[13] != None and dbRow[14] != None):
                x = float(dbRow[13])
                y = float(dbRow[14])        
            else:  
                x = float(dbRow[6]) / scaleFactor
                y = float(dbRow[7]) / scaleFactor
            z = float(dbRow[8]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            theVector = Vector((x,y,z))
            if (theVector not in vertexList): # check if entry already exists in list
                vertexList.append(theVector)
            # Second set of coordinates from DB
            if (dbRow[16] != None and dbRow[15] != None):
                x = float(dbRow[15])
                y = float(dbRow[16])              
            else:
                x = float(dbRow[10]) / scaleFactor
                y = float(dbRow[11]) / scaleFactor
            z = float(dbRow[12]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            theVector = Vector((x,y,z))
            if (theVector not in vertexList):
                vertexList.append(theVector)
dbConnection.close()
This is more or less it. Despite the fact that the xyz-coordinate data needs to be very correct and aligned with the other 3D model data coming from CATIA (following the s**t-in s**t-out paradigm), the script works pretty well and creates the desired bundles of Bezier curves in blender beveled with corresponding objects.

I hope you this post helps you with your own project, keep on geekin'!
Your WolfiG

Thursday, November 26, 2015

Calling 3rd Party Python Modules in Blender Scripts

Introduction

This costed me nerves....
Yesterday I had the idea of connecting a Blender 2.76b via a Blender plugin - which still needs to be written... - to a SQL Server database installation where I keep geometric data of some objects. Furthermore I am working in a windows environment using a Anaconda python installation, which I normally run with Python 2.7.6.
The python weapon of choice to connect to a database is the module PYODBC which offers convenient functions to access databases. Unfortunately, this module is not available in Blender's bundled Python installation. I did not manage to install pyodbc within the context of Blender's bundled python

So this is where my odysee started. I had the following challenges to solve:
  • Create a Python environment compatible woth Blender 2.76b
  • Turn Blender's python environment to the environment with pydbc

 Creating an Independent Python Environment

As I said I am using the Anaconda IDE. My first approach was to install plain Python 3.4.2 (required for Blender 2.76) from python.org in parallel to Anaconda. Installation and stuff wen fine, but quickly I ran into the issue that I wanted to install the pyodbc package on top of the standard installation and this was not possible as this installation requires a working VS2013 (?) environment to compile the sources. This is where I went back to Anaconda.
In Anaconda it is possible to create version dependent python environments in parallel to the intitial installation using the command
  • conda create -n <environment name> python=<version number>
The new environment can be found at the path:
  • ..\<Anaconda root path>\envs\<environment name>

To activate the environment call in the command line:
  • activate <environment name>
So whatever you have to change to adjust your Python installation for Blender needs to be done in the environment with the right version number for Blender. The following steps assume that you activated an environment with this right version.
In Anaconda modules like pyodbc can be installed very easily without the need for external tools and compilers. This is done by calling (in this example I am installing pyodbc):
  • conda install pyodbc
The conda command  fetches all the required sources from the internet, compiles them if needed and adjusts the environment so they can be used.
3rd party modules (like pyodbc) are stored at a path
  • ..\<Anaconda root path>\envs\<environment name>\Lib\site-packages
This is where Blender needs to look for the 3rd party module.

Telling Blender where to Find Things

I tried a couple of things to tell Blender where to look for pyodbc, including setting the PYTHONPATH variable following this blog post, deleting Blender's Python, etc. However, all of these approaches were very cumbersome and led to negative interferences between Blender and Anaconda - meaning either of these did not work.
Finally I found a solution which seems to me quite elegant: I don't do any system-related settings like PYTHONPATH and stuff and do everything need in my script.
I simple check if the path to pyodbc (or any other 3rd party module) is already included in the sys.path varialble of Python. If this is not the case I append the required path and import the module after having registered the path:

import sys

pyodbcPath = '..\\<Anaconda root path>\\envs\\<environment name>\\Lib\\site-packages'
systemPaths = sys.path

checkResult = [s for s in systemPaths if pyodbcPath in s]

#check if path to pyodbc exists in sys and add if needed
if (checkResult == []):
    sys.path.append(pyodbcPath)
   
import pyodbc
import bpy

[...] Your code comes here

This is it.

Thanks for reading my post, I hope you enjoyed it and it helps you with your own project.
Keep on geekin'! Your
WolfiG


Wednesday, November 25, 2015

Building Data Driven Applications: Combining Vis.js with Paper.js

Introduction

Currently I am working on a visualization project where I want display real world entities with the help of a network graph. Additional to the classical network graph and graphics I want to display information like groups of network nodes, or I want to highlight groups of nodes based on node attributes. Here an example of combined information:
The network graph displays the connection of "things"/entities and the colored areas show groups of these entities.
All the data I am using is stored in a database, so I was looking for a way to visualize these data with the least effort and media breaks. Furthermore, one factor for the choice of tools was the possibility to deploy visualized data with the least possible effort and in best case as view on "real time" data in the database.

Frameworks for Network Graphs

To achieve my task I evaluated a couple of tools and frameworks. My first choice was GEPHI, which is available in version 0.8.2. GEPHI provides means to layout quite large numbers of nodes and edges into nicely looking network graphs and it is quite popular among scientists and practitioners using networks. However, I found a couple of drawbacks which made me to leave the. GEPHI path: first of all GEPHI is a closed, standalone program. Hence, it is hard to enhance a readily layouted graph with additional information and this requires additional external tools. Furthermore it is not possible to provide interactive, real time graphs which can be presented to an end user. The GEPHI workflow requires the following steps: draw data from database (GEPHI has a direct DB interface), layouting of the network with some algorythm, export visualization to a format which allows post processing - e.g. SVG - and do the actual post processing.
Finaly I decided to abandon the GEPHI path as it involved too many steps with human intervention and turned to web-based tools. Quite quickly I found javascript frameworks as my weapon of choice and evaluated the following frameworks for network layouting: D3.js, cytoscape.js,  sigma.js and vis.js. To keep things short, I ended up with Vis.js, mostly because it was the only framework which allowed me to pin down nodes via configuration file (a JSON I export from database) and because it provides a "force atlas 2"-like layouting algorithm which yields networks layouts I was aiming for.

Frameworks for HTML5 Graphics

For the "additional" graphics frameworks I ended up quickly with the "big three": Paper.js, Processing.js and Raphael.js. I started with Raphael.js which I knew from a project I was involved before, but it quickly turned out that Raphael's SVG cannot or is hard to combine with vis.js as the latter one uses the HTML5 canvas. I didn't get warm with Processing.js as it uses its own scripting language, so finally I ended up with Paper.js, which for my taste yields satisfactory results.

Vis.js' Door to other Frameworks

The first steps of combining vis.js and paper.js were not easy. I managed to overcome the first obstacles with the help of the vis.js team (thanks for that). Based on one of the vis.js network examples I was able to hook Paper.js into Vis.js.

As the first step you have to tell Paper.js to paint on the Vis.js canvas. This can be done in the main program by the following lines:
[...] // Things required to setup network, etc.
// "mynetwork" is defined in html <body>
var container = document.getElementById('mynetwork');
var network = new vis.Network(container, data, options);
[...] // More network stuff here if needed
paperCanvas = container.firstChild.canvas;
paper.setup(paperCanvas);

The main entry points to inject other graphics into the Vis .js canvas are the Vis.js network events
  • network.on("beforeDrawing", function(ctx) { [...] }) this will draw graphics behind the network graph
  • network.on("afterDrawing", function(ctx) { [...] }) this will draw in front of th network graph
With these hook events one can start to draw Paper.js elements and display them together with the network graph. You can put any Paper.js related code replacing the square brackets. I use Vis.js Network/DataSet methods to extract geometric data on positions, etc. and pass them to Paper.js to draw elements related to the network nodes, etc.

I hope this posts helps you with your own project. Keep on geekin', your
WolfiG

Tuesday, October 13, 2015

How to Create and Deploy Custom Ribbon Entries in MS Office (including custom icons) and avoid some Pitfalls

Background

For a project in our company I wanted to create a Excel-macro-based user interface for some SQL Server-based database. The macro VBA-programming went quite well (due to some experience I already collected in this field), but I wanted to provide two buttons in the Excel 2010 ribbon to give the user the possibility to trigger macros to read/write from/to the database.
Many hints are available in the web, but I did not com across a receipe which provides the steps to create custom UIs in a concise form. Thus, and as I went into some traps, I want to share my experiences in this post.

Challenge

As described above, I wanted to provide two end-user buttons in the Excel ribbon as custom UI elements. To achieve this goal I had to problems to solve:
  • How to get the buttons into the ribbon?
  • How to deploy these custom buttons with my Excel in the most user-friendly way?

Custom Elements via Excel Options

Excel itself provides the means to create custom ribbon elements. There is a number of articles out there describing how to do this - for instance "How to Add Your Own Macros to Excel 2010" on changdoo.org. The process is quite simple and is easily achieved.
To deploy the customized ribbon to your customers, Excel offers the possibility to export the changed settings to an xml-file (called *.exportedUI) via the menu:
File --> Options --> "Customize Ribbon" menu entry --> "Import/Export" button:

As external reference see as well THIS microsoft article.

I do not have the administrator means to distribute these buttons centrally but the so created configuration file can be sent to those users who want to incorporate the new buttons into their work environment.
Nevertheless this process has some shortcomings:
  • The users have to follow a procedure to import the files with my buttons into their Excel environment 
  • One has to distribute a seperate file which has seperate versions
  • It is not possible to use customs icons for the buttons
And last but not least the export procedure creates xml-code with some traps, which become obvious if one investigates the *.exportedUI xml-code with some editor. The most critical to me is that the macro being called by "onAction" is referenced by the absolute path of the excel file where the export is made from. Here an example of a custom UI created by Excel Options:

Freshly exported to *.exportedUI, the code comes without formatting (no line breaks) and most important note the absolute path in the xml:

<mso:cmd app="Excel" dt="1" />
<mso:customUI xmlns:x2="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:x1="TFCOfficeShim.Connect.14" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">
<mso:ribbon>
<mso:qat>
<mso:sharedControls>
<mso:control idQ="mso:FileNewDefault" visible="false"/>
<mso:control idQ="mso:FileSave" visible="true" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:FileSendAsAttachment" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:Spelling" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:Undo" visible="true" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:Redo" visible="true" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:SortAscendingExcel" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:SortDescendingExcel" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:FileOpenRecentFile" visible="false" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:FileNew" visible="true" insertBeforeQ="mso:FileOpen"/>
<mso:control idQ="mso:FileOpen" visible="true"/>
<mso:control idQ="mso:PrintPreviewAndPrint" visible="true" insertBeforeQ="mso:FilePrintQuick"/>
<mso:control idQ="mso:FileSaveAsPdfOrXps" visible="true" insertBeforeQ="mso:FilePrintQuick"/>
<mso:control idQ="mso:FilePrintQuick" visible="true"/>
<mso:control idQ="mso:FileClose" visible="true"/>
</mso:sharedControls>
</mso:qat>
<mso:tabs>
<mso:tab id="mso_c4.1568D5E4" label="My New Tab" insertAfterQ="x1:IDC_TEAM_TAB">
<mso:group id="mso_c5.1568D5E4" label="My New Group" autoScale="true">
<mso:button idQ="myButtonID" label="Call My Macro" imageMso="ListMacros" onAction="Q:\FolderName\MyMacroExcel.xlsm!MyMacro" visible="true"/>
</mso:group>
</mso:tab>
</mso:tabs>

</mso:ribbon>
</mso:customUI>
PITFALL: If this is deployed to other users, the referenced macro will not be found if the Excel file is located in another location. To get around this trap you have to modify the code in *.exportedUI manually and remove the absolute path to the macro.

Custom Elements via Direct Ribbon Customization

With the above said, I was not very content with the "Excel Options"-option. After some searching I came across the "Custom UI Editor" option, which utilizes an external tool avaiable for example HERE.
With this little program one can modify the UI resources of an MS Office file directly without the need to export the changes to some external file. The UI is quite simple and offers the possibility to upload custom icons as images (any standard image format) and enter custom ribbon code directly in the Excel file:

 The resulting ribbon customization is stored inside the Excel file and is hence deployed directly with the file. The so created new ribbon entry is not visible in the "Options" dialog above.

The following code worked in MS Word 2010 and Excel 2010:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="myTab" label="MYLABEL">
<group id="testCaseTool" label="My Text">
<button id="ID1" label="Text 1" size="large" image="iconBitmap1" onAction="myMacro1" visible="true" />
<button id="ID2" label="Text 2" size="large" image="iconBitmap2" onAction="myMacro2" visible="true" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
It will create a new entry in to ribbon with two buttons calling two different macros. Add your icons and you're done!

PITFALL: Very critical is the fact that the macros referenced in the must have a special input parameter as described in the forum post "VBA error 'Wrong number of arguments or invalid property assignments' when running macro via custom button".
If one references routines created as VBA "Sub subname()", you will run into an error "Wrong number of arguments...". Hence you will have to modify your VBA code accordingly and create a new VBA sub with the right input parameter "Sub newsubname(control As IRibbonControl)".

This is how I was able to produce a satisfactory custom ribbon entry which I could deploy to my colleagues without any further hassle.

I hope you can get something out of this post for your own project - keep on geekin'

WolfiG

Wednesday, July 15, 2015

SQL Server DML Update Trigger for Multiple Rows

Background

Currently I am working on a tool creating cable labels according to some specification. In this tool  I need to perform a complex concatenation of values from about 10 columns depending on the fill status of these rows and update the label column with the concatenated value in SQL Server 2012. The concatenation should be performed in case of a single row update and of a multi-row update via UPDATE <table> SET col1=value1, ..., colN=valueN
The weapon of choice was a DML database trigger AFTER UPDATE. As I hard some hard time to achieve my goal I want to share the code with the community, maybe you can get something out of it for your own project.

Table Structure

The cable label consists mainly of location information of the start and end points of a cable (components/devices connected by the cable and the room where the device can be found). My table has a structure like this, with fields not required for the trigger operation omitted:
  • KeyField int, primary key
  • CONCAT_VALUE varchar(1000)
  • [...]
  • NUMBER int
  • START_COMP_ID varchar(100)
  • START_COMP_IS_NOMEN bit
  • START_AREA_ID varchar(50)
  • START_ROOM_NO varchar(200)
  • START_LEVEL_ID varchar(10)
  • START_BLD_ID varchar(50)
  • END_COMP_ID varchar(100)
  • END_COMP_IS NOMEN bit
  • END_AREA_ID varchar(50)
  • END_ROOM_NO varchar(200)
  • END_LEVEL_ID varchar(10)
  • END_BLD_ID varchar(50)
  • FUNCTION_ID varchar(10)
  • [...]

Database Trigger

The database trigger consists of two main parts: one executed if a single line has been changed (@@ROWCOUNT = 1, mostly for manual input in the DB editor) and one for multi-row update (@@ROWCOUNT > 1, e.g. in the case of UPDATE table SET <some column> = <new value>).
In the first case one can use IF statements checking if some column is NULL directly via selection by the primary key column. An example:
DECLARE @Buffer varchar(255) 
[...] 
IF NOT (SELECT START_COMP_ID FROM table WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
   @Buffer = <concatenation> 
[...]
UPDATE table SET CONCAT_VALUE = @Buffer WHERE KeyField =(SELECT KeyField FROM inserted); 
In case of multi-row updates I used an approach with DB cursor which loops over the INSERTED table. The IF statement has then to be based on the value coming from the cursor:
DELCARE @KeyField int
DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR SELECT KeyField FROM inserted 
[...]
OPEN tableCursor 
FETCH NEXT FROM tableCursor INTO @KeyField
WHILE @@FETCH_STATUS = 0 
  BEGIN 
    IF NOT (SELECT START_COMP_ID FROM table WHERE 
    KeyField = @KeyField) IS NULL
    [...] 
    UPDATE table SET CONCAT_VALUE = @Buffer WHERE              KeyField = @KeyField; 
    END 

Complete Code of Trigger

Here is the complete code of the trigger if you want to study it in detail:
USE [myDB]
GO
/****** Object:  Trigger [dbo].[TriggerName]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TriggerName] ON [dbo].[myTable] 
AFTER UPDATE 
AS 
DECLARE @START_COMP_ID varchar(100);
DECLARE @START_LOCATION varchar(255);
DECLARE @START_RACK varchar(255);
DECLARE @END_COMP_ID varchar(100);
DECLARE @END_LOCATION varchar(255);
DECLARE @END_RACK varchar(255);
DECLARE @FUNCTION varchar(255);
DECLARE @Buffer varchar(255);
DECLARE @KeyField int

DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT KeyField FROM inserted

BEGIN
IF @@ROWCOUNT = 0
    RETURN
SET NOCOUNT ON;
SET @Buffer = '';

-- Execute only if one line has been changed
If (SELECT COUNT(*) FROM inserted) = 1
  BEGIN
-- ID part for component/technical place
    IF NOT (SELECT START_COMP_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM insertedIS NULL
-- Component ID is not according to nomenclature
      IF (SELECT START_COMP_IS_NOMEN FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))= 1
        SET @START_COMP_ID = '<' + (SELECT START_COMP_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) + '>';
      ELSE
        SET @START_COMP_ID = (SELECT START_COMP_ID FROM myTable WHERE KABEL_ID=(SELECT KABEL_ID FROM inserted));
    ELSE
    SET @START_COMP_ID = '';
    IF NOT (SELECT ACCNOMEN_FUNCTION FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @CONCAT_VALUE = '[' + (SELECT CONCAT_VALUE FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    ELSE
      SET @ACCNOMEN_FUNCTION = '[-';
    IF NOT (SELECT NUMBER FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @CONCAT_VALUE = @CONCAT_VALUE + '.' + (SELECT NUMBER FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) + ']';
    ELSE
      SET @CONCAT_VALUE = @CONCAT_VALUE + ']';
-- ID part of location/building
    IF NOT (SELECT START_BLD_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))        IS NULL 
      SET @START_LOCATION = (SELECT START_BLD_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    ELSE
      SET @START_LOCATION = ''; 
    IF NOT (SELECT START_LEVEL_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))      IS NULL
      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_LEVEL_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    IF NOT (SELECT START_RAUM_NR FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))      IS NULL
      SET @START_LOCATION = @START_LOCATION + '.' (SELECT START_ROOM_NO FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    IF NOT (SELECT START_FLAECHE_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_AREA_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
-- Rack and stuff
    IF NOT (SELECT START_RACK FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @START_RACK = '#' + (SELECT START_RACK FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    ELSE 
      SET @START_RACK = '';
      IF NOT (SELECT START_FRAME_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
        SET @START_RACK = @START_RACK + '.' + (SELECT START_FRAME_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));
    IF NOT (SELECT START_SOCKET FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL
      SET @START_RACK = @START_RACK + '.b' +                                                          (SELECT START_SOCKET FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

----------------------------------------------------------
-- END component
----------------------------------------------------------
[...] similar statements as above, omitted for better reading

-- Final assembly of label tag string and DB update 
SET @Buffer = @START_COMP_ID + @CONCAT_VALUE + @START_LOCATION + @START_RACK + '/' + @END_KOMP_ID + @END_LOCATION + @END_RACK;
-- Insert statements for procedure here
IF @Buffer <> ''
  BEGIN                                                                                             UPDATE CABLES_LIST SET CONCAT_VALUE = @Buffer WHERE KeyField=(SELECT KeyField FROM inserted);                                                                                   END
END                                                                                             ------------------------------------------------------------------------------------------------- Multi Row Part                                                                             ------------------------------------------------------------------------------------------------
ELSE
  BEGIN
    OPEN tableCursor
    FETCH NEXT FROM tableCursor INTO @KeyField
    WHILE @@FETCH_STATUS = 0
      BEGIN
      -- ID part for component/technical place
        IF NOT (SELECT START_COMP_ID FROM myTable WHERE KeyField = @KeyField) IS NULL
      -- component ID is not according to nomenclature
          IF (SELECT START_COMP_IS_NOMEN FROM myTable WHERE KeyField = @KeyField) = 1
            SET @START_COMP_ID = '<' + (SELECT START_COMP_ID FROM myTable WHERE KeyField =                  @KeyField) + '>';
          ELSE
            SET @START_COMP_ID = (SELECT START_COMP_ID FROM myTable WHERE KeyField =                        @KeyField);
        ELSE
          SET @START_COMP_ID = '';
        IF NOT (SELECT FUNCTION FROM myTable WHERE KeyField = @KeyField) IS NULL
          SET @FUNCTION = '[' + (SELECT FUNCTION FROM myTable WHERE KeyField = @KeyField);
        ELSE
                                                                                            [...] the structure of the omitted code follows the code for the 1 row update. Statements of the type KeyField=(SELECT KeyField FROM inserted) are always replaced by KeyField = @KeyField   
SET @Buffer = @START_COMP_ID + @CONCAT_VALUE + @START_LOCATION + @START_RACK + '/' +
  @END_COMP_ID + @END_LOCATION + @END_RACK;
-- Update table and loop to next row of INSERTED table
IF @Buffer <> ''
Begin
UPDATE CABLES_LIST SET ID_ACCNOMEN = @Buffer, QR_CODE_TEXT = 'CAB:' + @Buffer WHERE KABEL_ID = @CableID;
END
FETCH NEXT FROM tableCursor INTO @CableID
END
END
END

Thursday, May 28, 2015

Mia and Me Orakelsprüche

Meine Kinder haben momentan nur EIN Thema: Mia and Me. Auf ihre Anregung hier eine Sammlung von Orakelsprüchen aus den verschiedenen Folgen.

Staffel I

Folge 1 - Ankunft in Centopia: "ICH BIN MIA"
Folge 2 - Eine neue Hoffnung: "AUCH WENN VOR QUAL DIE OHREN KLINGEN, WIRD NEUE HOFFNUNG EIN SCHRILLER TON DIR BRINGEN"
Folge 3 - Ein seltsames Orakel: "WENN AUS EINEM VIELES WIRD VERFOLG DEIN ZIEL DANZ UNBEIRRT."
Folge 4 - Der verlorene Trumptus: "DAS GESICHTSLOSE BÖSE GANZ GESCHWIND VERSTREUT DIE HOFFNUNG IN ALLE WIND(E)"
Folge 5 - Das goldene Einhorn: : "WIND UND WASSER, GOLDENES HORN, MIT ONCHAO WIRD NEUE HOFFNUNG GEBOREN
Folge 6 - Onchao und das Paradies:
Folge 7 - Beeren für Phuddle: "FEST VERKNÜPFT DURCH DES KÖNIGS HAND DEN BLICK SENK/WÄG (?) AB AUF'S KLEINE LAND"
Folge 8 - Ziggos freier Tag: "VON HÄNGENDER SCHÖNHEIT LIEBEVOLL BEWACHT UND VON FREUND UND FEIND IN GEFAHR GEBRACHT."
Folge 9 - Kleiner Drache Baby Blue: "DUNKEL IST DER BAUCH UND KEIN ENDE SICHT. FINDEN EINEN FREUND UND ENTDECKE DAS LICHT."
Folge 10 -  Der große Blütenbaum: : "EINHÖRNER KOMMEN UND EINHÖRNER GEHEN, DIE BELOHNUNG WERDEN SIE AM ENDE VOR SICH SEHEN"
Folge 11 - Polytheus im Goldrausch: "SCHÄTZE VERSTECKT IN DES DSCHUNGELS GRAB SUCHE HILFE BEI DEM DER NOCH NIE HILFE GAB."
Folge 12 - Phuddles große Stunde: "IN DER HÖHLE UNTER WEIN DER SCHATZ WIRD SEIN UND DER KLEINSTE IM KREIS ERLANGT DEN PREIS.
Folge 13 - Das Feuereinhorn: "ES KOMMT DIE ZEIT DIE VOLLENDUNG VERSPRICHT UND FREUNDE DICH BRAUCHEN, ERFÜLL DEINE PFLICHT."
Folge 14 - Der verdorrte Wald: "WO ALTE, KAHLE ÄSTE SICH RECKEN UND KLAGEN, FOLGE DEM PFAD DIE ERLÖSUNG ZU WAGEN"
Folge 15 - Die geheimnisvolle Laterne: "EIN SCHATZ KANN LÜGEN UND KANN BETRÜGEN SUCH DEN PFAD (/FARN?) WO DIE JUNGEN SICH VERGNÜGEN
Folge 16 - Die Einhornfalle: "GEH HINTER WASSER, VERWANDLE DIE LIST. KÄMPFE FÜR ONCHAO DER SCHUTZLOS IST."
Folge 17 - Die Blütenfestprinzessin: "DU SUCHST DEN PFAD, DER EINSICHT BRINGEN SOLL. FINDE DIE PFLANZE DEREN MAGEN NIE VOLL."
Folge 18 - König für einen Tag: "HINTER DER BRÜCKE, VERGOSSENE TRÄNEN. VERBOTENES HANDELN ERETTET DIE SCHÖNEN."
Folge 19 - Phuddles neueste Erfindung: "ES IST NICHT EINFACH DAS GOLD ZU ENTDECKEN, IM TIEFEN TAL NUR KANN ES SICH VERSTECKEN"
Folge 20 - Die Höhle der Wahrheit: "UM ZU BRINGEN DAS RETTENDE STÜCK ANS LICHT FÜRCHTE DIE DUNKELHEIT DER HÖHLE NICHT"
Folge 21 - Wiedersehen mit Baby Blue: "WIE DIE ELFEN ZU FLIEGEN IST WICHTIG UND LEICHT DIE ANTWORT LIEGT DORT WO LAND DEN HIMMEL ERREICHT."
Folge 22 - Die Vollmondnacht: "UNTER DER MASKE, UNTER DEM MOND, DAS IST DER ORT WO DIE SUCHE SICH LOHNT"
Folge 23 - Die defekte Wasserkuppel: "IM WASSER ZU LANDE SUCH MIT BEDACHT, DURCH LIST WIRD DER FEIND ZUM RETTER GEMACHT"
Folge 24 - Invasion der Pane:
Folge 25 - Pantheas letztes Angebot:"GANZ NAHE AN DES UNHEILS QUELLE
DER LETZTE BLIEB AN DIESER STELLE"
Folge 26 - Die große Entscheidung:

Staffel II

Folge 1 - Das Geheimnis der vier Ringe: "VOR DER MISSION EIN RÄTSEL STEHT, DAS DIR JEMAND ZUHAUS VERRÄT"
Folge 2 - Der würdige Sohn: "NUTZE DEN WIND, SPRECHE MIT STEIN UND DIE BELOHNUNG WIRD SICHERLICH DEIN."
Folge 3 - Tanz mit den Sternen: "SEI BEREIT DICH ZU ENTFERNEN, TANZE GEMEINSAM MIT FUNKELNDEN STERNEN"
Folge 4 - Der ungebetene Gast: "DER DIEB IST WO ER HINGEHÖRT"
Folge 5 - ??: "SUCHE DEN RING UN ER WIRD DEIN"
Folge 6 - ??: "DIE DUNKELELFE HAUSTE DORT, NUTZ IHREN GEIST AN DIESEM ORT"
Folge 7 - Das Fest des Bolobo: "WO NIEMAND MEHR FLÖTET UND STILLE ENTSTAND, SUCHE DEN KÜNSTLER DER EINSTMALS VERBAND"
Folge 8 - Die Brücke am Jadehorn: "DURCH DIE AUGEN DES EINHORN WIRST DU ERFAHREN, DASS DER DEN SU SUCHST NUR SCHLÄFT TROTZ GEFAHREN"
Folge 9 - Ein doppeltes Spiel: "WENN DEINE FEINDE DEINE FREUNDE FANGEN, WIRST DU VOM ERHABENEN HILFE ERLANGEN"
Folge 10 - Die Blume der Erinnerung: "Ein alter Feind den König sah, nur die Natur weiß wie's geschah"
Folge 11 - Die Feuerblume: "DIE BLUME KOSTBAR UND TEUER BEKOMMST DU NUR DURCH FEUER"
Folge 12 - Die Reise zum Nordhorn: "HAB KEINE ANGST WENN FEIDE SIND NAH, DENEN DU HILFST DIE SIND FÜR DICH DA"
FOLGE 13 - Der König der Einhörner: "WENN EUCH GEFAHR DROHT, VERZAGET NICHT, WAS EUCH VEREINT SCHLÄGT DEN BÖSEWICHT"
Folge 14 - Die Regenbogenquelle: "UM ZU BEKOMMEN WAS DU GLAUBT DAS SEI DEIN, FOLGE DER SPUR DER DINGE DIE HIER NICHT DAHEIM"
Folge 15 - Ein weiser Entschluss: "WAHRE FREUNDE SIND IMMER NAH, SAGEN ADE UND BLEIBEN DOCH DA"
Folge 16 - Das wahre Gesicht: "EINER AUS DEINEM FREUNDESKREIS LÄSST DICH IM STICH UND NOCH MEHR ER WEIß"
Folge 17 - ?? : "UM ZUR DURCHQUEREN HEIßESTES FEUER BEFREIE DAS GRÖSSTE DER UNGEHEUER"
Folge 18 - Der Bann ist gebrochen: "WILLST DU DEN ZAUBER BRECHEN SCHNELL, FÜLLE DEN TRANK IN DIE QUELLE SCHNELL"

Staffel III

Folge 8 - Ein überraschender Handel: "ZU BEKOMMEN WAS SIE SUCHT SO LANGE, FINDET WAS SIE BRAUCHT DIE SCHLANGE."
Folge 9 - Die Nacht wird zum Tag: "WO SCHUPPIGE SCHWINGEN HALTEN WACHT, SUCHT NACH DER SONNE IN DER NACHT."
Folge 10 - Der Kreislauf des Lebens: "WO DIE SIEBENTE WELLE STEHT IN STILLE, ZUM LETZTEN KIND KOMMT ER DER WILLE."
Folge 11 - Verloren im Nebel: "IN VERNEBELTEM GEWIRR UND ZERKLÜFTETEM FELS, DAS EINHORN WACHT ÜBER DAS WAS DU WILLST."
Folge 12 - Auf der Suche nach Simo: "UM DEINE FEINDE ZU BESIEGEN UND SEIEN SIE IN SCHAREN, SUCH NACH DEM DER ÜBER DAS MEER GEFAHREN."
Folge 13 - Im schwarzen Wald: "UNBEWEGLICH UND PARALYSIERT, GEWINNEN KANN NUR WER HARMONIERT."

Noch ohne Zuordnung zu einer Folge:

: "AUFOPFERUNG"