CFSTOREDPROC ?
Hello All: CFSTOREDPROC ? Im on an adventure with my SQL 7.0 server and I have a question. What Im attempting to do is run a that generates a large CSV file from an executed DTS package and then output that file to a network share which the DTS will take care of. Next Im going to have my CF server zip that file and serve it to the user. So happy they will be to have their data. 1. While that file is being generated and ColdFusion is zipping up that file is there a way to track the progress and let the user know how long they have to wait? 2. Can I lock the CSV file thats being created in case someone else makes the same requeest tring to over write the file being created by the DTS package? A Special Thanks Today To: Dan Switzers, write up on how to execute a DTS package from a stored procedure talk about cool. You can see his example at: http://www.pengoworks.com/index.cfm?action=articles:spExecuteDTS Regards, James Blaha ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CFStoredProc
Ok, gang, The following stored proc call works beautifully: It inserts a record into our test table and returns the ADID (INTEGER, autonumber) and DESC (CHAR, defaults to "TEST") fields. Then I try to pass the proc a value for DESC as follows: And I get the following error: Error Diagnostic Information DB2 Error Code = -10013 [IBM][CLI Driver][DB2/SUN] SQL10013N The specified library "/export/home/testingUser/sqllib/function/test" could not be loaded. SQL = "test" Data Source = "TESTING" The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (15:1) to (15:61). Date/Time: Tue Feb 11 13:56:01 2003 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; T312461; .NET CLR 1.0.3705) Remote Address: I'm stumped, any thoughts? Thanks, Ryan ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfstoredproc
Hi, i am getting a problem when i try to use stored procedures that are stored in the mysql database. the error that is coming up is java.lang.NullPointerException it is coming up with this error in the following cfstoredproc code: the stored procedure in the mysql database is: DELIMITER // CREATE PROCEDURE getStudentDetails (IN email VARCHAR(50)) BEGIN SELECT studentTitle, studentFirstName, studentLastName FROM STUDENTS WHERE studentEmail = email; END; // DELIMITER ; i would appreciate any help on why this error may be occuring. The datasource has shown to connect successfully. thanks for any help ~| 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:257344 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfstoredproc
I would really appreciate it if someone could Show me a simple example of how to use cfstoreproc With a MSSQL stored procedure and then the code to Display the results of that. I have worked with Access for years and am relatively new To MSSQL and have never used that tag. We have a new software In-house that uses MSSQL and the company that wrote it utilizes Stored procedures a lot, so I need to get up to speed on using CF with it. Terry Troxel ~| 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:259103 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
CFStoredProc
This should have been simple as making a PB&J. I am calling a SP (SQL Server 2005) using the CFStoredProc function and to get the result set returned as "nextval". Basically it should return an interget value. Now if I try to use CFDump to dump the results set "nextval" it indicates the variable does not exist. What is it I am missing? It has ot be something I can say "DOH" about. ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293036 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cfstoredproc timeout
Hi, I want to limit the total execution time of a stored procedure being called from Coldfusion using to 90 seconds. With cfquery there is a parameter timeout, but there is no equivalent in cfstoredproc. Can anyone think of an alternative to accomplishing this. The trick is, I want to wrap the stored procedure call around a cftry block and create an empty query if the procedure times out. Thanks, Asaf ~| 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:333251 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFSTOREDPROC error
Hi all, Had a strange error on a page that's been bug-free and unmodified for months. It uses CFSTOREDPROC, which I haven't used much in the past, so I thought I'd throw it to CF Talk. Unknown Data Access Error. The error occurred while processing an element with a general identifier of (CFSTOREDPROC) The stored proc has 3 in parameters, 1 out parameter and no return code. David Cummins ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
cfstoredproc behavior
I have a dynamic query that I was trying to use the tag with, but all I kept getting was the generic "Unknown data access" error. So I re-wrote it with and it works fine, but I can't see any difference in the two. Can anyone here see why the version wouldn't work? version (unknown data access error): version (works): sp_web_cf_softrevs_insert_update @row_id = , @mfg = , @tech_type = , @begdate = , @enddate = , @notes = , @#col_sql_name#= , The qColumns query contains a list of dynamic columns that appear or dissapear on the form based on mfg. CFSQLType is a udf I wrote that returns the cold fusion cfsqltype given a SQL Server data type. This is running on Win2k, CF5, SQL Server 2000 with all of the latest patches. Thanks in advance, Andy ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
cfc & cfstoredproc
Hi, How do we pass the result set of a cfstoredproc call to a cfc? TIA, Ro ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFSTOREDPROC ?
2) It is recommended that you always use with . You would do an exclusive lock for writing/deleting/copying. You would do a readonly lock for reading. I'm not sure about CFMX though. I'm fairly certain that the java.io classes aren't threadsafe. - Original Message - From: James Blaha <[EMAIL PROTECTED]> Date: Thursday, March 13, 2003 1:26 pm Subject: CFSTOREDPROC ? > Hello All: > > CFSTOREDPROC ? > > Im on an adventure with my SQL 7.0 server and I have a question. > What > Im attempting to do is run a that generates a > large CSV > file from an executed DTS package and then output that file to a > network > share which the DTS will take care of. Next Im going to have my > CF > server zip that file and serve it to the user. So happy they will > be to > have their data. > > 1. While that file is being generated and ColdFusion is zipping up > that > file is there a way to track the progress and let the user know > how long > they have to wait? > > 2. Can I lock the CSV file thats being created in case someone > else > makes the same requeest tring to over write the file being created > by > the DTS package? > > > A Special Thanks Today To: Dan Switzers, write up on how to > execute a > DTS package from a stored procedure talk about cool. > > You can see his example at: > > http://www.pengoworks.com/index.cfm?action=articles:spExecuteDTS > > > > Regards, > James Blaha > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CFSTOREDPROC ?
In this case where James is creating a CSV on the fly, he could name the file uniquely. I usually concatenate a file name with CreatUUID(). This way the file name is unique to the request, well as unique as the createUUID() function is. Doug >-Original Message- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >Sent: Thursday, March 13, 2003 4:04 PM >To: CF-Talk >Subject: Re: CFSTOREDPROC ? > > >2) It is recommended that you always use with >. You would do an exclusive lock for >writing/deleting/copying. You would do a readonly lock for reading. > >I'm not sure about CFMX though. I'm fairly certain that the >java.io classes aren't threadsafe. > > > >- Original Message - >From: James Blaha <[EMAIL PROTECTED]> >Date: Thursday, March 13, 2003 1:26 pm >Subject: CFSTOREDPROC ? > >> Hello All: >> >> CFSTOREDPROC ? >> >> Im on an adventure with my SQL 7.0 server and I have a question. >> What >> Im attempting to do is run a that generates a >> large CSV >> file from an executed DTS package and then output that file to a >> network >> share which the DTS will take care of. Next Im going to have my >> CF >> server zip that file and serve it to the user. So happy they will >> be to >> have their data. >> >> 1. While that file is being generated and ColdFusion is zipping up >> that >> file is there a way to track the progress and let the user know >> how long >> they have to wait? >> >> 2. Can I lock the CSV file thats being created in case someone >> else >> makes the same requeest tring to over write the file being created >> by >> the DTS package? >> >> >> A Special Thanks Today To: Dan Switzers, write up on how to >> execute a >> DTS package from a stored procedure talk about cool. >> >> You can see his example at: >> >> http://www.pengoworks.com/index.cfm?action=articles:spExecuteDTS >> >> >> >> Regards, >> James Blaha >> >> >> > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFSTOREDPROC ?
Doug, Wow, sounds good but how and where do I do this. The CSV file is created from the DTS package, I just call it from the stored PROC. Do you have an example for what I need to modify? Regards, James Blaha [EMAIL PROTECTED] wrote: >In this case where James is creating a CSV on the fly, he could name the file >uniquely. I usually concatenate a file name with CreatUUID(). This way the file >name is unique to the request, well as unique as the createUUID() function is. > >Doug > > >>-Original Message- >>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >>Sent: Thursday, March 13, 2003 4:04 PM >>To: CF-Talk >>Subject: Re: CFSTOREDPROC ? >> >> >>2) It is recommended that you always use with >>. You would do an exclusive lock for >>writing/deleting/copying. You would do a readonly lock for reading. >> >>I'm not sure about CFMX though. I'm fairly certain that the >>java.io classes aren't threadsafe. >> >> >> >>- Original Message - >>From: James Blaha <[EMAIL PROTECTED]> >>Date: Thursday, March 13, 2003 1:26 pm >>Subject: CFSTOREDPROC ? >> >> >> >>>Hello All: >>> >>>CFSTOREDPROC ? >>> >>>Im on an adventure with my SQL 7.0 server and I have a question. >>>What >>>Im attempting to do is run a that generates a >>>large CSV >>>file from an executed DTS package and then output that file to a >>>network >>>share which the DTS will take care of. Next Im going to have my >>>CF >>>server zip that file and serve it to the user. So happy they will >>>be to >>>have their data. >>> >>>1. While that file is being generated and ColdFusion is zipping up >>>that >>>file is there a way to track the progress and let the user know >>>how long >>>they have to wait? >>> >>>2. Can I lock the CSV file thats being created in case someone >>>else >>>makes the same requeest tring to over write the file being created >>>by >>>the DTS package? >>> >>> >>>A Special Thanks Today To: Dan Switzers, write up on how to >>>execute a >>>DTS package from a stored procedure talk about cool. >>> >>>You can see his example at: >>> >>>http://www.pengoworks.com/index.cfm?action=articles:spExecuteDTS >>> >>> >>> >>>Regards, >>>James Blaha >>> >>> >>> >>> >>> > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CFStoredProc
I've found that cfcprocresult should be last. (Oracle 8i/9i). Try that. > -Original Message- > From: Nuovo [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 11, 2003 3:13 PM > To: CF-Talk > Subject: CFStoredProc > > > Ok, gang, > > The following stored proc call works beautifully: > > > > > > It inserts a record into our test table and returns the ADID > (INTEGER, autonumber) and DESC (CHAR, defaults to "TEST") fields. > > Then I try to pass the proc a value for DESC as follows: > > > > cfsqltype="CF_SQL_CHAR" type="In"> > > > And I get the following error: > > Error Diagnostic Information > DB2 Error Code = -10013 > > [IBM][CLI Driver][DB2/SUN] SQL10013N The specified > library "/export/home/testingUser/sqllib/function/test" could > not be loaded. > > > > SQL = "test" > > Data Source = "TESTING" > > > The error occurred while processing an element with a > general identifier of (CFSTOREDPROC), occupying document > position (15:1) to (15:61). > > > Date/Time: Tue Feb 11 13:56:01 2003 > Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT > 5.0; T312461; .NET CLR 1.0.3705) > Remote Address: > > > > > I'm stumped, any thoughts? > > Thanks, > > Ryan > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFStoredProc
Thanks, but that didn't work. We're using DB2, Solaris 8, and Apache 2.13. - Original Message - From: "<"Everett>,Al"" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, February 11, 2003 2:17 PM Subject: RE: CFStoredProc > I've found that cfcprocresult should be last. (Oracle 8i/9i). Try that. > > > -Original Message- > > From: Nuovo [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, February 11, 2003 3:13 PM > > To: CF-Talk > > Subject: CFStoredProc > > > > > > Ok, gang, > > > > The following stored proc call works beautifully: > > > > > > > > > > > > It inserts a record into our test table and returns the ADID > > (INTEGER, autonumber) and DESC (CHAR, defaults to "TEST") fields. > > > > Then I try to pass the proc a value for DESC as follows: > > > > > > > > > cfsqltype="CF_SQL_CHAR" type="In"> > > > > > > And I get the following error: > > > > Error Diagnostic Information > > DB2 Error Code = -10013 > > > > [IBM][CLI Driver][DB2/SUN] SQL10013N The specified > > library "/export/home/testingUser/sqllib/function/test" could > > not be loaded. > > > > > > > > SQL = "test" > > > > Data Source = "TESTING" > > > > > > The error occurred while processing an element with a > > general identifier of (CFSTOREDPROC), occupying document > > position (15:1) to (15:61). > > > > > > Date/Time: Tue Feb 11 13:56:01 2003 > > Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT > > 5.0; T312461; .NET CLR 1.0.3705) > > Remote Address: > > > > > > > > > > I'm stumped, any thoughts? > > > > Thanks, > > > > Ryan > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CFStoredProc
could it be because 'DESC' is a reserved term? -- Ben Doom Programmer & General Lackey Moonbow Software, Inc : -Original Message- : From: Ryan [mailto:[EMAIL PROTECTED]] : Sent: Tuesday, February 11, 2003 3:26 PM : To: CF-Talk : Subject: Re: CFStoredProc : : : Thanks, but that didn't work. We're using DB2, Solaris 8, and : Apache 2.13. : : : - Original Message - : From: "<"Everett>,Al"" <[EMAIL PROTECTED]> : To: "CF-Talk" <[EMAIL PROTECTED]> : Sent: Tuesday, February 11, 2003 2:17 PM : Subject: RE: CFStoredProc : : : > I've found that cfcprocresult should be last. (Oracle 8i/9i). Try that. : > : > > -Original Message- : > > From: Nuovo [mailto:[EMAIL PROTECTED]] : > > Sent: Tuesday, February 11, 2003 3:13 PM : > > To: CF-Talk : > > Subject: CFStoredProc : > > : > > : > > Ok, gang, : > > : > > The following stored proc call works beautifully: : > > : > > : > > : > > : > > : > > It inserts a record into our test table and returns the ADID : > > (INTEGER, autonumber) and DESC (CHAR, defaults to "TEST") fields. : > > : > > Then I try to pass the proc a value for DESC as follows: : > > : > > : > > : > > > cfsqltype="CF_SQL_CHAR" type="In"> : > > : > > : > > And I get the following error: : > > : > > Error Diagnostic Information : > > DB2 Error Code = -10013 : > > : > > [IBM][CLI Driver][DB2/SUN] SQL10013N The specified : > > library "/export/home/testingUser/sqllib/function/test" could : > > not be loaded. : > > : > > : > > : > > SQL = "test" : > > : > > Data Source = "TESTING" : > > : > > : > > The error occurred while processing an element with a : > > general identifier of (CFSTOREDPROC), occupying document : > > position (15:1) to (15:61). : > > : > > : > > Date/Time: Tue Feb 11 13:56:01 2003 : > > Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT : > > 5.0; T312461; .NET CLR 1.0.3705) : > > Remote Address: : > > : > > : > > : > > : > > I'm stumped, any thoughts? : > > : > > Thanks, : > > : > > Ryan : > > : > : ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFStoredProc
It's possible, Ben, but we've tried this with a non-testing table that doesn't have any reserved words as field names and we get the same error. Good eye, though, good eye, mmhmmm. Ryan - Original Message - From: "Ben Doom" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, February 11, 2003 2:47 PM Subject: RE: CFStoredProc > could it be because 'DESC' is a reserved term? > > > -- Ben Doom > Programmer & General Lackey > Moonbow Software, Inc > > : -Original Message- > : From: Ryan [mailto:[EMAIL PROTECTED]] > : Sent: Tuesday, February 11, 2003 3:26 PM > : To: CF-Talk > : Subject: Re: CFStoredProc > : > : > : Thanks, but that didn't work. We're using DB2, Solaris 8, and > : Apache 2.13. > : > : > : - Original Message - > : From: "<"Everett>,Al"" <[EMAIL PROTECTED]> > : To: "CF-Talk" <[EMAIL PROTECTED]> > : Sent: Tuesday, February 11, 2003 2:17 PM > : Subject: RE: CFStoredProc > : > : > : > I've found that cfcprocresult should be last. (Oracle 8i/9i). Try that. > : > > : > > -Original Message- > : > > From: Nuovo [mailto:[EMAIL PROTECTED]] > : > > Sent: Tuesday, February 11, 2003 3:13 PM > : > > To: CF-Talk > : > > Subject: CFStoredProc > : > > > : > > > : > > Ok, gang, > : > > > : > > The following stored proc call works beautifully: > : > > > : > > > : > > > : > > > : > > > : > > It inserts a record into our test table and returns the ADID > : > > (INTEGER, autonumber) and DESC (CHAR, defaults to "TEST") fields. > : > > > : > > Then I try to pass the proc a value for DESC as follows: > : > > > : > > > : > > > : > > : > > cfsqltype="CF_SQL_CHAR" type="In"> > : > > > : > > > : > > And I get the following error: > : > > > : > > Error Diagnostic Information > : > > DB2 Error Code = -10013 > : > > > : > > [IBM][CLI Driver][DB2/SUN] SQL10013N The specified > : > > library "/export/home/testingUser/sqllib/function/test" could > : > > not be loaded. > : > > > : > > > : > > > : > > SQL = "test" > : > > > : > > Data Source = "TESTING" > : > > > : > > > : > > The error occurred while processing an element with a > : > > general identifier of (CFSTOREDPROC), occupying document > : > > position (15:1) to (15:61). > : > > > : > > > : > > Date/Time: Tue Feb 11 13:56:01 2003 > : > > Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT > : > > 5.0; T312461; .NET CLR 1.0.3705) > : > > Remote Address: > : > > > : > > > : > > > : > > > : > > I'm stumped, any thoughts? > : > > > : > > Thanks, > : > > > : > > Ryan > : > > > : > > : > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CFStoredproc problem..
Hi, Here I am giving the testcode which I executed in cfmx version and CF4.5.1 version. But I am expecting value '4' from following code. I will be getting that value in only CF4.5.1 but not in CFMX. Is it unknown cfmx bug or do we need to change any attributes kind of things when we use cfstoreproc in cfmx. Really appreciated for your suggestions also.. = #test# test value is 4 in cf4.5.1 and test value is 0 in cfmx .. why it is so like that.. ? Thanks in advance.. Best regards Sachin. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfstoredproc generator
Does anyone have a program that will generate cfstoredproc statements for SQL Server stored procedures laying around? I had one...and I even built one in the past but, I don't have mine here, and the one from the (oh so reliable) dev exchange is not available anymore apparently. I have like 30 sp's to get into CF and I really don't want to do them all by hand :) -- jon mailto:[EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
cfstoredproc breakthrough !!
Hi All, I'm actually rather excited. I just posted another entry to my blog about a breakthrough I've just had with the cfstoredproc tag. In the past, the fact that the dbvarname attribute of the cfprocparam tag is ignored by the ColdFusion Server has really frustrated me at times. I believe I've found a way to make the dbvarname attribute work (among other things). All my findings are on the blog. I'm sure the custom tag I designed will need debugging, and I welcome any feedback anyone has. Thanks! http://www.turnkey.to/ontap/blog/?20040124 s. isaac dealey 214-823-9345 team macromedia volunteer http://www.macromedia.com/go/team chief architect, tapestry cms http://products.turnkey.to onTap is open source http://www.turnkey.to/ontap [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
cfstoredproc problem
I have this strange problem happening with my stored procedure call...
somehow the last two cfprocparam's are not being included. I tried
switching the orders around but its always the last two that are not being
sent to the stored proc... Please help!
Thanks,
Tim
here is my code:
returncode="Yes">
type="In" value="#active#">
type="In" value="#litigated#">
null="No" type="In" value="#environmental#">
type="In" value="#incidentOnly#">
null="#iif(incidentStart NEQ "", DE("No"), DE("Yes"))#" type="In"
value="#incidentStart#">
null="#iif(incidentEnd NEQ "", DE("No"), DE("Yes"))#" type="In"
value="#incidentEnd#">
null="#iif(closedStart NEQ "", DE("No"), DE("Yes"))#" type="In"
value="#closedStart#">
null="#iif(closedEnd NEQ "", DE("No"), DE("Yes"))#" type="In"
value="#closedEnd#">
null="#iif(IsDefined("Form.assignStart"), DE("No"), DE("Yes"))#" type="In"
value="#iif(IsDefined("Form.assignStart"), "Form.assignStart", DE(""))#">
null="#iif(IsDefined("Form.assignEnd"), DE("No"), DE("Yes"))#" type="In"
value="#iif(IsDefined("Form.assignEnd"), "Form.assignEnd", DE(""))#">
null="#iif(IsDefined("Form.reportedStart"), DE("No"), DE("Yes"))#" type="In"
value="#iif(IsDefined("Form.reportedStart"), "Form.reportedStart",
DE(""))#">
null="#iif(IsDefined("Form.reportedEnd"), DE("No"), DE("Yes"))#" type="In"
value="#iif(IsDefined("Form.reportedEnd"), "Form.reportedEnd", DE(""))#">
null="#iif(buildingNumber NEQ "", DE("No"), DE("Yes"))#" type="In"
value="#buildingNumber#">
value="#iif(IsDefined("wnc"), "wnc", DE("2"))#">
null="#iif(IsDefined("Form.incidentType"), DE("No"), DE("Yes"))#" type="In"
value="#iif(IsDefined("Form.incidentType"), "Form.incidentType", DE(""))#">
and here is part of my stored proc:
CREATE PROC Get_Claims
(
@active integer
, @litigated integer
, @environmental integer
, @incidentOnly integer
, @incidentStart datetime
, @incidentEnd datetime
, @closedStart datetime
, @closedEnd datetime
, @assignStart datetime
, @assignEnd datetime
, @reportedStart datetime
, @reportedEnd datetime
, @buildingNumber varchar(6)
, @wnc integer
, @incidentType integer)
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
cfset/cfstoredproc
This is somewhat of a repost from the other day but haven't gotten any feedback- since I don't think I worded my problem very well. I am moving some of my work from cfquery to storedprocedures- the problem is the formatting when I use cfset with a stored procedures- they don't react the same way as a cfquery does... I need the info to show as such Mark: 1. A 2. B 3. A Mary 1. A 2. A 3. D John 1. C 2. A 3. B To achieve this with cfquery I did this: If I do the same with a stored procedure I end up with Mark: 1. A 2. B 3. A Mary 1. A 2. B 3. A John 1. A 2. B 3. A I can't use just a regular output like Ben Forta shows in this CF5 book-I've tried all his solutions but they all deal with regular outputs(which works fine with the cfquery and cfstored proc)- I need to use a cfset statement because each answer needs its own name. Has anyone run across this? What's the best solution? __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
cfstoredproc problem
I have been at this for the past 35 minutes and I just can't figure it out. This cfstoredproc just isn't working. I've tested the actual stored procedure on SQL Server by placing some actual values in the stored procedure and it worked. But when I try to use this cfstoredproc to do the stored procedure it doesn't do anything. The #cfstoredproc.statuscode# is even at 0> can you see anything wrong? here is my stored procedure: UPDATE PasUsers SET picture = '@picture' WHERE userid = '@userid' ~ Paid Sponsorship ~ Get Your Own Dedicated Win2K Server! Instant Activation for $99/month w/Free Setup from SoloServer PIII600 / 128 MB RAM / 20 GB HD / 24/7/365 Tech Support Visit SoloServer, https://secure.irides.com/clientsetup.cfm. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
CFSTOREDPROC Error!
I'm receiving the following error intermittently when using the CFSTOREDPROC tag to run a stored procedure that returns 3 result sets: Error Diagnostic Information unknown exception condition PCodeRuntimeContextImp::executeSQLTagCFStoredProc::endTag The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (26:1) to (26:96) in the template file D:\DEVWEB\INETPUB\WWWROOT\NATIVETRAX\QUERIES\QRY_PRODUCERTRANSACTIONREPORT.C FM. My CFSTOREDPROC tag reads: The procedure ProducerTransactionReport in turn runs two stored procedures, the first of which returns one result set and the second of which returns two result sets. The strange thing is that sometimes it works fine, and returns all three result sets, while other times it returns two result sets, and then issues the above error message. In each of the stored procedures I SET NOCOUNT ON at the beginning and SET NOCOUNT OFF at the end. This is ColdFusion Professional 4.5.1 SP2 running on W2K Server, and the database is MS SQL Server 2000, being accessed via OLE DB. If anyone has seen this before and/or has any suggestions I would appreciate hearing from you. Thanks, Bob ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cfstoredproc
Shouldnt the storedproc be... WHERE studentEmail = @email -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Thursday, October 19, 2006 9:36 AM To: CF-Talk Subject: cfstoredproc Hi, i am getting a problem when i try to use stored procedures that are stored in the mysql database. the error that is coming up is java.lang.NullPointerException it is coming up with this error in the following cfstoredproc code: the stored procedure in the mysql database is: DELIMITER // CREATE PROCEDURE getStudentDetails (IN email VARCHAR(50)) BEGIN SELECT studentTitle, studentFirstName, studentLastName FROM STUDENTS WHERE studentEmail = email; END; // DELIMITER ; i would appreciate any help on why this error may be occuring. The datasource has shown to connect successfully. thanks for any help ~| 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:257349 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfstoredproc
Does the stored proc work in MySQL (not running it from CF)? I wonder if the problem might be the cfsqltype. You could try a different type, to narrow it down some. > -Original Message- > From: Richard White [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 19, 2006 7:36 AM > > Hi, i am getting a problem when i try to use stored > procedures that are stored in the mysql database. > > the error that is coming up is java.lang.NullPointerException > > it is coming up with this error in the following cfstoredproc code: > > >value="[EMAIL PROTECTED]" null="no"> > > > > the stored procedure in the mysql database is: > > DELIMITER // > CREATE PROCEDURE getStudentDetails (IN email VARCHAR(50)) > BEGIN > SELECT studentTitle, studentFirstName, studentLastName FROM > STUDENTS WHERE studentEmail = email; > END; > // > DELIMITER ; "EMF " made the following annotations. -- This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. == ~| 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:257360 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfstoredproc
thanks for the replies, i have tested it in mysql and it executes fine and displays the results. if the stored procedure expects a varchar do you have any suggesstions on what i may try i am just wondering also why it is saying null pointer, it sounds like its not detecting the stored procedure. i have run the query in a cfquery tag and it works fine so not sure what the null pointer is referring to 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:257365 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfstoredproc
i have also checked the privileges in mysql, i have no idea why this is happening. Can anyone else using mysql and the stored procedure tag see a problem with the code or provide advice on what i need to check. Id really appreciate some help, ive been on this all day with no luck :( thanks very much ~| 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:257492 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfstoredproc
i have tried a very simple example with getStudentDetails just being select studentFirstName from students, this stored procedure took no parameters. and it is still coming out with the error null pointer it looks like for some reason the cfstoredproc is not finding the stored procedure in mysql, i have done all the checks in the db and it is fine. I am wondering if there is something i have to do to make sure it finds it??? 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:257493 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfstoredproc
I recommend you check out this article by Samuel Neff: Learning Stored Procedure Basics in ColdFusion MX http://www.adobe.com/devnet/coldfusion/articles/stored_procs.html "Most ColdFusion applications center around a database. The key to creating efficient and secure ColdFusion applications is ensuring that all database interaction are both efficient and secure. Stored procedures help developers accomplish both of these goals and also accomplish greater abstraction between the application code and database details. In this article, we'll explore stored procedures and review a few examples using Oracle, Microsoft SQL Server (MSSQL), and Microsoft Access." See also this by Darryl Bantarri: A Beginner's Guide to using Stored Procedures in ColdFusion http://www.adobe.com/devnet/coldfusion/articles/beg_storedproc.html -Steven Erat > -Original Message- > From: Terry Troxel [mailto:[EMAIL PROTECTED] > Sent: Friday, November 03, 2006 1:04 PM > To: CF-Talk > Subject: cfstoredproc > > I would really appreciate it if someone could > Show me a simple example of how to use cfstoreproc > With a MSSQL stored procedure and then the code to > Display the results of that. > > I have worked with Access for years and am relatively new > To MSSQL and have never used that tag. We have a new > software > In-house that uses MSSQL and the company that wrote it > utilizes > Stored procedures a lot, so I need to get up to speed on > using > CF with it. > > Terry Troxel ~| 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:259111 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfstoredproc
Hi Terry, Hope this helps I have popped some cf code together and a dummy stored procedure: - type="In" cfsqltype="CF_SQL_INTEGER"> #id# CREATE procedure risp_InsertExAdminReportCategories @id int as /* PROCEDURE:sp_SomeStoredProcedureName VERSION HISTORY Version Date WhoDescription --- --- --- -- 1.0 03 November 2006 Jose Diaz Baseline version --- --- -- */ SELECT * FROM tblYourTableName WHERE tblYourTableName.id = @id ORDER BY iEventID, iExhibitorID Thanks Jose Diaz On 11/3/06, Terry Troxel <[EMAIL PROTECTED]> wrote: > > I would really appreciate it if someone could > Show me a simple example of how to use cfstoreproc > With a MSSQL stored procedure and then the code to > Display the results of that. > > I have worked with Access for years and am relatively new > To MSSQL and have never used that tag. We have a new > software > In-house that uses MSSQL and the company that wrote it > utilizes > Stored procedures a lot, so I need to get up to speed on > using > CF with it. > > Terry Troxel > > > ~| 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:259116 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfstoredproc
The returned data would be accessed like this #resultset.fieldname#, resultset bing the value that you used for cfprocresult HTH sas Scott A. Stewart REAC/PASS-IT (202)-475-8875 "Terry Troxel" <[EMAIL PROTECTED]> 11/03/2006 01:03 PM Please respond to cf-talk To: CF-Talk cc: (bcc: Scott A. Stewart/REAC/HHQ/HUD) Subject:cfstoredproc I would really appreciate it if someone could Show me a simple example of how to use cfstoreproc With a MSSQL stored procedure and then the code to Display the results of that. I have worked with Access for years and am relatively new To MSSQL and have never used that tag. We have a new software In-house that uses MSSQL and the company that wrote it utilizes Stored procedures a lot, so I need to get up to speed on using CF with it. Terry Troxel ~| 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:259118 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Cfstoredproc message
Simple question, I hope How does one get back to CF the message a stored proc normally puts in the messages window when you run it in SQL Server management studio or enterprise manager? Thanks in advance Richard (I've hunted around but difficult to search for the word 'message'...) ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270739 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
CFStoredProc bug?
Okay, I just ran across this and I'm not sure if it's something that has been looked into before but it does give me reason for concern. I have a stored procedure, up_get_status, that takes two parameters, @nStatus_id and @cSearch. It is basically SELECT cLabel FROM Status WHERE nStatus_ID = CASE WHEN @nStatus_id = 0 THEN nStatus_id ELSE @nStatus_id END AND cLabel = CASE WHEN @cSearch IS NULL THEN cLabel ELSE @cSearch END I have the parameters set with defaults in the stored proceudure. In my code I have the following call (taking it out of the cffunction since I'm doing more than just calling this one table): When I run this I get a SQL error "Error converting data type varchar to int". This is the same result I get when I try to run 'up_get_status 'Pending'' as opposed to 'up_get_status @cSearch='Pending' ' Is this a new thing?? Hatton ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188390 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
CFStoredProc Question
I'm cleaning up some old code, can anyone say why this returns records: execute SearchCases '#Search#', '#ReferralType#', '#CaseType#', '#NumberType#' And this doesn't: Here's the sp: CREATE PROCEDURE [SearchCases] @Search varchar( 20), @ReferralType varchar(5), @CaseType int, @NumberType varchar(4) AS DECLARE @sqlString varchar(2000) DECLARE @searchVar varchar(30) SET @searchVar = '%' + @Search + '%' SET @sqlString = "SELECT TOP 500 Cases.CaseID, SUBSTRING((Parties.First + ' ' + Parties.Last),1,15) AS NCPName,SUBSTRING((Parties1.First + ' ' + Parties1.Last),1,35) AS CPName, Cases.FileNumber,Cases.CaseNumber, Parties1.SS " SET @sqlString = @sqlString + "FROM Cases LEFT OUTER JOIN Parties ON Cases.CaseID = Parties.CaseID LEFT OUTER JOIN Parties Parties1 ON Cases.CaseID = Parties1.CaseID " SET @sqlString = @sqlString + "WHERE (Parties.PartyType = 'CP') AND (Parties1.PartyType = 'NCP') AND ((Parties.Last LIKE '" + @searchVar + "') OR (Parties1.First LIKE '" + @searchVar + "') OR (Parties1.Middle LIKE '" + @searchVar + "') OR (Parties1.Last LIKE '" + @searchVar + "')" IF @NumberType = 'FILE' BEGIN SET @sqlString = @sqlString + " OR (Cases.FileNumber LIKE '" + @searchVar + "') " END IF @NumberType = 'CASE' BEGIN SET @sqlString = @sqlString + " OR (Cases.CaseNumber LIKE '" + @searchVar + "') " END IF @NumberType = 'CSE' BEGIN SET @sqlString = @sqlString + " OR (Cases.CSECaseNumber LIKE '" + @searchVar + "') " END IF @NumberType = 'REF' BEGIN SET @sqlString = @sqlString + " OR (Cases.ReferralID LIKE '" + @searchVar + "') " END IF @NumberType = 'SSN' BEGIN SET @sqlString = @sqlString + " OR (Parties1.SS LIKE '" + @searchVar + "') OR (Parties.SS LIKE '" + @searchVar + "') " END IF @NumberType = 'ALL' BEGIN SET @sqlString = @sqlString + " OR (Parties1.SS LIKE '" + @searchVar + "') OR (Parties.SS LIKE '" + @searchVar + "') OR (Cases.CaseNumber LIKE '" + @searchVar + "') OR (Cases.FileNumber LIKE '" + @searchVar + "') OR (Cases.CSECaseNumber LIKE '" + @searchVar + "') OR (Cases.ReferralID LIKE '" + @searchVar + "') " END SET @sqlString = @sqlString + " )" IF @ReferralType = 'E' BEGIN SET @sqlString = @sqlString + "AND ReferralType = 'E'" END IF @ReferralType = 'R' BEGIN SET @sqlString = @sqlString + "AND ReferralType <> 'E'" END IF @CaseType = 0 BEGIN SET @sqlString = @sqlString + "AND ArchiveBox = '' AND Closed IS NULL " END IF @CaseType = 1 BEGIN SET @sqlString = @sqlString + "AND (ArchiveBox IS NOT NULL AND ArchiveBox > ' ') " END IF @CaseType = 3 BEGIN SET @sqlString = @sqlString + "AND ArchiveBox = '' AND WarrantDate IS NOT NULL " END IF @CaseType = 4 BEGIN SET @sqlString = @sqlString + "AND Closed IS NOT NULL " END SET @sqlString = @sqlString + 'ORDER BY Cases.FileNumber' /* SELECT @sqlString */ EXEC( @sqlString ) GO ~| 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:192982 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: CFStoredProc
> I am calling a SP (SQL Server 2005) using the > CFStoredProc function and to get the result > set returned as "nextval". Basically it should > return an interget value. > > ... > > What is it I am missing? Without seeing the SP code, who can say? My guess is that your SP is returning an output parameter, rather than SELECTing a 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! ~| 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:293038 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFStoredProc
It looks like you are missing the DBVAR name. CF Developer wrote: > This should have been simple as making a PB&J. > > I am calling a SP (SQL Server 2005) using the > CFStoredProc function and to get the result set returned as "nextval". > Basically it should return an interget value. > >datasource="#application.datasource#" procedure="usp_select_nextval"> > > > > > Now if I try to use CFDump to > dump the results set "nextval" it indicates the variable does not exist. > > What is it I am missing? It has ot be something I can say "DOH" about. > > > > ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293041 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFStoredProc
cfprocresult is for returning a query result set. If you're not returning query, then you should use: CF Developer wrote: > > This should have been simple as making a PB&J. > > I am calling a SP (SQL Server 2005) using the > CFStoredProc function and to get the result set returned as "nextval". > Basically it should return an interget value. > >datasource="#application.datasource#" procedure="usp_select_nextval"> > > > > > Now if I try to use CFDump to > dump the results set "nextval" it indicates the variable does not exist. > > What is it I am missing? It has ot be something I can say "DOH" about. > > > > > > ~| 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:293042 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFStoredProc
The storedProc only returns a single interget value not a database object or field. It runs a Query, gets a value, increments the value, updates the table and returns the new calculated value. such as: database table as a field called: thisVal and that field is set to 1 It will increment it to 2, updates the table and set to return 2 as: return @nextVal I thought the dbvarname is used to specify the FIELD that you would want to be returned. From: Bruce Sorge <[EMAIL PROTECTED]> Sent: Friday, November 09, 2007 8:24 AM To: CF-Talk Subject: Re: CFStoredProc It looks like you are missing the DBVAR name. CF Developer wrote: > This should have been simple as making a PB&J. > > I am calling a SP (SQL Server 2005) using the CFStoredProc function and to > get the result set returned as "nextval". > Basically it should return an interget value. > > > > > > > Now if I try to use CFDump to dump the results set "nextval" it indicates the > variable does not exist. > > What is it I am missing? It has ot be something I can say "DOH" about. > > > > ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293043 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CFStoredProc
cfstoredproc.statuscode -Original Message- From: CF Developer [mailto:[EMAIL PROTECTED] Sent: Friday, November 09, 2007 10:40 AM To: CF-Talk Subject: Re: CFStoredProc The storedProc only returns a single interget value not a database object or field. It runs a Query, gets a value, increments the value, updates the table and returns the new calculated value. such as: database table as a field called: thisVal and that field is set to 1 It will increment it to 2, updates the table and set to return 2 as: return @nextVal I thought the dbvarname is used to specify the FIELD that you would want to be returned. From: Bruce Sorge <[EMAIL PROTECTED]> Sent: Friday, November 09, 2007 8:24 AM To: CF-Talk Subject: Re: CFStoredProc It looks like you are missing the DBVAR name. CF Developer wrote: > This should have been simple as making a PB&J. > > I am calling a SP (SQL Server 2005) using the CFStoredProc function and to get the result set returned as "nextval". > Basically it should return an interget value. > > > > > > > Now if I try to use CFDump to dump the results set "nextval" it indicates the variable does not exist. > > What is it I am missing? It has ot be something I can say "DOH" about. > > > > ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293046 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFStoredProc
If you arent returning a result set then you should use a procparam with type=out. -- Gary Gilbert http://www.garyrgilbert.com/blog ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293045 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFStoredProc
You are right. I misread your issue. Since you are only wanting an output variable, you need to change the type to "OUT" and use the Variable attribute. CF Developer wrote: > The storedProc only returns a single interget value not a > database object or field. > It runs a Query, gets a value, increments the value, updates the table and > returns the new calculated value. > > such as: database table as a field called: thisVal and that field is set to 1 > It will increment it to 2, updates the table > and set to return 2 > > as: return @nextVal > > I thought the dbvarname is used to specify the FIELD that you would want to > be returned. > > > > > ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293044 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfstoredproc timeout
> I want to limit the total execution time of a stored procedure being called > from Coldfusion using to 90 seconds. > With cfquery there is a parameter timeout, but there is no equivalent in >cfstoredproc. Can anyone think of an alternative to > accomplishing this. > > The trick is, I want to wrap the stored procedure call around a cftry block > and create an empty query if the procedure times out. You can often call stored procedures using CFQUERY. If your stored procedure doesn't return multiple recordsets, that might be a good approach. 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 ~| 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:333252 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfstoredproc code generator
Does a free one of these exist? I faintly remember the topic coming up in the past, but my searches are coming up empty. I did find this one for $99. http://www.netwebapps.com/Products/speedDB/introduction.cfm -- jon mailto:[EMAIL PROTECTED] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
CFSTOREDPROC and caching
As far as I can tell, there are no caching attributes for CFSTOREDPROC the way there are for CFQUERY. Has anyone developed a generic tag to implement this? It should theoretically be possible to mimic CFSTOREDPROC and keep all the relevant stuff in server variables, but I don't think it's trivial, and I don't want to do it if someone else has. --- Martin Herbener [EMAIL PROTECTED] Kentucky Department of Education -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
CFQUERY/CFSTOREDPROC error
I have a problem! the following code does not work: SELECT * FROM corskeys WHERE ID = #ID# the datasource definitely exists, the username and password are definitely right, and the SQL table definitely exists with 550,000 recs in it. The value of #ID# is an integer which is the data type of the field ID. I get the following error message: CMemoryException: unknown cause PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (7:1) to (7:95). This code runs fine in ISQL. I have also tried hard-codeing in a value for ID but that doesn't work either. I have also tried converting it to an SP but, although the SP works ok, CF produces the same error. Any ideas? -- Andrew Ewings Project Manager Thoughtbubble Ltd -- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
CFStoredProc vs CFQUERY
I was just wondering if any one knew the difference between the
following:
AND THIS:
{CALL usp_myproc("01/23/1971", "31")}
Also, when the stored procedure returns a query, how would you access it
through the first method? The second method at least allows you to name
the query so that you can access the resulting recordset.
Thanks,
Steve Durette
Database Administrator/Web Developer
Engineering/Construction System Support
SBC/Ameritech
__
Why Share?
Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
CFStoredProc vs CFQUERY
I was just wondering if any one knew the difference between the
following:
AND THIS:
{CALL usp_myproc("01/23/1971", "31")}
Also, when the stored procedure returns a query, how would you access it
through the first method? The second method at least allows you to name
the query so that you can access the resulting recordset.
Thanks,
Steve Durette
Database Administrator/Web Developer
Engineering/Construction System Support
SBC/Ameritech
__
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
cfstoredproc and cftransaction
What is the best way of handling the following situation. I have 3 stored procedures I want to call from coldfusion and once all 3 have executed sucessfully, I want to commit them, if any fails, I want to do a rollback. Can this be done with cftransaction or is that only to be used with the cfquery stuff. Thanks. Database: ORACLE 8 ColdFusion 4.5 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CFSTOREDPROC error
You may want to show a copy of your code. There's probably a typo or something. The sproc is not even being executed yet. -Original Message- From: David Cummins [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 05, 2001 7:00 PM To: CF-Talk Subject: CFSTOREDPROC error Hi all, Had a strange error on a page that's been bug-free and unmodified for months. It uses CFSTOREDPROC, which I haven't used much in the past, so I thought I'd throw it to CF Talk. Unknown Data Access Error. The error occurred while processing an element with a general identifier of (CFSTOREDPROC) The stored proc has 3 in parameters, 1 out parameter and no return code. David Cummins ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: CFSTOREDPROC error
i recommend you avoid this tag if possible. At 01:59 PM 7/6/01 +1200, you wrote: >Hi all, > >Had a strange error on a page that's been bug-free and unmodified for >months. It >uses CFSTOREDPROC, which I haven't used much in the past, so I thought I'd >throw >it to CF Talk. > >Unknown Data Access Error. > >The error occurred while processing an element with a general identifier of >(CFSTOREDPROC) > >The stored proc has 3 in parameters, 1 out parameter and no return code. > >David Cummins > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CFSTOREDPROC error
If there was no code change in quite some time then it's likely a fault on the part of the DB. Use the administrator (if you can) to turn on the debug info to help track this down. Bryan Love ACP Internet Application Developer [EMAIL PROTECTED] -Original Message- From: David Cummins [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 05, 2001 7:00 PM To: CF-Talk Subject: CFSTOREDPROC error Hi all, Had a strange error on a page that's been bug-free and unmodified for months. It uses CFSTOREDPROC, which I haven't used much in the past, so I thought I'd throw it to CF Talk. Unknown Data Access Error. The error occurred while processing an element with a general identifier of (CFSTOREDPROC) The stored proc has 3 in parameters, 1 out parameter and no return code. David Cummins ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CFSTOREDPROC error
> > Had a strange error on a page that's been bug-free and > > unmodified for months. It uses CFSTOREDPROC ... > > i recommend you avoid this tag if possible. Why is that? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
more cfstoredproc issues ...
Argh! I have a stored procedure with parameters as: CREATE PROCEDURE tap_adLibUpdate @LibraryID int, @Ident int, @UpdatedDate datetime, @PageFileName varchar(15) = NULL, @ArchivePath varchar(15) = NULL, @Scheduled bit = 0, @RestoreID int = NULL, @UpdateError varchar(8000) = NULL AS and I'm using this code in CF: and I'm getting this error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'tap_adLibUpdate' expects parameter '@LibraryID', which was not supplied. I know that the LibraryID parameter is defined in the tags. I know that none of it was misspelled. I know that the value of variables.PageID is an integer. What I don't know is why I'm getting an error message that tells me exactly what the problem is not. Anybody have any ideas? Isaac Dealey Certified Advanced ColdFusion 5 Developer new epoch www.turnkey.to 954-776-0046 __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: cfstoredproc behavior
Trying using CF_SQL_TIMESTAMP for the date fields. - Original Message - From: Andy Clary <[EMAIL PROTECTED]> Date: Friday, October 18, 2002 10:19 am Subject: cfstoredproc behavior > I have a dynamic query that I was trying to use the > tag with, > but all I kept getting was the generic "Unknown data access" > error. So I > re-wrote it with and it works fine, but I can't see any > differencein the two. Can anyone here see why the > version wouldn't > work? > > version (unknown data access error): > > datasource="#request.app_vars.warranty_dsn#"> >cfsqltype="CF_SQL_INTEGER" value="#form.row_id#"/> >value="#form.mfg#"/> >cfsqltype="CF_SQL_VARCHAR" value="#form.tech_type#"/> >value="#form.begdate#"/> >value="#form.enddate#"/> >value="#form.notes#"/> > > type="In" > dbvarname="@#col_sql_name#" > cfsqltype="#CFSQLType(col_type)#" > value="#StructFind(form, col_sql_name)#" > maxlength="#col_length#" > /> > > > > > version (works): > > datasource="#request.app_vars.warranty_dsn#"> > sp_web_cf_softrevs_insert_update > @row_id = value="#form.row_id#"/>, > @mfg = value="#form.mfg#"/>, > @tech_type = value="#form.tech_type#"/>, > @begdate = value="#form.begdate#"/>, > @enddate = value="#form.enddate#"/>, > @notes = value="#form.notes#"/>, > > @#col_sql_name#= > cfsqltype="#CFSQLType(col_type)#" > maxlength="#col_length#" > value="#StructFind(form, col_sql_name)#" > /> >qColumns.RecordCount)>, > > > > The qColumns query contains a list of dynamic columns that appear or > dissapear on the form based on mfg. CFSQLType is a udf I wrote > that returns > the cold fusion cfsqltype given a SQL Server data type. > > This is running on Win2k, CF5, SQL Server 2000 with all of the latest > patches. > > Thanks in advance, > Andy > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: cfstoredproc behavior
My only real suggestion here is to make sure that the cfprocparam tags in your output are in _exactly_ the same order as they are declared in your stored procedure declaration. I realize that it shouldn't matter, since they're named with the dbvarname attribute, however, there's an undocumented "feature" in cfstoredproc/cfprocparam which causes it to completely ignore the parameter names. This could especially be an issue with your at the end of your call if there are any parameters missing, or if they aren't in exactly the same order. hth S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 > I have a dynamic query that I was trying to use the tag > with, > but all I kept getting was the generic "Unknown data access" error. So I > re-wrote it with and it works fine, but I can't see any > difference > in the two. Can anyone here see why the version wouldn't > work? > version (unknown data access error): > datasource="#request.app_vars.warranty_dsn#"> >cfsqltype="CF_SQL_INTEGER" value="#form.row_id#"/> >value="#form.mfg#"/> >cfsqltype="CF_SQL_VARCHAR" value="#form.tech_type#"/> >value="#form.begdate#"/> >value="#form.enddate#"/> >value="#form.notes#"/> > > type="In" > dbvarname="@#col_sql_name#" > cfsqltype="#CFSQLType(col_type)#" > value="#StructFind(form, col_sql_name)#" > maxlength="#col_length#" > /> > > > version (works): > datasource="#request.app_vars.warranty_dsn#"> > sp_web_cf_softrevs_insert_update > @row_id = value="#form.row_id#"/>, > @mfg = value="#form.mfg#"/>, > @tech_type = value="#form.tech_type#"/>, > @begdate = value="#form.begdate#"/>, > @enddate = value="#form.enddate#"/>, > @notes = value="#form.notes#"/>, > > @#col_sql_name#= > cfsqltype="#CFSQLType(col_type)#" > maxlength="#col_length#" > value="#StructFind(form, col_sql_name)#" > /> >qColumns.RecordCount)>, > > > The qColumns query contains a list of dynamic columns that appear or > dissapear on the form based on mfg. CFSQLType is a udf I wrote that > returns > the cold fusion cfsqltype given a SQL Server data type. > This is running on Win2k, CF5, SQL Server 2000 with all of the latest > patches. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: cfstoredproc behavior
> The parameters aren't in the same order as in the > stored procedure, but is there any way to get > around this "feature". That sounds like a severe > limitation of the tag. I'd rather not have to put > the parameters in order, that is what the dbvarname > attribute is for. Does this happen all the time > or is there a way to reproduce this behavior? I'm pretty sure that you always have to specify the parameters in order. I agree that you shouldn't have to do that, if you go to the trouble of using the DBVARNAME attribute, but there it is. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: cfstoredproc behavior
> Does Macromedia know this is a bug? Macromedia is aware of this behavior, since it was specifically mentioned in the old ACFD curriculum, if I recall correctly. I don't know if it's their issue, or a problem with the database drivers, which are supplied by DataDirect. > Was this corrected in CFMX? Honestly, I haven't tested to find out. I just tend to assume that things work the way they did before, and code accordingly. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: cfstoredproc behavior
Does Macromedia know this is a bug? Was this corrected in CFMX? -Original Message- From: Dave Watts [mailto:dwatts@;figleaf.com] Sent: Friday, October 18, 2002 2:52 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: cfstoredproc behavior > The parameters aren't in the same order as in the > stored procedure, but is there any way to get > around this "feature". That sounds like a severe > limitation of the tag. I'd rather not have to put > the parameters in order, that is what the dbvarname > attribute is for. Does this happen all the time > or is there a way to reproduce this behavior? I'm pretty sure that you always have to specify the parameters in order. I agree that you shouldn't have to do that, if you go to the trouble of using the DBVARNAME attribute, but there it is. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: cfstoredproc behavior
Do you know what version of MDAC is on the server? > -Original Message- > From: Andy Clary [mailto:aclary@;teleplanwireless.com] > Sent: Friday, October 18, 2002 2:08 PM > To: CF-Talk > Subject: RE: cfstoredproc behavior > > > Isaac, > Thanks for the info, and to the previous poster - the > CF_SQL_TIMESTAMP date > type didn't help any. > > The parameters aren't in the same order as in the stored procedure, but is > there any way to get around this "feature". That sounds like a severe > limitation of the tag. I'd rather not have to put the parameters > in order, > that is what the dbvarname attribute is for. Does this happen > all the time > or is there a way to reproduce this behavior? > > Otherwise, I'll just stick to the cfquery implementation. > > Andy > > -Original Message- > From: S. Isaac Dealey [mailto:info@;turnkey.to] > Sent: Friday, October 18, 2002 11:42 AM > To: CF-Talk > Subject: Re: cfstoredproc behavior > > > My only real suggestion here is to make sure that the cfprocparam tags in > your output are in _exactly_ the same order as they are declared in your > stored procedure declaration. I realize that it shouldn't matter, since > they're named with the dbvarname attribute, however, there's an > undocumented > "feature" in cfstoredproc/cfprocparam which causes it to completely ignore > the parameter names. This could especially be an issue with your > > at the end of your call if there are any > parameters missing, > or if they aren't in exactly the same order. > > > hth > > S. Isaac Dealey > Certified Advanced ColdFusion 5 Developer > > www.turnkey.to > 954-776-0046 > > > I have a dynamic query that I was trying to use the tag > > with, > > but all I kept getting was the generic "Unknown data access" > error. So I > > re-wrote it with and it works fine, but I can't see any > > difference > > in the two. Can anyone here see why the version wouldn't > > work? > > > version (unknown data access error): > > > > datasource="#request.app_vars.warranty_dsn#"> > > > cfsqltype="CF_SQL_INTEGER" value="#form.row_id#"/> > > > value="#form.mfg#"/> > > > cfsqltype="CF_SQL_VARCHAR" value="#form.tech_type#"/> > > > value="#form.begdate#"/> > > > value="#form.enddate#"/> > > > value="#form.notes#"/> > > > > > type="In" > > dbvarname="@#col_sql_name#" > > cfsqltype="#CFSQLType(col_type)#" > > value="#StructFind(form, col_sql_name)#" > > maxlength="#col_length#" > > /> > > > > > > > > version (works): > > > > datasource="#request.app_vars.warranty_dsn#"> > > sp_web_cf_softrevs_insert_update > > @row_id = > value="#form.row_id#"/>, > > @mfg = > value="#form.mfg#"/>, > > @tech_type = > value="#form.tech_type#"/>, > > @begdate = > value="#form.begdate#"/>, > > @enddate = > value="#form.enddate#"/>, > > @notes = > value="#form.notes#"/>, > > > > @#col_sql_name#= > > > cfsqltype="#CFSQLType(col_type)#" > > maxlength="#col_length#" > > value="#StructFind(form, col_sql_name)#" > > /> > > > qColumns.RecordCount)>, > > > > > > > The qColumns query contains a list of dynamic columns that appear or > > dissapear on the form based on mfg. CFSQLType is a udf I wrote that > > returns > > the cold fusion cfsqltype given a SQL Server data type. > > > This is running on Win2k, CF5, SQL Server 2000 with all of the latest > > patches. > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: cfstoredproc behavior
I believe it is version 2.6 -Original Message- From: Kwang Suh [mailto:ksuh@;shaw.ca] Sent: Friday, October 18, 2002 3:04 PM To: CF-Talk Subject: RE: cfstoredproc behavior Do you know what version of MDAC is on the server? > -Original Message- > From: Andy Clary [mailto:aclary@;teleplanwireless.com] > Sent: Friday, October 18, 2002 2:08 PM > To: CF-Talk > Subject: RE: cfstoredproc behavior > > > Isaac, > Thanks for the info, and to the previous poster - the > CF_SQL_TIMESTAMP date > type didn't help any. > > The parameters aren't in the same order as in the stored procedure, but is > there any way to get around this "feature". That sounds like a severe > limitation of the tag. I'd rather not have to put the parameters > in order, > that is what the dbvarname attribute is for. Does this happen > all the time > or is there a way to reproduce this behavior? > > Otherwise, I'll just stick to the cfquery implementation. > > Andy > > -Original Message- > From: S. Isaac Dealey [mailto:info@;turnkey.to] > Sent: Friday, October 18, 2002 11:42 AM > To: CF-Talk > Subject: Re: cfstoredproc behavior > > > My only real suggestion here is to make sure that the cfprocparam tags in > your output are in _exactly_ the same order as they are declared in your > stored procedure declaration. I realize that it shouldn't matter, since > they're named with the dbvarname attribute, however, there's an > undocumented > "feature" in cfstoredproc/cfprocparam which causes it to completely ignore > the parameter names. This could especially be an issue with your > > at the end of your call if there are any > parameters missing, > or if they aren't in exactly the same order. > > > hth > > S. Isaac Dealey > Certified Advanced ColdFusion 5 Developer > > www.turnkey.to > 954-776-0046 > > > I have a dynamic query that I was trying to use the tag > > with, > > but all I kept getting was the generic "Unknown data access" > error. So I > > re-wrote it with and it works fine, but I can't see any > > difference > > in the two. Can anyone here see why the version wouldn't > > work? > > > version (unknown data access error): > > > > datasource="#request.app_vars.warranty_dsn#"> > > > cfsqltype="CF_SQL_INTEGER" value="#form.row_id#"/> > > > value="#form.mfg#"/> > > > cfsqltype="CF_SQL_VARCHAR" value="#form.tech_type#"/> > > > value="#form.begdate#"/> > > > value="#form.enddate#"/> > > > value="#form.notes#"/> > > > > > type="In" > > dbvarname="@#col_sql_name#" > > cfsqltype="#CFSQLType(col_type)#" > > value="#StructFind(form, col_sql_name)#" > > maxlength="#col_length#" > > /> > > > > > > > > version (works): > > > > datasource="#request.app_vars.warranty_dsn#"> > > sp_web_cf_softrevs_insert_update > > @row_id = > value="#form.row_id#"/>, > > @mfg = > value="#form.mfg#"/>, > > @tech_type = > value="#form.tech_type#"/>, > > @begdate = > value="#form.begdate#"/>, > > @enddate = > value="#form.enddate#"/>, > > @notes = > value="#form.notes#"/>, > > > > @#col_sql_name#= > > > cfsqltype="#CFSQLType(col_type)#" > > maxlength="#col_length#" > > value="#StructFind(form, col_sql_name)#" > > /> > > > qColumns.RecordCount)>, > > > > > > > The qColumns query contains a list of dynamic columns that appear or > > dissapear on the form based on mfg. CFSQLType is a udf I wrote that > > returns > > the cold fusion cfsqltype given a SQL Server data type. > > > This is running on Win2k, CF5, SQL Server 2000 with all of the latest > > patches. > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: cfstoredproc behavior
>> Was this corrected in CFMX? >Honestly, I haven't tested to find out. I ran the CFMX Code Analyzer against an old app last night and one of the things it mentioned was that dbvarname is ignored in CFMX and that parameters must be listed in proper order. Ken ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: cfstoredproc behavior
> Isaac,
> Thanks for the info, and to the previous poster - the
> CF_SQL_TIMESTAMP date
> type didn't help any.
> The parameters aren't in the same order as in the stored
> procedure, but is
> there any way to get around this "feature". That sounds
> like a severe
> limitation of the tag. I'd rather not have to put the
> parameters in order,
> that is what the dbvarname attribute is for. Does this
> happen all the time
> or is there a way to reproduce this behavior?
> Otherwise, I'll just stick to the cfquery implementation.
> Andy
Hi Andy ...
The "feature" is easily reproduceable. Try this stored proc:
create procedure
@a int,
@b int
as
select @a as a, @b as b
go
Write out a cfstoredproc tag with the tags in the right order
and output the results. Then reverse the order of the parameters and do the
same.
It't not just an occasionaly thing, it's with _every_ cfstoredproc call.
Updating MDAC ( while not necessarily a bad idea ) won't help, because the
issue lies with CF's implementation of the tag. The really nasty thing is
that if your columns are of the correct data type, it's really easy to
insert bad or bogus data, i.e. membername varchar(100), memberemail
varchar(100) -- POW -- you're now greeting me as "[EMAIL PROTECTED]" and
trying to send my email to "Isaac Dealey" in spite of the fact that you
named your parameters corectly.
I've not submitted a bug report for it -- I don't know if anyone else has.
There is no bug info about it on MM's site, and no-one from MM has said
anything about it in spite of my grumbling here ( maybe they just haven't
noticed it ) but it's well known amongst those of us who use the
tag. I find it difficult to imagine that it hasn't been
reported and that nobody's asked for it to be fixed, so I have a sneaky
suspicion that it's just being ignored.
My biggest gripe recently has been that there's no good alternative to it on
CFMX and therefore absolutely no good way to call stored procedures with
CFMX. The reason I say this is that with you can't return data
from a stored procedure using the JDBC drivers, and the ODBC Socket drivers
(which allow them to work as they do in on CF5) are just a wrapper
for those ( afaik ) and I'm not certain they are being provided by Sun but
the info on Sun's website about their ODBC bridge drivers says they
shouldn't be used accept as a last resort to maintain certain legacy
applications until those applications can be updated to accommodate JDBC
drivers.
So in the meantime I've settled for just making sure that all my
tags put the parameters in the right order -- in some cases
this has meant creating extra custom-tag wrappers around certain stored
procedures to help insulate them against being mangled by developers who
might not be aware of the issue.
If you're hopeful to upgrade to CFMX I wouldn't put off updating all your
stored procedures -- I'd just make sure they're in the right order for now
-- hopefully it will eventually be fixed. Personally, I'd probably do it
anyway, even if you're not planning to upgrade any time soon, just to get
the headache out of the way early.
Isaac
Certified Advanced ColdFusion 5 Developer
www.turnkey.to
954-776-0046
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
RE: cfstoredproc behavior
> It't not just an occasionaly thing, it's with _every_ cfstoredproc call. > Updating MDAC ( while not necessarily a bad idea ) won't help, because the The specific error that Andy was getting can be related to the version of MDAC that is installed on the server. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: cfstoredproc behavior
>> It't not just an occasionaly thing, it's with _every_ >> cfstoredproc call. >> Updating MDAC ( while not necessarily a bad idea ) won't >> help, because the > The specific error that Andy was getting can be related to > the version of > MDAC that is installed on the server. Ahh... well -- he mentioned the parameters not being in the same order, so I was just following up on the other known existing issue with his code -- didn't really read into the error. It's true that updating mdac won't help the dbvarname issue -- that's why I said updating MDAC wasn't a bad idea but wouldn't solve the issue I identified. I didn't mean to imply that there wasn't anything else necessarily going on or that the mdac reasoning was bad. Sorry if it came across that way. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: cfstoredproc behavior
I'm pretty sure it's the order that is hanging it up. I have used cfstoredproc in other places on our servers and it works just fine. It's pretty dissapointing that this seems to be a "feature" or "bug" or whatever you want to call it that MM is ignoring. What happens when you want to skip parameters in a dynamic statement like I was doing, I guess you have to revert to cfquery? Also, what is the deal with cfquery in CFMX? I've just finally got CFMX installed tonight for the first time. Our website which is probably 95% cfquery vs. cfstoredproc seems to be working just fine. For a datasource I just chose SQL Server from the list of datasource types when I created it, I think this means I'm using the JDBC driver? Either way, all of our stored procedure calls that are made in cfquery tags seem to be working fine. Personally, I have always preferred the cfquery tag over the cfstoredproc for the simple reason that you get real error messages back and not just "Unknown Data Access Error". Andy -Original Message- From: S. Isaac Dealey [mailto:info@;turnkey.to] Sent: Friday, October 18, 2002 8:52 PM To: CF-Talk Subject: RE: cfstoredproc behavior >> It't not just an occasionaly thing, it's with _every_ >> cfstoredproc call. >> Updating MDAC ( while not necessarily a bad idea ) won't >> help, because the > The specific error that Andy was getting can be related to > the version of > MDAC that is installed on the server. Ahh... well -- he mentioned the parameters not being in the same order, so I was just following up on the other known existing issue with his code -- didn't really read into the error. It's true that updating mdac won't help the dbvarname issue -- that's why I said updating MDAC wasn't a bad idea but wouldn't solve the issue I identified. I didn't mean to imply that there wasn't anything else necessarily going on or that the mdac reasoning was bad. Sorry if it came across that way. My biggest gripe recently has been that there's no good alternative to it on CFMX and therefore absolutely no good way to call stored procedures with CFMX. The reason I say this is that with you can't return data from a stored procedure using the JDBC drivers, and the ODBC Socket drivers (which allow them to work as they do in on CF5) are just a wrapper for those ( afaik ) and I'm not certain they are being provided by Sun but the info on Sun's website about their ODBC bridge drivers says they shouldn't be used accept as a last resort to maintain certain legacy applications until those applications can be updated to accommodate JDBC drivers. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: cfstoredproc behavior
> I'm pretty sure it's the order that is hanging it up. I > have used > cfstoredproc in other places on our servers and it works > just fine. > It's pretty dissapointing that this seems to be a > "feature" or "bug" or > whatever you want to call it that MM is ignoring. What > happens when you > want to skip parameters in a dynamic statement like I was > doing, I guess you > have to revert to cfquery? Or you have to include all the parameters you wanted to skip ... Which means you can't allow the stored procedure to use its defaults. In my case most of my stored procedures use NULL for any defaults, so passing null="yes" in the is equivalent, however, if I used "bilbo" as a default for instance, I'd have to pass "bilbo" as the value, which thoroughly defeats the purpose of having a default in my stored procedure and makes my job that much harder if that "default" value ever changes. > Also, what is the deal with cfquery in CFMX? I've just > finally got CFMX > installed tonight for the first time. Our website which > is probably 95% > cfquery vs. cfstoredproc seems to be working just fine. > For a datasource I > just chose SQL Server from the list of datasource types > when I created it, I > think this means I'm using the JDBC driver? Either way, > all of our stored > procedure calls that are made in cfquery tags seem to be > working fine. Hmmm... do many of your stored procedures return a recordset? That's the only issue really, so if you only use the stored procs to perform inserts and updates and aren't concerned with getting a recordset back, there wouldn't be any reason to notice the difference. Otherwise, you may be using the ODBC socket -- I wouldn't know, I've been too busy with recent updates and preparing for MX to actually install MX and work with it much. I suppose it's possible MM could have found a way to change the behavior with the JDBC drivers in the recent MX update though again I wouldn't know. > Personally, I have always preferred the cfquery tag over > the cfstoredproc > for the simple reason that you get real error messages > back and not just > "Unknown Data Access Error". Not to mention all that extra typing. :) Though iirc, it does have some other advantages in that I believe it's supposed to help speed up processing of stored procedures ( I wonder if this is done by not naming parameters ;P ) and it insulates the procedure from unlikely sql insertion attacks and issues with data insertion, like the \ on MySQL ( although current versions of MySQL don't support stored procedures ) being a special character that doesn't know to account for. Of course, much of this can also be accomplished with in a stored procedure called with so my biggest gripes are the lack of ability to use db defined defaults and the fact that it's completely undocumented anywhere on MM's site ( actually the documentation says that the dbvarname attribute is supposed to be the name of the parameter in the stored procedure, which you might be able to weasel out of saying is an out and out lie, but it's certainly seems to indicate that the order of parameters shouldn't matter ) and since I may not necessarily be the only developer who ever touches my code there's a good chance at some point that I'm going to have to field technical support calls or emails in which I'll have to explain this whole debacle over again. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: cfstoredproc behavior
Yes, many of my stored procedures return recordsets and are working just fine. I think not being able to return recordsets would be a HUGE limitation. I can't imagine that this would be the case. Again, when I created my datasouce I chose the SQL Server datasource type from the drop down menu. There is no mention of whether or not it is a JDBC, but I tend to believe it is because ODBC Socket was another option also on the drop down list. Can anyone else confirm or deny this behavior? Andy -Original Message- From: S. Isaac Dealey [mailto:info@;turnkey.to] Sent: Saturday, October 19, 2002 7:20 AM To: CF-Talk Subject: RE: cfstoredproc behavior > I'm pretty sure it's the order that is hanging it up. I > have used > cfstoredproc in other places on our servers and it works > just fine. > It's pretty dissapointing that this seems to be a > "feature" or "bug" or > whatever you want to call it that MM is ignoring. What > happens when you > want to skip parameters in a dynamic statement like I was > doing, I guess you > have to revert to cfquery? Or you have to include all the parameters you wanted to skip ... Which means you can't allow the stored procedure to use its defaults. In my case most of my stored procedures use NULL for any defaults, so passing null="yes" in the is equivalent, however, if I used "bilbo" as a default for instance, I'd have to pass "bilbo" as the value, which thoroughly defeats the purpose of having a default in my stored procedure and makes my job that much harder if that "default" value ever changes. > Also, what is the deal with cfquery in CFMX? I've just > finally got CFMX > installed tonight for the first time. Our website which > is probably 95% > cfquery vs. cfstoredproc seems to be working just fine. > For a datasource I > just chose SQL Server from the list of datasource types > when I created it, I > think this means I'm using the JDBC driver? Either way, > all of our stored > procedure calls that are made in cfquery tags seem to be > working fine. Hmmm... do many of your stored procedures return a recordset? That's the only issue really, so if you only use the stored procs to perform inserts and updates and aren't concerned with getting a recordset back, there wouldn't be any reason to notice the difference. Otherwise, you may be using the ODBC socket -- I wouldn't know, I've been too busy with recent updates and preparing for MX to actually install MX and work with it much. I suppose it's possible MM could have found a way to change the behavior with the JDBC drivers in the recent MX update though again I wouldn't know. > Personally, I have always preferred the cfquery tag over > the cfstoredproc > for the simple reason that you get real error messages > back and not just > "Unknown Data Access Error". Not to mention all that extra typing. :) Though iirc, it does have some other advantages in that I believe it's supposed to help speed up processing of stored procedures ( I wonder if this is done by not naming parameters ;P ) and it insulates the procedure from unlikely sql insertion attacks and issues with data insertion, like the \ on MySQL ( although current versions of MySQL don't support stored procedures ) being a special character that doesn't know to account for. Of course, much of this can also be accomplished with in a stored procedure called with so my biggest gripes are the lack of ability to use db defined defaults and the fact that it's completely undocumented anywhere on MM's site ( actually the documentation says that the dbvarname attribute is supposed to be the name of the parameter in the stored procedure, which you might be able to weasel out of saying is an out and out lie, but it's certainly seems to indicate that the order of parameters shouldn't matter ) and since I may not necessarily be the only developer who ever touches my code there's a good chance at some point that I'm going to have to field technical support calls or emails in which I'll have to explain this whole debacle over again. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: cfstoredproc behavior
Have you run the MX Updater? They may haved worked around the issue with the JDBC drivers in the updater. > Yes, many of my stored procedures return recordsets and > are working just > fine. I think not being able to return recordsets would > be a HUGE > limitation. I can't imagine that this would be the case. > Again, when I > created my datasouce I chose the SQL Server datasource > type from the drop > down menu. There is no mention of whether or not it is a > JDBC, but I tend > to believe it is because ODBC Socket was another option > also on the drop > down list. Can anyone else confirm or deny this behavior? > Andy > -Original Message- > From: S. Isaac Dealey [mailto:info@;turnkey.to] > Sent: Saturday, October 19, 2002 7:20 AM > To: CF-Talk > Subject: RE: cfstoredproc behavior >> I'm pretty sure it's the order that is hanging it up. I >> have used >> cfstoredproc in other places on our servers and it works >> just fine. >> It's pretty dissapointing that this seems to be a >> "feature" or "bug" or >> whatever you want to call it that MM is ignoring. What >> happens when you >> want to skip parameters in a dynamic statement like I was >> doing, I guess you >> have to revert to cfquery? > Or you have to include all the parameters you wanted to > skip ... Which means > you can't allow the stored procedure to use its defaults. > In my case most of > my stored procedures use NULL for any defaults, so passing > null="yes" in the > is equivalent, however, if I used "bilbo" > as a default for > instance, I'd have to pass "bilbo" as the value, which > thoroughly defeats > the purpose of having a default in my stored procedure and > makes my job that > much harder if that "default" value ever changes. >> Also, what is the deal with cfquery in CFMX? I've just >> finally got CFMX >> installed tonight for the first time. Our website which >> is probably 95% >> cfquery vs. cfstoredproc seems to be working just fine. >> For a datasource I >> just chose SQL Server from the list of datasource types >> when I created it, I >> think this means I'm using the JDBC driver? Either way, >> all of our stored >> procedure calls that are made in cfquery tags seem to be >> working fine. > Hmmm... do many of your stored procedures return a > recordset? That's the > only issue really, so if you only use the stored procs to > perform inserts > and updates and aren't concerned with getting a recordset > back, there > wouldn't be any reason to notice the difference. > Otherwise, you may be using > the ODBC socket -- I wouldn't know, I've been too busy > with recent updates > and preparing for MX to actually install MX and work with > it much. I suppose > it's possible MM could have found a way to change the > behavior with the JDBC > drivers in the recent MX update though again I wouldn't > know. >> Personally, I have always preferred the cfquery tag over >> the cfstoredproc >> for the simple reason that you get real error messages >> back and not just >> "Unknown Data Access Error". > Not to mention all that extra typing. :) > Though iirc, it does have some other advantages in that I > believe it's > supposed to help speed up processing of stored procedures > ( I wonder if this > is done by not naming parameters ;P ) and it insulates the > procedure from > unlikely sql insertion attacks and issues with data > insertion, like the \ on > MySQL ( although current versions of MySQL don't support > stored procedures ) > being a special character that doesn't know to > account for. Of > course, much of this can also be accomplished with > in a > stored procedure called with so my biggest > gripes are the lack of > ability to use db defined defaults and the fact that it's > completely > undocumented anywhere on MM's site ( actually the > documentation says that > the dbvarname attribute is supposed to be the name of the > parameter in the > stored procedure, which you might be able to weasel out of > saying is an out > and out lie, but it's certainly seems to indicate that the > order of > parameters shouldn't matter ) and since I may not > necessarily be the only > developer who ever touches my code there's a good chance > at some point that > I'm going to have to field technical support calls or > emails in which I'll > have to explain this whole debacle over again. > Isaac > Certified Advance
RE: cfstoredproc behavior
Yes, I have run the updater, but the stored procedure worked before the updater as well. -Original Message- From: S. Isaac Dealey [mailto:info@;turnkey.to] Sent: Saturday, October 19, 2002 12:09 PM To: CF-Talk Subject: RE: cfstoredproc behavior Have you run the MX Updater? They may haved worked around the issue with the JDBC drivers in the updater. > Yes, many of my stored procedures return recordsets and > are working just > fine. I think not being able to return recordsets would > be a HUGE > limitation. I can't imagine that this would be the case. > Again, when I > created my datasouce I chose the SQL Server datasource > type from the drop > down menu. There is no mention of whether or not it is a > JDBC, but I tend > to believe it is because ODBC Socket was another option > also on the drop > down list. Can anyone else confirm or deny this behavior? > Andy > -Original Message- > From: S. Isaac Dealey [mailto:info@;turnkey.to] > Sent: Saturday, October 19, 2002 7:20 AM > To: CF-Talk > Subject: RE: cfstoredproc behavior >> I'm pretty sure it's the order that is hanging it up. I >> have used >> cfstoredproc in other places on our servers and it works >> just fine. >> It's pretty dissapointing that this seems to be a >> "feature" or "bug" or >> whatever you want to call it that MM is ignoring. What >> happens when you >> want to skip parameters in a dynamic statement like I was >> doing, I guess you >> have to revert to cfquery? > Or you have to include all the parameters you wanted to > skip ... Which means > you can't allow the stored procedure to use its defaults. > In my case most of > my stored procedures use NULL for any defaults, so passing > null="yes" in the > is equivalent, however, if I used "bilbo" > as a default for > instance, I'd have to pass "bilbo" as the value, which > thoroughly defeats > the purpose of having a default in my stored procedure and > makes my job that > much harder if that "default" value ever changes. >> Also, what is the deal with cfquery in CFMX? I've just >> finally got CFMX >> installed tonight for the first time. Our website which >> is probably 95% >> cfquery vs. cfstoredproc seems to be working just fine. >> For a datasource I >> just chose SQL Server from the list of datasource types >> when I created it, I >> think this means I'm using the JDBC driver? Either way, >> all of our stored >> procedure calls that are made in cfquery tags seem to be >> working fine. > Hmmm... do many of your stored procedures return a > recordset? That's the > only issue really, so if you only use the stored procs to > perform inserts > and updates and aren't concerned with getting a recordset > back, there > wouldn't be any reason to notice the difference. > Otherwise, you may be using > the ODBC socket -- I wouldn't know, I've been too busy > with recent updates > and preparing for MX to actually install MX and work with > it much. I suppose > it's possible MM could have found a way to change the > behavior with the JDBC > drivers in the recent MX update though again I wouldn't > know. >> Personally, I have always preferred the cfquery tag over >> the cfstoredproc >> for the simple reason that you get real error messages >> back and not just >> "Unknown Data Access Error". > Not to mention all that extra typing. :) > Though iirc, it does have some other advantages in that I > believe it's > supposed to help speed up processing of stored procedures > ( I wonder if this > is done by not naming parameters ;P ) and it insulates the > procedure from > unlikely sql insertion attacks and issues with data > insertion, like the \ on > MySQL ( although current versions of MySQL don't support > stored procedures ) > being a special character that doesn't know to > account for. Of > course, much of this can also be accomplished with > in a > stored procedure called with so my biggest > gripes are the lack of > ability to use db defined defaults and the fact that it's > completely > undocumented anywhere on MM's site ( actually the > documentation says that > the dbvarname attribute is supposed to be the name of the > parameter in the > stored procedure, which you might be able to weasel out of > saying is an out > and out lie, but it's certainly seems to indicate that the > order of > parameters shouldn't matter ) and since I may not > necessarily be the only > developer who
RE: cfstoredproc behavior
That's really strange... You're the only person I've talked to yet who's had that behavior... at least, that I'm aware of. Everyone else I've talked to who's tried to use cfquery for stored procedures on MX without using the ODBC socket drivers has said they couldn't return record sets -- and I remember reading from this list someone said ( and I think they were from MM ) that the JDBC drivers just didn't support the feature that allowed it to be done with the ODBC drivers. Not to mention that, although I don't work with MX yet, I was able to install an application on it briefly several months ago and noticed that the cfquery syntax wasn't returning any recordsets then. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 > Yes, I have run the updater, but the stored procedure > worked before the > updater as well. > -Original Message- > From: S. Isaac Dealey [mailto:info@;turnkey.to] > Sent: Saturday, October 19, 2002 12:09 PM > To: CF-Talk > Subject: RE: cfstoredproc behavior > Have you run the MX Updater? They may haved worked around > the issue with the > JDBC drivers in the updater. >> Yes, many of my stored procedures return recordsets and >> are working just >> fine. I think not being able to return recordsets would >> be a HUGE >> limitation. I can't imagine that this would be the case. >> Again, when I >> created my datasouce I chose the SQL Server datasource >> type from the drop >> down menu. There is no mention of whether or not it is a >> JDBC, but I tend >> to believe it is because ODBC Socket was another option >> also on the drop >> down list. Can anyone else confirm or deny this >> behavior? >> Andy >> -Original Message- >> From: S. Isaac Dealey [mailto:info@;turnkey.to] >> Sent: Saturday, October 19, 2002 7:20 AM >> To: CF-Talk >> Subject: RE: cfstoredproc behavior >>> I'm pretty sure it's the order that is hanging it up. I >>> have used >>> cfstoredproc in other places on our servers and it works >>> just fine. >>> It's pretty dissapointing that this seems to be a >>> "feature" or "bug" or >>> whatever you want to call it that MM is ignoring. What >>> happens when you >>> want to skip parameters in a dynamic statement like I >>> was >>> doing, I guess you >>> have to revert to cfquery? >> Or you have to include all the parameters you wanted to >> skip ... Which means >> you can't allow the stored procedure to use its defaults. >> In my case most of >> my stored procedures use NULL for any defaults, so >> passing >> null="yes" in the >> is equivalent, however, if I used "bilbo" >> as a default for >> instance, I'd have to pass "bilbo" as the value, which >> thoroughly defeats >> the purpose of having a default in my stored procedure >> and >> makes my job that >> much harder if that "default" value ever changes. >>> Also, what is the deal with cfquery in CFMX? I've just >>> finally got CFMX >>> installed tonight for the first time. Our website which >>> is probably 95% >>> cfquery vs. cfstoredproc seems to be working just fine. >>> For a datasource I >>> just chose SQL Server from the list of datasource types >>> when I created it, I >>> think this means I'm using the JDBC driver? Either way, >>> all of our stored >>> procedure calls that are made in cfquery tags seem to be >>> working fine. >> Hmmm... do many of your stored procedures return a >> recordset? That's the >> only issue really, so if you only use the stored procs to >> perform inserts >> and updates and aren't concerned with getting a recordset >> back, there >> wouldn't be any reason to notice the difference. >> Otherwise, you may be using >> the ODBC socket -- I wouldn't know, I've been too busy >> with recent updates >> and preparing for MX to actually install MX and work with >> it much. I suppose >> it's possible MM could have found a way to change the >> behavior with the JDBC >> drivers in the recent MX update though again I wouldn't >> know. >>> Personally, I have always preferred the cfquery tag over >>> the cfstoredproc >>> for the simple reason that you get real error messages >>> back and not just >>> "Unknown Data Access Error". >> Not to mention all tha
RE: cfstoredproc behavior
> Have you run the MX Updater? They may haved worked around > the issue with the JDBC drivers in the updater. Based on my reading of the log file created by the Updater, no database drivers were changed by the update process. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: cfc & cfstoredproc
Pass it like any other parameter, the resultset from a stored proc is still a query datatype. -- jon mailto:[EMAIL PROTECTED] Tuesday, March 11, 2003, 1:50:03 PM, you wrote: RS> Hi, RS> How do we pass the result set of a cfstoredproc call to a cfc? RS> TIA, RS> Ro ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CFSTOREDPROC & It's Progress
Hello All: For all those out there using CFSTOREDPROC what do you do while youre generating an output for your user's while its generating? Is there a best practice to follow? Regards, James Blaha ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfstoredproc questions SOT
Hey Folks, First, the on-topic question. When using is there an easy way to return a recordcount like that which is returned by ? Now for the SOT question... Sorry for this question, but I have searched high and low for this info and am now at my wits end so I am posting here. I have read in a number of places (including the cftalk archives) that, for security reasons, instead of using you should move your queries over to stored procs and limit user access to only execute stored procedures. So, with this wonderful idea in mind, I moved all my queries over (I'm using MS SQL 2000). The thing is, that if I disable select, update, delete access and only give access to the stored procedure, I throw an error saying that I don't have select rights. What am I missing. If anyone out there can point me in the right direction you will save me from losing the last remaining hairs on my head. :) --Jeff ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CFSTOREDPROC remediation techniques?
I am working on a migration from CF5 to MX. I was working on determining how much effort would be involved in fixing our stored procedure calls (since named parameters are no longer supported). I was curious as to the different techniques people used to fix their proc calls. Brute force? Replacing CFSTOREDPROC with a CFX? Other ideas? There are different camps in our office and I am wondering what other people on the list did? Thanks. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
cfstoredproc and cache
I tend to use stored procs even for "select" queries (especially ones involving views, and many tables) and always use cfstoredproc over calling the procedures using cfquery. The downside is that there is no direct way to cache the result set(s). So... Yesterday I started working on a CFC to load a result set into either: session|application|server scope. The CFC has a few methods: refresh(), remove(), init(), and cacheIt(). Anyways, does this seem like a logical and organized approach to quasi-caching result sets from stored procs? I know I could just set the result set into the scope of preference in the cfprocresult tag, but I thought the above was more versatile and useful. Am I missing something major here? Does anyone else do it a different way? Michael T. Tangorre Abacus Technology Corporation Senior ColdFusion Developer Advanced Certified ColdFusion MX Developer 8601 Georgia Avenue, Suite 400 Silver Spring, MD 20910 o. 301.495.4223 f. 301.562.9891 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
cfstoredproc and debug
Sorry about this stupid question. if i put debug="Yes" inside cfstoredproc, where am I going to see the debug info? How am I going to see the cfstoredproc debug info just like what cfquery shows (record numbers, time, etc)? Thanks J ___ Get the FREE email that has everyone talking at http://www.mail2world.com __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cfset/cfstoredproc
Janine, I don't think I'm following your example. What's wrong with the second example? also, why do you need 2 queries? Why not a join? a join would give you columns of name , answer name, answer, name, answer name, answer ORder by name and make sure there is a PK ... then do this: #name# (or if you like) ... Use the PK to ensure all records are iterated #answer# (or ) Still... I'm not sure I know exactly what the problem is from the example below. -Mark * your example * This is somewhat of a repost from the other day but haven't gotten any feedback- since I don't think I worded my problem very well. I am moving some of my work from cfquery to storedprocedures- the problem is the formatting when I use cfset with a stored procedures- they don't react the same way as a cfquery does... I need the info to show as such Mark: 1. A 2. B 3. A Mary 1. A 2. A 3. D John 1. C 2. A 3. B To achieve this with cfquery I did this: If I do the same with a stored procedure I end up with Mark: 1. A 2. B 3. A Mary 1. A 2. B 3. A John 1. A 2. B 3. A I can't use just a regular output like Ben Forta shows in this CF5 book-I've tried all his solutions but they all deal with regular outputs(which works fine with the cfquery and cfstored proc)- I need to use a cfset statement because each answer needs its own name. Has anyone run across this? What's the best solution? __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cfset/cfstoredproc
Sorry I didn't respond immediately- I went home sick yesterday afternoon. The 2nd example shows the correct person but everyone comes up with the 1st persons answers. When I do a regular output each person comes up with the correct answers--once I add cfset each person receives the answers for the 1st person! I originally did a join (there are 6 tables making the actual table- 2 of which are one to many)and used CFPROCRESULT to get the answers. Again getting the output format wasn't working correctly when using the cfset- once I did a cfset it would only give me one page for the results- showing the first person's name/answers-when I looped it I got many pages- each page had the 1st persons name/answers. I then did my stored procedure with 2 queries one pulls all the "one to one" info and then the second one that pulls the "one to many" info. I use several CFPROCRESULT tags to display the info. Again it displays fine until I add in the cfset tags. (But when I do the exact thing as 2 separate cfqueries and use the cfset it works like a charm!) I find this very bizarre. I have them (cfquery/cfproc) literally set up the same- they both work fine with a regular cfoutput- add the cfset and suddenly the stored procedure doesn't put out the info that needs to loop (ie: the answers)... Can someone do a small demo and see if they get the same results? Thanks J ps. my first cfoutput in my code did use the id as a group-I just forgot to put that in: -Original Message- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 28, 2002 1:48 PM To: CF-Talk Subject: RE: cfset/cfstoredproc Janine, I don't think I'm following your example. What's wrong with the second example? also, why do you need 2 queries? Why not a join? a join would give you columns of name , answer name, answer, name, answer name, answer ORder by name and make sure there is a PK ... then do this: #name# (or if you like) ... Use the PK to ensure all records are iterated #answer# (or ) Still... I'm not sure I know exactly what the problem is from the example below. -Mark * your example * This is somewhat of a repost from the other day but haven't gotten any feedback- since I don't think I worded my problem very well. I am moving some of my work from cfquery to storedprocedures- the problem is the formatting when I use cfset with a stored procedures- they don't react the same way as a cfquery does... I need the info to show as such Mark: 1. A 2. B 3. A Mary 1. A 2. A 3. D John 1. C 2. A 3. B To achieve this with cfquery I did this: If I do the same with a stored procedure I end up with Mark: 1. A 2. B 3. A Mary 1. A 2. B 3. A John 1. A 2. B 3. A I can't use just a regular output like Ben Forta shows in this CF5 book-I've tried all his solutions but they all deal with regular outputs(which works fine with the cfquery and cfstored proc)- I need to use a cfset statement because each answer needs its own name. Has anyone run across this? What's the best solution? __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
CFSTOREDPROC and Access97
Are there any known problems using CFSTOREDPROC and queries in an Access97 database? I'm having a hell of a time getting a simple UPDATE query to work (although SELECT queries work no problem). If you run the query from within Access, it asks for the variable values and then updates the relevant record fine. But passing them as CFPROCPARAMs doesn't seem to work at all (pretty sure all the data-types are OK). I'd paste the code but there's *heaps*... just wondered if anyone had any general comments on this topic. Thanks again. -- Aidan Whitehall <[EMAIL PROTECTED]> Netshopper UK Ltd Advanced Web Solutions & Services http://www.netshopperuk.com/ Telephone +44 (01744) 648650 Fax +44 (01744) 648651 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: cfstoredproc problem
Hi, You might already solved the problem. Since I do not see any post here I reply it anyway. First, if you use SQL server , you have to set up the return code you self in the stored procedure. Because the default return code is 0. The stored procedure should look something like: CREATE PROCEDURE spUpdateUser @@picture char(20), @@userid char(20 ) As UPDATE PasUsers SET picture = '@picture' WHERE userid = '@userid' Return @@Rowcount GO The @@Rowcount is the number of the record affected by the update action, in this case should be 1. I made up the data type by guessing you should change them to the correct ones. Second, after the calling of the stored procedure, You put: UpdateOK record has been updated in the database Sorry, it failed You can customize the if statement to suit your needs. Hope this helps Sima "S R" tmail.com> cc: Subject: cfstoredproc problem 12/29/00 05:32 PM Please respond to cf-talk I have been at this for the past 35 minutes and I just can't figure it out. This cfstoredproc just isn't working. I've tested the actual stored procedure on SQL Server by placing some actual values in the stored procedure and it worked. But when I try to use this cfstoredproc to do the stored procedure it doesn't do anything. The #cfstoredproc.statuscode# is even at 0> can you see anything wrong? here is my stored procedure: UPDATE PasUsers SET picture = '@picture' WHERE userid = '@userid' ~ Paid Sponsorship ~ Get Your Own Dedicated Win2K Server! Instant Activation for $99/month w/Free Setup from SoloServer PIII600 / 128 MB RAM / 20 GB HD / 24/7/365 Tech Support Visit SoloServer, https://secure.irides.com/clientsetup.cfm. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
cfstoredproc and cftransaction
Does work wrapped around ? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: CFSTOREDPROC Error!
Someone else may have more info, but we upgraded our MDAC on one of our servers and consistently got the PCodeRuntimeContextImp error on passthrough queries in Access97 databases. If other similar stored procedures are doing this, you may want to look at the MDAC... jon - Original Message - From: "Bob Silverberg" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, January 12, 2001 12:16 PM Subject: CFSTOREDPROC Error! > I'm receiving the following error intermittently when using the CFSTOREDPROC > tag to run a stored procedure that returns 3 result sets: > > Error Diagnostic Information > unknown exception condition > > PCodeRuntimeContextImp::executeSQLTagCFStoredProc::endTag > > The error occurred while processing an element with a general identifier of > (CFSTOREDPROC), occupying document position (26:1) to (26:96) in the > template file > D:\DEVWEB\INETPUB\WWWROOT\NATIVETRAX\QUERIES\QRY_PRODUCERTRANSACTIONREPORT.C > FM. > > My CFSTOREDPROC tag reads: > > datasource="#request.DSN#" returncode="YES"> > value="#Val(attributes.RptMonth)#"> > value="#Val(attributes.RptYear)#"> > value="#Val(attributes.ClientItemId)#"> > > > > > > The procedure ProducerTransactionReport in turn runs two stored procedures, > the first of which returns one result set and the second of which returns > two result sets. > > The strange thing is that sometimes it works fine, and returns all three > result sets, while other times it returns two result sets, and then issues > the above error message. In each of the stored procedures I SET NOCOUNT ON > at the beginning and SET NOCOUNT OFF at the end. > > This is ColdFusion Professional 4.5.1 SP2 running on W2K Server, and the > database is MS SQL Server 2000, being accessed via OLE DB. > > If anyone has seen this before and/or has any suggestions I would appreciate > hearing from you. > > Thanks, > Bob > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
CFStoredProc vs CFQuery
Are there any benefits to calling stored procedures using CFStoredProc vs. CFQuery? All the stored procs return a single record set. The limitation I am running in to is how to cache the results of a query that is created calling CFStoredProc. TIA, Duane [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
cfstoredproc and cfdump
I'm using the following stored procedure and using cfdump to see the output. This is the stored procedure:- The output from cfdump returns :B1 P_KEY Y 1234567 The "Y" denotes that the user has a normal login. There are three other "flags" that can be returned based on the user's situation "A", "E" or "W" but I don't know what the column names are. How can I reference whether a "Y", "A", "E" or "W" are returned when I don't know the column id's? TIA ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185672 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
cfstoredproc and cache
Is there a way to cache the results of a stored procedure in or around the cfstoredproc tag? Sincerely, Andrew ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229980 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
CFStoredProc out variable
Hi, i have the following code to call a stored procedure which is stored in mysql. SELECT @queryResult; i have followed the instructions on mysql and the stored procedure works fine if i pass in the out as @queryResult. in mysql when i run SELECT @queryResult; it gives me back a value. however when i run it through coldfusion and do a cfdump of the checkOneToOneFolderAdded query it tells me that it is an empty string. i would appreciate any help you can give me on this, or any other way to reference an out variable, thanks very much thanks very much richard ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290358 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cfstoredproc vs cfquery
Anyone know if there is an advantage either way when it comes to calling a stored procedure? Say I have a storedproc called GetAllResults. I can call it 2 ways. Execute GetAllResults Or CFquery seems to be faster to me. I just wondered what everyone else thought about it and if there was an advantage or disadvantage either way that I dont know about. Oh, I also thought it was funny that cfquery was only faster than cfstoredproc when cfquery was NOT cached odd. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield ColdFusion Application Developer HYPERLINK "http://acoderslife.com"http://acoderslife.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.441 / Virus Database: 268.18.3/697 - Release Date: 2/22/2007 11:55 AM ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270462 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cfstoredproc message
Well if it is success or not then you just use the return codes. If you want a specific user defined message you will have to select it into a var and return it as an OUT or as a resultset. "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: Richard Meredith-Hardy To: CF-Talk Sent: Tue Feb 27 06:45:39 2007 Subject: Cfstoredproc message Simple question, I hope How does one get back to CF the message a stored proc normally puts in the messages window when you run it in SQL Server management studio or enterprise manager? Thanks in advance Richard (I've hunted around but difficult to search for the word 'message'...) ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270740 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Cfstoredproc message
Not quite what I'm looking for, I think. This is actually a sp which contains a RESTORE DATABASE command and I'm interested to capture the stuff which goes into messages like: Processed 10240 pages for database 'xx', file 'xxx' on file 1. Processed 1 pages for database 'x', file 'xx_log' on file 1. RESTORE DATABASE successfully processed 10241 pages in 11.732 seconds (7.150 MB/sec). How do I capture that? > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 27 February 2007 07:45 > To: CF-Talk > Subject: Re: Cfstoredproc message > > Well if it is success or not then you just use the return > codes. If you want a specific user defined message you will > have to select it into a var and return it as an OUT or as a > resultset. > > > > > > "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: Richard Meredith-Hardy > To: CF-Talk > Sent: Tue Feb 27 06:45:39 2007 > Subject: Cfstoredproc message > > Simple question, I hope > > How does one get back to CF the message a stored proc > normally puts in the messages window when you run it in SQL > Server management studio or enterprise manager? > > Thanks in advance > > Richard > > (I've hunted around but difficult to search for the word 'message'...) > > > > > > ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270748 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cfstoredproc message
If you use java DB stuff, I assume it would be available... Probably not from the built in CF DB stuff tho. :-/ On 2/27/07, Richard Meredith-Hardy <[EMAIL PROTECTED]> wrote: > Not quite what I'm looking for, I think. > > This is actually a sp which contains a RESTORE DATABASE command and I'm > interested to capture the stuff which goes into messages like: > > Processed 10240 pages for database 'xx', file 'xxx' on file 1. > Processed 1 pages for database 'x', file 'xx_log' on file 1. > RESTORE DATABASE successfully processed 10241 pages in 11.732 seconds (7.150 > MB/sec). > > How do I capture that? ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270897 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Cfstoredproc message
I suppose it's really a mssql question of trapping the generated message(s) and putting them in a var which can be returned to CF. How to do it though? > -Original Message- > From: Dinner [mailto:[EMAIL PROTECTED] > Sent: 28 February 2007 00:38 > To: CF-Talk > Subject: Re: Cfstoredproc message > > If you use java DB stuff, I assume it would be available... > > Probably not from the built in CF DB stuff tho. :-/ > > On 2/27/07, Richard Meredith-Hardy <[EMAIL PROTECTED]> wrote: > > Not quite what I'm looking for, I think. > > > > This is actually a sp which contains a RESTORE DATABASE command and > > I'm interested to capture the stuff which goes into messages like: > > > > Processed 10240 pages for database 'xx', file 'xxx' > on file 1. > > Processed 1 pages for database 'x', file > 'xx_log' on file 1. > > RESTORE DATABASE successfully processed 10241 pages in > 11.732 seconds > > (7.150 MB/sec). > > > > How do I capture that? > > ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270918 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cfstoredproc message
In a nutshell, you don't as you should always have a no count at the top of the SP anyway. You can certainly return a variable of your choosing etc by using custom return codes - which may, or may not be as detailed as you want. "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: Richard Meredith-Hardy To: CF-Talk Sent: Wed Feb 28 04:58:19 2007 Subject: RE: Cfstoredproc message I suppose it's really a mssql question of trapping the generated message(s) and putting them in a var which can be returned to CF. How to do it though? > -Original Message- > From: Dinner [mailto:[EMAIL PROTECTED] > Sent: 28 February 2007 00:38 > To: CF-Talk > Subject: Re: Cfstoredproc message > > If you use java DB stuff, I assume it would be available... > > Probably not from the built in CF DB stuff tho. :-/ > > On 2/27/07, Richard Meredith-Hardy <[EMAIL PROTECTED]> wrote: > > Not quite what I'm looking for, I think. > > > > This is actually a sp which contains a RESTORE DATABASE command and > > I'm interested to capture the stuff which goes into messages like: > > > > Processed 10240 pages for database 'xx', file 'xxx' > on file 1. > > Processed 1 pages for database 'x', file > 'xx_log' on file 1. > > RESTORE DATABASE successfully processed 10241 pages in > 11.732 seconds > > (7.150 MB/sec). > > > > How do I capture that? > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270921 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Cfstoredproc message
>I suppose it's really a mssql question of trapping the generated message(s) >and putting them in a var which can be returned to CF. >How to do it though? There may be better alternatives but two possibilities are - capture the warnings using java/jdbc as Dinner suggested - create a stored procedure that uses xp_cmdshell and sqlcmd/osql (sql 2005/2000) to capture the messages and return them to cfstoredproc Then parse the output messages Janet ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271054 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFStoredProc bug?
You should change your cSearch to @cSearch -Original Message- From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED] Sent: 21 December 2004 17:02 To: CF-Talk Subject: CFStoredProc bug? Okay, I just ran across this and I'm not sure if it's something that has been looked into before but it does give me reason for concern. I have a stored procedure, up_get_status, that takes two parameters, @nStatus_id and @cSearch. It is basically SELECT cLabel FROM Status WHERE nStatus_ID = CASE WHEN @nStatus_id = 0 THEN nStatus_id ELSE @nStatus_id END AND cLabel = CASE WHEN @cSearch IS NULL THEN cLabel ELSE @cSearch END I have the parameters set with defaults in the stored proceudure. In my code I have the following call (taking it out of the cffunction since I'm doing more than just calling this one table): When I run this I get a SQL error "Error converting data type varchar to int". This is the same result I get when I try to run 'up_get_status 'Pending'' as opposed to 'up_get_status @cSearch='Pending' ' Is this a new thing?? Hatton ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188393 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: CFStoredProc bug?
It's normal and not buggy. CFStoredproc passes values in the order that they are declared. I believe the "variable" attribute to the cfprocparam tag tells CF what the variable name will be (for OUT and IN/OUT variables) Stored procedures can be called by name sp_proc @two='xyz', @one='abc' Or by order sp_proc 'abc', 'xyz' -nathan strutz C. Hatton Humphrey wrote: > Okay, I just ran across this and I'm not sure if it's something that > has been looked into before but it does give me reason for concern. > > I have a stored procedure, up_get_status, that takes two parameters, > @nStatus_id and @cSearch. It is basically > > SELECT cLabel > FROM Status > WHERE nStatus_ID = CASE WHEN @nStatus_id = 0 THEN nStatus_id ELSE > @nStatus_id END > AND cLabel = CASE WHEN @cSearch IS NULL THEN cLabel ELSE @cSearch END > > I have the parameters set with defaults in the stored proceudure. In > my code I have the following call (taking it out of the cffunction > since I'm doing more than just calling this one table): > > > > > > When I run this I get a SQL error "Error converting data type varchar to int". > > This is the same result I get when I try to run 'up_get_status > 'Pending'' as opposed to 'up_get_status @cSearch='Pending' ' > > Is this a new thing?? > > Hatton > > ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188398 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: CFStoredProc bug?
If you mean in the CFPROCPARAM tag, no you should not. @cSearch is not the variable. @cSearch is the internal holder for the variable being passed in which is cSearch. > You should change your cSearch to @cSearch > > > > -Original Message- > From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED] > Sent: 21 December 2004 17:02 > To: CF-Talk > Subject: CFStoredProc bug? > > Okay, I just ran across this and I'm not sure if it's something that > has been looked into before but it does give me reason for concern. > > I have a stored procedure, up_get_status, that takes two parameters, > @nStatus_id and @cSearch. It is basically > > SELECT cLabel > FROM Status > WHERE nStatus_ID = CASE WHEN @nStatus_id = 0 THEN nStatus_id ELSE > @nStatus_id END > AND cLabel = CASE WHEN @cSearch IS NULL THEN cLabel ELSE @cSearch END > > I have the parameters set with defaults in the stored proceudure. In > my code I have the following call (taking it out of the cffunction > since I'm doing more than just calling this one table): > > > value="#f_search#"> > > > When I run this I get a SQL error "Error converting data type varchar to > int". > > This is the same result I get when I try to run 'up_get_status > 'Pending'' as opposed to 'up_get_status @cSearch='Pending' ' > > Is this a new thing?? > > Hatton > > > > ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188401 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: CFStoredProc bug?
Oh yeahsorry my bad = you only need it with @ if you are using @dbvarname. Thanks MD, was a little out on that one ;-) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 21 December 2004 17:23 To: CF-Talk Subject: RE: CFStoredProc bug? If you mean in the CFPROCPARAM tag, no you should not. @cSearch is not the variable. @cSearch is the internal holder for the variable being passed in which is cSearch. > You should change your cSearch to @cSearch > > > > -Original Message- > From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED] > Sent: 21 December 2004 17:02 > To: CF-Talk > Subject: CFStoredProc bug? > > Okay, I just ran across this and I'm not sure if it's something that > has been looked into before but it does give me reason for concern. > > I have a stored procedure, up_get_status, that takes two parameters, > @nStatus_id and @cSearch. It is basically > > SELECT cLabel > FROM Status > WHERE nStatus_ID = CASE WHEN @nStatus_id = 0 THEN nStatus_id ELSE > @nStatus_id END > AND cLabel = CASE WHEN @cSearch IS NULL THEN cLabel ELSE @cSearch END > > I have the parameters set with defaults in the stored proceudure. In > my code I have the following call (taking it out of the cffunction > since I'm doing more than just calling this one table): > > > value="#f_search#"> > > > When I run this I get a SQL error "Error converting data type varchar to > int". > > This is the same result I get when I try to run 'up_get_status > 'Pending'' as opposed to 'up_get_status @cSearch='Pending' ' > > Is this a new thing?? > > Hatton > > > > ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188403 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: CFStoredProc bug?
As an aside for info purposes, the variable attribute in cfprocparam is for info sent from the SP to CF. To bind a passed value to a SP variable, you use dbvarname. > It's normal and not buggy. > > CFStoredproc passes values in the order that they are declared. I > believe the "variable" attribute to the cfprocparam tag tells CF what > the variable name will be (for OUT and IN/OUT variables) > > Stored procedures can be called by name > > sp_proc @two='xyz', @one='abc' > > Or by order > > sp_proc 'abc', 'xyz' > > -nathan strutz > > > > C. Hatton Humphrey wrote: > > Okay, I just ran across this and I'm not sure if it's something that > > has been looked into before but it does give me reason for concern. > > > > I have a stored procedure, up_get_status, that takes two parameters, > > @nStatus_id and @cSearch. It is basically > > > > SELECT cLabel > > FROM Status > > WHERE nStatus_ID = CASE WHEN @nStatus_id = 0 THEN nStatus_id ELSE > > @nStatus_id END > > AND cLabel = CASE WHEN @cSearch IS NULL THEN cLabel ELSE @cSearch END > > > > I have the parameters set with defaults in the stored proceudure. In > > my code I have the following call (taking it out of the cffunction > > since I'm doing more than just calling this one table): > > > > > > value="#f_search#"> > > > > > > When I run this I get a SQL error "Error converting data type varchar to > int". > > > > This is the same result I get when I try to run 'up_get_status > > 'Pending'' as opposed to 'up_get_status @cSearch='Pending' ' > > > > Is this a new thing?? > > > > Hatton > > > > > > ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188405 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: CFStoredProc bug?
It's one that throws everyone due to a bad example somewhere in the past. Actually, the @ should never be used in either variable (outgoing data from the SP) or dbvarname (binding to a sp var name). I had to remove all of the @ from dbvarnames in the mothernature code. Ug. > Oh yeahsorry my bad = you only need it with @ if you are using > @dbvarname. > > Thanks MD, was a little out on that one ;-) > > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 21 December 2004 17:23 > To: CF-Talk > Subject: RE: CFStoredProc bug? > > If you mean in the CFPROCPARAM tag, no you should not. @cSearch is not the > variable. @cSearch is the internal holder for the variable being passed in > which is cSearch. > > > You should change your cSearch to @cSearch > > > > > > > > -Original Message- > > From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED] > > Sent: 21 December 2004 17:02 > > To: CF-Talk > > Subject: CFStoredProc bug? > > > > Okay, I just ran across this and I'm not sure if it's something that > > has been looked into before but it does give me reason for concern. > > > > I have a stored procedure, up_get_status, that takes two parameters, > > @nStatus_id and @cSearch. It is basically > > > > SELECT cLabel > > FROM Status > > WHERE nStatus_ID = CASE WHEN @nStatus_id = 0 THEN nStatus_id ELSE > > @nStatus_id END > > AND cLabel = CASE WHEN @cSearch IS NULL THEN cLabel ELSE @cSearch END > > > > I have the parameters set with defaults in the stored proceudure. In > > my code I have the following call (taking it out of the cffunction > > since I'm doing more than just calling this one table): > > > > > > > value="#f_search#"> > > > > > > When I run this I get a SQL error "Error converting data type varchar to > > int". > > > > This is the same result I get when I try to run 'up_get_status > > 'Pending'' as opposed to 'up_get_status @cSearch='Pending' ' > > > > Is this a new thing?? > > > > Hatton > > > > > > > > > > > > ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188406 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
