Solved

Treat Null as empty string in Sybase

Posted on 2006-06-24
11
934 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 142

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 142

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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 142

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 142

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 142

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Sybase - Convert Rows to Columns 10 377
SyBase SQL Querry Syntax 1 248
SyBase SQL Query Syntax 11 465
How do I get Sybase ASE to throw an error that Java can catch? 4 512
In the hope of saving someone else's sanity... About a year ago we bought a Cisco 1921 router with two ADSL/VDSL EHWIC cards to load balance local network traffic over the two broadband lines we have, but we couldn't get the routing to work consi…
Fine Tune your automatic Updates for Ubuntu / Debian
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now