Re: Stored procedures in CF10
By chance did you recently migrate and are using different credentials? Possibly just a database permissions issue. Byron Mann Lead Engineer & Architect HostMySite.com On Jul 19, 2013 5:47 PM, "vernon broussard" wrote: > > In ColdFusion 10 when trying to access one of our gateway applications now > I get an error saying that Executing stored procedures is not allowed. > How do you enable stored procedures in this version? Is it part of the > install or can it be changed within administrator? > Thanks v > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356248 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Stored procedures in CF10
> In ColdFusion 10 when trying to access one of our gateway applications now I > get an error saying that Executing stored > procedures is not allowed. How do you enable stored procedures in this > version? Is it part of the install or can it be changed > within administrator? CF 10 does not by default prevent you from executing stored procedures. However, you may disable execution of stored procedures within the datasource advanced settings, or within the database itself for the user account in question. So those are the places I'd look first. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsit ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356247 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Stored procedures in CF10
In ColdFusion 10 when trying to access one of our gateway applications now I get an error saying that âExecuting stored procedures is not allowed.â How do you enable stored procedures in this version? Is it part of the install or can it be changed within administrator? Thanks v ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356246 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Any way to log every call to stored procedures
I didn't find anything in any of the CF Admin API's for this. I do think this
would be a good idea so I added a feature request to the Adobe Bug Base. Voter
for it if you think this is a good idea.
https://bugbase.adobe.com/index.cfm?event=bug&id=3322414
Feature 3322414
Title
ColdFusion 11 - more Debug API features
Description
I would like to be able to access the Debug API to do more than just adjust the
Debug settings. I'd like to capture the Debug output data so we can do what
ever we want with it.
A current example is with a massive code base we inherited, there isn't an UML
or documentation anywhere. There are thousands of stored proces. We'd like to
be able to log/record all the SP's and queries called on each page (URL) thus
helping to generate a mapping of things.
I think more features in the Debug API would allow for this.
Example ideas:
Debug.queries();
Debug.stackTrace();
Debug.scope('session');
Debug.scope('application');
Etc
Wil Genovese
Sr. Web Application Developer/
Systems Administrator
CF Webtools
www.cfwebtools.com
wilg...@trunkful.com
www.trunkful.com
On Aug 30, 2012, at 3:06 PM, kbutte...@yahoo.com kbutte...@yahoo.com wrote:
>
> We have a client for whom we are doing a bunch of refactoring of code. As
> part of the QA, the client would like to know, for every page, what stored
> procedures are called and how that page is accessed.
>
> So what I would like to do is to log the call stack and the stored procedures
> called for every page.
>
> That information is in the debug info, so it should be obtainable, but I
> can't see how.
>
> Any ideas?
>
> Thanks,
> Kathryn
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352381
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Any way to log every call to stored procedures
We have a client for whom we are doing a bunch of refactoring of code. As part of the QA, the client would like to know, for every page, what stored procedures are called and how that page is accessed. So what I would like to do is to log the call stack and the stored procedures called for every page. That information is in the debug info, so it should be obtainable, but I can't see how. Any ideas? Thanks, Kathryn ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352363 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Array of struct into ORACLE using stored procedures
In Cold Fusion I have a large array of struct I'm trying to pass into ORACLE 10g using stored procedures. I am hitting a performance wall inside ORACLE (probably self inflicted). I don't know of a way to transfer a complex array directly to ORACLE -- if there is, I'd love to hear it. So what I've done is translate the array into a CLOB of delimited values where each array element is separated by a semicolon and each property value by a comma like: Prop1,prop2,prop3;Prop1,prop2,prop3;Prop1,prop2,prop3;Prop1,prop2,prop3; I can then pass the CLOB into ORACLE where I wrote a little parsing procedure that pulls the data elements out into a Global Temporary table which I then can merge into other data tables. The issue seems to be on the ORACLE end. It is taking too long to parse when the array gets large (10k rows takes about 4 mins). What I need is either a different method to move the array into ORACLE or to improve my parsing routine (see attached). Ideas? Warren Koch 314-323-2298 PROCEDURE get_items IS /* This routine extracts the item values from the input string into a global temporary table RENT_IMPORT_ITEMS It uses two delimiters: ; to separate items , to separate values */ instringCLOB; data_string VARCHAR2(2000); data_item VARCHAR2(100); data_type VARCHAR2(100); data_revVARCHAR2(100); data_shtVARCHAR2(100); data_location INTEGER; BEGIN instring := UPPER(TRIM(in_list_items)) || ';'; data_location := INSTR(instring, ';'); EXECUTE IMMEDIATE 'TRUNCATE TABLE RENT_IMPORT_ITEMS '; WHILE data_location > 0 LOOP data_string := SUBSTR(instring, 1, data_location - 1); data_string := REPLACE(data_string || '', ',', ' ,'); BEGIN SELECT SUBSTR(TRIM(REGEXP_SUBSTR(data_string, '[^,]+', 1, 1)), 1, 35) INTO data_item FROM DUAL; SELECT SUBSTR(TRIM(REGEXP_SUBSTR(data_string, '[^,]+', 1, 2)), 1, 5) INTO data_type FROM DUAL; SELECT SUBSTR(TRIM(REGEXP_SUBSTR(data_string, '[^,]+', 1, 3)), 1, 4) INTO data_rev FROM DUAL; SELECT SUBSTR(TRIM(REGEXP_SUBSTR(data_string, '[^,]+', 1, 4)), 1, 20) INTO data_sht FROM DUAL; INSERT INTO rent_import_items (item, item_type, item_rev, item_sht ) VALUES (data_item, data_type, data_rev, data_sht ); EXCEPTION WHEN OTHERS THEN NULL; END; instring := SUBSTR(instring, data_location + 1); data_location := INSTR(instring, ';'); END LOOP; EXCEPTION WHEN OTHERS THEN RAISE; END; ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334807 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Coldfusion Builder Stored Procedures
I'm pretty sure the data tools plungin from eclipse has this functionality, if you are looking for a better integrated database experience in eclipse. Sent from my mobile device On 2 Jun 2010 01:29, "Raymond Camden" wrote: I don't believe it is supported. I did a quick search on the CFBuilder public bug tracker and this was the closest related bug: http://cfbugs.adobe.com/bugreport/flexbugui/cfbugtracker/main.html#bugId=75148 It seems a bit broad though. I'd consider adding a very specific bug. I'd vote for it. On Tue, Jun 1, 2010 at 10:13 AM, Paul Alkema wrote: > > Hi, > > Does... ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334208 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Coldfusion Builder Stored Procedures
I don't believe it is supported. I did a quick search on the CFBuilder public bug tracker and this was the closest related bug: http://cfbugs.adobe.com/bugreport/flexbugui/cfbugtracker/main.html#bugId=75148 It seems a bit broad though. I'd consider adding a very specific bug. I'd vote for it. On Tue, Jun 1, 2010 at 10:13 AM, Paul Alkema wrote: > > Hi, > > Does anyone know if there's a way to view a databases stored procedures from > inside CF builder's RDS Dataview pane? From what I see it displays tables, > views, synonyms and system tables but no way to display stored procedures > for easy use. This seems like a major flaw to me. > > > > Any ideas? > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334175 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Coldfusion Builder Stored Procedures
Hi, Does anyone know if there's a way to view a databases stored procedures from inside CF builder's RDS Dataview pane? From what I see it displays tables, views, synonyms and system tables but no way to display stored procedures for easy use. This seems like a major flaw to me. Any ideas? Paul Alkema http://paulalkema.com/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334174 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
ColdFusion debug stored procedures
I have always had problem with the way ColdFusion reports errors in stored procedures and I was wondering how other CFâers go about debugging the same sort of errors (without costly software). For example, the following ColdFusion error is generated on a stored procedure... Application Execution Exception Error Type: Database : n/a Error Messages: Invalid data '' for CFSQLTYPE CF_SQL_INTEGER. I know what the error means but I have no way of knowing which one of cfprocparam fields its having problems with. ColdFusion does report a line number as always but it's the line end of the stored procedure not the actual cfprocparam line which it's failing on. This is only really an issue for me when I have a big procedure with lot of cfprocparam as I have to test each one at a time to find which has the invalid value. Looking forward to your comments Thanks ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329491 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: stored procedures
By table1 and table2 I actually meant database1 and database2. Im on a roll today... > -Original Message- > From: Chad Gray [mailto:cg...@careyweb.com] > Sent: Wednesday, November 18, 2009 12:36 PM > To: cf-talk > Subject: RE: stored procedures > > > Never mind.. I was editing the wrong stored procedure. There was one on > another table that was accessing the table I was working on. > > So basically there were two stored procs. One on table1 that was > accessing table2. And one on table2 that was accessing table2. > > I changed both stored procs and now it is working. > > Very confusing to have a stored proc on a table that is modifying another > table. > > Thanks for the input though! > Chad > > > -Original Message- > > From: b...@bradwood.com [mailto:b...@bradwood.com] > > Sent: Wednesday, November 18, 2009 12:26 PM > > To: cf-talk > > Subject: RE: stored procedures > > > > > > When you say you "modified" the stored proc and "executed it" do you > > mean you executed an ALTER statement on the proc? > > > > If you re-open the proc and the new parameter shows up, then I'm sure it > > is saved. > > Did you add the new parameter to the end of the list of params? Since > > dbvarname is largely ignored, the params are passed by position. > > > > Something quite possibly could be cached. Check your pooled statements > > setting in your datasource. I'm not sure if there is a way to clear > > those out easily with out restarting CF, SQL, or just going in and > > killing all the SPIDS that represent connections from your CF server. > > If it's a dev server, just bounce CF I guess. > > > > Before you do that though, double check the data source you are using > > and make sure that you are calling the procedure you think you are and > > not one in a different database/server/name/etc. > > > > ~Brad > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328496 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: stored procedures
Never mind.. I was editing the wrong stored procedure. There was one on another table that was accessing the table I was working on. So basically there were two stored procs. One on table1 that was accessing table2. And one on table2 that was accessing table2. I changed both stored procs and now it is working. Very confusing to have a stored proc on a table that is modifying another table. Thanks for the input though! Chad > -Original Message- > From: b...@bradwood.com [mailto:b...@bradwood.com] > Sent: Wednesday, November 18, 2009 12:26 PM > To: cf-talk > Subject: RE: stored procedures > > > When you say you "modified" the stored proc and "executed it" do you > mean you executed an ALTER statement on the proc? > > If you re-open the proc and the new parameter shows up, then I'm sure it > is saved. > Did you add the new parameter to the end of the list of params? Since > dbvarname is largely ignored, the params are passed by position. > > Something quite possibly could be cached. Check your pooled statements > setting in your datasource. I'm not sure if there is a way to clear > those out easily with out restarting CF, SQL, or just going in and > killing all the SPIDS that represent connections from your CF server. > If it's a dev server, just bounce CF I guess. > > Before you do that though, double check the data source you are using > and make sure that you are calling the procedure you think you are and > not one in a different database/server/name/etc. > > ~Brad ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328495 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: stored procedures
When you say you "modified" the stored proc and "executed it" do you mean you executed an ALTER statement on the proc? If you re-open the proc and the new parameter shows up, then I'm sure it is saved. Did you add the new parameter to the end of the list of params? Since dbvarname is largely ignored, the params are passed by position. Something quite possibly could be cached. Check your pooled statements setting in your datasource. I'm not sure if there is a way to clear those out easily with out restarting CF, SQL, or just going in and killing all the SPIDS that represent connections from your CF server. If it's a dev server, just bounce CF I guess. Before you do that though, double check the data source you are using and make sure that you are calling the procedure you think you are and not one in a different database/server/name/etc. ~Brad Original Message Subject: stored procedures From: "Chad Gray" Date: Wed, November 18, 2009 10:43 am To: cf-talk I am trying to add a field to a stored procedure. I "modified" the stored proc in SQL Studio and executed it. It appears to have "saved" the changes. Now I added to my CFStoredProc tag. I get the error "Procedure or function I_Orders has too many arguments specified." Did I not edit the stored procedure right? Is it cached somewhere? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328494 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
stored procedures
I am trying to add a field to a stored procedure. I "modified" the stored proc in SQL Studio and executed it. It appears to have "saved" the changes. Now I added to my CFStoredProc tag. I get the error "Procedure or function I_Orders has too many arguments specified." Did I not edit the stored procedure right? Is it cached somewhere? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328492 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Procedures
Rick, Um... This is not going to help. You will probably need to post the error you are receiving and also the SP code. There's nothin inherently wrong with the code below at first glance. -mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Rick Turner [mailto:[EMAIL PROTECTED] Sent: Saturday, June 21, 2008 2:27 PM To: CF-Talk Subject: Stored Procedures hi, i am trying to et my head around stored procedures (using MySQL) I can get a basic oe to work but having problems when trying to send parameters across Any help would be most welcome ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307919 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Stored Procedures
hi, i am trying to et my head around stored procedures (using MySQL) I can get a basic oe to work but having problems when trying to send parameters across Any help would be most welcome ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307915 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compiling Oracle Stored Procedures from ColdFusion
Ah, so we can use unix line feeds only? I wonder if that is the same issue we've seen with running inline PL/SQL blocks; I'll try it out and see what I can find. Thanks. On Nov 3, 2007 12:57 AM, Ryan, Terrence <[EMAIL PROTECTED]> wrote: > In case anyone has this problem in the future. The solution is to strip out > all chr(13)'s from the procedure creation code. > > Terrence Ryan > I.T. Director > Wharton Computing and Information Technology > E-mail: [EMAIL PROTECTED] > > > > ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292605 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compiling Oracle Stored Procedures from ColdFusion
In case anyone has this problem in the future. The solution is to strip out all chr(13)'s from the procedure creation code. Terrence Ryan I.T. Director Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292548 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Compiling Oracle Stored Procedures from ColdFusion
I figure this is a pretty random problem, but I figured I would try to ask here. Does anyone know how to compile Oracle stored procedures from ColdFusion? I can successful write stored procedures in Oracle from ColdFusion, they show up as uncompiled when I use Oracle Tools to view them. When I compile them through Oracle tools, they work perfectly. However when I try and run ALTER PROCEDURE [name] COMPILE; They don't compile, but without any error. Can anyone point me in the right direction here? Terrence Ryan I.T. Director Wharton Computing and Information Technology 3733 Spruce Street 216 Vance Hall Philadelphia, PA 19104 E-mail: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (215) 898-6705 Mobile: (267) 252-9727 ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292476 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Passing complex data into Oracle stored procedures
Surely your SQL guy would provide you with a properly normalised DB for you to insert your data into? Can't you just pass the values in as row entries? "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Heald, Timothy J To: CF-Talk Sent: Thu Apr 05 20:56:36 2007 Subject: Passing complex data into Oracle stored procedures I am adding a lot of additional functionality to an existing application (yuck). Basically taking a static survey application that generates a word document and turning it into a dynamic survey application that puts out multiple documents in multiple formats. I have several large forms that I am building dynamically. The number and names of fields won't be known until runtime, and are generated as you'd expect, using incremental or ID based variables. Here's the thing, currently these forms values are being handed into an oracle procedure in list form. I hate that :-). I know simply naming them all the same thing will make the list, but does that guarantee the order in which they will appear in the list? If not my referential integrity ill be shit. Now for whatever reason I am not allowed to just have a loop making multiple procedure calls on my action page, not looking to get into that. I would like to get away from handing the back end lists. I'm trying to look for a solution that can make me and our PL/SQL cats happy. Right now I can think of three ways to handle it, but I am not sure how I would/could accomplish them in CF. 1. Pass in arrays to the procedure. 2. Pass in a query or ref cursor to the procedure. 3. Pass in XML to the procedure. The SQL developer hasn't worked with 9i XML functionality and neither have I. He has looked into it and said he would essentially need to create his own string parser in order to properly write my objects into the DB, and the development and learning overhead would be more than what we are looking to invest right now. The ref cursor/query idea seems like one that would work if I wrote some Java. I cannot currently hand in a http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274649 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Passing complex data into Oracle stored procedures
> I've seen passing in arrays spoken about, but I cannot seem to find a > definitive example of how that would work. How about WDDX, can Oracle deal with that? There's a cfwddx tag that gives you some options. -- Josh ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274645 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Passing complex data into Oracle stored procedures
I am adding a lot of additional functionality to an existing application (yuck). Basically taking a static survey application that generates a word document and turning it into a dynamic survey application that puts out multiple documents in multiple formats. I have several large forms that I am building dynamically. The number and names of fields won't be known until runtime, and are generated as you'd expect, using incremental or ID based variables. Here's the thing, currently these forms values are being handed into an oracle procedure in list form. I hate that :-). I know simply naming them all the same thing will make the list, but does that guarantee the order in which they will appear in the list? If not my referential integrity ill be shit. Now for whatever reason I am not allowed to just have a loop making multiple procedure calls on my action page, not looking to get into that. I would like to get away from handing the back end lists. I'm trying to look for a solution that can make me and our PL/SQL cats happy. Right now I can think of three ways to handle it, but I am not sure how I would/could accomplish them in CF. 1. Pass in arrays to the procedure. 2. Pass in a query or ref cursor to the procedure. 3. Pass in XML to the procedure. The SQL developer hasn't worked with 9i XML functionality and neither have I. He has looked into it and said he would essentially need to create his own string parser in order to properly write my objects into the DB, and the development and learning overhead would be more than what we are looking to invest right now. The ref cursor/query idea seems like one that would work if I wrote some Java. I cannot currently hand in a http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274643 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY vs SQL Server Stored Procedures
Cached queries will execute faster than stored procedures in many cases. The problem of queries inside of ColdFusion deals directly with looping over the data of performing a Query of a Query against the data set. I tend to stay away from Query of a Query as the performance is not to my liking. The real problem is when you have data from two different data sets from two database servers that are not linked. Unless you perform an extract of the data and move it regularly, you will have an intermediary step of having ColdFusion combined data sets. Cached queries also introduce limitations. You set in the administrator how many cached queries to store in RAM. Database servers do not give you this limitation. In the SQL Server land, the execution plan of a stored procedure is calculated once and stored there. This does not affect your CF server and also gives you flexibility. You do have to query the database, but recalculation similar to the first cached query creation is the cost. If you are on a shared host, this could make your life easier. Now there is the business and model approach. I tend to use CF to handle framework dynamics and display logic. if I cannot abstract business logic into a CFC domain, I will keep it in the stored procedure. I tend to choose one or the other. I would consider this a good practice that I would recommend, but again it is my recommendation and you can take it for a grain of salt. Business logic really should be centralized so you can change configurations, rules and flow easily. As for performance gain, I typically see about a 25-30% performance boost when using cached queries. Now this sounds great, but the data set was 40K records and returned by a cached query and a stored procedure. They both executed in less than 100ms. In more business to business setting, this is negligible for the most part. Facing pages on high performing sites should tweak as much as they can. My other case for stored procedures is the increased functionality and control over my data. TSQL in the SQL Server realm gives me additional logic and commands to get low level information. ETL based tools like DTS and SSIS being invoked from my stored procedures creates automation avenues. Built in mail support can alleviate taxing my CF server on data notifications. On 3/23/07, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > > Indeed, I used to be in the camp of SPs were faster in ColdFusion but in > most cases they are not. What we do now is use them where we actually need > them - multiple recordsets for example. > > Also one of the major benefits is that they can be shared by our > ColdFusion > and .NET developers etc.. > > > > > > > > "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, > Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, > Registered in England, Number 678540. It contains information which is > confidential and may also be privileged. It is for the exclusive use of > the > intended recipient(s). If you are not the intended recipient(s) please > note > that any form of distribution, copying or use of this communication or the > information in it is strictly prohibited and may be unlawful. If you have > received this communication in error please return it to the sender or > call > our switchboard on +44 (0) 20 89107910. The opinions expressed within > this > communication are not necessarily those expressed by Reed Exhibitions." > Visit our website at http://www.reedexpo.com > > -Original Message- > From: Ryan, Terrence > To: CF-Talk > Sent: Fri Mar 23 02:13:36 2007 > Subject: RE: CFQUERY vs SQL Server Stored Procedures > > There are two reasons my organization encourages/forces all SQL to be in > stored procedures. And they don't directly touch on performance. > > 1. A belief that keeping SQL code in the database is of itself a good > thing. > By keeping the database interaction in the database you make it easier for > DBA's to do their job. Especially when a query goes awry, and your SQL > server starts bombing. It's in my humble opinion easier to troubleshoot > SQL > problems on the SQL server when the actual SQL is present. Especially > during > a crisis. > > Now, if you're a one person development team, or all of your CF developers > are also doing their own DBA work it might not make a whole lot of sense > for > this argument. > > However, having all of the SQL there allows you to more easily use SQL > tuning tools, which can improve indexing, which can make either inline > queries or stored procedures run faster. > > > 2. It forces developers to concentrate on the database first, as that > seems > to be the hardest thing to change once an application gets past th
Re: CFQUERY vs SQL Server Stored Procedures
Indeed, I used to be in the camp of SPs were faster in ColdFusion but in most cases they are not. What we do now is use them where we actually need them - multiple recordsets for example. Also one of the major benefits is that they can be shared by our ColdFusion and .NET developers etc.. "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Ryan, Terrence To: CF-Talk Sent: Fri Mar 23 02:13:36 2007 Subject: RE: CFQUERY vs SQL Server Stored Procedures There are two reasons my organization encourages/forces all SQL to be in stored procedures. And they don't directly touch on performance. 1. A belief that keeping SQL code in the database is of itself a good thing. By keeping the database interaction in the database you make it easier for DBA's to do their job. Especially when a query goes awry, and your SQL server starts bombing. It's in my humble opinion easier to troubleshoot SQL problems on the SQL server when the actual SQL is present. Especially during a crisis. Now, if you're a one person development team, or all of your CF developers are also doing their own DBA work it might not make a whole lot of sense for this argument. However, having all of the SQL there allows you to more easily use SQL tuning tools, which can improve indexing, which can make either inline queries or stored procedures run faster. 2. It forces developers to concentrate on the database first, as that seems to be the hardest thing to change once an application gets past the planning stage. Granted this is just enforcing good programming practices (taking a good long look at the database in this case) through policy. But considering that the database side of things was causing the majority of our server problems, it was the right call. So... Someone who tells you that all stored procedures are faster than inline queries is wrong. Someone who tells you that all stored procedures are "better", is probably wrong. Someone who tells you that stored procedures are better for their organization, group or specific problem might be right. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology      E-mail:        [EMAIL PROTECTED] ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273486 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFQUERY vs SQL Server Stored Procedures
There are two reasons my organization encourages/forces all SQL to be in stored procedures. And they don't directly touch on performance. 1. A belief that keeping SQL code in the database is of itself a good thing. By keeping the database interaction in the database you make it easier for DBA's to do their job. Especially when a query goes awry, and your SQL server starts bombing. It's in my humble opinion easier to troubleshoot SQL problems on the SQL server when the actual SQL is present. Especially during a crisis. Now, if you're a one person development team, or all of your CF developers are also doing their own DBA work it might not make a whole lot of sense for this argument. However, having all of the SQL there allows you to more easily use SQL tuning tools, which can improve indexing, which can make either inline queries or stored procedures run faster. 2. It forces developers to concentrate on the database first, as that seems to be the hardest thing to change once an application gets past the planning stage. Granted this is just enforcing good programming practices (taking a good long look at the database in this case) through policy. But considering that the database side of things was causing the majority of our server problems, it was the right call. So... Someone who tells you that all stored procedures are faster than inline queries is wrong. Someone who tells you that all stored procedures are "better", is probably wrong. Someone who tells you that stored procedures are better for their organization, group or specific problem might be right. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology      E-mail:        [EMAIL PROTECTED] ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273478 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY vs SQL Server Stored Procedures
At 11:17 AM 3/20/2007, D wrote: Are cached queries as good as stored procedures? Technically which are better? Is it a significant difference? Cached queries hold data in RAM so no call to the DB is necessary, so they are always much faster than stored procedures (except for the first call to retrieve the data of course). I'm thinking you might actually be asking about regular queries however. Some years ago I did a test to see how much difference sp's actually made. I constructed a couple of sp's to do lookups on large tables, compared their execution time with regular CFQUERY queries (not cached queries) doing the same thing. This was using SQL Server 7. I found there was no difference whatsoever, other than minor random millisecond variations (sometimes the regular queries were slightly faster even). Conclusion: the people who recommend stored procedures are working from theory and not practice and do not know what they are talking about in any practical sense. Stored procedures can involve significant extra work in esoteric code that's hard to find good documentation for, and they make your CF code far less portable between platforms (lots trickier to move between different kinds of databases). Consequently, since that time I have never again wasted my time writing stored procedures as they provide no practical benefit that I have been able to determine. I would love for someone to prove me wrong, however. That is not a request for your random uninformed opinion, if you're really a believer in sp's then please make a template or two that compares the execution time of a CFQUERY vs a sp, provide the code for your query and your sp, as well as a SQL Server maketable script and a downloadable data file in csv or tab-delimited format to populate the table so we can download and run the same test ourselves. I'm always willing to change my mind, but not for the sake of some buffoon spouting his ignorant opinions. *Show* me what the truth is, and I'll believe you. K ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273477 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY vs SQL Server Stored Procedures
Thanks guys. I ijust found this article on Adobe.com that helped if anyone had similar questions to my original questions. http://www.adobe.com/devnet/coldfusion/articles/beg_storedproc.html D >Opinions? > >Are cached queries as good as stored procedures? >Technically which are better? >Is it a significant difference? > >Thanks > >D ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273167 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFQUERY vs SQL Server Stored Procedures
Opinions? Are cached queries as good as stored procedures? Technically which are better? Is it a significant difference? Thanks If you are just using stored procedures to cache the data, it is probably a relatively even trade off. But, of course a stored procedure can do a lot more then just cache data when one starts using input parameters and outputting multiple recordsets that query caching doesn't even apply to. Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273148 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CFQUERY vs SQL Server Stored Procedures
It all depends on what you want to do. For startersCached Queries are good but you cannot use cfqueryparam, SPs can return multiple recordsets which cfquery cannot. In some cases it may be better to use cfquery and in some an SP. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 20 March 2007 16:30 To: CF-Talk Subject: CFQUERY vs SQL Server Stored Procedures Opinions? Are cached queries as good as stored procedures? Technically which are better? Is it a significant difference? Thanks D ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273145 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
CFQUERY vs SQL Server Stored Procedures
Opinions? Are cached queries as good as stored procedures? Technically which are better? Is it a significant difference? Thanks D ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273143 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Crystal Reports 10 with stored procedures...
What is the preferred method for calling CR files from CF when the underlying rpt file calls a stored procedure that requires one or more parameters to be passed? That is, how do I pass parameters to a stored procedure from within the cfreport tag? Anybody done this before? thx, -dante ~| Create robust enterprise, web RIAs.Upgrade & integrate Adobe Coldfusion MX7 with Flex 2http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272558 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Problem with cf executing stored procedures
On Thursday 11 January 2007 10:06, Simon Whittaker wrote: > I hope you can help us with a problem we are experiencing with execution of > stored procedures from coldfusion pages. Stored procedures are running very > slowly only when executed from cf pages, when they're run in the SQL 2000 > query analyser they run in sometimes less than a second but when run from Do the procs return lots of data ? If so, CF will be retriving them all at once, the query analyser maybe not. -- Tom Chiverton Helping to centrally foster efficient market-growth This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266363 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Problem with cf executing stored procedures
Simon, >From your description if each server is executing nominally on each end, it would suggest your issue exists in the network layer. Have you talked to your network admin to ask him to perform a round trip from server to server to see how long and how many "hops" are occuring to get from server to server? If the server are both internal, I would examine what is going on with your router or switch. I would examine network drivers on the Network cards. Are you crossing the DMZ to get from system to system? Are there software firewalls or packet sniffers delaying your transmissions? These questions are rhetoric and I do not need the answers to, but questions you need to ask yourself and team if you can find out how long it takes to get a data packet from one location to another? Teddy On 1/11/07, Simon Whittaker <[EMAIL PROTECTED]> wrote: > > All, > > I hope you can help us with a problem we are experiencing with execution > of > stored procedures from coldfusion pages. Stored procedures are running > very > slowly only when executed from cf pages, when they're run in the SQL 2000 > query analyser they run in sometimes less than a second but when run from > coldfusion they can take upwards of 60-80seconds seconds (which seems > ridiculous). The timer in the debugging states that the sp has taken < > 10ms > to run but when we put a time round it we get a time of the 60-80 seconds. > I am wondering if there is something I have missed in the cf > administrator or a hot fix I have missed or something wrong with my SQL > setup which is causing this huge delay? > > CF version is 7.0.2.142559 > SQL is Sql server 2000 > > All advice appreciated > > Cheers > > S > > > ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266222 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Problem with cf executing stored procedures
All, I hope you can help us with a problem we are experiencing with execution of stored procedures from coldfusion pages. Stored procedures are running very slowly only when executed from cf pages, when they're run in the SQL 2000 query analyser they run in sometimes less than a second but when run from coldfusion they can take upwards of 60-80seconds seconds (which seems ridiculous). The timer in the debugging states that the sp has taken < 10ms to run but when we put a time round it we get a time of the 60-80 seconds. I am wondering if there is something I have missed in the cf administrator or a hot fix I have missed or something wrong with my SQL setup which is causing this huge delay? CF version is 7.0.2.142559 SQL is Sql server 2000 All advice appreciated Cheers S ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266217 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: 7.02 cumulative hotfix 1 problems with stored procedures?
I think that hotfix added support for named parameters in cfprocparam back in. Previously the dbvarname would be ignored. Make sure your parameter names are correct. (And, depending on your database settings, check the case as well.) Cheers, Kris > Anybody getting errors with the hotfix? I've instaled in one of my > servers today and the website went down after a few hours of running. > The error is very wierd and does not says anything to me: > > Error Executing Database Query.[Macromedia][SQLServer JDBC > Driver][SQLServer]Line 1: Incorrect syntax near '='. The specific > sequence of files included or processed is: > C:\Inetpub\wwwroot\LocalUser\amazonia\index.cfm, line: 4 > > The stored proceedure wasn't change, nor the index.cfm file that calls > it. Sintax is > > 1 : DBVARNAME="IDidioma" VALUE="5"> > 2 : DBVARNAME="IDsite" VALUE="2"> > 3 : > 4 : ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262862 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
7.02 cumulative hotfix 1 problems with stored procedures?
Anybody getting errors with the hotfix? I've instaled in one of my servers today and the website went down after a few hours of running. The error is very wierd and does not says anything to me: Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '='. The specific sequence of files included or processed is: C:\Inetpub\wwwroot\LocalUser\amazonia\index.cfm, line: 4 The stored proceedure wasn't change, nor the index.cfm file that calls it. Sintax is 1 : 2 : 3 : 4 : After removing the hotfix and restarting CF, things got back to normal. PS: I've restarted the server before removing the HF and the error persisted. I've also restarted SQL Server too. No go. I'm using 3.5 DataDirect drivers from here http://www.adobe.com/go/42dcb10a. Any clues will be appreciated. Alex ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262833 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: stored procedures and mysql
thanks greg, yes my hair is now growing back as we speak :) and unfortunately for our company i am the DBA :) In fact me and my business partner are the DBA's, programmers, designers, marketers, salesman etc.. but thanks to all you guys on this site we are are getting better at other things, other than just making the coffee :) thanks again ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262670 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: stored procedures and mysql
Richard, Glad to be of help. I hope that now you will have a great weekend, happy holidays, etc. (and that your hair will grow back :-) And more than that, I hope that with Scorpio the MySQL drivers bundled with CF will be upgraded. (Anyone from the CF team at Adobe lurking here on this list ? ) btw ... I wore a "DBA hat" fulltime for several years before coming to CF. This made me very aware of the key role that the driver "middleware" plays in all SQL to client communication (CF included). I encourage that if others ever encounter problems related to CF and databases (including SQL), if you have access to DBAs, really try to engage your DBA when trying to resolve your problem. And truly, if you have access to DBAs, though you may not need them every day, be sure you make friends with them because there are times that they really can make magic happen for your database driven web applications :-) Happy CF'ing! Best regards, g On 12/1/06, Richard White <[EMAIL PROTECTED]> wrote: > > hi greg, you are a diamond :) I changed the driver over to the connector j > 5.0 and added a new datasource not with mysql connection but with 'other' > and set the configuration manually. I tried it and couldnt believe that it > is now working. It does make alot of sense now that we know what it is. > > Thanks again for your help greg, i really do appreciate it :) > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262475 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: stored procedures and mysql
hi greg, you are a diamond :) I changed the driver over to the connector j 5.0 and added a new datasource not with mysql connection but with 'other' and set the configuration manually. I tried it and couldnt believe that it is now working. It does make alot of sense now that we know what it is. Thanks again for your help greg, i really do appreciate it :) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262462 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: stored procedures and mysql
Richard, Although ColdFusion ships with MySQL database drivers, my sense is that these bundled drivers may not support Stored Procs. (Just guessing.) When I go into the ColdFusion Administrator, on the "Data Sources" page, the "Driver" dropdown for "Add New Data Source" has for MySQL "MySQL (3.x)". Stored procs only became available in MySQL as of version 5.x. So it could be that what has been causing you to be "tearing my hair out for weeks" is that ColdFusion may be talking to your MySQL 5.x database with a driver that has no (i.e. "null") logic for making Stored Proc calls. (Again, I am just guessing here. As a practical matter, however, the most important thing is just to get you configuration working :-) fyi ... drivers intermediate all communication between CF and the database. I.e.: ColdFusion --SQL--> driver --> Database --result set--> driver --> ColdFusion Following are links to two TechNotes on ColdFusion w/ MySQL. They cover going over to MySQL.com and downloading and installing their drivers<http://www.mysql.com/products/connector/j/>, product name Connector/J: Aug 1, 2006 ColdFusion MX: Configuring the MySQL JDBC driver (MySQL Connector) http://www.adobe.com/go/tn_19170 March 17, 2006 Configuring a JDBC Driver for MySQL 4.1 or 5 for use with ColdFusion MX http://www.adobe.com/go/6ef0253 If you follow what is outlined in the TechNotes, I believe that your existing ColdFusion MySQL datasource will be uneffected. That is you will be able to add datasources using these new Connector/J drivers alongside your existing datasources. If I am correct in this, once you have the new Connector/J based datasource set up, you existing code will be unaffected. Then for testing you can create an alternate version of your ColdFusion logic referencing the new Connector/J based datasource. Also, I notice on the Connector/J page<http://www.mysql.com/products/connector/j/>that the 5.x branch is now "production ready". If you are going to install a Connector/J driver, I encourage you to first try the 5.x branch. (As I had mentioned, my config is currently at 3.12. The 3.x branch is now up to 3.14. And for Connector/J there is no 4.x branch.) Regarding using Dreamweaver for setting up your datasource connection, it should make no difference. Essentially DW and ColdFusion Administrator provide alternate UIs for updating datasource records. fyi ... I first switched to using the MySQL Connector/J drivers in early 2004 for BLOB support. That is, whatever drivers were then bundled with CF did not support inserting BLOBs. If you do try out using Connector/J drivers, please do post back and let us know if you can get your procs working with ColdFusion with them. hth, g On 12/1/06, Richard White <[EMAIL PROTECTED]> wrote: > > hi greg, just noticed that you said you were using a > mysql-connector-java-3.1.12. > > i dont know if it makes any difference but i am using dreamweaver and have > setup a mysql connection through it. Queries work fine but not the stored > procedures. Do you think it may have something to do with the dreamweaver > connection? how did you set up your connection, maybe i can try it that way? > > I have gone to the cf administrator and it is showing the database as a > datasource so assume it has been setup fine though. > > thanks again greg, appreciate the help > > > >On 11/28/06, Richard White <[EMAIL PROTECTED]> wrote: > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262438 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: stored procedures and mysql
hi greg, just noticed that you said you were using a mysql-connector-java-3.1.12. i dont know if it makes any difference but i am using dreamweaver and have setup a mysql connection through it. Queries work fine but not the stored procedures. Do you think it may have something to do with the dreamweaver connection? how did you set up your connection, maybe i can try it that way? I have gone to the cf administrator and it is showing the database as a datasource so assume it has been setup fine though. thanks again greg, appreciate the help > >On 11/28/06, Richard White <[EMAIL PROTECTED]> wrote: ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262382 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: stored procedures and mysql
hi greg thanks for your reply. I thought that may be it for a minute but yes it is ticked - all of the allowed sql statements are ticked Thanks ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262381 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: stored procedures and mysql
Richard,
Just to confirm background stuff ...
Is the following enabled:
In ColdFusion Administrator --> Data Sources --> {select data source in
question} --> Advanced Settings --> Allowed SQL --> Stored Procedures
I am running stored procs with no problem with MySQL 5.0.19 with ColdFusion
7.0.2 with mysql-connector-java-3.1.12.
This is the first that I have seen this thread. Sorry.
hth,
g
On 11/28/06, Richard White <[EMAIL PROTECTED]> wrote:
>
> Hi i posted a problem a while ago as i couldnt get stored procedures
> stored in mysql to work, it keeps coming up with a java.lang null pointer
> error.
>
> previous thread:
> http://www.houseoffusion.com<http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:48365>
> /groups/CF-Talk/thread.cfm<http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:48365>
> /threadid:48365<http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:48365>
>
> i was wondering if any using mysql and cfstoredproc could provide an
> example or to see what i might be doing wrong as i cant work out whats
> wrong.
>
> I have granted full rights to my account as well so dont think its this
> but dont know why it is coming up with the null pointer. If i run the stored
> procedure from mysql it works fine so the stored procedure in the mysql
> database is fine.
>
> Thanks for any help, i would really appreciate it as i've been tearing my
> hair out for weeks, and i didn't have that much to start with!
>
>
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262059
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
stored procedures and mysql
Hi i posted a problem a while ago as i couldnt get stored procedures stored in mysql to work, it keeps coming up with a java.lang null pointer error. previous thread: http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:48365 i was wondering if any using mysql and cfstoredproc could provide an example or to see what i might be doing wrong as i cant work out whats wrong. I have granted full rights to my account as well so dont think its this but dont know why it is coming up with the null pointer. If i run the stored procedure from mysql it works fine so the stored procedure in the mysql database is fine. Thanks for any help, i would really appreciate it as i've been tearing my hair out for weeks, and i didn't have that much to start with! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261890 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: Stored Procedures in SQL Server
MSDN SQL Server 2005 Books Online: Stored Procedures http://msdn2.microsoft.com/en-us/library/ms190782.aspx Rick Root wrote: > Can anyone recommend a good resource (book, online training, etc) for > learning how to write stored procedures in Transact-SQL? > > Rick > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259125 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OT: Stored Procedures in SQL Server
Can anyone recommend a good resource (book, online training, etc) for learning how to write stored procedures in Transact-SQL? Rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259121 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
using stored procedures
Hi, i have created a mysql database. I have included some stored procedures. I have also created privileges and grants for the user on all tables, columns, rows, procedures etc. The table where i store the privileges for the stored procedures is procs_priv. I have granted all privileges to the user on the localhost. However, when i connect to the mysql database on the localhost dreamweaver finds all of the tables and their columns in the database section but under the section 'stored procedures' it says none, even though we have a stored procedure in the database, have set up the procedure privileges in the procs_priv table for the user and the stored procedure I am lost, has anyone experienced anything like this before? or be able to provide any suggesstions or advice. or does anyone use mysql as their database and could explain to me the steps you need to take to allow a user to access the stored procedures and to allow them to show in dreamweaver just so i can make sure what i have done is correct thanks ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257326 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procedures
Here is an example function spec from the package. No output parameters listed. FUNCTION GET_CONTENT_FILE_STATUS_ID( I_CFSREF_ALPHA_CODE CONTENT_FILE_STATUS_REF_TB.CFSREF_ALPHA_CODE%TYPE ) RETURN CONTENT_FILE_STATUS_REF_TB.CFSREF_CONTENT_FILE_STATUS_ID%TYPE; -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 11:55 AM To: CF-Talk Subject: ***SPAM*** Re: Stored procedures here's how ya get at a fucntion that is part of a package: HTH Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238661 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored procedures
If it is pure functions you are talking about, I am sure this will work: Select some_oracle_user_defined_function(arg) from dual -Original Message- From: Shawn McKee [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 9:27 AM To: CF-Talk Subject: Stored procedures I have used lots of Oracle stored procedures using but my DBA has now written several functions that I need to use. I have never had any luck accessing these via and was wondering if it was a) not possible or b) there is some clever trick involved. Using MX 6.1 on Linux. Thanks, Shawn McKee ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238655 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored procedures
If they are not part of a package(only say that because of the other emails referencing possibly being in a package) you could do something as simple as: SELECT MY_FUNCTION() FROM dual I use a lot of different custom Oracle functions to do the things many would normally do with ColdFusion but have to due to the framework in use here. For example I might have a query that does something like: SELECT ID, DISPLAYVALUE, YESNOFORMAT(ISACTIVE) ACTIVE FROM MYTABLE Then also use custom functions for other things such as treating a comma seperated list as a table in my FROM clause. On 4/25/06, Shawn McKee <[EMAIL PROTECTED]> wrote: > > Integers, strings, etc. A single value for a given function. > > -Original Message- > From: Aaron Rouse [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 25, 2006 11:33 AM > To: CF-Talk > Subject: Re: Stored procedures > > > What do the functions return? > > On 4/25/06, Shawn McKee <[EMAIL PROTECTED]> wrote: > > > > I have used lots of Oracle stored procedures using but my > > DBA > > has now written several functions that I need to use. I have never had > > any > > luck accessing these via and was wondering if it was a) > not > > possible or b) there is some clever trick involved. Using MX 6.1 on > > Linux. > > > > Thanks, > > > > Shawn McKee > > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238648 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: ***SPAM*** Re: Stored procedures
Yes they are part of an Oracle package. -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 11:46 AM To: CF-Talk Subject: ***SPAM*** Re: Stored procedures Are the functions part of an Oracle package? If so I can send ya the code to use 'emjust a normal CFQUERYno use of CFSTOREDPROC Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238646 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored procedures
here's how ya get at a fucntion that is part of a package: HTH Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238643 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored procedures
Integers, strings, etc. A single value for a given function. -Original Message- From: Aaron Rouse [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 11:33 AM To: CF-Talk Subject: Re: Stored procedures What do the functions return? On 4/25/06, Shawn McKee <[EMAIL PROTECTED]> wrote: > > I have used lots of Oracle stored procedures using but my > DBA > has now written several functions that I need to use. I have never had > any > luck accessing these via and was wondering if it was a) not > possible or b) there is some clever trick involved. Using MX 6.1 on > Linux. > > Thanks, > > Shawn McKee > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238641 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored procedures
Are the functions part of an Oracle package? If so I can send ya the code to use 'emjust a normal CFQUERYno use of CFSTOREDPROC Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238639 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored procedures
I don't believe you can access functions directly from ColdFusion. You'd have to write a stored proc that calls the function, I think. On 4/25/06, Shawn McKee <[EMAIL PROTECTED]> wrote: > I have used lots of Oracle stored procedures using but my DBA > has now written several functions that I need to use. I have never had any > luck accessing these via and was wondering if it was a) not > possible or b) there is some clever trick involved. Using MX 6.1 on Linux. > > Thanks, > > Shawn McKee > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238635 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored procedures
What do the functions return? On 4/25/06, Shawn McKee <[EMAIL PROTECTED]> wrote: > > I have used lots of Oracle stored procedures using but my > DBA > has now written several functions that I need to use. I have never had > any > luck accessing these via and was wondering if it was a) not > possible or b) there is some clever trick involved. Using MX 6.1 on > Linux. > > Thanks, > > Shawn McKee > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238632 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Stored procedures
I have used lots of Oracle stored procedures using but my DBA has now written several functions that I need to use. I have never had any luck accessing these via and was wondering if it was a) not possible or b) there is some clever trick involved. Using MX 6.1 on Linux. Thanks, Shawn McKee ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238623 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Procedures and when to use them
> From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED] > Is there any advantage having this type of query in a SP over > calling a simple CFC ?? Mike, Stored Procedures were a good choice for me when I was working at a place that had two, top-notch DBAs who could really analyze some of the more complex queries/transactions and optimize them for us (the developers) without us having to constantly go into the code to make changes, etc. We used inline queries for simple select, update, and delete statements; and used SPs for the complex tasks. Fast forward to where I work now where we have no DBA(s), anymore. I have two big internal apps that made heavy use of SPs which wasn't a problem when the DBAs were here. Since having to assume the DBA role as well, using SPs became a royal pain in the ass due to having to update so many areas, both in the application and on the DB server, everytime a change was made or a fix was necessary. Tango ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233858 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Procedures and when to use them
Yeah...intensive processes are a good application but that is not to say that CF could do the job as wellwithout seeing your code I meanthere may have been a better way to achieve the end result in CF to make it as fast. Each to their own I suppose... ;-) -Original Message- From: Martin Thorpe [mailto:[EMAIL PROTECTED] Sent: 02 March 2006 10:49 To: CF-Talk Subject: Re: Stored Procedures and when to use them An example. I had a set of queries to delete an organisation from one table and then all the relative data in the database for that organisation. I wrote it originally in CF but it was taking about 2 minutes, and timing out, to procees. After putting the whole lot into a stored proc the execution time dropped to seconds. Basically the process was not feasible in CF so was a good candidate for stored proc. Although I am talking Oracle here with PL/SQL not MSSQL which maybe different. In fact it was a PL/SQL package of stored procs to be more precise. So that is when I would use it basically. >Hi guys, > >I am just getting my head around stored procedures in SQL Server. One of >the things I am trying to understand is when I should be using them. > >For example, on my clients homepage I wish to pull 2 random product >records... > >password="#Request.App.DBpassword#" username="#Request.App.DBusername#"> >SELECT TOP 2 products.product_id, products.product_code, >products.product_price, products.product_price_sale, >products_description.product_title >FROM products INNER JOIN products_description >ON products.product_id = products_description.product_id >WHERE products.product_status = 1 >AND products.product_display = 1 >ORDER BY newid() > > >Is there any advantage having this type of query in a SP over calling a >simple CFC ?? > >Also, if anyone has any good references for me to check out... > >mike ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233856 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Procedures and when to use them
An example. I had a set of queries to delete an organisation from one table and then all the relative data in the database for that organisation. I wrote it originally in CF but it was taking about 2 minutes, and timing out, to procees. After putting the whole lot into a stored proc the execution time dropped to seconds. Basically the process was not feasible in CF so was a good candidate for stored proc. Although I am talking Oracle here with PL/SQL not MSSQL which maybe different. In fact it was a PL/SQL package of stored procs to be more precise. So that is when I would use it basically. >Hi guys, > >I am just getting my head around stored procedures in SQL Server. One of >the things I am trying to understand is when I should be using them. > >For example, on my clients homepage I wish to pull 2 random product >records... > >password="#Request.App.DBpassword#" username="#Request.App.DBusername#"> >SELECT TOP 2 products.product_id, products.product_code, >products.product_price, products.product_price_sale, >products_description.product_title >FROM products INNER JOIN products_description >ON products.product_id = products_description.product_id >WHERE products.product_status = 1 >AND products.product_display = 1 >ORDER BY newid() > > >Is there any advantage having this type of query in a SP over calling a >simple CFC ?? > >Also, if anyone has any good references for me to check out... > >mike ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233855 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Procedures and when to use them
One of the nice things about stored procedures wv. inline sql is that your database can tell you which procs are invalidated by a change to your schema, which can be a big time saver in development. /t ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233854 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Procedures and when to use them
Yeah, I mean definite major processing where using a cursor was just an example of when processing can get heavy, pending number of records and what you're doing with the data. Calling the database, looping results (in cf), calling the database again for whatever reason, looping results (in cf), then displaying filtered data would be a terrible way to do it. Obviously this is a made up situation but I think you understand my point. On 3/2/06, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > > They are not faster all the time with heavy processing as CF is not so > good > at waiting on long running requests. I would avoid cursors on nearly all > occasions and use CF - in these cases CF will no doubt be faster...on the > flip side if you are dealing with many thousands of records an SP/DTS may > be > your vice as CF will choke on large sets like this. > > > > > > -Original Message- > From: John C. Bland II [mailto:[EMAIL PROTECTED] > Sent: 02 March 2006 10:15 > To: CF-Talk > Subject: Re: Stored Procedures and when to use them > > Sprocs are faster if you are doing multiple things in 1 call. The idea is > you are in the database already so go ahead and let the database do what > it > does best. For general selects, inserts, updates, and deletes keeping it > in > the app is fine. > > It does come down to personal preference though. I used to use them all of > the time because the dba would spit them out. Well, I built a cfc > generator > that created my cfc's with all of the crud I need (and even grabs a view > if > it is named vMyTable [table name with v in front of it]) which makes > updates > way faster. Before I had to update the sproc, cfc(s), and calls to cfcs if > I > made 1 change to my table. Now I just run my generator again if I make any > table changes. > > So, for me...I'm done with sprocs unless there is some sort of mid to > major > processing I need done (cursors, etc). > > On 3/2/06, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED] > > > wrote: > > > > No benefit really. Not in this instance. > > > > > > > > -Original Message- > > From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED] > > Sent: 01 March 2006 21:39 > > To: CF-Talk > > Subject: Stored Procedures and when to use them > > > > Hi guys, > > > > I am just getting my head around stored procedures in SQL Server. One of > > the things I am trying to understand is when I should be using them. > > > > For example, on my clients homepage I wish to pull 2 random product > > records... > > > > > password="#Request.App.DBpassword#" username="#Request.App.DBusername#"> > > SELECT TOP 2 products.product_id, products.product_code, > > products.product_price, products.product_price_sale, > > products_description.product_title > > FROM products INNER JOIN products_description > > ON products.product_id = products_description.product_id > > WHERE products.product_status = 1 > > AND products.product_display = 1 > > ORDER BY newid() > > > > > > Is there any advantage having this type of query in a SP over calling a > > simple CFC ?? > > > > Also, if anyone has any good references for me to check out... > > > > mike > > > > > > > > > > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233852 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Procedures and when to use them
They are not faster all the time with heavy processing as CF is not so good at waiting on long running requests. I would avoid cursors on nearly all occasions and use CF - in these cases CF will no doubt be faster...on the flip side if you are dealing with many thousands of records an SP/DTS may be your vice as CF will choke on large sets like this. -Original Message- From: John C. Bland II [mailto:[EMAIL PROTECTED] Sent: 02 March 2006 10:15 To: CF-Talk Subject: Re: Stored Procedures and when to use them Sprocs are faster if you are doing multiple things in 1 call. The idea is you are in the database already so go ahead and let the database do what it does best. For general selects, inserts, updates, and deletes keeping it in the app is fine. It does come down to personal preference though. I used to use them all of the time because the dba would spit them out. Well, I built a cfc generator that created my cfc's with all of the crud I need (and even grabs a view if it is named vMyTable [table name with v in front of it]) which makes updates way faster. Before I had to update the sproc, cfc(s), and calls to cfcs if I made 1 change to my table. Now I just run my generator again if I make any table changes. So, for me...I'm done with sprocs unless there is some sort of mid to major processing I need done (cursors, etc). On 3/2/06, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > > No benefit really. Not in this instance. > > > > -Original Message- > From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED] > Sent: 01 March 2006 21:39 > To: CF-Talk > Subject: Stored Procedures and when to use them > > Hi guys, > > I am just getting my head around stored procedures in SQL Server. One of > the things I am trying to understand is when I should be using them. > > For example, on my clients homepage I wish to pull 2 random product > records... > > password="#Request.App.DBpassword#" username="#Request.App.DBusername#"> > SELECT TOP 2 products.product_id, products.product_code, > products.product_price, products.product_price_sale, > products_description.product_title > FROM products INNER JOIN products_description > ON products.product_id = products_description.product_id > WHERE products.product_status = 1 > AND products.product_display = 1 > ORDER BY newid() > > > Is there any advantage having this type of query in a SP over calling a > simple CFC ?? > > Also, if anyone has any good references for me to check out... > > mike > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233851 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Procedures and when to use them
Sprocs are faster if you are doing multiple things in 1 call. The idea is you are in the database already so go ahead and let the database do what it does best. For general selects, inserts, updates, and deletes keeping it in the app is fine. It does come down to personal preference though. I used to use them all of the time because the dba would spit them out. Well, I built a cfc generator that created my cfc's with all of the crud I need (and even grabs a view if it is named vMyTable [table name with v in front of it]) which makes updates way faster. Before I had to update the sproc, cfc(s), and calls to cfcs if I made 1 change to my table. Now I just run my generator again if I make any table changes. So, for me...I'm done with sprocs unless there is some sort of mid to major processing I need done (cursors, etc). On 3/2/06, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > > No benefit really. Not in this instance. > > > > -Original Message- > From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED] > Sent: 01 March 2006 21:39 > To: CF-Talk > Subject: Stored Procedures and when to use them > > Hi guys, > > I am just getting my head around stored procedures in SQL Server. One of > the things I am trying to understand is when I should be using them. > > For example, on my clients homepage I wish to pull 2 random product > records... > > password="#Request.App.DBpassword#" username="#Request.App.DBusername#"> > SELECT TOP 2 products.product_id, products.product_code, > products.product_price, products.product_price_sale, > products_description.product_title > FROM products INNER JOIN products_description > ON products.product_id = products_description.product_id > WHERE products.product_status = 1 > AND products.product_display = 1 > ORDER BY newid() > > > Is there any advantage having this type of query in a SP over calling a > simple CFC ?? > > Also, if anyone has any good references for me to check out... > > mike > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233850 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Procedures and when to use them
No benefit really. Not in this instance. -Original Message- From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED] Sent: 01 March 2006 21:39 To: CF-Talk Subject: Stored Procedures and when to use them Hi guys, I am just getting my head around stored procedures in SQL Server. One of the things I am trying to understand is when I should be using them. For example, on my clients homepage I wish to pull 2 random product records... SELECT TOP 2 products.product_id, products.product_code, products.product_price, products.product_price_sale, products_description.product_title FROM products INNER JOIN products_description ON products.product_id = products_description.product_id WHERE products.product_status = 1 AND products.product_display = 1 ORDER BY newid() Is there any advantage having this type of query in a SP over calling a simple CFC ?? Also, if anyone has any good references for me to check out... mike ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233849 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Procedures and when to use them
>Hi guys, > >I am just getting my head around stored procedures in SQL Server. One of >the things I am trying to understand is when I should be using them. I can't stand them myself. Used them in an application just for *hits'n'giggles, then needed to go back and make a few changes later. It was incredibly annoying having to go into the DB to make changes that should instinctively be made in the application. I don't like'em. Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233831 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Procedures and when to use them
to add to this question (and secretly bump it to the top of the list), how does the newID() function work in this case? mike ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233827 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Stored Procedures and when to use them
Hi guys, I am just getting my head around stored procedures in SQL Server. One of the things I am trying to understand is when I should be using them. For example, on my clients homepage I wish to pull 2 random product records... SELECT TOP 2 products.product_id, products.product_code, products.product_price, products.product_price_sale, products_description.product_title FROM products INNER JOIN products_description ON products.product_id = products_description.product_id WHERE products.product_status = 1 AND products.product_display = 1 ORDER BY newid() Is there any advantage having this type of query in a SP over calling a simple CFC ?? Also, if anyone has any good references for me to check out... mike ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233800 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Calling DB2/400 stored procedures with CF
Anyone have any experience calling stored procedures with CF? I can get the procedure to run, but I'm running into snags passing parameters. Thanks, Daron Smith PSEA ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230860 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Calling Oracle Stored Procedures from CF
Quite right, the Oracle thin driver won't automatically return a query for each cursor as the CF Enterprise Oracle driver will (afaik). On 12/9/05, Michael Bramwell <[EMAIL PROTECTED]> wrote: [snip] > Now Ive got the procedure working it appears that the jdbc thin driver is > not up to the job when calling the sp's from cf. Goddang. -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226656 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Calling Oracle Stored Procedures from CF
Thanks to James, Tanguy, Andrew and Deanna for your help. It appears that the explicit cursor is the way to go. Now Ive got the procedure working it appears that the jdbc thin driver is not up to the job when calling the sp's from cf. Goddang. Michaelb. - Original Message - From: "James Holmes" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Friday, December 09, 2005 7:53 AM Subject: Re: Calling Oracle Stored Procedures from CF > No I never said that it was bad - I said that it simply won't work if > the query returns more than one row, which it looks like it does. > > On 12/8/05, Dave Carabetta <[EMAIL PROTECTED]> wrote: > > > Just a side note on your cursor comment, as you seem to suggest that > > not using an explicit cursor is somehow bad. I would actually advise > > *against* explicit cursors in almost all cases. Here's supporting > > evidence from Oracle guru Tom Kyte himself, who demonstrates > > conclusively with thorough test cases that implicit cursors actually > > perform *better* than explicit cursors: > > -- > CFAJAX docs and other useful articles: > http://jr-holmes.coldfusionjournal.com/ > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226650 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Calling Oracle Stored Procedures from CF
No I never said that it was bad - I said that it simply won't work if the query returns more than one row, which it looks like it does. On 12/8/05, Dave Carabetta <[EMAIL PROTECTED]> wrote: > Just a side note on your cursor comment, as you seem to suggest that > not using an explicit cursor is somehow bad. I would actually advise > *against* explicit cursors in almost all cases. Here's supporting > evidence from Oracle guru Tom Kyte himself, who demonstrates > conclusively with thorough test cases that implicit cursors actually > perform *better* than explicit cursors: -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226633 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Calling Oracle Stored Procedures from CF
i tried answering this a few hours ago, but HOF seems to have eaten my mail, so here goes again: >Michael, > >1) Your proc is incorrect (that's what the error message is >complaining about). You need to fix a few things: > - don't use both AS and IS in the same declaration (just AS) > - no output parameters (the proc needs to get something >back to you) > - the select needs to be INTO an output param (or you >need to OPEN a cursor variable AS this select) > - no semi-colon at the end of the select > >2) once you've fixed these, try running the proc using > iso > >/t > Also, i noticed that you created this procedure in the SYSTEM schema, which is really - really - not a good idea. You should create a separate user/schema for your web application and only grant appropriate privs. If somebody can carry out a sql injection attack against your SYSTEM schema... ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226583 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Calling Oracle Stored Procedures from CF
>I am having troubles getting even basic Oracle sp's to call >from my coldfusion pages. > >Heres the procedure: > >CREATE OR REPLACE PROCEDURE selRandomQuestionIDs() AS >IS >BEGIN >SELECT quizQuestionID, RANDOM_ORDER >FROM random_number >WHERE rownum <= 20 >END; > >Heres the call: > > >BEGIN selRandomQuestionIDs(); END; > > >The error being returned is as follows: > >[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, >column 7: PLS-00905: object SYSTEM.SELRANDOMQUESTIONIDS is >invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored Michael, 1) Your proc is incorrect (that's what the error message is complaining about). You need to fix a few things: - don't use both AS and IS in the same declaration (just AS) - no output prameters (the proc needs to get something back to you) - the select needs to be INTO an output param (or you need to OPEN a cursor variable AS this select) - no semi-colon at the end of the select 2) once you've fixed these, try running the proc using iso /t ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226581 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Calling Oracle Stored Procedures from CF
On 12/8/05, James Holmes <[EMAIL PROTECTED]> wrote: > Not to mention that since there is no declared cursor you are relying > on an implicit cursor but; > > 1) this looks like it will return more than one row, which is a no-no > with an implicit cursor and > 2) there is no INTO clause to store the result, so the procedure won't > run anyway. > > Start with a procedure known to work properly and go from there. If > you want this example to be a procedure, declare a cursor and use > cfstoredproc. > Just a side note on your cursor comment, as you seem to suggest that not using an explicit cursor is somehow bad. I would actually advise *against* explicit cursors in almost all cases. Here's supporting evidence from Oracle guru Tom Kyte himself, who demonstrates conclusively with thorough test cases that implicit cursors actually perform *better* than explicit cursors: http://www.oracle.com/technology/oramag/oracle/03-jan/o13asktom.html and http://asktom.oracle.com/~tkyte/ivse.html The funny part about the Oracle development community that I've seen online and work(ed) with is that there is a tremendous amount of "old school" thought processes that go into making critical decisions, when those thought processes are clearly dated. In no way is that an attack on you (please don't take it that way). It's really a shot at the six-figure salary DBAs I've come across who are stuck in 1990! Regards, Dave. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226574 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Calling Oracle Stored Procedures from CF
Not to mention that since there is no declared cursor you are relying on an implicit cursor but; 1) this looks like it will return more than one row, which is a no-no with an implicit cursor and 2) there is no INTO clause to store the result, so the procedure won't run anyway. Start with a procedure known to work properly and go from there. If you want this example to be a procedure, declare a cursor and use cfstoredproc. On 12/8/05, Deanna Schneider <[EMAIL PROTECTED]> wrote: > Does that even work for you in sql plus? It compiles with errors for me. I > think you'd need this: > CREATE OR REPLACE PROCEDURE selRandomQuestionIDs AS > BEGIN >SELECT quizQuestionID, RANDOM_ORDER >FROM random_number >WHERE rownum <= 20; > END; > > Though, to be honest, I"m not sure why you're bothering making a stored proc > for this - it's just a simple select. > > > On 12/7/05, Michael Bramwell <[EMAIL PROTECTED]> wrote: > > > > Hi > > > > I am having troubles getting even basic Oracle sp's to call from my > > coldfusion pages. > > > > Heres the procedure: > > > > CREATE OR REPLACE PROCEDURE selRandomQuestionIDs() AS > > IS > > BEGIN > > SELECT quizQuestionID, RANDOM_ORDER > > FROM random_number > > WHERE rownum <= 20 > > END; > > > > Heres the call: > > > > > > BEGIN selRandomQuestionIDs(); END; > > > > > > The error being returned is as follows: > > > > [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: > > PLS-00905: object SYSTEM.SELRANDOMQUESTIONIDS is invalid ORA-06550: line > > 1, column 7: PL/SQL: Statement ignored > > > > Any help will be most appreciated. > > > > Cheers, > > > > Michaelb. > > > > > > > > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226547 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Calling Oracle Stored Procedures from CF
Does that even work for you in sql plus? It compiles with errors for me. I think you'd need this: CREATE OR REPLACE PROCEDURE selRandomQuestionIDs AS BEGIN SELECT quizQuestionID, RANDOM_ORDER FROM random_number WHERE rownum <= 20; END; Though, to be honest, I"m not sure why you're bothering making a stored proc for this - it's just a simple select. On 12/7/05, Michael Bramwell <[EMAIL PROTECTED]> wrote: > > Hi > > I am having troubles getting even basic Oracle sp's to call from my > coldfusion pages. > > Heres the procedure: > > CREATE OR REPLACE PROCEDURE selRandomQuestionIDs() AS > IS > BEGIN > SELECT quizQuestionID, RANDOM_ORDER > FROM random_number > WHERE rownum <= 20 > END; > > Heres the call: > > > BEGIN selRandomQuestionIDs(); END; > > > The error being returned is as follows: > > [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: > PLS-00905: object SYSTEM.SELRANDOMQUESTIONIDS is invalid ORA-06550: line > 1, column 7: PL/SQL: Statement ignored > > Any help will be most appreciated. > > Cheers, > > Michaelb. > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226537 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Calling Oracle Stored Procedures from CF
Michael, My experience has been that the easiest way to do this is: select selRandomQuestionIDs() from dual; Others will likely suggest , which I'm told is meant for just your situation. That said, I still prefer to select from dual (for no particular reason). Andrew On 12/7/05, Michael Bramwell <[EMAIL PROTECTED]> wrote: > Hi > > I am having troubles getting even basic Oracle sp's to call from my > coldfusion pages. > > Heres the procedure: > > CREATE OR REPLACE PROCEDURE selRandomQuestionIDs() AS > IS > BEGIN > SELECT quizQuestionID, RANDOM_ORDER > FROM random_number > WHERE rownum <= 20 > END; > > Heres the call: > > > BEGIN selRandomQuestionIDs(); END; > > > The error being returned is as follows: > > [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: > PLS-00905: object SYSTEM.SELRANDOMQUESTIONIDS is invalid ORA-06550: line 1, > column 7: PL/SQL: Statement ignored > > Any help will be most appreciated. > > Cheers, > > Michaelb. > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226534 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Calling Oracle Stored Procedures from CF
Hi I am having troubles getting even basic Oracle sp's to call from my coldfusion pages. Heres the procedure: CREATE OR REPLACE PROCEDURE selRandomQuestionIDs() AS IS BEGIN SELECT quizQuestionID, RANDOM_ORDER FROM random_number WHERE rownum <= 20 END; Heres the call: BEGIN selRandomQuestionIDs(); END; The error being returned is as follows: [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00905: object SYSTEM.SELRANDOMQUESTIONIDS is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored Any help will be most appreciated. Cheers, Michaelb. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226525 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CF7 and Oracle stored procedures problem
Currently using 1.0.2 > -Original Message- > From: Dave Carabetta [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 05, 2005 10:10 AM > To: CF-Talk > Subject: Re: CF7 and Oracle stored procedures problem > > On 10/5/05, Steve Brownlee <[EMAIL PROTECTED]> wrote: > > I believe I have found where the problem is happening with > ColdFusion 7 and > > Oracle 10 stored procedures. It appears as if there's an > endless loop going > > on in the code as per this forum message... > > > > http://www.jnetdirect.com/forum/viewtopic.php?t=156 > > > > When I take a snapshot of my JVM, I keep seeing the > following call stacks... > > > > "http-0.0.0.0-80-Processor24" daemon prio=5 tid=0x102d5938 > nid=0xc78 runnable > > [0x11c4e000..0x11c4fb68] > > at > > > coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunSt > atement.java:31 > > 9) > > at coldfusion.sql.Executive.getRowSets(Executive.java:395) > > at coldfusion.sql.Executive.executeCall(Executive.java:564) > > at coldfusion.sql.Executive.executeCall(Executive.java:517) > > at coldfusion.sql.Executive.executeCall(Executive.java:477) > > at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:320) > > > > and... > > > > "http-0.0.0.0-80-Processor24" daemon prio=5 tid=0x102d6ca8 > nid=0xcd0 runnable > > [0x120ef000..0x120efce8] > > at > > > coldfusion.server.j2ee.sql.JRunStatement.getMoreResults(JRunSt > atement.java:23 > > 5) > > at coldfusion.sql.Executive.getRowSets(Executive.java:395) > > at coldfusion.sql.Executive.executeCall(Executive.java:564) > > at coldfusion.sql.Executive.executeCall(Executive.java:517) > > at coldfusion.sql.Executive.executeCall(Executive.java:477) > > at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:320) > > > > These calls keep happening over and over again endlessly. > I'm wondering if > > there's some way I can modify either the Oracle stored > procedures or my > > CFSTOREDPROC tag (or sub-tag) parameters to stop this from > happening. I've > > never written an Oracle stored procedure so I'm lost on > that front, but any > > advice on how/if I modify the ColdFusion end would be of > immense help. > > > > Which version of Oracle 10g are you using? Release 1 or 2? > > Regards, > Dave. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220152 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF7 and Oracle stored procedures problem
On 10/5/05, Steve Brownlee <[EMAIL PROTECTED]> wrote: > I believe I have found where the problem is happening with ColdFusion 7 and > Oracle 10 stored procedures. It appears as if there's an endless loop going > on in the code as per this forum message... > > http://www.jnetdirect.com/forum/viewtopic.php?t=156 > > When I take a snapshot of my JVM, I keep seeing the following call stacks... > > "http-0.0.0.0-80-Processor24" daemon prio=5 tid=0x102d5938 nid=0xc78 runnable > [0x11c4e000..0x11c4fb68] > at > coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:31 > 9) > at coldfusion.sql.Executive.getRowSets(Executive.java:395) > at coldfusion.sql.Executive.executeCall(Executive.java:564) > at coldfusion.sql.Executive.executeCall(Executive.java:517) > at coldfusion.sql.Executive.executeCall(Executive.java:477) > at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:320) > > and... > > "http-0.0.0.0-80-Processor24" daemon prio=5 tid=0x102d6ca8 nid=0xcd0 runnable > [0x120ef000..0x120efce8] > at > coldfusion.server.j2ee.sql.JRunStatement.getMoreResults(JRunStatement.java:23 > 5) > at coldfusion.sql.Executive.getRowSets(Executive.java:395) > at coldfusion.sql.Executive.executeCall(Executive.java:564) > at coldfusion.sql.Executive.executeCall(Executive.java:517) > at coldfusion.sql.Executive.executeCall(Executive.java:477) > at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:320) > > These calls keep happening over and over again endlessly. I'm wondering if > there's some way I can modify either the Oracle stored procedures or my > CFSTOREDPROC tag (or sub-tag) parameters to stop this from happening. I've > never written an Oracle stored procedure so I'm lost on that front, but any > advice on how/if I modify the ColdFusion end would be of immense help. > Which version of Oracle 10g are you using? Release 1 or 2? Regards, Dave. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220122 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
CF7 and Oracle stored procedures problem
I believe I have found where the problem is happening with ColdFusion 7 and Oracle 10 stored procedures. It appears as if there's an endless loop going on in the code as per this forum message... http://www.jnetdirect.com/forum/viewtopic.php?t=156 When I take a snapshot of my JVM, I keep seeing the following call stacks... "http-0.0.0.0-80-Processor24" daemon prio=5 tid=0x102d5938 nid=0xc78 runnable [0x11c4e000..0x11c4fb68] at coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:31 9) at coldfusion.sql.Executive.getRowSets(Executive.java:395) at coldfusion.sql.Executive.executeCall(Executive.java:564) at coldfusion.sql.Executive.executeCall(Executive.java:517) at coldfusion.sql.Executive.executeCall(Executive.java:477) at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:320) and... "http-0.0.0.0-80-Processor24" daemon prio=5 tid=0x102d6ca8 nid=0xcd0 runnable [0x120ef000..0x120efce8] at coldfusion.server.j2ee.sql.JRunStatement.getMoreResults(JRunStatement.java:23 5) at coldfusion.sql.Executive.getRowSets(Executive.java:395) at coldfusion.sql.Executive.executeCall(Executive.java:564) at coldfusion.sql.Executive.executeCall(Executive.java:517) at coldfusion.sql.Executive.executeCall(Executive.java:477) at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:320) These calls keep happening over and over again endlessly. I'm wondering if there's some way I can modify either the Oracle stored procedures or my CFSTOREDPROC tag (or sub-tag) parameters to stop this from happening. I've never written an Oracle stored procedure so I'm lost on that front, but any advice on how/if I modify the ColdFusion end would be of immense help. Thanks in advance, Steve ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220114 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Report Builder Subreports and Stored Procedures
You should be able to use anything as a datasource for a report. You just pass it in. You just don't get the benefit of the fields automatically being available in design mode. You have to know the names being passed in. Constanty “Connie” DeCinko III Web Architect, Webmaster, Web Developer Lone Jet Enterprises Glendale, Arizona www.LoneJet.com -Original Message- From: Andrew Peterson [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 8:26 AM To: CF-Talk Subject: Report Builder Subreports and Stored Procedures Can you use a Microsoft SQL Stored Procedure to populate a sub-report (cfr) either by "Embedded Stored Procedure(s)" within the report builder or called by a CFM template? If so, how is this accomplished? We know that you can use a Microsoft SQL Stored Procedure in a CFM template to populate the report. Also, why don’t the stored procedure(s) show up under the “Queries†folder for the ODBC data source within the “Query Builderâ€? Thank you in advance for all of your help! Sincerely, Andrew ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202179 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Report Builder Subreports and Stored Procedures
Can you use a Microsoft SQL Stored Procedure to populate a sub-report (cfr) either by "Embedded Stored Procedure(s)" within the report builder or called by a CFM template? If so, how is this accomplished? We know that you can use a Microsoft SQL Stored Procedure in a CFM template to populate the report. Also, why don’t the stored procedure(s) show up under the “Queries” folder for the ODBC data source within the “Query Builder”? Thank you in advance for all of your help! Sincerely, Andrew ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202174 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Coldfusion MX Report Builder Sub-reports and Stored Procedures
Can you use a Microsoft SQL Stored Procedure to populate a sub-report (cfr) either by "Embedded Stored Procedure(s)" within the report builder or called by a CFM template? If so, how is this accomplished? We know that you can use a Microsoft SQL Stored Procedure in a CFM template to populate the report. Also, why don't the stored procedure(s) show up under the "Queries" folder for the ODBC data source within the "Query Builder"? Thank you in advance for all of your help! Gary 8-) ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202033 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: recordsets return by stored procedures?
> Is there a way to find out how many recordsets return by a > stored procedure? Or is there a way to merge all the > recordsets in a stored procedure so that it would only return > 1 recordset? > > I have a stored procedure that would return an unknown number > of recordsets depending on how many records we have in the > database, but each recordset will have the same number of > columns and column names. I need to know the number of > recorsets it will return so I can do a loop for the cfprocresult. Why not just write the stored procedure so that it returns what you want it to return? In this case, for example, your stored procedure could create a temp table, select all the records you want into that temp table, then select from the temp table to return a single recordset. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201393 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Joe Celko and Nested Sets; was Re: recordsets return by stored procedures?
Adam Howitt wrote: > You may want to check out Joe Celco's nested set model (no refs but google > should find it) for a neat way to handle this type of hierarchical > information storage. In the meantime you could keep the cursor but insert > the resulting recordsets into a working table before returning the complete > resultset. You would create the working table if @depth = 1 (set a local > variable and pass it to subsequent calls and drop it after the last call) or > permanently create it which would give you better performance and the > ability to use indexes. I highly recommend his book, SQL for Smarties[1]. Here's a few links that should help: 1. http://www.intelligententerprise.com/001020/celko.jhtml 2. http://www.dbmsmag.com/9604d06.html and http://www.dbmsmag.com/9603d06.html 3. http://troels.arvin.dk/db/rdbms/links/ 4. http://www.onlamp.com/pub/a/onlamp/2004/08/05/hierarchical_sql.html 5. http://dbazine.com/tropashko4.shtml 6. http://www.sitepoint.com/article/hierarchical-data-database He also wrote a whole book on trees and hierarchies[2]. K. [1] http://www.amazon.co.uk/exec/obidos/ASIN/1558605762/ [2] http://www.amazon.co.uk/exec/obidos/ASIN/1558609202/ ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201392 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
Oh hell, that's hideously simple... CREATE PROCEDURE dbo.sp_organizations @parent_id int AS DECLARE @Name varchar(500) DECLARE @Parent int DECLARE cur_Level CURSOR LOCAL FOR SELECT Organization_id AS ID, Organization_nm, Parent_organization_id FROM Organization WHERE Parent_organization_id = @parent_id ORDER BY Organization_nm CREATE TABLE #child (id int, name nvarchar(500), parent int) OPEN cur_Level FETCH NEXT FROM cur_Level INTO @parent_id, @Name, @Parent WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #child (id, name, parent) VALUES (@parent_id, @name, @parent) EXEC sp_organizations @parent_id FETCH NEXT FROM cur_Level INTO @parent_id, @Name, @Parent END CLOSE cur_Level DEALLOCATE cur_Level SELECT * FROM #child DROP TABLE #child GO The # tells SQL Server to create a temp-table. As a matter of fact, I'd eliminate the cursor too -- insert the first record into the temp table manually, then use this loop to insert all the child organizations: WHILE EXISTS (select * from organization where Parent_organization_id in (select id from #child) and organization_id not in (select id from #child)) BEGIN INSERT INTO #child (id, name, parent) SELECT organization_id, organization_nm, Parent_organization_id FROM organization where Parent_organization_id in (select id from #child) and organization_id not in (select id from #child) END (really #child is a bad name for a table but then, so are "id" and "name" bad names for columns). hth s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201391 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
You may want to check out Joe Celco's nested set model (no refs but google should find it) for a neat way to handle this type of hierarchical information storage. In the meantime you could keep the cursor but insert the resulting recordsets into a working table before returning the complete resultset. You would create the working table if @depth = 1 (set a local variable and pass it to subsequent calls and drop it after the last call) or permanently create it which would give you better performance and the ability to use indexes. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201383 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
Hi, Here is my stored procedure: CREATE PROCEDURE dbo.sp_organizations @parent_id int AS DECLARE @Name varchar(500) DECLARE @Parent int DECLARE cur_Level CURSOR LOCAL FOR SELECT Organization_id AS ID, Organization_nm, Parent_organization_id FROM Organization WHERE Parent_organization_id = @parent_id ORDER BY Organization_nm OPEN cur_Level FETCH NEXT FROM cur_Level INTO @parent_id, @Name, @Parent WHILE @@FETCH_STATUS = 0 BEGIN SELECT @parent_id AS ID, @Name AS Name, @Parent AS Parent EXEC sp_organizations @parent_id FETCH NEXT FROM cur_Level INTO @parent_id, @Name, @Parent END CLOSE cur_Level DEALLOCATE cur_Level GO Basically I have one table called "organization" and it has three columns: id, name, and parent_id. The parent_id will tell which organization is the parent of that organization. It goes into unlimited levels of parent-child relationships. I want to get all the children organizations that belongs to a specified parent_id. I am using MS SQL Server 2000 and CFMX. If you could help me to rewrite my stored procedure so that it would return just one recordset, I would greatly appreciate it. My experience with stored procedure is limited. So I have no ideas how to put these recordsets into a temp table. Thanks. Johnny ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201376 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
> Hi, > Is there a way to find out how many recordsets return by a > stored procedure? Or is there a way to merge all the > recordsets in a stored procedure so that it would only > return 1 recordset? > I have a stored procedure that would return an unknown > number of recordsets depending on how many records we have > in the database, but each recordset will have the same > number of columns and column names. I need to know the > number of recorsets it will return so I can do a loop for > the cfprocresult. > Johnny JDBC may privde some reporting of this information... I don't remember off the top of my head, but the onTap framework has a tag designed to execute stored procedures with named parameters (uses a structure, i.e. the form scope for instance). I know it's designed to allow multiple result sets, although unfortunately I don't remember offhand if the automation is driven by the code or by meta-data returned from the database. If you're interested in going that route, check out my JDBC article in the ColdFusion Developer's Journal. http://www.sys-con.com/story/?storyid=45569&de=1 Otherwise, if you have control over the stored procedure, I'd recommend rewriting it so that all of the recordsets are returned (regardless of conditions) with any unneeded recordsets simply being empty (a select statement which returns records where the table's primary key is null is an easy way to achieve this). If you need to distinguish these results from results which would otherwise return an empty recordset, you can return an output parameter from the procedure with an integer indicating the number of relevant recordsets. hth s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201362 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: recordsets return by stored procedures?
Assuming that each recordset you're returning has something that could uniquely identify it (eg a productID), I would probably return just 1 recordset, with all the data that you were returning via multiple recordsets combined - just add the ID number. Then when you're outputting or processing, just use the group attribute of cfoutput, or use QoQ. HTH, Alex -Original Message- From: Johnny Le [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 16:07 To: CF-Talk Subject: recordsets return by stored procedures? Hi, Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Johnny ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201361 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: recordsets return by stored procedures?
And unknown amount..can't you just return empty sets if now records exist (unless you are using god awful dynamic SQL within the SP! ) -Original Message- From: Johnny Le [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 16:07 To: CF-Talk Subject: recordsets return by stored procedures? Hi, Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Johnny ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201359 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
If they have the same set of column names you could do a union of the results creating a single recordset adding your own custom column to identify which set of results you are looking at: SELECT f_name, l_name, 'contact' as myGrouping FROM contacts UNION SELECT f_name, l_name, 'clients' as myGrouping FROM clients etc... But the bigger question is - why is your stored proc returning an unknown number of recordsets? This type of question usually can be answered by revisiting the stored proc to get it to generate a single recordset in the first place. If it just gets too complex a different answer would be to put the results into a temp table and return that recordset: SELECT f_name, l_name, 'contact' as myGrouping INTO #myTempTable FROM contacts INSERT INTO #myTempTable SELECT f_name, l_name, 'clients' as myGrouping FROM clients HTH, Adam Howitt On Apr 4, 2005 10:06 AM, Johnny Le <[EMAIL PROTECTED]> wrote: > Hi, > > Is there a way to find out how many recordsets return by a stored procedure? > Or is there a way to merge all the recordsets in a stored procedure so that > it would only return 1 recordset? > > I have a stored procedure that would return an unknown number of recordsets > depending on how many records we have in the database, but each recordset > will have the same number of columns and column names. I need to know the > number of recorsets it will return so I can do a loop for the cfprocresult. > > Johnny > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201358 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
recordsets return by stored procedures?
Hi, Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Johnny ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201354 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: ColdFusion 5 and Stored Procedures Issue
I ran into something today/yesterday which might be the same problem. When you use cfqueryparam coldfusion will 'pool' the sql statement - meaning that db connection will stay open with 'a' cursor to that pooled statement I specifically was having problem with a max-open-cursors error, but I suppose it could also happen with connections http://jehiah.com/archive/maximum-open-cursors-exceeded I remember getting max-processes errors before, but i'll have through my notes, because it isn't on my site. -- [EMAIL PROTECTED] http://jehiah.com/ ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:198129 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: ColdFusion 5 and Stored Procedures Issue
Gentlepersons: A bump and a few additional data points. A call to cfusion_dbconnections_flush() seems to clear up the issue, except: a) Macromedia doesn't recommend flushing manually. b) because we have multiple SPs on one page, we'd need to flush after each one to avoid hitting the connection limit with multiple concurrent users. Still doesn't explain why ColdFusion is not automatically releasing the connections. We've tried unchecking Maintain Database Connections and have set the Connection Timeout very low, but neither seems to help as much as not using Stored Procedures at all. I'll also note that most of our Stored Procedures select record sets and return them through REFCURSORS. Many of them return only a few rows (< 10). Thanks in advance for your time. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197994 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
ColdFusion 5 and Stored Procedures Issue
Greetings, CF-Talkers. We've recently been migrating a lot of our SQL code to our Oracle 8.1.7 database in the form of Stored Procedures. We currently run ColdFusion 5. Everything seemed to be going swimmingly. Yesterday, we began noticing significant performance slowdowns. Apparently, the maximum number of Oracle connections specified in the server had been reached, and inactive connections were not being released automatically. We reverted to an older codebase without Stored Procedures, and restarted the server, but that's removed quite a bit of functionality. It seems as though every time we call (certain?) stored procedures, the oracle connection is **never** considered inactive and closes/releases, as it should. Changing the Connection Timeout to a lower number has no effect -- the inactive connections stay open to our Oracle Server indefinitely. We've searched extensively, through Macromedia and CF-Talk archives, but have found no mention of this occuring elsewhere. Have no idea which procedures are causing the problem, much less why ColdFusion would leave the connections open for good. Any suggestions? Thanks in advance. Barrett ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197925 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Procedures in a CFC
> Dayum, dude... > And I thought *I* was long-winded... cl! > Someone who goes thru keyboards faster than I do! hehe Heh. I dunno... I've had this little jobber here for several years. I like it ... I actually got it at a little independant computer shop around the corner from the place where I lived with my ex in Orlando, primarily because it was so small. I was tired of (still am) seeing or working with desk-swallowing behemoths with keys or buttons for everything from email and irc to emptying my recycle bin. Although Tiff occasionally takes all the keys off to clean it, which may help with its lifespan. Though for a few months I thought it was broke because (I realized accidentally) she interposed the alt and windows keys on the left side, so I thought the left alt key was broke until I turned the machine on one day and got an unexpected response from ctrl+windows+del. > I just happened to be skimming the code example and saw > the isDefined thing. I saw it, and questioned it. Glad > you weren't put out. Nah, no reason to be. > I'm all for annoying people, except people you respect. Heh. I'll try and stay on your good side then. :) > You definitely make some good points. Especially since I > also find structKeyExists() painful and/or difficult to > type. And I ALWAYS spell it strucktKeyExists the first > time around, and it gives me many errors, and the errors > say unto me "variable undefined" and yea do I say then, > "farging iceholes, I done did it agin'..." Did you see in the newspaper the other day that Marone declared "Fargan' War"? > I use full syntax in on-list examples because I fear that > those who read it may inherit (or discover) bad habits in > my code, and they may later accuse me of being silly, lazy, > or, worse, utterly lacking in proper geek comportment. I often describe actual code as pseudocode just because it's incomplete, like if I put elipses in it with a description like ...some stuff I guess that's par for course for me -- I often tend to think and/or speak in a very open-ended fashion... sometimes. :) ENFP if Meyers/Briggs interests you at all. Though I've gotten to a point where I'm sort of "picking my battles" with regard to giving people advice on mailing lists -- or in general. It comes from noticing over the years that although people are grateful for advice which helps them solve a problem they've asked about, they often ignore or are actively put off by unsolicited advice, even if on my end it's given in good spirit. So if a guy says, "why doesn't this piece of code work" these days I try and stick more to the subject of why that code doesn't work than offering advice for general "good habbits". Otherwise I just get frustrated when I offer advice left and right only to see it ignored. There are exceptions -- I still rant about people using arrays where I think they ought to use a structure. > As far as the DB example, that's a return from a SQL > Server built-in stored procedure, and it will ALWAYS > return a minimum of 2 query objects... since my data > access layer is wrapped in a CFC and it's a one-off > call to a simple DAO to execute the sproc it seemed > much more sensible to code one function-local struct > and return the data to the caller than to write a > complicated system (it's a base DAO and 3 extension > classes as it is) wherein I have getters and setters > for anywhere between 1 and 8 resultsets, possibly > more than one sproc call (depending on which extension > class I'm calling), and all the data (or, at least > very most of the data) is always going to be needed. > Since they're not long resultsets anyway, I elected > to sacrifice a little bit of overhead on behalf of > a simpler design. Ahh, see in that context of it being a built in sproc it make a lot more sense to me. :) Given your description I'm thinking you're using SQL Server's features to perform some of the db meta-data management I accomplish in the onTap framework with JDBC methods. > Incidentally, since I was returning the data in a > struct, I discovered that I had a situation wherein > my tableDAO may return either 7 OR 8 resultsets, > depending on the structure of the table. The issue > that ensued is very difficult to explain > understandably so bear with me. > What I ended up doing was adding a private method > that reconstructs the return value on the fly, > looping over the struct generated by the sproc call > and keying off unique column names in the datasets > to reorder the struct. As I write this, I begin to > think it's probably not the best way, but... > it worked. And it proved to me that there is > definite value in API-based coding. > I hope that made sense. I didn't find that hard to follow at all... Possibly because I'm familiar with the need to perform similar "snafu-correction" myself. The onTap framework was originally designed for CF5 and when MM released MX some of the changes to the behavior of functions caused a lot of the frame
RE: Stored Procedures in a CFC
> I use structures to pass back multiple things from > functions, but I'm not so > sure you're right about not using an array. An array maps > to the > cfprocresult's resultset attribute(as it could equally to > it's name attrib) > but with the array you don't need to know the name used > only the number of > the resultset. > I hope that makes sense, I've just had a not so powerful > power nap and I > tell ya, it hasn't done anything for my thinking just this > minute. > Ade Well my comments weren't really with regard to the procedure itself per se... I realize that the stored procedure only offers the number of the result set to inform you about what's in it (unless you get creative with output variables), but rather, if I'm just looking at a piece of code and I see this: ... That's going to make a heck of a lot _less_ sense to me reading it as this: ... The latter is rather self-documenting -- you know simply from reading it what it is... The former, having only a number is obscure and leads to programmers wondering what the hell is going on and then needing to go investigate further in order to understand it. This is in general a frequent problem with code that I see submitted to the cf-talk list (or in other places), I think particularly with programmers who come to CF from other languages where associative arrays are less friendly to use, like Java or ASP, where rather than using the extraordinarily simple and self-explanatory CF structure, they resort to an array and a collection of numeric variables. I see this frequently in Java class documentation on the Sun site, where the class has public properties like STATUS_AVAILABLE = 1 : STATUS_CANCELLED = 2 : and then in order to actually know what the object is doing you have to compare method results to these public properties instead of simply using the method result. And this sort of thing I _think_ often translates when people come to CF from other languages to ugly codeblocks like this: Instead of something more efficient, elegant, legible and bulletproof like this: Granted that I have taken the liberty of using a function to convert the query into a structure in this example (a good habbit anyway), which also eliminates a good bit of the code, but just simply not having the local variables for productname, qty, unitcost and totalcost just to be able to know where the item is in the array is a subtle but powerful thing. Consider for a moment if you hand off the original code using the array to another programmer who'd hever seen the code before or never worked with ColdFusion and they need to debug it. You might expect if they've read about ColdFusion a little they might use to display the contents of the shopping cart... and to their shock and dismay they would be met with a huge list of completely arbitrary and meaningless numbers, each associated with an arbitrary string which may or may not be what they think it is. Then they'd have to start comparing the local variables productname, qty, unitcost and totalcost to those numbers in order to figure out what's what. If they're bright, they'll figure this out right away and they'll start making the association as they flip back and forth between their browser and the code and they'll probably figure it out in a reasonably short period of time, but honestly, why put them through the trouble? Why make it more difficult for them - why make them have to think about that association of the arbitrary number "2" to the intuitively obvious name "qty" at all when it's _easier_ to just use the CF native structure and let them know _instantaneously_ what everything is from looking at the dump output? Anyway -- that's the end of my rant. :) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=1 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197570 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
