Error: Object Was Open - Delphi

Question

I have the following issue, When ever I run this code in the procedure, getting a SQL from my SQL COMPACT DATABASE(sdf), it gives me an error "Object Already Open". How can I fix this. Below is my code for the procedure

Function GetSQL(sName: String; Var App: TApplication): String;
Var
  Q: TADOQuery;
Begin
  Q                  := TADOQuery.Create(App);
  Q.ConnectionString := GetConnectionStringFromRegistry;
  Q.Close;
  Q.SQL.Text                              := 'SELECT * FROM SQLs WHERE Name = :sname';
  Q.Parameters.ParamByName('sname').Value := sName;
  Try
    Q.Open;
    If Q.RecordCount >= 1 Then
      Begin
        Q.First;
        Result := Q['Query'];
        Q.Close;
      End;
  Finally
    Q.Free;
  End;
End;

[This is what the error looks like] [This is what the code looks like when I press Break]

The call stack shows that the line generating this does appear to show it's happening here, so set a breakpoint on the line after `Q := TAdoQuery.Create(App);`, and step through the code. When you reach (but before you execute) the line that starts with `Q.SQL.Text`, use Ctrl+F7 and evaluate `Q.Active` to see if it's open for some reason. I'm suspecting the problem is actually elsewhere (like stack or heap corruption) that's just being exposed here. — Ken White, May 24 '13 at 14:17
Check this response by @whosrdaddy (and comments!!) to this stackoverflow question: http://stackoverflow.com/a/14222561/582118 which identifies a known bug in the OLE DB provider and nvarchar fields bigger than 127 characters. If any of your fields from your table are larger, then this may be the reason for the "Object Already Open" error. — A Lombardo, May 24 '13 at 14:21

Ken White · Answer 1 · 2013-05-24T12:48:06.800

The only thing I see that could be a problem is that your code leaves the query open if there are no rows returned:

Q.Open;
Try
  If Q.RecordCount >= 1 Then
  Begin
    Q.First;
    Result := Q['Query'];
    Q.Close;   // If Q.RecordCount = 0 above, this line never executes
  End;
Finally
  Q.Free;
End;

Move the Q.Close inside your finally instead, so it will always be called:

Q.Open;
Try
  If Q.RecordCount >= 1 Then
  Begin
    Q.First;
    Result := Q['Query'];
  End;
Finally
  Q.Close;   // This will always run, even if no rows are returned
  Q.Free;    // or if an exception occurs.
End;

As an aside, you should use parameterized queries instead of concatenating the text, especially if you're running the same query multiple times with the only change being the value of sName. The server is smart enough to cache the compiled query and only replace the parameter value, which means your code executes faster and with less load on the server.

Function GetSQL(sName: String; Var App: TApplication): String;
Var
  Q: TADOQuery;
Begin
  Q                  := TADOQuery.Create(App);
  Q.ConnectionString := GetConnectionStringFromRegistry;

  // I've even tried closing it first
  Q.Close;
  Q.SQL.Text         := 'SELECT Query FROM SQLs WHERE Name = :sname'; 
  Q.ParamByName('sname').AsString := sName;
  Try
    // Error occurs here
    Q.Open;
    //Q.Active := True;
    If Q.RecordCount >= 1 Then
    Begin
      Q.First;
      Result := Q['Query'];
    End;
  Finally
    Q.Close;
    Q.Free;
  End;
End;

I changed the code to the code that you suggested, and i still got the same error, could this be affected by the fact that this code is in a separate unit, or could it possibly be affected by the fact that my code is being called from a form that is in a dll ? My application setup is as follows. I have one actual form in my project. The rest of my forms are all located in separate dll's so that i can update and replace them easily once I release the application. The application is being written in Delphi XE3 FireMonkey2 — Jacques Koekemoer, May 24 '13 at 12:15
I don't know, and it's not clear from your original question which of the two (the first or the overloaded second), both of which have the same issues I mentioned. Can you [edit] your post to provide the information from your comment here, the **exact** error message you're getting (including any address info), and which one of the two procedures is actually resulting in the error? — Ken White, May 24 '13 at 13:25
I edited the post, but unfortunately it doesn't give an address, I edited my post, i also added pictures of the error and what happens when I press break — Jacques Koekemoer, May 24 '13 at 14:01

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

Thanks to @user582118 for reminding this one...

This is actually a bug in the OleDB provider for SQL CE. If you have nvarchar fields greater than 127 characters in a table and you do a select query on that table, you will receive the DB_E_OBJECTOPEN error.

Original thread : https://stackoverflow.com/a/14222561/800214

edited May 23 '17 at 12:30

Community

1
1

answered May 24 '13 at 17:29

whosrdaddy

11,720
4
50
99

Okay so for me to fix this I need to change my field types to not be nvarchar where they are bigger than 127 Characters, Thank you so much – Jacques Koekemoer May 25 '13 at 06:45

Error: Object Was Open - Delphi

2 Answers2