Connect.It CFG Files
Hi All,
Does anyone know how to edit the Connect It cfg files to define links within the connectors?
I have a scenario writing from IDD to an Access Database but Connect It won't recognize the internal links in Access. Peregrine basically says they won't tell me how to do it that I have to purchase Studio for Connect It.
thanks,
Aaron
From scdb.cfg I found this which might be usefull:
Code:{ STRUCT eventregister NODETYPE = TABLE // Add all elements used for join as mandatory fields { STRING evmap MANDATORY = 1 } { STRING evtype MANDATORY = 1 } // Add a "link" element to store all the joins under the same element { STRUCT link // Create a join on eventmap, to associate each event type to its map { ARRAY eventmap NODETYPE = TABLE PIFLINK = evmap = @{....evmap} and evtype = @{....evftype} { ATTRIBUTE AllFields } } } { ATTRIBUTE AllFields Exception = evmap, evtype } }
Thank you Tommy.. I will take a look at this tomorrow
Possible solution posted in GPUG mailinglist
Code:You need to change your config file for ODBC. It should be in connectit\config\odbc folder. The file called odbcdb.cfg. Make a copy of the file. Then make the following changes: ( I am going to use an access database I have to be an example) I have a access database called test, it contains 2 tables: 1 is solution(contains solution_id, and solution), another one is problem(contains problem_id, solution_id, description). the link between solution and problem is solution_id, the relationship between solution and problem is one to many. In odbcdb.cfg, remove the previous code, put the following code { STRUCT SOLUTION TABLE = SOLUTION SOLUTION { ATTRIBUTE AllFields FIELD = SOLUTION.self } { ARRAY PROBLEM TABLE = PROBLEM PROBLEM LINK = @{..SOLUTION_ID} = PROBLEM.SOLUTION_ID { ATTRIBUTE AllFields FIELD = PROBLEM.self } } } save the config file, go back to your connect!it scenario, re-open the connector. I have tried. It should work. I have attached a screen print of how the relationship looks like in connect!it. I think that you need to restore to its original state if you are not going to use this database anymore. But this is just a thought.
Hi,
I found this on Peregrines support web under discussions:
Code:Code example: Retrieving the Default Configuration To get all the tables and fields exposed by the datasource, the following syntax applies: · VIRTUAL is the root node (no choice, need it) · AllTables iterates over all table exposed · AllFields iterates over all attributes exposed, for current table · TABLE specifies on which table the query will be launched (when retrieving data). You can define an alias for that table, just like SQL aliases (TABLE = realTable aliasTable) · self stands for the current item (table of field) { STRUCT VIRTUAL { STRUCT AllTables TABLE = self { ATTRIBUTE AllFields FIELD = self } } } Excluding Fields or Tables To exclude items from the AllTables or AllFields functions, use the EXCEPTION keyword. It can exclude a list of items (comma delimited). { STRUCT AllTables EXCEPTION=amAsset TABLE = self { ATTRIBUTE AllFields FIELD = self } } Customizing a Particular Table To build a struct out of a table, add a struct in the meta data that points to a table. { STRUCT My_amAsset TABLE=amAsset a { ATTRIBUTE AllFields FIELD = a.self } } Declaring a Single Link Between Tables i.e., building a child struct. LINK declares the SQL join (the clause will be added without processing to the current query). The join is a strict (inner) join if equals (=) is used, or an outer join if asterisk-equals(*=) is used. Note: This join should be 1-1 otherwise, you'll get one document per result row of the join. [...] { STRUCT ParentAsset TABLE = amAsset parent LINK=a.lParentId *= parent.lAstId { ATTRIBUTE AllFields FIELD = parent.self } } [...] If you reach the max number of tables supported in a select (depends on the DB engine), you can call PIFLINK instead of LINK, which will generate a brand new query. A path must be used to specify the link value [node.node.node.value]. (Not supported yet, as of 00/07/21). Declaring a Multiple (N) Link Between Tables i.e., building a child collection. In this case, a new query is built, pointing to the table defined by token TABLE. To get the current value of a field from the source table of the join, use @{...} When specifying a table, use the relative path syntax (..). This indicates that it's defined in the parent block. Note: when writing the LINK = clause, don't forget to use single quotes ( 'value' ) if the values compared are strings. [...] { STRUCT TITI TABLE = TITI a ... { ARRAY TOTO TABLE = amSoftInstall soft LINK = @{..lAstId} = soft.lStationId { ATTRIBUTE AllFields FIELD = soft.self } } [...] Note: Currently in Connect.It!, you need to explicitly select the field used to generate the join (in the example, it is ..lAstId) Declaring Fields as a Result of a DB Query i.e., exposing AssetCenter features as if they were fields in the amAsset table. The first step is to build a query with token QUERY; for each row of the result. A TEMPLATE will be applied to add nodes in the format. In this template, fields value can be accessed with ~Fieldn token. %n can also be used to distinguish different template applications. [...] { TEMPLATE MyTemplate { STRING fv_~Field1 TABLE=amFVAsset fv%n,amFeature f%n LINK=a.lAstId=fv%n.lAstId AND fv%n.lFeatId=f%n.lFeatId AND f%n.SQLName='~Field1' FIELD=fv%n.ValString } } [...] { STRUCT MyFeature { QUERY MyQuery TEMPLATE=MyTemplate TABLE=amFeature f,amFeatParam fp LINK=fp.TableName='amAsset' AND fp.lFeatId=f.lFeatId { ATTRIBUTE MySqlName FIELD=f.SQLName <-- define the fields of the query } } } [...] Declaring Fields as a Result of a Sub-Query In a template, if you have a struct in an array, you have to specify the link in the ARRAY block. For example, this will NOT work: [...] { TEMPLATE ObjAttrPropertyTpl { ARRAY ~Field1 TABLE = LD_OBJECT o // Here link is not defined { STRUCT ~Field2 TABLE = LD_OBJECTATTRIBUTE oa%n, LD_OCATTRIBUTE oca%n, LD_OBJECTCLASS oc%n, LD_MASTERATTRIBUTE ma%n LINK = @{....OBJECT_ROOT_IDN} = o.OBJECT_ROOT_IDN AND o.OBJECT_IDN = oa%n.OBJECT_IDN AND oa%n.OCA_IDN = oca%n.OCA_IDN AND oca%n.CLASS_IDN = oc%n.CLASS_IDN AND oc%n.CLASS_NAME = '~Field1' AND oca%n.MA_IDN = ma%n.MA_IDN AND ma%n.MA_NAME = '~Field2' { STRING OA_VAL_STR FIELD = oa%n.OA_VAL_STR } ... } } } { STRUCT LD_OBJECTROOT TABLE = LD_OBJECTROOT oroot { QUERY ObjAttrPropertyQry TEMPLATE = ObjAttrPropertyTpl TABLE = LD_OCATTRIBUTE oca { STRUCT LD_OBJECTCLASS TABLE = LD_OBJECTCLASS oc LINK = oca.CLASS_IDN = oc.CLASS_IDN AND oc.CLASS_NAME IN ($(USED_CATEGORIES)) { STRING ClassName FIELD = oc.CLASS_NAME } } { STRUCT LD_MASTERATTRIBUTE TABLE = LD_MASTERATTRIBUTE ma LINK = oca.MA_IDN = ma.MA_IDN { STRING AttributeName FIELD = ma.MA_NAME } } } { ATTRIBUTE AllFields FIELD = oroot.self } } [...] The correct syntax is this: [...] { TEMPLATE ObjAttrPropertyTpl { ARRAY ~Field1 TABLE = LD_OBJECT o LINK = @{....OBJECT_ROOT_IDN} = o.OBJECT_ROOT_IDN { STRUCT ~Field2 TABLE = LD_OBJECTATTRIBUTE oa%n, LD_OCATTRIBUTE oca%n, LD_OBJECTCLASS oc%n, LD_MASTERATTRIBUTE ma%n LINK = o.OBJECT_IDN = oa%n.OBJECT_IDN AND oa%n.OCA_IDN = oca%n.OCA_IDN AND oca%n.CLASS_IDN = oc%n.CLASS_IDN AND oc%n.CLASS_NAME = '~Field1' AND oca%n.MA_IDN = ma%n.MA_IDN AND ma%n.MA_NAME = '~Field2' ... } } } or: { TEMPLATE ObjAttrPropertyTpl { ARRAY ~Field1 TABLE = LD_OBJECT o LINK = 1 = 1 { STRUCT ~Field2 TABLE = LD_OBJECTATTRIBUTE oa%n, LD_OCATTRIBUTE oca%n, LD_OBJECTCLASS oc%n, LD_MASTERATTRIBUTE ma%n LINK = @{....OBJECT_ROOT_IDN} = o.OBJECT_ROOT_IDN AND o.OBJECT_IDN = oa%n.OBJECT_IDN AND oa%n.OCA_IDN = oca%n.OCA_IDN AND oca%n.CLASS_IDN = oc%n.CLASS_IDN AND oc%n.CLASS_NAME = '~Field1' AND oca%n.MA_IDN = ma%n.MA_IDN AND ma%n.MA_NAME = '~Field2' ... } } } Note: Do not write { STRING '~Field2' otherwise Connect.It! will substitute your quotes with spaces when generating the query. Using Pointers in Scheduled Mode First, you need to define a variable. [...] { ATTRIBUTE vPointer DEFINEVARS= VALUE = [amAsset.dtLastModif] } [...] Then, call it on the table you want to apply it to. (The connector provides the value to compare to the field pointed to). [...] { STRUCT amAsset TABLE=amAsset a OnSchedule= a.dtLastModif >= [vPointer] { ATTRIBUTE AllFields FIELD = a.self } } [...] Setting Fields as Mandatory When fields are hidden ( by using EXCEPTION = ...) they are not visible from child blocks. To make them visible, you must set them as MANDATORY = 1. This field will be considered as the main table field for consumed format [...] { STRUCT table1 TABLE=table1 t1 ... { ATTRIBUTE AllFields FIELD = t1.self EXCEPTION = t1.field1 } { ARRAY table2 TABLE=table2 t2 LINK = t1.field1 = t2.field2 { ATTRIBUTE FIELD = t2.self EXCEPTION = t2.field1 } } { STRING str1 FIELD = t1.field1 MANDATORY = 1 } } [...] Hiding a Note from an In or Out Format If you want to specify 2 different descriptions in the same description file (one for in format, one for out format) use the MODEIN or MODEOUT properties. Possible values 0 or 1. [...] { STRUCT table1 // Remove this node and its children from all consumed formats // it will be available only for produced format MODEIN = 0 TABLE=table1 t1 ... { ATTRIBUTE AllFields FIELD = t1.self EXCEPTION = t1.field1 } { ARRAY table2 TABLE=table2 t2 LINK = t1.field1 = t2.field2 { ATTRIBUTE FIELD = t2.self EXCEPTION = t2.field1 } } { STRING str1 FIELD = t1.field1 MANDATORY = 1 } } [...] Specifying the Value of a Foreign Key Use the FOREIGNKEY =@{...} property to specify that a field is a foreign key and that its value comes from the data relative pifPath specified in @{...}. Possible values 0 or 1. [...] { STRUCT DstAsset TABLE = Asset MODEOUT = 0 { STRUCT User TABLE = User // Use PIFLINK to force the creation of a new query // No need to specify the link because all values comes from the data PIFLINK = { LONG lUserId FIELD = lUserId // 2 to get the field in the query but do not display in the format MANDATORY = 2 } { ATTRIBUTE AllFields EXCEPTION = lUserId FIELD = self } } { LONG lAstId FIELD = lAstId // 2 to get the field in the query but do not display in the format MANDATORY = 2 } { LONG lUserId FIELD = lUserId FOREIGNKEY = @{User.lUserId} } { ATTRIBUTE AllFields EXCEPTION = lAstId,lUserId FIELD = self } } [...]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks