Creating a DataTools Link

Creating a DataTools Link

Clicking on the New or Edit button brings up the following dialog box, allowing the creation of a new Link, or the editing of and existing selected link.

• Enter the name of your new link in the Name box. This should be a unique name.

• Select your ODBC Driver from the drop-down list box and click on the Setup... button to setup that driver. The driver will walk you through the setup options. If you have difficulties setting up your connection details, contact your database administrator.

  The box underneath will show the connection string.

  Note

  This is editable, though should only be changed with care. Under most circumstances this can be left alone.

  Some examples are:

  • Connection string: Selecting a database with a certain name (test.mdb) that is always next to the model file (as in the resultant NWD/NWF not the original):

    DBQ=%pushpath(%poppath(%currentpath),"test.mdb");DRIVER={Microsoft Access Driver (*.mdb)};

  • Connection string: Selecting a database with the same name as the original model file (e.g. AutoPlant):

    DBQ=%join(%removeext(%removepath(%sourcepath)),".mdb");DRIVER={Microsoft Access Driver (*.mdb)};

• The Hold open for application lifetime check box, when checked, keeps the connection to the dbase open whilst JetStream is open, and only closes it on exit.

• The SQL String must always start with SELECT, and is used to connect to a table within the database. Set up the query for the table by writing the SQL String.

  Some SQL String examples include:

  • SQL String: Selecting all columns from table Test whilst requiring that the column called Entity Handles matches a category/property pair called Entity Handle/Value and the column called File Name matches the original filename of the drawing:

    SELECT * FROM Test WHERE "Entity Handle" = %prop("Entity Handle","Value") AND “File Name” = $removeext(%removepath(%sourcepath));

    Here the path and the extension of the file name are being stripped so a file like c:\'model\'3rdFloorDucts.dwg would come out as 3rdFloorDucts.

  • SQL String: Selecting two columns from table Test whilst requiring that the column called Entity Handle matches a category/property pair called Entity Handle/Value:

    SELECT Name,Part FROM Test WHERE "Entity Handle" = %prop("Entity Handle","Value");

  • SQL String: Selecting all columns from table Test whilst requiring that the column called Value is within a certain range given by two category/property pairs:

    SELECT * FROM Test WHERE Value BETWEEN %prop("Pressure","Minimum") AND %prop("Pressure","Maximum");

  The full Tag list includes:

  Property tags

  • %prop("category","property") - Property of the currently selected object. Category is the name of the tab in the property windows (e.g. Item or Entity Handle) and property is the name of the property in that tab. (e.g. Value or Layer)

  • %intprop("category","property") - Property of the currently selected object. This is the same as the previous tag except instead of using the publicly visible category and property name use the internal JetStream names. The benefit of using internal names is that they are not language dependent. [This is advanced tag that will make more sense to people familiar with the JetStream API]

  File/Path tags

  • %sourcepath – This tag represents the full path and filename that the currently selected object comes from. Even when a collection of model files have been combined into a single NWD file this tag will still remember the path and filename of the original model file.

  • %currentpath - This tag represents the full path and filename of the currently loaded model. If you currently have an NWF or NWD loaded that contains many other models it will just return the path and filename of the top level NWF/NWD.

  File/Path Manipulation Tags

  • %removeext("text") – If the provided text includes a filename with an extension this tag will remove the extension.

  • %removepath("text") – If the provided text includes a path and filename this tag will remove the path and just return the filename.

  • %poppath("text") – If the provided text includes a path this tag will remove the top level. If it the text also includes a filename that will count as the top level and be removed. [e.g. %poppath(“c:\'temp”) becomes c:\' and %popath(c:\'temp\'readme.txt”) becomes c:\'temp]

  • %pushpath("text1","text2") – If text1 is a path and text2 is a file or folder name then text2 will be added onto the path in text1. [e.g. %pushpath(“c:\'test”,”model.nwd”) becomes c:\'test\'model.nwd]

  String Manipulation Tags

  • %join("text","text") – This tag simply joins the two pieces of text together. [For example %join(“c:\'”,”model.nwd”) would return c:\'model.nwd]

  Note

  Tags must not contain white space between the brackets (unless enclosed by quote marks), so

  %prop("EntityHandle","Value")

  works, but

  %prop("EntityHandle", "Value")

  does not.

• Once the string is complete, clicking anywhere in the Fields box brings up a new edit control. Type in the name of a field from the table accessed by the SQL String. Pressing enter automatically copies this name into the display name. This display name is the text that appears in the database tab in the Properties control; click on this display name to alter it. Continue to fill this in until the table contains all the information required to be seen.

DataTools Links

DataTools Links

To allow object data to be read from a database, a link between the model and the database needs to be set up. This link can be set up in two places; Global Options and File Options . Both places allow the same settings to be set up and database links in both places exist in tandem. Having the two locations gives the option of setting up a single set of database links in Global Options for multiple files to access, whilst allowing files to have their own individual database links within File Options

• Click on Tools, File Options for setting up file related database information, or Tools, Global Options for setting up global database links.

• Clicking on the DataTools tab brings up the following tab

On the left hand side of this tab is shown all links that have been added. The link names listed are user defined, and all links can be set to Active or Inactive by selecting or deselecting the check box beside each.

• New is used to set up a new Link. An unlimited number of links can be set up using this button.

• Edit is used to edit an already existing Link. Select the link to edit and press the Edit button.

• Delete is used to delete an existing Link. Select the link to be deleted and press the Delete button.

• Import can be used to import a previously exported Link list. Clicking on this button will bring up an import dialog box to enable browsing to the required file.

• Export is used to export the currently selected list of Links and all their associated information. Select multiple Links for export using the Shift and Ctrl keys whilst selecting links in the list. Clicking on this button then brings up an export dialog box to enable a destination path and filename to be set. This also gives the option of stripping the Username and Password from the exported data.

• Default restores to the configuration as shipped with JetStream