From ICISWiki

Database Backends > Postgresql as Central GMS & Central DMS

Contents 1 Setup 1.1 Installation Diagnostic Tool 1.2 GMS Search 1.2.1 Search for name "APO" 1.2.2 Expand pedigree tree (Expand All) for "APO" 1.3 Browse 1.3.1 Enter search string "IR8" 1.3.2 Command string: Display GMS data for GID 675472 1.3.3 Command string: List close relatives of GID 675472 1.3.4 Command string: Display all group relatives of GID 675472 1.4 ICISWorkbook 1.4.1 STUDY Menu 1.4.1.1 Study --> Convert Excel file 1.4.1.2 Study --> Template from database 1.4.1.3 Study --> Retrieve: Factors & Variates 1.4.1.4 Study --> Retrieve: Dataset 1.4.2 ADD-INS Menu 1.4.2.1 Add-Ins --> Import data from Setgen 1.4.3 GMS Menu 1.4.3.1 GMS --> Get Unique Sample ID (15 entries in Observation Sheet) 1.4.3.2 GMS --> Get Species (15 entries in Observation Sheet) 1.4.3.3 GMS --> Get Origin (15 entries in Observation Sheet)

Setup

The following items are results of ICIS application tests using an IRIS central (GMS and DMS) database in PostgreSQL. Local databases are in MS Access.

ICIS Schema version: 5.4

Installation Diagnostic Tool

These are the ODBC settings, as output by the ICIS Installation Diagnostic Tool:

GMS Search

Module version: 5.4

Search for name "APO"

Result: OK.

Expand pedigree tree (Expand All) for "APO"

Result: OK.

Browse

Module version: 5.4 (Schema version: 5.3)

Enter search string "IR8"

Result: OK.

 ENTER SEARCH OR COMMAND STRING (.HLP=HELP, ./END)
IR8

Command string: Display GMS data for GID 675472

Result: OK.

 ENTER SEARCH OR COMMAND STRING (.HLP=HELP, ./END)
.GID 675472

Command string: List close relatives of GID 675472

Result: OK.

 ENTER SEARCH OR COMMAND STRING (.HLP=HELP, ./END)
.REL 675472

Command string: Display all group relatives of GID 675472

Result: OK.

 ENTER SEARCH OR COMMAND STRING (.HLP=HELP, ./END)
.GRP 675472

ICISWorkbook

Module version: 5.4

STUDY Menu

Study --> Convert Excel file

Result: OK.

Study --> Template from database

Result: OK.

Settings: List studies from local AND central databases; select study "1990WS-CHA"

Study --> Retrieve: Factors & Variates

Result: OK.

Study --> Retrieve: Dataset

Result: OK, but an error message initially appears

Possible cause(s):

• syntax error in SQL defined in dll function DMS_OunitData

Analysis:

• Syntax is correct; however casting (CAST(D.DVALUE AS CHAR)) does not seem to take effect once function DMS_getOunitData is called.

SELECT V.REPRESNO, D.OUNITID, V.VARIATID, CAST(D.DVALUE AS CHAR)  
FROM VEFFECT V, DATA_N D
WHERE (((V.VARIATID)=D.VARIATID) 
AND ((V.REPRESNO)=2) AND ((D.OUNITID)=1)) 
UNION 
SELECT V.REPRESNO, D.OUNITID, V.VARIATID, D.DVALUE 
FROM VEFFECT V, DATA_C D 
WHERE (((V.VARIATID)=D.VARIATID) 
AND ((V.REPRESNO)=2) AND ((D.OUNITID)=1))

However, after clicking the "OK" button, the dataset retrieval proceeds successfully.

ADD-INS Menu

Add-Ins --> Import data from Setgen

Result: OK.

SetGen list name: 1980-001

Number of list entries: 15

GMS Menu

Result: OK.

GMS --> Get Unique Sample ID (15 entries in Observation Sheet)

Result: OK.

GMS --> Get Species (15 entries in Observation Sheet)

Result: OK.

GMS --> Get Origin (15 entries in Observation Sheet)

Result: OK.