CODEWITHSUNDEEP
delphisql-server-ceado
Jacques Koekemoer
Jacques Koekemoer

Reputation: 1414

Error: Object Was Open - Delphi

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 error looks like [This is what the code looks like when I press Break] This is what the code looks like when I press Break

Upvotes: 1

Views: 3321

Answers (2)

whosrdaddy
whosrdaddy

Reputation: 11860

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

Upvotes: 1

Ken White
Ken White

Reputation: 125689

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;

Upvotes: 2

Related Questions