Problem with SELECT from database if using unicode character

Question

I'm using latest version of python and robot framework with DatabaseLibrary (https://franz-see.github.io/Robotframework-Database-Library/api/1.0.1/DatabaseLibrary.html)

And I have problem with SELECT from database when I try using Unicode character like this: select * from labcamprodfull where PRODUCTNAME like '%ščť%'

Then I execute/run test and i get this error: UnicodeEncodeError: 'ascii' codec can't encode character '\xae' in position 54: ordinal not in range(128)

Here is my code:

*** Settings ***
Resource  ../globalVariables.robot
Library  DatabaseLibrary

*** Variables ***
${DB_LOAD_CONNECT_STRING} =  '${userLoad}/${passwordDb}@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${hostnameDb})(PORT=${portDb}))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=${sid})))'
${originalSpecialCharacter}  ®

*** Test Cases ***
Validate_if_special_character_Original_symbol_is_correctly_saved_in_database

    ${byte_string}=  Encode String To Bytes     ${originalSpecialCharacter}  UTF-8
    log to console  bytes is
    log to console  ${byte_string}
    ${_string} =  Decode Bytes To String  ${byte_string}  UTF-8
    log to console  encoded string is
    log to console  ${_string}

    Connect To Database Using Custom Params  cx_Oracle  ${DB_LOAD_CONNECT_STRING}
        ${rowCount}  Row Count  select * from labcamprodfull where PRODUCTNAME like '%${_string}%'
        log to console  Product count with original symbol in name is
        log to console  ${rowCount}
    Disconnect from database

Then I execute/run test and i get this error: UnicodeEncodeError: 'ascii' codec can't encode character '\xae' in position 54: ordinal not in range(128)

Answer 1

The default encoding for cx_Oracle is indeed ASCII. If you can set the encoding for the connection upon creation, all should be well. With basic cx_Oracle that would be done as follows:

cx_Oracle.connect(user, password, dsn, encoding="UTF-8", nencoding="UTF-8")

I'm not sure how that is done with the tool you are using but hopefully that will be sufficient to get you going.

The other option is to the set the environment variable NLS_LANG as in

export NLS_LANG=.AL32UTF8

and then run your application.