Troubles in stored procedures (mySQL) in CFMX7

[Euripide]

Hi,

i'm using for the first time stored procedures in MySQL 5.0.18 and as database
client i'm using ColdFusion MX7.

The trouble is that the stored proc doesn't executed, the error message is:

The system has attempted to use an undefined value, which usually indicates a
programming error, either in your code or some system code.
Null Pointers are another name for undefined values.

My code is:

<cfstoredproc procedure="storproc" datasource="#dns#" debug="yes">
<cfprocresult name="RS1">
</cfstoredproc>

And the stored proc's code is:


DELIMITER $$

DROP PROCEDURE IF EXISTS `techinfinity`.`aree` $$
CREATE PROCEDURE `techinfinity`.`aree` ()
BEGIN
select * from aree;
END $$

DELIMITER ;

Thanks to all and sorry for my english!! :)

[paross1]

Was it a typo, or are you trying to use a different name in your CFSTOREDPROC than the declared proc name in MySQL?

<cfstoredproc procedure="techinfinity" datasource="#dns#" debug="yes">

Phil

[Euripide]

Thanks for your reply Phil.

Unfortunately also correcting the code,

<cfstoredproc procedure="techinfinity.aree" datasource="techinfinity"
debug="yes">
<cfprocresult name="RS1">
</cfstoredproc>

the trouble is the same!!

The system has attempted to use an undefined value, which usually indicates a
programming error, either in your code or some system code.
Null Pointers are another name for undefined values. :(

[paross1]

Does your stored procedure work? In other words, does it successfully execute
in MySQL? Try to eliminate the database end before assuming that it is on the
ColdFusion end. (I've never used MySQL, but I do use lots of SPs in Oracle, and
have "played" with some in SQL Server.)

Phil

[Euripide]

In MySQL the storedproc works!

In the mySQL GUI (MySQLQueryBrowser) the statement CALL aree();
returns the correct recordset.
Is in CF that does't work.

[paross1]

and this (without the techinfinity before the aree) doesn't work?

<cfstoredproc procedure="aree" datasource="techinfinity" debug="yes">
<cfprocresult name="RS1">
</cfstoredproc>

Hmm.... Have you tried a different SP, a simple one that takes no parameters,
nor returns any result set? Or, one that returns a single parameter out (via
cfprocparam) instead of an entire result set? In other words, can you call a
stored procedure at all, or is it just a matter of getting an error because of
the attempt to return a result set?

Phil

[Euripide]

Originally posted by: paross1
and this (without the techinfinity before the aree) doesn't work?

<cfstoredproc procedure="aree" datasource="techinfinity" debug="yes">
<cfprocresult name="RS1">
</cfstoredproc>

Hmm.... Have you tried a different SP, a simple one that takes no parameters,
nor returns any result set? Or, one that returns a single parameter out (via
cfprocparam) instead of an entire result set? In other words, can you call a
stored procedure at all, or is it just a matter of getting an error because of
the attempt to return a result set?

Phil

Hi Phil,

simple storedproc, no params nor result set tried on my local host.

DELIMITER $$

DROP PROCEDURE IF EXISTS `techinfinity`.`aree` $$
CREATE PROCEDURE `aree`()
BEGIN
DECLARE x INT;
SET x = 10;
END $$

DELIMITER ;

From CF's template:
<cfstoredproc procedure="aree" datasource="#APPLICATION.DataSource#"
debug="yes">
</cfstoredproc>


SOME ERROR!!! :( I am crazy!!!

[paross1]

Wow, very strange. I'm sorry that I do not have any experience using MySQL,
especially with stored procedures.

One thing that you might look at is in your data source configuration in
ColdFusion Administrator. Under Allowed SQL in your advanced parameters, you
should make sure that Stored Procedures is checked. This is an unlikely
possibility, and doesn't seem to fit the error that you are seeing, but like I
said, I'm not at all experienced with setting up MySQL with CF.

Good luck,
Phil

[Euripide]

Originally posted by: paross1
Wow, very strange. I'm sorry that I do not have any experience using MySQL,
especially with stored procedures.

One thing that you might look at is in your data source configuration in
ColdFusion Administrator. Under Allowed SQL in your advanced parameters, you
should make sure that Stored Procedures is checked. This is an unlikely
possibility, and doesn't seem to fit the error that you are seeing, but like I
said, I'm not at all experienced with setting up MySQL with CF.

You didn't say if you could get good results with CFQUERY, but I assume that
you have successfully connected to the database and returned results.
Otherwise, I would suspect a possible driver problem.

Good luck,
Phil

Very very strange!!!
In CFAdmin under Allowed SQL the stored procedures option is checked!

With <cfquery> tag my application does connect succesfully to db server.
I don't know... i've made a lot of searches on the web but nothing...

[paross1]

Just a SWAG, but could it perhaps be the JDBC driver that you are using?
According to this [url]http://www.macromedia.com/go/tn_19170[/url] ,

All versions of Macromedia ColdFusion MX include a MySQL JDBC Type 4 driver.
However, some users may choose to use more recent MySQL JDBC drivers
distributed by MySQL in order to use newer features of MySQL.

I wonder if "newer features" means stored procedures?

Phil

[Euripide]

Originally posted by: paross1
Just a SWAG, but could it perhaps be the JDBC driver that you are using?
According to this [url]http://www.macromedia.com/go/tn_19170[/url] ,

All versions of Macromedia ColdFusion MX include a MySQL JDBC Type 4 driver.
However, some users may choose to use more recent MySQL JDBC drivers
distributed by MySQL in order to use newer features of MySQL.

I wonder if "newer features" means stored procedures?

Phil


Hi Phil,

and thanks for your help. ;)

I'm reading the tech note and i'll try to update my MySQL JDBC drivers at the
most recent version....

[Euripide]

I've updated my JDBC drivers at 3.1.12 version and stored procs executed
succesfully.. with a little problem ;)

First, the success.

SP 'aree' in MySQL:
DELIMITER $$

DROP PROCEDURE IF EXISTS `techinfinity`.`aree` $$
CREATE PROCEDURE `aree`(out param1 int)
BEGIN
select count(*) into param1 from aree;
END $$

DELIMITER ;

In my CF's template:

<cfstoredproc procedure="aree" datasource="#APPLICATION.DataSource#">
<cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" variable="param1">
</cfstoredproc>
<cfoutput>#param1#</cfoutput>

Result OK.

StoredProc prodotti in MySQL.

DELIMITER $$

DROP PROCEDURE IF EXISTS `techinfinity`.`prodotti` $$
CREATE PROCEDURE `prodotti`()
BEGIN
select * from prodotti;
END $$

DELIMITER ;

In CF:
<cfquery name="q" datasource="#APPLICATION.DataSource#">
{CALL prodotti()}
</cfquery>

<cfdump var="#q#">

Dumping executed successfully.



Now, the problem:
When i call the sp with

<cfstoredproc procedure="prodotti" datasource="#APPLICATION.DataSource#">
<cfprocresult name="prodotti">
</cfstoredproc>

CF show this error:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '' at line 1
SQL {call prodotti}
DATASOURCE techinfinity
VENDORERRORCODE 1064
SQLSTATE 42000


I don't undestand!!

Thanks Phil!

[paross1]

Just another SWAG, but try selecting discrete column names from your table
instead of SELECT *. Since VENDORERRORCODE 1064 equates to a MySQL
ER_PARSE_ERROR error, perhaps there is something in the column names that you
are selecting that it doesn't "like". Interesting that the CFQUERY method
worked but CFPROCRESULT couldn't handle the return result set. Oh, and also,
the ResultSet parameter in the CFPROCRESULT is optional and defaults to 1, but
you might try specifying ResultSet="1", just in case it "thinks" that you are
attempting to return more than one result set.

Like I said, a SWAG and shot in the dark, but I have really haven't a clue why
this is working this way, unless you may be dealing with a MySQL bug too. I
know that it took me quite a while to figure out most of the "weird-nesses" of
calling Oracle stored procedures with CF, especially since every new version of
ColdFusion handled the return of result sets differently. That was lots of fun
having to modify hundreds of SP calls every time I upgraded to a new version of
CF.

Anyway, good luck,
Phil

[Euripide]

Originally posted by: paross1
Just another SWAG, but try selecting discrete column names from your table
instead of SELECT *. Since VENDORERRORCODE 1064 equates to a MySQL
ER_PARSE_ERROR error, perhaps there is something in the column names that you
are selecting that it doesn't "like". Interesting that the CFQUERY method
worked but CFPROCRESULT couldn't handle the return result set. Oh, and also,
the ResultSet parameter in the CFPROCRESULT is optional and defaults to 1, but
you might try specifying ResultSet="1", just in case it "thinks" that you are
attempting to return more than one result set.

Like I said, a SWAG and shot in the dark, but I have really haven't a clue why
this is working this way, unless you may be dealing with a MySQL bug too. I
know that it took me quite a while to figure out most of the "weird-nesses" of
calling Oracle stored procedures with CF, especially since every new version of
ColdFusion handled the return of result sets differently. That was lots of fun
having to modify hundreds of SP calls every time I upgraded to a new version of
CF.

Anyway, good luck,
Phil


Hi Phil,

new SP, i used discrete column names:
DELIMITER $$

DROP PROCEDURE IF EXISTS `techinfinity`.`prodotti` $$
CREATE PROCEDURE `prodotti`()
BEGIN
select nomeprodotto from prodotti;
END $$

DELIMITER ;

but CF give me an exception: Sintax error in mysql.
The cfquery version instead works succesfully...

<cfquery name="result" datasource="#APPLICATION.DataSource#">
CALL prodotti()
</cfquery>
<cfdump var="#result#">

At this point, i'll use a cfquery version... that i can also cache it!