[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1350
  • Last Modified:

DBExpress - returning value from TSQLStoredProc

I set up a test as basic as possible and get the same problem, returning
value of 0 from a SQLStoredProc.

I did the following test:

1) Placed TSQLConnection on form
2) Placed TSQLStoredProc on form and connected it to SQLConnection and
identified the storedprocname
3) Procedure is (in MSSQL Server 2000):
      INSERT REQHEAD (RequestNo) values (@requestno)
      RETURN @@identity
(tried above with @id = @@identity as well as OUTPUT parameter)

4) Put button on form with code attached:
var v_id, v_id2 : integer;
begin
     try
      sqlstoredproc1.parambyname('@requestno').asstring := Edit1.text;
      sqlstoredproc1.execproc;
      v_id2 := sqlstoredproc1.parambyname('@RETURN_VALUE').asinteger;
     except on E:Edatabaseerror do
      showmessage(E.message);
     end;
5) Tried same above with @id OUTPUT parameter as well

Both tests yield 0. Any ideas?
0
DARRAH
Asked:
DARRAH
1 Solution
 
geobulCommented:
Hi,

You may try something like:

v_id2 := sqlstoredproc1.Params.Items[sqlstoredproc1.Params.Count - 1].asinteger;

because the result will become the last parameter (I hope).

Alternatively you can always get that value using a separate query in the terms of the same session ('select @@IDENTITY as Ident'), open it and get the value.

Regards, Geo
0
 
mokuleCommented:
Hi,
it's just the opposite return value (from RETURN statement in stored procedure) is always number 0 parameter

    ADOCmd: TADOCommand;
begin
  with ADOCmd do
    begin
    CommandText := 'NowyDokument';
    Parameters.Refresh;
    Parameters.ParamByName('@symbol').Value := symbol;
    Prepared := True;
    Execute;
    result := Parameters[0].Value;
    end;
end;
0
 
DARRAHAuthor Commented:
Sorry, regardless, I'm still getting 0 back. I'd prefer not to have to do a separate query when the stored proc should be able to return the value. I hate to think I'm doing something obviously wrong, and not picking up on that. But as I setup the simplest test, I don't see anything.

Any other ideas or tests?
0
 
DARRAHAuthor Commented:
I tried move to dbexpsda.dll (Core Lab) and the problem is eliminated.
0
 
moduloCommented:
Closed, 250 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now