How to resolve Database naming convention issues ?

prais1852 over 6 years ago

Hi Community,

I am using Microsoft SQL Server for my Application Application. In my CDT (Say Employee) I have an array field (Say Skills) to store checkbox values but the problem is that when i go to data store to verify it and publish. It asks for Employee_Skills table. if i define it as EmployeeSkills then it throws an error saying table not found. Also by default it generates weird column names in the new table. How can i control naming convention of my foreign key table and choose friendly column name ?

I checked everywhere in CDT's XSD file also but i couldn't find any way to change table/column name.

Discussion posts and replies are publicly visible

0 Carlos Santander
A Score Level 2
over 6 years ago

Take a look at the supported JPA annotations (link below.) In particular, you'll want to look at @Table and @Column.

docs.appian.com/.../Supported_XSD_Elements_and_JPA_Annotations.html
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Reject Answer

Cancel

0 Rama Thummala

Certified Lead Developer

over 6 years ago

prais1852 , The below snippet would help you. Please modify accordingly in your .xsd file.

<xsd:complexType name="Emplyee">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
@Table(name="Emplyee_dbtable")
</xsd:appinfo>
</xsd:annotation>
<xsd:sequence>

----------------

----------------

---------------------

0 prais1852 over 6 years ago in reply to Rama Thummala

Hi and Carlos Santander Here is my CDT and XSD. It asks for CCCDetails_NodesAffected and CCCDetails_ToolsAffected tables when i try to publish my CDT to Data Store. How can i overwrote table and column names ? I can't find CCCDetails_NodesAffected and CCCDetails_ToolsAffected table names in my CDT. Please help me to resolve this. I want name to be without underscore i.e. CCCDetailsNodesAffected and CCCDetailsToolsAffected and similarly i want to overwrite the column names as well. I have two fields as Array and i do not want to create Nested CDTs.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types:CCCD" targetNamespace="urn:com:appian:types:CCCD">
  <xsd:complexType name="CCCDetails">
    <xsd:annotation>
      <xsd:documentation><![CDATA[Information about CCC in the CCC Application]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="CCCNumber" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Id @GeneratedValue</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Approved" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="ApprovalDate" nillable="true" type="xsd:date" />
      <xsd:element name="PRNumber" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="ECR" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Description" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=200)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Class" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Reclass" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="SixMonthsMaterialRequired" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="ChangeType" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="NotificationDate" nillable="true" type="xsd:date" />
      <xsd:element name="CCCPresenter" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="InEccos" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="DateAddedToEccos" nillable="true" type="xsd:date" />
      <xsd:element name="CEOldBomPN" nillable="true" type="xsd:string" />
      <xsd:element name="OldRevision" nillable="true" type="xsd:string" />
      <xsd:element name="CENewBomPN" nillable="true" type="xsd:string" />
      <xsd:element name="NewRevision" nillable="true" type="xsd:string" />
      <xsd:element name="ProductGroupAffected" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element maxOccurs="unbounded" minOccurs="0" name="ToolsAffected" nillable="true" type="xsd:string" />
      <xsd:element maxOccurs="unbounded" minOccurs="0" name="NodesAffected" nillable="true" type="xsd:string" />
      <xsd:element name="ApprovedBy" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Spared" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Sbom" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BetaRequired" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BetaRequested" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Bundled" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BundleQtr" nillable="true" type="xsd:int" />
      <xsd:element name="BundlePresentDate" nillable="true" type="xsd:date" />
      <xsd:element name="QualStatus" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="QualPartsDate" nillable="true" type="xsd:date" />
      <xsd:element name="QualApprovalDate" nillable="true" type="xsd:date" />
      <xsd:element name="Comments" nillable="true" type="xsd:string" />
      <xsd:element name="CreatedBy" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="CreatedOn" nillable="true" type="xsd:dateTime" />
      <xsd:element name="UpdatedBy" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="UpdatedOn" nillable="true" type="xsd:dateTime" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

0 prais1852 over 6 years ago in reply to prais1852

Hi Sorry to disturb you again. Can you give your expert advice on this one also if possible. I am finding it difficult to maintain naming convention in MS SQL Server ?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Mike Schmitt
Certified Lead Developer
over 6 years ago in reply to prais1852

I'm not really an expert at creating DB tables straight from XSD... but by looking at your XSD and the "tables" you're being asked for when you try to publish, its because you haven't pointed NodesAffected or ToolsAffected at any DB table column. You need to use the XSD syntax already pointed out by i'm guessing.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Reject Answer

Cancel
0 prais1852 over 6 years ago in reply to prais1852

Hi and Carlos Santander I am still looking for help. Can you help with above mentioned problem ?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

0 Rama Thummala

Certified Lead Developer

over 6 years ago in reply to prais1852

prais1852 , Please check the attached .xsd for reference. This would help you.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types" targetNamespace="urn:com:appian:types">
  <xsd:complexType name="EMPLOYEE_TYPE">
    <xsd:annotation>
      <xsd:appinfo source="appian.jpa">
			@Table(name="EMPLOYEE")
			</xsd:appinfo>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="Id" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="ID") @Id @GeneratedValue</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="empId" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="EMPID")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="dateTime" nillable="true" type="xsd:dateTime">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="DATE_TIME")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="createdUser" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="CREATED_USER", length=50)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="createDate" nillable="true" type="xsd:dateTime">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="CREATED_DATE")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="modifiedUser" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="MODIFIED_USER", length=50)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="modifiedDate" nillable="true" type="xsd:dateTime">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="MODIFIED_DATE")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="isActive" nillable="true" type="xsd:boolean">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="IS_ACTIVE")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="isPosted" nillable="true" type="xsd:boolean">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="IS_POSTED")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

0 prais1852 over 6 years ago in reply to Rama Thummala
Thanks for the example. But if you can see the screenshot and XSD i have attached. There are two fields ToolsAffected and NodesAffected which are array in my Designer CDT and when i go to publish this CDT Appian automatically asks to create two more table. In XSD i can't see the table/column name which appian suggests. How can i overwrite the name in that case ?

I can see only -

<xsd:element maxOccurs="unbounded" minOccurs="0" name="ToolsAffected" nillable="true" type="xsd:string" /> <xsd:element maxOccurs="unbounded" minOccurs="0" name="NodesAffected" nillable="true" type="xsd:string" />

and Appian is creating other two definition table internally. How can i find them to edit the table and column names ?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 prais1852 over 6 years ago in reply to prais1852

Hi and nageswararaoa Can you please help me with this ?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
Certified Lead Developer
over 6 years ago in reply to prais1852

Is this for an existing database structure?

If so, what does the structure look like?

If not, how exactly do you think this would be stored? What Appian is suggesting is you need a normalised structure for those values, which is quite correct. Additionally, as has already been noted, your XSD doesn't contain column or table mappings.

Have you actually read the documentation for data types and XSDs? The documentation has been updated relatively recently and is really quite helpful.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel