Page 1 of 1
SQL syntax with variables...
Posted: 06.11.2016, 18:59
by 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 !
Re: SQL syntax with variables...
Posted: 07.11.2016, 10:10
by 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:
MySQL 5.7 Reference Manual, 10.4 User-Defined Variables wrote: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
Re: SQL syntax with variables...
Posted: 07.11.2016, 12:05
by 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
Re: SQL syntax with variables...
Posted: 07.11.2016, 16:06
by 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
Re: SQL syntax with variables...
Posted: 07.11.2016, 20:38
by 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...
Re: SQL syntax with variables...
Posted: 08.11.2016, 11:17
by 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 :-)