Last hibernate sequence value

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

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.rowles@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

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 larshelge@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


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

On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles greg.rowles@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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

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.pickering@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


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

On Fri, Jan 16, 2015 at 9:49 AM, Lars Helge Øverland larshelge@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


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.pickering@gmail.com
tel:+46764147049

On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles greg.rowles@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

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 bobjolliffe@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.


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

On 16 January 2015 at 09:10, Jason Pickering jason.p.pickering@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


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

On Fri, Jan 16, 2015 at 9:49 AM, Lars Helge Øverland larshelge@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


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.pickering@gmail.com
tel:+46764147049

On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles greg.rowles@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

Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

I assume you are talking about entering Metadata, correct? Like data elements or orgunits?

What is the interface you are proposing to give them? Will you have a form with some kinds of restrictions? Are you thinking to link Postgres into Access via ODBC?

Will there be multiple simultaneous users of this, or is this for setting up a new database offline?

···

On Fri, Jan 16, 2015 at 9:20 AM, Greg Rowles greg.rowles@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


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

On Fri, Jan 16, 2015 at 11:17 AM, Bob Jolliffe bobjolliffe@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.


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

On 16 January 2015 at 09:10, Jason Pickering jason.p.pickering@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


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

On Fri, Jan 16, 2015 at 9:49 AM, Lars Helge Øverland larshelge@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


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.pickering@gmail.com
tel:+46764147049

On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles greg.rowles@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

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

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.rowles@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 bobjolliffe@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.


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

On 16 January 2015 at 09:10, Jason Pickering jason.p.pickering@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


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

On Fri, Jan 16, 2015 at 9:49 AM, Lars Helge Øverland larshelge@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


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.pickering@gmail.com
tel:+46764147049

On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles greg.rowles@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

Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

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.rowles@gmail.com wrote:

Let me not forget to mention that this type of work would be preparatory and not after roll out…


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

On Fri, Jan 16, 2015 at 11:20 AM, Greg Rowles greg.rowles@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


Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Fri, Jan 16, 2015 at 11:17 AM, Bob Jolliffe bobjolliffe@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.


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

On 16 January 2015 at 09:10, Jason Pickering jason.p.pickering@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


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

On Fri, Jan 16, 2015 at 9:49 AM, Lars Helge Øverland larshelge@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


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.pickering@gmail.com
tel:+46764147049

On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles greg.rowles@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

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

Thank you Everyone, that leaves our db managers with 3 options then!

···

On Fri, Jan 16, 2015 at 11:29 AM, Knut Staring knutst@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.rowles@gmail.com wrote:

Let me not forget to mention that this type of work would be preparatory and not after roll out…


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

On Fri, Jan 16, 2015 at 11:20 AM, Greg Rowles greg.rowles@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


Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Fri, Jan 16, 2015 at 11:17 AM, Bob Jolliffe bobjolliffe@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.


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

On 16 January 2015 at 09:10, Jason Pickering jason.p.pickering@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


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

On Fri, Jan 16, 2015 at 9:49 AM, Lars Helge Øverland larshelge@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


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.pickering@gmail.com
tel:+46764147049

On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles greg.rowles@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

Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

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.rowles@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 <knutst@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.rowles@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.rowles@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 <bobjolliffe@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.pickering@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 < >>>>>> larshelge@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.rowles@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.pickering@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.hedberg@gmail.com

Skype: calle_hedberg

*******************************************

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.hedberg@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.rowles@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 knutst@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.rowles@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.rowles@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 bobjolliffe@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.pickering@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 <

larshelge@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.rowles@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.pickering@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.hedberg@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

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 :slight_smile:

Bob

···

On 16 January 2015 at 11:56, Bob Jolliffe bobjolliffe@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.hedberg@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.rowles@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 knutst@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.rowles@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.rowles@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 bobjolliffe@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.pickering@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 <

larshelge@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.rowles@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.pickering@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.hedberg@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

To be frank, it doesn’t really matter what you use for I’d as long as it is always smaller than the nextval of the hibernate sequence. But in order to ensure that, it is best practice to use it.

···

On 16 January 2015 at 11:40, Calle Hedberg calle.hedberg@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.rowles@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 knutst@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.rowles@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.rowles@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 bobjolliffe@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.pickering@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 <

larshelge@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.rowles@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.pickering@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.hedberg@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

Hi,

FYI, Lars finally got back to me on those examples I sent him: (a)
confirming that using Excel for editing XM does not work; (b) pointing
out that the reason for my CSV file not working was incorrect field
sequence.

Point (b) highlights a fundamental (and frequent) problem with the
standard interface, then: the system does not check if the format is
correct - the import process just fails silently AND the standard
success message is displayed to the user.

The documentation is not clear - ref user manual chapter 20 for CSV
meta-data import:
"DHIS 2 supports import of meta-data in the CSV format. Columns which
are not required can be omitted in the CSV file, but the order will be
affected. If you would like to specify columns which appear late in
the order but not specify columns which appear early in the order you
can include empty columns ("") for them."

"Not required can be omitted", "you CAN include empty columns" - my
interpretation of this was that I could just drop columns I did not
need, but reality is that a far better and easy-to-understand rule
would be: ALL COLUMNS MUST BE INCLUDED, AND JUST USE BLANK VALUES IN
ANY COLUMN NOT ACTUALLY REQUIRED.

Even better/more logical would be for the core development team to
provide a complete set of empty templates - XML, JSON, and CSV - as a
download from the database (that way, the templates can be updated
whenever there's a data model change). We've had that type of
functionality in DHIS 1.x for as long as I can remember. Just add one
extra menu option under import/export saying "Export all legal
templates" and then allow the user to select XML, JSON, or CSV.

Regards from Windhoek
Calle

···

On 16/01/2015, Knut Staring <knutst@gmail.com> wrote:

To be frank, it doesn't really matter what you use for I'd as long as it is
always smaller than the nextval of the hibernate sequence. But in order to
ensure that, it is best practice to use it.
On Jan 16, 2015 11:57 AM, "Bob Jolliffe" <bobjolliffe@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.hedberg@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.rowles@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 <knutst@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.rowles@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.rowles@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 < >>> bobjolliffe@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.pickering@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 < >>> >>>>>> larshelge@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.rowles@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.pickering@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.hedberg@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

--

*******************************************

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.hedberg@gmail.com

Skype: calle_hedberg

*******************************************

I agree this is tricky and there should be examples and correction in the docs. Please go ahead and update the docs.

I mentioned Excel because many people have their data in that format, and it’s easy to either create csv in excel or create text strings with the required xml code, which can then be pasted into a text editor. We should probably aim to include examples of how to do this also.

···

On 17 Jan 2015 08:53, “Calle Hedberg” calle.hedberg@gmail.com wrote:

Hi,

FYI, Lars finally got back to me on those examples I sent him: (a)

confirming that using Excel for editing XM does not work; (b) pointing

out that the reason for my CSV file not working was incorrect field

sequence.

Point (b) highlights a fundamental (and frequent) problem with the

standard interface, then: the system does not check if the format is

correct - the import process just fails silently AND the standard

success message is displayed to the user.

The documentation is not clear - ref user manual chapter 20 for CSV

meta-data import:

"DHIS 2 supports import of meta-data in the CSV format. Columns which

are not required can be omitted in the CSV file, but the order will be

affected. If you would like to specify columns which appear late in

the order but not specify columns which appear early in the order you

can include empty columns ("") for them."

“Not required can be omitted”, “you CAN include empty columns” - my

interpretation of this was that I could just drop columns I did not

need, but reality is that a far better and easy-to-understand rule

would be: ALL COLUMNS MUST BE INCLUDED, AND JUST USE BLANK VALUES IN

ANY COLUMN NOT ACTUALLY REQUIRED.

Even better/more logical would be for the core development team to

provide a complete set of empty templates - XML, JSON, and CSV - as a

download from the database (that way, the templates can be updated

whenever there’s a data model change). We’ve had that type of

functionality in DHIS 1.x for as long as I can remember. Just add one

extra menu option under import/export saying "Export all legal

templates" and then allow the user to select XML, JSON, or CSV.

Regards from Windhoek

Calle

On 16/01/2015, Knut Staring knutst@gmail.com wrote:

To be frank, it doesn’t really matter what you use for I’d as long as it is

always smaller than the nextval of the hibernate sequence. But in order to

ensure that, it is best practice to use it.

On Jan 16, 2015 11:57 AM, “Bob Jolliffe” bobjolliffe@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.hedberg@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.rowles@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 knutst@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.rowles@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.rowles@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 < > > >>> bobjolliffe@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.pickering@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 < > > >>> >>>>>> larshelge@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.rowles@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.pickering@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.hedberg@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


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.hedberg@gmail.com

Skype: calle_hedberg


I just want to mention that we also have /api/schemas

This endpoint is a dynamic representation of what is allowed or not according to the schema (hibernate)

You can not send any payload to this endpoint right now, but in 2.18 you can send your payload to I.e. /api/schemas/dataElement and get a validation result

···

On Jan 17, 2015 5:53 PM, “Knut Staring” knutst@gmail.com wrote:

I agree this is tricky and there should be examples and correction in the docs. Please go ahead and update the docs.

I mentioned Excel because many people have their data in that format, and it’s easy to either create csv in excel or create text strings with the required xml code, which can then be pasted into a text editor. We should probably aim to include examples of how to do this also.

On 17 Jan 2015 08:53, “Calle Hedberg” calle.hedberg@gmail.com wrote:

Hi,

FYI, Lars finally got back to me on those examples I sent him: (a)

confirming that using Excel for editing XM does not work; (b) pointing

out that the reason for my CSV file not working was incorrect field

sequence.

Point (b) highlights a fundamental (and frequent) problem with the

standard interface, then: the system does not check if the format is

correct - the import process just fails silently AND the standard

success message is displayed to the user.

The documentation is not clear - ref user manual chapter 20 for CSV

meta-data import:

"DHIS 2 supports import of meta-data in the CSV format. Columns which

are not required can be omitted in the CSV file, but the order will be

affected. If you would like to specify columns which appear late in

the order but not specify columns which appear early in the order you

can include empty columns ("") for them."

“Not required can be omitted”, “you CAN include empty columns” - my

interpretation of this was that I could just drop columns I did not

need, but reality is that a far better and easy-to-understand rule

would be: ALL COLUMNS MUST BE INCLUDED, AND JUST USE BLANK VALUES IN

ANY COLUMN NOT ACTUALLY REQUIRED.

Even better/more logical would be for the core development team to

provide a complete set of empty templates - XML, JSON, and CSV - as a

download from the database (that way, the templates can be updated

whenever there’s a data model change). We’ve had that type of

functionality in DHIS 1.x for as long as I can remember. Just add one

extra menu option under import/export saying "Export all legal

templates" and then allow the user to select XML, JSON, or CSV.

Regards from Windhoek

Calle

On 16/01/2015, Knut Staring knutst@gmail.com wrote:

To be frank, it doesn’t really matter what you use for I’d as long as it is

always smaller than the nextval of the hibernate sequence. But in order to

ensure that, it is best practice to use it.

On Jan 16, 2015 11:57 AM, “Bob Jolliffe” bobjolliffe@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.hedberg@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.rowles@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 knutst@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.rowles@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.rowles@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 < > > > > >>> bobjolliffe@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.pickering@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 < > > > > >>> >>>>>> larshelge@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.rowles@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.pickering@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.hedberg@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


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.hedberg@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