• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Join, UpdateSQL, and params with unknown fields

Hi,
  I posted a similar question previously here:  Using Delphi 4, Woll2Woll components, Oracle 8 db.  I want to be able to edit a query with a join.  I understand that I can do this with a TUpdateSQL, one for each table in the query.  The problem is it seems I have to reference field names and params in TUpdateSQL InsertSQL, deleteSQL, modifySQL statements, but I want the ability to add fields to the db in the future, so referencing fields at design time will not work.  Any help with examples of syntax greatly appreciated.
0
posconsultant
Asked:
posconsultant
  • 2
  • 2
1 Solution
 
mrissmannCommented:
This is the only way.

You are able to keep adding field anytime you want.  Instead of modifing the fields in the InsertSQL... just double click on the TUpdateSQL component and a wizard will appear.  There are two columns, one for the primary key and the other is for the fields that you want posted.  Select the field here (cannot be any joined fields).  When finished click on generate SQL.  Anytime that you add a field just come back to this wizard(if you want the new field to be posted) and add it to your selection and generate the SQL statement again.  I never manually modify the InsertSQL...

Any questions let me know.  We figured out the art of cachedupdates and TUpdateSQL the hardway.

Mark Rissmann
0
 
posconsultantAuthor Commented:
Thanks for the answer.  I don't like it :-), so I'm going to keep this open.  I want to be able to do this without having to alter the source code.  
0
 
mrissmannCommented:
Without cachedupdates turned on you can not edit a query with joins.  Maybe tClientDataset or somekind of asp.  Enjoy reinventing your own wheel.  I have been doing this for 4 years.
0
 
DValeryCommented:
Hi, posconsultant

Try this code:

-----------------cut-----------------

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, StdCtrls, Buttons, Grids, DBGrids;

type
  TForm1 = class(TForm)
    BitBtnOpen: TBitBtn;
    BitBtnClose: TBitBtn;
    Query1: TQuery;
    DataSource1: TDataSource;
    Memo1: TMemo;
    DBGrid1: TDBGrid;
    Database1: TDatabase;
    UpdateSQL1: TUpdateSQL;
    procedure BitBtnOpenClick(Sender: TObject);
    procedure BitBtnCloseClick(Sender: TObject);
    procedure OpenMyTable;
    procedure Query1AfterEdit(DataSet: TDataSet);
    procedure Query1AfterInsert(DataSet: TDataSet);
    procedure Query1AfterPost(DataSet: TDataSet);
    procedure Query1BeforeDelete(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

const
     sTableName='Available';     // Table Name

var
  Form1: TForm1;
  bInsert, bEdit : Boolean;

implementation

{$R *.DFM}

procedure TForm1.BitBtnOpenClick(Sender: TObject);
begin
     DataSource1.DataSet := Query1;
     Query1.SQL.text:= 'select * from '+sTableName+' (nolock) ';
     DataSource1.DataSet.Open;
     OpenMyTable;
end;

procedure TForm1.BitBtnCloseClick(Sender: TObject);
begin
     DataSource1.DataSet.Close;
end;

procedure TForm1.OpenMyTable;
var i:integer;
var fields_i, fields,set_u,cond,cond_d:string;
var isStr:boolean;
var IsNullField:boolean;
var IsPrimaryKey:boolean;
var SavePlace: TBookmark;
begin
    with UpdateSQL1 do begin
      cond:=' WHERE ';
      cond_d:=' WHERE ';
      set_u := '';
      fields := '';
      for i:=0 to Query1.Fields.Count-1 do begin
        cond := cond + Query1.Fields[i].FieldName+' = :OLD_'+Query1.Fields[i].FieldName + ' and ';
        cond_d := cond_d + Query1.Fields[i].FieldName+' = :'+Query1.Fields[i].FieldName + ' and ';
        set_u := set_u + Query1.Fields[i].FieldName+' = :'+Query1.Fields[i].FieldName+',';
        fields := fields + Query1.Fields[i].FieldName + ',';
        fields_i := fields_i + ':' + Query1.Fields[i].FieldName + ',';
      end;
      if length(fields)>1 then fields := copy(fields, 1, length(fields)-1);
      if length(fields_i)>1 then fields_i := copy(fields_i, 1, length(fields_i)-1);
      if length(set_u)>1 then set_u := copy(set_u, 1, length(set_u)-1);
      if length(cond)>12 then cond := copy(cond, 1, length(cond)-5);
      if length(cond_d)>12 then cond_d := copy(cond_d, 1, length(cond_d)-5);
      SQL[ukModify].Text:='Update '+sTableName+ ' Set ' + set_u + cond;
      SQL[ukInsert].Text:='Insert into '+sTableName+ ' (' + fields + ') values (' + fields_i + ')';
      SQL[ukDelete].Text:='Delete From '+sTableName + cond_d;
      Memo1.Lines.Add(SQL[ukInsert].Text);
      Memo1.Lines.Add(SQL[ukModify].Text);
      Memo1.Lines.Add(SQL[ukDelete].Text);
    end;
end;


procedure TForm1.Query1AfterEdit(DataSet: TDataSet);
begin
     bEdit := True;
end;

procedure TForm1.Query1AfterInsert(DataSet: TDataSet);
begin
     bInsert := True;
end;

procedure TForm1.Query1BeforeDelete(DataSet: TDataSet);
begin
     UpdateSQL1.SetParams(ukDelete);
     UpdateSQL1.ExecSQL(ukDelete);
end;

procedure TForm1.Query1AfterPost(DataSet: TDataSet);
begin
   if bEdit then  begin
     UpdateSQL1.Apply(ukModify);
     bEdit := False;
   end;
   if bInsert then  begin
     UpdateSQL1.Apply(ukInsert);
     bInsert :=False;
   end;
end;

end.
-----------------cut-----------------

object Form1: TForm1
  Left = 185
  Top = 125
  Width = 607
  Height = 453
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object BitBtnOpen: TBitBtn
    Left = 8
    Top = 6
    Width = 75
    Height = 25
    Caption = 'BitBtnOpen'
    TabOrder = 0
    OnClick = BitBtnOpenClick
  end
  object BitBtnClose: TBitBtn
    Left = 517
    Top = 7
    Width = 75
    Height = 25
    Caption = 'BitBtnClose'
    TabOrder = 1
    OnClick = BitBtnCloseClick
  end
  object Memo1: TMemo
    Left = 8
    Top = 286
    Width = 585
    Height = 132
    ScrollBars = ssBoth
    TabOrder = 2
  end
  object DBGrid1: TDBGrid
    Left = 8
    Top = 38
    Width = 585
    Height = 241
    DataSource = DataSource1
    TabOrder = 3
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object Query1: TQuery
    CachedUpdates = True
    AfterInsert = Query1AfterInsert
    AfterEdit = Query1AfterEdit
    AfterPost = Query1AfterPost
    BeforeDelete = Query1BeforeDelete
    DatabaseName = 'DBTest'
    UpdateObject = UpdateSQL1
    Left = 336
    Top = 8
  end
  object DataSource1: TDataSource
    DataSet = Query1
    Left = 408
    Top = 8
  end
  object Database1: TDatabase
    DatabaseName = 'DBTest'
    SessionName = 'Default'
    Left = 376
    Top = 8
  end
  object UpdateSQL1: TUpdateSQL
    Left = 296
    Top = 8
  end
end

-----------------cut-----------------


0
 
posconsultantAuthor Commented:
Thanks.  I was pretty sure this could be done by obtaining db metadata and dynamically generating SQL statements.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now