Solved

Join, UpdateSQL, and params with unknown fields

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connection between libmysql.dll and MySQL Versions 7 99
creating threads in delphi 1 105
Adoquery sql  left join does not work 25 91
Graphics32 under Delphi 10.1 Berlin 2 67
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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