how pass fren extended characters to a stored procedure by odbc with sqlserver enable syntax?????????

From: Sylvain Dumont <sylvain_dumont_at_karat.com>
Date: Fri, 14 Jun 2002 14:52:34 -0400
Message-ID: <8YqO8.607$SR3.232559@localhost>

error: ORA-01756: quoted string not properly terminated

une chaine entre apostrophhes ne se termine pas correctement

oracle Retrieving extended characters thru ODBC

PL/SQL procedure parameters

hi, i hope you can help to me.

I have a problem with french and german characters. i have a little stored procedure than return what i'm passing to him.

see these example: (the second one work fine on plsql)

first exemple:

1. i created a new odbc dsn
2. i'm going into sqlserver migration tab to choose Enable Exac Syntax.
3. i'm open Winsql (this is a odbc tools) http://www.indus-soft.com/winsql/
4. i'm write exec ksp_test 0,'HéLLO ORACLE'

i receive this error:

Error: ORA-01756: quoted string not properly terminated   (State:S1000, Native Code: 6DC)

---

I trying to changed too the NLS_LANG in the registry like FRENCH_CANADA.WE8ISO8859P1

     French_France.WE8ISO8859P1

but without any success..

---

i got the same problem with
oracle 9 database with utf8 characters set. oracle 8.1.7 with iso8859p1 characters set.

i trying all latest odbc driver from oracle website.

second exemple:

SQL> variable mytest refcursor;
SQL> exec ksp_test (0,'HéLLO ORACLE',:MYTEST);

PL/SQL procedure successfully completed. SQL> PRINT MYTEST; Your Database Value

---

HéLLO ORACLE

CREATE OR REPLACE PACKAGE KSP_PLSQLRSETPKG       AS
TYPE RCT1 IS REF CURSOR;
END;
;

CREATE OR REPLACE PROCEDURE KSP_TEST (  PATCH INT DEFAULT 0,
 PONC VARCHAR2,
RC1 IN OUT KSP_PLSQLRSETPkg.RCT1
)

AS
BEGIN
OPEN RC1 FOR
SELECT PONC "Your Database Value" FROM DUAL; FROM DUAL;
RETURN ;
END; i'm trying also different nls setting but no good result.

AMERICAN_AMERICA.US7ASCII
AMERICAN_AMERICA.WE8MSWIN1252

FRENCH_CANADA.WE8DEC
FRENCH_CANADA.UTF8
FRENCH_CANADA.WE8MSWIN1252

FRENCH_FRANCE.WE8DEC

FRENCH_FRANCE.UTF8
FRENCH_FRANCE.WE8MSWIN1252 is working well on sqlplus but not by odbc.. also..
i'm declare a variable and
i set
v_variable := 'éä'

and the procedure return the good syntax...

i think is a odbc driver problem....
the driver don't want to accept a extended characters set by a parameters coming from the procedure.

can you confirm to me ..this is a major bug for the driver.. my procedure is very basic to make a little test. did you try my procedure to be sure you have the same problem?

i try with a oracle instance utf8,WE8MSWIN1252 and i got always the same problem.

if i write insert into test values ('äé');

everything is fine...but when i call the procedure... the procedure don't want to accept any german..french or any extended characters...

i need to confirm to me is a bug from the oracle odbc driver or if is possible to help me to find a way to pass theses characters to a stored procedure from odbc driver..

our application is working by odbc driver.

thank you very much.

i'm pretty sure is a bug in the driver ...the bug is coming only when i select "ENABLE EXEC SYNTAX" IN THE DSN (SQLSERVER MIGRATION SECTION) ... i try with Shema Database and Owner and Empty and i got always the same problem

exec KSP_TEST 0,'TéEST'

------------------------>>>>>>>NOT WORKING.

BUT IF I WRITE
CALL KSP_TEST (0,'TéEST')
------------------------->>>>IS WORKING
if i select enable exec or i unselect enable exec...

the CALL KSP_TEST...... is always working properly.

BETWEEN THESE SYNTAX THE NLS_LANG IS NEVER CHANGED.... IS WORKING.....THE NLS_LANG IS GOOD.......because i make a little modification in procedure to be sure the INSERT IS inside the database CORRECTLY. CREATE OR REPLACE PROCEDURE KSP_TEST
(
PATCH INT,
PONC VARCHAR2
)

AS
v_test varchar2(100);

BEGIN v_test := 'test';
INSERT INTO YYY VALUES (PONC); END; Received on Fri Jun 14 2002 - 13:52:34 CDT