Multiple Joins and Firebird 1.5

Discuss the Linked Server tehcnology, MS SQL Server and Business Intelligence environment

Multiple Joins and Firebird 1.5

Postby Bryce Godenir » 18 Aug 2014, 18:55

Hi there,

I've run into a problem while connecting through a linked server from SQL Server to a Firebird database. I've searched through the forums and the only thing I can find is that my problem may be related to the version of Firebird.

Here's some specifics:
I'm connecting from SQL Server 2008 R2 through a linked server with a provider of LCPI.IBProvider.3 (via IBProvider_prof_32-64_3.20.1.16065.r1), and connecting to a Firebird database - version 1.5. I'm able to connect and query data using single selects from single tables just fine. The problem occurs when the query gets a little more complex - multiple inner joins in combination with parameters specifying VARCHAR values.

I'm going to try and give as much information as possible, so here's some table definitions I've created for testing purposes along with queries and results.

Table1
(
TABLE1_PK int PRIMARY,
TABLE2_FK int,
TABLE1_DESC varchar(50)
)

TABLE2
(
TABLE2_PK int PRIMARY,
TABLE3_FK varchar(12),
TABLE2_DESC varchar(50)
)

TABLE3
(
TABLE3_PK varchar(12),
TABLE3_DESC varchar(50)
)

Here's some simple data that was added to the tables:
TABLE1
1 1 'Table 1 Record 1'
2 2 'Table 1 Record 2'

TABLE2
1 800020000030 'Table 2 Record 1'
2 800020000031 'Table 2 Record 2'

TABLE3
800020000030 'Table 3 Record 1'
800020000031 'Table 3 Record 2'


And then here's the query:
select * from FIREBIRD_WMS...TABLE1
inner join FIREBIRD_WMS...TABLE2 on TABLE1.TABLE2_FK = TABLE2.TABLE2_PK
inner join FIREBIRD_WMS...TABLE3 on TABLE2.TABLE3_FK = TABLE3.TABLE3_PK
where TABLE3.TABLE3_PK = '800020000030'

This is the error received:
OLE DB provider "LCPI.IBProvider.3" for linked server "FIREBIRD_WMS" returned message "Prepare SQL statement failed.
Dynamic SQL Error
SQL error code = -206
Column unknown
Col1022
At line 1, column 149.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT "Tbl1002"."TABLE1_PK" "Col1021","Tbl1002"."TABLE2_FK" "Col1022","Tbl1002"."TABLE1_DESC" "Col1023" FROM "TABLE1" "Tbl1002" ORDER BY "Col1022" ASC" for execution against OLE DB provider "LCPI.IBProvider.3" for linked server "FIREBIRD_WMS".



Now... Running the query from Flamerobin works fine:
select * from TABLE1
inner join TABLE2 on TABLE1.TABLE2_FK = TABLE2.TABLE2_PK
inner join TABLE3 on TABLE2.TABLE3_FK = TABLE3.TABLE3_PK
where TABLE3.TABLE3_PK = '800020000030'

Here's the output:
1 1 Table 1 Record 1 1 800020000030 Table 2 Record 1 800020000030 Table 3 Record 1


During testing, I noticed if I use the INT values instead of querying against the VARCHAR values, things work fine (ie. where TABLE1_PK = 1). I also noticed that using a SINGLE inner join works fine as well. So the problem seems to occur when multiple inner joins are used, in conjunction with looking for specific VARCHAR values.


Also, while searching for a solution to this problem, I came across a comment talking about upgrading Firebird to version 2.1 or higher - unfortunately this isn't possible for us at this time since the company we're dealing with aren't in a position to upgrade Firebird.

Looking forward to your response!

Regards,
Chris
Bryce Godenir
 
Posts: 4
Joined: 16 Aug 2014, 08:13

Re: Multiple Joins and Firebird 1.5

Postby Bryce Godenir » 18 Aug 2014, 19:00

One thing to add...

I created a new database in Firebird version 2.1, created the same tables, setup a linked server, and ran the same query... it worked fine. So I'm fairly certain Firebird 1.5 is at least one of the issues.

Is it possibly the version of IBProvider? Maybe there's an older version that would work with SQL Server 2008 R2 64 bit and Firebird 1.5.

Chris
Bryce Godenir
 
Posts: 4
Joined: 16 Aug 2014, 08:13

Re: Multiple Joins and Firebird 1.5

Postby Kovalenko Dmitry » 18 Aug 2014, 23:49

Hi

In common case, I recommend upgrade to 2.5.x.

In your case, I think, you can create the VIEW (with intersection of your TABLE1, TABLE2, TABLE 3), and work with this VIEW.

/* IN FB 1.5 database */
create VIEW VIEW_T123 as
select * from TABLE1
inner join TABLE2 on TABLE1.TABLE2_FK = TABLE2.TABLE2_PK
inner join TABLE3 on TABLE2.TABLE3_FK = TABLE3.TABLE3_PK

/* linked server query */
select * from FIREBIRD_WMS...VIEW_T123 V
where V.TABLE3_PK = '800020000030'

Result:
1 1 T1R1 1 800020000030 T2R1 800020000030 T3R1
Kovalenko Dmitry
Site Admin
 
Posts: 843
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk

Re: Multiple Joins and Firebird 1.5

Postby Bryce Godenir » 19 Aug 2014, 00:06

Hi Dmitry,

Thanks for your reply.

Yes, I'd definitely prefer the database being upgraded, but we're stuck using a very old version :(

As for your solution, I've tried it and it definitely works. However, there's 2 issues:
1) The Firebird database we're using is a production version, so I'll have to get permission to add additional views to their database, and
2) The overall purpose of getting this connection is to write multiple reports accessing various tables - so I'd have to create a view for every set of tables that need to be joined.

In any case, neither of the above problems are anything you can help with (or maybe you can just go over to the developer of the software that uses this old version of Firebird and talk some sense into them!).

From your answer, can I assume there's no other DLL's that are older that would work for us?

Thanks,
Chris
Bryce Godenir
 
Posts: 4
Joined: 16 Aug 2014, 08:13

Re: Multiple Joins and Firebird 1.5

Postby Kovalenko Dmitry » 19 Aug 2014, 05:26

Bryce Godenir wrote:Hi Dmitry,
In any case, neither of the above problems are anything you can help with (or maybe you can just go over to the developer of the software that uses this old version of Firebird and talk some sense into them!).

Personally, I stopped using FB1.5 a long time ago (in 2007). At current time, I run this server only for small tests (like this case). The 99.99% of time I use the the FB 2.5.3 (x64, SuperClassic).

Bryce Godenir wrote:From your answer, can I assume there's no other DLL's that are older that would work for us?

I think, YES - "no other DLL's".

IBProvider - this is "simple" driver, that (in general) transfers the queries to server and returns the result. With minimum transformations of queries.

But, you can try to use the OPENQUERY, also.

select * from openquery(FIREBIRD_WMS,'select * from TABLE1 inner join TABLE2 on TABLE1.TABLE2_FK = TABLE2.TABLE2_PK inner join TABLE3 on TABLE2.TABLE3_FK = TABLE3.TABLE3_PK where TABLE3.TABLE3_PK = ''800020000030''');

Result:
1 1 T1R1 1 800020000030 T2R1 800020000030 T3R1
Kovalenko Dmitry
Site Admin
 
Posts: 843
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk

Re: Multiple Joins and Firebird 1.5

Postby Bryce Godenir » 19 Aug 2014, 18:13

Hi Dmitry,

Thanks for this. I now have 2 options available aside from the Firebird upgrade!

Consider this item closed.

Thanks,
Chris
Bryce Godenir
 
Posts: 4
Joined: 16 Aug 2014, 08:13


Return to Using IBProvider in MS SQL Server

Who is online

Users browsing this forum: No registered users and 3 guests

cron