error code in my charset: ZHS16GBK

[homeway]

I can't got binary with right charset:

11> f(OciPort), OciPort = erloci:new([{logging, true},{env, [{"NLS_LANG","SIMPLIFIED CHINESE_CHINA.ZHS16GBK"}]}]).
12> f(OciSession), OciSession = OciPort:get_session(Tns, User, Pass).
13> f(SelectStmt), SelectStmt = OciSession:prep_sql(SelectSql).
14> SelectStmt:exec_stmt().
{cols,[{<<"SUPPLYNAME">>,'SQLT_CHR',50,0,0}]}
15> SelectStmt:fetch_rows(5).                                                                                     
{{rows,[[<<"¹ã¶«Ê¡¶«Ý¸¹úÒ©¼¯ÍÅÈʼÃÌÃÒ©ÒµÓÐÏÞ¹«Ë¾">>]]},true

In fact, I run the unit test for my oracle:

fun() ->
      application:start(erloci),
      OciPort = erloci:new([{logging, true}, {env, [{"NLS_LANG", "SIMPLIFIED CHINESE_CHINA.ZHS16GBK"}]}]),
...

and got follow error:

erloci_test: db_test_...*failed*
in function erloci_test:'-timestamp_interval_datatypes/1-fun-11-'/2 (test/erloci_test.erl, line 865)
in call from erloci_test:timestamp_interval_datatypes/1 (test/erloci_test.erl, line 858)
**error:{assertEqual_failed,[{module,erloci_test},
                     {line,865},
                     {expression,"SelectStmt : exec_stmt ( )"},
                     {expected,{cols,[{<<"NAME">>,'SQLT_CHR',30,0,0},
                                      {<<"DAT">>,'SQLT_DAT',7,0,0},
                                      {<<"TS">>,'SQLT_TIMESTAMP',11,0,6},
                                      {<<"TSTZ">>,'SQLT_TIMESTAMP_TZ',13,0,6},
                                      {<<"TSLTZ">>,'SQLT_TIMESTAMP_LTZ',11,0,...},
                                      {<<"IYM">>,'SQLT_INTERVAL_YM',5,...},
                                      {<<"IDS">>,'SQLT_INTERVAL_DS',...}]}},
                     {value,{cols,[{<<"NAME">>,'SQLT_CHR',60,0,0},
                                   {<<"DAT">>,'SQLT_DAT',7,0,0},
                                   {<<"TS">>,'SQLT_TIMESTAMP',11,0,6},
                                   {<<"TSTZ">>,'SQLT_TIMESTAMP_TZ',13,0,...},
                                   {<<"TSLTZ">>,'SQLT_TIMESTAMP_LTZ',11,...},
                                   {<<"IYM">>,'SQLT_INTERVAL_YM',...},
                                   {<<...>>,...}]}}]}

=INFO REPORT==== 19-Apr-2015::00:56:19 ===
    application: erloci
    exited: stopped
    type: temporary
=======================================================
  Failed: 2.  Skipped: 0.  Passed: 53.
Cover analysis: /Users/homeway/erlang/research/erloci/.eunit/index.html
ERROR: One or more eunit tests failed.
ERROR: eunit failed while processing /Users/homeway/erlang/research/erloci: rebar_abort

[c-bik]

UTF8 support is not very correct/complete yet. If you have time I can work with you on this and get it fixed.

{<<"NAME">>,'SQLT_CHR',30,0,0}
and
{<<"NAME">>,'SQLT_CHR',60,0,0}

are different size. May also be due to UTF8 issue. Can you please add a test case which I can try with?

[homeway]

@c-bik I am very grateful to you can do that !

I will add a test case soon : )

[c-bik]

@homeway No problem at all!

[homeway]

hi @c-bik , can you tell me how you create your oracle instance and user grant for scott ? I create a common utf8 instance, and don't through all test yet.

$ rebar eunit
==> erloci (eunit)
30:50.170934 [debug] {oci_logger,,66} ---- ERLOCI PORT PROCESS LOGGER ----
30:50.180668 [debug] {oci_port,,290} DYLD_LIBRARY_PATH = .../opt/oracle/instantclient_11_2
30:50.182460 [debug] {oci_port,,292} Extra Env :[{"NLS_LANG","GERMAN_SWITZERLAND.AL32UTF8"}]
30:50.182651 [debug] {oci_port,,302} Executable "/Users/homeway/erlang/research/erloci/priv/ocierl"
30:50.183020 [debug] {oci_port,,303} Options :[{packet,4},
          binary,exit_status,use_stdio,
          {args,["262144","true","59855"]},
          {env,[{"DYLD_LIBRARY_PATH",
                 "/opt/oracle/instantclient_11_2:/opt/oracle/instantclient_11_2"},
                {"NLS_LANG","GERMAN_SWITZERLAND.AL32UTF8"}]}]
30:50.183359 [debug] {oci_port,,314} started log enabled new port:
....
test/erloci_test.erl:204:<0.229.0>: 30:57.268086 +---------------------------------------------+
test/erloci_test.erl:205:<0.229.0>: 30:57.268219 |                   lob_test                  |
test/erloci_test.erl:206:<0.229.0>: 30:57.268291 +---------------------------------------------+
test/erloci_test.erl:223:<0.229.0>: 30:57.380745 created Directory alias /Users/homeway/erlang/research/erloci/.eunit
30:57.525790 [error] {c_src/erloci_lib/ocistmt.cpp,execute,277} failed OCIStmtExecute error ORA-00955: name is already used by an existing object
 (create table lobs(clobd clob, blobd blob, nclobd nclob, bfiled bfile))
test/erloci_test.erl:241:<0.229.0>: 30:58.699049 truncated table lobs
test/erloci_test.erl:257:<0.229.0>: 30:59.505424 inserted 5 rows into lobs
30:59.667891 [error] {c_src/erloci_lib/ocistmt.cpp,rows,870} failed OCILobGetLength for 0x7fdf9a8e97f8 row 1 column 3 reason ORA-22288: file or LOB operation GETLENGTH failed
ϵͳ?Ҳ???ָ?????ļ???
 (select * from lobs)
test/erloci_test.erl:648:<0.229.0>: 30:59.671079 +---------------------------------------------+
erloci_test: db_test_...test/erloci_test.erl:649:<0.229.0>: 30:59.671333 |               describe_test                 |
*failed*
in function erloci_test:lob_test/1 (test/erloci_test.erl, line 262)
**error:{badmatch,{error,{22288,
                  <<"ORA-22288: file or LOB operation GETLENGTH failed\nϵ"...>>}}}
test/erloci_test.erl:650:<0.229.0>: 30:59.671482 +---------------------------------------------+

...

test/erloci_test.erl:813:<0.229.0>: 31:03.115105 +---------------------------------------------+
test/erloci_test.erl:814:<0.229.0>: 31:03.115272 |       timestamp_interval_datatypes          |
test/erloci_test.erl:815:<0.229.0>: 31:03.115347 +---------------------------------------------+
31:03.646087 [debug] {c_src/erloci_drv/command.cpp,exec_stmt,525} Bounds 
31:04.063312 [debug] {c_src/erloci_drv/command.cpp,exec_stmt,525} Bounds 
31:04.380905 [error] {c_src/erloci_lib/ocistmt.cpp,execute,277} failed OCIStmtExecute error ORA-00942: table or view does not exist
 (drop table erloci_test_1)

=INFO REPORT==== 23-Apr-2015::21:31:04 ===
    application: erloci
    exited: stopped
    type: temporary
=======================================================
  Failed: 1.  Skipped: 0.  Passed: 54.
Cover analysis: /Users/homeway/erlang/research/erloci/.eunit/index.html
ERROR: One or more eunit tests failed.
ERROR: eunit failed while processing /Users/homeway/erlang/research/erloci: rebar_abort

[c-bik]

@homeway

lob_test only works for a localhost Oracle Server with matching folder permission.

[homeway]

ok, I try it continue

[c-bik]

@homeway accidental close?

[homeway]

@c-bik thanks for your help !

I tried some cases for utf8: 1) insert utf8 string and select it in test case 2) insert utf8 string from sqldeveloper and select it in test case

Then I found all the test passed ( I commented lob_test ).

So I guess my problem only happened with legacy database : )

[c-bik]

Ok. So that can not be solved by erloci, but perhaps the detection and handling of encoding can be improved or made more transparent (other than utf8 that is). It will be great if you can bring in some legacy support (like your scenario) into erloci. More erloci users will be benefited.

I am assuming this issue closed.