Truncated example:
INSERT INTO organisationunit (organisationunitid, name, .... other fields
...) values (nextval('hibernate_sequence'), 'Bob's Clinic', ...);
Aside:
I am not sure why this is not defined as a default value like:
create table example (
id int default(nextval('hibernate_sequence')),
etc
Probably there is a good reason. I guess you could always just modify your
table structure like the above and then you can happily forget all about
the existence of hibernate_sequence :-)
Bob
On 16 January 2015 at 11:56, Bob Jolliffe <bobjolli...@gmail.com> wrote:
> Agree Calle. I too have had to make use of the database directly to deal
> with some things which are just more difficult or not implemented in the
> api.
>
> So the point is just that if you are making sql insert queries then you
> should make use of the nextval function as described by Jason above to
> populate the id field.
>
> On 16 January 2015 at 11:40, Calle Hedberg <calle.hedb...@gmail.com>
> wrote:
>
>> Hi,
>>
>> You guys are all correct when saying that it's best to use the
>> standard tools and/or the web api - the problem is that DHIS2
>> processing is rather opaque and/or the interface simply do not allow
>> updating things (try accessing the OrgUnitLevel table if it's empty).
>> Example: the other day I exported the OrganisationUnit data using the
>> standard meta-data export, made some updates directly in the xml file,
>> and imported it again. The system indicates that import is OK and that
>> x records were updated - but when I look at the database table itself
>> there's no change. I tried again, this time importing updates via CSV
>> - same thing, import summary indicates all OK and x records updated,
>> but no actual changes in the database. (Sent the files to Lars for a
>> check, but no response so I guess he's just too busy). That
>> documentation tend to be brief, very techie and outdated .....
>>
>> I spend a lot of time working on pulling in data from a variety of
>> formats and sources. Using the web api etc means (a) lots of
>> complicated formatting and many steps; (b) painstakingly having to
>> cross-verify that the result of every single step has actually done
>> what it's supposed to, because the summary feedback tend to be limited
>> or - as shown above - unreliable.
>>
>> I cannot get "preparing things in Excel" to work either - with the
>> example above I tried to open the exported OU xml file in Excel. It
>> opens, but with somewhat strange formatting because the metaexport.xml
>> file do not have a scheme (at least that excel understand). So excel
>> creates a schema - but when trying to export as xml again after
>> modifications, it don't work... It's possible I'm doing something
>> wrong, of course - but export xml file, edit it in Excel, save xml
>> file, import it, that SHOULD be straightforward ...
>>
>> Anyway - the bottom line is that building up a database partially
>> using direct dbms access make the whole process a lot faster and less
>> painful.
>>
>> Regards from Windhoek
>> Calle
>>
>>
>>
>>
>> On 16/01/2015, Greg Rowles <greg.row...@gmail.com> wrote:
>> > Thank you Everyone, that leaves our db managers with 3 options then!
>> >
>> > On Fri, Jan 16, 2015 at 11:29 AM, Knut Staring <knu...@gmail.com>
>> wrote:
>> >
>> >> Yeah, that was my key question - and I thing it could work ok that way.
>> >> But I would think you could achieve most of what you want through
>> >> preparing
>> >> things in Excel and then using the Metadata import instead (so you
>> don't
>> >> have to worry about the internal database IDs)
>> >>
>> >> On Fri, Jan 16, 2015 at 9:25 AM, Greg Rowles <greg.row...@gmail.com>
>> >> wrote:
>> >>
>> >>> Let me not forget to mention that this type of work would be
>> preparatory
>> >>> and not after roll out...
>> >>>
>> >>> On Fri, Jan 16, 2015 at 11:20 AM, Greg Rowles <greg.row...@gmail.com>
>> >>> wrote:
>> >>>
>> >>>> Thanks Bob, Jason & Others
>> >>>>
>> >>>> I agree and understand but we have a DHIS2 implementors who prefer to
>> >>>> work directly inside the DBMS who are still new to this complex
>> >>>> environment. If they need to insert records directly into tables this
>> >>>> gives
>> >>>> them the opportunity to do the work they're used to (coming from
>> >>>> version
>> >>>> 1.4)...
>> >>>>
>> >>>> Regards,
>> >>>> Greg
>> >>>>
>> >>>>
>> >>>> On Fri, Jan 16, 2015 at 11:17 AM, Bob Jolliffe <
>> bobjolli...@gmail.com>
>> >>>> wrote:
>> >>>>
>> >>>>> Jason is right. You should only need the hibernate sequence number
>> if
>> >>>>> you are dealing with the database through sql. And if you are, then
>> >>>>> the
>> >>>>> only safe way to use it is to get the sequence during the execution
>> of
>> >>>>> the
>> >>>>> statement.
>> >>>>>
>> >>>>> Getting the next sequence number and storing it to some variable
>> with
>> >>>>> the aim of using it at some time in the future is inherently
>> fragile.
>> >>>>>
>> >>>>> On 16 January 2015 at 09:10, Jason Pickering <
>> >>>>> jason.p.picker...@gmail.com> wrote:
>> >>>>>
>> >>>>>> Hi Greg,
>> >>>>>> I would tend to agree with Lars that this should really not ever be
>> >>>>>> required, but it would be good to know what the use case actually
>> is.
>> >>>>>>
>> >>>>>> The easiest way to do this would be to create an SQL view in DHIS2
>> as
>> >>>>>>
>> >>>>>> SELECT currval('hibernate_sequence')
>> >>>>>>
>> >>>>>>
>> >>>>>> In a multi-user database environment, this value is of course
>> >>>>>> volatile, so you can never be sure what the actual current value
>> is.
>> >>>>>>
>> >>>>>> If you really need to inject SQL into your database, then using
>> >>>>>> something like 'nextval('hibernate_sequence'::regclass)'in your
>> SQL,
>> >>>>>> which will provide the next value of the sequence.
>> >>>>>>
>> >>>>>> Regards,
>> >>>>>> Jason
>> >>>>>>
>> >>>>>> On Fri, Jan 16, 2015 at 9:49 AM, Lars Helge Øverland <
>> >>>>>> larshe...@gmail.com> wrote:
>> >>>>>>
>> >>>>>>> Hi Greg,
>> >>>>>>>
>> >>>>>>> that would be easy to implement, but the idea is that it should
>> >>>>>>> never
>> >>>>>>> be necessary to expose database details and instead work with the
>> >>>>>>> API. Do
>> >>>>>>> you think you could elaborate a bit on the use-case?
>> >>>>>>>
>> >>>>>>> Lars
>> >>>>>>>
>> >>>>>>>
>> >>>>>>> On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles <
>> greg.row...@gmail.com>
>> >>>>>>> wrote:
>> >>>>>>>
>> >>>>>>>> Hi Devs
>> >>>>>>>>
>> >>>>>>>> Is there an easy way to expose the last hibernate sequence value
>> >>>>>>>> through the web API?
>> >>>>>>>>
>> >>>>>>>> Greg
>> >>>>>>>>
>> >>>>>>>> --
>> >>>>>>>> *Health Information Systems Program - South Africa*
>> >>>>>>>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - -
>> **-
>> >>>>>>>> - - - - *
>> >>>>>>>> Mobile : 073 246 2992
>> >>>>>>>> Landline: 021 554 3130
>> >>>>>>>> Fax: 086 733 8432
>> >>>>>>>> Skype: gregory_rowles
>> >>>>>>>>
>> >>>>>>>> _______________________________________________
>> >>>>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>> >>>>>>>> Post to : dhis2-devs@lists.launchpad.net
>> >>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> >>>>>>>> More help : https://help.launchpad.net/ListHelp
>> >>>>>>>>
>> >>>>>>>>
>> >>>>>>>
>> >>>>>>> _______________________________________________
>> >>>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>> >>>>>>> Post to : dhis2-devs@lists.launchpad.net
>> >>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> >>>>>>> More help : https://help.launchpad.net/ListHelp
>> >>>>>>>
>> >>>>>>>
>> >>>>>>
>> >>>>>>
>> >>>>>> --
>> >>>>>> Jason P. Pickering
>> >>>>>> email: jason.p.picker...@gmail.com
>> >>>>>> tel:+46764147049
>> >>>>>>
>> >>>>>> _______________________________________________
>> >>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>> >>>>>> Post to : dhis2-devs@lists.launchpad.net
>> >>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> >>>>>> More help : https://help.launchpad.net/ListHelp
>> >>>>>>
>> >>>>>>
>> >>>>>
>> >>>>> _______________________________________________
>> >>>>> Mailing list: https://launchpad.net/~dhis2-devs
>> >>>>> Post to : dhis2-devs@lists.launchpad.net
>> >>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> >>>>> More help : https://help.launchpad.net/ListHelp
>> >>>>>
>> >>>>>
>> >>>>
>> >>>>
>> >>>> --
>> >>>> *Health Information Systems Program - South Africa*
>> >>>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- -
>> -
>> >>>> - - *
>> >>>> Mobile : 073 246 2992
>> >>>> Landline: 021 554 3130
>> >>>> Fax: 086 733 8432
>> >>>> Skype: gregory_rowles
>> >>>>
>> >>>
>> >>>
>> >>>
>> >>> --
>> >>> *Health Information Systems Program - South Africa*
>> >>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- -
>> - -
>> >>> - *
>> >>> Mobile : 073 246 2992
>> >>> Landline: 021 554 3130
>> >>> Fax: 086 733 8432
>> >>> Skype: gregory_rowles
>> >>>
>> >>> _______________________________________________
>> >>> Mailing list: https://launchpad.net/~dhis2-devs
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>>
>> >>>
>> >>
>> >>
>> >> --
>> >> Knut Staring
>> >> Dept. of Informatics, University of Oslo
>> >> Liberia: +231 770 496 123 or +231 886 146 381
>> >> Norway: +4791880522
>> >> Skype: knutstar
>> >> http://dhis2.org
>> >>
>> >
>> >
>> >
>> > --
>> > *Health Information Systems Program - South Africa*
>> > *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- - -
>> - -
>> > *
>> > Mobile : 073 246 2992
>> > Landline: 021 554 3130
>> > Fax: 086 733 8432
>> > Skype: gregory_rowles
>> >
>>
>>
>> --
>>
>>
>> *******************************************
>>
>> Calle Hedberg
>>
>> 46D Alma Road, 7700 Rosebank, SOUTH AFRICA
>>
>> Tel/fax (home): +27-21-685-6472
>>
>> Cell: +27-82-853-5352
>>
>> Iridium SatPhone: +8816-315-19274
>>
>> Email: calle.hedb...@gmail.com
>>
>> Skype: calle_hedberg
>>
>> *******************************************
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to : dhis2-devs@lists.launchpad.net
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help : https://help.launchpad.net/ListHelp
>>
>
>
_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
