Connect to OLEDB data source from Java?

Question

We are trying to connect to a SQL Server instance used by the ACT CRM system. They have managed to lock things down so it isn't possible to connect to the SQL back-end using ODBC (there is some special utility that will add ODBC support that you can install if you purchase the primo version of the software, but that's just crazy).

The recommended method of connecting to theses databases is using an OLEDB connection.

Does anyone have any tricks/ideas/etc... for how to make and use an OLEDB connection from Java?

This doesn't have to be JDBC if that's not possible. All we really need to do is execute a SELECT query that returns two fields and parse those field values out for each row. I have very little experience with OLEDB, so 'use JACOB' might be a good answer, but I'd appreciate some details on what the COM calls would actually have to be.

Ballesteros

Why not just use the type 4 (native) Microsoft JDBC driver? – Protecting

I'll try it (I prefer jtds, myself) - but login credentials don't work (native authentication doesn't work, and putting in the username and password manually doesn't work either). As near as I can tell, Act has the login completely locked down on the database except via the OLEDB connection. I don't understand this, myself (SQL is SQL, right?) and I don't have access to the physical server, but all Google results indicate that this is indeed the situation. – Ballesteros

Answer 1

I know this is old, but could help someone to know how I did it

I described in more detail how to do it Here.

Mudfish

Answer 2

That's not your problem. The problem is the way they have locked down the server. Basically on startup it looks for logins other than ACTUSER and removes them.

You can unlock it pretty easily though, then you will be able to connect in the usual way.

https://serverfault.com/questions/77712/sqlserver-need-to-access-an-act-database-for-data-migration

I've managed to unlock mine but I forget how... I think I started it in single-user mode then did some funny stuff involving decrypting a stored proc in the master database and editing it to remove that "functionality". That in turn involved using SQLTrace to see what commands ACT was sending.

I suggest you ask on Server Fault.

Nitrite

Answer 3

Java can not access OLEDB directly. You need to do this in another language like C++ or C#. Then you can access via JNI or external process. If you does not want write the native part of JNI self then you can use JACOB how you suggest it. But I think an external process take the request seem be simpler.

Renaerenaissance

Answer 4

Two ways to solve this issue.

1. Spawn from java an external proc (c#, c++, etc) that connects to SQLSrv using OLEDB and redirect the stdin, stdout and stderr to your java program.
2. Create a C# listener on a particular port and have java pass all the requests via a client to that listener.

Subsumption

Answer 5

Or use: http://uda.openlinksw.com/jdbc/mt/jdbc-sqlserver-mt/ They develop all kinds of drivers. I used this company before...

Subsumption