Solved

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

Posted on 2002-03-18
8
871 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:g118481
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Expert Comment

by:SewellM
ID: 6878973
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
 
LVL 5

Expert Comment

by:Yog
ID: 6879444
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
 
LVL 5

Expert Comment

by:Yog
ID: 6879461
since its a access violation, maybe you have not run a GRANT statement and assigned access to the proc
0
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 5

Expert Comment

by:Yog
ID: 6881699
any updates Q1, if you want the call you dont need the "{" and "}" when executing the proc
0
 
LVL 1

Author Comment

by:g118481
ID: 6882973
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
 
LVL 5

Expert Comment

by:Yog
ID: 6885303
can you paste the cfstoredproc code so that we can have a look at it..
0
 

Expert Comment

by:Tsrom
ID: 6922139
Did you create a DSN too conect cold fusion too the data source
0
 
LVL 5

Accepted Solution

by:
CFXPERT earned 50 total points
ID: 7262555
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

Featured Post

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question