MySQL Bugs: #35423: Receiving error 1305 when trying to call a stored procedure from Coldfusion8

Bug #35423	Receiving error 1305 when trying to call a stored procedure from Coldfusion8
Submitted:	19 Mar 2008 3:07	Modified:	22 Apr 2008 16:39
Reporter:	David Price	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.51a	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I have written 75 stored procedures, which all work. I have created another listed below that deos not work when called from coldfusion8 or bluedragon 7.
I have deleted a number of existing sp's to see if it was limitation. 
I can call the SP from the MYSQl Browser and it works, but calling it through the applications fails.
I have tried to rename the SP --- still fails.
I have tried to retype the SP --- still fails.
I have upgraded the MYSQL server and the MYSQL java connector --- still Fails.

I have run out of options.  Please help.

How to repeat:
DELIMITER $$

DROP PROCEDURE IF EXISTS `csoc`.`advsrch` $$
CREATE DEFINER=`csoc_admin`@`%` PROCEDURE `advsrch`(
in inText char(50),
in inOption char(50)
)
begin
  if inOption = 'None' then
    select * from org where orgname=inText and status=1;
  end if;
end $$

DELIMITER ;

Thank you for a problem report. Please, specify the exact vesion of Connector/J used and send the exact text of error message you get.

{ "ER_SP_DOES_NOT_EXIST", 1305 },

maybe wrong database specified?

Valeriy,

 

I have installed Connector/J version - 5.1.6

 

Here is the error return from by Bluedragon server:

 

Type   Database
 
Query Error   FUNCTION csoc.advsrch does not exist
 
Datasource   csoc
 
Native Error Code  1305
 
SQL State   42000
 
SQL   { ? = call advsrch(?,?) }
 
Tag Context   CFIF: Line=26; Column=1
|
+-- CFOUTPUT: Line=27; Column=2
    |
    +-- CFSTOREDPROC: Line=34; Column=2
 
Source Code

32:   <!--- <cfset ErrorFlag = FALSE> --->
33:   <!--- <cftry> --->
34:   <cfstoredproc procedure="advsrch" datasource="#DBName#" username="#DBID#" password="#DBPWD#" debug="Yes" returnCode="Yes">
35:     <cfprocparam type="In" dbvarname="inText" value="#xText#" cfsqltype="CF_SQL_CHAR">
36:     <cfprocparam type="In" dbvarname="inOption" value="#xOption#" cfsqltype="CF_SQL_CHAR">
^ Snippet from underlying CFML source
 
 
  
 

 

 

Here is my Stored Procedure

 

delimiter $$

drop procedure if exists `advsrch` $$

create procedure `advsrch` (

in inText char(50),

in inOption char(50)

)

begin

  if inOption = 'None' then

    select * from org where orgname=inText and status=1;

  end if;

end $$

delimiter;

 

I can load this SP with no errors and even run it from the MySQL Browser window.

 

Thank you for your help!

 

David

David, as c/J has absolutely no known problems calling SP there must be something ColdFusion is doing wrong... Please provide me with relevant part of general query log from server so I can see what ColdFusion actually does. If you're putting some kind of connection string in ColdFusion, paste that too.

David. Are you absolutely sure procedure `advsrch` is in `csoc` database? Can you call it from `csoc` database in cl client?

Tonci, yes! I am sure it is in the database. I can call it from the Mysql Browser client.
I thought it might be my app server but I have two.
I develop in a server called Bluedragon from NewAtlanta. It is a Coldfusion emulator. I have also moved my code to Coldfusion8 and the same problem exists. This is why I contacted you.

I will check the logs and get back to you.

David, ok, let's see what logs have to show.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have had the same issue with ColdFusion 8 calling MySQL SPs and was able to resolve it by removing the returnCode="Yes" attribute from the CFSTOREDPROC tag, or setting it to the default "No".