Patch for ADOStoredProc (Apollo-talk) - Apollo

Hi!  Ajay.  This is take_tk

Re: [Apollo-talk] Patch for ADOStoredProc
"ajay sharma" <ajksh****@myrea*****> wrote:
: Does someone have a script that I can use as an example?

  Some additional patches as follows are needed..
  ADOConnection is somewhat differ (*) from this.  I have no environment
for test ADO now.  So, please for few dates/weeks.

  (*) ADOStoredProc has not "exec_proc" nor ParamByName( #[] methods).
So, must use #active=true and #parameters.  #parameters ( Parameters
class) is not implemented yet.  So, I'll ask Moriq for it.

----
sample for StoredProc
Using SQLConnection
----
require "phi"
require "rdb"
require "g:/apollo/kumalaw/kumalaw_db"

con = DB_v19.db  # please create your SQLConnection

puts con
##  #<RDB::SQLConnection:0x1686370>

stored = RDB::SQLStoredProc.new
stored.sql_connection = con

#p stored
#<RDB::SQLStoredProc:0x1674af8>

#puts stored.methods.sort
##stored_proc_name
##stored_proc_name=
##params
##params=

##puts con.procedure_names
##GET_EMP_PROJ
##ADD_EMP_PROJ
##SUB_TOT_BUDGET
##DELETE_EMPLOYEE
##DEPT_BUDGET
##ORG_CHART
##MAIL_LABEL
##SHIP_ORDER
##SHOW_LANGS
##ALL_LANGS
##MYPROCEDURE
##GET_ITEM_ID

stored.stored_proc_name = "GET_EMP_PROJ"

## -- contents of Stored Procedure "GET_EMP_PROJ"
##
##CREATE PROCEDURE GET_EMP_PROJ (
##  EMP_NO SMALLINT
##) RETURNS (
##  PROJ_ID CHAR(5)
##) AS 
##BEGIN
## FOR SELECT proj_id
##  FROM employee_project
##  WHERE emp_no = :emp_no
##  INTO :proj_id
## DO
##  SUSPEND;
##END

RDB::Param.new( stored , RDB::PT_INPUT )
RDB::Param.new( stored , RDB::PT_OUTPUT )

#stored.params.clear
#undefined method `clear' for #<RDB::Params:0x1681b18> (NameError)

stored.params[0].name = 'EMP_NO'
stored.params[1].name = 'PROJ_ID'

stored.params['EMP_NO'] = 52
stored.exec_proc

p stored.params['PROJ_ID']
  #=> MKTPR  # ok!

p "------"

query = con.new_query('select * from employee_project')
query.display

=begin
  FROM Delphi HELP

var
  P1, P2: TParam;
begin
  ...
  with StoredProc1 do begin
    StoredProcName := 'GET_EMP_PROJ';
    Params.Clear;
    P1 := TParam.Create(Params, ptInput);
    P2 := TParam.Create(Params, ptOutput);
    try
      Params[0].Name := 'EMP_NO';
      Params[1].Name := 'PROJ_ID';
      ParamByname('EMP_NO').AsSmallInt := 52;
      ExecProc;
      Edit1.Text := ParamByname('PROJ_ID').AsString;
    finally
      P1.Free;

      P2.Free;
    end;
  end;
  ...
end;

=end
----

  Patch:

----
C:/Program Files/Apollo/src/ext/rdb/uSQLStoredProc.pas
----
uses SqlExpr, uDefUtils, Pythia, uRDB, uDataSet, uParam;  //++

  ////

function SQLStoredProc_exec_proc(This: Tvalue): Tvalue; cdecl;
var
  real: TSQLStoredProc;
begin
  real := ap_data_get_struct(This);
  result := ap_Integer(real.ExecProc);
end;

function SQLStoredProc_get_params(This: Tvalue): Tvalue; cdecl;
var
  real: TSQLStoredProc;
begin
  real := ap_data_get_struct(This);
  result := ap_iParams(real.Params, This);
end;

procedure Init_SQLStoredProc;
begin
  cSQLStoredProc := DefinePersistentClass(mRDB, TSQLStoredProc, ap_cObject, nil);
  DefineSingletonMethod(cSQLStoredProc, 'new', SQLStoredProc_new);
    //++
  rb_define_method(cSQLStoredProc, 'exec_proc', @SQLStoredProc_exec_proc, 0);
  DefineAttrGet(cSQLStoredProc, 'params', SQLStoredProc_get_params);
end;

end.
----

take_tk = kumagai hidetake