Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
A working query started failing with build 10 |
Thu, Sep 8 2011 5:10 PM | Permanent Link |
Daniel Zimmerman | 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 |
Fri, Sep 9 2011 3:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Fri, Sep 9 2011 8:23 AM | Permanent Link |
Raul 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 |
Fri, Sep 9 2011 1:01 PM | Permanent Link |
Daniel Zimmerman | 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. |
Fri, Sep 9 2011 3:47 PM | Permanent Link |
Daniel Zimmerman | I forgot one last piece of information.
This test case is running as a thread (as part of DUnit). David |
Sat, Sep 10 2011 3:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Sep 14 2011 5:39 PM | Permanent Link |
Daniel Zimmerman | 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 |
Thu, Sep 15 2011 2:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Mon, Sep 19 2011 1:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |