cannot determine how field size is returned - data truncated

[ca_cruiser]

hello,

Delphi 6 sp2
MySql 3.23.58
MyDAC 5.55.0.39

I'm having trouble with a Field.Size for sql stmt below.
I cannot figure out why data is being truncated at field level
where the mysql client program handles it just fine.

mysql> desc proc_run;
+------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| proc_key | char(5) | | PRI | | |
| proc_name | char(40) | | PRI | | |
| proc_host | char(25) | | | | |
| proc_path | char(100) | | | | |
| proc_parms | char(100) | | | | |
+------------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- Data truncated here
select
replace(r.proc_path
,"Reports.exe" # 11 chars
,"Reports2.exe" # 12 chars
) proc_path2
,r.proc_path
from proc_run r

fieldByName('proc_path2').Size: 10
fieldByName('proc_path').Size: 100

-- Data NOT truncated here

select
replace(r.proc_path
,"Reports.exe" # 11 chars
,"Reports2.xe" # 11 chars
) proc_path2
,r.proc_path
from proc_run r

fieldByName('proc_path2').Size: 100
fieldByName('proc_path').Size: 100

Code: Select all

[ca_cruiser]

forgot config.

Delphi 6 sp2
MySql 3.23.58
MyDAC 5.55.0.39

[ca_cruiser]

If I replace valueA with valueB where valueB is larger in size, I get truncated data. if valueB is smaller or of equal size then truncation does NOT occur.

[ca_cruiser]

hello,

more info.

same code/config under MySql 5.0.20a-nt-log results:

select
replace(r.proc_path
,"Reports.exe" # 11 chars
,"Reports2.exe" # 12 chars
) proc_path2
,r.proc_path
from proc_run r

fieldByName('proc_path2').Size: 109
fieldByName('proc_path').Size: 100

[Dimon]

This is a MySQL Server peculiarity. MySQL returns specified fields length for specified queries.
In order to solve this problem you can use the CAST function, like this:

Code: Select all

SELECT 
CAST(REPLACE(r.proc_path 
,"Reports.exe" # 11 chars 
,"Reports2.exe" # 12 chars 
) AS CHAR(100)) proc_path2 
,r.proc_path 
FROM proc_run r 

[ca_cruiser]

thx