Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

OLEDB and CF 4.5

Posted on 2001-07-08
25
Medium Priority
?
311 Views
Last Modified: 2013-12-24
I am fairly experienced with CF, but I am having a weird problem.  I want to execute a stored procedure using the OLEDB connection I have created for the application.  I continue to receive the following error when I attempt to call a stored procedure which just does a simple select statement:

unknown exception condition

PCodeRuntimeContextImp::executeSQLTagCFStoredProc::endTag

...does anyone know the cause.  BTW, I do not receive this error when using an ODBC datasource (I just want/need to get away from ODBC).
0
Comment
Question by:jg4smile
  • 15
  • 10
25 Comments
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6264913
Can you post the code which is causing the error?

Spike
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6265240
basically the SP is as follows:

create procedure sp_test
as
begin
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6265277
sorry about that:

basically the SP is as follows:

create procedure sp_test
as
begin
  select * from pubs;
end;

ColdFusion Code:

<cfstoredproc procedure="sp_test" datasource="#application.dsn#">
   <cfprocresult name="rs1">
</cfstoredproc>

Even this simple case is causing the error above.  I can properly call this stored procedure using the same code as above if my connection in ColdFusion administrator is an ODBC type and not OLE DB (as this is my temporary solution).  From the help files I see that the <CFSTOREDPROC> tag only mentions ODBC and Native support (see below):

The CFSTOREDPROC tag is the main tag used for executing stored procedures via an ODBC or native connection to a server database. It specifies database connection information and identifies the stored procedure.

Does this mean that the tag is not supported for OLEDB connections?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 4

Expert Comment

by:CF_Spike
ID: 6265282
That's not what I meant,

The error is occurring in the ColdFusion code. Can you post the code you are using to call the stored procedure.

Spike
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6265322
ok,

Ignore the last post.

Have you tried calling the stored procedure from a normal cfquery block or using a dbtype attribute in the cfstoredproc tag?

Spike
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6265588
sorry about that:

basically the SP is as follows:

create procedure sp_test
as
begin
  select * from pubs;
end;

ColdFusion Code:

<cfstoredproc procedure="sp_test" datasource="#application.dsn#">
   <cfprocresult name="rs1">
</cfstoredproc>

Even this simple case is causing the error above.  I can properly call this stored procedure using the same code as above if my connection in ColdFusion administrator is an ODBC type and not OLE DB (as this is my temporary solution).  From the help files I see that the <CFSTOREDPROC> tag only mentions ODBC and Native support (see below):

The CFSTOREDPROC tag is the main tag used for executing stored procedures via an ODBC or native connection to a server database. It specifies database connection information and identifies the stored procedure.

Does this mean that the tag is not supported for OLEDB connections?
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6265606
Actually, in one of the other pages (well an include), I did call the procedure successfully from a CFQUERY block, but I would like to enhance the functionality at some point to capture error codes (or return codes) and/or return multiple result sets.  I'm hoping that CF5 has corrected this error (if it is an CF Server error), but to answer your questions yes, and it worked fine.
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6265796
Actually, in one of the other pages (well an include), I did call the procedure successfully from a CFQUERY block, but I would like to enhance the functionality at some point to capture error codes (or return codes) and/or return multiple result sets.  I'm hoping that CF5 has corrected this error (if it is an CF Server error), but to answer your questions yes, and it worked fine.
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6266026
Did you try the dbtype attribute in the cfstoredproc tag?

Spike
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6266029
Out of interest, why do you want to get away from ODBC?

Spike
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266306
Actually, in one of the other pages (well an include), I did call the procedure successfully from a CFQUERY block, but I would like to enhance the functionality at some point to capture error codes (or return codes) and/or return multiple result sets.  I'm hoping that CF5 has corrected this error (if it is an CF Server error), but to answer your questions yes, and it worked fine.
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266319
I don't see a DBTYPE attribute for <CFSTOREDPROC>...
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6266331
That's a bit strange, it appears in some of the help documents on my machine, but not in others. If you are unsure you can turn on strict attribute validation in CF Server Administrator and try running a page with it in. You should get an error if it isn't a valid attribute.

To repeat the question from above. Why do you want to move to OLE DB?

Spike
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266341
OLE DB appears to transact faster for SQL Server...
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6266358
When you say it appears to transact faster can you clarify what you mean. I have never found OLE DB to be faster on any system apart from for the first database connect after the server has been restarted, or the db connections have been flushed.

Typical numbers are around 300ms for the first database connect on ODBC and 30 or so for each subsequent connect. The same page with OLE DB will normally take around 80 ms for each DB Connect including the first one.

I would be interested to hear if you have different numbers.

Spike
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266364
I don't see a DBTYPE attribute for <CFSTOREDPROC>...
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266374
well we usually do transactions of 4million records or more.  we got about a 30% +/- 10% performance gain on our longest running queries.  it's a preference of mine also because of some of the JSP and CORBA support i'm currently implementing.
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6266436
I'm surprised that the data comes back faster for the same query, but the JSP CORBA thing makes a lot of sense.

I'm just wondering if the error you are getting isn't a locking issue.

If you are using application variables without locks then you could run into problems in some situations. It's a bit of a wild shot, but it might be worth trying the following:

<CFLOCK SCOPE="Application" TYPE="READONLY" TIMEOUT="2">
<CFSET dsn = application.dsn>
</CFLOCK>

<cfstoredproc procedure="sp_test" datasource="#variables.dsn#">
  <cfprocresult name="rs1">
</cfstoredproc>

Like I said, it's a bit unlikely that this will help, but it's probably worth trying.

Spike
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266447
well we usually do transactions of 4million records or more.  we got about a 30% +/- 10% performance gain on our longest running queries.  it's a preference of mine also because of some of the JSP and CORBA support i'm currently implementing.
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266449
have you been able duplicate what I have above?
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6266487
Nope,

The following code works fine on my machine.

<cfstoredproc procedure="sp_test" datasource="tests">
  <cfprocresult name="rs1">
</cfstoredproc>


<cfstoredproc procedure="sp_test" datasource="testsoledb">
  <cfprocresult name="rs1">
</cfstoredproc>

I only have about 30 records in that table though.

I am also running CF 5.0

Spike
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266597
have you been able duplicate what I have above?
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6266611
i think i'll have to go ahead and upgrade to CF5 i'll credit you with the answer.  we've been able to exchange enough information that i believe the exception i'm receiving is about the version i'm running (oh, btw, i have not installed any of the service packs to the CF4.5.1 i'm running.
0
 
LVL 4

Accepted Solution

by:
CF_Spike earned 400 total points
ID: 6266631
Thanks,

I think the service pack for 4.5.2 fixed quite a few bugs, but I don't have a list to hand. It's a free upgrade, so it might be worth checking before paying for 5.0. Personally I have found 5.0 to be much faster and I don't know how I lived without query of query and User Defined functions.

Spike
0
 
LVL 1

Author Comment

by:jg4smile
ID: 6981143
CF_Spike hung in there with me on an issue that was obviously a part of the CF bug/fix list.  Thanks CF_Spike.  If you're still around we'll run into each other again...
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month11 days, 21 hours left to enroll

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question