Participating Frequently

Question

CF ODBC 3 Support

Forum|Forum|4 years ago
April 13, 2021
3 replies
949 views

I have a customer who is trying to use my ODBC 3 driver with ColdFusion 2018. Their website worked with my ODBC 2.5 driver, but didn't with my ODBC 3 driver. Of course, I assumed that it was my Driver, and I investigated to see what we were doing wrong. Looking at the ODBC Trace, I'm seeing ColdFusion calling SQLSetStmtAttr with the attribute SQL_ATTR_ROW_ARRAY_SIZE of 1191. My driver returns 01S02, indicating that is more rows than it can support, and I see ColdFusion call SQLGetStmtAttr with SQL_ATTR_ROW_ARRAY_SIZE to fetch the number of rows that are supported. Then, ColdFusion calls SQLFetch, which fetchs 14 rows into the array that it allocated. ColdFusion then displays the first row from the array and calls SQLFetch again, gets a SQL_NO_DATA_FOUND, and stops. ColdFusion needs to call SQLSetStmtAttr with the attribute SQL_ATTR_ROW_FETCHED_PTR to determine the number of rows that were actually fetched, or it needs to stop trying to use SQL_ATTR_ROW_ARRAY_SIZE. I have investigated my driver and it seems to be working correctly. I wrote a C program that used the customer's data source using SQL_ATTR_ROW_ARRAY_SIZE and it worked properly with my driver.

So, I think that ColdFusion 2018 is not working properly with Row Arrays. If there is some other mechanism that ColdFusion uses to determine the actual rows fetched, I'd be happy to see if my driver is doing something wrong there, but at this point I think that is not my driver's problem.

And, BTW, I don't care if a JDBC driver is the recommended solution; that is not a helpful response. Any other advice?

This topic has been closed for replies.

BKBK

Adobe Expert

@MicroFocusMike : a customer who is trying to use my ODBC 3 driver with ColdFusion 2018.

That is unclear to me. Please explain what kind of driver it is (for example, file name), and how the customer goes about trying to use it in ColdFusion.

> Their website worked with my ODBC 2.5 driver

What kind of driver was this (for example, file name), and how did the customer go about using it in ColdFusion?

M

MicroFocusMikeAuthor

Participating Frequently

It is an ODBC system data source. One opens it with a connection string. For example DSN=DataSourceName.

I don't have a test case from the customer, but I was able to reproduce the problem with:
<html>
<head>
<title>Defect 8221 - Query executed by ColdFusion only returns one of the many, actual, matching rows</title>
</head>
<body>
<cfquery name="C001" datasource="defect8221">
SELECT * FROM C001_IC_Process_Equipment
</cfquery>
<h4>C001_IC_ProcessEquipment</h4>
<table>
<cfoutput query="C001">
<tr>
<td>#PrcsseqUniqueKey#</td>
<td>#PrcsseqName#</td>
<td>#PrcsseqLocation#</td>
</tr>
</cfoutput>
</table>
</body>
</html>
The data source type, with OpenFusion 2018, was ODBC Socket. I hope that helps.

BKBK

Adobe Expert

Thanks for your reply. Nevertheless, my questions remain unanswered.

Both of my questions amount to the following.

When you configure the ODBC datasources in the ColdFusion Administrator, what values do you use for ODBC DSN?

How did you set up this ODBC DSN? For the benefit of all, please mention the steps you followed. For example, you mentioned a version 3 driver, and compared it to a version 2.5 driver. This implies that your explanation will begin with a named driver file of a given version.

Charlie Arehart

Adobe Expert

Mike, with that great level of detail, consider creating a bug report at tracker.adobe.com. Adobe tends not to respond here, but should there.

That said, I appreciate all the additional replies/info from Dave and you.

/Charlie (troubleshooter, carehart. org)

D

Dave Watts

Adobe Expert

A JDBC driver is the recommended solution ... unless you're writing ODBC database drivers! So I'm not going to recommend that to you, although I'd recommend it to CF people using ODBC drivers generally.

CF uses a third-party product, called Datadirect Connect for ODBC, to connect to ODBC drivers. I think that's now owned by Progress. I'd spend a few minutes with them to see if they support ODBC 3 - as far as I know, they don't, but I honestly have not kept up to date with the ODBC situation lately. If not, I'd recommend to your CF customers that they stick with your ODBC 2.x drivers if that's a possibility for them.

Dave Watts, Eidolon LLC

D

Dave Watts

Adobe Expert

Turns out that ODBC 3 is old, and has been around forever, so what do I know? The Progress site for SequeLink, the JDBC-ODBC bridge used by ColdFusion, says that any ODBC 3.x driver is supported.

https://www.progress.com/supported-configurations/datadirect?ds=sequelink

So, the next step for me I guess would be to try using it directly from Windows and some ODBC client like Access or whatever to see if that works.

The problem, as I see it, is that this is really a Datadirect issue and Adobe might not be so helpful.

Dave Watts, Eidolon LLC

M

MicroFocusMikeAuthor

Participating Frequently

We have tried our ODBC 2.5 driver and it works. Tomorrow we'll try our JDBC driver.

I would think that Adobe would want their ODBC 3 support to work. My opinion is that I'd like my product to work regardless of Adobe support. So, as long as there isn't a problem in my code and I have a work around for the customer, I'm good.

I must admit that I wonder if the ColdFusion 2021 version works better. I tried installing it on a VM, but I couldn't get it to create an ODBC Data Source. We found an Azure machine with ColdFusion 2018 and so gave it a go.

I decided to post something in the forum just in case there's something that I'm missing. Perhaps we'll try 2021 one more time before we move on.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded