Ingres implentation - Two issues

[Jim]

Hello All,

Select Issue

var q = from planTable in cispDB.Plan
where (planTable.PlanNo == "ABC1234" ||
planTable.PlanNo == "ABC1235")
join planLaTable in cispDB.PlanLa on
planTable.PlanNo equals planLaTable.PlanNo
join localAuthTable in cispDB.LocalAUtH on
planLaTable.LaCode equals localAuthTable.LaCode
select new { planTable.PlanNo ,
planLaTable.LaCode, localAuthTable.LaName, planTable.TownCode };

Investigation showed that the eventual SQL query did not bracket the
OR at all. To get it to work temporarily I changed SqlBuilder.cs in
the BuildWhere method and added
if (!IsHavingClause(whereExpression))
{
string be = BuildExpression(whereExpression,
queryContext);
if (be.Contains("OR"))
be = "(" + be + ")";
whereClauses.Add(be);
//whereClauses.Add(BuildExpression
(whereExpression, queryContext));
}

Update Issue

I cannot get it to update a field ! Code I am using is beow, if
anybody has got an update to work with Ingres (I'm using Ingres 2.6 at
the moment) I would be most appreciated of some help.

CISpProd cispDB = new CISpProd(new IngresConnection
(connString));

Plan pl = (from c in cispDB.Plan
where c.PlanNo == "ABC1234"
select c).First<Plan>();

pl.TownCode = "Jim";

cispDB.SubmitChanges();

Hoping somebody can assist

Thanks,
Jim

[Jonathan Pryor]

On Wed, 2009-10-07 at 22:45 -0700, Jim wrote:
> Select Issue
>
> var q = from planTable in cispDB.Plan
> where (planTable.PlanNo == "ABC1234" || planTable.PlanNo == "ABC1235")
> join planLaTable in cispDB.PlanLa on planTable.PlanNo equals planLaTable.PlanNo
> join localAuthTable in cispDB.LocalAUtH on planLaTable.LaCode equals localAuthTable.LaCode

> select new { planTable.PlanNo, planLaTable.LaCode, localAuthTable.LaName, planTable.TownCode };

>
> Investigation showed that the eventual SQL query did not bracket the
> OR at all.

This is odd, because if I take src/DbLinq/Test/Providers/ReadTest.cs and
change C12_SelectEmployee_MultiJoinWithWhere() to use:

var q = from t in db.Territories
where (t.RegionID > 3 || t.RegionID > 4)
join l in db.EmployeeTerritories on t.TerritoryID equals l.TerritoryID
join e in db.Employees on l.EmployeeID equals e.EmployeeID
select e;
// ...
q.Count()

Then the SqlServer provider generates the SQL:

SELECT COUNT(*)
FROM [dbo].[Territories] AS t$, [dbo].[EmployeeTerritories] AS l
$,
[dbo].[Employees] AS e$
WHERE (t$.[TerritoryID] = l$.[TerritoryID]) AND
(l$.[EmployeeID] = e$.[EmployeeID]) AND
((t$.[RegionID] > 3) OR (t$.[RegionID] > 4))
-- Context: SqlServer Model: AttributedMetaModel Build: 0.19.0.0

Which properly encloses all of the OR's in parenthesis.

Since this works for SqlServer, I imagine that this is actually an
Ingres provider bug, so fixing SqlBuilder.cs is the place to fix it.

Part of the problem is that Ingres isn't regularly tested (no regular
developer has access to Ingres & runs/maintains the unit tests for it).

> Update Issue
>
> I cannot get it to update a field !

This is also odd, and is checked in numerous tests (e.g.
WriteTest.G6_UpdateTableWithStringPK() and
WriteTest.G9_UpdateOnlyChangedProperty() in WriteTest.cs). This may be
another regression in the Ingres provider.

You should see if those unit tests pass for you (though that will
require "porting" test/instnwnd.SqlServer.sql to Ingres, as has been
done forPostgreSQL, Oracle, and SQLite).

- Jon

[Shuggie]

Hi Jim,

I don't know anything about DbLinq but I do know Ingres.

It looks like DbLinq is using the .Net data provider for connection
and you say you're using Ingres 2.6. The Ingres .Net Data Provider
wasn't released under 2.6 and doesn't support it directly. It's
technically possible to use it to connect to a 2.6 JDBC server rather
than the DAS server but it's not supported and I know of a few
problems with it. There is a supported way to connect to 2.6 via .Net
and that is via an Ingres 2006 installation and an Ingres net vnode.
That is you point yor .Net connection to a DAS server in a 2006
installation where you have a vnode defined that points to your 2.6
isntallation and specify the database name as "vnode::dbname".

Also I notice that the source for DbLinq includes the
Ingres.Client.dll i.e. the .Net data provider. However it looked like
the Ingres part of the DbLinq source hadn't been downloaded for some
time - so it may be worth downloading the latest dll from
esd.ingres.com.


HTH
Paul

[Thomas Glaser]

Hi,

I've used to look after the Ingres implementation but had to drop it
because I didn't have time any more to look after it.

I think I'll have an hour or two this week to have a go at fixing
this. Jim, are you able to send me a test case I can debug against
(maybe a copy.in script and the Linq query(s))?

Cheers,
Thomas

> Paul- Hide quoted text -
>
> - Show quoted text -

[Thomas Glaser]

Hi,

I've looked into it. The updates don't work because there seems to be
something wrong with the primary key detection (the engine tries to
update on nonexisting key values which leads to zero-row-updates). I
don't have a fix yet but it feels like I'm getting close.

Cheers,
Thomas

[Thomas Glaser]

Since the Ingres Driver doesn't support named parameters, the params
must be supplied in the right order to the DbCommand object. The
expression engine doesn't do that. I've built a workaround for that
ages ago but with the new Sugar engine, this fix seems to have been
refactored out of the code.

One way would be to intercept the DbCommand.Execute Method...

Cheers,
Thomas