Solved

Join, UpdateSQL, and params with unknown fields

Posted on 2000-03-03
5
399 Views
Last Modified: 2012-05-04
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
Comment
Question by:posconsultant
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Expert Comment

by:mrissmann
ID: 2585466
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
 

Author Comment

by:posconsultant
ID: 2674569
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
 

Expert Comment

by:mrissmann
ID: 2675345
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
 
LVL 1

Accepted Solution

by:
DValery earned 200 total points
ID: 2708099
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
 

Author Comment

by:posconsultant
ID: 2708799
Thanks.  I was pretty sure this could be done by obtaining db metadata and dynamically generating SQL statements.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
When I am typing into a TEdit last word repeats 7 159
creating threads in delphi 1 158
Create a path if not exists 7 108
How to save the image in the .cds File ClientDataSet? 1 29
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question