Galaxy_Stranger's Profile User Rating: -----

Reputation: 1 Apprentice
Group:
Active Members
Active Posts:
100 (0.03 per day)
Joined:
07-February 06
Profile Views:
4,814
Last Active:
User is offline Mar 16 2014 06:27 PM
Currently:
Offline

Previous Fields

Country:
Who Cares
OS Preference:
Linux
Favorite Browser:
FireFox
Favorite Processor:
AMD
Favorite Gaming Platform:
PC
Your Car:
Pontiac
Dream Kudos:
0

Latest Visitors

Icon   Galaxy_Stranger has not set their status

Posts I've Made

  1. In Topic: PDO and Stored Procedures.

    Posted 16 Mar 2014

    Thanks for pointing that out. It never occurred to me not to loop through a single record.

    But the error message variable is set inside the stored procedure and it's printing. The idea was to figure out how to manually output messages when I wanted to do that.

    The next thing is to figure out proper error handling inside the stored procedure and then output proper responses for prompting the user and debugging.
  2. In Topic: PDO and Stored Procedures.

    Posted 16 Mar 2014

    Ok - thanks for all the help guys. I found a solution that works for me. I switched gears a bit and it actually simplified things. Here's what I wound up with:

    Stored Procedure:
    DELIMITER $$
    
    CREATE DEFINER=`blah`@`blah` PROCEDURE `USER_AUTH_ROUTINE`(IN userName VARCHAR(45), IN userPassword VARCHAR(45))
    BEGIN
    
    	/*	Grab the user's account information where the password matches in USER_AUTH:     */
    
    	/*		Create local variables:		*/
    	DECLARE errorMessage VARCHAR(4000) ;		/*	Used to grab error-correction to display to application.	*/
    
    	/*		Select user information that matches the user name and password provided:	*/
    	SELECT *
    	FROM LPF_USERS a
    	WHERE a.USER_NAME = userName
    	AND a.USER_PASSWORD = userPassword ;
    
    	
    	SET errorMessage := "SPAWWWWWWWWCK!!!!" ;
    
    	/*		Return error message if there is one:	*/
    	SELECT errorMessage ;
    END
    


    PHP Code:
    		$myData = array($postUserName, $postUserPassword) ;						//	Put parameters into an array.
    		$sql= 'CALL USER_AUTH_ROUTINE(?, ?)' ;										//	Put Placeholders for parameters.
    		$statement = $connection->prepare($sql) ;								//	Sanitize the statement.
    		$statement->execute($myData) ;																//	Execute Call with array of parameters.
    
    		if($statement->rowCount() == 1)
    		{
    			$statement->setFetchMode(PDO::FETCH_OBJ) ;
    			
    			//	Grab the record set from the object:
    			while( $row = $statement->fetch() )
    			{
    				echo "<br />UserID: " . $row->USER_ID ;
    				echo "<br />First Name: " . $row->USER_FIRST_NAME ;
    				echo "<br />Last Name: " . $row->USER_LAST_NAME . "<hr />" ;
    			}
    			
    			//	Grab the error string:
    			$statement->nextRowset() ;
    			while( $row = $statement->fetch() )
    			{
    				$ERROR_MESSAGE = $row->errorMessage ;
    			}
    			
    			
    			echo "<br />Error Message: " . $ERROR_MESSAGE ;
    			
    			unset($statement) ;
    		}
    		else
    		{
    			echo "There were no results." ;
    			unset($statement) ;
    		}
    


    Again, thanks for all the help. Now I can take this and clean it up.
  3. In Topic: PDO and Stored Procedures.

    Posted 15 Mar 2014

    Ok, still trying to just get it to work. I got rid of the array and replaced everything:
    $postUserName = $_POST['userName'] ;
    $postUserPassword = $_POST['password'] ;
    $ERROR_MESSAGE = 'TEST ERROR' ;
    
    $sql= 'CALL USER_AUTH_ROUTINE(?, ?, ?)' ;           //  Put Placeholders for parameters.
    $statement = $connection->prepare($sql) ;                //  Prepare the statement.
    $statement->bindParam(1, $postUserName, PDO::PARAM_STR, 45) ;
    $statement->bindParam(2, $postUserPassword, PDO::PARAM_STR, 45) ;
    $statement->bindParam(3, $ERROR_MESSAGE, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 500) ;		//	Grab the Error string.
    $statement->execute() ;                     //  Execute Call.
    


    I'm getting Error 42000 - SQL syntax issue. Shouldn't this be right?


    -=[EDIT]=-
    Ok, the examples I've seen say this should work. But I'm going to try returning two selects and putting my string information in that second select and grabbing that. Other examples show doing a separate procedure call specifically for the output variable and that just seems inefficient.
  4. In Topic: PDO and Stored Procedures.

    Posted 13 Mar 2014

    Ok, so it looks like I can't mix and match passing an array to execute() with bindParam(). I think I can deal with this. My big hang-up is that I'm trying to genericize a single method that will take any number of arguments and return the results. Passing the array to execute() makes that easy, I just can't get any return variables back, if needed.

    But I think I can get this to work the way I'd like. I'll post my results when I do.
  5. In Topic: PDO and Stored Procedures.

    Posted 12 Mar 2014

    Thanks for the replies.

    Ok, I guess it won't let me edit the other post, so here's what I've got:
    $myData = array($postUserName, $postPassword) ;           //  Put parameters into an array.
    $sql= "CALL USER_AUTH_ROUTINE(?, ?, :myError)" ;           //  Put Placeholders for parameters.
    $statement = $connection->prepare($sql) ;                //  Prepare the statement.
    $statement->bindParam(':myError', $ERROR_MESSAGE, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 500) ;       //  Grab the Error string.
    $statement->execute($myData) ;                     //  Execute Call with array of parameters.
    


    I'm sorry - I'm just lost on this. I can't find any examples of using bindParam() while passing an array to execute(). I don't understand how I should use the syntax for bindParam in this context. I've tried using a number placeholder and all of the other standard methods. I believe I have correctly altered the data type.

    To check the procedure call, I logged-in to my MySQL server with the command-line client and was able to run the call and then select the output variable - and it had the correct value, so I know the procedure is correctly assigning the value.

    I changed the variable name in case it was reserved or something. In its current state, it runs, but the Error Message value isn't returned. It must have something to do with "myError", but I don't understand what's wrong.

My Information

Member Title:
D.I.C Head
Age:
Age Unknown
Birthday:
Birthday Unknown
Gender:
Years Programming:
1
Programming Languages:
C++,Java,VB.NET,HTML/XHTML/CSS/Javascript

Contact Information

E-mail:
Click here to e-mail me
AIM:
AIM  Series5Ranger
MSN:
MSN  galaxy_stranger@hotmail.com
Website URL:
Website URL  http://robodoom.net
ICQ:
ICQ  92112427

Friends

Galaxy_Stranger hasn't added any friends yet.

Comments

Galaxy_Stranger has no profile comments yet. Why not say hello?