[asp] Stored Proc: afvangen returnwaarde na insert of update

[Verwijderd]

Ik probeer het volgende te bereiken: een record opslaan in een database middels een stored procedure q_saveSubject. De SP kijkt of het record bestaat, zo ja dan doet ie een update, zo nee, dan een insert. So far so good.

Nu return ik in die SP het ID wat het record heeft gekregen. Bij een insert is dit het laatste ID, bij een update is dit ID reeds meegegeven.

De stored procedure:

code:

CREATE PROCEDURE q_saveSubject 
    @intSubjectID int,
    @intCategoryID int,
    @strDescription varchar(100),
    @strSubjecttext varchar(512),
             @strBodytext varchar(1024),
    @datDate datetime,
    @strPath varchar(100),
    @strPicturePath varchar(100),
    @intUserID int,
    @strExternalID varchar(20)
    
AS
    If exists (SELECT ID FROM Subject WHERE ID = @intSubjectID)
    BEGIN
        UPDATE Subject SET
        Category_ID = @intCategoryID,
        [Description] = @strDescription,
        Subjecttext = @strSubjecttext,
        Bodytext = @strBodytext,
        [Date] = @datDate,
        Path = @strPath,
        PicturePath = @strPicturePath,
        UpdateDate = GETDATE(),
        [User_ID ] = @intUserID,
        External_ID = @strExternalID
        WHERE [ID] = @intSubjectID  

        SELECT @intSubjectID AS ID
    END
    ELSE
    BEGIN
        INSERT INTO Subject ( Category_ID, [Description], Subjecttext, Bodytext, Path, PicturePath, UpdateDate, [User_ID], External_ID)
        VALUES (@intCategoryID, @strDescription, @strSubjecttext, @strBodytext, @strPath, @strPicturePath, GETDATE(), @intUserID, @strExternalID)

        SELECT IDENT_CURRENT('Subject') AS ID
    END
GO

Als ik deze SP gewoon in de query analyser aanroep werkt het gewoon. Maar vanuit ASP wil ik het resultaat van de SP in een recordset zetten om vervolgens dat ID weer uit te lezen. Maar ik krijg al een foutmelding (Operation is not allowed when the object is closed) als ik check op rsSubject.EoF (regel 6 in onderstaand asp voorbeeld).

code:

Set rsSubject   = Server.CreateObject("ADODB.Recordset")

spSubject = "q_saveSubject " & .... parametes ....
rsSubject.Open spSubject, dbConnectie, adOpenForwardOnly, adLockReadOnly

    If Not rsSubject.EoF Then
        intSubjectID = Str2Num( rsSubject("ID"))
    End If
    rsSubject.Close

Hoe kan dit??

[faabman]

[b][message=20988667,noline]mentos schreef op 15 juni 2004 @

code:

CREATE PROCEDURE q_saveSubject 
    @intSubjectID int,
    @intCategoryID int,
    @strDescription varchar(100),
    @strSubjecttext varchar(512),
             @strBodytext varchar(1024),
    @datDate datetime,
    @strPath varchar(100),
    @strPicturePath varchar(100),
    @intUserID int,
    @strExternalID varchar(20)
    
AS
    If exists (SELECT ID FROM Subject WHERE ID = @intSubjectID)
set nocount on  
BEGIN
        UPDATE Subject SET
        Category_ID = @intCategoryID,
        [Description] = @strDescription,
        Subjecttext = @strSubjecttext,
        Bodytext = @strBodytext,
        [Date] = @datDate,
        Path = @strPath,
        PicturePath = @strPicturePath,
        UpdateDate = GETDATE(),
        [User_ID ] = @intUserID,
        External_ID = @strExternalID
        WHERE [ID] = @intSubjectID  
set noucount off
        SELECT @intSubjectID AS ID
    END
    ELSE
    BEGIN
set nocount on
        INSERT INTO Subject ( Category_ID, [Description], Subjecttext, Bodytext, Path, PicturePath, UpdateDate, [User_ID], External_ID)
        VALUES (@intCategoryID, @strDescription, @strSubjecttext, @strBodytext, @strPath, @strPicturePath, GETDATE(), @intUserID, @strExternalID)
set nocount off
        SELECT IDENT_CURRENT('Subject') AS ID
    END
GO

set nocount dus

note: ik weet ff niet zeker of het nu set nocount = of set nocount is... (kijk maar ff in BoL)

[ Voor 5% gewijzigd door faabman op 15-06-2004 14:22 ]

[gorgi_19]

Waarom werk je eigenlijk niet met een OUTPUT parameter? Scheelt weer een recordset openen.

[ Voor 25% gewijzigd door gorgi_19 op 15-06-2004 14:21 ]

[bigbeng]

gorgi_19 schreef op 15 juni 2004 @ 14:21:
Waarom werk je eigenlijk niet met een OUTPUT parameter? Scheelt weer een recordset openen.

@TS: Dit is dus de beste oplossing naar mijn mening, maar dan moet je wel een Command object gaan gebruiken. Dit is sowieso een aanrader omdat een command object ook minder gevoelig is voor eventuele hackpogingen. Voor meer informatie over het Command object:
http://msdn.microsoft.com...70/htm/mdmscadoobjmod.asp

Edit:
Ik zeg iets over hackpogingen zonder te verduidelijken, sorry:
Als je input van een gebruiker zonder te controleren toevoegt aan de sql string dan kan een evil gebruiker rare constructies doen in zijn string bv:
';delete * from sysusers;'
Dan zou jouw resultaat sql query wel eens iets anders kunnen gaan doen:

Visual Basic:

"'select * from zoek where zoekstring like '" & userinput & "%' "

Denk maar even na hoe het resultaat er dan uit ziet.
Dit is een veel en makkelijk gemaakte fout.

Bij een Command object doe je iets in de trant van:

Visual Basic:

cmd.CreateParameter("@zoekstring", adVarChar, adInput, Len(userinput), userinput)

En CreateParameter doet dan dus alle input escaping en dergelijke.

[ Voor 41% gewijzigd door bigbeng op 15-06-2004 14:40 ]

[Verwijderd]

Aah, bedankt. Het werkt nu inderdaad.
Stored procedures zijn voor mij nog vrij nieuw. Een klant wilde opeens dat we ze gebruiken, dus vandaar.

Ik zal me er nog eens verder in verdiepen...

Edit:
Inderdaad die manier met die parameter had ik al wel gezien, maar ik vond het niet prettig om steeds ook de naam van de kolom mee te moeten geven en de lengte van de kolom. Hierdoor haal je een groot deel van je voordeel van het gebruik van Stored procedures weer weg. Want je moet nu namelijk bij het wijzigen van je database (andere kolomnaam of andere lengte, vooral dat laatste kan best eens voorkomen) ook je code weer aanpassen.

[ Voor 54% gewijzigd door Verwijderd op 15-06-2004 14:44 ]

[bigbeng]

En om o.a. whoami voor te zijn:
Voor je gewone queries kun je ook beter parametrized queries gebruiken, om dezelfde redenen die ik eerder noemde. Ook is het dan makkelijker om met datums te werken in je query.

[party42]

Verwijderd schreef op 15 juni 2004 @ 14:40:
Stored procedures zijn voor mij nog vrij nieuw. Een klant wilde opeens dat we ze gebruiken, dus vandaar.

Overigens vind ik dit een vrij bizarre overweging hoor. Je werkt toch vanuit eigen kennis? Je werkt toch niet vanuit een soort gedrevenheid c.q. programmeerwens vanuit de klant? De klant geeft je toch een opdracht. Jij bepaalt dan toch hoe je daar invulling aan gaat geven?

Maargoed, ik ken natuurlijk de rest van het verhaal niet...

bigbeng schreef op 15 juni 2004 @ 14:45:
En om o.a. whoami voor te zijn:

[ Voor 10% gewijzigd door party42 op 15-06-2004 14:56 ]

[whoami]

party42 schreef op 15 juni 2004 @ 14:56:
[...]


Overigens vind ik dit een vrij bizarre overweging hoor. Je werkt toch vanuit eigen kennis? Je werkt toch niet vanuit een soort gedrevenheid c.q. programmeerwens vanuit de klant? De klant geeft je toch een opdracht. Jij bepaalt dan toch hoe je daar invulling aan gaat geven?

Klant is altijd koning.
Als die klant nu wil dat je SP's gebruikt, dan doe je dat. Het kan heel goed zijn dat je bv. geen rechtstreekse SELECT / INSERT / UPDATE /DELETE rechten krijgt op de tabellen zelf, maar dat alles via reeds bestaande SP's moet gebeuren.

[curry684]

Verwijderd schreef op 15 juni 2004 @ 14:40:
Aah, bedankt. Het werkt nu inderdaad.
Stored procedures zijn voor mij nog vrij nieuw. Een klant wilde opeens dat we ze gebruiken, dus vandaar.

Mag een klant zich met jouw implementatie bemoeien?

whoami schreef op 15 juni 2004 @ 15:14:
[...]

Klant is altijd koning.
Als die klant nu wil dat je SP's gebruikt, dan doe je dat. Het kan heel goed zijn dat je bv. geen rechtstreekse SELECT / INSERT / UPDATE /DELETE rechten krijgt op de tabellen zelf, maar dat alles via reeds bestaande SP's moet gebeuren.

Als er al bestaande architectuur ligt is dat een tweede, maar als ik van een klant een DB-applicatie moet ontwikkelen vanaf scratch en zij gaan beslissingen op implementatieniveau voor mij nemen dan mogen ze aardig op z'n strepen gaan staan voordat ze koning zijn. Ik ga de automonteur toch ook niet vertellen hoe ie m'n auto moet repareren?

[ Voor 55% gewijzigd door curry684 op 15-06-2004 15:19 ]

[Verwijderd]

Verwijderd schreef op 15 juni 2004 @ 14:40:
Inderdaad die manier met die parameter had ik al wel gezien, maar ik vond het niet prettig om steeds ook de naam van de kolom mee te moeten geven en de lengte van de kolom. Hierdoor haal je een groot deel van je voordeel van het gebruik van Stored procedures weer weg. Want je moet nu namelijk bij het wijzigen van je database (andere kolomnaam of andere lengte, vooral dat laatste kan best eens voorkomen) ook je code weer aanpassen.

- Je geeft de naam van je parameter mee, ipv de kolomnaam.
- Door gebruik van "Len(strInput)" wordt de lengte automatisch goed gezet, gaat nl om de lengte van de string en niet om die van een kolom.
- je had je code ook aan moeten passen als je ergens een parameter bij had gezet

[1liter]

Wat betreft het parameter gebeuren heb ik een aanvullende vraag. Moet de volgorde waarin de parameters in de code opgebouwd worden overeen komen met de volgorde in de sp? Want als ik het in een andere volgorde doe dan krijg ik een conversie error.

Bij voorbaat dank

Hieronder voor de zekerheid ook nog even mijn test:

Visual Basic

code:

Private Sub Test(ByVal Connstring As String)
    ' Create ADO objects
    Dim cmd             As ADODB.Command
    Dim strTest         As String
    Dim intTest         As Integer
    
    Set cmd = New ADODB.Command
    
    strTest = "testdata"
    intTest = 1
    ' Initilize the ADODB.Command Object
    cmd.ActiveConnection = Connstring
    cmd.CommandText = "TEST_Select"
    cmd.CommandType = adCmdStoredProc
       
    ' Create the output parameter
    cmd.Parameters.Append cmd.CreateParameter("@test", adVarChar, adParamInput, Len(strTest), strTest)
    cmd.Parameters.Append cmd.CreateParameter("@id", adInteger, adParamInput, Len(intTest), intTest)
    cmd.Parameters.Append cmd.CreateParameter("@retval", adInteger, adParamOutput, 4)
    
    ' Execute and retrieve the output parameter
    cmd.Execute , , adExecuteNoRecords
    MsgBox (cmd.Parameters("@retval").Value)
    
    Set cmd.ActiveConnection = Nothing
    ' Free objects from memory
    Set cmd = Nothing
End Sub

Stored procedure

code:

CREATE PROCEDURE TEST_Select
@id int,
@test varchar(50),
@retval int output
 AS

Set @retval = 1
GO

[whoami]

1liter schreef op 16 juni 2004 @ 08:43:
Wat betreft het parameter gebeuren heb ik een aanvullende vraag. Moet de volgorde waarin de parameters in de code opgebouwd worden overeen komen met de volgorde in de sp? Want als ik het in een andere volgorde doe dan krijg ik een conversie error.

Ja, alleszins in ASP. Het is de volgorde waarmee je de parameters toevoegd waarmee bepaald wordt welke param het is.

In .NET heb je met de SqlClient classes 'named parameters', en daar maakt de volgorde opzich niet zo veel uit. Met OleDb in .NET moet je de volgorde dan weer wel respecteren.

[Verwijderd]

je hoeft geen lengte van een integer mee te geven.

[/code]
cmd.Parameters.Append cmd.CreateParameter("@id", adInteger, adParamInput, ,intTest)

[/code]


Zelf gebruik ik het connection object als cmd.activeconnection:
in een database.inc.asp:

code:

dim objConn
dim strConnect
set objConn = server.CreateObject("ADODB.Connection")
strConnect = sGetConnString()

en in de pagina:

code:

objConn.open strConnect

en in de include waar je je SP aanroept:

code:

cmd.ActiveConnection = objConn
    objConn.cursorlocation = 3

ff geen id meer waarom we dat hier zo doen (of eigenlijk ooit gedaan hebben) maar goed. het werkt (ook)

[1liter]

Wanneer ik :

code:

cmd.Parameters.Append cmd.CreateParameter("@test", adVarChar, adParamInput, Len(strTest), strTest)

gebruik dan maakt de volgorde tussen sp en code wat uit, maar wanneer ik

code:

cmd.Parameters("@test") = strTest

gebruik is er niets aan de hand en voert hij de sp gewoon uit.

Mijn vraag is dan echter: Welke manier is beter voor bijv. performance, etc.

[whoami]

Je eerste en 2de manier zijn dan toch ook 2 complete verschillende dingen?
Bij de eerste voeg je een parameter toe aan de collection EN geef je 'm een waarde.
Bij de tweede geef je 'm IMO enkel een waarde.

Het is bij het toevoegen dat de volgorde uitmaakt. Qua performance zal er niet veel verschil zijn; dat zijn ook niet de 'optimalisaties' die het verschil maken. (Als je al van optimalisatie kunt spreken).