Solved

Treat Null as empty string in Sybase

Posted on 2006-06-24
11
983 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

717 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