Page 1 of 1

mssql_free_result()

Posted: Tue May 16, 2017 11:37 pm
by ptmi1047
Hi,

I created a trigger to execute stored procedure (update).

$query = "execute SP_UpdateBazaarSeq '$Store','$ReqDate'";
$result = executeQuery($query, $db);
Code: Select all
--Stored Procedure
IF EXISTS (SELECT store_code FROM [MDS]..ms_Bazaar_Seq
						WHERE store_code = @store_code and year = @year )
		BEGIN
			
			SET @Exists = 1

			CREATE TABLE [MDS]..temp_ms_Bazaar_Seq1(
				[store_code] [char](3) NOT NULL,
				[year] [char](4) NOT NULL,
				[sequence] [int]NOT NULL
			)

			INSERT INTO [MDS]..temp_ms_Bazaar_Seq1
			SELECT TOP 1 store_code, year, sequence from [MDS]..ms_Bazaar_Seq where store_code = @store_code and year = @year order by sequence DESC  

			UPDATE [MDS]..ms_Bazaar_Seq
			SET sequence = b.sequence + 1
			FROM [MDS]..ms_Bazaar_Seq a INNER JOIN [MDS]..temp_ms_Bazaar_Seq1 b
			ON a.store_code = b.store_code and a.year = b.year
			WHERE a.store_code = @store_code
			AND a.year = @year

			DROP TABLE [MDS]..temp_ms_Bazaar_Seq1

		END		
		
		ELSE
		BEGIN
            SET @Exists = 0
		END
 
		RETURN @Exists	
When I debug, I got error : mssql_free_result() expects parameter 1 to be resource, boolean given. How to solved this error.

Thank you.

Re: mssql_free_result()

Posted: Wed May 17, 2017 3:20 am
by mishika
Hello,

Which database are you using? MS SQL or MY SQL

If you are using MY SQL, the code in the trigger to execute the trigger should be like the following:
Code: Select all
$Store = //appropriate value;
$ReqDate = //appropriate value;
@@sql = "CALL SP_UpdateBazaarSeq($Store ,$ReqDate)";
@@result = mysql_query(@@sql);
This code works for me to successfully execute a procedure in MY SQL.
Also, you can check the values of @@sql and @@result in the debug section.

If you are using MS SQL, you can try the following code:
Code: Select all
$Store = //appropriate value;
$ReqDate = //appropriate value;
@@sql = "EXEC SP_UpdateBazaarSeq $Store ,$ReqDate";
@@result = mssql_query(@@sql);
I don't have the MS SQL database, so I have not tested this code. Please try this and let me know if this solves your problem.
Also, I think the line of code IF EXISTS in your Procedure must be written inside BEGIN section.

Hope this helps

Best Regards
Mishika

Re: mssql_free_result()

Posted: Thu May 18, 2017 1:43 am
by ptmi1047
Hi Mishika,

I'm using MSSQL and I already tried your code. I got another error "mssql_query() expects parameter 2 to be resource, string given".
The value of @@result is Array(). I think the issue is in Array whilst it should be a parameter (var/int).