dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Figured out what the problem was actually. shortname instead of shortName. Oops.

I am using Postgresql to transform the data to XML. The postgresql
function "xmlelement" is case specific, like many functions in
postgres.

My query  to produce each stanza of the data element block in DXF
looks like the SQL at the end of this mail. Note the double quotes
around "shortName" and "alternativeName" are necessary.

FYI.


Regards,
Jason


  SELECT
  '<dataElement>
  <id>' || serialid || '</id>
  <uuid />
  '
  || xmlelement( name name, dataelementname )
  ||
  '
  '
  || xmlelement( name "alternativeName" , attribute)
  ||'
  '
  || xmlelement( name "shortName", substring(dataelementname from 0
for 22) || '_' ||  round(random() * 100))
  || '
  <code />
  <description />
  <active>true</active>
  <type>int</type>
  <aggregationOperator>sum</aggregationOperator>
  <categoryCombo>10</categoryCombo>
  <lastUpdated>2010-11-05</lastUpdated>
  </dataElement>
'

 FROM harvard.attributes_de

where dataelementname is not null




On 12/27/10, Jason Pickering <jason.p.pickering@xxxxxxxxx> wrote:
> Hi there,
> I am attempting to use the "preferred" route for data migration from a
> legacy system into DHIS2. This means transforming the legacy source
> into XML and importing via the normal mechanisms. I have attached an
> XML file which can be uploaded and processed, but after that, I get an
> error..
> What is not entirely clear is I do not see any element which has a
> NULL shortname Certainly some of them have shortnames, but these do
> not show up in the SQL statement
>
>  It seems that maybe the shortname is being ignored entirely.
>
> Any tips or perhaps better eyes?
>
> Best regards,
> Jason
>
>
>
> Caused by: org.h2.jdbc.JdbcSQLException: NULL not allowed for column
> "SHORTNAME"
> ; SQL statement:
> INSERT INTO dataelement
> (uuid,name,alternativename,shortname,code,description,ac
> tive,valuetype,domaintype,aggregationtype,extendeddataelementid,categorycomboid,
> sortorder,lastupdated) VALUES (null,'Antenatal 1st visits total
> (calc)',null,nul
> l,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Benzyl
> penici
> llin stock
> out',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-0
> 5'),(null,'InStock_Chloro',null,null,null,null,true,'int',null,'sum',null,1,null
> ,'2010-11-05'),(null,'Antenatal follow up
> visit',null,null,null,null,true,'int',
> null,'sum',null,1,null,'2010-11-05'),(null,'Cotrimoxazole 480mg stock
> out',null,
> null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'AntiBio_Pr
> escribed',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(n
> ull,'BCG dose <1
> year',null,null,null,null,true,'int',null,'sum',null,1,null,'20
> 10-11-05'),(null,'Bed
> days',null,null,null,null,true,'int',null,'sum',null,1,nul
> l,'2010-11-05'),(null,'Below the dotted line under
> 5',null,null,null,null,true,'
> int',null,'sum',null,1,null,'2010-11-05'),(null,'Caesarean
> section',null,null,nu
> ll,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'DPT-HepB+Hib
> vacc
> ine stock
> out',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05
> '),(null,'CHWs_Active',null,null,null,null,true,'int',null,'sum',null,1,null,'20
> 10-11-05'),(null,'CHWs_Patients',null,null,null,null,true,'int',null,'sum',null,
> 1,null,'2010-11-05'),(null,'Vector Rodent complaints
> received',null,null,null,nu
> ll,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Any Anti malaria
> for I
> PT stock
> out',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'
> ),(null,'InStock_Keta',null,null,null,null,true,'int',null,'sum',null,1,null,'20
> 10-11-05'),(null,'InStock_Lance',null,null,null,null,true,'int',null,'sum',null,
> 1,null,'2010-11-05'),(null,'Complicated_Deliveries',null,null,null,null,true,'in
> t',null,'sum',null,1,null,'2010-11-05'),(null,'Vector Rodent complaints
> attended
> to',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'
> Deaths_48hours',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-0
> 5'),(null,'Deaths_O5',null,null,null,null,true,'int',null,'sum',null,1,null,'201
> 0-11-05'),(null,'Deaths_U5',null,null,null,null,true,'int',null,'sum',null,1,nul
> l,'2010-11-05'),(null,'DPT-Hib+HepB 3rd
> dose',null,null,null,null,true,'int',nul
> l,'sum',null,1,null,'2010-11-05'),(null,'Drug_Kits',null,null,null,null,true,'in
> t',null,'sum',null,1,null,'2010-11-05'),(null,'Expiry_Drug',null,null,null,null,
> true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Expiry_HIV',null,null,nul
> l,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Expiry_RPR',null,n
> ull,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Expiry_Uri'
> ,null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Immu
> nised fully <1 year
> new',null,null,null,null,true,'int',null,'sum',null,1,null,'
> 2010-11-05'),(null,'Attendance Family Planning -
> New',null,null,null,null,true,'
> int',null,'sum',null,1,null,'2010-11-05'),(null,'Attendance Family Planning
> - Re
> visit',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null
> ,'HC_Staff',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),
> (null,'Hosp_OPDStaff',null,null,null,null,true,'int',null,'sum',null,1,null,'201
> 0-11-05'),(null,'Hosp_SuppStaff',null,null,null,null,true,'int',null,'sum',null,
> 1,null,'2010-11-05'),(null,'Hosp_WardStaff',null,null,null,null,true,'int',null,
> 'sum',null,1,null,'2010-11-05'),(null,'Food inspections
> performed',null,null,nul
> l,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Food
> inspections r
> esulting in seizure and disposal of
> food',null,null,null,null,true,'int',null,'s
> um',null,1,null,'2010-11-05'),(null,'Premises
> inspected',null,null,null,null,tru
> e,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Premises inspected in
> compli
> ance',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,
> 'Usable beds
> total',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-
> 11-05'),(null,'InStock_Measles',null,null,null,null,true,'int',null,'sum',null,1
> ,null,'2010-11-05'),(null,'InStock_OPV',null,null,null,null,true,'int',null,'sum
> ',null,1,null,'2010-11-05'),(null,'Amoxicillin capsules stock
> out',null,null,nul
> l,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'InStock_OralCon',n
> ull,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'InStoc
> k_BCG',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null
> ,'Paracetamol 500mg stock
> out',null,null,null,null,true,'int',null,'sum',null,1,
> null,'2010-11-05'),(null,'RPR test stock
> out',null,null,null,null,true,'int',nul
> l,'sum',null,1,null,'2010-11-05'),(null,'InStock_TT',null,null,null,null,true,'i
> nt',null,'sum',null,1,null,'2010-11-05'),(null,'OP_U5_First_Attendances_Refer',n
> ull,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Inpati
> ent Under 5
> Admissions',null,null,null,null,true,'int',null,'sum',null,1,null,'2
> 010-11-05'),(null,'Inpatient Over 5
> Admissions',null,null,null,null,true,'int',n
> ull,'sum',null,1,null,'2010-11-05'),(null,'Inpatient discharge 5 years and
> over'
> ,null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Inpa
> tient discharges under
> 5',null,null,null,null,true,'int',null,'sum',null,1,null,
> '2010-11-05'),(null,'Old_HMIS_Default_LiveBirths',null,null,null,null,true,'int'
> ,null,'sum',null,1,null,'2010-11-05'),(null,'Live birth in facility under
> 2500g'
> ,null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Majo
> r_Surgical_Procedures',null,null,null,null,true,'int',null,'sum',null,1,null,'20
> 10-11-05'),(null,'Maternal_Deaths',null,null,null,null,true,'int',null,'sum',nul
> l,1,null,'2010-11-05'),(null,'Measles first dose under 1
> year',null,null,null,nu
> ll,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Normal delivery in
> fac
> ility',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null
> ,'OP_O5_First_Attendances',null,null,null,null,true,'int',null,'sum',null,1,null
> ,'2010-11-05'),(null,'OP_O5_First_Attendances_ByPass',null,null,null,null,true,'
> int',null,'sum',null,1,null,'2010-11-05'),(null,'OP_O5_First_Attendances_Refer',
> null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'OP_O5
> _Reattendances',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-0
> 5'),(null,'OP_U5_First_Attendances',null,null,null,null,true,'int',null,'sum',nu
> ll,1,null,'2010-11-05'),(null,'OP_U5_First_Attendances_ByPass',null,null,null,nu
> ll,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'OP_U5_Reattendances',n
> ull,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'OPV
> 3r
> d
> dose',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(nul
> l,'Postnatal first
> attendances',null,null,null,null,true,'int',null,'sum',null,1
> ,null,'2010-11-05'),(null,'PostOp_Wound_Infections',null,null,null,null,true,'in
> t',null,'sum',null,1,null,'2010-11-05'),(null,'Tetanus Toxoid 2nd or booster
> dos
> e to pregnant
> woman',null,null,null,null,true,'int',null,'sum',null,1,null,'2010
> -11-05'),(null,'Water samples
> taken',null,null,null,null,true,'int',null,'sum',n
> ull,1,null,'2010-11-05'),(null,'Still
> births',null,null,null,null,true,'int',nul
> l,'sum',null,1,null,'2010-11-05'),(null,'Supervisory_Visits',null,null,null,null
> ,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Salt samples tested
> with
>  adequate
> iodine',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11
> -05'),(null,'TBAs_Active',null,null,null,null,true,'int',null,'sum',null,1,null,
> '2010-11-05'),(null,'Delivery by a trained traditional birth
> attendant',null,nul
> l,null,null,true,'int',null,'sum',null,1,null,'2010-11-05'),(null,'Child <5
> year
> s weighed
> total',null,null,null,null,true,'int',null,'sum',null,1,null,'2010-11-
> 05'),(null,'OPD First Attendances Over
> 5',null,null,null,null,true,'int',null,'s
> um',null,1,null,'2010-11-05'),(null,'OPD First Attendances Under
> 5',null,null,nu
> ll,null,true,'int',null,'sum',null,1,null,'2010-11-05') [90006-143]
>         at
> org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
>         at org.h2.message.DbException.get(DbException.java:167)
>         at org.h2.message.DbException.get(DbException.java:144)
>         at
> org.h2.table.Column.validateConvertUpdateSequence(Column.java:294)
>         at org.h2.table.Table.validateConvertUpdateSequence(Table.java:621)
>         at org.h2.command.dml.Insert.insertRows(Insert.java:117)
>         at org.h2.command.dml.Insert.update(Insert.java:83)
>         at org.h2.command.CommandContainer.update(CommandContainer.java:70)
>         at org.h2.command.Command.executeUpdate(Command.java:199)
>         at
> org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:12
> 5)
>         at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:110)
>         at
> org.amplecode.quick.batchhandler.AbstractBatchHandler.flush(AbstractB
> atchHandler.java:276)
>         ... 7 more
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+260968395190
>


-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+260968395190