MySQL Bugs: #36162: Can't use stored procedures in Crystal Reports 2008

Bug #36162	Can't use stored procedures in Crystal Reports 2008
Submitted:	16 Apr 2008 23:26	Modified:	24 Apr 2008 7:17
Reporter:	John Burrows	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows (2003 Server)
Assigned to:		CPU Architecture:	Any
Tags:	crystal reports, stored procedure

Description:
I have 

Windows 2003 Server
Crystal Reports 2008 (I haven't tried other versions)
MySQL 5.1 (Windows)
mySQLODBC 5.1.3 (also did't work in 5.1.2 but different error message)

Try and connect to the stored procedure and you get the error;

Database connector error HY000:[MySQL][ODBC 5.1 Driver][mysql-5.0.51a community-nt-log]incorrect nummber of arguments for PROCEDURE mobile_active_dw.test;expected 1, got 0;[Database Vendor Code 1318]

How to repeat:
I tried very simple procedures and still got this error

Thank you for a problem report. Please, try with a newer Connector/ODBC version, 5.1.4. In case of the same problem, please, send sample stored procedure code that demonstrates the behaviour described.

OK I have updated the connector to 5.1.4 but the issue still arises

My Stored procedure is

DELIMITER |

CREATE PROCEDURE `test` 
(
  IN `@testparam` int
)
  MODIFIES SQL DATA
  DETERMINISTIC
BEGIN
  /* Procedure text */
  select * from carrier_receipts_billed;
END|

DELIMITER ;

John, can you tell me why IN param has leading "at"? Please try with "IN testparam" and inform me of result.

Hi

The @ sign is just a habbit i'm in from sql.  Anyway i changed it so that the procedure is now as below and it still does not work

-- Procedure: test

-- DROP PROCEDURE IF EXISTS `test`;

DELIMITER |

CREATE PROCEDURE `test` 
(
  IN `testparam` int
)
  READS SQL DATA
BEGIN
  /* Procedure text */
  select * from carrier_receipts_billed;
END|

DELIMITER ;

I just discovered that MySQL ODBC driver (version 5.1.4) does not correctly return description of parameters of a stored procedure. Function SQLProcedureColumns returns Success with an information (saying that the server is not able to return this information) and an empty ResultSet. So no way to discover how to call the procedure. may be the problem is here !

Sounds like a maybe but i don't know enough about this stuff to confirm.  Happy to test anything you want though

Guys, thank you for pointing out to SQLProcedureColumns.

This is a known server problem (check for yourself that there's no information requested by SQLProcedureColumns available in `information_schema`.`ROUTINES` table) and it's been decided that no more "workarounds" will be added to MyODBC. In any case, there is *no* good workaround as SHOW ... requires privileges and a parser which makes that approach error-prone.

This shows one more thing, when filing ODBC bugs please include DM trace file as described in manual.

The error you are seeing comes from dummy function in driver:
SQLProcedures:
	In:	StatementHandle = 0x00851FE0, CatalogName = "test", NameLength1 = 4, SchemaName = "test", NameLength2 = 4, ProcName = SQL_NULL_HANDLE, NameLength3 = 0
	Return:	SQL_SUCCESS=0

Get Data All:
"PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME", "NUM_INPUT_PARAMS", "NUM_OUTPUT_PARAMS", "NUM_RESULT_SETS", "REMARKS", "PROCEDURE_TYPE"
"test", <Null>, "test35199_function", <Null>, <Null>, <Null>, "This function provides lookup/insert/update behavior for table t", 2
"test", <Null>, "test_function", <Null>, <Null>, <Null>, "This function provides lookup/insert/update behavior for table t", 2
"test", <Null>, "test_function35199", <Null>, <Null>, <Null>, "This function provides lookup/insert/update behavior for table t", 2
3 rows fetched from 8 columns.

SQLProcedureColumns:
	In:	StatementHandle = 0x00851FE0, CatalogName = "test", NameLength1 = 4, SchemaName = "test", NameLength2 = 4,ProcName = "test_function35199", NameLength3 = 18, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
	Return:	SQL_SUCCESS_WITH_INFO=1
		stmt:		szSqlState = "01000", *pfNativeError = 4000, *pcbErrorMsg = 105, *ColumnNumber = -2, *RowNumber = -2
										MessageText = "[MySQL][ODBC 5.1 Driver][mysqld-5.0.58-pb1083-log]MySQL server does not provide the requested information"

Thanks for your interest in MySQL.

Sorry guys excuse my ignorance but are you saying that the odbc driver does not return enough information to discover the number of parameters and hence you cannot use ODBC to run stored procedures on MySQL?  Or are you saying it's specific to the way Crystal Queries for the information about parmaters?

And then are you saying it will stay that way?

Thanks

John, MySQL server returns insufficient metadata to MyODBC. This causes the error you're seeing. There are several workarounds other connectors take, but they are less then perfect (as all workarounds are, by definition) and have many unwanted side effects. This made ODBC team decide not to implement any workaround and wait for server team to change I__S so that it provides all necessary info on stored procedures.

So, it has nothing to do with CR and it will stay like this for some time.

Thanks very much for the clarification.  Disappointing as it is, at least I know where i stand.

Would it be in the next release of MySQL?

Thanks

Fix should be in 6.0 with implementation of I__S.params table I think.

Hi,

  Please fixed the bug in mySQL 6.  (duplicate bug #39204)

  We are in the mid of migrating our ERP solutions to mySQL database.  Stored procedure is useful for us to manipulating the enterprise data and fast way for retrieve a complex data in crystal reports. 

 we  hope mysql can assist us.
  
  Our production environments :-

  • Operating system and version
    Windows XP SP 3.

  • Connector/ODBC version
    MYODBC 5.1

  • ODBC Driver Manager type and version
    MySQL ODBC 5.1 Driver Version 5.01.05.00

  • MySQL community server version 6. (latest version)

  • Simple reproducible sample

    Sample Table zpureqh
  
    CREATE TABLE zpureqh
    (comid varchar(8),
     docno varchar(15),
     lineno integer,
      itmcd varchar(30)
    )

    Sample Stored Procedure

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `mynetwin`.`sps_test` $$
    CREATE PROCEDURE `sps_test`(strcomid varchar(8))
    begin
      select * from zpureqh where comid = strcomid;
    end $$

    DELIMITER ;

   • Crystal Report 2008.

   Thank you.

   Regards
   Gilbert

http://bugs.mysql.com/bug.php?id=70242 duplicate of this one.