Solved

Executing non data returning stored procedures from Delphi ADO component

Posted on 2006-10-20
8
198 Views
Last Modified: 2010-05-18
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_reference.ExecProc;) 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

0
Comment
Question by:TheForestMan
8 Comments
 
LVL 28

Expert Comment

by:ciuly
ID: 17772591
you should add the eoExecuteNoRecords   to the executeOptions property of teh ado stored proc component.
0
 

Author Comment

by:TheForestMan
ID: 17772995
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
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 17773430
Try using

  datamodule1.update_diff_reference.Open;

instead of

  datamodule1.update_diff_reference.ExecProc;
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:TheForestMan
ID: 17773707
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
0
 
LVL 28

Expert Comment

by:ciuly
ID: 17773735
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
0
 

Author Comment

by:TheForestMan
ID: 17773928
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
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 17983763
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now