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

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

Postby 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; <= Error is here
   
   fmdatamodule.MyConnection.Commit;
  except
   fmdatamodule.MyConnection.rollback;
  end;

finally
MyTmpQuery.free;
end;


After the opening, i'm gettingthis error message :

Project xyz.exe raised exception class EMyError with message
'#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4'.


I check with the Debugger : everything is fine !
I've also tried to Edit the field as a blob

Code: Select all
    BlobField := MyTmpQuery.FieldByName('Proprietes') as TBlobField;
    BlobField.AsString:=trim(Prorietes.Text);
    //BlobField.AsString:=trim(Prorietes.Text); Excpetion is raised


When looking the DBMonitor, i'm getting this

Code: Select all
SQL Execute : UPDATE Preferences SET Proprietes = :1 WHERE :1(Memo,IN)=

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 ?
Last edited by swierzbicki on Tue 31 Jan 2006 08:03, edited 1 time in total.
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby 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.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby 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
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby swierzbicki » Wed 01 Feb 2006 07:35

Any Feedback from Crlab support ?
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby swierzbicki » Fri 03 Feb 2006 10:32

:?: :?: :?:
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby 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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby swierzbicki » Mon 06 Feb 2006 10:41

It is working when adding the Key fieldname.
thank you
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59


Return to MySQL Data Access Components