Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
Replies
17 replies
Subscribers
5 subscribers
Views
7447 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
Integrations
Column Names and Quotes in Oracle. I have an existing table with all column nam
venkats533
over 10 years ago
Column Names and Quotes in Oracle. I have an existing table with all column names in CAPITAL LETTERS. In my XSD, I gave UPPER CASE letters for column name. But Verify is failing when I am trying to add this entity into a data store. Appian is converting all column names to lower case and adding a quotes around them in DDL scripts. How to resolve this issue in Appian. In other words, how do I make Appian not to use quotes around column name or use the exact name in XSD ?...
OriginalPostID-92697
OriginalPostID-92697
Discussion posts and replies are publicly visible
0
Patty Isecke
Appian Employee
over 10 years ago
You can use JPA annotations in your XSD files to define tables and column names, as well as other attributes that require specific parameters or as in your Oracle database to create schemas, tables that are case sensitive.
Check out:
forum.appian.com/.../Defining_a_Complex_Data_Type
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
venkats533
over 10 years ago
I am very much familiar with the documentation you are referring to. The issue is that in my JPA annotations I have used all Capital Letters. Yet, the DDL generated has all Lower case letter AND quotes around each column name. The quotes is triggering case sensitivity and making Appian think the table does not exist. This is specific to Oracle
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Patty Isecke
Appian Employee
over 10 years ago
Which version of Appian is this?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
venkats533
over 10 years ago
I have tried in all versions 6.2, 7.2 and 7.3, with same results
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Patty Isecke
Appian Employee
over 10 years ago
Are you using the @Column(name="") and @Table(name="") annotations and place the exact name (case sensitive) inside this annotation? Appian will correctly read and maintain that name when searching for existing tables.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
venkats533
over 10 years ago
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema
targetNamespace="
appian.expressnet.redstone.gov/.../"
xmlns:types="
appian.mms.vsak.com/.../"
xmlns:xsd="
www.w3.org/.../XMLSchema">
<xsd:complexType name="EXPRESS_NET_Users">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
@Table(name="USERS")
</xsd:appinfo>
<xsd:documentation><![CDATA[Express Net Users]]></xsd:documentation>
</xsd:annotation>
<xsd:sequence>
<xsd:element name="UserName" type="xsd:string">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
@Id
@Column(name="ID", columnDefinition="varchar(40)")
</xsd:appinfo>
</xsd:annotation>
</xsd:element>
<xsd:element name="FirstName" nillable="true" type="xsd:string">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
@Column(name="FIRST_NAME", columnDefinition="varchar(40)")
</xsd:appinfo>
<xsd:documentation>First Name of the User</xsd:documentation>
</xsd:annotation>
</xsd:element>
<xsd:element name="LastName" nillable="true" type="xsd:string">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
@Column(name="LAST_NAME", columnDefinition="varchar(40)")
</xsd:appinfo>
<xsd:documentation>Last Name of the User</xsd:documentation>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
venkats533
over 10 years ago
and the DDL generated is
/*
Data Store: EXPRESS_NET_USERS
Created: 2013-12-13T19:34:29.846Z
By: venkat.sakruti
Appian Version: 6.2.0.0
Target Database: Oracle Oracle Database 11g Release 11.1.0.0.0 - Production
Database Driver: Oracle JDBC driver 10.2.0.2.0
*/
/* UPDATE DDL */
create table "users" (
"id" varchar(40) not null,
"first_name" varchar(40),
"last_name" varchar(40),
primary key ("id")
);
/* DROP AND CREATE DDL */
/* WARNING: The DDL commented out below will drop and re-create all tables.
drop table "users" cascade constraints;
create table "users" (
"id" varchar(40) not null,
"first_name" varchar(40),
"last_name" varchar(40),
primary key ("id")
);
*/
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Patty Isecke
Appian Employee
over 10 years ago
You could try creating a synonym at the database level for this table (this means you won't have to use the schema annotation).
docs.oracle.com/.../statements_7001.htm
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
venkats533
over 10 years ago
I don't have a DBA access to the Database. Datasource is configured and provided with existing tables. That it, we have to use existing schema and no changes are allowed in schema.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
venkats533
over 10 years ago
Also synonym will not solve the column name problem. That will solve only the table name problem
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
>