What am I doing wrong with this PDO Insert Statement?

Here's my code:

$project_name=$project['str_ProjectName'];
		$tool_name=$project['str_Tool'];
		$developer_name=$project['str_Full_Name_D'];
		$description=$project['str_Description'];
		$project_type=$project['str_ProjectType'];
		$comp_date=$project['str_TargetCompletionDate'];
		if($project['dec_PercentageCompleted']=="" OR $project['dec_PercentageCompleted']==" " OR empty($project['dec_PercentageCompleted']))
			{
				$dec_percentage=0;
			}
			else
			{
				$dec_percentage=$project['dec_PercentageCompleted'];
			}
		$project_id=$project['str_ProjectId'];
		$project_rank=$project['int_ProjectRank'];
		
		//here's my sql
		$sql_11="insert into #tmp_Results 
		(str_ProjectName, str_Tool, str_FullName, str_Description, str_ProjectType, str_TargetCompletionDate, dec_PercentageCompleted, str_ProjectId, int_ProjectRank)
		VALUES (:project_name, :tool_name, :developer_name, :description, :project_type, :comp_date, :dec_percentage, :project_id, :project_rank)";
		$mssql_stmt_11=$mssql_pdo->prepare($sql_11);
		$mssql_stmt_11->execute(array(
		':project_name'=>$project_name, 
		':tool_name'=>$tool_name, 
		':developer_name'=>$developer_name, 
		':description'=>$description, 
		':project_type'=>$project_type, 
		':comp_date'=>$comp_date, 
		':dec_percentage'=>$dec_percentage,
		':project_id'=>$project_id,
		':project_rank'=>$project_rank));
		
		echo $sql_11.'<br>';
			if(!$mssql_stmt_11)
			{
				$arr = $mssql_pdo->errorInfo();
				 print_r($arr);
				 die(); 
			}

Open in new window


I've got some data that contains a " ' " within the string, so I've got "escape" that. I'm working under the impression that by preparing the sql statement and then running the actual insert statement as an array, I don't have to worry about those renegade single ticks causing me problems.

When I run the query, I get this:

insert into #tmp_Results (str_ProjectName, str_Tool, str_FullName, str_Description, str_ProjectType, str_TargetCompletionDate, dec_PercentageCompleted, str_ProjectId, int_ProjectRank) VALUES (:project_name, :tool_name, :developer_name, :description, :project_type, :comp_date, :dec_percentage, :project_id, :project_rank)
insert into #tmp_Results (str_ProjectName, str_Tool, str_FullName, str_Description, str_ProjectType, str_TargetCompletionDate, dec_PercentageCompleted, str_ProjectId, int_ProjectRank) VALUES (:project_name, :tool_name, :developer_name, :description, :project_type, :comp_date, :dec_percentage, :project_id, :project_rank)

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 515 General SQL Server error: Check messages from the SQL Server [515] (severity 16) [(null)]' in /var/www/html/testsite/c0gusb1/test.php:75 Stack trace: #0 /var/www/html/testsite/c0gusb1/test.php(75): PDOStatement->execute(Array) #1 {main} thrown in /var/www/html/testsite/c0gusb1/test.php on line 75

It doesn't look llike any data is being inserted into the database. I say taht basedon the fact that instead of seeing data in the ":placeholders," instead I'm seeing the actual placeholder.

What am I doing wrong?
Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
If you have integer parameters, you have to specify that because the default binding is for a string.
bindParam(8, $project_id, PDO::PARAM_INT)

Open in new window

Bruce GustPHP DeveloperAuthor Commented:
Mark! Thanks SO much for getting back with me. I've been combing the internet looking for clues and I'm coming up short, so I'm glad that I've got a ninja looking at my dilemma.

Since the first post, I was able to implement your suggestion, even before you recommended it, but I'm still not out of the woods. Take a look (I've simplified things so I can better identify where things are going south):

$sth = $mssql_pdo->prepare("exec stp_Select_REPORT_SSRS_DeveloperCurrentProjectList");
//$sth->bindParam(1, $int_DEVELOPERS_ProjectPriority_Id);
$sth->execute();

$result_array="";

	while($result = $sth->fetch(PDO::FETCH_ASSOC)) 
		{
			$result_array[]=$result;
		}
	
$sql_50 = "CREATE TABLE #tmp_Results
	(
		id	int IDENTITY,
		str_ProjectName varchar(150),
		str_Tool varchar(150),
		str_FullName varchar(150),
		str_ProjectType varchar(150), 
		str_Description varchar(max), 
		str_TargetCompletionDate varchar(50), 
		dec_PercentageCompleted decimal(10,2), 
		str_ProjectId varchar(150),
		int_ProjectRank int
	)";
$mssql_stmt_50=$mssql_pdo->query($sql_50);	
if($mssql_stmt_50)
{
	echo "you have a table";
}

foreach($result_array as $project)
		{
		//preparing the data
		$project_name=$project['str_ProjectName'];
		$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (str_ProjectName) VALUES (:project_name)");
		$mssql_stmt_11->bindValue(':project_name', $project_name, PDO::PARAM_STR);
		$mssql_stmt_11->execute();
		}

Open in new window


Now, I've been able to ensure that I do have data, so that much is certain. Still, this is my error:

you have a table Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 515 General SQL Server error: Check messages from the SQL Server [515] (severity 16) [(null)]' in /var/www/html/testsite/c0gusb1/test.php:92 Stack trace: #0 /var/www/html/testsite/c0gusb1/test.php(92): PDOStatement->execute() #1 {main} thrown in /var/www/html/testsite/c0gusb1/test.php on line 92

Line 92 is the $mssql_stmt_11->execute();
 
Where am I blowing it?
Mark BullockQA EngineerCommented:
Is $project_name null?
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Bruce GustPHP DeveloperAuthor Commented:
Mark, I've been able to narrow it down to one particular element. It's when I go insert a decimal value into the #tmp_Results table.

Check it out: This works...

$sql_50 = "CREATE TABLE #tmp_Results
	(
		id	int IDENTITY,
		str_ProjectName varchar(150),
		str_Tool varchar(150), 
		str_FullName varchar(150),
		str_ProjectType varchar(150),
		str_Description varchar(max),
		str_TargetCompletionDate varchar(50)
	)";
$mssql_stmt_50=$mssql_pdo->query($sql_50);

$project_name="";
$tool_name="Donald's List";
$developer_name="Bruce Gust";
$project_type="Operations";
$description="a very large chunk of Bruce's concentration";
$dec_percentage=.2;

$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (str_ProjectName, str_Tool, str_FullName, str_ProjectType, str_Description, str_TargetCompletionDate) VALUES (':project_name', ':tool_name', ':developer_name', ':project_type', ':description', ':comp_date')");
$mssql_stmt_11->bindValue(':project_name', $project_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':tool_name', $tool_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':developer_name', $developer_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':project_type', $project_type, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':description', $description, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':comp_date', $comp_date, PDO::PARAM_STR);

$mssql_stmt_11->execute();

Open in new window


Now, by adding the following, I get the error that's been plaguing me from the beginning. Take a look at line 10, 19, 21 and 28. Those are the new lines and the ones that prompt the error. Here it is:

$sql_50 = "CREATE TABLE #tmp_Results
	(
		id	int IDENTITY,
		str_ProjectName varchar(150),
		str_Tool varchar(150), 
		str_FullName varchar(150),
		str_ProjectType varchar(150),
		str_Description varchar(max),
		str_TargetCompletionDate varchar(50),
		dec_PercentageCompleted decimal(10,2) 
	)";
$mssql_stmt_50=$mssql_pdo->query($sql_50);

$project_name="";
$tool_name="Donald's List";
$developer_name="Bruce Gust";
$project_type="Operations";
$description="a very large chunk of Bruce's concentration";
$dec_percentage=.2;

$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (str_ProjectName, str_Tool, str_FullName, str_ProjectType, str_Description, str_TargetCompletionDate, dec_PercentageCompleted) VALUES (':project_name', ':tool_name', ':developer_name', ':project_type', ':description', ':comp_date', :dec_percentage)");
$mssql_stmt_11->bindValue(':project_name', $project_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':tool_name', $tool_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':developer_name', $developer_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':project_type', $project_type, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':description', $description, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':comp_date', $comp_date, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':dec_percentage', $dec_percentage, PDO::PARAM_STR);

$mssql_stmt_11->execute();

Open in new window


The error that I'm getting is this:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 8114 General SQL Server error: Check messages from the SQL Server [8114] (severity 16) [(null)]' in /var/www/html/testsite/c0gusb1/pdo_sql_test.php:83 Stack trace: #0 /var/www/html/testsite/c0gusb1/pdo_sql_test.php(83): PDOStatement->execute() #1 {main} thrown in /var/www/html/testsite/c0gusb1/pdo_sql_test.php on line 83

How do I insert a decimal correctly? I'm thinking that's the problem and I'm at a loss...
Mark BullockQA EngineerCommented:
For decimal values, try converting the decimal to a string value.
$mssql_stmt_11->bindValue(':dec_percentage', strval($dec_percentage), PDO::PARAM_STR);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bruce GustPHP DeveloperAuthor Commented:
Mark!

OK, tried it and it didn't seem to change anything.

Here's my code:

$sql_50 = "CREATE TABLE #tmp_Results
	(
		id	int IDENTITY,
		str_ProjectName varchar(150),
		str_Tool varchar(150), 
		str_FullName varchar(150),
		str_ProjectType varchar(150),
		str_Description varchar(max),
		str_TargetCompletionDate varchar(50),
		dec_PercentageCompleted decimal(10,2) 
	)";
$mssql_stmt_50=$mssql_pdo->query($sql_50);

$project_name="";
$tool_name="Donald's List";
$developer_name="Bruce Gust";
$project_type="Operations";
$description="a very large chunk of Bruce's concentration";
$dec_percentage=.2;

$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (str_ProjectName, str_Tool, str_FullName, str_ProjectType, str_Description, str_TargetCompletionDate, dec_PercentageCompleted) VALUES (':project_name', ':tool_name', ':developer_name', ':project_type', ':description', ':comp_date', ':dec_percentage')");
$mssql_stmt_11->bindValue(':project_name', $project_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':tool_name', $tool_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':developer_name', $developer_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':project_type', $project_type, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':description', $description, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':comp_date', $comp_date, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':dec_percentage', strval($dec_percentage), PDO::PARAM_STR);

$mssql_stmt_11->execute();


echo "done";

Open in new window


What do you think?
Mark BullockQA EngineerCommented:
You could try omitting the PDO::PARAM_STR.
If that doesn't work, try PDO::PARAM_INT
Bruce GustPHP DeveloperAuthor Commented:
BAM!

Here's what worked:

$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (dec_PercentageCompleted) VALUES (:dec_percentage)");

$mssql_stmt_11->bindValue(':dec_percentage', $dec_percentage, PDO::PARAM_INT);

$mssql_stmt_11->execute();

Got rid of the ticks in the insert statement and that did it!

Thanks, Mark!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.