[SQL] Insert binnen een cursor?

[Verwijderd]

Ik probeer een insert of update te doen binnenin een cursor. Ik wil namelijk wat data ophalen uit een tabel, en daar vervolgens een functie mee aanroepen, en daar weer een andere actie mee uitvoeren. Maar zodra ik een insert of update uitvoer binnenin een cursor, dan sluit de cursor. Als ik alleen een select uitvoer binnenin een cursor, dan loopt hij wel netjes door de hele tabel heen.

Ik heb dus maar even een simpel testje opgezet dat niks doet behalve testen of een insert lukt, maar dat gaat ook al niet.

Ik snap er helemaal niks van. Ik ben ik de veronderstelling dat ik gewoon een insert binnenin een cursor kan doen, maar is dat ook zo?

Om wat te testen heb ik de volgende code geschreven:

SQL:

declare curGebruiker cursor for
select * from tblGebruiker

open curGebruiker

fetch next from curGebruiker

while @@fetch_status = 0
begin
   insert into tblTest ( colTest) values ('T')

   fetch next from curGebruiker
end

close curGebruiker
deallocatie curGebruiker

De fetch next from curGebruiker binnenin de loop (dus na de insert) levert een error op:

"Cursor not open"

Snapt iemand wat hier mis aan is?

[Remus]

Ik denk dat je iig minimaal een FOR UPDATE in de cursor declaratie moet opnemen. Zie ook http://msdn.microsoft.com...ary/aa258831(SQL.80).aspx

Edit: Hmm, misschien toch niet, want je doet in een insert in een niet gerelateerde tabel. Nou ja, misschien is er iemand die hier meer verstand van heeft dan ik.

[ Voor 34% gewijzigd door Remus op 16-09-2008 18:27 ]

[Verwijderd]

Misschien probeert MSSQL hier slimmer te zijn dan jij, en ziet 'ie dat je niks met de data uit de cursor doet? Select in die cursor 's 1 veld, fetch die into een locale variabele en insert die variabele in tblTest, wie weet werkt 't dan wel.

Ik gebruik dagelijks inserts/updates binnen een cursor, misschien wel miljoenen keren per dag als ik de cursors in triggers bij onze klanten meetel, en heb nog nooit meegemaakt dat bij de 2e 'fetch next' de cursor opeens gesloten is...

[EfBe]

Om welke database gaat het eigenlijk? Indien sqlserver, dan kun je wellicht table variables gebruiken, alleen weet ik niet hoeveel data je wilt gaan gebruiken via de cursor.

Het gebruik van cursors is veelal een teken dat men imperatief met SQL wil omgaan, maar zo werkt SQL niet. Je kunt wellicht dus INSERT INTO table (...) SELECT ... FROM ... gebruiken, dus voor de values een select statement met in de projection (SELECT clause) de function aanroep.

[Verwijderd]

EfBe schreef op dinsdag 16 september 2008 @ 18:33:
Om welke database gaat het eigenlijk?

Gezien @@fetch_status: MSSQL.

Het gebruik van cursors is veelal een teken dat men imperatief met SQL wil omgaan

Of een teken dat je niet met een flinke 'INSERT INTO ... SELECT ... FROM ...' query 100en of 1000en andere gebruikers met timeouts of transaction locks wilt opzadelen.

Met een goed ontworpen query die een cursor gebruikt voorkom je dat. Aan de ene kant voorkom je door iteratief door de records te lopen dat MSSQL besluit om een table lock te leggen, en aan de andere kant belast het de processor en I/O minder bruut, zodat de andere gebruikers ook nog comfortabel de DB kunnen benaderen.
Je eigen query duurt dan wel langer, maar voor onderhouds of reparatie jobs is dat niet zo'n ramp. 't Is een grotere ramp wanneer binnen 5 minuten nadat je een query start de helpdesk wordt platgebeld omdat 't systeem voor hun niet vooruit te branden is.

edit: En bij triggers waarbij in 1 aanroep meerdere records getouched kunnen zijn is bij MSSQL een cursor onontbeerlijk. Daarmee kun je tenminste door alle records in deleted/inserted lopen. Maar dat is iets MSSQL eigens (en Sybase), bij InterBase/Firebird worden de triggers bv. per record aangeroepen.

[ Voor 13% gewijzigd door Verwijderd op 16-09-2008 19:01 ]

[Verwijderd]

Verwijderd schreef op dinsdag 16 september 2008 @ 18:32:
Misschien probeert MSSQL hier slimmer te zijn dan jij, en ziet 'ie dat je niks met de data uit de cursor doet?

Goede opmerking, alleen mijn originele code gebruikt wel data uit de cursor. Ik heb hier alleen een versimpeld voorbeeld neergezet.

[Verwijderd]

Verwijderd schreef op dinsdag 16 september 2008 @ 18:32:
Ik gebruik dagelijks inserts/updates binnen een cursor, misschien wel miljoenen keren per dag als ik de cursors in triggers bij onze klanten meetel, en heb nog nooit meegemaakt dat bij de 2e 'fetch next' de cursor opeens gesloten is...

Maak ik misschien een syntax fout ofzo?

Ik kon me namelijk ook niet voorstellen dat het niet kan wat ik wil. Dus ik heb flink gezocht in de help en google. Queries anders geschreven: een select binnen een cursor doet het gewoon, maar zodra ik een update of insert doe gaat het mis. Ook de aanroep van een andere SP pikt SQL Server niet. En ik geloof niet dat dat aan SQL Server ligt, maar aan mijn code. Ik snap alleen niet wat ik fout doe.

[Verwijderd]

In je uitgeklede voorbeeld maak je geen syntax fouten voor zover ik kan zien (wel 1 tikfout, 't is 'deallocate').
Geef 's de source van de echte query, en indien nodig de tabel/veld info die die query nodig heeft?

[Verwijderd]

Verwijderd schreef op dinsdag 16 september 2008 @ 21:40:
In je uitgeklede voorbeeld maak je geen syntax fouten voor zover ik kan zien (wel 1 tikfout, 't is 'deallocate').
Geef 's de source van de echte query, en indien nodig de tabel/veld info die die query nodig heeft?

ok, morgenochtend als ik weer op mijn werk ben dan schrijf ik de echte code hier.

[Verwijderd]

Hieronder staat de code waarmee ik test. Het is een vereenvoudigd voorbeeld, omdat de daadwerkelijke productie-code gegevens ophaalt uit een paar andere stored procedures en tabellen etc. Dat wordt te lastig om kort en bondig hier neer te zetten.

SQL:

alter procedure testMetEenCursor
as
    declare @naam varchar(90)

    declare curGebruiker cursor for select naam from gebruiker

    open curGebruiker

    fetch next from curGebruiker into @naam

    while @@fetch_status = 0
    begin
        insert into testing (test) values (@naam)

        fetch next from curGebruiker into @naam
    end

    close curGebruiker
    deallocate curGebruiker
go

Deze SP wordt aangeroepen met deze code:

SQL:

exec testMetEenCursor

De tabellen waar ik data uit ophaal zijn deze:

SQL:

CREATE TABLE [Gebruiker] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [Username] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Naam] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_User] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [testing] (
    [test] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Het probleem is dus dat tijdens het loopen door de cursorset de cursor wordt afgesloten. De daadwerkelijke melding is:

Cursor is not open

En die melding wordt gegeven op de vetgedrukte regel:

while @@fetch_status = 0
begin
insert into testing (test) values (@naam)
fetch next from curGebruiker into @naam
end

[EfBe]

Verwijderd schreef op dinsdag 16 september 2008 @ 18:50:
[...]
Gezien @@fetch_status: MSSQL.

Sybase gebruikt die ook dacht ik, vandaar.

[...]
Of een teken dat je niet met een flinke 'INSERT INTO ... SELECT ... FROM ...' query 100en of 1000en andere gebruikers met timeouts of transaction locks wilt opzadelen.

OPTIMISTIC specificeren omdat je niet wilt locken (anders krijg je echt locks en is je argument niet correct) en wel inserts/updates doen is IMHO echt niet correct, je lapt dan ACID aan je laars omdat je niet zeker weet of je updates/inserts ook echt gelukt zijn. (Zie BOL). Dat lijkt me nooit de bedoeling.

Verder is het aan de RDBMS om een insert int select te optimizen, dat ga je zelf niet doen met een cursor, die een temptable veroorzaakt en die dus ook performance kan kosten.

Met een goed ontworpen query die een cursor gebruikt voorkom je dat. Aan de ene kant voorkom je door iteratief door de records te lopen dat MSSQL besluit om een table lock te leggen, en aan de andere kant belast het de processor en I/O minder bruut, zodat de andere gebruikers ook nog comfortabel de DB kunnen benaderen.

insert into select legt ook geen table lock alleen read locks, maar idd je loopt het risico dat er een table lock KAN wordt gelegt ivm de vele readlocks, maar lijkt me wel een edge case.

Je eigen query duurt dan wel langer, maar voor onderhouds of reparatie jobs is dat niet zo'n ramp. 't Is een grotere ramp wanneer binnen 5 minuten nadat je een query start de helpdesk wordt platgebeld omdat 't systeem voor hun niet vooruit te branden is.

Mja, ik weet niet wat voor insert into select queries jij in gedachten had, maar als die dermate groot zijn dat ze de complete db dichttrekken is het wellicht verstandig die te draaien op een tijdsstip dat er geen gebruikers zijn?

edit: En bij triggers waarbij in 1 aanroep meerdere records getouched kunnen zijn is bij MSSQL een cursor onontbeerlijk. Daarmee kun je tenminste door alle records in deleted/inserted lopen. Maar dat is iets MSSQL eigens (en Sybase), bij InterBase/Firebird worden de triggers bv. per record aangeroepen.

Die kun je ook met een set operation op deleted/inserted gebruiken. Zelf met cursors werken in een loop is niets anders dan wat de DB op een lager niveau in relationele algebra gaat doen. Wellicht ook met een cursor maar wellicht ook niet. Ik zie werkelijk niet waarom iemand in een trigger met een cursor door deleted of inserted zou willen wandelen om per row een operatie te doen die je ook met een enkele set operation kan sturen.

[Verwijderd]

OPGELOST!!!!

De optie "Set cursor_close_on_commit" stond aan bij mijn connection.

Deze optie kan je in SQL Query Analyzer zien via:

rechtermuisklik in het query window
eennalaatste optie: Current Connection Properties

...en dan krijg je een lijstje met opties te zien. Het uitzetten van "Set cursor_close_on_commit" lost het probleem op.

[ Voor 82% gewijzigd door Verwijderd op 17-09-2008 09:16 ]

[dominic]

Als je cursor_close_on_commit op disabled zet loop je kans dat cursors zich niet meer sluiten als je ergens vergeet te closen..

Ook al is het probleem opgelost, het lijkt me netter om een aparte stored procedure van de insert query te maken, dan werkt het gewoon. (De transactie vindt dan plaats binnen die stored procedure)

[Verwijderd]

EfBe schreef op woensdag 17 september 2008 @ 09:12:
Sybase gebruikt die ook dacht ik, vandaar.

Klopt. MSSQL 6 is in feite gewoon een aangepaste versie van Sybase, en de volgende versies slepen die legacy dingen nog mee (of genieten ervan, want Sybase is zo slecht nog niet).

OPTIMISTIC specificeren omdat je niet wilt locken (anders krijg je echt locks en is je argument niet correct) en wel inserts/updates doen is IMHO echt niet correct, je lapt dan ACID aan je laars omdat je niet zeker weet of je updates/inserts ook echt gelukt zijn. (Zie BOL). Dat lijkt me nooit de bedoeling.

In theorie heb je gelijk, maar in de praktijk gaat 't soms anders. Pessimistic locking zal in ons geval niet geaccepteerd worden, en realtime inserts/updates in een aantal tabellen zijn essentieel.
Die inserts/updates zijn overigens in tabellen die op zich redundant zijn (en dus herbouwbaar), maar de UI een stuk sneller maken.

Verder is het aan de RDBMS om een insert int select te optimizen, dat ga je zelf niet doen met een cursor, die een temptable veroorzaakt en die dus ook performance kan kosten.

En wat als je uit ervaring weet dat 't RDBMS op z'n bek ging bij de optimalisatie, en een simpel script met een cursor het 100x beter deed?
Ik snap dat je daar in een O/R mapper niks mee kunt, maar als je gewoon direct met de database babbelt is 't wel fijn om te weten.

insert into select legt ook geen table lock alleen read locks, maar idd je loopt het risico dat er een table lock KAN wordt gelegt ivm de vele readlocks, maar lijkt me wel een edge case.

Een edge case idd, maar wel een case die ik meerdere malen heb meegemaakt. Meestal met een UPDATE query overigens, niet met een INSERT.

Mja, ik weet niet wat voor insert into select queries jij in gedachten had, maar als die dermate groot zijn dat ze de complete db dichttrekken is het wellicht verstandig die te draaien op een tijdsstip dat er geen gebruikers zijn?

Dat tijdstip is er niet wanneer je software maakt voor hotels en hotelketens. Ook al slaapt al 't personeel, dan werken de boekings websites en GDS's nog.

Die kun je ook met een set operation op deleted/inserted gebruiken.

Leg 's uit (of geef een link)? BOL, MSDN en Google gaven niet echt iets nuttigs over "set operation".

[EfBe]

'set operation' als in joins, IN queries etc. gewoon, SQL.