VISOCO Software  Home | Products | Services | Download | Order | Support | Forum | Resources | Search | About
  RSS feedRSS Feed  FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups  RegisterRegister   ProfileProfile    Log inLog in 
TSQLStoredProc - "Cursor not returned from query"

 
Post new topic   Reply to topic    VISOCO Software Forum Index -> VISOCO dbExpress driver for Sybase ASE
Author Message
Guest
Guest
PostPosted: Mon Apr 07, 2003 10:14 pm    Post subject: TSQLStoredProc - "Cursor not returned from query" Reply with quote
I got this message when I tried to open a TSQLStoredProc that is expected to return a result set when parameters are correctly set. Even when I instantiate all parameters, the error message remains the same.
I used SQLMonitor and there was no indication of what is the reason of this message, no error is reported its SQL log.

So, 3 questions:

1. How do I get my resultset?
2. How to debug this thing?
3. Once these problems are solved, how do I give a comprehensive message to the user exactly as it comes from the stored procedure's raiserror command?
Back to top
VISOCO Support
VISOCO Software Support


Joined: 16 Jul 2002
Posts: 96

PostPosted: Wed Apr 09, 2003 8:38 am    Post subject: Reply with quote
1, 2. Please, provide a sample of stored procedure.
3. SQLMonitor can trace all server messages generated with raiserror and print commands
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Guest II - the reply
Guest
PostPosted: Wed Apr 09, 2003 6:44 pm    Post subject: Details Reply with quote
VISOCO support wrote:
1, 2. Please, provide a sample of stored procedure.
3. SQLMonitor can trace all server messages generated with raiserror and print commands


create procedure SomeProc (
@pin1 int,
@pvc2 varchar(255),
@pch3 char(1),
@pb4 int = null out,
@pbResultset bit = 1)
as
begin
declare @ierrno int,
@vcerrmsg varchar(255)

if (@pin1 <= 0) or (@pin1 is null )
begin
select @ierrno = 350111,
@vcerrmsg = "Invalid parameter"
goto erro
end

if @pvc2 is null begin
select @pb4 = 0
end

if @pb4 is not null
begin
select @pb4 = SomeIntField
from SomeTable
return
end

if @pbResultset = 1
begin
select SomeOtherIntField,
convert(bit,1) as SomeName
from SomeTable
end
return

erro:
raiserror @ierrno @vcerrmsg
end

Remarks:

* The stored procedure must return a result set when pb4 IS NULL,
@pbResultset = 1 and the remaining parameters are not null. It must return no result set otherwise.

* I have no way of checking whether SQL monitor works correctly because all parameters are set and the stored procedure DOES NOT return my result set (it works fine when I use some non-DBExpress tool like SQL Advantage). I can confirm if SQLMonitor always returns error messages when I see my result set or a comprehensive error message.
Back to top
Guest II - the reply
Guest
PostPosted: Wed Apr 09, 2003 6:44 pm    Post subject: Details Reply with quote
VISOCO support wrote:
1, 2. Please, provide a sample of stored procedure.
3. SQLMonitor can trace all server messages generated with raiserror and print commands


create procedure SomeProc (
@pin1 int,
@pvc2 varchar(255),
@pch3 char(1),
@pb4 int = null out,
@pbResultset bit = 1)
as
begin
declare @ierrno int,
@vcerrmsg varchar(255)

if (@pin1 <= 0) or (@pin1 is null )
begin
select @ierrno = 350111,
@vcerrmsg = "Invalid parameter"
goto erro
end

if @pvc2 is null begin
select @pb4 = 0
end

if @pb4 is not null
begin
select @pb4 = SomeIntField
from SomeTable
return
end

if @pbResultset = 1
begin
select SomeOtherIntField,
convert(bit,1) as SomeName
from SomeTable
end
return

erro:
raiserror @ierrno @vcerrmsg
end

Remarks:

* The stored procedure must return a result set when pb4 IS NULL,
@pbResultset = 1 and the remaining parameters are not null. It must return no result set otherwise.

* I have no way of checking whether SQL monitor works correctly because all parameters are set and the stored procedure DOES NOT return my result set (it works fine when I use some non-DBExpress tool like SQL Advantage). I can confirm if SQLMonitor always returns error messages when I see my result set or a comprehensive error message.
Back to top
VISOCO Support
VISOCO Software Support


Joined: 16 Jul 2002
Posts: 96

PostPosted: Sat Apr 12, 2003 10:41 pm    Post subject: Cursor not returned from query Reply with quote
Hello

Some insignificant simplifications was made:
Code:

create procedure SomeProc (
  @pin1 int,
  @pvc2 varchar(255),
  @pch3 char(1),
  @pb4 int = null out,
  @pbResultset bit = 1)
as
begin
  declare @paramStr varchar(100)
  /* @pin1 */
  select @paramStr = "@pin1 = " + convert(varchar(10), @pin1)
  print @paramStr
  /* @pvc2 */
  select @paramStr = "@pvc2 = " + @pvc2
  print @paramStr
  /* @pch3 */
  select @paramStr = "@pch3 = " + @pch3
  print @paramStr
  /* @pb4 */
  select @paramStr = "@pb4 = " + convert(varchar(10), @pb4)
  print @paramStr
  /* @pbResultset */
  select @paramStr = "@pbResultset = " + convert(varchar(10), @pbResultset)
  print @paramStr

  declare @ierrno int, @vcerrmsg varchar(255)

  if (@pin1 <= 0) or (@pin1 is null )
  begin
    select @ierrno = 350111,
           @vcerrmsg = "Invalid parameter"
    goto erro
  end

  if @pvc2 is null begin
    select @pb4 = 0
  end

  if @pb4 is not null
  begin
    select @pb4 = 0
    return
  end

  if @pbResultset = 1
  begin
    select 999, convert(bit,1) as SomeName
  end

  return

  erro:
    raiserror @ierrno @vcerrmsg
end


Now, you can trace all parameter values with SQLMonitor. It's OnTrace event handler will look like:
Code:

procedure TfMain.smMonitorTrace(Sender: TObject; CBInfo: pSQLTRACEDesc;
  var LogTrace: Boolean);
begin
  if CBInfo.eTraceCat = trMISC then
    mLog.Lines.Add(CBInfo.pszTrace);
end;


We'll see something like
Code:

Message: 0. Severity: 10. @pin1 = 1
Message: 0. Severity: 10. @pvc2 = ABC
Message: 0. Severity: 10. @pch3 = 1
Message: 0. Severity: 10. @pb4 = 0
Message: 0. Severity: 10. @pbResultset = 1


A dbExpress components pass 0 instead of NULL! Here is a sample of code from SQLExpr.pas, line 1059 (hope, you are using Delphi 7, cause situation with Delphi6 is much horrible Evil or Very Mad ):
Code:

{Check if the IN param is NULL and set the NULL indicator}
if ((Params[I].ParamType = ptInput) and Params[I].IsNull) then
  iInd := 1
else
...

Can somebody explain this ?
In that way, you cannot pass initial value of Input/Output parametes as Null. (You can define a parameter pb4 as input (not out) and the problem will disappear).

And finally, a sample of code dealing with driver's interfaces directly:
Code:

var
  Command: ISQLCommand;
  pin1: Integer;
  pvc2: String;
  pch3: String;
  Cursor: ISQLCursor;
  Field1: Integer;
  IsBlank: LongBool;
begin
  if scConnection.SQLConnection.getSQLCommand(Command) <> SQL_SUCCESS then Exit;
  if Command.SetOption(eCommStoredProc, 1) <> SQL_SUCCESS then Exit;
  if Command.prepare('SomeProc', 3) <> SQL_SUCCESS then Exit;
  pin1 := 1;
  if Command.setParameter(1, 0, paramIN, fldINT32, 0, 0, 0, 4, @pin1, 0) <> SQL_SUCCESS then Exit;
  pvc2 := 'ABC';
  if Command.setParameter(2, 0, paramIN, fldZSTRING, 0, 0, 0, Length(pvc2) + 1, PChar(pvc2), 0) <> SQL_SUCCESS then Exit;
  pch3 := '0';
  if Command.setParameter(3, 0, paramIN, fldZSTRING, 0, 0, 0, Length(pch3) + 1, PChar(pch3), 0) <> SQL_SUCCESS then Exit;

  if Command.execute(Cursor) <> SQL_SUCCESS then Exit;

  while Cursor.next = SQL_SUCCESS do
  begin
    Cursor.getLong(1, @Field1, IsBlank);
    if not IsBlank then
      ShowMessage(IntToStr(Field1))
    else
      ShowMessage('Null');
  end;

  Cursor := nil;
  Command := nil;
end;


No need to pass a default parameters. There is no problem.
If you need a full code please email to support@visoco.com
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    VISOCO Software Forum Index -> VISOCO dbExpress driver for Sybase ASE All times are GMT + 2 Hours
Page 1 of 1

 


©VISOCO Software. phpBB by phpBB Group