Source: SQL on the fly for Updating, Inserting, and Deleting records 
Author Message
 Source: SQL on the fly for Updating, Inserting, and Deleting records

Hi,

I have completed my work on an interesting demo (all source
included... and its free).

I have created a class that you inherit through your form's class
definition (it inherits from TForm) that allows you to define what
component goes to what field in a database. You also set up one TQuery
component and three buttons (Update/Add, New/Cancel, and Delete). From
there the majority of your work comes in defining what the TEdit or
TMemo field is in relation to your database.

For the moment, it only supports one table per form. I have already
staged things so that it will be able to go to an unlimited number of
tables with no change on your side (I just have to code it!  :) ).

There are two forms of definition that you can do: Parameter and
Function.

In the Parameter mode, you type the definition of the field into the
Text property of the TEdit or TMemo field. I will read that definition
on the form create and clear it from the field. From then on, I will
use what you defined to determine how to build my Insert, Update and
Delete SQL statements.

In the Function mode (also known as Dynamic SQL... I forget why I had
called it that :) ), you create several small functions that define
each component needed in the Insert, Update, and Delete SQL
statements. You will be (probably) creating one function per control
(though you could keep a local counter and call the same function over
and over again). On form create, you send me a pointer to each of your
functions and I call them each time I need to do an Insert, Update, or
Delete of data. I wipe out the previous definitions and only use what
you currently tell me.

Below is the read me file that I have included with the demo. So, you
can decide if you are interested in it.

I just now (12/11/95 at around midnight) uploaded the demo to:
  ftp://sunsite.icm.edu.pl/pub/delphi/incoming
It probably will not stay there long. It is called SQLDEMO.ZIP. It
will hopfully show up in the following web page's freeware section:
  http://www.*-*-*.com/
Which is the delphi Super Page (a VERY useful web page).

Please tell me if there is a better place to upload these types of
examples.

Thanks for your time and here's that README file:
*************************************************************************************
Documentation For SQLDemo - This program is written using Delphi v1.0
from Borland International

Written By: Allen M. Servedio (11/25/95)

This is the first Delphi program that I have released, so please let
me know if
I have forgotten anything (there is a file list at the bottom).

This sample program is my first and second (crude) attempts at
creating SQL 'on
the fly' to Insert, Update, and Delete records on a simple database
(an example
Access 2.0 database has been included) on simple screens. After a few
lines of
setup code (and a few mandatory buttons added), you can add as many
TEdit or
TMemo fields as you like to your screen. You then take one of two
paths:
  1. You define the field in the Text property of the TMemo and TEdit
fields.
                                   OR
  2. You write small functions that define the fields.

The last thing that you need is at least one TQuery component and a
suggested
TDatabase component.

For those of you that will look at this and say, "Why doesn't he just
use the
TDB components? Or the BatchMove component?" The answer to that is
that there
are several problems with using the RequestLive property and I like
this format
of entering and updating data a bit better. There are no restrictions
on what
data you represent (baring the ability of the TEdit and TMemo field to
display
it) from an SQL stand point and what the user is supposed to do is a
bit clearer
than the Navigator.

By the way... I made the primary key visible; this is usually not the
case. You
should not change its value. While it does not matter on an Insert
(Add), it does
on an Update or Delete. I left it visible so that you could see what
is
happening.

I am VERY interested in any comments or such that anyone may have.


********************************************************************************
This software is presented as-is and as freeware. The author (Allen M.
Servedio)
requests that you please include this README.TXT file with every copy
of the
software so that comments can be directed to him and that other users
can see
how to use it. The author is in no way responsible for the validity,
integrity,
functionality or use of the software and does not guarantee the
software in any
way.
********************************************************************************

--------------------------------------------------------------------------------

Required Attributes Before Setting Up TEdit and TMemo Fields For Both
Methods:
------------------------------------------------------------------------------

1. Add/Update Button -
   You must add a TButton component with:
     a. Name = AddUpdate
     b. Caption = &Update
     c. Event OnClick = AddUpdateClick
        Code AddUpdateClick:
         inherited AddUpdateClick(Sender, AddUpdate.Caption); {This
calls the
                                                            parent
form create.}
2. New/Cancel Button -
   You must add a TButton component with:
     a. Name = NewCancel
     b. Caption = &New
     c. Event OnClick = NewCancelClick
        Code for NewCancelClick:
         inherited NewCancelClick(Sender); {This calls the parent form
create.}
3. Delete Button -
   You must add a TButton component with:
     a. Name = Delete
     b. Caption = &Delete
     c. Event OnClick = DeleteClick
        Code for DeleteClick:
          inherited DeleteClick(Sender); {This calls the parent form
create.}

--------------------------------------------------------------------------------
********************************************
* If you choose to use the Parameter path: *
********************************************

On the Form itself:
-------------------
  a. Event OnCreate = FormCreate
  b. Code for FormCreate:
       {Will process all SQL using the parameters found in the
controls.}
       DynSetSQLProcessType('ParmBuild');
       inherited FormCreate(Sender); {This calls the parent form
create.}
  c. In your 'uses' clause (top of program), add: Dyninout
  d. Instead of your class inheriting from TForm, inherit from:
TDynSQLClass

Query Component:
----------------
  a. Hook it up to your database (I suggest hooking it up to the
TDatabase
     component so that you can turn off the login prompt if you want
to).
  b. Add into its SQL property these 3 lines:
       INSERT
       UPDATE
       DELETE
     You can place each of these in a seperate Query component, but
there is
     little reason to.

Setting Up A TEdit or TMemo Field (setup is exactly the same):
--------------------------------------------------------------

The following can be placed in the Text of the TEdit component or the
first line
in the TMemo component. Each of these must be seperated from each
other by a
space (except for the table and column combo) and must appear in the
order they
are specified below.

1. Group number - This is not being used yet, but will be used to
group components
    that effect the same table for that record.
2. Table.Column - First type your table name, then a period then the
column in
    that table that the field represents.
3. Column Type - Capital 'I' for numbers or a capital 'S' for strings
(including
   dates/times).
4. (optional) Primary Key - Capital 'P' to say this is part of, or is,
the primary
    key. You must specify at least one field with this.
5. (optional) Max Plus One - Add the clause 'MaxPlusOne' if you would
like that
    column to be set to the maximum number plus one for that column
during an
    Insert/Add. This is a quick and dirty way to get a unique key for
a record.

And few examples of the above are:

 The following means Group number 1, table HTML, column DSC, column
type STRING:
   1 HTML.DSC S

 The following means Group number 1, table HTML, column SEQNUM, column
type NUMBER,
  it is part of the primary key:
   1 HTML.SEQNUM I P

 The following means Group number 1, table HTML, column SEQNUM, column
type NUMBER,
  it is part of the primary key, make its value the maximum number
plus 1:
   1 HTML.SEQNUM I P MaxPlusOne

--------------------------------------------------------------------------------
*********************************************************
* If you choose to use the Dynamic (or function) path: *
*********************************************************

On the Form itself:
-------------------
  a. Event OnCreate = FormCreate
  b. Code for FormCreate:
       {Will process all SQL using the DynBuild functions.}
       DynSetSQLProcessType('DynBuild');
       {This tells me what function to run for your TQuery components.
The name
        does not matter. What you define when I call it is what
matters. You
        should define one for Update, one for Insert, and one for
Delete (though
        it can be the same control).}



       {You then tell me what function to call for each TEdit or TMemo
field.}

       {This calls the parent form create.}
       inherited FormCreate(Sender);
  c. In your 'uses' clause (top of program), add: Dyninout
  d. Instead of your class inheriting from TForm, inherit from:
TDynSQLClass

Query Component:
----------------
  Hook it up to your database (I suggest hooking it up to the
TDatabase
   component so that you can turn off the login prompt if you want
to).
  You can have one of these for each type of SQL, but there is little
reason to.
  The Functions that you should create for each type of SQL should
look like:

   {Example of filling out info for a Update TQuery component.}
   function Query1UpdateSQLInfo(var FieldDef: Pointer; SQLType:
string): string; far;
   begin
      with TSQLFieldInfo(FieldDef)^ do begin
         {Name of the TQuery component.}
         ComponentName := 'Query1';
         {Type of SQL to be done here. Valid types are Update, Insert
and Delete.}
         ExtraParm := 'Update';
      end;
   end;

Setting Up A TEdit or TMemo Field (setup is exactly the same):
--------------------------------------------------------------
  For each TEdit and TMemo field that will be used by this program,
define a
  function like the following:

   {Possible fields to fill in FieldDef:
   {  Table            {Table data is associated with.}
   {  Column           {Column in table that data is associated with.}
   {  DataType         {Type of data: N = Number, T = Text String, D =
Date}
   {  KeyFlag          {Primary key (P) or Foreign Key (F)... Only
really need Primary}

   {  ExtraParm        {Allows extra functionality. Like MaxPlusOne -
see below.}

   {  ComponentName    {The value of the Name property of the
component.}
   {  StringData       {If the data can be represented as a string you
can pass it to me.}
   {  ComponentNumber  {The number of the component (each component
has a number within a form)}

   {  ComponentType    {The type of the component: Edit = Edit field,
Memo = Memo field}
   {  PtrData          {Can pass the data via a pointer - not yet
implimented.}
   {  NextOne          {Pointer to next object - I will be filling
this out.}

   {SQLType will either have: Insert, Update or Delete as its value.
You can
     use this to alter what you send according to what is about to
happen.}

{Example of filling out info for a primary key field.}
function Edit1SQLInfo(var FieldDef: Pointer; SQLType: string): string;
far;
begin
   {These fields are mandatory. However, ExtraParm & KeyFlag are only
needed for THIS
    primary key. The MaxPlusOne will run a Max(SEQNUM)+1 to generate a
unique number for
    this column.}
   with TSQLFieldInfo(FieldDef)^ do begin
      Table := 'Table1';  {Table name.}
      Column := 'SEQNUM'; {Column name.}
      DataType := 'N';    {Type of data. N = Numeric, S = String, D =
Date}
      ExtraParm := 'MaxPlusOne'; {You can add more parameters here -
seperate
                             each one with a space. So, if you also
did not want
                             the field cleared, set it to: 'MaxPlusOne
NoClear'}
      KeyFlag := 'P';     {Indicates that this is a primary key.}

      ComponentName := 'Edit1'; {Name of the component.}

      {I can get this one for ya. You CAN fill it out if you want to.}
      StringData := Form1.Edit1.Text; {This is usually only needed if
you need to
                                 pull the data from a control that I
do not support.
                                 I put it here as an example. In
reality, I can pull
                                 the data from this TEdit component.}
   end;
end;

{Example of filling out info for a field no primary key field - Most
of your
 functions will look like this.}
function Edit2SQLInfo(var FieldDef: Pointer; SQLType: string): string;
far;
begin
   {These fields are mandatory.}
   with TSQLFieldInfo(FieldDef)^ do begin
      {Table to be written to.}
      Table := 'Table1';
      {Column to be written to.}
      Column := 'CODE';
      {Type of data. N = Numeric, S = String, D = Date}
      DataType := 'T';

      ComponentName := 'Edit2'; {Name of the component.}
   end;
end;

--------------------------------------------------------------------------------

Please look over the little demo that I included with this file. It
defines the
above and is a nice working example (providing that you have Access
2.0 ODBC
software). Otherwise, you will have to create your own database to
test it (no
problem... but I suggest that you start simple).

If you need to create your own database to test this, the database
specifics
are:
  Database Name: TEST
  Table Name: Table1
  Columns in Table:  Name        Type          Key Indicator
                     ----------- ------------- -------------
                     SEQNUM      Long Integer  Primary Key
                     CODE        Varchar(20)
                     DESCRIPTION Varchar(50)

Again, please send me any comments or questions that you may have and
enjoy it
(the price is certainly right: FREE).  :)

--------------------------------------------------------------------------------

File List:
------------
DYNINOUT.PAS
DYNIO.INC
PARAMSQL.DFM
PARAMSQL.PAS
PTRFUNC.DFM
PTRFUNC.PAS
PTRFUNCP.DPR
PTRFUNCP.EXE
PTRFUNCP.OPT
PTRFUNCP.RES
README.TXT
TEST.LDB
TEST.MDB



Fri, 29 May 1998 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. 'Fly-away' records and SQL

2. Cached Updates - OldValue property upon record insert is not NULL

3. Updating/ Inserting Records into Informix DB

4. Cached Updates: deleting records

5. Updating/deleting records with TQuery

6. Updating/deleting records with TQuery

7. Multiple Insert/Delete SQL syntax

8. Cached Updates QUESTION: Sorting table of updated and non-updated records

9. ttable.fielddefs.update doesn't update deleted fields

10. Q : SQL and INSERT and Multi-Records

11. Using SQL to delete duplicate records

12. How to INSERT a RECORD using SQL??

 

 
Powered by phpBB® Forum Software