#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    1
    Rep Power
    0

    SQL Deadlock in Delphi


    Hello I have SQL Script that works fine in SQL Management Studio but not in Delphi... In Delphi it gives me Deadlock error.

    This is the script that I have

    Code:
    RepairStocks.Close;
    RepairStocks.SQL.Clear;
    
    RepairStocks.SQL.Add('truncate TABLE [dbo].[tmp_Stocks]');
    
    RepairStocks.SQL.Add('insert into tmp_Stocks');
    RepairStocks.SQL.Add('select Barkod,');
    RepairStocks.SQL.Add('sum(kolicina) as Kolicina,');
    RepairStocks.SQL.Add('Max(Kategorija) as Kategorija,');
    RepairStocks.SQL.Add('Max(Artikal) as Artikal,');
    RepairStocks.SQL.Add('Max(Opis) as Opis,');
    RepairStocks.SQL.Add('Max(N_cena) as N_cena,');
    RepairStocks.SQL.Add('Max(N_cena) * sum(kolicina) as N_Iznos,');
    RepairStocks.SQL.Add('Max(P_cena) as P_cena,');
    RepairStocks.SQL.Add('Max(P_Cena) * sum(kolicina) as P_Iznos,');
    RepairStocks.SQL.Add('Max(datum) as datum,');
    RepairStocks.SQL.Add('Max(Golemina) as Golemina');
    RepairStocks.SQL.Add('from [dbo].[Stocks] group by Barkod');
    
    RepairStocks.SQL.Add('truncate TABLE [dbo].[Stocks]');
    
    RepairStocks.SQL.Add('Insert into Stocks');
    RepairStocks.SQL.Add('select * from tmp_Stocks');
    
    RepairStocks.ExecSQL;
    What should I change to avoid Deadlock in Delphi?

    Thank you
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    You are trying to execute multiple SQL statements in one go (and not terminating them correctly either). Try executing them individually. If you want the whole thing to be processed as a transaction, try using a stored proc or call .BeginTrans (or .StartTransaction) on your connection object before you do your DB stuff and then call .Commit at the end.
    Code:
    RepairStocks.SQL.Clear;
    
    RepairStocks.SQL.Add('truncate TABLE [dbo].[tmp_Stocks]');
    RepairStocks.ExecSQL;
    
    RepairStocks.SQL.Clear;
    RepairStocks.SQL.Add('insert into tmp_Stocks');
    RepairStocks.SQL.Add('select Barkod,');
    RepairStocks.SQL.Add('sum(kolicina) as Kolicina,');
    ...
    ...
    RepairStocks.ExecSQL;
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo

IMN logo majestic logo threadwatch logo seochat tools logo