dhis2-devs team mailing list archive

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

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@xxxxxxx> 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@xxxxxxx> 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@xxxxxxxxx>
> 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@xxxxxxx> 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@xxxxxxxxx> 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
>>
>>
>> On Fri, Jul 26, 2013 at 11:57 AM, easy <lin_xd@xxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
>
>
>