TheForestMan
asked on
Executing non data returning stored procedures from Delphi ADO component
Hello !
I am having some trouble executing a procedure using the Delphi ADO StoredProc component.
The component is calling a stored procedure on a MS SQL 2000 server but this procedure does not return any data.
It is only composed of a while loop and is used to update some data in other tables.
The procedure looks like this :
-------------------------- ---
while (select hardship_diffed_score_id from hardship_diffed_scores where hardship_diffed_score_id = @j) < @i+1
begin
set @frgn_id = (select frgn_id from hardship_diffed_scores where hardship_diffed_score_id = @j)
update hardship_diffed_scores
set
standard_score_id = (select hardship_standard_score_id from hardship_standard_scores
where frgn_id = @frgn_id and hardship_date_id = @newdate),
hist_diff_id = (select hardship_diff_id from hardship_diffs where hardship_date = @newdate
and frgn_id = @frgn_id and account_id = @account_id and diff_type_id = 1),
cs_diff_id = (select hardship_diff_id from hardship_diffs where hardship_date = @newdate
and frgn_id = @frgn_id and account_id = @account_id and diff_type_id = 2)
where hardship_diffed_score_id = @j
set @j = @j +1
end
-------------------------- ----------
The problem is that this procedure executes prefectly when run from the SQL server but does not run when called from the ADO component.
I use the execproc procedure ( datamodule1.update_diff_re ference.Ex ecProc;) in delphi to "execute" but with no success so far.
It seems I am missing something here ... any idea what is wrong?
Thanks already for your help,
Laurent
I am having some trouble executing a procedure using the Delphi ADO StoredProc component.
The component is calling a stored procedure on a MS SQL 2000 server but this procedure does not return any data.
It is only composed of a while loop and is used to update some data in other tables.
The procedure looks like this :
--------------------------
while (select hardship_diffed_score_id from hardship_diffed_scores where hardship_diffed_score_id = @j) < @i+1
begin
set @frgn_id = (select frgn_id from hardship_diffed_scores where hardship_diffed_score_id = @j)
update hardship_diffed_scores
set
standard_score_id = (select hardship_standard_score_id
where frgn_id = @frgn_id and hardship_date_id = @newdate),
hist_diff_id = (select hardship_diff_id from hardship_diffs where hardship_date = @newdate
and frgn_id = @frgn_id and account_id = @account_id and diff_type_id = 1),
cs_diff_id = (select hardship_diff_id from hardship_diffs where hardship_date = @newdate
and frgn_id = @frgn_id and account_id = @account_id and diff_type_id = 2)
where hardship_diffed_score_id = @j
set @j = @j +1
end
--------------------------
The problem is that this procedure executes prefectly when run from the SQL server but does not run when called from the ADO component.
I use the execproc procedure ( datamodule1.update_diff_re
It seems I am missing something here ... any idea what is wrong?
Thanks already for your help,
Laurent
you should add the eoExecuteNoRecords to the executeOptions property of teh ado stored proc component.
ASKER
Hello!
Thanks for your answer, but I already tried it with no result.
Here are the setting I have for the object:
active = false
autocalcfields = true
cachesize = 1
commandtimeout = 30
cursorlocation = cuseclient
cursortype ctkeyset
enablebcd = true
executeoption = [eoExecuteNorecords]
filtered = false
locktype = ltOptimistic
MarsdallOptions moMarshalAll
maxrecords = 0
prepared = false
tag = 0
Hope this helps clarufying
thanks again for your help,
Laurent
Thanks for your answer, but I already tried it with no result.
Here are the setting I have for the object:
active = false
autocalcfields = true
cachesize = 1
commandtimeout = 30
cursorlocation = cuseclient
cursortype ctkeyset
enablebcd = true
executeoption = [eoExecuteNorecords]
filtered = false
locktype = ltOptimistic
MarsdallOptions moMarshalAll
maxrecords = 0
prepared = false
tag = 0
Hope this helps clarufying
thanks again for your help,
Laurent
Try using
datamodule1.update_diff_re ference.Op en;
instead of
datamodule1.update_diff_re ference.Ex ecProc;
datamodule1.update_diff_re
instead of
datamodule1.update_diff_re
ASKER
Hello PierreC!
I tried that as well, it tells me "arguments are of the wrong type , are out of acceptable range or are in conflict with one another".
However, I checked all the types, ranges and nothing seems to be faulty.
And the procedure works great when run from MS Query Analyser with the same parameters... :0(
Thanks anyway,
L
I tried that as well, it tells me "arguments are of the wrong type , are out of acceptable range or are in conflict with one another".
However, I checked all the types, ranges and nothing seems to be faulty.
And the procedure works great when run from MS Query Analyser with the same parameters... :0(
Thanks anyway,
L
can we see tha parameter list and type you are using? eitehr copy it from the dfm if you created them in design mode, or the code that creates them
ASKER
Hello !
I found the bug... I was actually looking in the wrong place.
Sorry for wasting your time and thanks again for your help.
the execproc does work. however, one of the parameter was feeding a reference to an id that did not exist. Somehow, the integer variable feeding this parameter was substracted by 1 (old code i needed to clean up) which would point to a non existent id in the database.
the procedure was therefore executing but got no effect since the id was incorrect.
That will teach me to clean up my code more often I guess.
thanks again !
Laurent
I found the bug... I was actually looking in the wrong place.
Sorry for wasting your time and thanks again for your help.
the execproc does work. however, one of the parameter was feeding a reference to an id that did not exist. Somehow, the integer variable feeding this parameter was substracted by 1 (old code i needed to clean up) which would point to a non existent id in the database.
the procedure was therefore executing but got no effect since the id was incorrect.
That will teach me to clean up my code more often I guess.
thanks again !
Laurent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.