Solved

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

Posted on 2002-03-18
8
853 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
 
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
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 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

Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
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.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now