Solved

Treat Null as empty string in Sybase

Posted on 2006-06-24
11
956 Views
Last Modified: 2008-02-01
Hi,

I have vendor application which invokes their own sybase stored procedure that contains following piece of code.  The value of BEN_CODE in TABLE_A is "              ". On invoking stored proc from Visual Basic screen (vendor apps), the @msg is set to ":52D:". I'm trying to create batch program by onvoking same vendor sybase stored procedure but the @msg is getting set to ":52A:".  I'm connecting through ISQL session. Any thoughts how I can get the same result - ":52D:"  as invoking from Visual Basic screen?

declare @ben_code char(11)

select @ben_code = BEN_CODE from TABLE_A

if rtrim(@ben_code) <> ""
select @msg=":52A:"
else
select @msg=":52D:"

Any inputs to resolve this issue would of great help !

regards
0
Comment
Question by:sridevig
  • 5
  • 5
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16975054
Please try to use ISNULL() function:

declare @ben_code char(11)

select @ben_code = isnull( BEN_CODE , "" )from TABLE_A

if rtrim(@ben_code) <> ""
select @msg=":52A:"
else
select @msg=":52D:"
0
 

Author Comment

by:sridevig
ID: 16975080
Thanks for your response. Yes, I have requested to make the similar change to vendor. But they are not willing to do so as it works from their application. I want to understand if there is any way to get similar results as connecting from VB.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16975095
you should get the good result with the following setting:
SET ANSINULL OFF

I assume that the application set's that setting for the session.
link:
http://manuals.sybase.com/onlinebooks/group-as/asg1251e/commands/@Generic__BookTextView/47813
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:sridevig
ID: 16975249
Turning off ANSINULL doesn't appear to help. I tried the following code. Expected result is "Works". I'm still gettting "Not working". Please review and let me know your thoughts.

set ANSINULL OFF

declare @x char(2)
select @x = '  '

if ( rtrim(@x) <> '')
select 'Not working'
else
select 'Works'

regards
0
 

Author Comment

by:sridevig
ID: 16975255
Following works. Let me try my batch program and shall get back to you. Thanks for your inputs.

set ANSINULL ON

declare @x char(2)
select @x = '  '

if ( rtrim(@x) <> '')
select 'Not working'
else
select 'Works'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16975268
you seem now to be using single quotes, vs above you were using double quotes?
0
 

Author Comment

by:sridevig
ID: 16975274
Does it make any difference? It works with double quotes as well.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 16975285
>set ANSINULL ON  works...
this goes contrary to my knowledge of how this setting works in MS SQL Server and how it is explaned in the documentation in Sybase, but, well, if it works...
note that I am not a Sybase expert, just trying to help/guide based on my MS SQL Server experience, which has the same origin as Sybase.

0
 

Author Comment

by:sridevig
ID: 16975328
I was trying to understand if there is any actual difference. My batch program works with this option.

Many thanks for inputs. This was identified as show stopper for my project yesterday. Your input is highly valued and it has helped me to resolve this issue. Once thanks for your inputs and time.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16975752
Glad I could help you!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
Learn how to PXE Boot both BIOS & UEFI machines with DHCP Policies and Custom Vendor Classes
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question