Conect to a 32 bit ODBC data source from Railo 64 bit?

[C. Schmitz]

Hi guys,

I inherited a pretty old application (developed with CF5 to CF8) that
uses a 32 bit ODBC data source on Win 2008 64 bit to connect to a 32
bit Sybase SQL Anywhere 7 database.

When I try to set up this data spource in Railo (WAR file deployed on
Tomcat 64 bit) as a JDBC-ODBC bridge datasource, Railo complains that
the datasource was not found and that no suitable driver exists. When
I try to define a datasource using the Sybase option that comes with
Railo, I get the message that the login failed, although I am 100%
certain that the credentials are correct.

So my question is: is there an easy way to connect to a 32 bit
database from Railo 64 bit?

Thank you

Chris

[Michael Offner]

to be honest i have no idea ;-)

Like you perhaps know the ODBC jdbc driver is bundled with the JRE itself, there is no way to replace with a other driver versions (as long you not replace the JRE itself).

the "Driver" for ODBC is very simple, go to "WEB-INF/railo/context/dbdriver/ODBC.cfc", 

in this cfc the jdbc driver class and the connection string assembly is defined.

perhaps there is a additional possible setting to do what you need.

 google:"jdbc odbc bridge 32 bit from 64 bit"

/micha

2011/11/2 C. Schmitz <cfl...@gmail.com>

[Gert Franz]

Chris,

Actually that has nothing to do with 64bit or 32bit. Just imagine the server
being behind a firewall or in another network. How should Railo detect that
at all? I guess it is the ODBC datasource definition/driver that gives you
trouble. Could you try to use a JDBC driver for it? There should be SQL
Anywhere JDBC drivers available.

http://iablog.sybase.com/hinsperg/2009/10/connecting-to-sql-anywhere-using-j
dbc/

http://dcx.sybase.com/1200/de/dbprogramming/pg-jdbc-3-0-load.html

Greetings from Switzerland
Gert Franz
 
Railo Technologies      Professional Open Source
skype: gert.franz         ge...@getrailo.com
+41 76 5680 231           www.getrailo.com

-----Ursprüngliche Nachricht-----
Von: ra...@googlegroups.com [mailto:ra...@googlegroups.com] Im Auftrag von
C. Schmitz
Gesendet: Mittwoch, 2. November 2011 08:23
An: ra...@googlegroups.com
Betreff: [railo] Conect to a 32 bit ODBC data source from Railo 64 bit?

[JayB]

While this isn't optimal, it might work for you. If there is a 32 bit
driver available, set up a ODBC System DSN on the 64 bit box using
%windir%\SysWOW64\odbcad32.exe

You can read about my adventure with an old FoxPro db I was running
32bit CF at the time but a commenter was able to verify it worked in
64bit. Hopefully a similar approach works w/ Railo as well.

http://sidfishes.wordpress.com/2008/10/08/coldfusion-8-foxpro-datasources/

(this is actually one of my most viewed posts so I wasn't alone)

[Bilal]

Chris,
This may have changed, but the only way I was able to access 32-bit
ODBC datasource via the JDBC/ODBC bridge is to switch everything to 32
bit including the JVM.
Thus, if at all possible I would recommend the use of a JDBC driver
first.

-Bilal

[CF]

Jay,

> While this isn't optimal, it might work for you. If there is a 32 bit
> driver available, set up a ODBC System DSN on the 64 bit box using
> %windir%\SysWOW64\odbcad32.exe
>
> You can read about my adventure with an old FoxPro db I was running
> 32bit CF at the time but a commenter was able to verify it worked in
> 64bit. Hopefully a similar approach works w/ Railo as well.

thanks for your response. There already is a 32 bit ODBC System DSN,
but, Railo refuses to see it. :-(

Chris

[CF]

Bilal,

> This may have changed, but the only way I was able to access 32-bit
> ODBC datasource via the JDBC/ODBC bridge is to switch everything to 32
> bit including the JVM.

That's what I was afraid of.

> Thus, if at all possible I would recommend the use of a JDBC driver
> first.

Haven't found a free driver, but, I must admit, I haven't really
searched thoroughly. This is just for demo purposes and once I get my
client convinced, we will set up a new box and migrate everything. So
I guess for now I'll just install a 32 bit version of Railo. ;-)

BTW I just read your blog post on CFCamp. Your presentation on
application security really was great. It was just the right mix of
basic advices (long live CFQUERYPARAM) and advanced techniques. And I
admit, I may at last be partially responsible for the increased coffee
consume you mentioned. ;-)

Chris

[JayB]

How have you set up the connection string? Are you using the Other -
JDBC Driver?

(I'm relying on my CF experience here since I'm new to Railo so this
may all be pointless but you never know)

[JayB]

oops I see you aren't using the "other" driver but maybe you should
give it a try??

Railo gurus - Does "Other JDBC Driver" equate to the ODBC socket in
CF? Seems like it should as class & DSN setup seem to be similar

[ADK]

Has anyone found a solution to this yet? We have Railo/Tomcat 64bit installed but need to access an older FoxPro db and Railo does not seem to recognize the 32b DSN created with %windir%\SysWOW64\odbcad32.exe ? When I try to create the datasource in Railo Admin I get this error:

[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

ACF allowed us to do this with an ODBC connection so there must be a way to do so here, correct?

Or do we need to revert all of our other sites/apps to use Railo/Tomcat 32b on this 64b machine just so one app can connect to this legacy db? I hope that's not the only solution?

Thanks for any insights!
Andrew

[Michael Offner]

All datasource drivers you have in the Admin are nothing else than wrappers for the underlaying JDBC Drivers.

To execute a JDBC Drivers you need only 2 things, a class name and a connection string, the only job of all drivers we have in the admin is to convert the inputs you do to this information.

With the "Others - JDBC Driver" you can give this information directly with no interpretation/conversion happeing from Railo, so you can call EVERY JDBC driver possible.

Micha

2011/11/3 JayB <jbi...@gmail.com>

--

/micha

Michael Offner CTO Railo Technologies GmbH

[ADK]

thank you Micha.

I know to put this in this for the conn string:

Driver={Microsoft Visual FoxPro Driver}; SourceType=DBF; SourceDB=c:\inetpub\wwwroot\dbf; Exclusive=No;

however, I do not know what to put for class?

[Kai Koenig]

Do you have a JDBC driver for FoxPro? Normally you'd find the class name in the documentation of said driver.

Cheers
Kai

[ADK]

Hi Kai - ok, so I suppose I do not. I do not believe that there are any...  :(

I have an ODBC driver from MSFT, which is why I was originally trying to use the JDBC-ODBC Bridge. This works fine for a 32b DSN but does not seem to work with a 64b DSN. I'm at a loss as how to proceed.

[Kai Koenig]

Try this:

http://www.hxtt.com/dbf.html

A client of mine is using their commercial Access JDBC drivers and really happy with it --- and they struggled with the JDBC-ODBC bridge, too.

Cheers

Kai

[ADK]

Thanks Kai - I have seen this before, but it's rather pricey. I was hoping to access this legacy data with a non-commercial solution?

So would you say that I am spinning my wheels trying to get 64b Railo/Tomcat to work with a "dbf" db (i.e. FoxPro, dBASE III, etc.) without paying for a commercial driver?

[Kai Koenig]

I would purely out of principle not bother with JDBC-ODBC integration of whatever sort. Every time one uses such a bridging layer it is asking for trouble in some way.

Yes, the driver costs money, I guess it comes down to what your time fiddling around with this problem and trying to get something else going is worth. How much time do you already have spent on the problem that you could have spent otherwise (another client, your family etc.) Given that you already got the Railo server for free, I wouldn't hesitate a second to buy the driver for a project if it saves me the hassle. YMMV though :)

Cheers

Kai

--
Did you find this reply useful? Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to the Google Groups "Railo" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/railo/87d31ef9-8b20-45e4-9b4d-392b31389c73%40googlegroups.com?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

--
Kai Koenig - Ventego Creative Ltd
ph: +64 4 889 3626 - mob: +64 21 928 365 /  +61 435 179 091
web: http://www.ventego-creative.co.nz

Blog in Black: http://www.bloginblack.de
2DDU Podcast: http://www.2ddu.com/
Twitter: @AgentK
--

[Peter Boughton]

It's one thing to pay money to save time. It's quite another paying US$ 339 for a single license of a JDBC driver. :/

Just convert the database to another format that does have JDBC drivers - problem solved.

LibreOffice and MS Office both support DBF format, if they don't work there's probably plenty of other free tools available, or failing that there's a $20 export utility on the dBase website.

[Bilal]

I may be thinking too simply here but the problem I found with ODBC is the lack of support for 64 bit anything.
The solution for me was to do a 32 bit instance of jvm, tomcat, Railo
This kept everything working.
No promise as I don't know FoxPro just my 2.

-Bilal

[ADK]

Thanks guys. So you have some context, this is a legacy application written in FoxPro that reads/writes to these dbf files... the organization runs on this ancient, monolithic app. I am trying to "bolt on" functionality via an intranet app. This is why I cannot just convert the data as per Peter's suggestion. Although...

I currently do convert by importing the FoxPro dbf's into MySQL every evening and run the intranet app off of that, but basically I'm using stale data the next day. The next step was to start moving some of these intranet modules to real-time using the "live" FoxPro tables. Also, I only need to read this data, not modify or write to it (anything I need to write could be added to the MySQL db and just referenced to the FoxPro tables...) if that matters...

I suppose I could "link" the dbf tables to an Access db and then have Railo talk to Access? (assuming that would even work?) But if the JDBC-ODBC bridge is as wonky as Kai says it is, then adding this extra level of wonky complexity to that scenario may be folly?

I've also thought of using a directory watcher gateway to monitor changes in the dbf's which would trigger an import into MySQL... but that seems crazy...

To Bilal's point, I suppose I could do that if I had to. We already have 64-bit Tomcat/Railo running other apps on that server... could I run 32b and 64b versions side-by-side on the same machine (would I even want to do this??) or would I need to convert everything over to 32b just so this app could access this db?

In the end, Access can read these files, Navicat can read them, other progs can read it... I was just hoping there was a way to do so with Tomcat/Railo...

[ADK]

In case anyone is interested in this, I have been able to accomplish this using the free Advantage SQL ODBC server drivers:

http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=19&Product=14

[Jason101]

ADK,

What connection string do you use to connect to the system dsn?

[ADK]

I setup the dsn in the ODBC Data Sources pointing to the folder with the dbf files and then the connection string looks something like this:

this.datasources.fox={
 class:'sun.jdbc.odbc.JdbcOdbcDriver'
 ,connectionString:'jdbc:odbc:Fox'
 };

We've since purchased the Hxtt JDBC DBF driver as the JDBC-ODBC bridge is no longer supported in Java 1.8