Clarity XOG - XML - WSDL -SOAP - Content package

XOG compatibility

Usually XOG version are compatible with Clarity version if there is not changes in the database structure. Differences in .xsd files could mean differences between data in Clarity.

Anyway default version association:
Clarity v.12.1 SP1
i.e. XOG 10.50.0.125

Clarity v.13.0.1.0102

Clarity v.13.1.00248
 for example  XOG V12.0.6.5471 could work with a Clarity v.13.0.1, but there is a risk...



Testing XOG from Clarity web
 v12.

……/niku/app?action=xog.client

 v13.
……/niku/nu#action:xog.client




XML Open Gateway (XOG)
web service interface that you can use to:
  *Import data
  *Export data
  *Move configuration data from one system to another
to read data from or write data to CA Clarity using XML and web services
GEL tags that can be used with XML for more advanced custom integration tasks
CA Clarity web services use XML messages that follow the SOAP standard.

WSDL Reference
https://servername.domain.com/niku/wsdl

SOAP Testing - Queries\WSDL

SOAP UI

 SOAP UI, free licence software, after downloading a Clarity  WSDL template, remove or comment all those conditions or filters that you are not using for your request.



SOAP Sonar.

SOAP Sonar

Commercial software and gratis only for a single project/query open. Very easy to work with filter parameters, because  only those parameters introduced in inputboxes will be used for the request.



Working\Saving XML Read Files
ANSI/CP1252, UTF8without BOM
Tags:
Comments:


Common filters:

EQUALS
OR
BEFORE
AFTER
BETWEEN

i.e
x,y

Custom Objects
Reading Object definition
custom_object_instances_read.xml

Reading contents/instances
custom_object_read.xml


Stock objects
Read reference.

Sort results

or when more there is more tahn one element



Arguments:



XOG-out /XOG-in

Between different environments (i.e Project)

1. XOGout a project
2. Edit XML to adapt to new enviroment 
1. Replace Projectid by an id not in use, if attribute is autonumeric, use the next id
   
3. Remove all unique tags in a Clarity instance (i.e internalTaskID)
1. from notepad: find internalTaskID=".*?"
2. Select "Regular expression"
3. checkbox ".matches newline"
4. Count (verify)
5. Replace  (with nothing)
4. (Some unique tags like taskID are unique  but the ambit is just the project  )
5. Check 
1. resources, if userid is autonumeric could be different between the environments
6. XOG-in to new environment

Between different environments (i.e User/resources)

1. XOGOut resources (rsm_resources)
1. filter by resourceID supports wildcard %
2. XOGOut Users/accounts (cmn_users)   
1. filter by userName doesn't supports wildcards, so use criteria="OR" and values separated by comma (without spaces)      
2. Password not extracted, default password=Niku2000
3. Edit XML files
4. --Users: add properties for delete to XML tags
1. Note: complete and completed/Iscomplete are synonyms      
2. i.e. complete="true" for (capscase, don't use Uppercase)
3. supported by: OBSAssocs, Groups, GlobalRights, InstanceRights, InstanceOBSRights, SkillAssocs
4. i.e. ReplaceAll "Groups" by "Groups complete="true""
5. --Resources
1. complete="true" for (optional)
2. resetCalendar="true" to
3. (*)additional fields cannot  be reset from XOG such as Booking Manager,Date of Termination, Allocations,....
6.     XOGIn     
1. Reset by hand all those attributes which could not be reset by XOG

  Content packages/Export

•  Add process (must be Active)
• 
  

 Import/Install Add-in/Upload Content Package 

• Install in Migration Mode. This option forces all content items in the package to install and overwrite old existing content. 
• A package with same version cannot be uploaded twice
• Add-in packages cannot be deleted

Databases, csv, spreadsheets and other herbs

Data export and import between databases and spreadsheets should be an easy task, but sometimes progress in time doesn't mean advance of products or functionalities, at least it is true fact when we are speaking about Microsoft products.



For instance, a task like specify a separator symbol in Excel; different than comma was so easy in the past, but now it is nightmare.

Dbvisualizer - find drivers

Database extraction

For example SQLTools or DB visualizer

-SQLTools, fast and free tool, can work without installation but requires Oracle client installed.

Config of connections:
.\GNU\SQLTools\connections.xml
or
.\SQLTools\SharedData\connections.xml

-DB visualizer supports many database drivers (even can be detected from the computer)
Support extraction to CSV files, and select a separator symbol.

Remove Newline Character. Always checked, to avoid return carriage ia a field being used as delimiter

C:\Users\myuser\.dbvis\config70\dbvis.xml

Notes: There is an issue with  DBvisualizer/Gnumeric, default windows enconding "cp1252" from DBvis cannot be recognized by Gnumeric.

Oracle SQL Developer - Export

Squirrel SQL

C:\Users\myuser\.squirrel-sql\SQLAliases23.xml

SQL Developer (Oracle)
With many options and able to work with many connections and tables at same time.
Can export to csv, tsv, dsv, xls,xlsx (including query in a different tab)

v 4.1.3.20
Features:
CVS integrated with compare features (tab: Historial)
Result of previous queries  (results tabs)

Settings/configuration:
Herramientas,Preferencias,Editor de codigos, Canal de Linea, Mostrar números de líneas
Herramientas,Preferencias,Base de datos,NLS, formato fecha=DD/MM/RR HH24:MI:SSXFF
Herramientas,Preferencias,Base de datos,Avanzada, Tamaño de recuperación.. =50


Connections configuration:

Users\myself\AppData\Roaming\SQL Developer\system4.1.3xx\o.jdeveloper.db.connection.12.2.1.0xx\connections.xml (includes encrypted password, file with passwords will work on other system/version)


Connections, Export/import connections  (requires a password to encrypt passwords in xml file)

Install Password Plugin: Show me password
com.tomecode.showme.password.sqldeveloper.1.0.0


JDK configuration


by default ../../jdk
(if not detected there; a browse folder box will allow us to select the folder)



sqldeveloper4.x.\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf

SetJavaHome ../../jdk



Spreadsheets

Gnumeric:
is able to import csv files with a different than comma symbol,
is able to export a spreadsheet with an specific separator

There are problems importing a  csv file DBvis enconded with "cp1252"
The following combinatiosn works

DBvisualizer / Gnumeric
UTF8        /    ISO 8859-2
CP1252    /    Windows 1250

Import
Go to "Data", "Import Text File", "Text import configuration" or "Data", "Text to Columns"

Export
Save As, File type: Text (configurable)

Gnumeric Export

Ms. Excel 2003
Can import csv, and even specify delimiters but it is useless for exportation to csv as it always delimit columns using semicolon.
Theorically from Computer, regional settings, delimiter can be configured but it doesn´t work.

Ms. Excel (>2007)

Last versions removed the option Import in Menu, so now it is not very intuitive, but it can be done opening a text file with a .txt extension (don't use csv, because automatically comma will be used as delimiter)  and a wizards will be launched.
Theoretically from Computer, regional settings, delimiter can be configured but it doesn't work with Windows 7.  Probably excel is one of the best Microsoft apps but there is a complete lack of interoperability. Better to use Libreoffice

Excel import

Formating contents in Excel:

Cleaning acents:
Limited by maximun of nested functions:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"á","a"),"é","e"),"í","i"),"ó","o"),"ú","u"),"ã","a")

Convert to Proper name and clean acents
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A9),"á","a"),"é","e"),"í","i"),"ó","o"),"ú","u")


Look for a string in an array a extract a column from same row
=VLOOKUP(b2,!equivalencias$1:$20,2,FALSE)
=VLOOKUP(stringtofind,in_array!$first_row:$last_row,col_to extract,FALSE)
False=match exact string

Find a string in the last characters
=IF(RIGHT(E2,10)="@domain.es","Spanish",IF(RIGHT(E2,13)="@domain.co.uk","British","none"))

Detect white spaces at beginning or end of a string
i.e if space found write the string without spaces.
=IF(OR(LEFT(A2,1)=" ",RIGHT(A2,1)=" "),TRIM(A2),"Word  is OK")


Libreoffice/OpenOffice

Import, just open a csv file and wizard automatically will open.

Export, Save as: CSV with checkbox "Edit filter settings" let us to change delimiter character or add quotation marks to fields