Bug #39204 Crystal Report N mySQL Stored Procedure
Submitted: 3 Sep 2008 0:52 Modified: 15 Sep 2008 4:46
Reporter: Low Gee Leng Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:mySQL 5.1 & MyODBC 5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: Incorrect no of arguments mysql in crystal reports

[3 Sep 2008 0:52] Low Gee Leng
Description:
Hi,

  I got this error when i tried to connect crystal report to mysql stored procedure.
  I create a mySQL ODBC Datasource Name. I used crystal report to connect to the datasource name and select the stored procedure in mySQL database.  I prompt me the following error message :-

    ODBC error:[Mysql][ODBC 5.1 Driver][mysqld-5.1.26-rc-community]Incorrect number of arguments for PROCEDURE mynetwin.SEL_IREG; expected 3, got 0.

   The stored procedure name call SEL_IREG and it works fine in other database like MS SQL and etc.

   Please help and very urgent...and i have tried this a couple of month.

   my email is gilbert_low@yahoo.com

   Thank you.

   Regards
   gilbert

How to repeat:
1) create a simple create a stored procedure with one parameter at least
2) use crystal report and to select the stored procedure.
[3 Sep 2008 4:22] Valeriy Kravchuk
Thank you for a problem report. Please, send sample procedure that demonstrates this behaviour. What exact MyODBC version, 5.1.x, do you use?
[3 Sep 2008 10:42] Low Gee Leng
Hi,
  The error message is :-
      ODBC error:[Mysql][ODBC 5.1Driver][mysqld-5.1.26-rc-community]Incorrect number of arguments for PROCEDURE mynetwin.SEL_IREG; expected 3, got 0.

  I using the mysql ODBC 5.1 as mentioned in the error message.  You could download from the link below :-

  http://dev.mysql.com/downloads/connector/odbc/5.1.html

  and mysql database version is 5.1.26-rc community server...which is version 5.1 in short.

  mynetwin.SEL_IREG stand for :-
  mynetwin is mysql database name and SEL_IREG is stored procedure name in the mynetwin database.

  expected 3, got 0. stand for :-
  3 arguments/parameter in SEL_IREG but crystal report un-able to detect it, got 0.
  At far as i known, crystal report below version 10 got this problem.  Not sure about crystal report version 11.

  You could use any create any stored procedure for re-produce the error provieded that :-
  a) the stored procedure must at one argument/parameter.  for example :-
     
     DELIMITER $$

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

     DELIMITER ;

     In this case the error will be expected 3, got 0.

   Thank you for your keen interest of helping me.

   Thank again.

   This problem is ready making me feel sick.

   Regards
   Gilbert
[3 Sep 2008 10:46] Low Gee Leng
sorry, the stored procedure must at least one argument/parameter and you could use any stored procedure for testing or create a new stored procedure.  Or your could use the sample stored procedure given sps_test.  Thank you.
[3 Sep 2008 10:48] Low Gee Leng
We hope that crystal report and mysql look into this problem and help me.  Thanks.
[3 Sep 2008 14:46] Tonci Grgin
Hi Low.

I will need much more info from you before proceeding:
  - ODBC trace file attached here. See our manual for details.
  - Relevant part of general query log produced by MySQL server at the time error occurs.
  - Complete and detailed version of CR.
  - Version of c/ODBC 5.1
[4 Sep 2008 4:59] Low Gee Leng
Hi,
  
  I hope that the following information will help :-

  • 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 server version
    MySQL 5.1.26-rc-community via TCP/IP 

  • Connector/ODBC log file
    SQL.log refer to attachments.

  • 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
     Version 9.2.2.634

   Please let me know if any information you need.

   Thank alot.

   Regards
   Gilbert
[4 Sep 2008 6:11] Low Gee Leng
I have attached the sql log for odbc trace file for your reference.  If you have not received, please let me know.  Thanks.
[8 Sep 2008 1:52] Low Gee Leng
Hi Tonci Grgin,

   Please help and awaiting for your assistance.  Please let me know if you need more information.  Thanks.

   Please everyone, anyone...please help me.

   Thank you very much.

   Regards
   Gilbert
[10 Sep 2008 11:33] Low Gee Leng
Hi everyone,

   Please help...still facing the same problem.

   I have tried on crystal report 2008, crystal report 7, 9 and 10.  All prompt me the same error message : incorrect of number of argument : xxxxx stored procedure name, expect no of arguments ; got 0.

   I use crystal report bind directly to myODBC version 5 to stored procedure. Please help if anyone have know the way of solving this problem. 
   
   So far i have search from internet for answer but only said that call stored procedure name in the crystal reports. But how to do this.  Appeciate that if anyone can show me step by step of the solutions.

   This problem is dragging me for more than a month.  

   We are report writer using crystal report to bind with table, stored procedure via myodbc.

   Wish that someone can assist me and any patches from mysql or crystal reports to fixed this problem.

   Thank you.
  
   Regards
   Gilbert
[13 Sep 2008 19:07] Tonci Grgin
Hi Low. This is in fact duplicate of Bug#36162. Please see my comments there.
[15 Sep 2008 4:46] Low Gee Leng
Hi Tonci Grgin,

   refer to subject Can't use stored procedures in Crystal Reports 2008.

   Ok.. as refer to duplicate Bug#36162, we have also download the mySQL 6 for our development.  It's stil fail to connect stored procedure to crystal reports and we also suspect on myODBC fail due to sqlBindParameter. Please check. 

   Please make it support in mySQL 6 at least else when can we expecting the bugs fixed ? as we are in the mid of migrating our ERP application to your database mySQL.

   Thank you.

   Regards
   Gilbert
[15 Sep 2008 6:21] Tonci Grgin
Low, we have still to see how to implement new metadata available in MySQL 6.x. The talk on subject is scheduled for this week at DevCon. I can not speculate on Connectors team decision but I hope this gets fixed soon.

Thanks for your interest in MySQL.
[7 Nov 2008 11:47] Alex Brown
Hi Low,

I have had the same problem but have found a bit of a work around from within Crystal...

Within the Database Expert under your connection there should be AddCommand. When you add this you can pass in your sql "call sp_testcrystal('param1','param2')"

Once this is done you will be able to expand the sp as you would a table, and using the select expert you can filter for the results you want.

Hope this helps
Alex
[7 Nov 2008 11:56] Tonci Grgin
Alex, you are right, thanks for sharing workaround with others.

Now, c/ODBC just passes "CALL ..." to MySQL server for execution so it will always work that way. We are also looking into supporting "?=" syntax now.