null string handling different from BDE

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jsantos98
Posts: 7
Joined: Tue 07 Oct 2008 11:29

null string handling different from BDE

Post by jsantos98 » Wed 19 Nov 2008 19:21

Hello!

We've bought the components to migrate from BDE to SDAC and everithing was working fine with some minor problems.

However, today we have faced a bigger problem!

Using BDE, the SQL:
select 'a' + null + 'b'

return 'ab'

however, using SDAC, we get null!

This is impacting a lot the application!
Is there any "magic" flag to set to maitain the compatibility?

Thanks!

jsantos98
Posts: 7
Joined: Tue 07 Oct 2008 11:29

Post by jsantos98 » Thu 20 Nov 2008 12:07

After an very very very extended search in the internet, I've found a solution!

http://msdn.microsoft.com/en-us/library/ms176056.aspx

After finding this, I've sniffed the initialization procedure of our previous version using BDE and here are the initializations performed by BDE :
-- network protocol: Named Pipes
set quoted_identifier off
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings off
set ansi_padding off
set ansi_nulls off
set concat_null_yields_null off
set language us_english
set dateformat mdy
set datefirst 7

If anyone else has a similar problem, here is your solution ;)

Regards

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 21 Nov 2008 07:27

It is good to see that this problem has been solved.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Fri 21 Nov 2008 13:16

I do NOT recommend changing these ansi settings, as you will lose much of the advanced funktionality of sql server as indexed views, query notifications etc.

much better solution is to take the time for corrections - this one is very simple. just write following

select 'a' + coalesce(null, '') + 'b'

jsantos98
Posts: 7
Joined: Tue 07 Oct 2008 11:29

Post by jsantos98 » Mon 24 Nov 2008 14:00

Ludek wrote:I do NOT recommend changing these ansi settings, as you will lose much of the advanced funktionality of sql server as indexed views, query notifications etc.

much better solution is to take the time for corrections - this one is very simple. just write following

select 'a' + coalesce(null, '') + 'b'
The problem is that this error occurred inside a trigger!
The project is simply huge and the company that developped this before didn't have any care about this kind of stuff, so, for now, we have to use this temporary solution until we have the time and resources to perform some deep revisions.

Post Reply