UniDAC change values of TUniQuery.Params

[ael]

Hello,

Since UniDAC 8.4 (tested with 8.4.1, 8.4.2, 8.4.3 and 9.0.1) we noticed some errors when inserting data in a Firebird database (tested with 3.0.6 and 3.0.7 both in server and embedded mode). The same issue occurs in Delphi 10.4.2 and 11.

dbMonitor is showing the right values (fields are 2 integer and 3 varchar):


But when running a trace on the same database the second parameter is changed by adding 0x700 (tested with 41 turning into 1833 and 42 to 1834), as seen here:


Any clue as to why this value is changed? As far as I know this only happens with ibprovider (we use the same code with PostgreSQL and SQL Server and haven't noticed any issue).

[ViktorV]

Hi there,

Thank you for contacting Devart and for your inquiry!

Unfortunately, we were unable to reproduce the issue in our environment based on the information you provided. So, in order to investigate the issue and find an appropriate solution faster, please send us a complete sample which will stably reproduce the issue in our environment.
Please try to create a sample demonstrating the behavior you specified, including scripts to create and populate database data and send it to us for review using our contact form:
https://devart.com/company/contactform.html

Should you have any questions, do not hesitate to ask!

Best regards,
Viktor

[FredS]

Hello ViktorV,

Similar issue here but only with a Win32 compile:

Errors:

Code: Select all

    PA.exe raised exception class EUniError with message 'validation error for column "TRUSTEDDOMAINS"."DSDOMAINKINDS", value "4294967330"'.

    PA.exe raised exception class EIBCError with message 'validation error for column "TRUSTEDDOMAINS"."TRUSTTYPE", value "4294967298"

'.

Input:

Code: Select all

    ASql    'insert into trusteddomains (netbiosname, domainid, dnsdomainname, domainsid, dc, dsdomainkinds, trusttype) values (:netbiosname, :domainid, :dnsdomainname, :domainsid, :dc, :dsdomainkinds, :trusttype)'
    AParams    (
    'SMB03', 1, 'smb03.local',
    'S-1-5-21-3188387342-845300696-3033364349',
    'Offline or Unavailable', 34, 2)

Monitor:

Code: Select all

    SQL Execute: insert into trusteddomains (netbiosname, domainid, dnsdomainname, domainsid, dc, dsdomainkinds, trusttype) values (:netbiosname, :domainid, :dnsdomainname, :domainsid, :dc, :dsdomainkinds, :trusttype)
    :netbiosname(WideString[4])='EMCO'
    :domainid(LargeInt)=1
    :dnsdomainname(WideString[10])='emco.local'
    :domainsid(WideString[41])='S-1-5-21-2788477859-2423432321-3061298541'
    :dc(WideString[22])='Offline or Unavailable'
    :dsdomainkinds(LongWord)=34
    :trusttype(LongWord)=2
    Pending

[ViktorV]

Hi Fred,

Unfortunately, we missed to reproduce the described behavior.

I fully understand that this reply may sound like a generic one but in order to fix the issue, we need to reproduce it in our environment. The fastest way is to make this is when we can receive a small sample with queries, because the issue may be in DDL tables (which we try to create and field types may be different on your and our sides), or in project or component settings.

Best regards,
Viktor

[FredS]

Thanks ViktorV, but I'll wait for the next release this one may actually be part of that one: viewtopic.php?f=28&t=48012

[FredS]

this reply may sound like a generic one but in order to fix the issue

Sure, but that always places the problem on us, v9 is making me feel like an unpaid beta tester!

So let's be clear, this error is new in v9:

• doesn't occur with 8.4.4 (15-Jul-2021)

• nor 8.3.2 (14-Sep-2020)

• occurs only in Win32 not Win64 against the same db..

• occurs in a thread, but I haven't and wont' bother to test outside of a thread due to these other factors

• a NON parameterized Insert, using the same main connection works fine

• error is in both FBv3 and FBv4, only in v9

The only possible variable left is the Domain used on these two fields:

Code: Select all

CREATE DOMAIN DWORD AS
BIGINT
check ( value between 0 and 4294967295 );

Everything else is as vanilla as it gets..

[ViktorV]

Hi Fred!

Kindly be informed that unfortunally we weren't able fully reproduce your situation on our side, but we were able to reproduce a similar one.
So we able to fix the bug we have reproduced and its fix will be included in the next build of UniDAC, which we plan to release next month.

If you need a nightly build, let us know by fulfilling the form: https://devart.com/company/contactform.html

[jberge]

Hello,

Just to let you know, we've been able to consistently reproduce the issue reported by ael.
Please download the following POC here :
POC
In this archive, besides the source code, and firebird support folder, you'll find in the Win32 subfolders 2 different executable file :
- Parametric_8x.exe : compiled with Delphi 10.4 + Unidac 8.3.1
- Parametric_9x.exe : compiled with Delphi 11.1 + Unidac 9.1.1

Binaries will create and populate a new database using runtime/sql scripts in the DatabaseCreate function, and then perform 2 different queries in DatabasePatch function :
- QUERY_ERROR_MODEL_EXPLICIT where we replace the various values using format before passing the resulting SQL query string directly to TUniSQL to insert.
- QUERY_ERROR_MODEL_PARAMS where we're using Params & Macros of TUniSQL instead

This shouldn't trigger any exception in Parametric_8x.

Code: Select all

SUCCESS: Empty DB created!
SUCCESS: Database creation completed
SUCCESS: Connected to new_db.fdb
INSERT INTO p_error_model (bha_id, bha_errormodelcategoryid, bha_srvtypid, bha_estsys, bha_revisionnumber, bha_revisiondate, bha_inclinationrangemin, bha_inclinationrangemax, bha_horeasrwestexclusion, bha_toolparameterid, bha_modelsource, bha_owsgprefix, bha_shortname, bha_longname, bha_revisioncomment, bha_source, bha_application, bha_rangecomment) VALUES (2, 3, 1, 'TRUE', 1, 1, 0, 3.14159, 0, 1, 'Explicit', 'Explicit', 'Explicit', 'Explicit', 'Explicit', 'Explicit', 'Explicit', 'None');
INSERT INTO p_error_model (bha_id, bha_errormodelcategoryid, bha_srvtypid, bha_estsys, bha_revisionnumber, bha_revisiondate, bha_inclinationrangemin, bha_inclinationrangemax, bha_horeasrwestexclusion, bha_toolparameterid, bha_modelsource, bha_owsgprefix, bha_shortname, bha_longname, bha_revisioncomment, bha_source, bha_application, bha_rangecomment) VALUES ("3", "3", "1", "FALSE", "1", "1", "0", "3,141593", "0", "1", "Parametric", "Parametric", "Parametric", "Parametric", "Parametric", "Parametric", "Parametric", "None");
UPDATE s_cst SET bha_val = :Val WHERE bha_id = :Id;

In Parametric_9x on the other hand, you should get an exception for QUERY_ERROR_MODEL_PARAMS about one of the parameter : Problematic key value is ("BHA_SRVTYPID" = 14337) on table "P_ERROR_MODEL"

Code: Select all

SUCCESS: Empty DB created!
SUCCESS: Database creation completed
SUCCESS: Connected to new_db.fdb
INSERT INTO p_error_model (bha_id, bha_errormodelcategoryid, bha_srvtypid, bha_estsys, bha_revisionnumber, bha_revisiondate, bha_inclinationrangemin, bha_inclinationrangemax, bha_horeasrwestexclusion, bha_toolparameterid, bha_modelsource, bha_owsgprefix, bha_shortname, bha_longname, bha_revisioncomment, bha_source, bha_application, bha_rangecomment) VALUES (2, 3, 1, 'TRUE', 1, 1, 0, 3.14159, 0, 1, 'Explicit', 'Explicit', 'Explicit', 'Explicit', 'Explicit', 'Explicit', 'Explicit', 'None');
SELECT GEN_ID(p_error_model_Gen, 1) FROM RDB$DATABASE;
INSERT INTO p_error_model (bha_id, bha_errormodelcategoryid, bha_srvtypid, bha_estsys, bha_revisionnumber, bha_revisiondate, bha_inclinationrangemin, bha_inclinationrangemax, bha_horeasrwestexclusion, bha_toolparameterid, bha_modelsource, bha_owsgprefix, bha_shortname, bha_longname, bha_revisioncomment, bha_source, bha_application, bha_rangecomment) VALUES ("1", "3", "1", "FALSE", "1", "1", "0", "3,141593", "0", "1", "Parametric", "Parametric", "Parametric", "Parametric", "Parametric", "Parametric", "Parametric", "None");
Problematic key value is ("BHA_SRVTYPID" = 14337) on table "P_ERROR_MODEL"

Please note that this value has been (silently) changed from what we've requested ("BHA_SRVTYPID" = 1) (we've added a dirty log to every request to make this fact more visible).
Could you please take a look and let us know if you're seeing the same behaviour on your end ? Don't hesitate to let me know if you need anything else as this issue is blocking our migration to Delphi 11 (and unidac 9)

Thanks a lot for your time,
Regards,

[pavelpd]

Hi

We've reproduced the issue and fixed it. The fix will be included in the next build of our product.

Also during the week we expect the UniDAC release with this fix included.

[jberge]

Thx a lot for your time and support !
Can't wait to update our toolchain.

Regards,

[pavelpd]

Hi there!
You are always welcome.

Should you have any product related questions, do not hesitate to ask!