sda vs oda unicode behavior difference?

jpc · Post by **jpc** » Tue 14 Sep 2010 18:47

We have an application that we are converting to D2010 that runs on both Oracle and MS SQL Server. We have run across a possible inconsistency in field mapping when UseUnicode = True.

The Oracle driver brings back Varchar2 as TWideStringField. The SQL Server driver brings back Varchars as TStringField.

Obviously this becomes a problem for our persistent fields.

I would like to find out if we are missing something or if this is really an inconsistency.

This can easily be reproduced by dropping a TSQLConnection and TSQLQuery component on a form. The TSQLQuery selects a table with a simple varchar/varchar2 column. Then you create a persistent field for that column and repeat for each database type.

Example of a SQL Server test case.

Code: Select all

  object SQLConnection1: TSQLConnection
    ConnectionName = 'Dev 6.7 (SqlServer)'
    DriverName = 'DEVARTSQLSERVER'
    GetDriverFunc = 'getSQLDriverSQLServer'
    LibraryName = 'dbexpsda40.dll'
    LoginPrompt = False
    Params.Strings = (
      'drivername=DEVARTSQLSERVER'
      'blobsize=-1'
      'productname=MSSQL'
      'HostName=sql2k8'
      'Database=v670_test'
      'User_Name=test'
      'Password=test'
      'UseUnicode=True')
    VendorLib = 'sqloledb.dll'
    Connected = True
    Left = 32
    Top = 16
  end
  object SQLQuery1: TSQLQuery
    MaxBlobSize = 1
    Params = 
    SQL.Strings = (
      'select * from ac_ids')
    SQLConnection = SQLConnection1
    Left = 32
    Top = 64
    object SQLQuery1LOGINID: TStringField
      FieldName = 'LOGINID'
      Size = 8
    end
  end

Where ac_ids.LOGINID varchar(8) not null

Currently we are using Delphi 2010 (14.0.3593.25826)
dbexpoda40.dll 4.50.0.21
dbexpsda40.dll 4.55.0.23
MS SQL Server 2000, 2005, 2008. Collated Latin
Oracle 10g, 11g NLS Characterset WE8ISO8859P1
Oracle Client 11.1.01
Windows 7 64bit

Thanks for any assistance.

Jeff

AndreyZ · Post by **AndreyZ** » Thu 16 Sep 2010 13:04

Hello,

Varchar type in MSSQL database doesn't support Unicode. If you want to use Unicode, then you have to use nvarchar type instead varchar type.
For nvarchar fields DbxSda driver generates TWideStringField fields.

jpc · Post by **jpc** » Thu 16 Sep 2010 18:52

Thank you for the response AndreyZ. We see this spelled out in the Sda documentation. However our problem is that the Oracle and SQL Server Devart drivers behave differently. A Varchar2 in Oracle doesn't support Unicode either, however, the Devart Oracle driver generates a TWideStringField when UseUnicode is set True and a TStringField when UseUnicode is set False. SQL Server generates a TStringField no matter what we have set.

We would like both drivers to generate TWideStringField for Varchar2/Varchar when UseUnicode = True. This is desirable to allow phased conversion to unicode and allow customers to use Unicode or non Unicode database schemas without two seperate code bases (or persistent fields).

Thanks,

Jeff

bork · Post by **bork** » Thu 23 Sep 2010 14:49

Hello

If you set the UseUnicode property to True, then Oracle client will send all text fields in Unicode (UTF16) encoding to the Oracle server. And the Oracle server convert Unicode to the server charset for the VARCHAR2 data type on his own. For the NVARCHAR2 fields all text data will be saved without changes. SQL client always sends text to SQL Server in non-Unicode encoding for the VARCHAR data type. It is the reason why for the Oracle VARCHAR2 data type TWideStringField is created, but for SQL Server TStringField is created when UseUnicode = True.

TWideStringField is inherited from TStringField and you can use common methods and properties for compatibility in your application. Or please describe where this difference is critical for you and we will try to help.

jpc · Post by **jpc** » Mon 27 Sep 2010 15:52

Hello

Thank you for your response bork.

The difference is critical when it comes to persistent fields. The error we receive is "Type mismatch for field 'xxx', expecting: WideString actual: String."

This error above is an example of running on a SQL Server database when persistent fields created as TWideStringFields from Orale. The opposite message "expecting: String actual: WideString" occurs when persistent fields are created as TStringFields and we are running the app on an Oracle database.

Thanks,

Jeff

AndreyZ · Post by **AndreyZ** » Wed 29 Sep 2010 14:04

You can solve this problem in two ways:
- set UseUnicode=False; in this case both servers will be working with the TStringField field type;
- set UseUnicode=True and change all your varchar fields in the SQL Server database to nvarchar; in this case both servers will be working with the TWideStringField field type.

jpc · Post by **jpc** » Wed 29 Sep 2010 16:20

Hello,
Thanks for your response AndreyZ.

We were hoping to be able to somehow have this consistency between the drivers/databases. The reason why is that the behavior of the Oracle client yields a layer of abstraction that allows a application, written in unicode enabled Delphi, to be able to work with a unicode and non-unicode database without having to maintain two separate code bases. If one of our customers needs a unicode database then we have to force everyone into unicode in that release.

Our application has 137 data modules and about 7750 TStringFields. That's not that difficult to switch the field defs but it's the 791 database tables, 9463 varchar/text columns and, largest reported 30GB, database size that would have me worried about if switching only SQL Server to nvarchars to get around this.

We are going to stay with UseUnicode = False and TStringFields and will re-evaluate for a release after our conversion to Delphi 2010. Thank you for your time in discussing this.

Jeff