#12522: Upper on database side compared to java toUpper() on client side does not always match

ivangron reported 2014-09-24T20:32:51Z · last modified 2014-09-26T17:04:59Z

Upper on database side compared to java toUpper() on client side does not always match

Dev	ivangron
QA	tariqrahiman

Priority	Low
Complexity	Unknown

Component	App - General
Version	15.0

In our extraction queries we sometimes compare string variables after upper casing them. We should be passing the unmodified string variable to the database and then using the database to UPPER both its data value and the string variable before the compare instead of java's toUpper() on the client side. This would avoid differences in the return value of upper when language and collation differences occur.

All Comments (7) Change History

SachinPrakash 2014-09-24T23:42:44Z

@ivan: pls specify the queries you have fixed so that QA knows the appropriate scope of testing.

tariqrahiman 2014-09-25T17:33:45Z

@ivan: Is this just for SQL Server 2012 or for all versions of SQL Server ? Where all do I need to verify this ?

a.) Execute Edit in the Server Tree Node only ?

b.) Execute Edit by executing in the Query Analyzer ?

SachinPrakash 2014-09-25T18:54:36Z

SQL Server - all versions

tariqrahiman 2014-09-25T19:13:15Z

create database UniversiteSMS_MimarSinan collate TURKISH_CI_AS

use UniversiteSMS_MimarSinan

-- Scenario 1

create table Information(c1 int, c2 Int);

-- Below SELECT works in Query Analyzer

select * from Information

-- Below EXECUTE EDIT fails : Failed to locate table

select * from Information

-- Scenario 2 with new Table 1

CREATE TABLE [dbo].[ogrencİ] (

[OrderİD] int IDENTITY(1,1) NOT NULL,

[CustomerİD] nchar(5) NULL,

[EmployeeiD] int NULL,

[OrderDate] datetime NULL,

[RequiredDate] datetime NULL,

[ShippedDate] datetime NULL,

[ShipVİa] int NULL,

[FreIght] money NULL,

CONSTRAINT [pk_OrderİD] PRIMARY KEY CLUSTERED([OrderİD])

)

-- Below SELECT works

select * from ogrencİ

-- Below EXECUTE EDIT works

select * from ogrencİ

-- Scenario 3 with new Tables

CREATE TABLE [dbo].[ogrencİ_turkish_upper] (

[myİD_turkish_upper] int IDENTITY(1,1) NOT NULL,

[myID_english_upper] nchar(5) NULL,

[myiD_english_lower] int NULL,

[ShipVİa] int NULL,

[FreIght] money NULL,

[aiIİ] money NULL,

CONSTRAINT [pk_OrderİD_turkish_upper] PRIMARY KEY CLUSTERED([myİD_turkish_upper])

)

CREATE TABLE [dbo].[ogrencI_english_upper] (

[myID_english_upper] int IDENTITY(1,1) NOT NULL,

[İD_english_upper] nchar(5) NULL,

[myiD_english_lower] int NULL,

[ShipVİa] int NULL,

[FreIght] money NULL,

[aiIİ] money NULL,

CONSTRAINT [pk_OrderİD_english_upper] PRIMARY KEY CLUSTERED([myID_english_upper])

)

CREATE TABLE [dbo].[ogrenci_english_lower] (

[myİD_turkish_upper] int IDENTITY(1,1) NOT NULL,

[myID_english_upper] nchar(5) NULL,

[myiD_english_lower] int NULL,

[ShipVİa] int NULL,

[FreIght] money NULL,

[aiIİ] money NULL,

CONSTRAINT [pk_aiIİ] PRIMARY KEY CLUSTERED([myİD_turkish_upper])

)

-- SELECT works but below EXECUTE EDIT fails for all 3 tables

select * from dbo.ogrenci_english_lower

select * from dbo.ogrencI_english_upper

select * from dbo.ogrencİ_turkish_upper

-- Script to Window fails as do Schema Script Generation for above 3 tables in scenario 3

java.lang.Exception: [ogrencİ_turkish_upper] table data not found in system catalog!

at \\...\\ .\\हिñçêČάй語简�?한\\.vꅤᠢꏁꆱ extends 4.a(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.lꆠꅮꎇꉧ enum while.g(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.lꆠꅮꎇꉧ enum while.b(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.lꆠꅮꎇꉧ enum while.run(Unknown Source)

create database UniversiteSMS_MimarSinan collate TURKISH_CI_AS

use UniversiteSMS_MimarSinan

-- Scenario 1

create table Information(c1 int, c2 Int);

-- Below SELECT works in Query Analyzer

select * from Information

-- Below EXECUTE EDIT fails : Failed to locate table

select * from Information

-- Scenario 2 with new Table 1

CREATE TABLE [dbo].[ogrencİ] (

[OrderİD] int IDENTITY(1,1) NOT NULL,

[CustomerİD] nchar(5) NULL,

[EmployeeiD] int NULL,

[OrderDate] datetime NULL,

[RequiredDate] datetime NULL,

[ShippedDate] datetime NULL,

[ShipVİa] int NULL,

[FreIght] money NULL,

CONSTRAINT [pk_OrderİD] PRIMARY KEY CLUSTERED([OrderİD])

)

-- Below SELECT works

select * from ogrencİ

-- Below EXECUTE EDIT works

select * from ogrencİ

-- Scenario 3 with new Tables

CREATE TABLE [dbo].[ogrencİ_turkish_upper] (

[myİD_turkish_upper] int IDENTITY(1,1) NOT NULL,

[myID_english_upper] nchar(5) NULL,

[myiD_english_lower] int NULL,

[ShipVİa] int NULL,

[FreIght] money NULL,

[aiIİ] money NULL,

CONSTRAINT [pk_OrderİD_turkish_upper] PRIMARY KEY CLUSTERED([myİD_turkish_upper])

)

CREATE TABLE [dbo].[ogrencI_english_upper] (

[myID_english_upper] int IDENTITY(1,1) NOT NULL,

[İD_english_upper] nchar(5) NULL,

[myiD_english_lower] int NULL,

[ShipVİa] int NULL,

[FreIght] money NULL,

[aiIİ] money NULL,

CONSTRAINT [pk_OrderİD_english_upper] PRIMARY KEY CLUSTERED([myID_english_upper])

)

CREATE TABLE [dbo].[ogrenci_english_lower] (

[myİD_turkish_upper] int IDENTITY(1,1) NOT NULL,

[myID_english_upper] nchar(5) NULL,

[myiD_english_lower] int NULL,

[ShipVİa] int NULL,

[FreIght] money NULL,

[aiIİ] money NULL,

CONSTRAINT [pk_aiIİ] PRIMARY KEY CLUSTERED([myİD_turkish_upper])

)

-- SELECT works but below EXECUTE EDIT fails for all 3 tables

select * from dbo.ogrenci_english_lower

select * from dbo.ogrencI_english_upper

select * from dbo.ogrencİ_turkish_upper

-- Script to Window fails as do Schema Script Generation for above 3 tables in scenario 3

java.lang.Exception: [ogrencİ_turkish_upper] table data not found in system catalog!

at \\...\\ .\\हिñçêČάй語简�?한\\.vꅤᠢꏁꆱ extends 4.a(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.lꆠꅮꎇꉧ enum while.g(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.lꆠꅮꎇꉧ enum while.b(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.lꆠꅮꎇꉧ enum while.run(Unknown Source)

tariqrahiman 2014-09-25T19:26:51Z

http://blogs.msdn.com/b/qingsongyao/archive/2009/02/13/introduce-the-turkish-i-issue.aspx

http://connect.microsoft.com/SQLServer/feedbackdetail/view/382858/field-names-becomes-case-sentive-when-we-use-turkish-ci-as

ivangron 2014-09-25T20:16:16Z

should be fixed now. we use a .toUpper() at start of all extractions that will need to be discontinued as this causes issues. This issue will need to be looked a broader level and not just a fix for this specific situation.

tariqrahiman 2014-09-26T00:18:21Z

Verified in ADS v15.0.7-7

Search Tips

Aqua Data Studio / nhilam

Upper on database side compared to java toUpper() on client side does not always match

Issue #12522

Completion

1 issue link

Issue #12517

Search Tips

Aqua Data Studio / nhilam

Title

Upper on database side compared to java toUpper() on client side does not always match

Issue #12522

Completion

1 issue link

Issue #12517