Re: Cold Fusion and Oracle Unsupported Data Conversion
On Thursday 02 Sep 2004 22:04 pm, Adrocknaphobia wrote: This is def a driver/cfmx issue as you will not see this error in any form in SQL*Plus. Do you still see the error with the updated drivers from the Updater ? -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
I'd found that out, too, and ended up adding error catching in the PL/SQL to always make sure that I opened the ref cursor and passed it back, even if it was empty. - Original Message - From: Adrocknaphobia ** this is a response to a post from 3/9/2004 which never seemed to be resolved ** Explanation of CFMX Oracle JDBC database error Unsupported Data Conversion This issue occurs when using RefCursors to return queries from oracle via a stored procedure. When you declare a RefCursor it is NULL until it is populated with the OPEN ... FOR syntax. If any sort of error or premature return from the stored procedure occurs, Oracle still passes the RefCursor to CFMX. CFMX cannot convert the NULL RefCursor to an empty query. Now, if oracle performed and OPEN...FOR on the cursor and no rows were returned, then the RefCursor is successfully passed back to CFMX as it is no longer considered NULL. This one can be a little tricky to debug, especially you have good exception handling. In my case I was catching and returning an error before it got to populate the RefCursor. But CFMX threw it's Unsupported Data Conversion before it threw the actual error. This is def a driver/cfmx issue as you will not see this error in any form in SQL*Plus. Adam [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
Not Adrocknaphobia, but as far as we'er concerned Don't know - the updated drivers crash our whole system. :( - Original Message - From: Thomas Chiverton Do you still see the error with the updated drivers from the Updater ? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
I'll post back with more information about drivers. I'm going to be running test against oracle's drivers, as well as the latest ones MM released with the updater. (I'm still struggling with a thread locking issues, on another system, that seem to be related to the DataDirect 3.2 drivers not dealing with Oracle deadlocks very well) -Adam - Original Message - From: Deanna Schneider [EMAIL PROTECTED] Date: Fri, 3 Sep 2004 08:45:29 -0500 Subject: Re: Cold Fusion and Oracle Unsupported Data Conversion To: CF-Talk [EMAIL PROTECTED] Not Adrocknaphobia, but as far as we'er concerned Don't know - the updated drivers crash our whole system. :( - Original Message - From: Thomas Chiverton Do you still see the error with the updated drivers from the Updater ? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
I'd be interested to hear how the Oracle drivers work for you. We had to roll back from the latest MM drivers. But, I don't think they've tried any of the Oracle ones. Are there new drivers with this latest release? It hasn't been installed here yet. - Original Message - From: Adrocknaphobia I'll post back with more information about drivers. I'm going to be running test against oracle's drivers, as well as the latest ones MM released with the updater. (I'm still struggling with a thread locking issues, on another system, that seem to be related to the DataDirect 3.2 drivers not dealing with Oracle deadlocks very well) -Adam [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
Yeah, according to Forta one of the major reasons they relased the 6.1 Updater was because they got some new drivers made for Blackstone and didnt want to make us wait for them. Previously the lastest dataDirect driver they released was 3.3, so I assume that the ones included with the 6.1 Updater are 3.4. NEway, I got one task in between me and deploying different drivers to our test servers. I'll let you know what I find. There is also a 3rd reason I'm testing other drivers... LOBs. We keep running into issues using BLOBs with CFMX. Currently I have java classes in place for each application to manage clobs, but I'd like to get it to work with stored procedures. -Adam - Original Message - From: Deanna Schneider [EMAIL PROTECTED] Date: Fri, 3 Sep 2004 10:46:28 -0500 Subject: Re: Cold Fusion and Oracle Unsupported Data Conversion To: CF-Talk [EMAIL PROTECTED] I'd be interested to hear how the Oracle drivers work for you. We had to roll back from the latest MM drivers. But, I don't think they've tried any of the Oracle ones. Are there new drivers with this latest release? It hasn't been installed here yet. - Original Message - From: Adrocknaphobia I'll post back with more information about drivers. I'm going to be running test against oracle's drivers, as well as the latest ones MM released with the updater. (I'm still struggling with a thread locking issues, on another system, that seem to be related to the DataDirect 3.2 drivers not dealing with Oracle deadlocks very well) -Adam [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
On Fri, 3 Sep 2004 13:15:31 -0400, Adrocknaphobia [EMAIL PROTECTED] wrote: Yeah, according to Forta one of the major reasons they relased the 6.1 Updater was because they got some new drivers made for Blackstone and didnt want to make us wait for them. Previously the lastest dataDirect driver they released was 3.3, so I assume that the ones included with the 6.1 Updater are 3.4. NEway, I got one task in between me and deploying different drivers to our test servers. I'll let you know what I find. There is also a 3rd reason I'm testing other drivers... LOBs. We keep running into issues using BLOBs with CFMX. Currently I have java classes in place for each application to manage clobs, but I'd like to get it to work with stored procedures. The Updater is still using the 3.3 driver family, but just a later build (I *believe* it's build 48). We are actually still using the Updater 3 Plus Oracle drivers because all drivers released after that set have issues (removeOnExceptions in pre-Rollup releases and dropping client variables in the latest Updater Rollup). Since we've downgraded the drivers, we haven't had to re-boot our instances once for performance reasons. Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
Dave which version of the Oracle JDBC drivers are you using? -Adam - Original Message - From: Dave Carabetta [EMAIL PROTECTED] Date: Fri, 3 Sep 2004 13:23:19 -0400 Subject: Re: Cold Fusion and Oracle Unsupported Data Conversion To: CF-Talk [EMAIL PROTECTED] On Fri, 3 Sep 2004 13:15:31 -0400, Adrocknaphobia [EMAIL PROTECTED] wrote: Yeah, according to Forta one of the major reasons they relased the 6.1 Updater was because they got some new drivers made for Blackstone and didnt want to make us wait for them. Previously the lastest dataDirect driver they released was 3.3, so I assume that the ones included with the 6.1 Updater are 3.4. NEway, I got one task in between me and deploying different drivers to our test servers. I'll let you know what I find. There is also a 3rd reason I'm testing other drivers... LOBs. We keep running into issues using BLOBs with CFMX. Currently I have java classes in place for each application to manage clobs, but I'd like to get it to work with stored procedures. The Updater is still using the 3.3 driver family, but just a later build (I *believe* it's build 48). We are actually still using the Updater 3 Plus Oracle drivers because all drivers released after that set have issues (removeOnExceptions in pre-Rollup releases and dropping client variables in the latest Updater Rollup). Since we've downgraded the drivers, we haven't had to re-boot our instances once for performance reasons. Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
On Fri, 3 Sep 2004 13:51:20 -0400, Adrocknaphobia [EMAIL PROTECTED] wrote: Dave which version of the Oracle JDBC drivers are you using? Using the script at the below URL, this is what I get: [Macromedia][Oracle JDBC Driver]Driver Version: 3.1.003921002406001721 Script: http://www.bpurcell.org/blog/index.cfm?mode=entryentry=988 Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle Unsupported Data Conversion
I think that script is refering to the dataDirect drivers, not actual Oracle drivers. Although I could be wrong, the dataDirect drivers are around version 3.x whereas Oracle drivers follow version of the database (ie 9.2, 10.1) they correspond to. -Adam - Original Message - From: Dave Carabetta [EMAIL PROTECTED] Date: Fri, 3 Sep 2004 14:03:52 -0400 Subject: Re: Cold Fusion and Oracle Unsupported Data Conversion To: CF-Talk [EMAIL PROTECTED] On Fri, 3 Sep 2004 13:51:20 -0400, Adrocknaphobia [EMAIL PROTECTED] wrote: Dave which version of the Oracle JDBC drivers are you using? Using the script at the below URL, this is what I get: [Macromedia][Oracle JDBC Driver]Driver Version: 3.1.003921002406001721 Script: http://www.bpurcell.org/blog/index.cfm?mode=entryentry=988 Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Cold Fusion and Oracle Unsupported Data Conversion
** this is a response to a post from 3/9/2004 which never seemed to be resolved ** Explanation of CFMX Oracle JDBC database error Unsupported Data Conversion This issue occurs when using RefCursors to return queries from oracle via a stored procedure. When you declare a RefCursor it is NULL until it is populated with the OPEN ... FOR syntax. If any sort of error or premature return from the stored procedure occurs, Oracle still passes the RefCursor to CFMX. CFMX cannot convert the NULL RefCursor to an empty query. Now, if oracle performed and OPEN...FOR on the cursor and no rows were returned, then the RefCursor is successfully passed back to CFMX as it is no longer considered NULL. This one can be a little tricky to debug, especially you have good exception handling. In my case I was catching and returning an error before it got to populate the RefCursor. But CFMX threw it's Unsupported Data Conversion before it threw the actual error. This is def a driver/cfmx issue as you will not see this error in any form in SQL*Plus. Adam [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Cold Fusion and Oracle
Janet's scripts here are dead on for how it should be done. I've missed the earliest part of this thread, so I'm not sure if this is what you originally had, but this is what it should be. What happens when you run this script and call it from CF? -Adam -Original Message- From: Janet Schmitt [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 9, 2004 01:52 AM To: 'CF-Talk' Subject: Re: Cold Fusion and Oracle I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
i am just wondering why my Package.procedure works fine on my local machine which uses ODBC SOCKET connection and fails when i use JDBC connection on (oracle thin client) development server and throws following error. any help would be appreciated. thanks, -sampath here is my procedure and cf code : cfmx 6.1/oracle 8i --- create or replace package mySchema.myPackage AS Type spl_refcursortype IS REF CURSOR; PROCEDURE spl_proc_summary(spl_resultset OUT spl_refcursortype); END myPackage; create or replace package body mySchema.myPackage As PROCEDURE spl_proc_summary(spl_resultset OUT spl_refcursortype) IS BEGIN OPEN spl_resultset FOR SELECT * FROM MyTable; END; END; CFSTOREDPROC procedure= mySchema.myPackage.spl_proc_summary datasource=#request.datasource# cfprocparam cfsqltype=CF_SQL_REFCURSOR type=OUT CFPROCRESULT NAME=rs1 /CFSTOREDPROC [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Nick Han wrote: You need the ref cursor.types.cursorType---don't take this out. studentInfo types.cursorType; did you declare types.cursorType in the package spec?, and it has to be a ref cursor type! example: TYPE cursorType IS REF CURSOR; =you need this line in the package spec or else it will not work! again, it looks like you need to wrap this procedure in a package and invoke it through Cold Fusion via [package_name.procedure_name] in the storeproc tag. Yep, I've got a package called dlc and this procedure is part of the package body; I invoke the procedure with dlc.test_dlc_sp_getstudentinfo.types.cursorType is defined as a ref cursor in the package called types. Nick Han [EMAIL PROTECTED] 03/08/04 03:35PM It yields this error: Variable GETNAME is undefined Rob wrote: what does cfdump var=#getName# yield? On Mon, 2004-03-08 at 15:25, Richard Crawford wrote: Well, I altered the stored procedure as follows: = PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number ) as studentInfo types.cursorType; sFirst varchar2(50); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; CLOSE studentInfo; END test_dlc_sp_getStudentInfo; = Essentially, I removed the studentInfo ref cursor from the parameter list. Now, I run the Cold Fusion page, and the Unsupported Data Conversion error message is gone.Woo hoo! However, if I call the Stored Procedure from Cold Fusion like this: = cfstoredproc datasource=DLCampus procedure=dlc.test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_decimal cfprocresult name=getName /cfstoredproc = I would hope that I could get at the value of sOrient like this: cfoutput#getName.sOrient#/cfoutput Unfortunately, now I get a new error: = Element SORIENT is undefined in GETNAME = I don't know if I'm making progress or not. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Janet, Thanks for the suggestion.I tried it, but continue to receive the unsupported data conversion error. This is getting mighty frustrating. Janet Schmitt wrote: I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Stacy Young wrote: Perhaps I spoke too soon...I just remembered an unsolved mystery with our CF servers relating to an occasional JDBC error involving CF_SQL_NUMBER and a numeric field in oracle. Thanks for the tip! Stace Stace, How did you resolve the issue?I think I'm having the same problem. _ From: Doug Keen [mailto:[EMAIL PROTECTED] Sent: Monday, March 08, 2004 2:19 PM To: CF-Talk Subject: Re: Cold Fusion and Oracle I've had similar issues when migrating a CF/Oracle app from CF 4.5 to CF 6.1.During that adventure I found this resource: http://www.blinex.com/~sam/CF_SQL_TYPES.cfm This chart maps CF_SQL_TYPE's to types specific to different database platforms, including Oracle.In your case, you might have some luck if you change you CF_SQL_NUMBER parameters to CF_SQL_FLOAT (it worked for me, and as that chart shows, it's a better match for Oracle's NUMBER type). Doug Keen Senior Consultant, The Seva Group http://www.sevagroup.com Phone: 443-703-2087 Fax: 443-703-2097 Toll-Free: 1-888-230-1576 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
connection on (oracle thin client) development server and throws following error. ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SPL_PROC_SUMMARY' ORA-06550: line 1, column 7: PL/SQL: Statement ignored -sampath [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Richard - What is the format of the table tblStudentInfo?What are the datatypes specified for sFirst, sLast, sOrient and studentId? What is the CFSTOREDPROC code that was used to call this procedure? Janet. At 09:45 AM 3/9/2004 -0800, you wrote: Janet, Thanks for the suggestion.I tried it, but continue to receive the unsupported data conversion error. This is getting mighty frustrating. Janet Schmitt wrote: I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Janet Schmitt wrote: Richard - What is the format of the table tblStudentInfo?What are the datatypes specified for sFirst, sLast, sOrient and studentId? What is the CFSTOREDPROC code that was used to call this procedure? Janet. Janet, Here is the table definition: SID NUMBER NOT NULL SFIRST VARCHAR2(30) NOT NULL SLAST VARCHAR2(50) NOT NULL SORIENT CHAR(1) NOT NULL There are a few other fields in the table, but I'm not referencing them, so they don't seem relevant. Here is the CFSTOREDPROC code: cfstoredproc datasource=DLCampus procedure=dlc.test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_integer cfprocresult name=getName /cfstoredproc And, just for completeness's sake, here is the package definition, including the SP: CREATE OR REPLACE PACKAGE dlc AS PROCEDURE test_dlc_sp_getstudentinfo ( studentID IN integer, studentInfo OUT types.cursorType ); END dlc; / CREATE OR REPLACE PACKAGE BODY dlc AS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN integer, studentInfo OUT types.cursorType ) as sFirst varchar2(30); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = 1881; CLOSE studentInfo; END test_dlc_sp_getStudentInfo; END dlc; At 09:45 AM 3/9/2004 -0800, you wrote: Janet, Thanks for the suggestion.I tried it, but continue to receive the unsupported data conversion error. This is getting mighty frustrating. Janet Schmitt wrote: I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
sampath nellutla wrote: CFSTOREDPROC procedure= mySchema.myPackage.spl_proc_summary datasource=#request.datasource# Based on my own understanding, this line: cfprocparam cfsqltype=CF_SQL_REFCURSOR type=OUT is unnecessary in CFMX+. CFPROCRESULT NAME=rs1 /CFSTOREDPROC Now if only I could find out what's necessary to make MY situation work out! -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
I wonder if it does not like the way you are passing the value 1881.Try to set a variable to 1881 and pass that as a parameter. Something like the following (I am not sure if I have the syntax exactly right): cfset cstudentid=1881 cfProcParam type=In value=#cstudentid#cfSqlType=cf_sql_numeric variable=studentID Also, I am not sure about your cursor reference.Try using the ref cursor definition by doing the following: 1.Add this line to the package specification: TYPE student_type_ref_cursor IS REF CURSOR; 2.Change the procedure definition in the package spec and body from: PROCEDURE test_dlc_sp_getstudentinfo ( studentID IN integer, studentInfo OUT types.cursorType ); to PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); 3.Remove the close cursor statement from the package body. Janet. At 10:59 AM 3/9/2004 -0800, you wrote: Janet Schmitt wrote: Richard - What is the format of the table tblStudentInfo?What are the datatypes specified for sFirst, sLast, sOrient and studentId? What is the CFSTOREDPROC code that was used to call this procedure? Janet. Janet, Here is the table definition: SIDNUMBERNOT NULL SFIRSTVARCHAR2(30)NOT NULL SLASTVARCHAR2(50)NOT NULL SORIENTCHAR(1)NOT NULL There are a few other fields in the table, but I'm not referencing them, so they don't seem relevant. Here is the CFSTOREDPROC code: cfstoredproc datasource=DLCampus procedure=dlc.test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_integer cfprocresult name=getName /cfstoredproc And, just for completeness's sake, here is the package definition, including the SP: CREATE OR REPLACE PACKAGE dlc AS PROCEDURE test_dlc_sp_getstudentinfo ( studentID IN integer, studentInfo OUT types.cursorType ); END dlc; / CREATE OR REPLACE PACKAGE BODY dlc AS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN integer, studentInfo OUT types.cursorType ) as sFirst varchar2(30); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = 1881; CLOSE studentInfo; END test_dlc_sp_getStudentInfo; END dlc; At 09:45 AM 3/9/2004 -0800, you wrote: Janet, Thanks for the suggestion.I tried it, but continue to receive the unsupported data conversion error. This is getting mighty frustrating. Janet Schmitt wrote: I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; -- [ -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
cfprocparam cfsqltype=CF_SQL_REFCURSOR type=OUT is unnecessary in CFMX+. agreed and i commented it.But, no change. as i said before it works with ODBC SOCKET connection on my local machine but not with oracle JDBC THIN driver on dev server :(( thanks to the tech note by macromedia...here i found some info in the following link. .Note: Oracle Ref Cursors are only supported with the DataDirect Technologies Oracle JDBC Driver. Customers may, however, choose instead to use the Oracle JDBC http://www.macromedia.com/support/coldfusion/ts/documents/tn18344.htm [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle -- RESOLVED
Janet Schmitt wrote: 3.Remove the close cursor statement from the package body. Janet, Turns out that #3, above, was all that I needed to do.I feel kinda silly (why would I want to close the cursor if I wanted to read it later on?!!?), but now the thing works! Thank you very much! -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Cold Fusion and Oracle
The endless issues go on.Sigh. Does anyone know of a very good resource detailing the issues involved with hooking Cold Fusion up to Oracle? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cold Fusion and Oracle
What kind of issues are ya having?I have use CF and oracle together extensively... perhaps i can help -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Monday, March 08, 2004 2:11 PM To: CF-Talk Subject: Cold Fusion and Oracle The endless issues go on.Sigh. Does anyone know of a very good resource detailing the issues involved with hooking Cold Fusion up to Oracle? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Kristopher Pilles wrote: What kind of issues are ya having?I have use CF and oracle together extensively... perhaps i can help Kristopher, I've posted about my issues before, but I'll happily do so again.:) I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Richard Crawford wrote: Kristopher Pilles wrote: What kind of issues are ya having?I have use CF and oracle together extensively... perhaps i can help Kristopher, I've posted about my issues before, but I'll happily do so again.:) I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: Hm... My earlier post seems to have gotten truncated. Let's try again. Kristopher Pilles wrote: What kind of issues are ya having?I have use CF and oracle together extensively... perhaps i can help Kristopher, I've posted about my issues before, but I'll happily do so again.:) I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: === CREATE OR REPLACE procedure test_dlc_sp_getStudentInfo ( studentID IN number, studentInfo OUT types.cursorType ) as sFirst varchar2(50); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; fetch studentInfo into sFirst, sLast, sOrient; close studentInfo; end; / === Here is how I call the SP from Cold Fusion: === cfstoredproc datasource=DLCampus procedure=test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_number cfprocresult name=getName /cfstoredproc === At this point, I've trimmed the CF page down to just this little tiny snippet.If it works, it should generate no output at all, but I should be able to reference, say, sFirst like this later on in the same page: cfoutput query=getName#sFirst#/cfoutput or cfoutput#getName.sFirst#/cfoutput In older versions of CF, a result set from an Oracle SP would have to be referenced with a variable in the cfstoredproc section; with MX (which is what I'm using; not MX6.1), it's sufficient to reference a result set from an Oracle SP which is returned by a ref cursor with the cfprocresult attribute.I think I'm right about this, but I will happily accept that I'm wrong if it means that I can get this all working.;-) At any rate, when I try to execute the script, I get this error: === Error Executing Database Query. [Macromedia][Oracle JDBC Driver]Unsupported data conversion. === I don't know if this means that I'm experiencing a problem with the way I've written my stored procedure, the way I'm referencing it in Cold Fusion, or if I've got a problem with JDBC (I've got the native CFMX drivers referenced in my datasource definition in Cold Fusion). Any help would be more than appreciated.I'll send you a six-pack if you can help me figure out this problem.:-D -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cold Fusion and Oracle
I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: Unfortunately, it didn't come through. But anyway, have you tried calling this stored procedure from SQL*Plus? How about from another Java client? You might try using ViennaSQL, which I've found helpful for diagnosing problems occasionally. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Dave Watts wrote: I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: Unfortunately, it didn't come through. But anyway, have you tried calling this stored procedure from SQL*Plus? How about from another Java client? You might try using ViennaSQL, which I've found helpful for diagnosing problems occasionally. Yep, I've called it from SQL*Plus, and it works as expected. I'll look into ViennaSQL.I currently use the free version of TOAD, which doesn't (as far as I can tell) allow me to test stored procedures. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cold Fusion and Oracle
I'll look into ViennaSQL.I currently use the free version of TOAD, which doesn't (as far as I can tell) allow me to test stored procedures. Well, the thing we want to test specifically is what happens when you call it through JDBC. I don't think TOAD uses JDBC. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Richard, I think in order to call an Oracle stored procedure through Cold Fusion, you need to create a package spec. Don't think you can solely reference the procedure name directly. Create a package spec and put your procedure in the package body.After you have done that, modify your calling code to cfstoredproc datasource=DLCampus procedure=PACKAGE_SPEC_NAME.PROCEDURE_NAME Procedure name in this case is test_dlc_sp_getStudentInfo Nick Han [EMAIL PROTECTED] 03/08/04 11:28AM Richard Crawford wrote: Kristopher Pilles wrote: What kind of issues are ya having?I have use CF and oracle together extensively... perhaps i can help Kristopher, I've posted about my issues before, but I'll happily do so again.:) I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: Hm... My earlier post seems to have gotten truncated. Let's try again. Kristopher Pilles wrote: What kind of issues are ya having?I have use CF and oracle together extensively... perhaps i can help Kristopher, I've posted about my issues before, but I'll happily do so again.:) I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: === CREATE OR REPLACE procedure test_dlc_sp_getStudentInfo ( studentID IN number, studentInfo OUT types.cursorType ) as sFirst varchar2(50); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; fetch studentInfo into sFirst, sLast, sOrient; close studentInfo; end; / === Here is how I call the SP from Cold Fusion: === cfstoredproc datasource=DLCampus procedure=test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_number cfprocresult name=getName /cfstoredproc === At this point, I've trimmed the CF page down to just this little tiny snippet.If it works, it should generate no output at all, but I should be able to reference, say, sFirst like this later on in the same page: cfoutput query=getName#sFirst#/cfoutput or cfoutput#getName.sFirst#/cfoutput In older versions of CF, a result set from an Oracle SP would have to be referenced with a variable in the cfstoredproc section; with MX (which is what I'm using; not MX6.1), it's sufficient to reference a result set from an Oracle SP which is returned by a ref cursor with the cfprocresult attribute.I think I'm right about this, but I will happily accept that I'm wrong if it means that I can get this all working.;-) At any rate, when I try to execute the script, I get this error: === Error Executing Database Query. [Macromedia][Oracle JDBC Driver]Unsupported data conversion. === I don't know if this means that I'm experiencing a problem with the way I've written my stored procedure, the way I'm referencing it in Cold Fusion, or if I've got a problem with JDBC (I've got the native CFMX drivers referenced in my datasource definition in Cold Fusion). Any help would be more than appreciated.I'll send you a six-pack if you can help me figure out this problem.:-D -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
I've had similar issues when migrating a CF/Oracle app from CF 4.5 to CF 6.1.During that adventure I found this resource: http://www.blinex.com/~sam/CF_SQL_TYPES.cfm This chart maps CF_SQL_TYPE's to types specific to different database platforms, including Oracle.In your case, you might have some luck if you change you CF_SQL_NUMBER parameters to CF_SQL_FLOAT (it worked for me, and as that chart shows, it's a better match for Oracle's NUMBER type). Doug Keen Senior Consultant, The Seva Group http://www.sevagroup.com Phone: 443-703-2087 Fax: 443-703-2097 Toll-Free: 1-888-230-1576 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Doug Keen wrote: I've had similar issues when migrating a CF/Oracle app from CF 4.5 to CF 6.1.During that adventure I found this resource: http://www.blinex.com/~sam/CF_SQL_TYPES.cfm This chart maps CF_SQL_TYPE's to types specific to different database platforms, including Oracle.In your case, you might have some luck if you change you CF_SQL_NUMBER parameters to CF_SQL_FLOAT (it worked for me, and as that chart shows, it's a better match for Oracle's NUMBER type). Thanks for the suggestion.Unfortunately, I got the same error.I also tried cf_sql_decimal with the same result. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Nick Han wrote: Richard, I think in order to call an Oracle stored procedure through Cold Fusion, you need to create a package spec. Don't think you can solely reference the procedure name directly. Create a package spec and put your procedure in the package body.After you have done that, modify your calling code to cfstoredproc datasource=DLCampus procedure=PACKAGE_SPEC_NAME.PROCEDURE_NAME Procedure name in this case is test_dlc_sp_getStudentInfo Thanks for the tip.I've done that (I've been planning on putting all of our SP's into one or two packages anyway).Unfortunately, I got the same error when I tried again. Nick Han [EMAIL PROTECTED] 03/08/04 11:28AM Richard Crawford wrote: Kristopher Pilles wrote: What kind of issues are ya having?I have use CF and oracle together extensively... perhaps i can help Kristopher, I've posted about my issues before, but I'll happily do so again.:) I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: Hm... My earlier post seems to have gotten truncated. Let's try again. Kristopher Pilles wrote: What kind of issues are ya having?I have use CF and oracle together extensively... perhaps i can help Kristopher, I've posted about my issues before, but I'll happily do so again.:) I have a stored procedure written in PL/SQL, which I'm attempting to call from Cold Fusion, but only errors are generated. Here is the stored procedure: === CREATE OR REPLACE procedure test_dlc_sp_getStudentInfo ( studentID IN number, studentInfo OUT types.cursorType ) as sFirst varchar2(50); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; fetch studentInfo into sFirst, sLast, sOrient; close studentInfo; end; / === Here is how I call the SP from Cold Fusion: === cfstoredproc datasource=DLCampus procedure=test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_number cfprocresult name=getName /cfstoredproc === At this point, I've trimmed the CF page down to just this little tiny snippet.If it works, it should generate no output at all, but I should be able to reference, say, sFirst like this later on in the same page: cfoutput query=getName#sFirst#/cfoutput or cfoutput#getName.sFirst#/cfoutput In older versions of CF, a result set from an Oracle SP would have to be referenced with a variable in the cfstoredproc section; with MX (which is what I'm using; not MX6.1), it's sufficient to reference a result set from an Oracle SP which is returned by a ref cursor with the cfprocresult attribute.I think I'm right about this, but I will happily accept that I'm wrong if it means that I can get this all working.;-) At any rate, when I try to execute the script, I get this error: === Error Executing Database Query. [Macromedia][Oracle JDBC Driver]Unsupported data conversion. === I don't know if this means that I'm experiencing a problem with the way I've written my stored procedure, the way I'm referencing it in Cold Fusion, or if I've got a problem with JDBC (I've got the native CFMX drivers referenced in my datasource definition in Cold Fusion). Any help would be more than appreciated.I'll send you a six-pack if you can help me figure out this problem.:-D -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Is types.cursorType a REF CURSOR (not your plain-vanilla CURSOR)?If not, I think that may be another potential cause of your error... try changing types.cursorType to be a REF CURSOR and delete your FETCH ... INTO statement from the proc. Doug Keen Senior Consultant, The Seva Group http://www.sevagroup.com Phone: 443-703-2087 Fax: 443-703-2097 Toll-Free: 1-888-230-1576 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Doug Keen wrote: Is types.cursorType a REF CURSOR (not your plain-vanilla CURSOR)?If not, I think that may be another potential cause of your error... try changing types.cursorType to be a REF CURSOR and delete your FETCH ... INTO statement from the proc. Hm.It actually IS a REF CURSOR.I deleted the FETCH INTO line (actually, I just commented it out) per your suggestion, but that didn't help.Thanks for the tip. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Dave Watts wrote: I'll look into ViennaSQL.I currently use the free version of TOAD, which doesn't (as far as I can tell) allow me to test stored procedures. Well, the thing we want to test specifically is what happens when you call it through JDBC. I don't think TOAD uses JDBC. I downloaded and ran ViennaSQL and hooked it up to Oracle just fine from my computer over JDBC.I don't seem to be able to run stored procedures from the client, though. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Use SQL*Plus to test your procedures. -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Monday, March 8, 2004 10:19 PM To: 'CF-Talk' Subject: Re: Cold Fusion and Oracle Dave Watts wrote: I'll look into ViennaSQL.I currently use the free version of TOAD, which doesn't (as far as I can tell) allow me to test stored procedures. Well, the thing we want to test specifically is what happens when you call it through JDBC. I don't think TOAD uses JDBC. I downloaded and ran ViennaSQL and hooked it up to Oracle just fine from my computer over JDBC.I don't seem to be able to run stored procedures from the client, though. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cold Fusion and Oracle
Use SQL*Plus to test your procedures. ... but that won't tell him why they're not working through JDBC! Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Sorry, missed the begining of the thread, why do you guys think its an issue with JDBC? -Adam -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Monday, March 8, 2004 10:57 PM To: 'CF-Talk' Subject: RE: Cold Fusion and Oracle Use SQL*Plus to test your procedures. ... but that won't tell him why they're not working through JDBC! Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Adrocknaphobia wrote: Use SQL*Plus to test your procedures. Thanks.I've done so, and they both compile and run fine.It's where I'm trying to return the ref cursor to Cold Fusion that it stops working. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Adrocknaphobia wrote: Sorry, missed the begining of the thread, why do you guys think its an issue with JDBC? Hi, Adam. This is the error message I've been receiving: Error Executing Database Query. [Macromedia][Oracle JDBC Driver]Unsupported data conversion. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
Well, I altered the stored procedure as follows: = PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number ) as studentInfo types.cursorType; sFirst varchar2(50); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; CLOSE studentInfo; END test_dlc_sp_getStudentInfo; = Essentially, I removed the studentInfo ref cursor from the parameter list. Now, I run the Cold Fusion page, and the Unsupported Data Conversion error message is gone.Woo hoo! However, if I call the Stored Procedure from Cold Fusion like this: = cfstoredproc datasource=DLCampus procedure=dlc.test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_decimal cfprocresult name=getName /cfstoredproc = I would hope that I could get at the value of sOrient like this: cfoutput#getName.sOrient#/cfoutput Unfortunately, now I get a new error: = Element SORIENT is undefined in GETNAME = I don't know if I'm making progress or not. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
what does cfdump var=#getName# yield? On Mon, 2004-03-08 at 15:25, Richard Crawford wrote: Well, I altered the stored procedure as follows: = PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number ) as studentInfo types.cursorType; sFirst varchar2(50); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; CLOSE studentInfo; END test_dlc_sp_getStudentInfo; = Essentially, I removed the studentInfo ref cursor from the parameter list. Now, I run the Cold Fusion page, and the Unsupported Data Conversion error message is gone.Woo hoo! However, if I call the Stored Procedure from Cold Fusion like this: = cfstoredproc datasource=DLCampus procedure=dlc.test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_decimal cfprocresult name=getName /cfstoredproc = I would hope that I could get at the value of sOrient like this: cfoutput#getName.sOrient#/cfoutput Unfortunately, now I get a new error: = Element SORIENT is undefined in GETNAME = I don't know if I'm making progress or not. -- Rob [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
It yields this error: Variable GETNAME is undefined Rob wrote: what does cfdump var=#getName# yield? On Mon, 2004-03-08 at 15:25, Richard Crawford wrote: Well, I altered the stored procedure as follows: = PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number ) as studentInfo types.cursorType; sFirst varchar2(50); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; CLOSE studentInfo; END test_dlc_sp_getStudentInfo; = Essentially, I removed the studentInfo ref cursor from the parameter list. Now, I run the Cold Fusion page, and the Unsupported Data Conversion error message is gone.Woo hoo! However, if I call the Stored Procedure from Cold Fusion like this: = cfstoredproc datasource=DLCampus procedure=dlc.test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_decimal cfprocresult name=getName /cfstoredproc = I would hope that I could get at the value of sOrient like this: cfoutput#getName.sOrient#/cfoutput Unfortunately, now I get a new error: = Element SORIENT is undefined in GETNAME = I don't know if I'm making progress or not. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
You need the ref cursor.types.cursorType---don't take this out. studentInfo types.cursorType; did you declare types.cursorType in the package spec?, and it has to be a ref cursor type! example: TYPE cursorType IS REF CURSOR; =you need this line in the package spec or else it will not work! again, it looks like you need to wrap this procedure in a package and invoke it through Cold Fusion via [package_name.procedure_name] in the storeproc tag. Nick Han [EMAIL PROTECTED] 03/08/04 03:35PM It yields this error: Variable GETNAME is undefined Rob wrote: what does cfdump var=#getName# yield? On Mon, 2004-03-08 at 15:25, Richard Crawford wrote: Well, I altered the stored procedure as follows: = PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number ) as studentInfo types.cursorType; sFirst varchar2(50); sLast varchar2(50); sOrient char(1); begin open studentInfo for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; CLOSE studentInfo; END test_dlc_sp_getStudentInfo; = Essentially, I removed the studentInfo ref cursor from the parameter list. Now, I run the Cold Fusion page, and the Unsupported Data Conversion error message is gone.Woo hoo! However, if I call the Stored Procedure from Cold Fusion like this: = cfstoredproc datasource=DLCampus procedure=dlc.test_dlc_sp_getStudentInfo cfprocparam type=in value=1881 cfsqltype=cf_sql_decimal cfprocresult name=getName /cfstoredproc = I would hope that I could get at the value of sOrient like this: cfoutput#getName.sOrient#/cfoutput Unfortunately, now I get a new error: = Element SORIENT is undefined in GETNAME = I don't know if I'm making progress or not. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cold Fusion and Oracle
We use 'em both. Some hair pulling issues with CF 4.5 and some with 5 but CFMX has been very, very smooth. *knocks on wood* CFMX 6.1 Oracle 8.1.6 and now 8.1.7 -Stace _ From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Monday, March 08, 2004 2:11 PM To: CF-Talk Subject: Cold Fusion and Oracle The endless issues go on.Sigh. Does anyone know of a very good resource detailing the issues involved with hooking Cold Fusion up to Oracle? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cold Fusion and Oracle
Perhaps I spoke too soon...I just remembered an unsolved mystery with our CF servers relating to an occasional JDBC error involving CF_SQL_NUMBER and a numeric field in oracle. Thanks for the tip! Stace _ From: Doug Keen [mailto:[EMAIL PROTECTED] Sent: Monday, March 08, 2004 2:19 PM To: CF-Talk Subject: Re: Cold Fusion and Oracle I've had similar issues when migrating a CF/Oracle app from CF 4.5 to CF 6.1.During that adventure I found this resource: http://www.blinex.com/~sam/CF_SQL_TYPES.cfm This chart maps CF_SQL_TYPE's to types specific to different database platforms, including Oracle.In your case, you might have some luck if you change you CF_SQL_NUMBER parameters to CF_SQL_FLOAT (it worked for me, and as that chart shows, it's a better match for Oracle's NUMBER type). Doug Keen Senior Consultant, The Seva Group http://www.sevagroup.com Phone: 443-703-2087 Fax: 443-703-2097 Toll-Free: 1-888-230-1576 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle
I would try something like this for the Oracle portion: CREATE OR REPLACE PACKAGE schemaname.studentpackage IS TYPE student_type_ref_cursor IS REF CURSOR; PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number, student_cursor IN OUT student_type_ref_cursor); END; Create or replace package body schemaname.studentpackage IS PROCEDURE test_dlc_sp_getStudentInfo ( studentID IN number, student_cursor IN OUT student_type_ref_cursor) is begin open student_cursor for select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; end; END studentpackage; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
On Monday 23 Feb 2004 23:37 pm, Richard Crawford wrote: more disturbing indications that Oracle Stored Procedures do not return result sets. I am investigating this today / yesterday. Using cfprocresult works, with Oracle 9i and CFMX6.1 under weblogic, using CFMX 'oracle' datasources. Some combination of WebLogic JNDI datasources and some beta MM drivers causes this to break, however. More later :-) -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
On Tuesday 24 Feb 2004 09:46 am, Thomas Chiverton wrote:
More later :-)
There appears to be an issue with Oracle's JDBC driver and weblogic 7 (fixed
in 8 of course) that prevents the use of stored procs that return result
sets.
I was hoping to use weblogic's jdbc driver, with code likethe below, taken
from the WebLogic examples. However the registerOutParmeter fails (JDBC 3.0
method not implemented) :-(
--snip--
thingy=CreateObject('java','com.bf.common.j2ee.jdbc.JdbcConnectionFactory');
conn=thingy.getFactory().getConnection('dev_ora_resultSet');
cstmt=CreateObject('java','weblogic.jdbc.common.OracleCallableStatement');
cstmtWrong=conn.prepareCall(begin
bf_report.client_facade_pkg.get_available_reports(?, ?); end;);
wibble=CreateObject('java','com.bf.common.util.cast.CastingFactory');
cstmt=wibble.cast(cstmtWrong);
otherObj=CreateObject('java','java.sql.Types');
other=otherObj.OTHER;
cstmt.registerOutParameter(2,otherObj.OTHER);
cstmt.setString(1,'h');
cstmt.execute();
rs=cstmt.getResultSet(1);
--pins--
Solution: Use native CFMX drivers. Which is a shame :-(
I dunno if it's worth putting in on the wish list...
--
Tom Chiverton
Advanced ColdFusion Programmer
Tel: +44(0)1749 834997
email: [EMAIL PROTECTED]
BlueFinger Limited
Underwood Business Park
Wookey Hole Road, WELLS. BA5 1AF
Tel: +44 (0)1749 834900
Fax: +44 (0)1749 834901
web: www.bluefinger.com
Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple
Quay, BRISTOL. BS1 6EG.
*** This E-mail contains confidential information for the addressee
only. If you are not the intended recipient, please notify us
immediately. You should not use, disclose, distribute or copy this
communication if received in error. No binding contract will result from
this e-mail until such time as a written document is signed on behalf of
the company. BlueFinger Limited cannot accept responsibility for the
completeness or accuracy of this message as it has been transmitted over
public networks.***
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Scott Brady wrote: Try swapping the last two cfprocparams.Assuming you're using CFMX, CFMX no longer uses the dbvarname attribute (at least for Oracle, not sure about other databases), so the order in which you provide the parameters needs to match the order they're defined in the procedure. In the procedure, you have success last, but in the CF you're listing studentID last. Scott, That fixed one issue.Thanks! -- Richard S. Crawford Programmer/Analyst III, UC Davis Extension Distance Learning Group (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Thomas Chiverton wrote: On Monday 23 Feb 2004 23:37 pm, Richard Crawford wrote: more disturbing indications that Oracle Stored Procedures do not return result sets. I am investigating this today / yesterday. Using cfprocresult works, with Oracle 9i and CFMX6.1 under weblogic, using CFMX 'oracle' datasources. Some combination of WebLogic JNDI datasources and some beta MM drivers causes this to break, however. Our setup:Oracle 9i, CFMX, JRun, Apache.Weblogic doesn't seem to be involved in our servers. -- Richard S. Crawford Programmer/Analyst III, UC Davis Extension Distance Learning Group (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Thomas Chiverton wrote: Solution: Use native CFMX drivers. Which is a shame :-( I dunno if it's worth putting in on the wish list... I figured out to return a ref cursor, and this appears to return a result set fine.However, now I'm getting an error reading, [Oracle JDBC Driver]Unsupported data conversion.The offending line that the CF processor shows has nothing to do with SP's, of course (in fact, it's a comment line).I'm using CFMX's native drivers to talk to Oracle -- or, at least, I believe I am. Oh, the humanity! -- Richard S. Crawford Programmer/Analyst III, UC Davis Extension Distance Learning Group (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
On Tue, 2004-02-24 at 11:50, Richard Crawford wrote: Thomas Chiverton wrote: Solution: Use native CFMX drivers. Which is a shame :-( I dunno if it's worth putting in on the wish list... I figured out to return a ref cursor, and this appears to return a result set fine.However, now I'm getting an error reading, [Oracle JDBC Driver]Unsupported data conversion. That just passes along the problem from the driver/database it cant tell you the line number of the stored proc if it was not given The offending line that the CF processor shows has nothing to do with SP's, of course (in fact, it's a comment line). its talking about the cfproc call, probably near the comment. Sometimes it points before sometimes after, but generally near the query/proc call. In other words, its telling you were cf made the proc call not where the proc errored. -- Vale, Rob Luxuria immodica insaniam creat. Sanam formam viatae conservate! http://www.rohanclan.com http://treebeard.sourceforge.net http://ashpool.sourceforge.net [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Rob Rohan wrote: On Tue, 2004-02-24 at 11:50, Richard Crawford wrote: Thomas Chiverton wrote: Solution: Use native CFMX drivers. Which is a shame :-( I dunno if it's worth putting in on the wish list... I figured out to return a ref cursor, and this appears to return a result set fine.However, now I'm getting an error reading, [Oracle JDBC Driver]Unsupported data conversion. That just passes along the problem from the driver/database it cant tell you the line number of the stored proc if it was not given The offending line that the CF processor shows has nothing to do with SP's, of course (in fact, it's a comment line). its talking about the cfproc call, probably near the comment. Sometimes it points before sometimes after, but generally near the query/proc call. In other words, its telling you were cf made the proc call not where the proc errored. Thanks.I'm still not sure how to fix the problem. -- Richard S. Crawford Programmer/Analyst III, UC Davis Extension Distance Learning Group (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Rob Rohan wrote: That just passes along the problem from the driver/database it cant tell you the line number of the stored proc if it was not given Here is the revised SP in Oracle: CREATE OR REPLACE procedure dlc_sp_getStudentInfo ( studentID IN int, studentInfo in out types.cursorType ) as type studentCursor is ref cursor; --studentInfo studentCursor; xID int; xName varchar2(255); xURL varchar2(255); nID int; nNumber int; nGrdRoster int; lStartDate date; lEndDate date; iID int; iFirst varchar2(50); iLast varchar2(50); zero int := 0; userLast varchar2(50); rowcount int; nextQ int; annID int; error int := 0; sID int; v_login varchar2(500); sFirst varchar2(50); sLast varchar2(50); sOrient varchar2(5); begin open studentInfo for -- Part One: get student name, etc., and whether they've done the orientation select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; fetch studentInfo into sFirst, sLast, sOrient; close studentInfo; -- Part Two: get their other class info. /* select x.xID, x.xName, x.xURL, n.nID, n.nNumber, n.nGrdRoster, r.lStartDate, r.lEndDate, n.iID, i.iFirst, i.iLast, 0, u.usrLast into xID, xName, xURL, nID, nNumber, nGrdRoster, lStartDate, lEndDate, iID, iFirst, iLast, zero, userLast from tblCourses x, tblRoster r, tblSections n, tblInstructors i, tblUser u where x.xID = n.xID and r.nID = n.nID and r.sID = studentID and n.iID = i.iID and r.lStartDate current_date and u.usrID = studentID; */ end; / And the accompanying CFSTOREDPROC call in CF: cfstoredproc datasource=DLCampus procedure=dlc_sp_getStudentInfo cfprocparam type=in value=#cookieID# cfsqltype=cf_sql_integer cfprocresult name=getName !--- cfprocresult name=getInfo resultSet=2 --- /cfstoredproc The offending line that the CF processor shows has nothing to do with SP's, of course (in fact, it's a comment line). its talking about the cfproc call, probably near the comment. Sometimes it points before sometimes after, but generally near the query/proc call. In other words, its telling you were cf made the proc call not where the proc errored. -- Richard S. Crawford Programmer/Analyst III, UC Davis Extension Distance Learning Group (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
On Tue, 2004-02-24 at 12:23, Richard Crawford wrote: I know you are not supposed to, but try casting sFirst etc to varchar (not varchar2) just to see if that fixes the unknown type problem. ... still looking... Rob Rohan wrote: That just passes along the problem from the driver/database it cant tell you the line number of the stored proc if it was not given Here is the revised SP in Oracle: CREATE OR REPLACE procedure dlc_sp_getStudentInfo ( studentID IN int, studentInfo in out types.cursorType ) as type studentCursor is ref cursor; --studentInfo studentCursor; xID int; xName varchar2(255); xURL varchar2(255); nID int; nNumber int; nGrdRoster int; lStartDate date; lEndDate date; iID int; iFirst varchar2(50); iLast varchar2(50); zero int := 0; userLast varchar2(50); rowcount int; nextQ int; annID int; error int := 0; sID int; v_login varchar2(500); sFirst varchar2(50); sLast varchar2(50); sOrient varchar2(5); begin open studentInfo for -- Part One: get student name, etc., and whether they've done the orientation select sFirst, sLast, sOrient from tblStudentInfo where sid = studentID; fetch studentInfo into sFirst, sLast, sOrient; close studentInfo; -- Part Two: get their other class info. /* select x.xID, x.xName, x.xURL, n.nID, n.nNumber, n.nGrdRoster, r.lStartDate, r.lEndDate, n.iID, i.iFirst, i.iLast, 0, u.usrLast into xID, xName, xURL, nID, nNumber, nGrdRoster, lStartDate, lEndDate, iID, iFirst, iLast, zero, userLast from tblCourses x, tblRoster r, tblSections n, tblInstructors i, tblUser u where x.xID = n.xID and r.nID = n.nID and r.sID = studentID and n.iID = i.iID and r.lStartDate current_date and u.usrID = studentID; */ end; / And the accompanying CFSTOREDPROC call in CF: cfstoredproc datasource=DLCampus procedure=dlc_sp_getStudentInfo cfprocparam type=in value=#cookieID# cfsqltype=cf_sql_integer cfprocresult name=getName !--- cfprocresult name=getInfo resultSet=2 --- /cfstoredproc The offending line that the CF processor shows has nothing to do with SP's, of course (in fact, it's a comment line). its talking about the cfproc call, probably near the comment. Sometimes it points before sometimes after, but generally near the query/proc call. In other words, its telling you were cf made the proc call not where the proc errored. -- Vale, Rob Luxuria immodica insaniam creat. Sanam formam viatae conservate! http://www.rohanclan.com http://treebeard.sourceforge.net http://ashpool.sourceforge.net [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cold Fusion and Oracle Stored Procedures
-Original Message- From: Richard Crawford Sent: Tuesday, February 24, 2004 3:24 PM To: CF-Talk Subject: Re: Cold Fusion and Oracle Stored Procedures CREATE OR REPLACE procedure dlc_sp_getStudentInfo ( studentID IN int, studentInfo in out types.cursorType ) AFAIK, you can't pass a cursor in from CF, so why is studentInfo in out? Might as well just use an out ref cursor. Can you run your procedure directly in Oracle? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Plunkett, Matt wrote: -Original Message- From: Richard Crawford Sent: Tuesday, February 24, 2004 3:24 PM To: CF-Talk Subject: Re: Cold Fusion and Oracle Stored Procedures CREATE OR REPLACE procedure dlc_sp_getStudentInfo ( studentID IN int, studentInfo in out types.cursorType ) AFAIK, you can't pass a cursor in from CF, so why is studentInfo in out? Might as well just use an out ref cursor. Thanks for the suggestion.I simply followed the example I found on line, tailoring a bit for my needs.I should mention that I do have a package, declared elsewhere, called types, where cursorType is defined. Can you run your procedure directly in Oracle? It compiles just dandy. -- Richard S. Crawford Programmer/Analyst III, UC Davis Extension Distance Learning Group (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cold Fusion and Oracle Stored Procedures
-Original Message- From: Richard Crawford Sent: Tuesday, February 24, 2004 4:12 PM To: CF-Talk Subject: Re: Cold Fusion and Oracle Stored Procedures Can you run your procedure directly in Oracle? It compiles just dandy. But what happens when you run it?Do you get the results you'd expect?I guess what I'm asking is, are you certain that the problem exists between ColdFusion and Oracle, instead of just in your stored proc?I'm afraid I don't know how to run a stored proc from the command line and check that your cursor has what you expect in it.I use SQL Navigator to do that. It seems like you've made progress with your problem since yesterday, but have you ever gotten results back correctly in ColdFusion from Oracle yet? If so, great!If not, I'd suggest writing a very simple proc to get your feet wet.Something like PROCEDURE test (p_ref OUT myrefcursor, p_status OUT NUMBER) IS BEGIN p_status := 0; OPEN p_ref FOR select * from tblCourses; EXCEPTION WHEN OTHERS THEN p_status := SQLCODE; END test; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Plunkett, Matt wrote: -Original Message- From: Richard Crawford Sent: Tuesday, February 24, 2004 4:12 PM To: CF-Talk Subject: Re: Cold Fusion and Oracle Stored Procedures Can you run your procedure directly in Oracle? It compiles just dandy. But what happens when you run it?Do you get the results you'd expect?I guess what I'm asking is, are you certain that the problem exists between ColdFusion and Oracle, instead of just in your stored proc?I'm afraid I don't know how to run a stored proc from the command line and check that your cursor has what you expect in it.I use SQL Navigator to do that. I confess that I only just now grokked the difference between compiling and running the stored proc in Oracle.It does compile, but I'm not getting desired results back when I run it in SQL*Plus; I think I'm just not sure how to get it to consider the second parameter.I'm still hitting the books. It seems like you've made progress with your problem since yesterday, but have you ever gotten results back correctly in ColdFusion from Oracle yet? If so, great!If not, I'd suggest writing a very simple proc to get your feet wet.Something like PROCEDURE test (p_ref OUT myrefcursor, p_status OUT NUMBER) IS BEGIN p_status := 0; OPEN p_ref FOR select * from tblCourses; EXCEPTION WHEN OTHERS THEN p_status := SQLCODE; END test; I've done several minor SP's like that already, but nothing passing a cursor back.Thanks for the suggestion. -- Richard S. Crawford Programmer/Analyst III, UC Davis Extension Distance Learning Group (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Cold Fusion and Oracle Stored Procedures
I am having difficulty passing values from an Oracle Stored Procedure to
Cold Fusion.
Here is the stored procedure:
==
CREATE OR REPLACE procedure dlc_sp_verifyLogin (
sLogin IN varchar2,
sPassword IN varchar2,
sRemoteAddr IN varchar2,
sBrowser IN varchar2,
screen IN varchar2,
studentID OUT number,
success OUT int
)
as
FAILEDLOGIN exception;
begin
studentID := 0;
success := 1;
select
s.sID into studentID
from
tblUser u,
tblStudentInfo s
where
u.USRLOGIN = sLogin and
u.USRPWD = sPassword and
'{'||s.USERID||'}' like u.USRID;
if ( studentID = NULL ) then
raise FAILEDLOGIN;
end if;
studentID := sidGrabbed;
insert into tblTrace
(dIP, dStudent, dDate, dAttLog, dAttPass, dUser, dType, dScreen)
values
(sRemoteAddr, studentID, current_date, sLogin, sPassword,
substr(sBrowser, 1, 200), 's',
screen);
exception
when FAILEDLOGIN then
success := 0;
insert into tblTrace
(dIP, dStudent, dDate, dAttLog, dAttPass, dUser, dType, dScreen)
values
(sRemoteAddr, '', current_date, sLogin, sPassword, substr(sBrowser,
1, 200), 's', screen);
end dlc_sp_verifyLogin;
/
==
and here is where I call it in Cold Fusion:
==
cfstoredproc datasource=DLCampus procedure=dlc_sp_verifyLogin
debug=yes
cfprocresult name=loggedIn
cfprocparam type=in dbvarname=sLogin value=#trim(FORM.sLogin)#
cfsqltype=cf_sql_varchar
cfprocparam type=in dbvarname=sPassword
value=#trim(FORM.sPassword)# cfsqltype=cf_sql_varchar
cfprocparam type=in dbvarname=sRemoteAddr
value=#CGI.REMOTE_HOST# cfsqltype=cf_sql_varchar
cfprocparam type=in dbvarname=sBrowser
value=#cgi.http_user_agent# cfsqltype=cf_sql_varchar
cfprocparam type=in dbvarname=screen value=#screenSize#
cfsqltype=cf_sql_varchar
cfprocparam type=out dbvarname=success variable=successCode
cfsqltype=cf_sql_integer
cfprocparam type=out dbvarname=studentID variable=cookieID
cfsqltype=cf_sql_integer
/cfstoredproc
==
Now, I *should* be able to reference cookieID in my Cold Fusion page by
doing this:
cfoutput query=loggedIn#cookieID#/cfoutput
or by doing this:
cfoutput#loggedIn.cookieID#/cfoutput
Unfortunately, neither of these works.With the latter I get this error
message:
Element COOKIEID is undefined in LOGGEDIN.
With the former, I get an error message telling me that the attribute
name loggedIn is invalid.
What am I doing wrong?The Cold Fusion code is almost identical for the
SQL Server database we were using, and works just fine.
--
Richard S. Crawford
Programmer/Analyst III,
UC Davis Extension Distance Learning Group
(916)327-7793 / [EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Cold Fusion and Oracle Stored Procedures
Don't use a cfprocresult unless you're getting a ref cursor back.Just refer to the variable as cookieId. -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Monday, February 23, 2004 4:56 PM To: CF-Talk Subject: Cold Fusion and Oracle Stored Procedures and here is where I call it in Cold Fusion: == cfstoredproc datasource=DLCampus procedure=dlc_sp_verifyLogin debug=yes cfprocresult name=loggedIn cfprocparam type=in dbvarname=sLogin value=#trim(FORM.sLogin)# cfsqltype=cf_sql_varchar cfprocparam type=in dbvarname=sPassword value=#trim(FORM.sPassword)# cfsqltype=cf_sql_varchar cfprocparam type=in dbvarname=sRemoteAddr value=#CGI.REMOTE_HOST# cfsqltype=cf_sql_varchar cfprocparam type=in dbvarname=sBrowser value=#cgi.http_user_agent# cfsqltype=cf_sql_varchar cfprocparam type=in dbvarname=screen value=#screenSize# cfsqltype=cf_sql_varchar cfprocparam type=out dbvarname=success variable=successCode cfsqltype=cf_sql_integer cfprocparam type=out dbvarname=studentID variable=cookieID cfsqltype=cf_sql_integer /cfstoredproc [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Plunkett, Matt wrote: Don't use a cfprocresult unless you're getting a ref cursor back.Just refer to the variable as cookieId. Matt, Thanks for the suggestion.But as I research this, I'm finding more and more disturbing indications that Oracle Stored Procedures do not return result sets. -- Richard S. Crawford Programmer/Analyst III, UC Davis Extension Distance Learning Group (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cold Fusion and Oracle Stored Procedures
Try swapping the last two cfprocparams.Assuming you're using CFMX, CFMX no longer uses the dbvarname attribute (at least for Oracle, not sure about other databases), so the order in which you provide the parameters needs to match the order they're defined in the procedure. In the procedure, you have success last, but in the CF you're listing studentID last. Scott Richard Crawford wrote: Here is the stored procedure: == CREATE OR REPLACE procedure dlc_sp_verifyLogin ( sLogin IN varchar2, sPassword IN varchar2, sRemoteAddr IN varchar2, sBrowser IN varchar2, screen IN varchar2, studentID OUT number, success OUT int ) cfstoredproc datasource=DLCampus procedure=dlc_sp_verifyLogin debug=yes cfprocresult name=loggedIn cfprocparam type=in dbvarname=sLogin value=#trim(FORM.sLogin)# cfsqltype=cf_sql_varchar cfprocparam type=in dbvarname=sPassword value=#trim(FORM.sPassword)# cfsqltype=cf_sql_varchar cfprocparam type=in dbvarname=sRemoteAddr value=#CGI.REMOTE_HOST# cfsqltype=cf_sql_varchar cfprocparam type=in dbvarname=sBrowser value=#cgi.http_user_agent# cfsqltype=cf_sql_varchar cfprocparam type=in dbvarname=screen value=#screenSize# cfsqltype=cf_sql_varchar cfprocparam type=out dbvarname=success variable=successCode cfsqltype=cf_sql_integer cfprocparam type=out dbvarname=studentID variable=cookieID cfsqltype=cf_sql_integer /cfstoredproc -- --- Scott Brady http://www.scottbrady.net/ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
