ADO reads null values from Sybase stored procedure result

[Carolina]

Hello.

I am using Sybase SQL Advantage to execute a stored procedure which
returns rows with a nvarchar data type column, and everything works
fine. It always returns valid data for that column.

But when I execute the same stored procedure from VB6, VB.NET or ASP,
I get null values for the same nvarchar column, and just for some
rows, not for all of them.

I am using:

a) ASE 12.0.0.5 running on Solaris 8

b) Sybase System 11 ODBC Driver (SYSYBNT.DLL, version 3.11.00.01)

c) Visual Basic 6.0 SP5 with this connection string:
"Provider=MSDASQL;Driver=Sybase System 11;SRVR=servername;DB=dbname;
UID=login;PWD=password;WKID=hostname;APP=appname"

or Visual Basic .NET with this connection string:
"Driver=Sybase System 11;SRVR=servername;DB=dbname;
UID=login;PWD=password;WKID=hostname;APP=appname"
and this reference: Imports Microsoft.Data.Odbc

or ASP 3.0


d) ADO 2.5 to 2.8


Have anybody outhere faced this problem?

I don't understand what this could be. I think there is some problem
with ADO, but I'm not sure where is the problem exactly.

I would appreciate any help!

Thank you.

 

[Al Reid]

Are you sure that none of the columns will allow nulls AND that there are no records that contain nulls? I don't know of there ever
being a bug in ADO related to returning false null values.

I will assume that your table actually includes null value. You have a couple of choices.

1. test for null (If IsNull(rs(0).value) then ...)
2. in your case of string values you can just concatenate an empty string to the field ( strData = rs(0).value & "")

I hope this helps.

 

[Carolina]

Dear Al, thank you for your help!

Here are some more details:

The stored procedure expects two parameters: an employee ID and a
date, which are the primary key for my first table, and part of the
primary key for a related table, thus the procedure returns rows from
the second table for the specified employee and date.

For a particular set of parameters, the procedure returns four rows:
for the first row the nvarchar column that I need to read has a null
value, which is correct based on a set of conditions for this row.
For the next three rows, the same column have valid string values.
Well... this is the result that I get when I execute the procedure
using Sybase SQL Advantage and other tool provided by my Sybase
vendor.

But, when I execute this stored procedure for the same employee + date
using VB6, VB.NET or ASP, I get null values for the same nvarchar
column for the third and fourth rows, and only the second row has the
valid string value.

I've used the VB.NET IsDBNull() and VB6 IsNull() functions, as you
suggested. That's how I know that my app reads null values for some
records, but SQL Advantage has no problem reading the correct values
in the same column for those same records.

 

[Al Reid]

Carolina,

The additional info is useful. First, I would need to see the code you are
using to access the stored procedure in order to help troubleshoot and/or
offer suggestions.

Again, I have never had a problem such as you describe using ADO with
Oracle, MS Access or Oracle.

Perhaps if you post some code, myself or someone with SyBase experience
could help.
--

Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

 

[Carolina]

VB.NET code:

Dim strcon As String
strcon = "Driver=Sybase System 11;SRVR=srvname;DB=bdname;
UID=login;PWD=password;WKID=hostname;APP=appname"

Dim cn As New OdbcConnection(strcon)
cn.Open()

Dim cmd As New OdbcCommand()
With cmd
.Connection = cn
.CommandType = CommandType.StoredProcedure
.CommandText = "{call myprocname ?, ?}"
End With

With cmd.Parameters
.Add("Date", OdbcType.Date).Value = CDate("Mar 7 2004")
.Add("EmpID", OdbcType.Char, 6).Value = "123456"
End With

Dim dr As OdbcDataReader = cmd.ExecuteReader

Do While dr.Read
ListBox1.Items.Add(IIf(IsDBNull(dr("columnname")), "null value", _
dr("columnsname").ToString))
Loop

VB.NET considerations:
I'm using ODBC .NET Data Provider (Namespace: Microsoft.Data.Odbc),
because it was the only way I found for connecting to Sybase. (Any
suggestions about this would be very useful!)


For VB6, I'm using DataEnvironment and a command with this Properties:
General tab:
Dababase Object: Stored Procedure
Object Name: dbo.myprocname
Advanced tab:
Lock type: 1 - Read Only
Recordset returning: On

Procedure call:
Call mydataenvironment.mycommandname(mydate, myempId)
then I retrieve results from recordset

 

[Val Mazur]

Hi,

I think this could be a bug in a Sybase provider. What you could do is to
use Sybase ASE OLEDB Provider. I am using it in my development right now. It
has its own issues, but you could try to see if it fixes the issue with
nulls. You connection string would be like

"Provider=Sybase ASE OLE DB Provider;Server Name=MyServerNameHere;Initial
Catalog=MyDatabaseNameHere;User ID=MyIDHere;Password=PasswordHere;"

Do not forget to use Oledb Managed provider instead. Potential issues with
this connection string is that if your Sybase server configured to use other
port address that 5000, then you need to specify port number as well. For
example with port number 6000 it would look like

"Provider=Sybase ASE OLE DB Provider;Server
Name=MyServerNameHere,6000;Initial Catalog=MyDatabaseNameHere;User
ID=MyIDHere;Password=PasswordHere;

 

[Teaser]

(e-mail address removed) (Carolina) wrote in @posting.google.com:

VB.NET code:

Dim strcon As String
strcon = "Driver=Sybase System 11;SRVR=srvname;DB=bdname;
UID=login;PWD=password;WKID=hostname;APP=appname"

Dim cn As New OdbcConnection(strcon)
cn.Open()

Dim cmd As New OdbcCommand()
With cmd
.Connection = cn
.CommandType = CommandType.StoredProcedure
.CommandText = "{call myprocname ?, ?}"
End With

With cmd.Parameters
.Add("Date", OdbcType.Date).Value = CDate("Mar 7 2004")
.Add("EmpID", OdbcType.Char, 6).Value = "123456"
End With

Dim dr As OdbcDataReader = cmd.ExecuteReader

Do While dr.Read
ListBox1.Items.Add(IIf(IsDBNull(dr("columnname")), "null value", _
dr("columnsname").ToString))
Loop

VB.NET considerations:
I'm using ODBC .NET Data Provider (Namespace: Microsoft.Data.Odbc),
because it was the only way I found for connecting to Sybase. (Any
suggestions about this would be very useful!)


For VB6, I'm using DataEnvironment and a command with this Properties:
General tab:
Dababase Object: Stored Procedure
Object Name: dbo.myprocname
Advanced tab:
Lock type: 1 - Read Only
Recordset returning: On

Procedure call:
Call mydataenvironment.mycommandname(mydate, myempId)
then I retrieve results from recordset

Wasn't there something with the datestamps doing something strange? One
of the developers told me something about the datestamps needing to be
converted to a string, but that's on a 11.5.1 system.

As for the tools you are using, there's a Sybase ADO.NET that is better
than the odbc we've found. We had to get with Sybase directly to get it
and had to have it ordered in. The Reps were a bit clueless when we
first talked about it, but they finally got us what we needed. The
documentation is out on their web page - I think you want to do a search
on Sybase ADO .NET or something like that to find the exact page.

Perhaps that will fix your date issues.

Carl