?
Solved

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

Posted on 2002-03-18
8
Medium Priority
?
876 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
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 200 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
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…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

764 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