CF ODBC 3 Support

[MicroFocusMike]

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?

[Dave Watts]

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

[Dave Watts]

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

[MicroFocusMike]

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.

[Charlie Arehart]

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. 

[BKBK]

@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? 

[MicroFocusMike]

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]

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.

[MicroFocusMike]

Well, I'm not sure why the data source is such a concern, as I'm not reporting a problem creating it, but here goes.
On a Windows 10 machine with ColdFusion 2018 (64-bit) and my product, Relativity Client (64-bit), already installed:

1) From the Start menu, scroll down to Windows Administrative Tools, click, then click on ODBC Data Sources (64-bit).

2) The ODBC Data Source Administrator (64-bit) will open. Click the System DSN tab. Click the Add button.

Here is a picture before the Add button is clicked.

3) The Create New Data Source dialog will be displayed. Select Relativity Client (64-bit) and click the Finish button. Here is another picture before the Finish button is clicked.

4) The ODBC Relativity C/S Setup dialog will appear. In Data Source Name, enter Data Client Verify. In Server Name, select tcp:localhost:1583, in Server DSN, select Data Server Verify. Click the OK button. Here is another picture before the OK button is clicked.

OK, so a bit of explanation here. The Client/Server version of my product was used in my testing, mostly because the ColdFusion was a 64-bit version. The server was running on the local host on port 1583, and I have already created a data source on the server with the name Data Server Verify.

5) The ODBC Data Source Administrator (64-bit) dialog will reappear. Click the OK button to close. Another picture:

6) Start a browser and nagivate to 127.0.0.1:8500/CFIDE/administrator/index.cfm.

7) Login with the administrator account.

8) If necessary, click on Data & Services in the left-hand pane.

9) In Data Source Name, enter the name of the Data Source that ColdFusion will use. In Driver, select the driver type. I used CFVerify and ODBC Socket. Click the Add button.

10) In ODBC DSN field, I placed Data Client Verify.

So, at this point, I'm going to confess that the Azure VM where I did all this testing is no longer available. I tried to verify this with the ColdFusion 2021 VM that I created, but the damn thing won't start the ColdFusion 2021 ODBC Server Service. But, to the best of my knowledge, this is what we did to create the data source on ColdFusion 2018. It was really quite simple and worked right away. It was what happened when I tried to use the ColdFusion data source.

[Charlie Arehart]

Mike, while BKBK works with you on his consideration, I just want to ask about your last sentence. When you say openfusion, is that just a finger fumble for cf? or are you referring to some real thing with that name? If so, please tell us more. 

[MicroFocusMike]

Yes. That's a typo.

[Charlie Arehart]

OK, and while you consider BKBK's last questions to you, I have some more things for you to consider as well. I've numbered them, to make it easier for you to respond to.

BTW, to be clear, this is a community support site. We're not Adobe employees, just folks who like to help CF folks solve problems. Please keep that in mind, if any points further raise your ire toward the situation or this discussion.

1) You mentioned on the 13th that you might try things on CF2021 to see if it made a difference (I wouldn't necessarily expect it, but it's worth a shot).

You said you had tried it previously but could not even create the datasource. What was the issue there? It may be that the CF ODBC services (there are two) are not starting for you. I realize you may not want to go down the path of trying to resolve that, if your code doesn't even work on CF2018 (with an odbc 3 driver).

2) You also mused in that message on the 13th that "Adobe would want their ODBC 3 support to work". As Dave has shared, the ODBC support in CF really  comes from Progress and their DataDirect drivers, which ADobe licenses.

And it doesn't help that ODBC is waning tech and support for it in CF is waning it seems. (I realize some might quip that's the pot calling the kettle black.) More on ODBC support in CF in a moment. 

3) Indeed, you said in the same msg that "Tomorrow we'll try our JDBC driver." I assume that was on CF2018 (or did yoy perhaps try 2021 as well)? I don't see that we heard back from you on that. Did it work ok?

I realize you warned us all in your initial note here that you "don't care if a JDBC driver is the recommended solution; that is not a helpful response." I only ask now because a) you offered and b) at least you could then know that THAT does work for you and for your customers (it seems you are a vendor of a tool that uses such drivers, and you have customers using CF with them, is that right?)

4) And if JDBC works while ODBC does not, can you help us (and Adobe, if they may read this) to understand if there's a particularly compelling reason to get the ODBC drivers working if the JDBC ones do?  If it's just "CF says ODBC should work, so it should work", that's fine. Again, just trying to get a full grasp of things, for you and all concerned.

5) And finally in that case (and assuming you get no better answers here), I had shared in my first reply that you should at least create a ticket at tracker.adobe.com, since you had so much low-level detail to share on the issue. I suspect they rarely hear from anyone with such low-level knowledge of ODBC drivers.

6) It does seem ODBC shouljd still be supported, at least in that it's still listed in the CF Admin.  I do noticee that there's no mention of ODBC on the CF2021 support matrix. I'm not sure if there's been any formal change in the status. That, too, is something you could ask in that tracker ticket.

Hope that's helpful.

[MicroFocusMike]

<<1) You mentioned on the 13th that you might try things on CF2021 to see if it made a difference (I wouldn't necessarily expect it, but it's worth a shot).

You said you had tried it previously but could not even create the datasource. What was the issue there?>>

The CF data source setup wizard was reporting an error that the JDBC data source could not be found. This seems like a separate problem. I am willing to acknowledge that the VM may not have been set up properly. We had the most success with an Azure machine that already had a CF2018 preconfigured on it. My co-worker who set up the Azure machine didn't say why he used CF2018 rather than CF2021. Perhaps he didn't see one. Perhaps there wasn't one. Perhaps CF2018 was closer to what the customer was using.

I don't see any questions in 2, so moving on.

<<Indeed, you said in the same msg that "Tomorrow we'll try our JDBC driver." I assume that was on CF2018 (or did yoy perhaps try 2021 as well)? I don't see that we heard back from you on that. Did it work ok?>>

Yes, it did work. I believe that I only tried it with CD2018.

<<(it seems you are a vendor of a tool that uses such drivers, and you have customers using CF with them, is that right?)>>

Well, I'm a vendor of such drivers, to be completely clear.

<<And if JDBC works while ODBC does not, can you help us (and Adobe, if they may read this) to understand if there's a particularly compelling reason to get the ODBC drivers working if the JDBC ones do?>>

As a vendor of ODBC drivers, I am very concerned that my product behaves correctly, which is what I am really mostly concerned with here. I wrote the forum post in hopes that Adobe would see it and address the issue, if they cared about it. I'm not really interested in purchasing a copy of ColdFusion. But, from another standpoint, our customer was trying to use a single-tier version of our product, which is cheaper. If we were to require them to use the JDBC solution, then we'd have to convince them to purchase the two-tier (Client/Server) version. I'm sure that our sales guys would love to sell the more expensive version to them, but usually what happens is that our customers just continues to run of the older version of our product, and I'm trying to remove roadblocks to that.

<<And finally in that case (and assuming you get no better answers here), I had shared in my first reply that you should at least create a ticket at tracker.adobe.com, since you had so much low-level detail to share on the issue. I suspect they rarely hear from anyone with such low-level knowledge of ODBC drivers.>>

I still plan to do this, as soon as I can reproduce the problem on CF2021.

<<It does seem ODBC should still be supported, at least in that it's still listed in the CF Admin.>>

At the end of the day, it's all about enabling customers to solve their problems. I know that people keep saying that ODBC is dead and Microsoft keeps writing replacements to it, but it is something that is just too embedded in the eco-system. And the replacements just don't have a compelling enough reason to make people switch. Yes, the early 90's ODBC API is very hard to use. There's many times that I've said "This is so not the API that we'd create today." (There are just too many places where 32-bit values are being passed in a pointer argument. Shutter.) But, it is what it is. At one point in the early 2000's we started a project to support ADO.net, but we put it down for some reason, and to be honest, we just didn't have any customers contact us and ask for ADO.net support, so we never got back to it. There were lots of request for JDBC support, but not ADO.net. And, let's be honest, Microsoft has replaced ADO.net with something else.

So, I'm going to try and answer BKBK's questions at some point, but to be quite honest, this problem is now in my rearview mirror. I really think that submitting the defect to Adobe directly should be my next step.

[Charlie Arehart]

Yep, on your conclusion. And thanks for the rest. Just two points of clarification to your comments:

• you don't need to demonstrate the problem happening on CF2021. If you can cause it on 2018, it's supported for 2 more years.
• you don't need to "purchase a copy of ColdFusion" (I don't see anyone suggesting that you did need to). Note that there are completely free editions, all based on the same installer.  When you install CF, you are asked (in the full installer, or in the cfinstall script of the zip installation mode, new in CF2021) whether you want to put in a license key, or go with a 60-day trial (which runs as CF's "Enterprise" edition), or with the free Developer edition (which runs much like Enterprise but has ip address limitations). If you go with the Trail, but don't put in a license key during that time, that also reverts to teh free Developer edition. Again, I'm not saying you need to install it...only that you can (without paying anyone), if you want to.

I will add that we (Dave, BKBK, and I) have been in these forums answering thousands of questions per year. I don't recall ever any other occurrence of a CF OBDC datasource returning only one record when it should return more. Just clarifying that this seems either a rare problem or perhaps a new one that has not been noticed given the reduced call for use of ODBC with CF. Again, given the detail you shared originally, I think you may get somewhere in providing that to Adobe at tracker.adobe.com.

If you do create a ticket, and want to share the number here, it may lead others (who find this thread in the future) to come join in on the conversation/voting on the ticket, which could aid your cause--or at least could aid theirs if they experience the same issue and want to get it solved.