how to insert special characters

[randomailer]

I insert this string: 'С 10−00 до 18−00'
but when I select, returns 'С 10?00 до 18?00'

may be need some config encoding?

[cjbj]

Got a testcase? versions? Is the DB character set OK? What's your OS locale configuration?

[randomailer]

I think that DB charset ok,
I'am using utf8, mac os

In env vars set NLS_LANG=AMERICAN_AMERICA.UTF8

test case:

SELECT 'С 10−00 до 18−00' AS STR FROM DUAL

[cjbj]

@randomailer It works for me on OS X.

My DB is AL32UTF8:

CJ@localhost/orcl> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------------------------------------------
AL32UTF8

I don't have NLS_LANG set.

My code is:

var async = require('async');
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');

var query = function (conn, cb) {
  conn.execute(
    "SELECT 'С 10−00 до 18−00' AS STR FROM DUAL",
    function (err, result) {
      if (err) {
        return cb(err, conn);
      } else {
        var buf = result.rows[0];
        console.log("Row returned:");
        console.log(buf);
        return cb(null, conn);
      }
    });
};

async.waterfall(
  [
    function(cb) {
      oracledb.getConnection(dbConfig, cb);
    },
    query
  ],
  function (err, conn) {
    if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }
    conn.release(function (err) { if (err) console.error(err.message); });
  });

Output is

cjones@cjones-mac:~/n$ node issue223.js
Row returned:
[ 'С 10−00 до 18−00' ]

[randomailer]

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------------------------------------------
CL8MSWIN1251

may be this affect?

[cjbj]

@randomailer I don't have direct experience with that charset; it's certainly documented as 'MS Windows Code Page 1251 8-bit Latin/Cyrillic'. Are you running the script in a terminal window? Can that terminal handle the character set? Have you tried the original query in SQL*Plus, in the same terminal that you are running Node?

[randomailer]

sorry, can you save this symbols INSERT INTO abc VALUES ('漢字仮名交じり文');

[cjbj]

@randomailer like this?

var async = require('async');
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');

var drop = function (conn, cb) {
  var sql = "BEGIN "
          + "EXECUTE IMMEDIATE 'DROP TABLE mytab'; "
          + "EXCEPTION WHEN OTHERS THEN " 
          + "IF SQLCODE <> -942 THEN " 
          + "RAISE; " 
          + "END IF; "
          + "END;";
  conn.execute(
    sql,
    function(err, result) {
      if (err) {
        return cb(err, conn);
      } else {
        return cb(null, conn);
      }
    });
};

var create = function (conn, cb) {
  conn.execute(
    "CREATE TABLE mytab (c1 VARCHAR2(100))",
    function(err, result) {
      if (err) {
        return cb(err, conn);
      } else {
        return cb(null, conn);
      }
    });
};

var insert = function (conn, cb) {
  var sql = "INSERT INTO mytab VALUES ('漢字仮名交じり文')";
  conn.execute(
    sql,
    function(err, result) {
      if (err) {
        return cb(err, conn);
      } else {
        console.log(result.rowsAffected + " row(s) inserted.");
        return cb(null, conn);
      }
    });
};

var query = function (conn, cb) {
  conn.execute(
    "SELECT * FROM mytab",
    function (err, result) {
      if (err) {
        return cb(err, conn);
      } else {
        var buf = result.rows[0];
        console.log("Row returned:");
        console.log(buf);
        return cb(null, conn);
      }
    });
};

async.waterfall(
  [
    function(cb) {
      oracledb.getConnection(dbConfig, cb);
    },
    drop,
    create,
    insert,
    query
  ],
  function (err, conn) {
    if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }
    conn.release(function (err) { if (err) console.error(err.message); });
  });

This gives

cjones@cjones-mac:~/n$ node issue223.js 
1 row(s) inserted.
Row returned:
[ '漢字仮名交じり文' ]

[randomailer]

I'm tried many ways to save special chars, but I don't have result. My oracle db admin said that problem with client because he insert normally this symbols by the console client

[randomailer]

@cjbj I execute this query

SELECT * FROM NLS_DATABASE_PARAMETERS;

NLS_NCHAR_CHARACTERSET,AL16UTF16
NLS_LANGUAGE,AMERICAN
NLS_TERRITORY,AMERICA
NLS_CURRENCY,$
NLS_ISO_CURRENCY,AMERICA
NLS_NUMERIC_CHARACTERS,".,"
NLS_CHARACTERSET,CL8MSWIN1251
NLS_CALENDAR,GREGORIAN
NLS_DATE_FORMAT,DD-MON-RR
NLS_DATE_LANGUAGE,AMERICAN
NLS_SORT,BINARY
NLS_TIME_FORMAT,"HH.MI.SSXFF AM"
NLS_TIMESTAMP_FORMAT,"DD-MON-RR HH.MI.SSXFF AM"
NLS_TIME_TZ_FORMAT,"HH.MI.SSXFF AM TZR"
NLS_TIMESTAMP_TZ_FORMAT,"DD-MON-RR HH.MI.SSXFF AM TZR"
NLS_DUAL_CURRENCY,$
NLS_COMP,BINARY
NLS_LENGTH_SEMANTICS,BYTE
NLS_NCHAR_CONV_EXCP,FALSE
NLS_RDBMS_VERSION,11.2.0.4.0

I now that my db have NLS_CHARACTERSET,CL8MSWIN1251.
But NLS_NCHAR_CHARACTERSET,AL16UTF16

May be driver ignore this field.

if I insert like this

INSERT INTO test VALUES (N'漢字仮名交じり文');

it's ok.

But then I select this table and result have a many question symbols instead of the normaly symbols

[cjbj]

NLS_NCHAR_CHARACTERSET isn't relevant here because node-oracledb doesn't yet support NCHAR etc types. With CL8MSWIN1251 (an 8-bit Latin/Cyrillic char set) are you really able to correctly insert & fetch Chinese characters in any tool? I don't have experience with the char set, but I'd be suspicious.

Show me a screen shot where you run my script and then run the query in SQL*Plus in the same terminal. You will need to add oracledb.autoCommit = true to the script.

There's a dedicated globalization forum at https://community.oracle.com/community/database/globalization_support/content if you need help configuring machine locales etc. The only special thing about node-oracledb is that it always sets the 'client' character set to AL32UTF8.

[cjbj]

Closing: no response for a month.