Welcome to Telelogic Product Support
  Home Downloads Knowledgebase Case Tracking Licensing Help Telelogic Passport
Telelogic System Architect (steve huntington)
Decrease font size
Increase font size
Topic Title: How to display individual properties in a SQLServer Statement.
Topic Summary: You know we have individual properties held in the properties field. Who want to look through that though?
Created On: 7-Feb-2007 02:37
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
Subscribe to this topic Subscribe to this topic
E-mail this topic to someone. E-mail this topic
Bookmark this topic Bookmark this topic
View similar topics View similar topics
View topic in raw text format. Print this topic.
 7-Feb-2007 02:37
User is offline View Users Profile Print this message


Duane Hennessy

Posts: 112
Joined: 18-May-2006

As I'm always running SQL queries on SQLServer for System Architect, I decided to create a formula that would extract properties individually from the Properties field and display them as individual fields in the returned recordset. Basically using the Left, Substring and Patindex functions you can display almost all of the properties. There are some exceptions:

1. Where the field exceeds the number of characters that can be converted to varchar.
2. Where the property is the last or only property in the property field, which just means you slightly adapt the query and can still extract it.

The query SQL string to return a single known property as a field is attached. The syntax for each function is:

SUBSTRING(FIELD name,START character,NUMBER of characters to extract)

PATINDEX(PATTERN,FIELD name)

CHARINDEX(SEARCH char(s),FIELD name,NUMERIC location start)

CAST(FIELD name as DATA-TYPE)

The syntax for the formula is:

SELECT

LEFT(SUBSTRING(SUBSTRING(Properties,PATINDEX(escaped brackets + property name + brackets + return char + form-feed char),Properties),enough character length to cover all detail of property we're extracting),start substring at the end of escaped brackets + property name + brackets + return char + form-feed char + one extra char, enough chars to cover what the patindex returned), CAST(CHARINDEX(brackets at the beginning of the next property name, after the beginning of the propery name we are looking at) casted to integer) minus 3 characters to remove the three brackets from the return. ) column_name

FROM ENTITY
WHERE TYPE = type_number

!!! NOTE: Ensure you know how many records must be returned by doing a simple SELECT NAME FROM ENTITY WHERE TYPE = ??? because if you do not provide enough characters in the SUBSTRING function it will filter down to those it can capture. So in the attached code example, 400 chars might not provide all of the records I need because some Role Descriptions may exceed this. In that case I'll just put in 1000 or as many as I need until the recordset returns the expected number of rows.

Also if there is not properties we are looking for, junk will be returned from the other properties instead. I simply filtered them records out and used a UNION query. To speed this up I also indexed the Properties field and used the FREETEXT function to filter the results.

Any comments or question please post here :-)




Edited: 12-Feb-2007 at 10:47 by Duane Hennessy
Report this to a Moderator Report this to a Moderator
 12-Feb-2007 00:27
User is offline View Users Profile Print this message


Duane Hennessy

Posts: 112
Joined: 18-May-2006

Making it useful. So we can view the properties within an NText field in System Architect as separate columns with the above select statement. We can make this useful for doing updates to the meta-properties via a SQL query (in SQLServer)

Today I had to update the Module property from value FI-AR to FI. First I tested the result by creating two columns, the Properties field column, and the result I want Column.

Like so...

select id, properties,
left(cast(properties as varchar(2000)),cast(charindex('[[[Module]]]',cast(properties as varchar(2000))) as int)-1)
+ '[[[Module]]]' + char(13) + char(10) + 'FI' + char(13) + char(10) +
substring(cast(properties as varchar(2000)),charindex('[[[Module Application]]]',cast(properties as varchar(2000))),2000) as result_i_want
from entity
where id=88888

So what I am doing is extracting the left side of the Properties field up to, but not including, "[[[Module]]]". I then append the property and value I want to appear with return chars:

+ '[[[Module]]]' + char(13) + char(10) + 'FI' + char(13) + char(10)

I then append the rest of the Properties field to the end, knowing the next property is "[[[Module Application]]]".

+ substring(cast(properties as varchar(2000)),charindex('[[[Module Application]]]',cast(properties as varchar(2000))),2000)

I included the ID field because without it Encyclopedia Manager in Version 10.0.22 doesn't expand upon the Properties field when the cursor hovers over it.

The two resulting column, properties and result_i_want show the before and after shots of how the properties field will look once I have updated it. To be doubley sure I have got this right, I selected each column and saved them into two separate text files and ran a file compare over them. The result of the file compare was:

fc fileone.txt filetwo.txt
Comparing files fileone.txt and FILETWO.TXT
***** fileone.txt
[[[Module]]]
FI-AR
[[[Module Application]]]
***** FILETWO.TXT
[[[Module]]]
FI
[[[Module Application]]]
*****

Exactly what I want. Now to run the update I changed the SQL string a little:

update entity set properties=
left(cast(properties as varchar(2000)),cast(charindex('[[[Module]]]',cast(properties as varchar(2000))) as int)-1)
+ '[[[Module]]]' + char(13) + char(10) + 'FI' + char(13) + char(10) +
substring(cast(properties as varchar(2000)),charindex('[[[Module Application]]]',cast(properties as varchar(2000))),2000)
where id=88888

In this case I knew the ID number of the record to change. If I was doing a blanket change across many properties I would have to either:

- WHERE Properties Like '%[[][[][[]Module]]]' + char(13) + char(10) + 'FI%'

or if I have an indexed Properties field I could use:

- select id, properties from entity where Contains(Properties,'Module near FI-AR' )

The latter would work well unless there is a FI-AR1 or some such value.

Of course, always test the results on a Test Encyclopedia first.

Any questions please post. Hope this helps.

 

 

Report this to a Moderator Report this to a Moderator
Statistics
20925 users are registered to the Telelogic System Architect forum.
There are currently 0 users logged in.
The most users ever online was 16 on 30-Oct-2008 at 14:46.
There are currently 0 guests browsing this forum, which makes a total of 0 users using this forum.
You have posted 0 messages to this forum. 0 overall.

FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.