Proposal: Profiles section - compatibility with bigints types

[luisleon1894]

Hi! I am developer in IOMED (link: Home). At our company company, one of the services we use is Atlas + WebPAPI. Recently , we encountered a limitation in the Profiles section related to the observartion_period.

Explanation

We are aware that in the official DDL the observation_period_id column is of integer type. However in our environment we have set this column as bigint.

Upon reviewing the traceback we notice that the function used currently is getInt. Is there any chance to use the getLong instead?

Steps to reproduce behavior & Actual behavior

1. Have a observation_period_id bigger than a Integer, for example:

observation_period_id	person_id	observation_period_start_date	observation_period_end_date	period_type_concept_id
389686681703353278	12319690674377349	2007-09-15	2023-06-16	44814724

2. Search in Profiles the person_id

3. See logs from web api

Caused by: org.postgresql.util.PSQLException: Bad value for type int : 389686681703353278
    at org.postgresql.jdbc.PgResultSet.toInt(PgResultSet.java:3218) ~[postgresql-42.3.7.jar!/:42.3.7]
    at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2408) ~[postgresql-42.3.7.jar!/:42.3.7]
    at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2836) ~[postgresql-42.3.7.jar!/:42.3.7]
    at org.ohdsi.webapi.service.PersonService$2.mapRow(PersonService.java:103) ~[classes/:?]
    at org.ohdsi.webapi.service.PersonService$2.mapRow(PersonService.java:95) ~[classes/:?]
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93) ~[spring-jdbc-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60) ~[spring-jdbc-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:701) ~[spring-jdbc-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:638) ~[spring-jdbc-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
    ... 115 more

Using getLong function & Expected behavior

To check that the change solves the problem we have created a new fork and applied the proposed solution

[luisleon1894]

I have created a PR for this issue --> https://github.com/OHDSI/WebAPI/pull/2371

[chrisknoll]

I understand that this is a simple change, but I'm worried about violating the standard. This example could be used to further make 'compatible' changes to the schema, encouraging people to deviate from the published specs when it's possibly unknown what the impact might be. For example, in JavaScript, it's not actually possible to represent a full 64bit integer (It's like this as well with native R): some portion of the bits are used for intger, exponent ans sign of the number, so you get to a point where you get a value from a bigint in the db but it is interpreted differently in the UI.

Can you do a quick select count(*) from observation_period and let me know the number of records in that table? Seems strange that you actually have at least 389686681703353278 rows.