Solved

Problems with Stored Procs(MSSQL6.5) and Delphi 3

Posted on 1998-07-16
26
364 Views
Last Modified: 2010-04-04
I'm  having problems with my StoredProc with MS SQL 6.5 and Delphi 3.  

The Stored Procs(stp) works on record that match a certain id.  The stp takes 2 parameters both integers.  If the stp works on more than 15 records, it quits about on record 14.  It will post the first 14 but after that not 15 and later.  (14 is not consistant either sometimes less never more) If this is wrapped in a transaction nothing is rolled back and no errors are returned.  

Now to debug this I have pulled all the code and placed executing the stp in a button click, this didn't help.  If I execute this stp from ISQL everything works fine...I'm going crazy...if Delphi3Detected then ScrewWithRick...

Rick
0
Comment
Question by:rickpet
  • 13
  • 7
  • 3
  • +3
26 Comments
 
LVL 3

Author Comment

by:rickpet
ID: 1358005
procedure ExecStp(receipt_id: integer;StoredProc: TStoredProc);
begin
  with StoredProc do begin
    Close;
//tried ParamBindMode-pbByNumber
//ParamByName('@receipt_id').AsInteger := receipt_id;
//ParamByName('@iw_jou_trans_num').AsInteger:=JournalTransAction;
    Params.Items[0].AsInteger := FJournalTransAction;
    Params.Items[1].AsInteger := receipt_id;
    try
      Prepare;
      ExecProc;
    except
      Raise Exception.Create(ts_stpAuthorizeReturn_failed);
    end;
  end;
end;
0
 
LVL 1

Expert Comment

by:andrey070798
ID: 1358006
Hi, Rick.

I'm working on Interbase 4.1.
I don't know if it works with MS SQL or not.
Interbase have a limit for the returning cursor (as I remember 64K). When limit reached the result (after limt) might be unknown. Perhaps you've reached the limit. If you can - check documentation for Size Limit Warnings and check size of returning cursor.

Andrey.
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358007
Andry...

there's no dataset being returned...

Rick
0
 
LVL 1

Expert Comment

by:andrey070798
ID: 1358008
Very fast!!!

Can you tell me what is it doing.

1) Select matching records.
2) ......
3) .....

Andrey.
0
 
LVL 1

Expert Comment

by:andrey070798
ID: 1358009
Very fast!!!

Can you tell me what is it doing.

1) Select matching records.
2) ......
3) .....

Andrey.
0
 
LVL 2

Expert Comment

by:odessa
ID: 1358010
first:
you don't need a "prepare" statement becouse, "prepare" do a "compile" of SQL in MSSQL ( or Other) and your SP is already compiled "prepare" is usefull only with multy invoked Query's

and can you write down your SP
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358011
Odessa...

Actually I'm not doing a prepare...I added the prepare right before copying the code...(last thing I tried)...

Andrey

yes Select on matching parameters passed, then spawn new records to children tables...this works fine from ISQL

I do this hundreds of times...in differnt Stored Procs

I think the next thing I'll try is to create the TStoredProc on the fly...and not use the DataModule(DFM)

Rick
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358012
if exists (select * from sysobjects where id = object_id('dbo.sp_iw_auth_return_receipt') and sysstat & 0xf = 4)
      drop procedure dbo.sp_iw_auth_return_receipt
GO


create procedure sp_iw_auth_return_receipt  @iw_jou_trans_num int = NULL,
                                            @receipt_id int
as
DECLARE
  @period int,
  @store int,
  @vendor_id int,
  @receipt_dt datetime,
  @qty decimal(15,5),
  @unit_cost decimal(14,4),
  @expense_flg smallint,
  @store_coa int,
  @receipt_coa int,
  @inc_vat_tax varchar(40),
  @factor decimal(8,4),
  @store_acct int,
  @tax_id int,
  @tax_amount decimal(14,4),
  @total_tax_amount decimal(14,4),
  @tax_per_dollar decimal(14,4),
  @diff_tax_amount decimal(14,4),
  @total_cost decimal(15,5),
  @receipt_type char(1),
  @close_flg smallint,
  @receipt_detail_id int,
  @item_id int,
  @vat_cat_id int,
  @is_first_receipt smallint,   @receipt_tax_amount decimal(14,4),
  @receipt_vat_amount decimal(14,4),
  @offset_acct int,
  @cash_coa int,
  @def_tax_account int,
  @tax_coa int,
  @vat_def_acct int,
  @vat_coa int,
  @unit_tax_amount decimal(14,4)
begin
  select @store        = store,
         @vendor_id    = vendor_id,
         @receipt_dt   = receipt_dt,
         @tax_id       = tax_id,
         @receipt_type = receipt_type
    from iw_receipt_hd
    where receipt_id = @receipt_id
  if @receipt_type <> 'R'
    RETURN
  if @iw_jou_trans_num <> -1
    select @period = period
      from ts_gljetran
      where iw_jou_trans_num = @iw_jou_trans_num
  if @iw_jou_trans_num <> -1
    begin
      select @def_tax_account = def_tax_account
        from sa_tax
        where tax_id = @tax_id
      select @inc_vat_tax = se.switch_value
        from sa_enumerations se
          join sa_switches ss on ss.value_id = se.enum_id
        where se.type = 'ts_inc_vat'
      select @tax_amount = tax_amount
        from ts_gljetran
        where iw_jou_trans_num = @iw_jou_trans_num
      select @total_cost = sum(qty * unit_cost)
        from iw_receipt
        where receipt_id = @receipt_id
      select @tax_per_dollar = @tax_amount / @total_cost
      select @total_tax_amount = sum(convert(decimal(12,2), @tax_per_dollar * (qty * unit_cost)))
        from iw_receipt
        where receipt_id = @receipt_id
      select @diff_tax_amount = @tax_amount - @total_tax_amount
    end
  if exists (select *
               from iw_receipt
               where receipt_id = @receipt_id)
    begin
      select @receipt_detail_id = min(receipt_detail_id)
        from iw_receipt
        where receipt_id = @receipt_id      select @close_flg        = 0,
             @is_first_receipt = 1
    end
  else
    select @close_flg = 1
  while @close_flg <> 1
    begin
      select @item_id     = iwr.item_id,
             @vat_cat_id  = iwr.vat_id,
             @qty         = iwr.qty,
             @unit_cost   = iwr.unit_cost,
             @expense_flg = iwr.expense_flg,
             @receipt_coa = iwr.coa_serial_link,
             @offset_acct = iwr.offset_acct,
             @vat_cat_id  = iwr.vat_id,
             @store_acct  = isa.acct_id
        from iw_receipt iwr
          join iw_store_acct isa on iwr.item_id = isa.item_id
                                              and isa.store = @store
        where receipt_detail_id = @receipt_detail_id      if @iw_jou_trans_num <> -1        begin
          select @factor = factor/100
            from sa_vat
            where vat_cat_id = @vat_cat_id
          if @factor is null
            select @factor = 0
          if @is_first_receipt = 1
            select @receipt_tax_amount = convert(decimal(12,2), (@tax_amount/@total_cost) *
                                                 (qty * unit_cost)) + @diff_tax_amount
              from iw_receipt
              where receipt_detail_id = @receipt_detail_id
          else
            select @receipt_tax_amount = convert(decimal(12,2), (@tax_amount/@total_cost) *
                                                 (qty * unit_cost))
              from iw_receipt              where receipt_detail_id = @receipt_detail_id

          select @unit_tax_amount = @receipt_tax_amount / @qty
          if @unit_tax_amount is null
            select @unit_tax_amount = 0
          select @receipt_vat_amount = (@qty * @unit_cost) * @factor
          if @inc_vat_tax = 'Y'            select @unit_cost = @unit_cost + (@unit_cost * @factor) + @unit_tax_amount
        end      
      if @expense_flg = 1
        exec sp_coa_acct_replace_out @receipt_coa, @store_acct, @store_coa OUTPUT
      else
        select @store_coa = @receipt_coa
      exec sp_iw_add_stock @store_coa, @store, @vendor_id, @item_id, @qty, @unit_cost, @receipt_dt
      if @iw_jou_trans_num <> -1
        begin
          insert ts_gljelitm (coa_serial_link, iw_jou_trans_num, debit, period, off_account)
            values (@receipt_coa, @iw_jou_trans_num, @unit_cost * @qty, @period, @offset_acct)
        end
      if exists (select *
                   from iw_receipt
                   where receipt_id = @receipt_id
                     and receipt_detail_id > @receipt_detail_id)
         select @receipt_detail_id = min(receipt_detail_id)
           from iw_receipt
           where receipt_detail_id > @receipt_detail_id
      else
        select @close_flg = 1
    end
end

GO

GRANT  EXECUTE  ON dbo.sp_iw_auth_return_receipt  TO Phoenix$Users
GO

GRANT  EXECUTE  ON dbo.sp_iw_auth_return_receipt  TO OasisUsersLocal
GO

0
 
LVL 3

Author Comment

by:rickpet
ID: 1358013
Things I've tried...

moved from a Tstoredproc to a TQuery and executed from there...
didn't change anything...created the TQuery and Params on the fly...didn't change...same results...

Next things to do...change stored proc...insert new records into a new table with debug info about the stored proc...see what it's doing...see if we can gleam anything from the debug info...i.e. what section of stp code and id's...

oh well...it's another week...

Rick
0
 
LVL 1

Expert Comment

by:andrey070798
ID: 1358014
Hi Rick.
Looks like problem in  Delphi ....
May be passing parameters (???! ! ! ! ! ), may be components (well ...  may be)  may be something else.

Theoretical questions in my mind:
   1) About your operation system? (If NT do you have Service Pack installed)
   2) Do you have last Patch for Delphi 3?

Practical questions:
May be you'll try to cheat? :-)  
Way I
 1)  Create procedure(with parameters) to call your procedure with parameters ->  in this case your working procedure will be called and executed in SQL

Way II
1) Create table where you'll pass your parameters (To store only one record with two integers)
2) Create procedure (with ID of that record OR without ID (just take last record OR the only one)) to get parameters from table and call your procedure with loaded parameters.
Again call and execution in SQL

Hope it will help.
Andrey

0
 
LVL 2

Expert Comment

by:mvz121697
ID: 1358015
Is it not a sequrity problem ?
When you are accessing the database, with your Delphi-program,
are you using a loginname/password for MS-SQL with enough rights ?

Greetings,
MvZ
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358016
Andrey...I like the way you think...

I'll try your suggestions...

I keep thinking it's a Delphi problem...but you never know...Why would the SQL Server stop...it should just keep going...am stumped on this one...

Mvz...no security problem...have full admin rights...

Rick
0
 
LVL 1

Expert Comment

by:andrey070798
ID: 1358017
Hi Rick.
Check  question
http://www.EXPERTS-EXCHANGE.COM/topics/comp/databases/sql/Q.10064170.
Looks similar to your problem.

Andrey
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Expert Comment

by:andrey070798
ID: 1358018
Hi, Rick.
It interesting to know results.
I think you won!!!
Andrey.
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358019
Andrey...

This problem totally fried my brain last week...so I put it down for this week...I again will pick it up next week and let you know how it goes...

I think your recommendations should work...so cross fingers...

Rick
0
 
LVL 1

Expert Comment

by:andrey070798
ID: 1358020
Hi, Rick!

I just want to know, can we cheat this way.
This is first time I've tried to cheat with DB.
Usually people don't like to cheat. And I'm not give them such examples.
I thought you'd stuck.

Thanks Andrey.
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358021
Andrey...

Sometimes cheating is the only way to get by bugs in the system...I mean where is the bug???We know the bug is on Borlands side...but is it in the BDE? SQL Links? or Delphi 3?
I would suspect the SQL Links...but would need to test with ODBC to rule out the BDE...and I don't have and SQL 6.5 ODBC drivers...

Rick...
0
 

Expert Comment

by:DPedrelli
ID: 1358022
Your log device may not be big enough.  I recently switched from D3 to D4 both running against MSSQL 6.5 with very complicated StoredProcs that effect many rows without a problem.  I made my log device 200MB because when it was 50 or less it kept filling up and causing problems.
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358023
I agree it could be the log device...but I dont think it is...I should see the problem across other more complicated STP that are affecting way more records...appreciate the comment though...

Hopefully I'll have enough time again to work on it today...

Rick
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358024
Okay...back to square one...

We tried hard coding our parameters in another stored proc which called our original stored proc.  We get the same result.

We put some debug info in our stored proc to see if it could see all the records...no problem there...

In the BDE set SQLPassThrough to Not Shared...same thing...

Tested to make sure that ISQL_w could still execute the Stored Proc correctly...yep no problem there...

Got my hands on some ODBC sql65 drivers...will test them out...to see if the problems is with BDE or SQL Links...

Rick
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1358025
Rick,

This is real long shot and I'm kind of embarassed to suggest it, but...

I've had off-and-on trouble using RETURN in a stored proc that I called from Delphi.  (I'm not sure exactly why.  I never really ran it down once I got my proc working.)

You have the line:

if @receipt_type <> 'R'
    RETURN

and I wonder if this is giving you trouble.

Again, I realize this is a real long shot, but it will take about 2 minutes to check.

And it sounds like you've tried about everything else.  :)

Don
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358026
Don...

no effect...

Okay...updated BDE to 4.52...no effect...still using Delphi 3.00...hmmm...want to update to 3.02...but have a small problem...have lots and lots of packages...that other programmers in group are using that are compiled with 3.00...this could cause problems with the group...

Okay...may just break this stored proc in to multi procs...(or code the logic in Delphi and use queries...last resort)
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1358027
Sorry, Rick.

This thing sounds like a real drag!

I'll present it to our DBA (and keep thinking on it myself) and I'll let you know if we come up with anything.

It may very well be the Delphi upgrade will do the trick.  But that also sounds prohibitive at this point.

In any case, good luck with it!
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358028
Okay...

We found the answer to our problem...

Delphi has problems if you pass result sets from one stored proc to another...it seems that Delphi listens in on and if the bit pattern on the record basically looks like a return value(7th/14th record for us) and Delphi will tell SQL server to stop...no problem returned...or rolled back...as there was no problem...SQL Server did what it was told to do...Delphi is just listening a little to closely...

Don(Mayhew)...post an answer for all your help...I'll give you the points...I would like to add this one to the archives...Thanks again Don...I'm glad we finally found the answer to this one...

Rick
0
 
LVL 5

Accepted Solution

by:
mayhew earned 400 total points
ID: 1358029
Rick,

I'm glad you found it too!  It was driving me and our DBA nuts!  This is a good tip too, as this is a situation that I could easily find myself in.

I feel a little bad taking the points, but thanks for your genorisity.  :)
0
 
LVL 3

Author Comment

by:rickpet
ID: 1358030
Don...Thanks again...

Rick
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 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