Jump to content

pdo stored procedure output parameter


gerhardt7

Recommended Posts

i have made a logon script but he must give a output parameter "relatieid" but if i do print relatieid i don't get the output parameter. what can i do to get the output parameter. i must give -1 if your logon data don't be good and your relatie id if you are succesfull logon. i saw that you can use fatchall but how

 

my script is:

php

<?php 
session_start(); 
$sessieid = session_id(); 
error_reporting(-1); 
ini_set('display_errors', 1); 

if($_SERVER['REQUEST_METHOD'] == 'POST') 
{ 
     $username = $_POST['username']; 
     $wachtwoord = $_POST['wachtwoord']; 
     $ip = $_SERVER["REMOTE_ADDR"]; 
     $computernaam = php_uname('n'); 

     if(trim($username) == '') 
     { 
        echo "<font color='red'>Vul geldige gebruikersnaam in!</font><br>"; 
        header("refresh:5;url=/login/"); 
    exit() ; 
    }     
     
     if(trim($wachtwoord) == '') 
     { 
        echo "<font color='red'>Vul geldige wachtwoord in!</font><br>"; 
        header("refresh:5;url=/login/"); 
    exit() ; 
    } 
      
      
    $db = new PDO('mssql:host=localhost\snelstart;dbname=SluisWWW','test','********'); 
     
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
     
    $stmt = $db->prepare("EXECUTE spMagInvoeren ?,?,?,?,?,?,?"); 
      
    $stmt->bindValue(1 ,$username, PDO::PARAM_STR); 
    $stmt->bindValue(2 ,$wachtwoord); 
    $stmt->bindValue(3 ,$ip); 
    $stmt->bindValue(4 ,$computernaam); 
    $stmt->bindValue(5 ,$sessieid); 
    $stmt->bindParam(6 ,$poging); 
    $stmt->bindParam(7 ,$relatieid); 

    $stmt->execute(); 

print "<br/>Returned: $relatieid<br/><br/>\r\n"; 





    { 
        setcookie("TestCookie", $username); 
        // redirecten 
        exit(); 
    } 
     
     
} 

 

stored procedure ms sql server

USE [sluisWWW]
GO
/****** Object:  StoredProcedure [dbo].[spMagInvoeren]    Script Date: 04/04/2012 09:54:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Henk Boessenkool
-- Create date: 23 Maart 2012
-- Description:	test login
-- =============================================
ALTER PROCEDURE [dbo].[spMagInvoeren] 
-- Add the parameters for the stored procedure here
@Usernaam nVarchar(20) , 
@Wachtwoord nvarchar(20),
@IPAdres nvarchar(20),
@Computer nvarchar (20),
@SessieID nvarchar(50),
@PogingenOver integer  OUTPUT,
@RelatieNummer integer OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @Success INT
SET NOCOUNT ON;
    SET @Success = (SELECT COUNT(*)
        FROM contactpersonen
        WHERE (username = @Usernaam AND wachtwoord = @Wachtwoord)) 
    IF @Success = 1
    BEGIN
       SET @RelatieNummer = (SELECT TOP 1 relatie_id FROM [sluisWWW].[dbo].[contactpersonen]
                             WHERE (username = @Usernaam AND wachtwoord = @Wachtwoord)) 
       
    END
    ELSE SET @RelatieNummer = -1
    INSERT INTO [sluisWWW].[dbo].[Logins] (Login,Wachtwoord,RelatieID,IP,Computer,SessieID) 
                VALUES (@Usernaam,@Wachtwoord,@RelatieNummer,@IPAdres,@Computer,@SessieID)
                
                set @PogingenOver = 24

    
   
    

  
    -- Insert statements for procedure here
END

Link to comment
https://forums.phpfreaks.com/topic/260319-pdo-stored-procedure-output-parameter/
Share on other sites

You have to set the type and length when you bind the variable for it to be output.  You also need to set the OUTPUT flag in the sql text I believe, like so:

    $stmt = $db->prepare("EXECUTE spMagInvoeren ?,?,?,?,?,? OUTPUT,? OUTPUT"); 
      
    $stmt->bindValue(1 ,$username, PDO::PARAM_STR); 
    $stmt->bindValue(2 ,$wachtwoord); 
    $stmt->bindValue(3 ,$ip); 
    $stmt->bindValue(4 ,$computernaam); 
    $stmt->bindValue(5 ,$sessieid); 
    $stmt->bindParam(6 ,$poging, PDO::PARAM_INT, 11); 
    $stmt->bindParam(7 ,$relatieid, PDO::PARAM_INT, 11); 

    $stmt->execute(); 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.