[ASP / SQL] - Stored procedure aanroepen

[fredman159]

Hoi,
Ik ben onderhand bezig database functies in stored procedures te gieten om zodoende ff wat meer performance te genereren. Erg leuk allemaal, alleen loop ik nu tegen een enigzins vreemd probleem aan. Als ik een recordset ophaal met een stored procedure welke gewoon een select uitvoert is er nix aan de hand, krijg ik een keurige recordset.

Als ik echter een belangrijke procedure in ASP ophaal krijg ik geen recordset terug maar een vaag object waarvan ik niet weet wat het is. Geen array, recordset of iets dergelijks in ieder geval. Terwijl de stored procedure in ISQLW gewoon keurig de records teruggeeft die ik wil hebben!

Wat dus wel werkt is het volgende:

CREATE PROC getAllCats
AS
SELECT cat.catID, cat.catName, cs.cseRenderer AS renderer, cat.catParentID , cat.catCseID
FROM Categories cat
JOIN categoryStyles cs
ON cs.cseID=cat.catCseID
WHERE cat.catDisplay=1
ORDER BY cat.catOrdID
GO

En in VBscript haal ik op de volgende manier de recordset in een array op:

Dim objConn, objRst, objCmd, rs, tmp
Set objConn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
ObjConn.OpenConnstr
objConn.getAllCats RS
tmp=RS.Getrows
ObjConn.Close
Set ObjConn=Nothing
Set RS=Nothing

Erg gezellig. Maar de volgende stored procedure geeft dus (deze is ff groter) geeft in ISQLW keurig terug wat ik moet hebben, maar niet als ik hem binnen VBscript ophaal:

CREATE PROCEDURE test

@soort int
,@idint
,@pageint
,@sizeint

AS

BEGIN
DECLARE @Full int, @List int, @force int, @uselist int

If @soort = 1
BEGIN
SET @uselist = 1
Declare getlist cursor for
SELECT catImsIDFull, catIMSIDList, catForceItemStyle FROM Categories, items WHERE catID=itmCatID AND itmID= @id for read only
END
Else
BEGIN
SET @uselist = 0
Declare getlist cursor for
SELECT catImsIDFull, catImsIDList, catForceItemStyle f FROM Categories WHERE catID=@id For read only
END

OPEN getlist
fetch next from getlist into @full, @list, @force
Close getlist
deallocate getlist

CREATE TABLE #TEMP_ITEMTABLE
(
Row int IDENTITY(1,1) PRIMARY KEY,
itmID int,
itmImsIdFullint,
itmImsIdListint,
ItmUseListbit,
ItmObjectIDint,
typeint
)

IF @soort=1
BEGIN
INSERT INTO #TEMP_ITEMTABLE ( itmID, itmImsIDFull, ItmImsIDList, ItmUseList, ItmObjectID, type)
SELECT itmID, itmImsIDFull, itmImsIDList, itmUseList, itmObjectID, itmObjectTypeID
FROM items
WHERE items.itmcatID = @ID AND items.itmUnapprovedVersionOf IS NULL AND (items.itmDatePubStart <= CURRENT_TIMESTAMP OR items.itmDatePubStart IS NULL) AND (items.itmDatePubEnd >= CURRENT_TIMESTAMP OR items.itmDatePubEnd IS NULL) AND items.itmDisplay=1 ORDER BY itmOrdID
END
ELSE
BEGIN
INSERT INTO #TEMP_ITEMTABLE ( itmID, itmImsIDFull, ItmImsIDList, ItmUseList, ItmObjectID, type)
SELECT itmID, itmImsIDFull, itmImsIDList, itmUseList, itmObjectID, itmObjectTypeID
FROM items
WHERE items.itmID = @ID AND items.itmUnapprovedVersionOf IS NULL AND (items.itmDatePubStart <= CURRENT_TIMESTAMP OR items.itmDatePubStart IS NULL) AND (items.itmDatePubEnd >= CURRENT_TIMESTAMP OR items.itmDatePubEnd IS NULL) AND items.itmDisplay=1 ORDER BY itmOrdID
END

SELECT * FROM #TEMP_ITEMTABLE

END
GO

De data hiervan haal ik met VBscript op de volgende manier binnen:

Dim objConn, objRst, objCmd, tmp, RS
Set objConn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
ObjConn.OpenConnstr
objConn.test 8,1,1,5, RS
tmp=RS.Getrows
ObjConn.Close
Set ObjConn= Nothing
Set RS= Nothing

De volgende foutmelding is het gevolg:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
De fout wijst naar de regel waar ik de RS.GetRows uitvoer...geen idee waarom. Op een of andere manier krijg ik in plaats van een recordset een ander object terug.
Erg interessant, maar wat kan ik daar mee??

Weet iemand hoe ik gewoon braaf een recordset terug kan krijgen?

[Scare360]

Je weet dat stored niet altijd performance winst met zich meebrengt? Ik neem aan dat je een apparte database server gebruikt anders moet je geens tored procedures gebruiken?

Verder kan ik je er niet mee vooruit helpen...aangezien ik uit performance overwegingen nooit storedp's gebruik. En mocht het voorkomen...alleen om de onderhoudbaarheid van de code te garranderen. En ik dus niet al mijn clients moet voorzien van een gereviseerde front-end.

[Crazy D]

Hmmm ik heb (de laatste tijd ) geen problemen met stored procedures, al begrijp ik niet helemaal hoe jij 'm nu aanroept...
Ik roep m'n stored procs zo altijd aan (con is een geopende connectie, en rs een recordsetje)

code:

rs.Open "exec spMijnStoredProc '" & sStringParam & "', " & 
      iNumeriek & ", '" & 
      sNogEenString & "'", con

(in het echt iet netter neergezet)
En dat geeft mij gewoon altijd een recordsetje terug zoals ik ook graag wil...
(uiteraard roep ik stored procs die niets teruggeven gewoon met con.Execute aan).

En uhmm stored procs alleen maar nuttig als je een aparte db server hebt? Heb je daar meer info over?
Bij mijn weten is een stored proc altijd sneller dan een gewone query, omdat die al geoptimalizeerd is terwijl een gewone query on the fly geoptimalizeerd moet worden. Waarom zou het alleen sneller zijn als je een aparte db server hebt

[Scare360]

omdat je normaal de client een recordset laat halen...en deze daarna op de client verwerkt...

als je een HTML stroom moet versturen en tegelijkertijd query's moet uitvoeren wordt je server nou niet bepaald sneller van.

daarom gebruikt men een database server in het geval van stored procedures..

gelukkig is het niet allemaal zo zwart wit dus...maakt niet zoveel hoe je het doet als "het het" maar doet

[Crazy D]

Op vrijdag 23 november 2001 17:49 schreef paulgielens het volgende:
omdat je normaal de client een recordset laat halen...en deze daarna op de client verwerkt...

Hmm ok, maar ik zie geen verschil tussen een select * from tabelletje die in een stored proc zit, of eenzelfde query die je vanuit je recordset opent. De SQL server die verwerkt bij mijn weten altijd de complete query, en geeft (als er wat terug te geven is uiteraard) het resultaat terug, de client bladert alleen maar door de records heen. Dus zou het niets uitmaken lijkt me of die db nou lokaal staat of niet.
Of mis ik nou iets?

[fredman159]

Op vrijdag 23 november 2001 17:31 schreef CrazyD_at_work het volgende:
Hmmm ik heb (de laatste tijd ) geen problemen met stored procedures, al begrijp ik niet helemaal hoe jij 'm nu aanroept...

Hehehehe, ik had ook nooit problemen met die dingen, maar het begint onderhand enigzins op een gestoorde procedure te lijken

Over het aanroepen van het ding. Eeeh, ik heb een hele library van functies op data-access te verzorgen, dus meestal komt het inderdaad neer op een aanroep als jij doet. Aangezien ik er deze keer niet uitkwam ging ik maar met verschillende soorten aanroepen klooien.

Op zich moet dat niet uitmaken, daar ben ik wel achter. Als ik het ding aanroep via een SQL statement SQL = procname & var1 & "," & var2 & "," & var3 dan moet het gewoon een brave recordset teruggeven. En dat is ook normaal het geval. Behalve dan bij deze

En uhmm stored procs alleen maar nuttig als je een aparte db server hebt? Heb je daar meer info over?
Bij mijn weten is een stored proc altijd sneller dan een gewone query, omdat die al geoptimalizeerd is terwijl een gewone query on the fly geoptimalizeerd moet worden. Waarom zou het alleen sneller zijn als je een aparte db server hebt

Hmm ... geen idee waar deze opinie op gebaseerd is. Het is natuurlijk zowiezo handig om een DB server en een webserver apart te houden, en dat is in de meeste gevallen denk ik wel zo, behalve misschien als je de boel thuis test

En een stored proc lijkt me veel sneller dan een query die je zo maar aan de DB voert, zeker als het een ingewikkeld kreng is! Dit vanwege dus de compilatie en het geoptimaliseerde executieplan van de stored proc.

Met name als je op je page waarden uit de DB ophaalt, ze bewerkt, gebruikt in een volgende statement, etc.. kan het een leuk voordeel opleveren omdat je dan niet om de zoveel tijd een SQL statement naar de DB hoeft te pompen maar de DB het hele script uitvoert.

Dat is ook precies de reden waarom ik de bovenstaande stored proc aan het bakken ben...

[raptorix]

Op vrijdag 23 november 2001 17:13 schreef paulgielens het volgende:
Je weet dat stored niet altijd performance winst met zich meebrengt? Ik neem aan dat je een apparte database server gebruikt anders moet je geens tored procedures gebruiken?

Verder kan ik je er niet mee vooruit helpen...aangezien ik uit performance overwegingen nooit storedp's gebruik. En mocht het voorkomen...alleen om de onderhoudbaarheid van de code te garranderen. En ik dus niet al mijn clients moet voorzien van een gereviseerde front-end.

uhm je zegt nu allerlei dingen waar ik minimaal mijn twijfels bij heb, geef me 1 goede reden waarom je GEEN stored procedure zou gebruiken.

[raptorix]

Paul ik heb niet je hele code doorgekregen maar heb sterk het idee dat je je recordset ook als readonly moet ophalen.

[tijn]

De volgende foutmelding is het gevolg:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
De fout wijst naar de regel waar ik de RS.GetRows uitvoer...geen idee waarom. Op een of andere manier krijg ik in plaats van een recordset een ander object terug.
Erg interessant, maar wat kan ik daar mee??

Hehe, afgelopen week een halve dag met exact hetzelfde probleem gezeten.
Het komt waarschijnlijk doordat je een temp table gebruikt. Wanneer je de insert acties uitvoert op de temp table krijg je in de query analyzer altijd iets te zien van 'x row(s) affected'. Het object waar je niks mee kunt (RS) bevat dan de output van de insert actie. Oplossing is om SET NOCOUNT ON aan het begin van je sp te zetten, dan zou het moeten werken.

[fredman159]

Op zaterdag 24 november 2001 15:48 schreef tijn het volgende:

[..]

Hehe, afgelopen week een halve dag met exact hetzelfde probleem gezeten.
Het komt waarschijnlijk doordat je een temp table gebruikt. Wanneer je de insert acties uitvoert op de temp table krijg je in de query analyzer altijd iets te zien van 'x row(s) affected'. Het object waar je niks mee kunt (RS) bevat dan de output van de insert actie. Oplossing is om SET NOCOUNT ON aan het begin van je sp te zetten, dan zou het moeten werken.

Ah cool. Dit klinkt interessant. In de query analyser krijg ik inderdaad meerdere malen 'x row(s) affected' terug ...

Volgens mij zou dit dus wel eens kunnen zijn! Dus aanstaande maandag direct proberen. Alvast bedankt!!!

Kan ik weer rustig slapen dit weekend