Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Firebird: Data truncated after "&"


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 5 - Last Post: Nov 2, 2017 1:01 PM Last Post By: Robert Triest
Guest
Firebird: Data truncated after "&"
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 8:01 AM
Please help me, how to solve just a simpe execute SQL:

UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA) VALUES ('AAA','SSSS','A&A','B&B') MATCHING (FILENAME,SECTION,IDENTITY)

Saved data:
FILENAME='AAA' -> ok
SECTION='SSSS' -> ok
IDENTITY='A' -> truncated, original value 'A&A'
DATA='B' -> truncated, original value 'B&B'

My table
CREATE TABLE INIFILE (
FILENAME VARCHAR(40) DEFAULT '',
SECTION VARCHAR(40) DEFAULT '',
IDENTITY VARCHAR(40) DEFAULT '',
DATA VARCHAR(80) DEFAULT '',
);

Tool=RAD Studio 10.1 Berlin
FireDAC=15.0.1 (Build 86746)
Platform=Windows 32 bit
Server version=WI-V2.5.7.27050 Firebird 2.5

No Issue if execute in IBExpert

On Trace file I got:

Prepare [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
. Preprocessed [CMD="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A','B','U','NOW') MATCHING (FILENAME,SECTION,IDENTITY)", FROM="", VP=80, VPE=118, OBP=0, CK=6]

What does it means ?

Trace file
--- new start of FireDAC Trace ---
-=#!!! FireDAC Tracer started !!!#=-
. CreateConnection [ConnectionDef=""]
. CreateTransaction [ConnectionDef=""]
. CreateTransaction [ConnectionDef=""]

Open [ConnectionDef=""]
Definition [Name=""]
. Database=D:\DataClient\Cloud\CloudFO.GDB
. User_Name=SYSDBA
. Password=*****
. CharacterSet=UTF8
. MonitorBy=FlatFile
. DriverID=FB
<< Definition [Name=""]
FireDAC info
. Tool=RAD Studio 10.1 Berlin
. FireDAC=15.0.1 (Build 86746)
. Platform=Windows 32 bit
. Defines=FireDAC_NOLOCALE_META;FireDAC_MONITOR
<< FireDAC info
. isc_attach_database [dbname="D:\DataClient\Cloud\CloudFO.GDB", params="user_name=SYSDBA,password=*********,lc_ctype=UTF8,sql_dialect=3,utf8_filename"]
. isc_database_info [db_handle=$00000001, info=103]
Driver log
. Loading driver FB ...
<< Driver log
. isc_database_info [db_handle=$00000001, info=103]
Client info
. Brand=Firebird
. Client version=205079900
. Client DLL name=C:\WINDOWS\SYSTEM32\fbclient.dll
<< Client info
. isc_database_info [db_handle=$00000001, info=103]
Session info
. Current catalog=
. Current schema=
. Server version=WI-V2.5.7.27050 Firebird 2.5
WI-V2.5.7.27050 Firebird 2.5/XNet (JAYT430)/P12
WI-V2.5.7.27050 Firebird 2.5/XNet (JAYT430)/P12

<< Session info
<< Open [ConnectionDef=""]
. CreateCommand [ConnectionDef=""]
Prepare [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
. Preprocessed [CMD="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A','B','U','NOW') MATCHING (FILENAME,SECTION,IDENTITY)", FROM="", VP=80, VPE=118, OBP=0, CK=6]
StartTransaction [ConnectionDef=""]
. isc_start_multiple [count=1, params="write,read_committed,rec_version,nowait"]
<< StartTransaction [ConnectionDef=""]
. isc_dsql_allocate_statement [db_handle=$00000001]
. isc_dsql_prepare [tra_handle=$00000002, stmt_handle=$00000003, sql="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A','B','U','NOW') MATCHING (FILENAME,SECTION,IDENTITY)", dialect=3]
. isc_dsql_sql_info [stmt_handle=$00000003, info=21]
. isc_dsql_describe_bind [stmt_handle=$00000003, dialect=3]
<< Prepare [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
<< TFDCustomCommand.Prepare [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
TFDCustomCommand.InternalExecute [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0, ABlocked=False]
Execute [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0]
. isc_dsql_execute2 [tra_handle=$00000002, stmt_handle=$00000003, dialect=3]
Commit [ConnectionDef="", Retaining=False]
TFDCustomCommand.Unprepare [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
Unprepare [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
. isc_dsql_free_statement [stmt_handle=$00000003, option="DSQL_drop"]
<< Unprepare [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
<< TFDCustomCommand.Unprepare [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
. isc_commit_transaction [tra_handle=$00000002]
<< Commit [ConnectionDef="", Retaining=False]
<< Execute [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)", ATimes=1, AOffset=0, RowsAffected=1, RowsAffectedReal=True, ErrorAction=5]
. TFDCustomCommand.InternalExecuteFinished [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)", AState=2, FRowsAffected=1]
. Destroy [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)"]
<< TFDCustomCommand.InternalExecute [Command="UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING (FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0, ABlocked=False]
-=#!!! FireDAC Tracer stopped !!!#=-

TIA
Jay
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Firebird: Data truncated after "&"  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 8:38 AM   in response to: Guest in response to: Guest
Jayanto Chua wrote:

Please help me, how to solve just a simpe execute SQL:

UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA) VALUES
('AAA','SSSS','A&A','B&B') MATCHING (FILENAME,SECTION,IDENTITY)

Saved data:
FILENAME='AAA' -> ok
SECTION='SSSS' -> ok
IDENTITY='A' -> truncated, original value 'A&A'
DATA='B' -> truncated, original value 'B&B'

My table
CREATE TABLE INIFILE (
FILENAME VARCHAR(40) DEFAULT '',
SECTION VARCHAR(40) DEFAULT '',
IDENTITY VARCHAR(40) DEFAULT '',
DATA VARCHAR(80) DEFAULT '',
);

Tool=RAD Studio 10.1 Berlin
FireDAC=15.0.1 (Build 86746)
Platform=Windows 32 bit
Server version=WI-V2.5.7.27050 Firebird 2.5

No Issue if execute in IBExpert

On Trace file I got:

Prepare [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] . Preprocessed [CMD="UPDATE or
INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT)
VALUES ('AAA','SSSS','A','B','U','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", FROM="", VP=80, VPE=118, OBP=0, CK=6]

What does it means ?

Trace file
--- new start of FireDAC Trace ---
-=#!!! FireDAC Tracer started !!!#=-
. CreateConnection [ConnectionDef=""]
. CreateTransaction [ConnectionDef=""]
. CreateTransaction [ConnectionDef=""]

Open [ConnectionDef=""]
Definition [Name=""]
. Database=D:\DataClient\Cloud\CloudFO.GDB
. User_Name=SYSDBA
. Password=*****
. CharacterSet=UTF8
. MonitorBy=FlatFile
. DriverID=FB
<< Definition [Name=""]
FireDAC info
. Tool=RAD Studio 10.1 Berlin
. FireDAC=15.0.1 (Build 86746)
. Platform=Windows 32 bit
. Defines=FireDAC_NOLOCALE_META;FireDAC_MONITOR
<< FireDAC info
. isc_attach_database [dbname="D:\DataClient\Cloud\CloudFO.GDB",
params="user_name=SYSDBA,password=*********,lc_ctype=UTF8,sql_dialect=
3,utf8_filename"] . isc_database_info [db_handle=$00000001,
info=103] >> Driver log . Loading driver FB ...
<< Driver log
. isc_database_info [db_handle=$00000001, info=103]
Client info
. Brand=Firebird
. Client version=205079900
. Client DLL name=C:\WINDOWS\SYSTEM32\fbclient.dll
<< Client info
. isc_database_info [db_handle=$00000001, info=103]
Session info
. Current catalog=
. Current schema=
. Server version=WI-V2.5.7.27050 Firebird 2.5
WI-V2.5.7.27050 Firebird 2.5/XNet (JAYT430)/P12
WI-V2.5.7.27050 Firebird 2.5/XNet (JAYT430)/P12

<< Session info
<< Open [ConnectionDef=""]
. CreateCommand [ConnectionDef=""]
Prepare [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] . Preprocessed [CMD="UPDATE or
INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT)
VALUES ('AAA','SSSS','A','B','U','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", FROM="", VP=80, VPE=118, OBP=0, CK=6]
StartTransaction [ConnectionDef=""] . isc_start_multiple
[count=1, params="write,read_committed,rec_version,nowait"] <<
StartTransaction [ConnectionDef=""] .
isc_dsql_allocate_statement [db_handle=$00000001] .
isc_dsql_prepare [tra_handle=$00000002, stmt_handle=$00000003,
sql="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A','B','U','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", dialect=3] . isc_dsql_sql_info
[stmt_handle=$00000003, info=21] . isc_dsql_describe_bind
[stmt_handle=$00000003, dialect=3] << Prepare [Command="UPDATE or
INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT)
VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] << TFDCustomCommand.Prepare
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] >> TFDCustomCommand.InternalExecute
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0, ABlocked=False] >>
Execute [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0] .
isc_dsql_execute2 [tra_handle=$00000002, stmt_handle=$00000003,
dialect=3] >> Commit [ConnectionDef="", Retaining=False]
TFDCustomCommand.Unprepare [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] >> Unprepare
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] .
isc_dsql_free_statement [stmt_handle=$00000003, option="DSQL_drop"]
<< Unprepare [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] << TFDCustomCommand.Unprepare
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] . isc_commit_transaction
[tra_handle=$00000002] << Commit [ConnectionDef="",
Retaining=False] << Execute [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", ATimes=1, AOffset=0, RowsAffected=1,
RowsAffectedReal=True, ErrorAction=5] .
TFDCustomCommand.InternalExecuteFinished [Command="UPDATE or INSERT
INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", AState=2, FRowsAffected=1] . Destroy
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] << TFDCustomCommand.InternalExecute
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0, ABlocked=False]
-=#!!! FireDAC Tracer stopped !!!#=-

TIA
Jay

Most probably the FD AI is altering your statements. Tako a look at:
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Preprocessing_Command_Text_(FireDAC).

Special Character Processing

To transmit special characters - ‘!’, ‘&’, ‘:’, '?', ‘{’ or ‘}’ to the
DBMS, you need to do the following:
Double special characters (for example, ‘{{’.)
On MySQL, precede this character by the ‘\’ character (for example,
‘\”’.)
For '!', '&', '{', '}, set ResourceOptions.MacroCreate, MacroExpand
and EscapeExpand to False.
For ':', '?', set ResourceOptions.ParamCreate, ParamExpand to False.

Otherwise, the '!', '&', '{', '}' characters will be treated as a macro
command, and ':', '?' as parameter markers. Excluding the following
cases, when a construction is detected by FireDAC and will not be
treated as a parameter marker:
The Oracle PL/SQL assignment operator.
The Firebird EXECUTE BLOCK parameters inside of BEGIN … END.
The TSQL label.
The PostgreSQL and Informix '::' cast operator.
The Informix ':' catalog separator.
Guest
Re: Firebird: Data truncated after "&"
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 9:04 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Thanks so much, it works.

Lajos Juhasz wrote:
Jayanto Chua wrote:

Please help me, how to solve just a simpe execute SQL:

UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA) VALUES
('AAA','SSSS','A&A','B&B') MATCHING (FILENAME,SECTION,IDENTITY)

Saved data:
FILENAME='AAA' -> ok
SECTION='SSSS' -> ok
IDENTITY='A' -> truncated, original value 'A&A'
DATA='B' -> truncated, original value 'B&B'

My table
CREATE TABLE INIFILE (
FILENAME VARCHAR(40) DEFAULT '',
SECTION VARCHAR(40) DEFAULT '',
IDENTITY VARCHAR(40) DEFAULT '',
DATA VARCHAR(80) DEFAULT '',
);

Tool=RAD Studio 10.1 Berlin
FireDAC=15.0.1 (Build 86746)
Platform=Windows 32 bit
Server version=WI-V2.5.7.27050 Firebird 2.5

No Issue if execute in IBExpert

On Trace file I got:

Prepare [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] . Preprocessed [CMD="UPDATE or
INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT)
VALUES ('AAA','SSSS','A','B','U','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", FROM="", VP=80, VPE=118, OBP=0, CK=6]

What does it means ?

Trace file
--- new start of FireDAC Trace ---
-=#!!! FireDAC Tracer started !!!#=-
. CreateConnection [ConnectionDef=""]
. CreateTransaction [ConnectionDef=""]
. CreateTransaction [ConnectionDef=""]

Open [ConnectionDef=""]
Definition [Name=""]
. Database=D:\DataClient\Cloud\CloudFO.GDB
. User_Name=SYSDBA
. Password=*****
. CharacterSet=UTF8
. MonitorBy=FlatFile
. DriverID=FB
<< Definition [Name=""]
FireDAC info
. Tool=RAD Studio 10.1 Berlin
. FireDAC=15.0.1 (Build 86746)
. Platform=Windows 32 bit
. Defines=FireDAC_NOLOCALE_META;FireDAC_MONITOR
<< FireDAC info
. isc_attach_database [dbname="D:\DataClient\Cloud\CloudFO.GDB",
params="user_name=SYSDBA,password=*********,lc_ctype=UTF8,sql_dialect=
3,utf8_filename"] . isc_database_info [db_handle=$00000001,
info=103] >> Driver log . Loading driver FB ...
<< Driver log
. isc_database_info [db_handle=$00000001, info=103]
Client info
. Brand=Firebird
. Client version=205079900
. Client DLL name=C:\WINDOWS\SYSTEM32\fbclient.dll
<< Client info
. isc_database_info [db_handle=$00000001, info=103]
Session info
. Current catalog=
. Current schema=
. Server version=WI-V2.5.7.27050 Firebird 2.5
WI-V2.5.7.27050 Firebird 2.5/XNet (JAYT430)/P12
WI-V2.5.7.27050 Firebird 2.5/XNet (JAYT430)/P12

<< Session info
<< Open [ConnectionDef=""]
. CreateCommand [ConnectionDef=""]
Prepare [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] . Preprocessed [CMD="UPDATE or
INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT)
VALUES ('AAA','SSSS','A','B','U','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", FROM="", VP=80, VPE=118, OBP=0, CK=6]
StartTransaction [ConnectionDef=""] . isc_start_multiple
[count=1, params="write,read_committed,rec_version,nowait"] <<
StartTransaction [ConnectionDef=""] .
isc_dsql_allocate_statement [db_handle=$00000001] .
isc_dsql_prepare [tra_handle=$00000002, stmt_handle=$00000003,
sql="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A','B','U','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", dialect=3] . isc_dsql_sql_info
[stmt_handle=$00000003, info=21] . isc_dsql_describe_bind
[stmt_handle=$00000003, dialect=3] << Prepare [Command="UPDATE or
INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT)
VALUES ('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] << TFDCustomCommand.Prepare
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] >> TFDCustomCommand.InternalExecute
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0, ABlocked=False] >>
Execute [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0] .
isc_dsql_execute2 [tra_handle=$00000002, stmt_handle=$00000003,
dialect=3] >> Commit [ConnectionDef="", Retaining=False]
TFDCustomCommand.Unprepare [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] >> Unprepare
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] .
isc_dsql_free_statement [stmt_handle=$00000003, option="DSQL_drop"]
<< Unprepare [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] << TFDCustomCommand.Unprepare
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] . isc_commit_transaction
[tra_handle=$00000002] << Commit [ConnectionDef="",
Retaining=False] << Execute [Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", ATimes=1, AOffset=0, RowsAffected=1,
RowsAffectedReal=True, ErrorAction=5] .
TFDCustomCommand.InternalExecuteFinished [Command="UPDATE or INSERT
INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", AState=2, FRowsAffected=1] . Destroy
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)"] << TFDCustomCommand.InternalExecute
[Command="UPDATE or INSERT INTO INIFILE
(FILENAME,SECTION,IDENTITY,DATA,USERID,LASTEDIT) VALUES
('AAA','SSSS','A&A','B&B','U&SER','NOW') MATCHING
(FILENAME,SECTION,IDENTITY)", ATimes=0, AOffset=0, ABlocked=False]
-=#!!! FireDAC Tracer stopped !!!#=-

TIA
Jay

Most probably the FD AI is altering your statements. Tako a look at:
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Preprocessing_Command_Text_(FireDAC).

Special Character Processing

To transmit special characters - ‘!’, ‘&’, ‘:’, '?', ‘{’ or ‘}’ to the
DBMS, you need to do the following:
Double special characters (for example, ‘{{’.)
On MySQL, precede this character by the ‘\’ character (for example,
‘\”’.)
For '!', '&', '{', '}, set ResourceOptions.MacroCreate, MacroExpand
and EscapeExpand to False.
For ':', '?', set ResourceOptions.ParamCreate, ParamExpand to False.

Otherwise, the '!', '&', '{', '}' characters will be treated as a macro
command, and ':', '?' as parameter markers. Excluding the following
cases, when a construction is detected by FireDAC and will not be
treated as a parameter marker:
The Oracle PL/SQL assignment operator.
The Firebird EXECUTE BLOCK parameters inside of BEGIN … END.
The TSQL label.
The PostgreSQL and Informix '::' cast operator.
The Informix ':' catalog separator.
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Firebird: Data truncated after "&"  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 8:52 AM   in response to: Guest in response to: Guest
UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA) VALUES ('AAA','SSSS','A&A','B&B') MATCHING (FILENAME,SECTION,IDENTITY)

You should use parameters instead of putting your values in the SQL.

qryIniFile.SQL.Add('INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA) VALUES (:FILENAME,:SECTION,:IDENTITY,:DATA) MATCHING (FILENAME,SECTION,IDENTITY)');

qryIniFile.ParamByName('FILENAME').AsString:='AAA';
qryIniFile.ParamByName('SECTION').AsString:='SSSS';
qryIniFile.ParamByName('IDENTITY').AsString:='A&A';
qryIniFile.ParamByName('DATA').AsString:='B&B';

qryIniFile.ExecSQL;

Guest
Re: Firebird: Data truncated after "&"
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 9:06 AM   in response to: Robert Triest in response to: Robert Triest
Done after set ResourceOptions.MacroCreate, MacroExpand and EscapeExpand to False.

Thanks

Robert Triest wrote:
UPDATE or INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA) VALUES ('AAA','SSSS','A&A','B&B') MATCHING (FILENAME,SECTION,IDENTITY)

You should use parameters instead of putting your values in the SQL.

qryIniFile.SQL.Add('INSERT INTO INIFILE (FILENAME,SECTION,IDENTITY,DATA) VALUES (:FILENAME,:SECTION,:IDENTITY,:DATA) MATCHING (FILENAME,SECTION,IDENTITY)');

qryIniFile.ParamByName('FILENAME').AsString:='AAA';
qryIniFile.ParamByName('SECTION').AsString:='SSSS';
qryIniFile.ParamByName('IDENTITY').AsString:='A&A';
qryIniFile.ParamByName('DATA').AsString:='B&B';

qryIniFile.ExecSQL;

Robert Triest

Posts: 687
Registered: 3/24/05
Re: Firebird: Data truncated after "&"  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 1:01 PM   in response to: Guest in response to: Guest
But what if you have to pass Date/Time fields in your query instead of only strings?
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02