jg4smile
asked on
OLEDB and CF 4.5
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::ex
...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).
unknown exception condition
PCodeRuntimeContextImp::ex
...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).
ASKER
basically the SP is as follows:
create procedure sp_test
as
begin
create procedure sp_test
as
begin
ASKER
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.d
<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?
basically the SP is as follows:
create procedure sp_test
as
begin
select * from pubs;
end;
ColdFusion Code:
<cfstoredproc procedure="sp_test" datasource="#application.d
<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?
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
The error is occurring in the ColdFusion code. Can you post the code you are using to call the stored procedure.
Spike
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
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
ASKER
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.d
<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?
basically the SP is as follows:
create procedure sp_test
as
begin
select * from pubs;
end;
ColdFusion Code:
<cfstoredproc procedure="sp_test" datasource="#application.d
<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?
ASKER
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.
ASKER
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.
Did you try the dbtype attribute in the cfstoredproc tag?
Spike
Spike
Out of interest, why do you want to get away from ODBC?
Spike
Spike
ASKER
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.
ASKER
I don't see a DBTYPE attribute for <CFSTOREDPROC>...
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
To repeat the question from above. Why do you want to move to OLE DB?
Spike
ASKER
OLE DB appears to transact faster for SQL Server...
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
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
ASKER
I don't see a DBTYPE attribute for <CFSTOREDPROC>...
ASKER
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.
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
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
ASKER
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.
ASKER
have you been able duplicate what I have above?
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
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
ASKER
have you been able duplicate what I have above?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
Spike