generate resource table error

Support - Assistance technique
1
  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt
    p-apr-8080-exec-2])
    org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
    TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
    R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11
    ), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
    error: 语法错误 在 “(” 或附近的
    位置：118
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S
    QLErrorCodeSQLExceptionTranslator.java:237)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
    (AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet
    Structure(JdbcResourceTableStore.java:163)
    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS
    etTable(DefaultResourceTableService.java:276)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.
    java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
    ectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe
    thodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
    nvocation(TransactionInterceptor.java:96)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin
    Transaction(TransactionAspectSupport.java:260)
2

Hi easy,

it seems the error message is in chinese (after org.postgresql.util.PSQLException: error ), hard for me to understand in order to help, any chance you can translate or provide in English?

regards,

Lars

···

On Fri, Jul 26, 2013 at 11:57 AM, easy lin_xd@126.com wrote:

  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt

p-apr-8080-exec-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
error: 语法错误 在 “(” 或附近的
位置：118
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S

QLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet

Structure(JdbcResourceTableStore.java:163)

    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS

etTable(DefaultResourceTableService.java:276)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.

java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
ectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe

thodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
nvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin

Transaction(TransactionAspectSupport.java:260)

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

3

that’s right, for chinese DE groupset name,create table sql will get null.
CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), [this chinese name ] VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11
), PRIMARY KEY ( dataelementid ) )

···

On Fri, Jul 26, 2013 at 11:57 AM, easy lin_xd@126.com wrote:

  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt

p-apr-8080-exec-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
error: 语法错误 在 “(” 或附近的
位置：118
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S

QLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet

Structure(JdbcResourceTableStore.java:163)

    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS

etTable(DefaultResourceTableService.java:276)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.

java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
ectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe

thodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
nvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin

Transaction(TransactionAspectSupport.java:260)

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

4

Using Google Translate, the syntax error is related to the attempt to create a table with a blank column name, namely VARCHAR (250) (notice the blank)

I think the the current methods to generate column names attempt to remove all non-safe characters, and looks like they are all being stripped out resulting in a blank column name.

Regards,

Jason

···

On Sat, Jul 27, 2013 at 6:19 AM, easy lin_xd@126.com wrote:

that’s right, for chinese DE groupset name,create table sql will get null.
CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA

R (250), [this chinese name ] VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-26 20:36:59,“Lars Helge Øverland” larshelge@gmail.com wrote:

Hi easy,

it seems the error message is in chinese (after org.postgresql.util.PSQLException: error ), hard for me to understand in order to help, any chance you can translate or provide in English?

regards,

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, Jul 26, 2013 at 11:57 AM, easy lin_xd@126.com wrote:

  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt

p-apr-8080-exec-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
error: 语法错误 在 “(” 或附近的
位置：118
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S

QLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet

Structure(JdbcResourceTableStore.java:163)

    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS

etTable(DefaultResourceTableService.java:276)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.

java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
ectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe

thodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
nvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin

Transaction(TransactionAspectSupport.java:260)

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

5

It’s true, but we want create DE groupeset name in local words. It’s easy for user. so should adjust the column name generating method,ok?..

···

On Sat, Jul 27, 2013 at 6:19 AM, easy lin_xd@126.com wrote:

that’s right, for chinese DE groupset name,create table sql will get null.
CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA

R (250), [this chinese name ] VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-26 20:36:59,“Lars Helge Øverland” larshelge@gmail.com wrote:

Hi easy,

it seems the error message is in chinese (after org.postgresql.util.PSQLException: error ), hard for me to understand in order to help, any chance you can translate or provide in English?

regards,

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, Jul 26, 2013 at 11:57 AM, easy lin_xd@126.com wrote:

  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt

p-apr-8080-exec-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
error: 语法错误 在 “(” 或附近的
位置：118
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S

QLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet

Structure(JdbcResourceTableStore.java:163)

    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS

etTable(DefaultResourceTableService.java:276)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.

java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
ectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe

thodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
nvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin

Transaction(TransactionAspectSupport.java:260)

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

6

package org.hisp.dhis.system.util;
CodecUtils.databaseEncode:

Pattern pattern = Pattern.compile( “[a-zA-Z0-9_]” );

==>Pattern pattern = Pattern.compile( “[a-zA-Z0-9_\u4e00-\u9fa5]” );

···

On Sat, Jul 27, 2013 at 6:19 AM, easy lin_xd@126.com wrote:

that’s right, for chinese DE groupset name,create table sql will get null.
CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA

R (250), [this chinese name ] VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-26 20:36:59,“Lars Helge Øverland” larshelge@gmail.com wrote:

Hi easy,

it seems the error message is in chinese (after org.postgresql.util.PSQLException: error ), hard for me to understand in order to help, any chance you can translate or provide in English?

regards,

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, Jul 26, 2013 at 11:57 AM, easy lin_xd@126.com wrote:

  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt

p-apr-8080-exec-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
error: 语法错误 在 “(” 或附近的
位置：118
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S

QLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet

Structure(JdbcResourceTableStore.java:163)

    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS

etTable(DefaultResourceTableService.java:276)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.

java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
ectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe

thodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
nvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin

Transaction(TransactionAspectSupport.java:260)

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

7

Hi Easy,
I see what you are getting at and it might work, but I am not certain that the column names are being passed as escaped UNICODE. It seems as if they are not, because you should get something as a column name, as opposed to it being blank.

As a test, look at this code (or run in pgAdmin)

CREATE TABLE test (免疫 text) ;

INSERT INTO test (“免疫”) VALUES (‘foo’);

SELECT * FROM test;

SELECT “免疫” FROM test;

SELECT 免疫 FROM test;

#Now the Escaped unicode sequences for these characters

CREATE TABLE test2(“\u514d\u75ab” text);

INSERT INTO test2 (“\u514d\u75ab”) VALUES (‘bar’);

SELECT * FROM test2;

SELECT “\u514d\u75ab” FROM test2;

SELECT \u514d\u75ab FROM test2; --This fails

CREATE table test3( SELECT text);

Everything works except the last row, which does not have double quotes.

To the devs, it would seem that the use of the regex in this case may not be a good idea because the non-Latin characters are being stripped out, even though they would be allowed as a viable column name. Enclosing all of the column names in double quotes would help obviously, and would ensure that any string could be used as a column name (except reserved SQL words), for instance,

CREATE table test3( SELECT text); --Does not work, as it is reserved keyword in SQL

CREATE TABLE test3( “SELECT” text); --Works when enclosed in double quotes.

Regards,

Jason

···

On Sat, Jul 27, 2013 at 8:40 AM, easy lin_xd@126.com wrote:

package org.hisp.dhis.system.util;
CodecUtils.databaseEncode:

Pattern pattern = Pattern.compile( “[a-zA-Z0-9_]” );

==>Pattern pattern = Pattern.compile( “[a-zA-Z0-9_\u4e00-\u9fa5]” );

–
此致

莫愁前路无知己，天下谁人不识君。

At 2013-07-27 14:24:27,easy lin_xd@126.com wrote:

It’s true, but we want create DE groupeset name in local words. It’s easy for user. so should adjust the column name generating method,ok?..

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-27 14:18:17,“Jason Pickering” jason.p.pickering@gmail.com wrote:

Using Google Translate, the syntax error is related to the attempt to create a table with a blank column name, namely VARCHAR (250) (notice the blank)

I think the the current methods to generate column names attempt to remove all non-safe characters, and looks like they are all being stripped out resulting in a blank column name.

Regards,

Jason

On Sat, Jul 27, 2013 at 6:19 AM, easy lin_xd@126.com wrote:

that’s right, for chinese DE groupset name,create table sql will get null.
CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA

R (250), [this chinese name ] VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-26 20:36:59,“Lars Helge Øverland” larshelge@gmail.com wrote:

Hi easy,

it seems the error message is in chinese (after org.postgresql.util.PSQLException: error ), hard for me to understand in order to help, any chance you can translate or provide in English?

regards,

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, Jul 26, 2013 at 11:57 AM, easy lin_xd@126.com wrote:

  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt

p-apr-8080-exec-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
error: 语法错误 在 “(” 或附近的
位置：118
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S

QLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet

Structure(JdbcResourceTableStore.java:163)

    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS

etTable(DefaultResourceTableService.java:276)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.

java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
ectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe

thodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
nvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin

Transaction(TransactionAspectSupport.java:260)

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

8

Hi,

yes I agree. In fact we could have simply removed the name columns altogether. After we introduced the UIDs they are not necessary - at the moment we have two columns for each group set in those resouce tables, one with names and one with uids. One could arrive at the names with SQL joins anyway.

I am reluctant to remove the name columns from 2.12 stable since it might break potential SQL-based reports. But we can remove them for 2.13. Are there any objections against this?

Lars

···

On Sat, Jul 27, 2013 at 11:03 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Easy,
I see what you are getting at and it might work, but I am not certain that the column names are being passed as escaped UNICODE. It seems as if they are not, because you should get something as a column name, as opposed to it being blank.

As a test, look at this code (or run in pgAdmin)

CREATE TABLE test (免疫 text) ;

INSERT INTO test (“免疫”) VALUES (‘foo’);

SELECT * FROM test;

SELECT “免疫” FROM test;

SELECT 免疫 FROM test;

#Now the Escaped unicode sequences for these characters

CREATE TABLE test2(“\u514d\u75ab” text);

INSERT INTO test2 (“\u514d\u75ab”) VALUES (‘bar’);

SELECT * FROM test2;

SELECT “\u514d\u75ab” FROM test2;

SELECT \u514d\u75ab FROM test2; --This fails

CREATE table test3( SELECT text);

Everything works except the last row, which does not have double quotes.

To the devs, it would seem that the use of the regex in this case may not be a good idea because the non-Latin characters are being stripped out, even though they would be allowed as a viable column name. Enclosing all of the column names in double quotes would help obviously, and would ensure that any string could be used as a column name (except reserved SQL words), for instance,

CREATE table test3( SELECT text); --Does not work, as it is reserved keyword in SQL

CREATE TABLE test3( “SELECT” text); --Works when enclosed in double quotes.

Regards,

Jason

On Sat, Jul 27, 2013 at 8:40 AM, easy lin_xd@126.com wrote:

package org.hisp.dhis.system.util;
CodecUtils.databaseEncode:

Pattern pattern = Pattern.compile( “[a-zA-Z0-9_]” );

==>Pattern pattern = Pattern.compile( “[a-zA-Z0-9_\u4e00-\u9fa5]” );

–
此致

莫愁前路无知己，天下谁人不识君。

At 2013-07-27 14:24:27,easy lin_xd@126.com wrote:

It’s true, but we want create DE groupeset name in local words. It’s easy for user. so should adjust the column name generating method,ok?..

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-27 14:18:17,“Jason Pickering” jason.p.pickering@gmail.com wrote:

Using Google Translate, the syntax error is related to the attempt to create a table with a blank column name, namely VARCHAR (250) (notice the blank)

I think the the current methods to generate column names attempt to remove all non-safe characters, and looks like they are all being stripped out resulting in a blank column name.

Regards,

Jason

On Sat, Jul 27, 2013 at 6:19 AM, easy lin_xd@126.com wrote:

that’s right, for chinese DE groupset name,create table sql will get null.
CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA

R (250), [this chinese name ] VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-26 20:36:59,“Lars Helge Øverland” larshelge@gmail.com wrote:

Hi easy,

it seems the error message is in chinese (after org.postgresql.util.PSQLException: error ), hard for me to understand in order to help, any chance you can translate or provide in English?

regards,

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, Jul 26, 2013 at 11:57 AM, easy lin_xd@126.com wrote:

  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt

p-apr-8080-exec-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
error: 语法错误 在 “(” 或附近的
位置：118
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S

QLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet

Structure(JdbcResourceTableStore.java:163)

    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS

etTable(DefaultResourceTableService.java:276)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.

java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
ectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe

thodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
nvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin

Transaction(TransactionAspectSupport.java:260)

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

9

Hi again,

since there were no complaints I have now removed the name columns from the *groupset resource tables. You can use the uid columns instead. Should now be no problems with character encoding / Chinese.

Lars

···

On Tue, Aug 6, 2013 at 5:42 PM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi,

yes I agree. In fact we could have simply removed the name columns altogether. After we introduced the UIDs they are not necessary - at the moment we have two columns for each group set in those resouce tables, one with names and one with uids. One could arrive at the names with SQL joins anyway.

I am reluctant to remove the name columns from 2.12 stable since it might break potential SQL-based reports. But we can remove them for 2.13. Are there any objections against this?

Lars

On Sat, Jul 27, 2013 at 11:03 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Easy,
I see what you are getting at and it might work, but I am not certain that the column names are being passed as escaped UNICODE. It seems as if they are not, because you should get something as a column name, as opposed to it being blank.

As a test, look at this code (or run in pgAdmin)

CREATE TABLE test (免疫 text) ;

INSERT INTO test (“免疫”) VALUES (‘foo’);

SELECT * FROM test;

SELECT “免疫” FROM test;

SELECT 免疫 FROM test;

#Now the Escaped unicode sequences for these characters

CREATE TABLE test2(“\u514d\u75ab” text);

INSERT INTO test2 (“\u514d\u75ab”) VALUES (‘bar’);

SELECT * FROM test2;

SELECT “\u514d\u75ab” FROM test2;

SELECT \u514d\u75ab FROM test2; --This fails

CREATE table test3( SELECT text);

Everything works except the last row, which does not have double quotes.

To the devs, it would seem that the use of the regex in this case may not be a good idea because the non-Latin characters are being stripped out, even though they would be allowed as a viable column name. Enclosing all of the column names in double quotes would help obviously, and would ensure that any string could be used as a column name (except reserved SQL words), for instance,

CREATE table test3( SELECT text); --Does not work, as it is reserved keyword in SQL

CREATE TABLE test3( “SELECT” text); --Works when enclosed in double quotes.

Regards,

Jason

On Sat, Jul 27, 2013 at 8:40 AM, easy lin_xd@126.com wrote:

package org.hisp.dhis.system.util;
CodecUtils.databaseEncode:

Pattern pattern = Pattern.compile( “[a-zA-Z0-9_]” );

==>Pattern pattern = Pattern.compile( “[a-zA-Z0-9_\u4e00-\u9fa5]” );

–
此致

莫愁前路无知己，天下谁人不识君。

At 2013-07-27 14:24:27,easy lin_xd@126.com wrote:

It’s true, but we want create DE groupeset name in local words. It’s easy for user. so should adjust the column name generating method,ok?..

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-27 14:18:17,“Jason Pickering” jason.p.pickering@gmail.com wrote:

Using Google Translate, the syntax error is related to the attempt to create a table with a blank column name, namely VARCHAR (250) (notice the blank)

I think the the current methods to generate column names attempt to remove all non-safe characters, and looks like they are all being stripped out resulting in a blank column name.

Regards,

Jason

On Sat, Jul 27, 2013 at 6:19 AM, easy lin_xd@126.com wrote:

that’s right, for chinese DE groupset name,create table sql will get null.
CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA

R (250), [this chinese name ] VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )

–
此致

easy
莫愁前路无知己，天下谁人不识君。

At 2013-07-26 20:36:59,“Lars Helge Øverland” larshelge@gmail.com wrote:

Hi easy,

it seems the error message is in chinese (after org.postgresql.util.PSQLException: error ), hard for me to understand in order to help, any chance you can translate or provide in English?

regards,

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, Jul 26, 2013 at 11:57 AM, easy lin_xd@126.com wrote:

  • ERROR 2013-07-26 17:46:46,431 Error while executing action (ExceptionInterceptor.java [htt

p-apr-8080-exec-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE
TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHA
R (250), VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11), VARCHAR (250), PVBSMpEIesq CHARACTER(11

), PRIMARY KEY ( dataelementid ) )]; nested exception is org.postgresql.util.PSQLException:
error: 语法错误 在 “(” 或附近的
位置：118
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S

QLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet

Structure(JdbcResourceTableStore.java:163)

    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS

etTable(DefaultResourceTableService.java:276)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.

java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
ectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe

thodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
nvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin

Transaction(TransactionAspectSupport.java:260)

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