TSQLExporter does not quote memo fields (or blobs)
Original Reporter info from Mantis: BigChimp
-
Reporter name: Reinier Olislagers
Original Reporter info from Mantis: BigChimp
- Reporter name: Reinier Olislagers
Description:
If you export a dataset using TSQLExporter, string fields are quoted (using '). Memo fields are not quoted, which leads to problems using the generated SQL.
Looking at the code, I suspect blob fields are not quoted either. See Additional Information for more details.
Proposed solution in Additional Information. If required, I can create a patch, but it's a one line change.
I propose that when a fix is made, it should also be introduced in the 2.6.0 stable branch:
- The change is small and confined to this unit alone
- Current SQL export functionality is likely to fail if you have memo fields
Steps to reproduce:
Compile the attached demo project.
Run: sqlexp>sqlexporttest -a --format=plain
This will create an sql file in your temp dir (the program will tell you where), containing output of an integer, string and memo field. String and memo field have the same content assigned to it.
The string field is quoted, the memo field is not.
Additional information:
Error happens because of line 194 of fpSQLExport:
Function TCustomSQLExporter.SQLValue(F : TField) : String;
begin
Result:=FormatField(F);
If (F.DataType in StringFieldTypes+DateFieldTypes) then
Result:=''''+QuoteFIeld(Result)+'''';
end;
that refers to fpDBExport
Line 295:
StringFieldTypes = [ftString,ftFixedChar,ftWidestring,ftFixedWideChar];
while line 299 contains:
MemoFieldTypes = [ftMemo,ftFmtMemo,ftWideMemo];
I'd suggest changing fpSQLExport:
Function TCustomSQLExporter.SQLValue(F : TField) : String;
begin
Result:=FormatField(F);
If (F.DataType in BlobFieldTypes+StringFieldTypes+MemoFieldTypes+DateFieldTypes) then
Result:=''''+QuoteFIeld(Result)+'''';
end;
Why also blobfieldtypes? These contain byte data and may contain ,s and ;.
There is no real cross-db solution for exporting blob data anyway; a pragmatic approach could be to just base64 encode it.
Mantis conversion info:
- Mantis ID: 19937
- OS: Windows
- OS Build: Vista
- Build: 2.5.1, SVN 31910
- Platform: x64
- Fixed in version: 3.0.0
- Fixed in revision: 19937 (#f79dc850)
- Target version: 3.0.0