Hi,

Before upgrading to build 10, I found several issues with ElevateDB (many of them related to constraints) which were fixed with that release.
But I was using several queries (based on several views) and one of them started failing.
This is the query:

SELECT
 *
FROM
 viewFailedLogins
WHERE
 (ContextID = 'X') AND
 (DateTime BETWEEN TIMESTAMP '2011-08-25 13:18:35' AND TIMESTAMP '2011-08-27 13:18:35')
ORDER BY DateTime DESC;

This query is returning two rows using EDBManager, but returns no rows when opened in code.

The view itself is using UNION ALL

SELECT
 ContextID,
 DateTime,
 Reason,
 UserName,
 '' AS LoginName,
 WindowsUserName,
 ComputerName,
 IPAddress,
 '' AS FirstName,
 '' AS LastName
FROM viewInvalidUserNames
UNION ALL
SELECT
 ContextID,
 DateTime,
 Reason,
 LoginName as UserName,
 LoginName,
 WindowsUserName,
 ComputerName,
 IPAddress,
 FirstName,
 LastName
FROM viewRejectedLogins

and uses data from two other views

viewInvalidUserNames

SELECT
 e.ContextID,
 u.EventID,
 e.DateTimeStamp AS DateTime,
 t.Name AS Reason,
 u.UserName,
 c.WindowsUserName,
 c.ComputerName,
 c.IPAddress
FROM
 EventTypes t
   INNER JOIN Events            e ON t.ID = e.TypeID
   INNER JOIN InvalidUserNames  u ON e.ID = u.EventID
   INNER JOIN ClientConnections c ON u.ClientID = c.EventID
WHERE
 t.ID = 2

and

viewRejectedLogins

SELECT
 e.ContextID,
 l.EventID,
 e.DateTimeStamp AS DateTime,
 t.Name AS Reason,
 u.LoginName,
 c.WindowsUserName,
 c.ComputerName,
 c.IPAddress,
 u.FirstName,
 u.LastName
FROM
 Events e
   INNER JOIN EventTypes        t ON e.TypeID   = t.ID
   INNER JOIN RejectedLogins    l ON e.ID       = l.EventID
   INNER JOIN Users             u ON l.UserID   = u.UserID
   INNER JOIN ClientConnections c ON l.ClientID = c.EventID
WHERE
 t.ID BETWEEN 3 AND 8

Just for confirming that the issue was present in ElevateDB and not in my code, I loaded the example "simplequerydemo" and modified some properties for pointing to my database and using my query.
I activated everything in design mode and again, the resultset was empty.

I'm thinking about upgrading to build 11, but I'm afraid of receiving more bugs and no fixes.
Is this issue already fixed in build 11? Should I upgrade?

Best Regards,

David

David


>This query is returning two rows using EDBManager, but returns no rows when opened in code.

This line in your post says to me that the problem lies in your code somewhere, unless you're running EDBManager based on a different version of ElevateDB than you app uses. Remember EDBManager is simply an application built using ElevateDB and anytime there are differences between what it produces and what your app produces the difference is almost every time caused by your code.

>I'm thinking about upgrading to build 11, but I'm afraid of receiving more bugs and no fixes.
Is this issue already fixed in build 11? Should I upgrade?

Why not. If you keep the downloaded install file (I have them going back to 1.0) its easy enough to de-install and revert. I've had to do this a few times whilst Tim's tracked down and stomped on a bug.

Roy Lambert [Team Elevate]

I'm with Roy on the if it works in EDB then re-check your code.

I also suggest you  check out the incident reports to see what was fixed in newer build :
http://www.elevatesoft.com/incident?action=addressed&category=edb&release=2.05


Raul

Roy,

As I said before, the query was working before and, even stranger, it is working now, sometimes.
I'm using the following procedure for querying the database and storing the expected result in a local TClientDataset.

procedure TAbstractSecuritySystemSQLMapper.OpenSQL(
 const aSelectText: string;
 const aClientDataset: TClientDataset);
var
 Query: ISQLQuery;
 Field: TField;
begin
 Query := SQLEngine.CreateQuery;

 Query.AddToSQL(aSelectText);
 try
   Query.Open;
   aClientDataset.Close;
   aClientDataset.CreateDataset;
   while not Query.Eof do
   begin
     aClientDataset.Append;
     for Field in aClientDataset.Fields do
       if Field.FieldKind <> fkInternalCalc then
         Field.Value := Query.FieldByName(Field.FullName).Value;
     aClientDataset.Post;

     Query.Next;
   end;
   aClientDataset.First;
 except
   on E: Exception do
     raise Exception.Create(E.Message)
 end;
end;

This procedure is used for several queries, not only this particular query which is failing now.
If I request a report based in that query, it works perfectly.
But when I'm running some test cases which request all queries one after another, this particular query reports the right RecordCount, but it fails to return the field DateTime throwing an exception for conversion error (an internal division by zero and '0.0' is not a valid timestamp...)

All this code is also executed with MSSQL and MySQL (the previous Query comes from a SQLEngine factory) and works all the time.

The test case executes some actions which leave a trace on the database. Then each query verifies that the database recorded the right information.

I forgot one last piece of information.
This test case is running as a thread (as part of DUnit).

David

David

This

>As I said before, the query was working before and, even stranger, it is working now, sometimes.

and this

>But when I'm running some test cases which request all queries one after another, this particular query reports the right RecordCount, but it fails to return the field DateTime throwing an exception for conversion error (an internal division by zero and '0.0' is not a valid timestamp...)

make me think data error.

However, since you can duplicate it at will your best approach would probably be to create a test case and email it to Tim so he can have a look at it.

Roy Lambert [Team Elevate]

Roy,

Just for making everything more interesting...
As all my scripts were generic (that is, not using any explicit COLLATION), I upgraded my database to Unicode (that is Delphi 2007 ElevateDB 2.05 build 11 Unicode) and now my test cases are running OK...

Then, again, something is happening with the ANSI version, but I don't care anymore.

Thanks,

David

David

>Just for making everything more interesting...
>As all my scripts were generic (that is, not using any explicit COLLATION), I upgraded my database to Unicode (that is Delphi 2007 ElevateDB 2.05 build 11 Unicode) and now my test cases are running OK...
>
>Then, again, something is happening with the ANSI version, but I don't care anymore.

Could it have been that columns had a different collation defined to the default?

Roy Lambert [Team Elevate]

David,

Per email:

<< A few days ago I created a new
thread<http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_general&msg=13689&page=1>in
the forum about an issue we were having. After some tests, we found that our
particular query was failing when running in a background thread (as a DUnit
test case) but it was succeeding when running as a common report from the
main thread. >>

Are other threads, including the main thread, running that are accessing
ElevateDB databases when this thread executes ?  Normally, when there is an
issue with a query running in a thread vs. running in the ElevateDB Manager,
then the problem is with how ElevateDB is being accessed in the thread.
Furthermore, the fact that you're seeing floating point exceptions just
accessing the columns in the result set indicates that the internal state of
one or more ElevateDB engine objects is being violated, which is a signature
of threading issues in the code.

<< So, instead of fixing something which is not in our hands (because it was
running previously and it continues running for all our other databases,
namely SQL Server and MySQL), we will wait and see what happens with a new
build. >>

I wouldn't suggest that - there isn't any issue, in general, with query
execution in ElevateDB, and so nothing will get resolved if you don't send
me a test case or example that shows the problem that you're encountering.
Also, the fact that the problem went away with the Unicode version is not
indicative of there being a difference in the code between the two versions.
The problem simply went away, at least for now, due to the fact that timings
are different once you switch the underlying code in the engine.  It is very
likely that the same exact problem will appear again in the future.

---
Tim Young
Elevate Software
www.elevatesoft.com