Solved

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

Posted on 2002-03-18
8
856 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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