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:

[19 Mar 2008 3:07] David Price
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 ;
[19 Mar 2008 4:12] Valerii Kravchuk
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.
[19 Mar 2008 11:49] Shane Bester
{ "ER_SP_DOES_NOT_EXIST", 1305 },

maybe wrong database specified?
[20 Mar 2008 23:50] David Price
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
[21 Mar 2008 10:48] Tonci Grgin
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.
[21 Mar 2008 10:52] Tonci Grgin
David. Are you absolutely sure procedure `advsrch` is in `csoc` database? Can you call it from `csoc` database in cl client?
[21 Mar 2008 14:45] David Price
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.
[22 Mar 2008 16:39] Tonci Grgin
David, ok, let's see what logs have to show.
[22 Apr 2008 23:00] Bugs System
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".
[8 Feb 2009 11:26] Julian Halliwell
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".