More on SQL Generation

ASTA can generate SQL either on the client or on an ASTA server. On the client side the TAstaClientSocket has the following properties and events to allow you to customize your SQL. If you are using server side SQL generation then you must have an ASTASQLGenerator connected to the TAstaServerSocket on your ASTA server. The AstaSQLGenerator acts similarly to the TAstaClientSocket in allowing you to customize your SQL. SQL dialects can vary greatly between databases and locations. ASTA provides certain properties and events to allow you to customize your SQL to fit your database and your locale.

AstaClientSockets and ASTASqlGenerators contain an SQLOption property of type TAstaServerSQLOptions that is defined in AstaSqlUtils.

TAstaServerSQLOptionTypes = (ssUseQuotesInFieldNames,

  ssTableNameInInsertStatments, ssBooleanAsInteger, ssUSDatesForLocalSQL,

  ssTerminateWithSemiColon, ssNoQuotesInDates, ssDoubleQuoteStrings,

  ssUseISInNullCompares);

TAstaServerSQLOptions = set of TAstaServerSQLOptionTypes;

·      ssUseQuotesInFieldNames:      Used for Paradox or any database that allows spaces in field names.

·      ssTableNameInInsertStatments:      Databases like Access need this set to false.

·      ssBooleanAsInteger:      MS SQL Server has bit fields that map to ftboolean in the VCL but then SQL must be generated as Integers, eg. 0 or 1.

·      ssUSDatesForLocalSQL:      Interbase, Paradox and xBase using the BDE don’t like international dates so this forces the server to receive US formatted dates.

·      ssTerminateWithSemiColon:      Access likes its sql statements to be termintaed with semi-colons.

·      ssNoQuotesInDates:      Use this if you don’t want to use single quotes in dates and datetimes.

·      ssDoubleQuoteStrings:      Use this if your strings don’t need single quotes but double quotes.

·      ssUseISInNullCompares:      Paradox doesn’t like = null and wants IS null

There may be times where these settings are not enough to customize the SQL that ASTA generates. In those instances you can use the DateMaskforSQL and DateTimeMaskForSQL properties to tell ASTA how to format your ftDate and ftDateTime fields in select statements and update and insert statements.

There are also two events available on the TAstaClientSocket and the TAstaSQLGenerator that allow any field to be formatted to your specifications. Note that you MUST set the boolean parameter Handled to true if you are overriding the default format that ASTA uses. It is up to you to format the string parameter TheResult to your specifictions.

procedure TForm1.AstaClientSocket1CustomSQLSyntax(Sender: TObject;

  DS: TDataSet; FieldName: string; var TheResult: string;

  var Handled: Boolean);

begin

  case DS.FieldByName(FieldName).DataType of

    ftDate: begin

              TheResult := QuotedStr(FormatDateTime(‘DD/MM/YYYY’,

                DS.FieldByName(FieldName).AsDateTime)));

              Handled := True;

            end;

  end;

end;

The TAstaClientSocket has an UpdateSQLSyntax property. Since there is no standard date and date time SQL syntax, this allows you to set the way date and datetime fields are represented in the SQL generated by ASTA. The default is usBDE and should be used with the BDE Server. usODBC should be used for the supplied ODBC Server. Since Access has it’s own idiosyncrasies, there is also a usAccess choice.

If the SQL that ASTA generates for your database is not correct, you may write a method handler that allows you to supply the correct SQL syntax.

Use the OnCustomSQLSyntax event from the AstaClientSocket. Here is an example of how to create your own SQL override event for an AstaClientSocket.

procedure TForm1.AstaClientSocket1OnCustomSQLSyntax(Sender: TObject;

  DS: TDataSet; FieldName: string; var TheResult: string;

  var Handled: Boolean);

const

 SingleQuote = #39;

begin

  case DS.FieldByName(FieldName).DataType of

    ftDateTime,

    ftDate:

      begin

        TheResult := SingleQuote + DS.FieldByName(FieldName).AsString +

          SingleQuote;

        Handled := True;

      end;

  end;

end;

Remember to set Handled to true so that ASTA knows that you are going to be handling these fields.