I am evaluating the ODBC-2-JDBC Bridge and I have a problem during the following configuration:
MSSql-Server running on machine A.
Another MSSql-Server running on machine B and providing only a JDBC interface.
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.
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 ?
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.
