null string handling different from BDE

jsantos98 · 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 · 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 · Post by **Dimon** » Fri 21 Nov 2008 07:27

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

Ludek · 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 · 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.