dhis2-devs team mailing list archive

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

Hi Knut,

I am really not sure how or why this would ever happen. Are you using
DXF to import hierarchies? If so, then this would seem to be a bug.

If you are manually injecting SQL into the database, you must be very
careful NOT to use externally generated IDs. It is a recipe for
disaster.

Follow this pattern instead..

INSERT INTO organisationunit(organisationunitid, name, shortname,
parentid, active,openingdate)
VALUES(nextval('hibernate_sequence'::regclass),'ad Federal Muslim
Women Association in Nigeria Adamawa (FOMWAN
A)','adFederalMuslimWomenAssociationinNigeriaAdamawaF',501,'true','2001-01-01');

Notice instead of setting the organisationunitid, i use the nextval of
the hibernate_sequence. Of course the injection becomes a bit more
complex, as you have to be sure that you get all of the parentIDs
correct, but if you do it organisation layer by organisation layer,
then it is relatively easy.

Why would you want to set the value to 1010? I guess this is an
example, but the issue is how do you actually know what the value
should be? The hibernate_sequence is used through out the application,
so wouldn't you need to determine the maximum value of all possible
identifiers in all database objects and then to set the sequence value
to one greater than the maximum?

I am just not sure we should reccommend to anyone to start fiddling
with the sequence, as the consequences could be really messy to try
and clean up unless you are very careful.

Regards,
Jason



On Wed, Dec 7, 2011 at 1:00 PM, Knut Staring <knutst@xxxxxxxxx> wrote:
> When importing metadata (e.g. orgunit hierarchies), one sometimes ends up
> using externally generated IDs, that will then conflict with the Postgres
> hibernate_sequence. If this is not resolved, new orgunits added through
> DHIS2 will give errors when DHIS2 gets nextval from the sequence and tries
> an insert. The current value of the sequence can be set like this:
>
>  SELECT setval('hibernate_sequence', 1010);
> http://www.java2s.com/Code/PostgreSQL/Sequence/Usingsetvalfunctiontosetsequencevalue.htm
>
> It should also be possible to call the sequence directly over ODBC:
> http://www.techonthenet.com/access/queries/passthrough2.php
>
> Knut
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>