ODBC datasource from Lucee

dev support
1

Is there any way to set Lucee up to connect to an ODBC datasource that is
configured in Windows? We have some text-based datasources that are set up
using the Windows ODBC tool. We’d like to connect to those via Lucee, but I
can’t seem to find an option to use ODBC for a datasource.

2

Java 1.8 dropped support for the JDBC-ODBC bridge, so if you’re on the
latest supported version of Java you would not be able to use ODBC.

http://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/bridge.html

On Friday, March 10, 2017 at 7:00:17 AM UTC-8, Michael Sprague wrote:

Is there any way to set Lucee up to connect to an ODBC datasource that is
configured in Windows? We have some text-based datasources that are set up
using the Windows ODBC tool. We’d like to connect to those via Lucee, but I
can’t seem to find an option to use ODBC for a datasource.

3

Interesting. Wondering how Adobe CF does it on Java 1.8 then. Something
proprietary to them, I suppose.

4

Thanks Andrew and Joseph. In case anyone stumbles on this thread in the
future, I thought I should mention that I did find a solution.

That will buy me some time until legacy code can be updated to a better
method of processing the raw text files that are used as a data source.

-Mike

On Fri, Mar 10, 2017 at 4:45 PM, Joseph Gooch <@Joseph_Gooch> wrote:

Adobe packages Sequelink by DataDirect.

SequeLink Connectors & Drivers | Progress DataDirect

JDBC driver that talks to a windows process that can then access the ODBC
datasources and proxy them. But it’s a commercial product that Adobe
packages with ACF.

-G

1 Like
5

I went to the Lucee group yesterday and replied to this thread. I was then notified by a moderator that the group had been moved to dev.lucee.org. So here I am. My question was: has the problem in this thread ever been solved? I am writing a backdoor intranet application that will interface with an accounting program called AccountMate. For dev purposes, we installed the AccountMate 10 Demo. It comes with MSSQL Express. I need to be able to select from and update tables in the AccountMate database called “sample999.” Tho I can connect to this database using Microsoft SQL Server Manager Studio, I am NOT able to connect to it through the Lucee administrator (database) with any of the drivers supplied. Next, the AccountMate dev team manager suggested I connect using ODBC. However, I find confusing information about that here in this thread. Support for it was dropped? And never reinstated? It really doesn’t matter to me what driver I use to connect, but until I can connect with Lucee, my project has come to a complete, grinding halt. Can someone help me with this problem?

Thanks,
Zy

6

IIRC, SQL Express normally installs named pipes but doesn’t enable the TCP listener - SSMS is probably using named pipes.

You probably want to configure the SQL Express instance to enable support for TCP - that would be my guess.

-G

1 Like
7

Yes, we are using SQL Server Express using the bundled jTDS driver. You do need to enable the TCP/IP protocol when configuring the SQL Server.

8

Hello Joe and Julian,

I appreciate your attempts to help me. However, I am nothing more than a ColdFusion coder. I’m not a Java or Lucee developer. I’m not sure what I’m being asked to do. Hate to ask it like this, but could you “dumb this down” for me a bit?

Many thanks,
Zy

9

Hi Zy. You should be able to do this using the SQL Server Configuration Manager GUI.

Just follow steps 1 and 2 of these instructions which include screenshots

Once your SQL Server is listening on TCP you can set up an MS datasource in Lucee, just as you would in ColdFusion.

10

you could use opencsv http://opencsv.sourceforge.net

here is an example

with Lucee you can just pass in the path to the jar file in the createObject call as an array of paths

i.e.

csvReader = createObject("java","au.com.bytecode.opencsv.CSVReader", ['..\libs\opencsv-2.3.jar']);
11

Wrong thread Zac? This one’s about SQL Server data sources not CSV.

12

at the start it was talking about odbc text datasources, I just replied at the end, sorry for the confusion

13

Right, understood.

14

Thank you Julian. I will try this tomorrow afternoon.

Best Regards,
Zy

15

Hello Julian,

I tried to follow steps 1 and 2 of the instructions with screenshots. But what I see on my screen isn’t what the screenshots show. I’ll upload a screenshot of what I’m seeing.

SQLserverConfigurationManager

I do not see “Protocols for SQLEXPRESS” The only protocols are for AM10DEMO, for which you can see Named Pipes and TCP/IP is already enabled. I right clicked the “TCP/IP” child node for “Protocols for AM10DEMO” and selected properties, then followed the instructions for step 2. I used your TCP port 5171 because I didn’t know what else to do.

I am still unable to set up an MS datasource for it in Lucee. What datasource “type” should I be using? I only see the following:

lucee%20datasource%20types

Tho I have configured a Windows ODBC connection that tests okay, I don’t see an ODBC type. And none of the others seem to work no matter what values I enter into the Lucee dialog.

16

Zy,

I understand you’re a coder. But please understand what you’re asking isn’t a Lucee issue. You’re going to have to put on your systems administration hat, and gain some understanding of the technologies you’re trying to use before this works.

ODBC - Open DataBase Connectivity

JDBC - Java DataBase Connectivity

They solve the same problem, for different platforms. Lucee has a microsoft SQL driver for JDBC, Windows has ODBC drivers. Suggesting Lucee should use ODBC isn’t going to solve your problem, and your account manager suggesting ODBC isn’t your problem either.

To connect from Lucee to SQL you need to have a bunch of puzzle pieces in place.

  1. Is the SQL server installed on the same server as Lucee, or a different one? Is SSMS on the same server as Lucee? If it’s the same server, you shouldn’t have firewalling in the way. If it’s a different server, you’ll either need to disable the windows firewall on the SQL server, or put through an exception… which leads to 2

  2. What protocols and ports is SQL listening on? You were in SQL config manager, you’d have to open the TCP/IP settings, go to the second tab and see the port settings at the bottom. Since they’ve installed a named instance (AM10DEMO), it’s probably not listening on 1433 (which is the default port, not 5171). If it’s a default install, it could very well be configured to use dynamic ports - which means everytime you reboot it’ll be on a different port. That will make it difficult to punch through a firewall. If it’s the same box, skip this and move on. If it’s a different box, either create a firewall rule for the static TCP Port, OR disable the firewall.

You can also determine the port it’s on through SSMS, run this query in master:

select * from sys.dm_tcp_listener_states

The port column will be what it’s listening on. If it’s something in the 50000’s or 60000’s the instance is likely configured for dynamic ports.

  1. The next problem you’re going to have is a username/password to connect with. Windows/ODBC connections can use windows credentials. SQL express by default is configured for Windows authentication only. The best way to connect Lucee (or any non-windows program) to SQL is to enable SQL Authentication, and create appropriate credentials. In SSMS, you would go to the properties of the instance, and on the security page, make sure SQL Auth is available. (it’ll either be windows only, or “both”) Then you’d have to go down to Security for the server, add a username, give it a password, and ensure it has the rights you need to access the database you want. This would fall under general SQL server management.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-2017

https://www.lifewire.com/creating-sql-server-2008-database-account-1019857

(Note this isn’t the only way, you could get Lucee to do windows auth, but it’s way more complicated, involves service accounts, dlls, etc… So SQL Auth is your best bet.)

  1. If you have a fixed port to connect to, you can enter the host name (localhost for the local machine, or the IP or hostname of the remote machine), and the port, and connect using the Microsoft JDBC driver. If you DONT have a fixed port to connect to, your best bet is to use the name of the instance. Based on https://luceeserver.atlassian.net/browse/LDEV-1106 (unresolved), the best way to do this is to follow the workaround here: https://issues.jboss.org/browse/RAILO-3275?_sscc=t, and create an “Other” datasource following the format laid out in the ticket. (Aside for @IAmSigmund and others - seriously, we can’t connect with named instances?!) Use the credential you created in 3, and verify the connection

  2. Profit. The datasource should show as OK, and you should be able to use it for cfqueries.

Note than only step 4 has anything to do with Lucee, so until you have all the other steps ironed out there’s limited help you’ll be able to get from here. If you run into a problem we’ll need detail, screenshots, what you’ve run into, etc… like you just did. Until that point we didn’t even know the instance name was AM10DEMO (instead of the default SQLEXPRESS).

I’ve done procedures like this to connect to other systems using sql express embedded. (For instance, ACT) So it is possible, it just takes some configuration. Remember, it probably wasn’t part of the vendor’s requirement doc to make it easy for an external program to connect to their internal database platform.

1 Like
17

Hello everyone. My connection issue is fixed. It ended up being a port issue. I had it partially configured right, but not completely.

Thanks to all of you who were patient with me in getting this resolved.

I’m back in business, full steam ahead!

~ Zy

1 Like