This site requires JavaScript to be enabled

IncidentLookup using list
How can we help?

Solution Suggestions

Please enter your question to get suggestions.
Boolean Operators
OR or vertical bar symbol (|)
Finds a match if either the terms exist in a document (a union using sets).
Finds a match if both terms exist in a document (an intersection of sets).
NOT, minus (-), or exclamation point (!)
Excludes documents that contain the term after NOT (a difference of sets).
Asterisk (*)
The asterisk symbol performs a multi character wildcard search.
Percent sign (%)
The percent sign performs a single character wildcard search.
Quotation marks (")
Use quotation marks to search for an exact phrase with multiple terms.
For examples and more information look at our Knowledge Base: Advanced Search On The Support Portal

Retrieving numerical data using TBox and/or Classic DBEngine

Mising or incorrect decimal separator on numbers with decimal places using ODBC


This behaviour was examined with support case INC0163082 using an Oracle database but might also be valid for other database types.
The customer wanted to verify a number with decimal places using the TBox DBExpert module but the verification failed because the decimal seprator was missing from the actual value: "35,61" != "3561"
On the other hand, using the Classic DBExpert module the verification was always successful: "35,61" == "35,61"

In every case the connection to the database was created by using an original Oracle ODBC driver.
The driver has a setting called "Numeric settings" which determines in which format numeric data from the database should be returned.
It has three options:
Option A: "Use Oracle NLS settings" (Use the locale/language settings from the database itself)
Option B: "Use Microsoft regional settings" (Use the locale/language settings from the local machine)
Option C: "Use US settings" (always return data using the US locale/language settings)

The underlying problem are the different locales of the database and the machine where Tosca runs. Or, to be more precise, the language version of the installed .NET runtime.

The TBox DBExpert module uses a n ODBCConnection which in turn uses an ODBCDataReader to fetch the data from the database.
The .NET framework internally uses the "SQLGetData" ODBC method to retrieve data for a single column.

This can be observed in the ODBC trace log file:


TBox DBExpert (Tosca 13.0)
Oracle ODBC (64 BIt)
Numeric Settings: Use OracleNLS settings

user           6880-52dc EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
  HSTMT               0x0000011C71F930B0
  UWORD                        3
  SWORD                       -8 <SQL_C_WCHAR>
  PTR                 0x0000011C7191B8B0 [      10] "35.61"
  SQLLEN                  4092
  SQLLEN *            0x0000000E90E7DA70 (10)


TBox DBExpert (Tosca 13.0)
Oracle ODBC (64 BIt)
Numeric Settings: Use Microsoft regional settings

user           3138-6e6c EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
  HSTMT               0x0000022D6BB730B0
  UWORD                        3
  SWORD                       -8 <SQL_C_WCHAR>
  PTR                 0x0000022D6B5E1A70 [      10] "35,61"
  SQLLEN                  4092
  SQLLEN *            0x0000005AE71FD510 (10)


TBox DBExpert (Tosca 13.0)
Oracle ODBC (64 BIt)
Numeric Settings: Use US settings

user           be8-6fc8 EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
  HSTMT               0x000001F2ED6730B0
  UWORD                        3
  SWORD                       -8 <SQL_C_WCHAR>
  PTR                 0x000001F2EDDA6130 [      10] "35.61"
  SQLLEN                  4092
  SQLLEN *            0x0000005137DFD4F0 (10)


As you can see depending on the Numeric Settings option the returned number value is formatted with the according decimal separator ("35.61" or "35,61").

In addition to this setting of the Oracle ODBC driver the internal method "internalGetDecimal(int i)" of the  "OdbcDataReader" class of the .NET framework always reads the numeric data as String and then converts this to a decimal number.

// ---------------------------------------------------------------------------------------------- //
// internal GetDecimal
// -------------------
// Get Value of type SQL_DECIMAL or SQL_NUMERIC
// Due to provider incompatibilities with SQL_DECIMAL or SQL_NUMERIC types we always read the value
// as SQL_C_WCHAR and convert it back to the Decimal data type

This is also reflected in the ODBC trace log: the column type in each case is "SQL_C_WCHAR".

So, with this knowledge, we can determine why the verification in Tosca fails (or, why the decimal separator is missing).
For example, if the Oracle database has german NLS settings, but the .NET framework on the Tosca computer was installed with US language settings:
The database returns the String value "35,61" which is correct for german locale/language settings.
The .NET framework method "internalGetDecimal(...)" then converts this String into a decimal number using US locale/language (InvariantCulture) settings.
The conversion interprets the comma "," character as a thousand separator and simply ignores it.

To make the verification work, you have to set the "Numeric Settings" option in the Oracle ODBC driver to a value Tosca/.NET can convert correct.
This in most cases will be: "Use US settings"

What is left, is to see why the Classic DBExpert module does not seem to have the same kind of problem.
The answer can be found again by checking the ODBC trace log files:


MetaSettings    445c-6580 EXIT  SQLExtendedFetch  with return code 0 (SQL_SUCCESS)
  HSTMT               0x0FC344F8
  UWORD                        1 <SQL_FETCH_NEXT>
  SQLLEN                     0
  SQLULEN *           0x0019D870 (1)
  UWORD *             0x06644C38 (0)


In contrast to .NET (which uses the SQLGetData ODBC method to retireve data), Visual Basic 6 uses the "SQLExtendedFetch" ODBC method which fetches the specified rowset of data from the result set and returns data for all bound columns.
This rowset already contains all data in the correct format and no conversion is necessary.




When using the Classic DBExpert module you can probably ignore the different locale/language settings of the database and Tosca, but not when using the TBox DBExpert module.

[opt.] Reference


Tosca is the perfect solution

Optimize - Manage - Automate

Download Trial