?
Solved

Treat Null as empty string in Sybase

Posted on 2006-06-24
11
Medium Priority
?
1,013 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
10 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

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