Solved

OLEDB and CF 4.5

Posted on 2001-07-08
25
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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