IQToolkit with Firebird

Jan 25, 2010 at 7:06 AM
Edited Jan 25, 2010 at 7:08 AM

I'm trying to extend the IQToolkit to work with Firebird database. Until now 200 of 221 are passed, but there are some tests where I don't know why they fail or how to correct the translating.

1. Some tests return "invalid data type". During debugging I can see that the parameters are strings and when I replace @p0 and @p1 with the values there is no error anymore.
SELECT MAX(UPPER((CASE WHEN (t0.CustomerID = @p0) THEN @p1 ELSE @p1 END)))
FROM Customers t0
WHERE (t0.CustomerID = @p0)
-- p0 = [ALFKI]
-- p1 = [abc] 

Test 1: TestStringToUpper - FAILED
Reason: Dynamic SQL Error
SQL error code = -804
Data type unknown

2. In this test I get all records unique, but other programs give all 12930 rows with same query
SELECT t0.OrderID AS OrderID1, t0.ProductID
FROM Customers t1
INNER JOIN Orders t2
  ON (t2.CustomerID = t1.CustomerID)
CROSS JOIN "Order Details" t0
WHERE (t1.CustomerID = @p0)
-- p0 = [ALFKI]
1 ORDERID1=10248, PRODUCTID=11,
2 ORDERID1=10248, PRODUCTID=42,
3 ORDERID1=10248, PRODUCTID=72,
4 ORDERID1=10249, PRODUCTID=14,
5 ORDERID1=10249, PRODUCTID=51,
6 ORDERID1=10250, PRODUCTID=41,
7 ORDERID1=10250, PRODUCTID=51,
8 ORDERID1=10250, PRODUCTID=65,
9 ORDERID1=10251, PRODUCTID=22,
10 ORDERID1=10251, PRODUCTID=57,
11 ORDERID1=10251, PRODUCTID=65,
...

Test 124: TestMultipleJoinsWithMissingJoinCondition - FAILED
Reason: Assert failure - expected: 12930 actual: 2155
3. Some queries are build in form "select ... as value". Firebird can't return values without selecting rows from table,view or stored procedure.
SELECT CASE WHEN (EXISTS(
  SELECT NULL
  FROM Customers t0
  )) THEN 1 ELSE 0 END AS value
 
Test 195: TestAnyTopLevel - FAILED
Reason: Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, column 27
value
4. The construct ROW_NUMBER() OVER(...) is unknown in Firebird 
SELECT t0.City
FROM (
  SELECT t1.City, ROW_NUMBER() OVER(ORDER BY t1.City) AS rownum
  FROM (
    SELECT DISTINCT t2.City
    FROM Customers t2
    ) AS t1
  ) AS t0
WHERE t0.rownum BETWEEN 5 + 1 AND 5 + 10
ORDER BY t0.City
 
Test 207: TestDistinctSkipTake - FAILED
Reason: Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, column 36
(
I found also that there is problem with decimal and float numbers. In german returns ToString() always "," as decimal separator, but SQL-Language expects ".". I had to set
                 Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
in the constructor of SqlFormatter.
Has someone ideas how to get failed tests to run?
Regards, Gabriel
Feb 23, 2010 at 12:25 PM

Hi Gabriel,

I did not run any unit test yet, but I am also implementing a IQToolkit.Data.Firebird.dll and found the same problem with the "select ... as value" queries.

I solved this replacing all references to "value" by "v" in the IQToolkit.Data assembly.

It would be nice if the name of this param is retuned by a virtual method, or something like that, in the next version, so that we can extend it without changing the base code.

Regards,

Rafael Romão

Feb 23, 2010 at 3:22 PM

Hi Rafael,

maybe it would be better to put our resources together. I can send you my current work. Actually I've no free time to continue the provider, but I need them at my office.

Regards, Gabriel

Feb 24, 2010 at 4:30 PM

Ok Gabriel,

I can send you my job and analyze yours.

I sent you a private message with my mail address.

Let's put our efforts together and make Firebird another option for IQToolkit.

Regards,

Rafael Romão

Mar 3, 2013 at 11:44 PM
Gabriel56

I haven't used it, but Kellerman Software has a LINQ provider for Firebird
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx