Board index » delphi » Update From DBGrid

Update From DBGrid

Hi:

  Am selecting table from a dialog and display contents in DBGrid. Want to be able to
edit whichever table is selected but can't seem to since I don't have a ModifySQL in
the IBDataSet. Want user to be able to fix{*word*222}ups without having to go to console.

sprintf(OpenTable, "SELECT * FROM %s", TableName.c_str());
      IBTableView->SelectSQL->Clear();
      IBTableView->SelectSQL->Add(OpenTable);
      if (Trim(OrderTable) != "")
        IBTableView->SelectSQL->Add(OrderTable);
      OrderTable = " ";
      TableNameCaption->Caption = "Table: " + TableName;
      try
        {
         IBTableView->Prepare();
         IBTableView->Open();
         IBTableView->First();
        }
      catch(EIBError *E)
        {
         MesgDlg->DisplayMsg(E->IBErrorCode, E->Message);
         IBTableViewTransaction->RollbackRetaining();
         return;
        }

  So question is:

  Is there anyway to force IBDataSet to create the Modify/Insert/Delete/RefreshSQL's
at runtime
based on the loaded select statement?

Thanks.

                                                                        Best regards

 

Re:Update From DBGrid


Based on a single table select statement -

procedure TdmSchemaData.GenerateUpdateSQL;
 var
  InsertFieldList, InsertParamList, UpdateFieldList: string;
  WhereAllFieldList: string;

  procedure GenerateFieldLists;
  var
    I: Integer;
  begin
    for I := 0 to sqlData.FieldDefs.Count - 1 do
    begin
      with sqlData.FieldDefs[I] do
      begin
        if not (InternalCalcField or (faReadOnly in Attributes) or
          (DataType = ftUnknown)) then
        begin
          if ( InsertFieldList <> '' ) then
          begin
            InsertFieldList := InsertFieldList + ', ';
            InsertParamList := InsertParamList + ', ';
            UpdateFieldList := UpdateFieldList + ', ';
            if (DataType <> ftBlob) and (DataType <>ftMemo) then
              WhereAllFieldList := WhereAllFieldList + ' AND ';
          end;
          InsertFieldList := InsertFieldList +
            QuoteIdentifier(Database.SQLDialect, Name);
          InsertParamList := InsertParamList + ':' +
            QuoteIdentifier(Database.SQLDialect, Name);
          UpdateFieldList := UpdateFieldList +
            QuoteIdentifier(Database.SQLDialect, Name) +
            ' = :' +
            QuoteIdentifier(Database.SQLDialect, Name);
          if (DataType <> ftBlob) and (DataType <>ftMemo) then
            WhereAllFieldList := WhereAllFieldList +
              QuoteIdentifier(Database.SQLDialect, Name) + ' = :' +
              QuoteIdentifier(Database.SQLDialect, Name);{do not localize}
        end;
      end;
    end;
  end;

begin
  sqlData.SelectSQL.Clear;
  sqlData.SelectSQL.Text := 'select ' + {do not localize}
    QuoteIdentifier(Database.SQLDialect, TableName) + '.*, '
    + 'RDB$DB_KEY as IBX_INTERNAL_DBKEY from ' {do not localize}
    + QuoteIdentifier(Database.SQLDialect, TableName);

  sqlData.RefreshSQL.Text := 'select ' + {do not localize}
    QuoteIdentifier(Database.SQLDialect, TableName) + '.*, '
    + 'RDB$DB_KEY as IBX_INTERNAL_DBKEY from ' {do not localize}
    + QuoteIdentifier(Database.SQLDialect, TableName) +
    ' where RDB$DB_KEY = :IBX_INTERNAL_DBKEY'; {do not localize}

  sqlData.Prepare;

  GenerateFieldLists;
  sqlData.DeleteSQL.Text := 'delete from ' + {do not localize}
    QuoteIdentifier(Database.SQLDialect, TableName) +
    ' where RDB$DB_KEY = ' + ':IBX_INTERNAL_DBKEY'; {do not localize}
  sqlData.InsertSQL.Text := 'insert into ' + {do not localize}
    QuoteIdentifier(Database.SQLDialect, TableName) +
  ' (' + InsertFieldList + {do not localize}
    ') values (' + InsertParamList + ')'; {do not localize}
  sqlData.ModifySQL.Text := 'update ' +
    QuoteIdentifier(Database.SQLDialect, TableName) +
    ' set ' + UpdateFieldList + {do not localize}
    ' where RDB$DB_KEY = :IBX_INTERNAL_DBKEY'; {do not localize}
end;

This is all fired off when a TableName property is changed on the DM.

Quote
"Robert F. Tulloch" wrote:

> Hi:

>   Am selecting table from a dialog and display contents in DBGrid. Want to be able to
> edit whichever table is selected but can't seem to since I don't have a ModifySQL in
> the IBDataSet. Want user to be able to fix{*word*222}ups without having to go to console.

> sprintf(OpenTable, "SELECT * FROM %s", TableName.c_str());
>       IBTableView->SelectSQL->Clear();
>       IBTableView->SelectSQL->Add(OpenTable);
>       if (Trim(OrderTable) != "")
>         IBTableView->SelectSQL->Add(OrderTable);
>       OrderTable = " ";
>       TableNameCaption->Caption = "Table: " + TableName;
>       try
>         {
>          IBTableView->Prepare();
>          IBTableView->Open();
>          IBTableView->First();
>         }
>       catch(EIBError *E)
>         {
>          MesgDlg->DisplayMsg(E->IBErrorCode, E->Message);
>          IBTableViewTransaction->RollbackRetaining();
>          return;
>         }

>   So question is:

>   Is there anyway to force IBDataSet to create the Modify/Insert/Delete/RefreshSQL's
> at runtime
> based on the loaded select statement?

> Thanks.

>                                                                         Best regards

--
Jeff Overcash (TeamB)   I don't think there are any Russians
(Please do not email    And there ain't no Yanks
 me directly unless     Just corporate criminals
 asked.  Thank You)     Playing with tanks.  (Michael Been)

Re:Update From DBGrid


Just my opinion but situations like this call more for a
TIBTable than a query. However, if you want to see how Jeff
does it, look in the IBTable.pas unit under
TIBTable.GenerateSQL and GenerateUpdateSQL. Using his code,
though, may incur his wrath!! :)

HTH
Woody

"Robert F. Tulloch" <tult...@attglobal.net> wrote in message
news:3B4B3C31.21B40380@attglobal.net...

Quote
> Hi:

>   Am selecting table from a dialog and display contents in

DBGrid. Want to be able to
Quote
> edit whichever table is selected but can't seem to since I

don't have a ModifySQL in
Quote
> the IBDataSet. Want user to be able to fix{*word*222}ups

without having to go to console.
Quote

> sprintf(OpenTable, "SELECT * FROM %s", TableName.c_str());
>       IBTableView->SelectSQL->Clear();
>       IBTableView->SelectSQL->Add(OpenTable);
>       if (Trim(OrderTable) != "")
>         IBTableView->SelectSQL->Add(OrderTable);
>       OrderTable = " ";
>       TableNameCaption->Caption = "Table: " + TableName;
>       try
>         {
>          IBTableView->Prepare();
>          IBTableView->Open();
>          IBTableView->First();
>         }
>       catch(EIBError *E)
>         {
>          MesgDlg->DisplayMsg(E->IBErrorCode, E->Message);
>          IBTableViewTransaction->RollbackRetaining();
>          return;
>         }

>   So question is:

>   Is there anyway to force IBDataSet to create the

Modify/Insert/Delete/RefreshSQL's
Quote
> at runtime
> based on the loaded select statement?

> Thanks.

Best regards

Re:Update From DBGrid


Hi Jeff:

  Thanks for response. Had a question below. What is the + '.*, ' below?? Looks to be
  MyTable.* and I don't understand this??

Quote
> begin
>   sqlData.SelectSQL.Clear;
>   sqlData.SelectSQL.Text := 'select ' + {do not localize}
>     QuoteIdentifier(Database.SQLDialect, TableName) + '.*, '
>     + 'RDB$DB_KEY as IBX_INTERNAL_DBKEY from ' {do not localize}
>     + QuoteIdentifier(Database.SQLDialect, TableName);

                                                     Best regards

Re:Update From DBGrid


Hi:

Quote
> Just my opinion but situations like this call more for a
> TIBTable than a query.

  Thanks much for suggestion. Caused me to look at IBTable which I now see it is
derived from IBCustomDataset and implements its own SQL generation to look "simple" to
user. This seems kind of stupid now that I look at it since all the comments I ever
see say don't use IBTable. Really weird.

  Probably using IBTable here would save all the effort as suggested by Jeff's
response. I will look see, especially since my app is C++ and I just question here
since I usually get a knowledgeable and quick response.

  Thanks.

                                                  Best regards

Re:Update From DBGrid


Quote
"Robert F. Tulloch" wrote:

> Hi Jeff:

>   Thanks for response. Had a question below. What is the + '.*, ' below?? Looks to be
>   MyTable.* and I don't understand this??

In dialect 1 it would be MyTable.* in Dialect3 it would be "MyTable".* to
properly quote mixed case table names.  Instead of getting the primary key
(which may or may not exist depending on if you use Primary keys or just use
unique indexes) I get the RDB$DB_KEY which is always unique and always available
for every record.

Quote

> > begin
> >   sqlData.SelectSQL.Clear;
> >   sqlData.SelectSQL.Text := 'select ' + {do not localize}
> >     QuoteIdentifier(Database.SQLDialect, TableName) + '.*, '
> >     + 'RDB$DB_KEY as IBX_INTERNAL_DBKEY from ' {do not localize}
> >     + QuoteIdentifier(Database.SQLDialect, TableName);

>                                                      Best regards

--
Jeff Overcash (TeamB)   I don't think there are any Russians
(Please do not email    And there ain't no Yanks
 me directly unless     Just corporate criminals
 asked.  Thank You)     Playing with tanks.  (Michael Been)

Other Threads