Professional Web Applications Themes

Stored Procedures and MySQL 5 - Coldfusion Database Access

I've written a stored procedure in MySQL 5 with CF MX 6.1 and am struggling to get it working ok. In MySQL, it compiles and runs just fine but executing it via CFSTOREDPROC always just returns a "java.lang.NullPointerException" error. My CF is: <cfstoredproc procedure="spGetActiveVenues" datasource="#application.datasource#"> <cfprocresult name="qSearch" resultset="1"> </cfstoredproc> <cfdump var="#qSearch#"> And my simplfieid SP is: CREATE PROCEDURE spGetActiveVenues() AS BEGIN select * from mytable; END help please! ta Jon...

  1. #1

    Default Stored Procedures and MySQL 5

    I've written a stored procedure in MySQL 5 with CF MX 6.1 and am struggling to
    get it working ok. In MySQL, it compiles and runs just fine but executing it
    via CFSTOREDPROC always just returns a "java.lang.NullPointerException" error.

    My CF is:
    <cfstoredproc procedure="spGetActiveVenues"
    datasource="#application.datasource#">
    <cfprocresult name="qSearch" resultset="1">
    </cfstoredproc>
    <cfdump var="#qSearch#">

    And my simplfieid SP is:

    CREATE PROCEDURE spGetActiveVenues()
    AS
    BEGIN
    select * from mytable;
    END

    help please!

    ta
    Jon

    Jon Guest

  2. #2

    Default Re: Stored Procedures and MySQL 5

    I am having the same problem and I'm just trying to return an integer, not a resultset. Did you ever figure it out? Thanks.
    fgwenger Guest

  3. #3

    Default Re: Stored Procedures and MySQL 5

    I tried it with CF7.0.1, MySQL 5.0.21, and Connector/J 3.1.14 on Windows.

    I get the MySQL error You have an error in your SQL syntax.

    The problem seems to be a MySQL JDBC driver bug when a stored procedure has no
    arguments. I could work around the problem two ways:

    workaround #1: Define the procedure to take one useless argument (MySQL
    didn't require the AS keyword when I definied my procedure)


    delimiter $
    CREATE PROCEDURE spGetActiveVenues(IN x INT)
    BEGIN
    select * from mytable;
    END
    $
    delimiter ;

    Then call it with a (superfluous) argument:

    <cfprocparam cfsqltype="CF_SQL_INTEGER" value="0">

    workaround #2: Add a pair of parenthesis to the procedure name in the
    cfstoredproc tag.

    <cfstoredproc procedure="spGetActiveVenues()"
    datasource="#application.datasource#">


    These both worked for me, but #2 seems too kludgy to really consider. It will
    probably break once MySQL fixes their driver.

    -tom-

    TomD_2 Guest

Similar Threads

  1. Replies: 1
    Last Post: December 4th, 11:33 AM
  2. Troubles in stored procedures (mySQL) in CFMX7
    By Euripide in forum Coldfusion Database Access
    Replies: 13
    Last Post: February 24th, 04:02 PM
  3. Replies: 7
    Last Post: January 31st, 05:24 PM
  4. Stored Procedures mySQl 5, CFMX 6.1
    By WeeJames in forum Coldfusion Database Access
    Replies: 3
    Last Post: December 20th, 07:00 PM
  5. cf 7 and mysql 5 stored procedures and temporary tables
    By gnurulz in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 02:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139