stored pro

[Guest]

hi i have a mysql stored procedure and i get an error when i try and call it, using

<cfstoredproc procedure = "sp_test23" dataSource = "#application.ds#">
</cfstoredproc>

<cfdump var="sp_test23">


any ideas why i would be getting this error

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.

[cf_dev2]

That code doesn't create any variables.

If the procedure returns a resultset use CFPROCRESULT. If it uses output variables, use cfprocparam type="OUT"

[Guest]

ok,

the stored procedure is

create procedure sp_test()
select * from s_table;

so how would i call all columns?

[cf_dev2]

CFPROCRESULT returns a query object. You can loop through it like a regular cfquery or access the column names via ColumnList variable: #yourQueryName.ColumnList#

[Guest]

ok so would this code work?

where does the RS1 come from ?

<cfstoredproc procedure = "sp_test" dataSource = "#application.ds#">
<cfprocresult name = RS1>
</cfstoredproc>

<cfdump var="RS1">

[cf_dev2]

RS1 is the name of the resultset (query) variable. The name should be in quotes.

<cfstoredproc procedure = "sp_test" dataSource = "#application.ds#">
<cfprocresult name = "RS1">
</cfstoredproc>

You must # signs around the cfdump variable.
<cfdump var="#RS1#">


That's the typical syntax for ms sql. Theoretically it should work for mysql IF the db version and driver support stored procedures.

Edit - code correction

[Guest]

ok i have tried that but i get this error.

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.

[cf_dev2]

Did you put quotes around the resultset name?

<cfprocresult name = "RS1">

[Guest]

ok yes changed that and i get

Variable RS1 is undefined

[cf_dev2]

>> Variable RS1 is undefined
On which line? The cfdump?

Originally the procedure name was sp_test123 and now its sp_test. Are you running the correct stored procedure? Does it return results when you run it in mysql?

[Guest]

no on this line

<cfprocresult name = "#RS1#">


any idea?

many thanks for this

[cf_dev2]

>>> <cfprocresult name = "#RS1#">
Here "RS1" is a constant. So don't put # signs around it. Try this:

<cfstoredproc procedure = "sp_test" dataSource = "#application.ds#">
<cfprocresult name = "RS1">
</cfstoredproc>

<cfdump var="#RS1#">

[Guest]

no good i still get this error

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.

[cf_dev2]

Did you copy the code in my last example exactly?

[Guest]

yes i did

[cf_dev2]

>> Originally the procedure name was sp_test123 and now its sp_test.

And are you certain you're running the correct procedure? Does it return a resultset when you run it in mysql?

[Guest]

its def sp_test i ran a show procedure and it showed up in mysql

[cf_dev2]

That's not enough. Run it in mysql. Does it return a resultset?

[Guest]

ok this is the error in mysql

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 98
STR: </
SQL: <cfstoredproc procedure = "sp_test" dataSource = "#application.ds#">
<cfprocresult name = "RS1">
</cfstoredproc>

<cfdump var="#RS1#">

[cf_dev2]

No. I mean run the procedure in mysql (command line)

mysql> call sp_test();

[Guest]

i cant its an external web server

[cf_dev2]

Sorry I have to go.

Ok. Its a remote db. I'm not seeing why can't you connect to it. But I tested your procedure with CF and it works with mysql5 and connector/j 5 driver. So it suggests you may be using different procedure code.

MYSQL:
create procedure sp_test()
select * from stable;

CF:
<cfstoredproc procedure="sp_test" datasource="MySQL">
<cfprocresult name="RS1">
</cfstoredproc>
<cfdump var="#RS1#">

Good luck

[Chuck1411]

You still aught to be able to do what he suggested using a <cfquery>

IE:
<cfquery name="somename" datasource="yourdatasource">
call sp_test();
</cfquery>

<cfdump var="#somename#">