Board index » delphi » Stored Proc works with SqlAnywhere ISQL but fails with DELPHI

Stored Proc works with SqlAnywhere ISQL but fails with DELPHI

It strange, its frustrating.  I have the following stored procedure that I
call with 'execsql' from Delphi.  It dies with the message:  "Capability Not
Supported" from the BDE.

Any ideas?  I have another stored proc (with no arguments) that has lots of
these 'execute immediate' statements and everything works fine there.  I can
execute this thing just fine from ISQL as well.

alter procedure medsgroup.grantuser(in userid char(40),in pwd char(40),in gr
char(1),out response char(60))
on exception resume
begin
  declare uid integer;
  /*
  setup or revoke the user we have been passed
  */
  message '---------------------------------' type info to console;
  message 'Begin grantuser with user name = ',userid type info to console;
  message '---------------------------------' type info to console;
  set uid=0;
  set response='';
  select user_id into uid
    from sysuserperm where
    user_name = userid;
  if uid = 0 then
    message 'User does not exist' type info to console;
    if gr = 'R' then
      message 'Revoke request denied' type info to console;
      set response='User logon previously disabled';
      return
    end if;
    execute immediate 'grant connect to ' || userid || ' identified by ' ||
pwd;
    execute immediate 'grant membership in group medsgroup to ' || userid;
    execute immediate 'grant resource to ' || userid;
    message 'User established' type info to console;
    set response='User established and logon enabled';
    return
  else
    message 'User exists' type info to console;
    if gr = 'G' then
      execute immediate 'grant connect to ' || userid || ' identified by '
|| pwd;
      message 'User password changed' type info to console;
      set response='User password changed - logon enabled';
      return
    else
      execute immediate 'revoke connect from ' || userid;
      message 'User revoked' type info to console;
      set response='User logon disabled';
      return
    end if
  end if
end

 

Re:Stored Proc works with SqlAnywhere ISQL but fails with DELPHI


More on this.  I converted the procedure back to it's original form of being
a function.  Originally it returned a char(60) string but that didn't work
any better than it being a procedure (versus function).  I tried returning
an integer status code rather than a string AND IT WORKS.

What gives, is Delphi unable to handle returns of strings from stored
procedures?

Re:Stored Proc works with SqlAnywhere ISQL but fails with DELPHI


Delphi does not support OUT parameters, or at least I was never able to make
it work. D5 help says it supports them now, but I didn't try.
So, alternative is to use result instead of out parameters:
alter procedure medsgroup.grantuser(in userid char(40),in pwd char(40),in gr
char(1)) result response char(60)

instead of:
set response='User logon previously disabled';
you write:
select 'User logon previously disabled' as response;

And btw, Delphi has nothing to do with body of procedure - it can't know
it's using "execute immediate"
:)
--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Quote
Dean Wooldridge wrote in message <7unr2p$8f...@forums.borland.com>...
>It strange, its frustrating.  I have the following stored procedure that I
>call with 'execsql' from Delphi.  It dies with the message:  "Capability
Not
>Supported" from the BDE.

>Any ideas?  I have another stored proc (with no arguments) that has lots of
>these 'execute immediate' statements and everything works fine there.  I
can
>execute this thing just fine from ISQL as well.

>alter procedure medsgroup.grantuser(in userid char(40),in pwd char(40),in
gr
>char(1),out response char(60))
>on exception resume
>begin
>  declare uid integer;
>  /*
>  setup or revoke the user we have been passed
>  */
>  message '---------------------------------' type info to console;
>  message 'Begin grantuser with user name = ',userid type info to console;
>  message '---------------------------------' type info to console;
>  set uid=0;
>  set response='';
>  select user_id into uid
>    from sysuserperm where
>    user_name = userid;
>  if uid = 0 then
>    message 'User does not exist' type info to console;
>    if gr = 'R' then
>      message 'Revoke request denied' type info to console;
>      set response='User logon previously disabled';
>      return
>    end if;
>    execute immediate 'grant connect to ' || userid || ' identified by ' ||
>pwd;
>    execute immediate 'grant membership in group medsgroup to ' || userid;
>    execute immediate 'grant resource to ' || userid;
>    message 'User established' type info to console;
>    set response='User established and logon enabled';
>    return
>  else
>    message 'User exists' type info to console;
>    if gr = 'G' then
>      execute immediate 'grant connect to ' || userid || ' identified by '
>|| pwd;
>      message 'User password changed' type info to console;
>      set response='User password changed - logon enabled';
>      return
>    else
>      execute immediate 'revoke connect from ' || userid;
>      message 'User revoked' type info to console;
>      set response='User logon disabled';
>      return
>    end if
>  end if
>end

Re:Stored Proc works with SqlAnywhere ISQL but fails with DELPHI


There's simply something strange going on here.

I came in this morning and wrote a simple stored procedure:

alter function medsgroup.testfunc()
returns varchar(40)
begin
  declare rstring varchar(40);
  set rstring='this is a return ';
  return(rstring)
end

and an equally simple D5 unit:

unit formTest;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables;

type
  TfrmTest = class(TForm)
    sp1: TStoredProc;
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmTest: TfrmTest;

implementation

{$R *.DFM}

procedure TfrmTest.FormShow(Sender: TObject);
var
  msg: string;
begin
  with sp1 do
  begin
    Prepare;
    ExecProc;
    msg := Params.ParamByName('testfunc').AsString;
    showmessage(msg + intToStr(length(msg)));
  end; {with}
end;

end.

D5 reports a single parameter in "Params".  This executes fine and prints
the return string as expected.

Next I change the stored procedure to:

alter function medsgroup.testfunc(in p1 integer)
returns varchar(40)
begin
  declare rstring varchar(40);
  set rstring='this is a return '+string(p1);
  return(rstring)
end

and modify the D5 code to:

 with sp1 do
  begin
    Params.ParamByName('p1').AsInteger := 5;
    Prepare;
    ExecProc;
    msg := Params.ParamByName('testfunc').AsString;
    showmessage(msg + intToStr(length(msg)));
  end; {with}

and again it works fine passing in the "5" and returning it concatenated to
the string.

Finally I change the stored procedure to:

alter function medsgroup.testfunc(in p1 integer,in p2 varchar(20))
returns varchar(40)
begin
  declare rstring varchar(40);
  set rstring='this is a return '+string(p1)+p2;
  return(rstring)
end

and D5 to:

 with sp1 do
  begin
    Params.ParamByName('p1').AsInteger := 5;
    Params.ParamByName('p2').AsString := 'xx';
    Prepare;
    ExecProc;
    msg := Params.ParamByName('testfunc').AsString;
    showmessage(msg + intToStr(length(msg)));
  end; {with}

D5 shows 3 parameters now (0-2) and the program aborts with "Capability not
supported".

I'm stumped.

Other Threads