ODBC-JDBC-Bridge fails on Prepared statements


#1

I am evaluating the ODBC-2-JDBC Bridge and I have a problem during the following configuration:

  1. MSSql-Server running on machine A.
  2. Another MSSql-Server running on machine B and providing only a JDBC interface.
  3. I want to query data placed on B from A using a DBLink on A.

On machine A, I have built a database link to machine B, while the link is configured to use ODBC2JDBC-Bridge (Lite-Edition) (with official MSSqlServer-jdbc-driver).

So far, simple statements are forwarded and executed correctly.

The problem arises with queries, that are based on a PreparedStatements construction.
The test case:

Query
___________
-- Declare the variable to be used.
DECLARE @Val int;

-- Initialize the variable.
SET @Val = 4;

select * from BRIDGELINK.master.dbo.test where value = @Val;


Result (Microsoft SQL Server Management Studio 17)
___________

Msg 7399, Level 16, State 1, Line 8
The OLE DB provider "MSDASQL" for linked server "BRIDGELINK" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7399, Level 16, State 1, Line 8
The SELECT "Tbl1002"."value" "Col1004" FROM "master"."dbo"."test" "Tbl1002" WHERE "Tbl1002"."value"=?-query for OLE DB provider "MSDASQL" for linked server "BRIDGELINK" cannot be executed.


Notes
___________

- BRIDGELINK is a MSSQL DB-Link to a Database using the 'Openlink ODBC to JDBC'-Driver, which includes the MSSQL JDBC driver.

- table 'test' is a testtable with 'value' as integer column including the values 1, 2, 4, 4.

- The Query works without the ODBC-to-JDBC-Bridge (just using MS ODBC driver for linked connection).

- Query was also tested with the provided 'C++ Demo' application with the same result.

- The static Query 'select * from BRIDGELINK.master.dbo.test where value = 4' works well. The failure arises when a variable is used.
We alredy tested the "emulate PreparedStatements"-option in OpenLink-Driver configuration without success.

Do you have any experience with this problem? Any advice would be appreciated.


#2

Can you create an ODBC ( and JDBC) trace for the query that is failing, so we can see if there are any ODBC or JDBC calls failing that might be the cause of the failure ?

Also, are you saying the same failing query using the LinkedServer interface works with the sample C++Demo program we provide using the same ODBC DSN ?


#3

I uploaded the tracefile here, because it was to big to paste it here…

Yes, the problem arises also with the C++ demo application.


#4

Looking at the trace logs provided it shows the query SELECT "Tbl1002"."value" "Col1004" FROM "master"."dbo"."test" "Tbl1002" WHERE "Tbl1002"."value"=? being run during which time the SQLExecute of it never returns, which seems to be the failure point.

You say the same problem occurs with C++Demo, but it that running exactly the same query ie SELECT "Tbl1002"."value" "Col1004" FROM "master"."dbo"."test" "Tbl1002" WHERE "Tbl1002"."value"=? as you cannot run a parameterised query with C++Demo as there is not means of passing the param (?) to it, thus please confirm the query being run against C++Demo as if we can recreate the problem and get a trace from C++Demo it would easier to diagnose.


#5

Just could not get it running today :frowning:
Finally found out…

[OpenLink][ODBC] License has expired

Seems like evaluation time is over. I am going to ask someone else in the company to reproduce the problem on monday.


#6

Ok, we have now again tested with the C++ Demo application and the logging is here, as open-link.log.2,

Steps to reproduce the problem:

  1. Install MS SQL Server DB

  2. Create a table “test” with one integer column named value

  3. Add some data in new table (e.g. 1,2,3,4,4)

  4. Create an Open-Link ODBC DSN with mssql-jdbc-7.0.0-driver

  5. In MS SQL Server Management Studio, create a Database Link (under server objects) using the OpenLink ODBC DSN to the same database instance

  6. Execute the statements in C++ Demo app:

    DECLARE @Val int;
    SET @Val = 4;
    select * from MSSQLLINK.master.dbo.test where value = @Val;