4

Is it possible to determine a parameters data type in a parameterized query using Zeoslib and Firebird?

I'm migrating from FibPlus to Zeoslib.

with a stament like 'select name, surname, age from members where registration > :since and nationality = :country'

With FibPlus I do this:

  for i := 0 to Q1.Params.Count - 1 do
  begin

    t := Q1.Params[i].ServerSQLType;

    case t of
      SQL_TYPE_DATE, SQL_TYPE_DATE + 1:
        begin
          with TsDateEdit.Create(self) do
          begin
            Left := lblleft + MaxWidth + 10;
            Top := lbltop + (25 * i) - (height - LabelHeight);
            Parent := f;
            Tag := i + 1;
            TabOrder := i;
          end;
        end;
      sql_short, sql_long, sql_int64, sql_short + 1, sql_long + 1, sql_int64 + 1:
        begin
          with TsSpinEdit.Create(self) do
          begin
            Left := lblleft + MaxWidth + 10;
            Top := lbltop + (25 * i) - (height - LabelHeight);
            Parent := f;
            Tag := i + 1;
            TabOrder := i;
          end;
        end;
....

With Zeoslib I have tried this:

  for i := 0 to Q1.Params.Count - 1 do
  begin

    if Q1.Params[i].DataType = ftDate then
        begin
          with TsDateEdit.Create(self) do
          begin
            Left := lblleft + MaxWidth + 10;
            Top := lbltop + (25 * i) - (height - LabelHeight);
            Parent := f;
            Tag := i + 1;
            TabOrder := i;
          end;
        end;

    if Q1.Params[i].DataType in [ftInteger, ftShortInt, ftLargeInt, ftLongWord] then
        begin
          with TsSpinEdit.Create(self) do
          begin
            Left := lblleft + MaxWidth + 10;
            Top := lbltop + (25 * i) - (height - LabelHeight);
            Parent := f;
            Tag := i + 1;
            TabOrder := i;
          end;
        end;
....

but Q1.Params[i].DataType is allways ftUnkown.

maz
  • 41
  • 3
  • This is to allow a user to pick a query and present an apropriate input form, for them to enter the values required for the parameters. – maz Oct 10 '15 at 10:53

1 Answers1

2

You can't do that with prams because they are not set at this moment. I suggest you to use TZSQLMetadata;

  ZSQLMetadata1 := TZSQLMetadata.Create(Self);
  try
    ZSQLMetadata1.Connection := ZConnection1; //Your connection to db
    ZSQLMetadata1.MetadataType := mdColumns; //What kind of meta data get you want column types so we get columns metadata
    ZSQLMetadata1.TableName := 'MYTABLE'; //from which table metadata to get
    ZSQLMetadata1.Open;

    //After open ZSQLMetadata1 rows contains information about table columns
    while not ZSQLMetadata1.Eof do
    begin
      if ZSQLMetadata1.FieldByName('TYPE_NAME').AsString = 'char' then
      begin
        //Do something
      end;

      { if you do not like working with strings
      if ZSQLMetadata1.FieldByName('DATA_NAME').AsInteger = 9 then
      begin
        //Do something
      end;
      }   
      ZSQLMetadata1.Next;
    end;
  finally
    ZSQLMetadata1.Free;
  end;

ZSQLMetadata1 works like dataset you can assign it to datasource ad look at all columns and values in dbgrid

/edit/ Column names and field values is DB specifics my example is on MySQL database

Imants Gulbis
  • 87
  • 1
  • 8
  • Hi Imants. I agrre that you can obtain the metadata for a table with a TZSQLMetadata component. I use it frequenly. The problem is how to determine the required parameters data types, from the sql statement. – maz Oct 13 '15 at 07:59
  • It could be done, but would require parsing the statement for the tables involved, the fields with which the parameters are compared, etc... A TZQuery must do all of this internally anyway. I just think there must be a more straight forward way of doing it. – maz Oct 13 '15 at 08:03
  • If parameters are named same as fields then they types must match (I usually name them same). ZQury do not determine parameter types it sends them same as user set them and then checks DB errors. DB checks parameters. – Imants Gulbis Oct 13 '15 at 10:51