Solved

Join, UpdateSQL, and params with unknown fields

Posted on 2000-03-03
5
389 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
  • 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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 The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now