SQL syntax with variables...

[gelinp]

Hi,

I've got a complicated query with MySQL. I tried a lot of syntax but nothing work... This is the last state of my query :

Code: Select all

procedure TZQueryClassesAdaptateur.SynchronizeApresSupression;
var
  OldSQL: String;
  sqltxt : String;
begin
  try
    with _query do
    begin
      OldSQL := SQL.Text;
      SQL.Clear;
      sqltxt := 'SET @VueGD := ''SELECT `gauche` AS `seq_number`, `idplan` FROM `DOCClassements` '+
                      'UNION SELECT `droit` AS `seq_number`, `idplan` FROM `DOCClassements'';';
      SQL.Add(sqltxt);
      sqltxt := 'UPDATE `DOCClassements` ' +
        'SET `gauche` = ( SELECT COUNT(*) FROM @VueGD WHERE `idplan` = :idplan AND `seq_number` <= `gauche`), ' +
             '`droit` = ( SELECT COUNT(*) FROM @VueGD WHERE `idplan` = :idplan AND `seq_number` <= `droit`) ' +
        'WHERE `idplan` = :idplan;';
      SQL.Add(sqltxt);
      ParamByName('idplan').Value := _clsracine.idplan;
      ExecSQL;
      SQL.Text := OldSQL;
    end;

  except
    On E: EDatabaseError do
    begin
      _query.SQL.Text := OldSQL;
      TraitementErreurDatabase('Synchronize', E);
    end;
  end;
end; 

Lazarus warm with a syntax error message next to 'SET `gauche` = ( SELECT COUNT(*) FROM @VueGD WHERE `idplan` = :idplan AND `seq_number` <= `gauche`)'...

Thank you for your help !

[marsupilami]

Hello Patrick,

two things come to my mind. I am not sure wether TZQuery will behave correctly if you want to execute more than one statement in one go with it. Better start an explicit transaction and execute each statement on its own, like this:

Code: Select all

procedure TZQueryClassesAdaptateur.SynchronizeApresSupression;
var
  OldSQL: String;
  sqltxt : String;
begin
  try
    with _query do
    begin
      OldSQL := SQL.Text;
      Connection.StartTransaction;
      try
        SQL.Text := 'SET @VueGD := ''SELECT `gauche` AS `seq_number`, `idplan` FROM `DOCClassements` '+
                        'UNION SELECT `droit` AS `seq_number`, `idplan` FROM `DOCClassements`';
        ExecSQL;
        SQL.Text := 'UPDATE `DOCClassements` ' +
          'SET `gauche` = ( SELECT COUNT(*) FROM @VueGD WHERE `idplan` = :idplan AND `seq_number` <= `gauche`), ' +
               '`droit` = ( SELECT COUNT(*) FROM @VueGD WHERE `idplan` = :idplan AND `seq_number` <= `droit`) ' +
          'WHERE `idplan` = :idplan;';
        ParamByName('idplan').Value := _clsracine.idplan;
        ExecSQL;
        Connection.Commit;
      except
        Connection.Rollback;
        raise;
      end;
      SQL.Text := OldSQL;
    end;
  except
    On E: EDatabaseError do
    begin
      _query.SQL.Text := OldSQL;
      TraitementErreurDatabase('Synchronize', E);
    end;
  end;
end;

Second: it seems that in your second statement you use a user variable for a table name. The MySQL documantation says:

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected...

So I assume, your subselects will fail anyway. You probably will have to transfer @VueGD to your program and create the second statement according to the result of your first statement.

With best regards,

Jan

[gelinp]

Ok, thank you for your response :-) So I understand I have to try master/detail query. Whay not ! I will try this evening...

Do you know if zeoslib will suport views later ? I downloaded a PDF documentation but there is a lot of paragraph to write again...

Do you know if defaults lazarus dabase component support views ?

Thank you for your response.
Patrick

[marsupilami]

Hello Patrick,

I didn't intend to tell you that Zeos doesn't support what you do. I wanted to tell you, that in your second SQL statement, the use of @VueGD might be incorrect, if I understand the MySQL manual (MySQL 5.7 Reference Manual, 10.4 User-Defined Variables) correctly. If you are sure, that your SQL is correct then Zeos will support you in getting your job done.

With best regards,

Jan

[gelinp]

Finally, this last version do the job :

Code: Select all

procedure TZQueryClassesAdaptateur.SynchronizeApresSupression;
var
  OldSQL: String;
  VueGDText, SQLText : String;
begin
  try
    with _query do
    begin
      OldSQL := SQL.Text;

      VueGDText := '(SELECT gauche AS seq_number, idplan FROM DOCClassements ' +
        'UNION SELECT droit AS seq_number, idplan FROM DOCClassements) AS VueGD';
      SQLText := 'UPDATE DOCClassements ' +
        'SET gauche = ( SELECT COUNT(*) FROM ' + VueGDText + ' WHERE idplan = :idplan AND seq_number <= gauche ) , ' +
             'droit = ( SELECT COUNT(*) FROM ' + VueGDText + ' WHERE idplan = :idplan AND seq_number <= droit ) ' +
        'WHERE idplan = :idplan';
      SQL.Text := SQLText;
      ParamByName('idplan').Value := _clsracine.idplan;
      ExecSQL;

      SQL.Text := OldSQL;
    end;

  except
    On E: EDatabaseError do
    begin
      _query.SQL.Text := OldSQL;
      TraitementErreurDatabase('Synchronize', E);
    end;
  end;
end; 

I'm astonished that I can't write :

'[...]
droit = ( SELECT COUNT(*) FROM VueGD WHERE idplan = :idplan AND seq_number <= droit )
[...]'

Because an alisa could be use whenever into the SQL request.... May be, it's because of parentheses, but I can't delete...

[marsupilami]

From a Zeos POV this is a limitation of MySQL. @VueGD is a user defined Variable. In MySQL one can't reference result sets or some kind of instant views with user defined Variables. Your current solution uses VueGD as a subselect and not as a view.

I am happy that you got your problem solved :-)