Stored Procedure (mysql) fails with "can't return a result set in the given context"

Im new to mysql stored procedures, so bare with me. But im trying to get this SP to return (leave) if some conditions fails and so forth.

This code validates and it saves the procedure, but when I call the procedure with:

CALL ACH_Deposit(30027616,3300012003,200.00,"USD", "127.0.0.1")

It fails with error: "Procedure can't return a result set in the given context"

Does anyone have any idea on what the error is?

Procedure code:

CREATE DEFINER=`redpass_web_urs`@`%` PROCEDURE `ACH_Deposit`( IN __Account_ID BIGINT, IN __To_Bank_Account BIGINT, IN __Amount DECIMAL(10,2), IN __Currency CHAR(3), IN __IP_Address VARCHAR(50) ) COMMENT 'Makes a ACH deposit' BEGIN -- Declare Account Parameters DECLARE _Account_Status INT; DECLARE __Transaction_ID INT; DECLARE _Account_Type INT DEFAULT 0; DECLARE _Fee INT; SELECT Account_Status AS _Account_Status, Account_Type AS _Account_Type FROM Account_Users WHERE Account_ID = __Account_ID; main: BEGIN -- Step 1, is account active ? IF _Account_Status <> 1 THEN -- Account must be active (not restricted, closed etc) SELECT Response_Code, Description FROM ResponseCodes WHERE Response_Code = 106; LEAVE main; -- Here we die.. END IF; -- Step 2, Calculate the FEE (Loading Funds with ACH) IF _Account_Type = 1 THEN -- Personal Account SET _Fee = (SELECT Fee_Template_Personal_1 FROM Fees WHERE Fee_Type_ID = __Fee_Type_ID); ELSE -- Business Account SET _Fee = (SELECT Fee_Template_Business_1 FROM Fees WHERE Fee_Type_ID = __Fee_Type_ID); END IF; -- Step 3, Check that Fee is not bigger then the actual amount IF _Fee > __Amount THEN SELECT Response_Code, Description FROM ResponseCodes WHERE Response_Code = 108; LEAVE main; -- Here we die.. END IF; -- If we come here, we can make the transactions INSERT INTO Bank_Transaction (Bank_Account_ID ,Transaction_Type ,Amount ,IP_Address ,Pending) VALUES (__To_Bank_Account ,11 ,__Amount ,__IP_Address ,1); -- Reserverade pengar -- Transaction ID SET __Transaction_ID = (SELECT LAST_INSERT_ID()); -- Deduct the Fee INSERT INTO Bank_Transaction (Bank_Account_ID ,Transaction_Type ,Amount ,Fee_Type_ID ,Fee_Transaction_ID) VALUES (__To_Bank_Account ,4 ,-__Fee ,21 ,__Transaction_ID); END main; SELECT Response_Code, Description, __Transaction_ID AS Transaction_ID FROM ResponseCodes WHERE Response_Code = 1; END

-------------Problems Reply------------

To retrieve multiple resultsets from the stored procs, you should use a client which supports multiple queries.

If you use PHP, use MySQLi extension and call the procedure using mysqli_multi_query.

MySQL extension is only able to retrieve the first recordset returned by the proc. To be able to use ti, you should set CLIENT_MULTI_RESULTS (decimal 131072) in the parameter $client_flags to mysql_connect

Category:mysql Views:1 Time:2011-03-27

Related post

  • Stored procedure with default NULL parameter to return cubed results 2012-04-18

    This is how the stored procedure would be structured: [dbo].[getSp] ( @Color varchar(10) = Null, @Finish varchar(10) = Null, @Height varchar(10) = Null, @Type varchar(10) = Null, @Trim varchar(10) = Null, @Width varchar(10) = Null ) This is how the d

  • Stored Procedure does not and should not return anything results in error 2011-06-02

    I have a stored Procedure that we are using to simply do an insert of a new record into various tables. This Stored Procedure is setup to not return anything only to execute. When I pull it into my DBML it sets it up as a "Void" which I think is corr

  • Stored Procedures, MySQL and PHP 2008-09-23

    The question is a fairly open one. I've been using Stored Procs with MS SQLServer for some time with classic ASP and ASP.net and love them, lots. I have a small hobby project I'm working on and for various reasons have gone the LAMP route. Any hints/

  • ActiveRecord::StatementInvalid (Mysql::Error: PROCEDURE db_name.proc_spName can't return a result set in the given context: 2010-07-12

    I have use activerecord to access data from db. It works fine in localhost but when I tried it on another server, I get the following errors: ActiveRecord::StatementInvalid (Mysql::Error: PROCEDURE db_name.proc_spName can't return a result set in the

  • MYSQL ERROR: PROCEDURE can't return a result set in the given context 2010-07-23

    I am new to Stored procedure in mysql This is the procedure for returning difference of date excluding Weekends, but it returns error #1312 - PROCEDURE blog1.DayCount can't return a result set in the given context This is the procedure DROP PROCEDURE

  • mssql_execute failing with, "stored procedure execution failed" 2011-08-05

    I am currently connecting sucessfully to an SQL database sat on a Windows 2008 using the following query; $result = mssql_query("EXEC dbo.stored_procedure_name @param_level = 2"); I am basing my queries on existing code written in VB / ADO which look

  • PHP / mssql mssql_execute(): stored procedure execution failed 2011-08-08

    This one is a little strange: I have an SP that runs several time over a few hours in the same script. After about 3 hours, it has run 20 times or so, then I get a series of warnings: "mssql_execute(): stored procedure execution failed ..." for the r

  • Can't return a result set in the given context 2009-07-29

    When ever I try to call store procedure in mysql that sends back a result set, it keeps saying me that "can't return a result set in the given context". I've google it and some said it's mysql bug, some said you should change your mysqli driver and .

  • Return variable from stored procedure MySQL 2009-11-19

    How can I return a declared string like ( lastInsertId ) from my MySQL stored procedure and out? It's really annoying I can't return error messegts, complate messages and more out to my code in PHP5. I hope somebody can help me here, I have search Go

  • SQL Server stored procedure calls multiple stored procedure and fails to return result 2012-01-18

    I have a stored procedure (USA2YearPremiumAnalysis) that calls two variations of another stored procedure (2YearPremiumAnalysisByState) with different variables. So the code for SP-Parent looks like this: CREATE PROCEDURE USA2YearPremiumAnalysis @Con

  • Cannot get the return values of stored procedure mysql 2012-04-08

    I want to get the out parameter of stored procedure using Zend framework Here's the procedure DELIMITER // CREATE procedure getAllUsers( out sysUsers VarChar(50) ) BEGIN DECLARE usersCursor CURSOR FOR SELECT username FROM SYSTEM_USERS; OPEN usersCurs

  • linq to sql stored procedure call fails 2011-01-26

    I have recursive stored procedure on SQL Server. I'm using Linq-to-SQL generated classes, and drag & drop procedure to this class. Other procedures are working fine, but this procedures fails with exception: "System.Void" not allowed return type.

  • sql stored procedure execution failing because of permissions on tables 2010-06-23

    Let me explain the context first. There are two databases sitting on two distinct servers on the same network. There is a stored procedure which copies data from a table in one database to a table in the other database (the two databases have same st

  • SQL Server 2005 Stored Procedure Using Table Variable Doesn't Return ResultSet to VB6 App 2010-07-02

    I have a stored procedure which selects a single field from a single row (based on a conditional statement) and stores it in a local variable. Based on the value of this field I update the same field in the same row of the table (using the same condi

  • error calling nested stored procedures mysql 2010-08-28

    I m calling a stored procedure inside another stored procedure in MySQL The error i m getting on calling simply using Mysql administrator call sp_update_back_image(2, 3); is: - OUT or INOUT argument 2 for routine void.sp_sel_options_id is not a varia

  • Test if Table has at least certain Stored Procedure, else fail test c# 2010-09-28

    This test checks if only one SP ends with Insert, Load, or Save, then it's successful. //Query to find SPs for a certain Table name string check_SP = "SELECT ROUTINE_NAME, ROUTINE_DEFINITION " + "FROM INFORMATION_SCHEMA.ROUTINES " + "WHERE ROUTINE_DE

  • stored procedure + mysql question 2011-01-06

    I have created a stored procedure to delete data from multiple tables. my work flow as follows I'm using mysql 5.0 and running on linux table dependencies as follows table C depending on table B table B depending on table A I want to delete a record

  • how to display records in PHP from stored procedure Mysql 2011-01-25

    I m creating stored procedure with multiple select statement,as shows below: DELIMITER $$ DROP PROCEDURE IF EXISTS `testsp` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `testsp`( ) BEGIN select area_id,areaname from area; select loc_id,locname from

  • iSeries stored procedure calling RPG program doesn't return a value to the program 2011-03-08

    I have a stored procedure that calls an SQLRPGLE program. The program is running fine as I have verified in the debugger and sends the return value as expected. Instead what is returned when looking at cmd.Parameters["@ISMATCH"].Value = {}. I am gues

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.088 (s). 11 q(s)