New MS SQL database login having accentuated UTF8 characters created from 4GL code using STORED-PROCEDURE has corrupted characters

Jul 21, 2023
Knowledge

Title

URL Name

new-ms-sql-database-user-login-having-accentuated-utf8-characters-created-from-4gl-code-using-stored-procedure-has-corrupted-characters

Article Number

000229955

Environment

Product: OpenEdge Version: All supported versions OS: All supported platforms Other: UTF-8 MS SQL Dataserver STORED-PROCEDURE

Question/Problem Description

Creating a new MS SQL login on a UTF-8 MS SQL database using the Dataserver STORED-PROCEDURE functionality, generate an MS SQL login having a corrupted character on accentuated characters such as ü

Steps to Reproduce

Start a prowin dataserver connection using the client parameters -cpinternal utf-8 -cpstream utf-8 to an MS SQL UTF-8 database and execute the following 4GL code: def var sql_ as char no-undo. DEFINE VARIABLE hnd AS INTEGER NO-UNDO. sql_ = "CREATE LOGIN ~"Hans Müller~" WITH PASSWORD = 'somePassword', DEFAULT_DATABASE = ~"theMsSqlDbName~", CHECK_POLICY = OFF " . RUN STORED-PROCEDURE l.send-sql-statement hnd = PROC-HANDLE NO-ERROR (sql_). the MS SQL user is then created as: Hans MÃ¼ller instead of Hans Müller

Clarifying Information

Error Message

Defect Number

OCTA-53617

Enhancement Number

Cause

Characters are not passed correctly as UTF-8 characters through the dataserver STORED-PROCEDURE functionality

Resolution

Fixed version(s): OpenEdge 12.8, OpenEdge 12.2.13

Workaround

Instead of using the Dataserver STORED-PROCEDURE functionality, use OS-COMMAND instead to execute the MS SQL utility:
C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE
to add the new MS SQL login using 4GL code like this:

DEFINE VARIABLE msSqlUser AS CHARACTER NO-UNDO.
DEFINE VARIABLE msSqlPassword AS CHARACTER NO-UNDO.

msSqlUser = "Hans Müller".
msSqlPassword = "somePassword".

OUTPUT TO "C:\DLCWORK\test\addLogin.sql" UNBUFFERED.
PUT UNFORMATTED "CREATE LOGIN [" msSqlUser "] WITH PASSWORD = ~"" msSqlPassword "~", DEFAULT_DATABASE = theMsSqlDbName, CHECK_POLICY = OFF ;".
OUTPUT CLOSE.

DEFINE VARIABLE cmd AS CHARACTER NO-UNDO.
cmd = "~"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE~" -S WINDOWSSRV2019 -E -i C:\DLCWORK\test\addLogin.sql -o C:\DLCWORK\test\exit.txt -f 65001 " .
OS-COMMAND SILENT VALUE(cmd).

ASSIGN FILE-INFO:FILE-NAME = "C:\DLCWORK\test\exit.txt".
IF FILE-INFO:FILE-SIZE <> 0 THEN
DO:
DEFINE VARIABLE text-memptr AS MEMPTR NO-UNDO.
COPY-LOB FROM FILE "C:\DLCWORK\test\exit.txt" TO text-memptr.
MESSAGE GET-STRING(text-memptr,1)
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.

or code like this:

DEFINE VARIABLE msSqlUser AS CHARACTER NO-UNDO.
DEFINE VARIABLE msSqlPasswords AS CHARACTER NO-UNDO.
DEFINE VARIABLE createLogin AS CHARACTER NO-UNDO.

msSqlUser = "Hans Müller".
msSqlPasswords = "somePassword".

createLogin = "CREATE LOGIN [" + msSqlUser + "] WITH PASSWORD = ~'" + msSqlPasswords + "~', DEFAULT_DATABASE = lkomMs, CHECK_POLICY = OFF ;".

DEFINE VARIABLE cmd AS CHARACTER NO-UNDO.
cmd = "chcp 65001 && C:\PROGRA~~1\MICROS~~1\CLIENT~~1\ODBC\170\Tools\Binn\SQLCMD.EXE -S WINDOWSSRV2019 -E -Q ~"" + createLogin + "~" -f 65001 " .
OS-COMMAND SILENT VALUE(cmd).

If the Microsoft SQLCMD.EXE executable cannot use Windows authentication but needs to authenticate using an MS SQL login ID and an MS SQL password then the 2 additional Microsoft SQLCMD.EXE parameters:
-U login_id -P somePassword
will then need to be added to the 4GL code examples shown above.

Notes

Progress documentation:
https://docs.progress.com/bundle/abl-reference/page/RUN-STORED-PROCEDURE-statement.html

Keyword Phrase

Last Modified Date

7/21/2023 5:24 PM

New MS SQL database login having accentuated UTF8 characters created from 4GL code using STORED-PROCEDURE has corrupted characters

Files(0)

Was this article helpful?

Was this article helpful?

New MS SQL database login having accentuated UTF8 characters created from 4GL code using STORED-PROCEDURE has corrupted characters

Information

Related Articles