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

There is no changes in version 6.0 for these fields but there were a change in version 5.08.02 and many existing scripts are not yet updated. I would also recommend to double-check new scripts written by Novetta.

Verify that the following fields are set and correct in Pojo Fieldreport:

• Date of Information (previously called Date of Report) -> DateOfReport
• Data entry form ID -> LocalId
• Approved Date -> ReportVerifiedDate
• Approver -> VerifiedBy

Verify that the following fields are set and correct in Pojo Xxxinfoversion

• Item ID -> LocalId
• Type of item -> Type
• Status of item -> Status

If you have dates that you would like to use as

• Data Entry Date
• Submitted Date

see Data Entry Form Table which fields to use.

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 = 
 }}
}}

}}}}}}