Calling stored procedure from MysQL 5.0

[ShytKicker]

I am trying to call stored procedures using <cfquery> in Coldfusion MX 7.0, but
I am getting this error:

Error Executing Database Query.
General error: PROCEDURE start.getforums can't return a result set in the
given context

Has anybody successfully called a stored procedure in CF from MySQL 5.0? When
I run the stored procedure from the MySQL Command Line, it works without a
problem. Here is the code I am using:



<cfquery name='query1' datasource='datasource1'>
call getforums();
</cfquery>

[mxstu]

I have run a stored procedure with MX7 / mySQL 5.0.12. Are you sure you're
calling a stored procedure and not a function? From the mySQL docs: "
Statements that return a result set cannot be used within a stored function."

[url]http://dev.mysql.com/doc/mysql/en/create-procedure.html[/url]

[ShytKicker]

Yes, I am sure that I am calling a stored procedure, not a stored function. It
works from the MySQL Command Line "call getforums();", but doesn't work from
Coldfusion.

mxstu, what connector were you using with Coldfusion 7 and MySQL 5? I also
found this, don't know how helpful it will be to me:
[url]http://macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=9[/url]
08525&highlight_key=y&keyword1=mysql%20stored%20pr ocedure

[mxstu]

I'm using the jdbc connector mysql-connector-java-3.1.7-bin.jar

[mxstu]

It also works with the version 3.1.10 connector. There are general
instructions on installing the connector here:
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=6ef0253[/url] .

A few important details about the instructions:
1) Download version 3.1.10 from
[url]http://dev.mysql.com/downloads/connector/j/3.1.html[/url]
2) Extract mysql-connector-java-3.1.10-bin.jar. This is the file referred to
in steps 2 and 3 of the instructions above
3) Make sure there is only (1) version of the connector jar in the
cf_root/WEB-INF/lib directory. If there is more than one version you may
receive a "No suitable driver" error.
5) You must restart the CF server after placing the driver in the
cf_root/WEB-INF/lib directory


After you create and verify the new datasource try running the stored
procedure again:

<cfquery name='query1' datasource='datasource1'>
{ call getforums(); }
</cfquery>

[ShytKicker]

Worked well, thanks, but now I am having a problem now with my old queries,
they are returning this error:

Error Executing Database Query.
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='

I have to convert each table and each column to utf8_general_ci inorder for
the queries to work. Why would it show this error over a JDBC connector?

[ShytKicker]

mxstu, apparently <cfstoredproc> tag works a little different in MySQL 5.0, for
example, this would throw an error:

<cfstoredproc procedure='getOrders' datasource='name' />

Although, instead, you would have to use this to successfully call the MySQL
stored procedure:

<cfstoredproc procedure='getOrders()' datasource='name' />

The returncode attribute also throws an error. Any luck getting those to work
100% perfectly?

[mxstu]

>Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='

See [url]http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html[/url]

>The returncode attribute also throws an error. Any luck getting those to work

100% perfectly?
No, but I haven't used them extensively in this version and I don't really use
return codes. Since mySQL 5.0.12 is still a beta and the CF MX7 docs only list
support for mySQL 3.x and 4.0, I wasn't really expecting perfection ;-) I'll
run a few more tests and let you know what I find.

[ShytKicker]

Yes, I am happy Coldfusion MX 7 and MySQL 5.0.12 Beta even work to begin with!
mxstu, I am getting JDBC errors that I never had with ODBC. Here is the error I
am getting, any thoughts:

[url]http://forums.mysql.com/read.php?39,43138,43138#msg-43138[/url]

[ShytKicker]

mxstu, I don't know if this is JDBC 3.1 problem or MySQL 5.0.12 beta problem,
but when using <cfqueryparam> in the where clause of a SELECT statement, the
selected numeric columns return extrelemy strange values. For instance, this
for me returns 1 for userid:

SELECT userid
FROM table
WHERE userid = 1;

But, this, returns 4294967297 for userid:

SELECT userid
FROM table
WHERE userid = <cfqueryparam value='1'>;

Any ideas?

[mxstu]

First, you need to specify the cfsqltype (integer, varchar, etc).

[ShytKicker]

I did, still nothing. Does it work for you?

[mxstu]

Yes, this is my table and statement

create table myTable (
userId int auto_increment primary key,
username varchar(50)
);

<cfquery name="test" datasource="myDSN">
SELECT userid
FROM mytable
WHERE userid = <cfqueryparam value="1" cfsqltype="cf_sql_integer">;
</cfquery>
<cfdump var="#test#">

[ShytKicker]

Hmmm that worked right. Maybe I should recreate my tables since I created them on MySQL 4.1 via ODBC.

[mxstu]

Could be. I haven't used their ODBC connector for anything. Is the basic table structure the same?

[mxstu]

Originally posted by: ShytKicker
Yes, I am happy Coldfusion MX 7 and MySQL 5.0.12 Beta even work to begin with!
mxstu, I am getting JDBC errors that I never had with ODBC. Here is the error I
am getting, any thoughts:

[url]http://forums.mysql.com/read.php?39,43138,43138#msg-43138[/url]



Can you post the query and value(s) that generated this error? I haven't
experienced it yet with this version.

[ShytKicker]

Basically, if you have a date/time value of "0000-00-00 00:00:00" or a date
value of "0000-00-00" then it will return an error since Java considers those
"not valid date" values.

So I had to change the datetime column default value to "0001-01-01 00:00:00".
JDBC annoys me.

The basic table structure is the same, only alot more info, but same WHERE
clause, same primary key, auto_increment, etc. And the funny thing is, when it
returns data, it returns all the integer fields with the MAXIMUM value for that
datatype. For userid I had mediumint unsigned, and it returned 4294967297

[ShytKicker]

mxstu, the problem still persists! I figured out why, it is because of UNSIGNED
columns. I changed userid to UNSIGNED and now it is returning "4294967300"
instead of "1". Let me know what you get after you change userid to UNSIGNED.

[mxstu]

I get the same thing ("4294967300") when using cfqueryparam. I know I have not
seen any mention of support for "unsigned" in the cfqueryparam docs so far.
The query does work correctly without the cfqueryparam though.

--- works correctly
SELECT userid
FROM mytable
WHERE userid = 1;

[ShytKicker]

UNSIGNED worked with cfqueryparam on MySQL 4.1.x for me when using the myODBC
driver. I don't understand why it wouldn't, all that UNSIGNED means is that
integer column cannot store negatives and can store double the positive total
value.

This is strange.