Anyone know how the syntax for using a STORED PROCEEDURE within CF query?

I have a DB2 stored procedure that I am trying to access to do an insert into a DB2 table.

Here is my query:

<CFQUERY datasource="xxxxx" username="xxxxx" password="xxxxx" name="AddRecord">
{CALL CBSTDTF0.CBSPSTAT (
'#Form.statustitle#',
'#Form.curdate#',
'#Form.curtime#'
'#Form.title#'
'#Form.name#'
'#Form.statustext#'
'#Form.textdate#') }
</cfquery>

I am getting the following error:

ODBC Error Code = 37000 (Syntax error or access violation)
[IBM][CLI Driver] CLI0118E Invalid SQL syntax. SQLSTATE=37000


Can anyone help with getting the syntax of this query correct?
Thanks
LVL 1
g118481Asked:
Who is Participating?
 
CFXPERTConnect With a Mentor Commented:
Or
<cfquery name="???" datasource="???">
EXEC CBSTDTF0.CBSPSTAT('#Form.statustitle#',
'#Form.curdate#',
'#Form.curtime#',
'#Form.title#',
'#Form.name#',
'#Form.statustext#',
'#Form.textdate#')
</cfquery>

I dont if this will work either but worth a shot.

KWK
0
 
SewellMCommented:
Not experienced with using DB2, but I am gonna give this a shot because it looks like something I have seen before.

Notice the commas after the first two items.  Notice that there aren't any after that.  Try this:

<CFQUERY datasource="xxxxx" username="xxxxx" password="xxxxx" name="AddRecord">
{CALL CBSTDTF0.CBSPSTAT (
'#Form.statustitle#',
'#Form.curdate#',
'#Form.curtime#',
'#Form.title#',
'#Form.name#',
'#Form.statustext#',
'#Form.textdate#') }
</cfquery>

Just my $.02
Michael
0
 
YogCommented:
How about cfstoredproc

<CFSTOREDPROC PROCEDURE="addrecord"
  DATASOURCE="xxxx"
  USERNAME="xxxx"
  PASSWORD="xxxx">

  <CFPROCPARAM TYPE="IN"
    CFSQLTYPE="CF_SQL_VARCHAR"
    VALUE="#Form.statustitle#"
    NULL="NO">

 --

 --

</CFSTOREDPROC>

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
YogCommented:
since its a access violation, maybe you have not run a GRANT statement and assigned access to the proc
0
 
YogCommented:
any updates Q1, if you want the call you dont need the "{" and "}" when executing the proc
0
 
g118481Author Commented:
I tried both suggestions, and I am getting this error.

Anyone have an idea of what is wrong?

/*******************************************************/
ODBC Error Code = 22005 (Error in assignment)


[IBM][CLI Driver] CLI0112E Error in assignment. SQLSTATE=22005



The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (17:1) to (20:19).
/*******************************************************/
0
 
YogCommented:
can you paste the cfstoredproc code so that we can have a look at it..
0
 
TsromCommented:
Did you create a DSN too conect cold fusion too the data source
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.