• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1027
  • Last Modified:

Treat Null as empty string in Sybase

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
sridevig
Asked:
sridevig
  • 5
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sridevigAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
sridevigAuthor Commented:
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
 
sridevigAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you seem now to be using single quotes, vs above you were using double quotes?
0
 
sridevigAuthor Commented:
Does it make any difference? It works with double quotes as well.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
sridevigAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Glad I could help you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now