Issue when posting this SQL Statement (assign TStringList.Text to a field)

swierzbicki · Post by **swierzbicki** » Mon 30 Jan 2006 17:37

Hi Ikar,

I'm having an issue when opening a TMYquery having this SQL Statement :

I'm storing some preferences into a mediumTExt field (Proprietes field).
It contains datas structured like this :

DernierCodeTournee=0000

Code: Select all

  //Query is automatically created then freed
MyTmpQuery:=TMyQuery.create(nil);
MyTmpQuery.Connection:= fmdatamodule.MyConnection;
try
  fmdatamodule.MyConnection.StartTransaction;
  try
   MyTmpQuery.close;
   MyTmpQuery.text:=
   'Select Proprietes From Preferences where Section = "SIA_Parametres" FOR UPDATE'
   MyTmpQuery.open;
   AValue.Text := MyTmpQuery.fieldbyname('Proprietes').AsString;
   AValue.ValueFromIndex[Prorietes.IndexOfName('DernierCodeTournee')]:= '0001';
   
   MyTmpQuery.edit;
   MyTmpQuery.fieldbyname('Proprietes').AsString := Trim(AValue.text) ;
   MyTmpQuery.post; 

UPDATE Preferences
SET
  Proprietes = :1
WHERE

instead of

Code: Select all

UPDATE Preferences
SET
  Proprietes = 'DernierCodeTournee=0001'
WHERE Section = 'SIA_Parametres'

Here is the Mysql SQL Script to generate the Table :

Code: Select all

CREATE TABLE `preferences` (
  `Section` VARCHAR(25) COLLATE latin1_swedish_ci NOT NULL,
  `Proprietes` MEDIUMTEXT,
  `Descriptif` MEDIUMTEXT,
  PRIMARY KEY (`Section`))TYPE=InnoDB;

COMMIT;

INSERT INTO `preferences` (`Section`, `Proprietes`, `Descriptif`) VALUES 
  ('SIA_Parametres','DernierCodeTournee=0000',NULL);

INSERT INTO `preferences` (`Section`, `Proprietes`, `Descriptif`) VALUES 
  ('VERSION_Parametres','Version=0.3.0.0\r\nMiseAJour=SIAFCI_0.3.0.0.exe\r\nDate=23/01/2004',NULL);

COMMIT;

MySQL server version: 4.1.14-nt
MySQL client version: Direct
Borland BDS 2006 Update 1
MyDAC 4.30.0.10

Any idea ?

GEswin · Post by **GEswin** » Mon 30 Jan 2006 21:52

Perhaps it's a fault when pasting example, but I noticed:

Code: Select all

MyTmpQuery.text:=
   'Select Proprietes From Preferences where Section = "SIA_Parametres" FOR UPDATE'
   MyTmpQuery.open;
   AValue.Text := MyTmpQuery.fieldbyname('Settings').AsString;

On third line you refer to field "Settings" that isn't selected in first line.

swierzbicki · Post by **swierzbicki** » Tue 31 Jan 2006 08:03

Hi Geswin,

Yes, this was a tipo in my example. I will edit my post and correct it.
The problem still exists

swierzbicki · Post by **swierzbicki** » Wed 01 Feb 2006 07:35

Any Feedback from Crlab support ?

swierzbicki · Post by **swierzbicki** » Fri 03 Feb 2006 10:32

Ikar · Post by **Ikar** » Fri 03 Feb 2006 15:46

SQL generator tries to generate UPDATE query using fields that appear in the SELECT clause of SQL statement. If you want to update BLOB fields correctly, you should request key field(s) from the table in you SELECT statement.

swierzbicki · Post by **swierzbicki** » Mon 06 Feb 2006 10:41

It is working when adding the Key fieldname.
thank you