IMSMA3 script changes 6.0

Importing data to PostgreSQL

If you have imported data to temporary tables, you need to double-check how your data looks like after import to PostgreSQL. There are changes in date format and boolean compared to MySQL.

Configuration files

Compared to IMSMA^NG 5.08.04 these rows are changed:

1. MIGRATION_FROM_TYPE=6.0
2. MIGRATION_TO_TYPE=6.0
3. IMSMA_6.0_JDBC_URL=jdbc:postgresql://localhost:5432/imsma
4. IMSMA_6.0_Driver=org.postgresql.Driver
5. IMSMA_6.0_User=imsma
6. IMSMA_6.0_Password=password

Scripts

SQL syntax

It is strongly recommended to copy all SQL statements to a SQL tool like PGadminIII or NaviCat and test them before running the script in IMSMANG 6.0

Due to PostgreSQL is case sensitive you need to adapt SQL statements by adding " around column names that contains capital case and because of the " you also need to add \ (escape character).

5.08.04 example

SELECT Loc_VillageGUID, mMREID FROM loc_link_mre WHERE loc_link_mre.Loc_VillageGUID IS NOT NULL

needs to be changed to

SELECT \"Loc_VillageGUID\", \"mMREID\" FROM loc_link_mre WHERE \"Loc_VillageGUID\" IS NOT NULL

In PostgreSQL there are other reserved words than in MySQL. In your SQL tool you will see them marked with blue text.

How it looks like in SQL tool before correction

How it looks like in SQL tool after correction

How it looks like in integrated into the script with escape characters

There are more SQL syntax changes between MySQL and PostgreSQL, refer to Difference MySQL PostGreSQL.

Data type Byte

In PostgreSQL the data type Byte has been replaced with Boolean. You find all places you need to change by searching for Byte. 5.08.04 example

Attribute.SetByte("IsActive", Cast.Byte("1"))

needs to be changed to

Attribute.SetBoolean("Active", Cast.Boolean("true"))

Geodata

The column userenteredcoord has been split up in 3 columns

• user_entered_x
• user_entered_y
• user_entered_mgrs

Search for UserEnteredCoord and replace the 5.08.04 syntax with (the row should end with a , or ;) :
Attribute.SetString("UserEnteredX", Row.GetString("Longitude"))
Attribute.SetString("UserEnteredY", Row.GetString("Latitude"))
Attribute.SetString("UserEnteredMGRS", Row.GetString("MGRS"))

You also need to verify that the following fields get correct values:

• CoordRefSys
• CoordRefFormat
• UserInputFormat

Mandatory information

Verify that the following fields are set and correct:

• Date of Information (previously called Date of Report) -> DateOfReport
• Data entry form ID -> LocalId in Pojo Fieldreport
• Approved Date -> ReportVerifiedDate
• Approver -> VerifiedBy
• Item ID e.g. Victim ID -> -> LocalId in Pojo Victim

Workarounds

1. ICR-114: The update query in Multi-select CDFs needs to be added as a Data.RunSQLStatement if any of the fields that are imported are multi-select.

Limitations

Unfortunately there are no change in the following limitations of logic in scripts:

• greater than (>)
• less than (<)
• LIKE (e.g. Eriks* or Eriks%)

do not work because it is not included in the Java scripts.

{{#switch:|subgroup|child=|none=|#default=

}}{{#if:|}}{{#if:Upgrade Process|<td style="text-align:left;border-left-width:2px;border-left-style:solid;|{{#if:|}}}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:imsma3 script changes 6.0

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group2}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list2}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:imsma3 script changes 6.0

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group3}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list3}}}

}}{{#if:|{{#if:|{{{group4}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list4}}}

}}{{#if:|{{#if:|{{{group5}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list5}}}

}}{{#if:|{{#if:|{{{group6}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list6}}}

}}{{#if:|{{#if:|{{{group7}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list7}}}

}}{{#if:|{{#if:|{{{group8}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list8}}}

}}{{#if:|{{#if:|{{{group9}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list9}}}

}}{{#if:|{{#if:|{{{group10}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list10}}}

}}{{#if:|{{#if:|{{{group11}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list11}}}

}}{{#if:|{{#if:|{{{group12}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list12}}}

}}{{#if:|{{#if:|{{{group13}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list13}}}

}}{{#if:|{{#if:|{{{group14}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list14}}}

}}{{#if:|{{#if:|{{{group15}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list15}}}

}}{{#if:|{{#if:|{{{group16}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list16}}}

}}{{#if:|{{#if:|{{{group17}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list17}}}

}}{{#if:|{{#if:|{{{group18}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list18}}}

}}{{#if:|{{#if:|{{{group19}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list19}}}

}}{{#if:|{{#if:|{{{group20}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list20}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:imsma3 script changes 6.0

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}

{{{below}}}

}}{{#switch:|subgroup|child=

|none=|#default=}}{{#ifeq:|Template|{{#ifeq:|child||{{#ifeq:|subgroup||{{#switch:imsma3 script changes 6.0

|doc
|sandbox
|testcases =
|#default = {{#switch:hlist
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}