Cannot drop the table 'temp_tbl', because it does not exist or you do not have permission

PHP
1

Hi,

I’m creating a duplicate input button, so that the user can create duplicate content with a different auto_incremented ID.

I have the code below, but get this error.

Error in executing query.
Array ( [0] => Array ( [0] => 42S02 [SQLSTATE] => 42S02 [1] => 3701 [code] => 3701 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table ‘temp_tbl’, because it does not exist or you do not have permission. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table ‘temp_tbl’, because it does not exist or you do not have permission. ) )

if(isset($_POST['btnduplicate']))
{
$sr=$_POST['srno1'];
$flag=$_POST['flag'];
if ($flag==0)
{
$query="CREATE TEMPORARY TABLE temp_tbl SELECT * FROM Intranet WHERE ID = $sr";
$query="UPDATE temp_tbl SET ID = ID + 1";
$query="INSERT INTO Intranet SELECT * FROM temp_tbl";
$query="DROP TABLE temp_tbl";

$res = sqlsrv_query($conn, $query);
if( $res === false ) 
{ 
echo "Error in executing query.</br>"; 
die( print_r( sqlsrv_errors(), true)); 
} 
sqlsrv_free_stmt( $res);
$flag=1;
}
}
2

In this bit

$query="CREATE TEMPORARY TABLE temp_tbl SELECT * FROM Intranet WHERE ID = $sr";
$query="UPDATE temp_tbl SET ID = ID + 1";
$query="INSERT INTO Intranet SELECT * FROM temp_tbl";
$query="DROP TABLE temp_tbl";

$res = sqlsrv_query($conn, $query);

you’re defining $quote from scratch each time, but by the time you come to execute it, it only contains the last definition which is the “drop table”. Because you don’t execute the first three times you define $query, there’s no table to drop.

3

Ah I see, I changed it to this, which might not be the right way and got a new error.

 $query="CREATE TEMPORARY TABLE temp_tbl SELECT * FROM Intranet WHERE ID = $sr UPDATE temp_tbl SET ID = ID + 1 INSERT INTO Intranet SELECT * FROM temp_tbl DROP TABLE temp_tbl";

Error in executing query.
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 343 [code] => 343 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Unknown object type ‘TEMPORARY’ used in a CREATE, DROP, or ALTER statement. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Unknown object type ‘TEMPORARY’ used in a CREATE, DROP, or ALTER statement. ) )

4

Well, I don’t know SQL server but that would imply to me that it’s having trouble with you trying to create a temporary table. One search result suggested that (for SQL Server 2005) you specify a temporary table by prefixing the name with a # symbol.

I don’t know about merging all the queries into one - at the very least I think you’d need some separators between each query so it can parse them properly. What I was really thinking you’d do is call sqlsrv_query() on each query individually - which at least will mean you can check the return from each call for errors and deal with them, which might allow you more flexibility than just knowing that they all failed.

5

Right, ok I think I get you but could you explain what you just said in a code example.

Please

6

I have just had a thought too, this isn’t going to work.

The contract the user decides to duplicate could be the most recent or even from years back, so taking that contract ID and adding 1 to it isn’t going to work, because it might be already used up, which might be the cause of the problem here possibly.

So what I need to do is copy the content of the ID and then duplicate it to the next available ID.

So it has to change.

7

Oh no, sorry I think I have read it wrong, looks ok I think.

8

I’m sort of progressing I think, I took TEMPORARY out and tried it again, and the error moved onto the SELECT part as below,

Error in executing query.
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 156 [code] => 156 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword ‘SELECT’. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword ‘SELECT’. ) )

For

$query="CREATE TABLE temp_tbl SELECT * FROM Intranet WHERE ID=$sr UPDATE temp_tbl SET ID=ID + 1 INSERT INTO Intranet SELECT * FROM temp_tbl DROP TABLE temp_tbl";

But still not sure.

9

Shouldnt there be semicolons between the seperate commands? How does SQLServer do multiqueries?

10

MM, ye good point so tried it by doing this

$query="CREATE TEMPORARY TABLE temp_tbl; SELECT * FROM Intranet WHERE ID=$sr; UPDATE temp_tbl SET ID=ID + 1; INSERT INTO Intranet; SELECT * FROM temp_tbl; DROP TABLE temp_tbl";

Ended up with this error

Error in executing query.
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 343 [code] => 343 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Unknown object type ‘TEMPORARY’ used in a CREATE, DROP, or ALTER statement. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Unknown object type ‘TEMPORARY’ used in a CREATE, DROP, or ALTER statement. ) [1] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102 [code] => 102 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ‘;’. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ‘;’. ) )

11

so it doesnt like the word TEMPORARY; get rid of that… also you’re inserting into Intranet but not telling it what you want to insert. (Too many semicolons?)

12

Also… why do all this, instead of just UPDATE Intranet SET ID = ID +1 WHERE ID = $sr?

13

I think the idea is to duplicate the record rather than to change the ID - would that have that effect, or just change the existing record to have a new value for ID? But as @multichild said, it’s not necessarily safe to assume that ID+1 isn’t already used, once you get to that point. I don’t know SQL (of any flavour) well enough to know whether it’s an easy query to retrieve every field except the ID and insert that into a new record, in one query. I think you can do that by specifying each field individually, but not sure if there’s a way, without a temporary table, to grab all except one value.

14

I… would need an SQLServer expert to answer better, but wouldnt a subselect that doesnt use * but instead enumerated the fields and specified (ID+1) work?

15

Morning guys,

Thanks for the reply’s.

Yes the idea is to be able to duplicate any contract and create it using the next available ID that’s created by auto increment.

I’m back on it today, so if anyone has got any further adice to get this working, that would be great.

I tried taking TEMPORARY away and it created a new error, and I have played with adding the semi colons and that didn’t resolve it either, so still on no mans land at the mo.

I also didn’t think about the fact that I need to duplicate the content but not the ID, so that another issue.

Surely there must be a way to do this, as I have seen supplicate buttons in CMS’s before.

I’ve googled it and it seems that the way I’m doing it works for others.

16

This code below is still work in progress, but am thinking now of going about it this way -

$query="CREATE TEMPORARY TABLE temp_tbl_1 ( `itineraryId` int NOT NULL,`Contract_Number`, `Contract_Status`, `Company_Name`, `Invoice_Street`, `Invoice_City`, `Invoice_State`, `Invoice_Country`, `Invoice_Postcode`, `Invoice_Tax_Code`, `Invoice_Telephone`, `Invoice_Fax`, `Invoice_Contact_Name`, `Invoice_Email`, `Annual_Contract_Value`, `Invoicing_Profile`, `Invoice_Paid_By`, `Currency_of_Contract`, `Contract_Start`, `Contract_End`, `Renewal_Date`, `Contract_Length`, `Quote_Number`, `Site_Name`, `Street`, `City`, `State`, `Country`, `Postcode`, `Type_of_Establishment`, `No_of_Rooms`, `Group_Name`, `Invoice_Value`, `Invoice_Currency`, `Payment_Contact`, `Payment_Contact_Tel`, `Invoice_Frequency`, `Seasonal_or_Full`, `Month_Opens`, `Month_Closes`, `Brandcheck_Frequency`, `Standardscheck_Frequency`, `Roomcheck_Frequency`, `Foodcheck_Frequency`, `Crisischeck_Frequency`, `Poolcheck_Frequency`, `Firecheck_Frequency`, `Aquacheck_Frequency`, `Spacheck_Frequency`, `Safetycheck_Frequency`, `Accesscheck_Frequency`, `Ecocheck_Frequency`, `Supplycheck_Frequency`, `Dinecheck_Frequency`, `Tourcheck_Frequency`, `Training_Days`, `Labcheck_Aqua_Frequency`, `Labcheck_Food_Frequency`, `Labcheck_Pool_Frequency`, `Labcheck_Room_Frequency`, `Legionella_Test_Frequency`, `Additional_Sampling`, `No_of_Modules`, `Principle_Contact`, `Principle_Telephone`, `Preferred_Language`, `Principle_Contact_Job_Title`, `Principle_Contact_Email`, PRIMARY KEY(itineraryId) )";

mysql_query($maketemp, $conn) or die ("Sql error : ".mysql_error());

$inserttemp = "
INSERT INTO temp_table_1
  (`Contract_Number`, `Contract_Status`, `Company_Name`, `Invoice_Street`, `Invoice_City`, `Invoice_State`, `Invoice_Country`, `Invoice_Postcode`, `Invoice_Tax_Code`, `Invoice_Telephone`, `Invoice_Fax`, `Invoice_Contact_Name`, `Invoice_Email`, `Annual_Contract_Value`, `Invoicing_Profile`, `Invoice_Paid_By`, `Currency_of_Contract`, `Contract_Start`, `Contract_End`, `Renewal_Date`, `Contract_Length`, `Quote_Number`, `Site_Name`, `Street`, `City`, `State`, `Country`, `Postcode`, `Type_of_Establishment`, `No_of_Rooms`, `Group_Name`, `Invoice_Value`, `Invoice_Currency`, `Payment_Contact`, `Payment_Contact_Tel`, `Invoice_Frequency`, `Seasonal_or_Full`, `Month_Opens`, `Month_Closes`, `Brandcheck_Frequency`, `Standardscheck_Frequency`, `Roomcheck_Frequency`, `Foodcheck_Frequency`, `Crisischeck_Frequency`, `Poolcheck_Frequency`, `Firecheck_Frequency`, `Aquacheck_Frequency`, `Spacheck_Frequency`, `Safetycheck_Frequency`, `Accesscheck_Frequency`, `Ecocheck_Frequency`, `Supplycheck_Frequency`, `Dinecheck_Frequency`, `Tourcheck_Frequency`, `Training_Days`, `Labcheck_Aqua_Frequency`, `Labcheck_Food_Frequency`, `Labcheck_Pool_Frequency`, `Labcheck_Room_Frequency`, `Legionella_Test_Frequency`, `Additional_Sampling`, `No_of_Modules`, `Principle_Contact`, `Principle_Telephone`, `Preferred_Language`, `Principle_Contact_Job_Title`, `Principle_Contact_Email`) SELECT `ID`, `Contract_Number`, `Contract_Status`, `Company_Name`, `Invoice_Street`, `Invoice_City`, `Invoice_State`, `Invoice_Country`, `Invoice_Postcode`, `Invoice_Tax_Code`, `Invoice_Telephone`, `Invoice_Fax`, `Invoice_Contact_Name`, `Invoice_Email`, `Annual_Contract_Value`, `Invoicing_Profile`, `Invoice_Paid_By`, `Currency_of_Contract`, `Contract_Start`, `Contract_End`, `Renewal_Date`, `Contract_Length`, `Quote_Number`, `Site_Name`, `Street`, `City`, `State`, `Country`, `Postcode`, `Type_of_Establishment`, `No_of_Rooms`, `Group_Name`, `Invoice_Value`, `Invoice_Currency`, `Payment_Contact`, `Payment_Contact_Tel`, `Invoice_Frequency`, `Seasonal_or_Full`, `Month_Opens`, `Month_Closes`, `Brandcheck_Frequency`, `Standardscheck_Frequency`, `Roomcheck_Frequency`, `Foodcheck_Frequency`, `Crisischeck_Frequency`, `Poolcheck_Frequency`, `Firecheck_Frequency`, `Aquacheck_Frequency`, `Spacheck_Frequency`, `Safetycheck_Frequency`, `Accesscheck_Frequency`, `Ecocheck_Frequency`, `Supplycheck_Frequency`, `Dinecheck_Frequency`, `Tourcheck_Frequency`, `Training_Days`, `Labcheck_Aqua_Frequency`, `Labcheck_Food_Frequency`, `Labcheck_Pool_Frequency`, `Labcheck_Room_Frequency`, `Legionella_Test_Frequency`, `Additional_Sampling`, `No_of_Modules`, `Principle_Contact`, `Principle_Telephone`, `Preferred_Language`, `Principle_Contact_Job_Title`, `Principle_Contact_Email`
FROM `Intranet`
WHERE `ID` = $sr;

mysql_query($inserttemp, $conn) or die ("Sql error : ".mysql_error());

Hopefully someone can jump on in now and let me know what they think

17

I think if all you want to do is duplicate a record in a table, why don’t you just do something like this pseudo-code:

$query = "select * from intranet where id = $whatever";
$res = sqlsrv_query($conn, $query);
// now do whatever you need to get the values, not sure about SQL Server access functions
$query = "insert into intranet .. fieldnames but not the id field ..  (values .. list-the-values-you-got-from-the-query ..)";
$res = sqlsrv_query($conn, $query);

but make sure that when you do the insert, you don’t specify the ID field or its value. I don’t see the need for a temporary table here - that just seems like quite a lot of work to me. So to explain the pseudo-code - retrieve all the values in a normal query, then do a second query to insert a new record, minus the ID field (which will cause a new one to be generated), into the same table.

Another way I’ve seen it done is along the lines you started with - create the temporary table based on the result of your select query, but then alter the table to drop the ID field, and then merge the contents back into the original table, whereupon it will receive a new ID. This seems to be the method to use when you don’t want to specify each individual field - if you don’t mind doing that as per your example, I would lose the temporary table. You seem to have switched from SQL server to MySQL on the example code.

18

Droopsnoot’s correct; you cannot say that “ID+1” is the next available ID.
If i created 2 itineraries in your database, ID 1 and 2. And i run this code on ID 1… i’d have 2 ID 2’s now… and thats a problem.

This is what auto_incrementing fields are designed for - automatically assigning an ID that is unique to the table.

19

Iive got the process sorted now I think, but I get an annoying error and I cant work out where its is because of the error, and ive been through every line and cant see the issue.

if(isset($_POST['btnduplicate']))
{
$sr=$_POST['srno1'];
$flag=$_POST['flag'];
if ($flag==0)
{
$query = "select 'ID', 'Contract_Number', 'Contract_Status', 'Company_Name', 'Invoice_Street', 'Invoice_City', 'Invoice_State', 'Invoice_Country', 'Invoice_Postcode', 'Invoice_Tax_Code', 'Invoice_Telephone', 'Invoice_Fax', 'Invoice_Contact_Name', 'Invoice_Email', 'Annual_Contract_Value', 'Invoicing_Profile', 'Invoice_Paid_By', 'Currency_of_Contract', 'Contract_Start', 'Contract_End', 'Renewal_Date', 'Contract_Length', 'Quote_Number', 'Site_Name', 'Street', 'City', 'State', 'Country', 'Postcode', 'Type_of_Establishment', 'No_of_Rooms', 'Group_Name', 'Invoice_Value', 'Invoice_Currency', 'Payment_Contact', 'Payment_Contact_Tel', 'Invoice_Frequency', 'Seasonal_or_Full', 'Month_Opens', 'Month_Closes', 'Brandcheck_Frequency', 'Standardscheck_Frequency', 'Roomcheck_Frequency', 'Foodcheck_Frequency', 'Crisischeck_Frequency', 'Poolcheck_Frequency', 'Firecheck_Frequency', 'Aquacheck_Frequency', 'Spacheck_Frequency', 'Safetycheck_Frequency', 'Accesscheck_Frequency', 'Ecocheck_Frequency', 'Supplycheck_Frequency', 'Dinecheck_Frequency', 'Tourcheck_Frequency', 'Training_Days', 'Labcheck_Aqua_Frequency', 'Labcheck_Food_Frequency', 'Labcheck_Pool_Frequency', 'Labcheck_Room_Frequency', 'Legionella_Test_Frequency', 'Additional_Sampling', 'No_of_Modules', 'Principle_Contact', 'Principle_Telephone', 'Preferred_Language', 'Principle_Contact_Job_Title', 'Principle_Contact_Email' from Intranet where 'ID' = $sr";
$res = sqlsrv_query($conn, $query);
while ($result = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC)) {  
$new1 = $result["Contract_Number"];
$new2 = $result["Contract_Status"];
$new3 = $result["Company_Name"];
$new4 = $result["Invoice_Street"];
$new5 = $result["Invoice_City"];
$new6 = $result["Invoice_State"];
$new7 = $result["Invoice_Country"];
$new8 = $result["Invoice_Postcode"];
$new9 = $result["Invoice_Tax_Code"];
$new10 = $result["Invoice_Telephone"];
$new11 = $result["Invoice_Fax"];
$new12 = $result["Invoice_Contact_Name"];
$new13 = $result["Invoice_Email"];
$new14 = $result["Annual_Contract_Value"];
$new15 = $result["Invoicing_Profile"];
$new16 = $result["Invoice_Paid_By"];
$new17 = $result["Currency_of_Contract"];
$new18 = $result["Contract_Start"];
$new19 = $result["Contract_End"];
$new20 = $result["Renewal_Date"];
$new21 = $result["Contract_Length"];
$new22 = $result["Quote_Number"];
$new23 = $result["Site_Name"];
$new24 = $result["Street"];
$new25 = $result["City"];
$new26 = $result["State"];
$new27 = $result["Country"];
$new28 = $result["Postcode"];
$new29 = $result["Type_of_Establishment"];
$new30 = $result["No_of_Rooms"];
$new31 = $result["Group_Name"];
$new32 = $result["Invoice_Value"];
$new33 = $result["Invoice_Currency"];
$new34 = $result["Payment_Contact"];
$new35 = $result["Payment_Contact_Tel"];
$new36 = $result["Invoice_Frequency"];
$new37 = $result["Seasonal_or_Full"];
$new38 = $result["Month_Opens"];
$new39 = $result["Month_Closes"];
$new40 = $result["Brandcheck_Frequency"];
$new41 = $result["Standardscheck_Frequency"];
$new42 = $result["Roomcheck_Frequency"];
$new43 = $result["Foodcheck_Frequency"];
$new44 = $result["Crisischeck_Frequency"];
$new45 = $result["Poolcheck_Frequency"];
$new46 = $result["Firecheck_Frequency"];
$new47 = $result["Aquacheck_Frequency"];
$new48 = $result["Spacheck_Frequency"];
$new49 = $result["Safetycheck_Frequency"];
$new50 = $result["Accesscheck_Frequency"];
$new51 = $result["Ecocheck_Frequency"];
$new52 = $result["Supplycheck_Frequency"];
$new53 = $result["Dinecheck_Frequency"];
$new54 = $result["Tourcheck_Frequency"];
$new55 = $result["Training_Days"];
$new56 = $result["Labcheck_Aqua_Frequency"];
$new57 = $result["Labcheck_Food_Frequency"];
$new58 = $result["Labcheck_Pool_Frequency"];
$new59 = $result["Labcheck_Room_Frequency"];
$new60 = $result["Legionella_Test_Frequency"];
$new61 = $result["Additional_Sampling"];
$new62 = $result["No_of_Modules"];
$new63 = $result["Principle_Contact"];
$new64 = $result["Principle_Telephone"];
$new65 = $result["Preferred_Language"];
$new66 = $result["Principle_Contact_Job_Title"];
$new67 = $result["Principle_Contact_Email"];
}

 $query2 = "insert into Intranet (Contract_Number,Contract_Status,Company_Name,Invoice_Street,Invoice_City,Invoice_State,Invoice_Country,Invoice_Postcode,Invoice_Tax_Code,Invoice_Telephone,Invoice_Fax,Invoice_Contact_Name,Invoice_Email,Annual_Contract_Value,Invoicing_Profile,Invoice_Paid_By,Currency_of_Contract,Contract_Start,Contract_End,Renewal_Date,Contract_Length,Quote_Number,Site_Name,Street,City,State,Country,Postcode,Type_of_Establishment,No_of_Rooms,Group_Name,Invoice_Value,Invoice_Currency,Payment_Contact,Payment_Contact_Tel,Invoice_Frequency,Seasonal_or_Full,Month_Opens,Month_Closes,Brandcheck_Frequency,Standardscheck_Frequency,Roomcheck_Frequency,Foodcheck_Frequency,Crisischeck_Frequency,Poolcheck_Frequency,Firecheck_Frequency,Aquacheck_Frequency,Spacheck_Frequency,Safetycheck_Frequency,Accesscheck_Frequency,Ecocheck_Frequency,Supplycheck_Frequency,Dinecheck_Frequency,Tourcheck_Frequency,Training_Days,Labcheck_Aqua_Frequency,Labcheck_Food_Frequency,Labcheck_Pool_Frequency,Labcheck_Room_Frequency,Legionella_Test_Frequency,Additional_Sampling,No_of_Modules,Principle_Contact,Principle_Telephone,Preferred_Language,Principle_Contact_Job_Title,Principle_Contact_Email)". 
"VALUES($new1,$new2,$new3,$new4,$new5,$new6,$new7,$new8,$new9,$new10,$new11,$new12,$new13,$new14,$new15,$new16,$new17,$new18,$new19,$new20,$new21,$new22,$new23,$new24,$new25,$new26,$new27,$new28,$new29,$new30,$new31,$new32,$new33,$new34,$new35,$new36,$new37,$new38,$new39,$new40,$new41,$new42,$new43,$new44,$new45,$new46,$new47,$new48,$new49,$new50,$new51,$new52,$new53,$new54,$new55,$new56,$new57,$new58,$new59,$new60,$new61,$new62,$new63,$new64,$new65,$new66,$new67)";
 $res2 = sqlsrv_query($conn, $query2);	
	
if( $res2 === false ) 
{ 
echo "Error in executing query.</br>"; 
die( print_r( sqlsrv_errors(), true)); 
} 
sqlsrv_free_stmt( $res2);
$flag=1;
}
}

This is the error

Error in executing query.
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102 [code] => 102 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ‘,’. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ‘,’. ) )

20

Assuming that error occurs on the second query (you don’t check for errors on the first, so it’s probably the case) my guess would be that there’s something in the data that causes the problem. Look for commas, quotes etc. that you don’t seem to be handling anywhere.

Also I would think you could simplify the first query down to

$query = "select * from Intranet where 'ID' = $sr";

I can’t see a need to specify every field individually there for a single record return.

Once you have it working there’s probably a way you can iterate through each field name (the index part of the $result array) and its value to dynamically produce the insert query. I suspect that for a database table of this number of fields, this is probably the reason that you’d use a temporary table rather than having to specify each individual column name.