TD-4222: Insert/Update number-values in Oracle with bind-variables

General discussion forum about all databases other than SqlBase.
IPS-Support

TD-4222: Insert/Update number-values in Oracle with bind-variables

Post by IPS-Support » 16 Nov 2007, 13:52

Hello,



I want to insert or update number-values (datatype of the oracle-columne float or number) in a Oracle-database. The value cames with bind-variables in the Sql-statments. The datatype of the bind-variable in TD5.1 is number.



If the value of the variable is an integer (e. g. 1, 4, 6) all is ok, but if the value is a real number (e. g. 4.56, the result from 3/4) I get the error "Ora-01722: invalid number.



I work with the following system:

Windows 2000, german, german settings

Oracle client 10

TD 5.1

Oracle database 9



Select * from nls_session_parameters shows that NLS_NUMERIC_CHARACTERS is ",."



Select * from nls_database_parameters shows that NLS_NUMERIC_CHARACTERS is ".,"



If I execute Alter Session set NLS_NUMERIC_CHARACTERS = ".," then all works fine, but with TD 3.0 it works without altering the NLS-value and I do not want to change my applications.



Andrea Steimel

Olive

Re: Insert/Update number-values in Oracle with bind-variables

Post by Olive » 16 Nov 2007, 15:28

hi

I really not sure it is a good idea,

I don't guarantee effects...



:idea:

do you try to insert this following line into TD3.0 sql.ini



Code: Select all

substitute=.,,





Olive.

Olive

Re: Insert/Update number-values in Oracle with bind-variables

Post by Olive » 16 Nov 2007, 15:29

hi

I really not sure it is a good idea,

I don't guarantee effects...



:idea:

do you try to insert this following line into TD3.0 sql.ini



Code: Select all

substitute=.,,





Olive.

IPS-Support

Re: Insert/Update number-values in Oracle with bind-variables

Post by IPS-Support » 19 Nov 2007, 16:40

Our SQL.INI already looks like the following



[oragtwy]

remotedbname=IPS,@ips

substitute=SYSSQL.,





Clients PCs will be from all around the world, the database is located in Germany centrally. I asume that the Oracle is installed in english.

The application will be distributed on fileservers, same with the client software for TD3 and the oracle client.



Martin, IPS-Support

Olive

Re: Insert/Update number-values in Oracle with bind-variables

Post by Olive » 21 Nov 2007, 09:56

See bellow may parameters into my database :





Code: Select all

Select * from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS' ;

  NLS_NUMERIC_CHARACTERS is .,

Select * from nls_database_parameters where parameter = 'NLS_NUMERIC_CHARACTERS' ;
NLS_NUMERIC_CHARACTERS is   .,


I never alter my session before executing TD3.0 application
In Sql.ini file, the substitue values are :

Code: Select all

substitute=SYSSQL.,

substitute=",


Others parameters

Code: Select all

NLS_LANGUAGE = AMERICAN

NLS_TERRITORY = AMERICA
NLS_CHARACTERSET = WE8ISO8859P1




first assumption

Maybe NLS_CHARACTERSET causes problem, I remember we had the same problem because NLS_CHARACTERSET was not correctly set,

I can post you our all database parameters, if you want

In this case, it works well





Second assumption

maybe the clients PC international number settings are bad mine are the following

decimal separator is ,



Explore this





Oliv.

IPS-Support

Re: Insert/Update number-values in Oracle with bind-variables

Post by IPS-Support » 11 Jan 2008, 10:39

Hello,

the problem still occurs after installing the service pack 1.

Like wrote before. With Team Developer 3.1 all works fine, with the same PC. With Team Developer 5.1 it only works if I manuelly change the NLS_NUMERIC_CHARACTERS.

Will this be fixed with the next service pack?

Andrea, IPS-Support

cschubert
Germany
Posts: 867
Joined: 23 May 2018, 11:26
Location: Germany

Re: Insert/Update number-values in Oracle with bind-variables

Post by cschubert » 11 Jan 2008, 10:57

Andrea,

IIRC it has always been necessary to set NLS_NUMERIC_CHARACTERS in TD applications. TD needs a point '.' as decimal separator to work correctly. If you don't set it your app becomes dependant of the client settings. You only have to set it once, not for every connection.

HTH

Christian Schubert

IPS-Support

Re: Insert/Update number-values in Oracle with bind-variables

Post by IPS-Support » 11 Jan 2008, 11:12

Hello Christian,

thanks for the information, so it is not a great change, but what I do not understand is, why it works with TD 3.0 without executing the alter session-statement.

I do not change anything on the client-configuration. In the Sql.in there exist the entry "substitute=SYSSQL.,"

Andrea

Jean-Marc Gemperle

Re: Insert/Update number-values in Oracle with bind-variables

Post by Jean-Marc Gemperle » 11 Jan 2008, 14:29

Hi Andrea,

Finally having a look at it, I agree with you IMO this is a bug, but n fact this problem always exister, I tested this back to TD 4.2, 3.1, 3.0 with the same problem...so I'm wondering if you are really sure this worked fine back with TD 3.0. I use ORACLE 10g, with VISTA OS REGIONAL setting set to GERMANY, the NLS_LANG to NLS_LANG=GERMAN_GERMANY.UTF8 or GERMAN_GERMANY.WE8MSWIN1252, the DATABASE INSTANCE is AMERICAN THUS NLS_NUMERIC_CHARACTERS is ".," (AMERICAN NLS) but that setting is TOTALY overiden by the CLIENT NLS_LANG and should take precedence from the DATABASE setting according to what I read from ORACLE. So the database instance setting for NLS_NUMERIC_CHARACTERS should not matter here. If your CLIENT NLS LANG would be something that uses NLS_NUMERIC_CHARACTERS is ".," (ie American) but your OS REGIONAL settings uses COMA as decimal separation then the INSERT would not give the error, the FETCH would work, but would SHOW with a DOT decimal separatoin.

Also this is WORKING FINE WITH OLEDB... this convince me further that there is a bug with NATIVE ROUTER, users should not have to ALTER the session, though this a total functional workaround, it does not hurt to do and what ever setting are on the registry of the deployed application, but also the DATABASE setting you would be garanty to get what you want...I know people use that for long time with the TD products...

So the bug is

TD-4222
ORACLE NATIVE connectivity ONLY : ORA-01722: invalid number when inserting a DECIMAL with ORACLE NLS and OS LOCAL set to any country using COMA as decimal separator

and again NOT SPECIFIC TO TD 5.1 from my testing...See simple testcase attached.

Cheers

Jean-Marc
You do not have the required permissions to view the files attached to this post.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest