none
[PHP] Azure SQL and temporary tables with PHP RRS feed

  • Question

  • I've written these lines of code to create a temporary table and then store some info into it and then get the info out of it. I am writing php to write to a temp table in SQL Azure.

    Creating the table seems ok as the output says that the table was created.

    When I try to put info into it, or take it out, the error is that the table name #salcalc   is invalid.

    Can someone shed some light on this?

    Thanks.

    <?php include'..\menu.shtml';include'..\1_database_credential.php';print("start");print("<br>");$maketemp="CREATE TABLE [#salcalc] ( [Id] int NOT NULL, [divfun] varchar(30) NULL, [month] int NULL, [baseamount] money NULL, [eiamount] money NULL, [cppamount] money NULL PRIMARY KEY (Id) )";$stmtmaketemp=sqlsrv_prepare($conna,$maketemp); sqlsrv_execute($stmtmaketemp);if(sqlsrv_execute($stmtmaketemp)){print("table created.\n");}else{print("table not created.\n");die(print_r(sqlsrv_errors(),true));}print("<br>");$sql="INSERT INTO #salcalc (Id, divfun, month, baseamount, eiamount, cppamount) VALUES (1,'happy',2,123,234,345)";$stmt=sqlsrv_prepare($conna,$sql); sqlsrv_execute($stmt);if(sqlsrv_execute($stmt)){print("Record Updated.\n");}else{print("NOT UPDATED.\n");die(print_r(sqlsrv_errors(),true));}print("<br>");$sql2="SELECT * FROM #salcalc";$stmt2=sqlsrv_query($conna,$sql2);if($stmt2===false){die(print_r(SQLSRV_ERRORS(),true));}while($row=sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)){$id=$row['Id'];$divfun=$row['divfun'];$month=$row['month'];$baseamount=$row['baseamount'];$eiamount=$row['eiamount'];$cppamount=$row['cppamount'];print("$id");print("$divfun");print ("$month");print("$baseamount");print("$eiamount");print("$cppamount");}?>

    it seems as though the table is created successfully


    Wednesday, July 29, 2015 9:24 PM

Answers

  • It didn't work. But instead of creating a temp table, I just created the table and dropped it once I was done with it.

    Thanks for all of your help!

    Thursday, July 30, 2015 5:26 PM

All replies

  • Hi,

    Please try the following code, try to put SQL statements into one long string and run it using one call to sqlsrv_query, I would also suggest you have a look at this thread:http://stackoverflow.com/questions/30893902/query-in-php-fails-to-see-temp-table-in-sql-server-if-parameter-binding-is-used, " When you prepare an SQL statement with parameters PHP eventually calls sp_executesql (most likely, though I didn't trace it). And you get this documented behavior - a temp table is created inside this stored procedure as part of the query and is immediately dropped when sp_executesql returns. When you run an SQL statement without parameters PHP sends it to the server as is without using sp_executesql." 

    $conn = sqlsrv_connect($serverName, $connectionOptions);
    if($conn)
    {
    	
    	 $maketemp="CREATE TABLE [#salcalc] (
                [Id] int NOT NULL,
                [divfun] varchar(30) NULL,
                [month] int NULL,
                [baseamount] money NULL,
                [eiamount] money NULL,
    	        [cppamount] money NULL
                PRIMARY KEY (Id)
                )
    			";
    	 
    	 $stmt_create = sqlsrv_query($conn, $maketemp);
    	 if( $stmt_create === false )
    	 {
    	 	echo "CREATE TABLE failed\n";
    	 	die( print_r( sqlsrv_errors(), true));
    	 }
    	 else
    	 {
    	 	echo "CREATE TABLE result set:\n";
    	 	while ($row = sqlsrv_fetch_array($stmt_create))
    	 	{
    	 		var_dump($row);
    	 	}
    	 }
    	 sqlsrv_free_stmt($stmt_create);
      	print("<br>");
    	$sql="INSERT INTO #salcalc (Id, divfun, month, baseamount, eiamount, cppamount) VALUES (69661,'happy',2,123,234,345)";
    	$stmt=sqlsrv_prepare($conn,$sql);
    	$suss=sqlsrv_execute($stmt);
    	if($suss){print("Record Updated.\n");}else{print("NOT UPDATED.\n");die(print_r(sqlsrv_errors(),true));}
    	print("<br>");   

    Best Regards,

    Jambor


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.






    Thursday, July 30, 2015 7:24 AM
    Moderator
  • It didn't work. But instead of creating a temp table, I just created the table and dropped it once I was done with it.

    Thanks for all of your help!

    Thursday, July 30, 2015 5:26 PM