Delphi Database default value error

Question

(SQL Server 2012, Delphi XE2, DataSet, DBGrid and adoquery)

I have a simple table with these columns:

id entering, exiting, description

entering and exiting the field to get the default value of 0

These defaults when creating the new record does not appear in Delphi. Let's say we get only 5 description field to field entering the summer. When recording is not a problem. No shortage does not appear in normal şartlarda. You can continue to operate on something other records.

But you enter the value you add just entering the field is nothing new, and the error occurs in the process of trying to unseen on record. 0 seems to have opened in the area off connections and is no longer an error.

Very rarely these default values appear and not an error.

I wonder how I solve this problem?

Answer 1

If your question is how to get default column values from the server, the code below shows two methods of doing this where the server is an MS Sql Server, and how to supply them to a new row in the table via its OnNewRecord event. OnNewRecord is the dataset event you should use for setting field values for records as they are being inserted.

To minimize the possibility of misunderstandings, the code is intended to be self-contained: It creates a new table which has an Identity column and three others which have default values, and then uses two different methods of adding data rows to it.

Note: Delphi's TField and its descendants have an AutoGenerateValue property, and setting this to arDefault is supposed to retrieve the default column/field value from the server. In the Delphi7 era, the server-side functionality to do this was AFAIK only ever implemented in the DBTables unit for the long-obsolete BDE. In particular it was not supported by Delphi's ADO dataset types. I have not checked whether AutoGenerateValue works with other, more recent, Delphi versions and dataset types.

Also, note that the Sql statement to create the table calls DROP TABLE before, and to avoid an error the first time it executes, you'll need to temporarily comment-out the DROP TABLE part.

Code:

unit adodefaultsu;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ExtCtrls, DBCtrls, Grids, DBGrids, DB, ADODB, Variants, StdCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    Button1: TButton;
    ADOQuery2: TADOQuery;
    ADOQuery1id: TAutoIncField;
    ADOQuery1f1: TIntegerField;
    ADOQuery1f2: TIntegerField;
    ADOQuery1description: TStringField;
    DataSource2: TDataSource;
    DBGrid2: TDBGrid;
    ADODataSet1: TADODataSet;
    procedure FormCreate(Sender: TObject);
    procedure ADOQuery1NewRecord(DataSet: TDataSet);
    procedure Button1Click(Sender: TObject);
  private
    function GetColumnDefault(Field: TField): Variant;
    function GetColumnDefault2(Field: TField): Variant;
  public
    procedure CreateAndOpenTable;
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

const

  scSqlCreateTable =
   'DROP TABLE Table1;'#13#10
   + 'CREATE TABLE [dbo].[Table1]('#13#10
   + '  [id] [int] IDENTITY(1,1) NOT NULL,'#13#10
   + '  [f1] [int] NOT NULL CONSTRAINT [DF_Table1_f1]  DEFAULT ((1)),'#13#10
   + '  [f2] [int] NOT NULL CONSTRAINT [DF_Table1_f2]  DEFAULT ((2)),'#13#10
   + '  [description] [varchar](40) default('''')'#13#10
   + ') ON [PRIMARY]';

  scSqlInsertRow = 'INSERT table1 (f1, description) VALUES(5, ''first'')';

  scSqlSelect = 'select * from table1';

  scSqlGetColumnDefault =
  'SELECT object_definition(default_object_id) AS definition'#13#10
   + 'FROM   sys.columns'#13#10
   + 'WHERE  name      =''%s'''#13#10
   + 'AND    object_id = object_id(''dbo.%s'')';


procedure TForm1.FormCreate(Sender: TObject);
begin
    if not AdoDataSet1.Active then
       AdoConnection1.OpenSchema(siColumns, VarArrayOf(['MATest', 'dbo', 'Table1']), EmptyParam, AdoDataSet1);

end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  CreateAndOpenTable;
end;


function ExtractDefaultValue(Field : TField; const Input : String) : String;
begin
  //  The Input value is the default value for the column, surrounded by two
  //  sets of parentheses and, in the case of a Char column, by leading and
  //  trailing single-quote characters

  Result := Input;

  // remove leading and trailing parentheses
  while (Result <> '') and (Result[1] = '(') do
    Result := Copy(Result, 2, Length(Result) - 2);

  // for string fields, remove leading and trailing single-quotes
  if Field.DataType in [ftString] then begin
    if (Result <> '') and (Result[1] = '''') then
      Result := Copy(Result, 2, Length(Result) - 2);
  end;
end;

function TForm1.GetColumnDefault(Field : TField) : Variant;
var
  Sql : String;
  sValue : String;
begin
  try
    //  First, contruct and execute a query to retrieve the default value
    //  for the Field's server column
    Sql := Format(scSqlGetColumnDefault, [Field.FieldName, 'Table1']);
    if AdoQuery2.Active then
      AdoQuery2.Close;
    AdoQuery2.Sql.Text := Sql;
    AdoQuery2.Open;

    //  WARNING: The following code has only been tested with Int and VarChar() columns
    //  and may require extra code for other column types

    sValue := AdoQuery2.Fields[0].AsString;
    Result := ExtractDefaultValue(Field, sValue);
    Field.Value := Result;
  finally
    AdoQuery2.Close;
  end;
end;

function TForm1.GetColumnDefault2(Field : TField) : Variant;
var
  Sql : String;
  sValue : String;
begin

  //  First, open the schema for the server via the AdoConnection
  if not AdoDataSet1.Active then
     AdoConnection1.OpenSchema(siColumns, VarArrayOf(['MATest', 'dbo', 'Table1']), EmptyParam, AdoDataSet1);

  //  WARNING: The following code has only been tested with Int and VarChar() columns
  //  and may require extra code for other column types

  if not AdoDataSet1.Locate('COLUMN_NAME', Field.FieldName, [loCaseInsensitive]) then
    raise Exception.CreateFmt('GetColumnDefault2: Field % not found in schema', [Field.FieldName]);

  sValue := AdoDataSet1.FieldByName('COLUMN_DEFAULT').AsString;

  Result := ExtractDefaultValue(Field, sValue);
  Field.Value := Result;
end;

procedure TForm1.ADOQuery1NewRecord(DataSet: TDataSet);
begin
  GetColumnDefault2(DataSet.FieldByName('f1'));
  GetColumnDefault2(DataSet.FieldByName('f2'));
  GetColumnDefault2(DataSet.FieldByName('description'));
end;

procedure TForm1.CreateAndOpenTable;
begin
  AdoQuery1.SQL.Text := scSqlCreateTable;
  AdoQuery1.ExecSQL;

  AdoQuery1.SQL.Text := scSqlInsertRow;
  AdoQuery1.ExecSQL;

  AdoQuery1.Sql.Text := scSqlSelect;
  AdoQuery1.Open;

  //  Next, insert a second row by the equivalent of clicking the
  //  DbNavigator's '+' button

  AdoQuery1.Insert;
  AdoQuery1.Post;
end;

end.

The above code uses a second method of getting the column default values, by retrieving them from the server's database schema. To use this code, change the OnNewRecord code to refer to GetColumnDefault2 instead of GetColumnDefault. This method is possibly preferable, because it only involves getting the default values once, and thereafter the AdoDataSet1 caches them.

The above code was written for Delphi7 but should work with later versions, too.

If this answers your question, I'll try to edit your question to make it a bit clearer.

Finally, no Delphi answer that involves changing Sql in the client app seems complete these days without warning about the risk of Sql Injection - see https://en.wikipedia.org/wiki/Sql_injection.