Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.


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


Permlink Replies: 11 - Last Post: Apr 18, 2017 6:53 AM Last Post By: Sergey Smagin
Walter Prins

Posts: 8
Registered: 6/21/01
FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 15, 2016 2:19 AM
Hello,

I am running into a perplexing problem with FireDAC via ODBC against an iSeries running v6r1 OS and using the latest ODBC drivers from the v7r1 PC software install. (Unfortunately I don't have exact matching drivers/software available, but note that all other software works entirely without incident, including accessing the iSeries using several other data access layers, including [BDE->ODBC] and DBX, all of which works fine so fundamentally this doesn't seem to be a version issue.) I'm using Windows 10 and Delphi 10 Seattle.

The symptoms are basically that no matter what options I use in connecting to the AS/400, the resultant dataset is read-only or fails to actually update the AS/400. Has anyone else run into this problem? What am I missing?

Information from FireDAC connection editor is as follows:

================================
Connection definition parameters
================================
ODBCDriver=iSeries Access ODBC Driver
User_Name=sqltest
Password=*****
MonitorBy=Remote
ODBCAdvanced=SYSTEM=S44H5004;DefaultLibraries=,TFERDTA,MAFHAM,TESTDTA6,TESTDTA,TESTWP;Naming=1
DriverID=ODBC
================================
FireDAC info
================================
Tool = RAD Studio 10 Seattle
FireDAC = 13.0.1 (Build 82709)
Platform = Windows 32 bit
Defines = FireDAC_NOLOCALE_META;FireDAC_MONITOR
================================
Client info
================================
Loading driver ODBC ...
Loading odbc32.dll driver manager
Creating ODBC environment handle
Driver Manager version = 03.81.10240.0000
================================
Session info
================================
Current catalog =
Current schema =
Driver name = CWBODBC.DLL
Driver version = 06.01.0010
Driver conformance = 2
DBMS name = DB2/400 SQL
DBMS version = 06.01.0014

The CWODBC.DLL (iSeries ODBC driver file) reports as follows: http://postimg.org/image/eo9f2k8xl/

Some other remarks: The FireDAC wizard puts the AS/400 library list in the "Database" property field in FireDAC. This is problematic as this property has a length limit of [IIRC] 32 characters while library lists can be a lot longer. You can however specify an arbitrary length library list by putting it in the ODBCAdvanced property in the iSeries ODBC driver parameter "DefaultLibraries". Above I therefore used a manual ODBCAdvanced string and used so called System Naming convention (Naming=1) to ensure the specified library list was actually used for Table resolution. (So called "SQL naming" does not use the fully library list, and will only resolve unqualified table names in the default library.) I also tried a wizard generated set of parameters (which by default uses SQL naming etc), this made no difference to the above problem.

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 16, 2016 12:15 AM   in response to: Walter Prins in response to: Walter Prins
We fixed several issues related to DB2 AS400 compatibility.
All that will be included into upcoming RAD Studio version.
If you like, you can join to beta program and test DB2 AS400 support.

--
With best regards,
Dmitry
Walter Prins

Posts: 8
Registered: 6/21/01
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 16, 2016 2:09 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Hi Dmitry,

Thanks for your response, much appreciated. I would definitely like to join the beta program and would love to ensure solid AS/400 ability as this as this problem is currently stopping us from developing a new solution with FireDAC and preventing migrating older code from non-legacy data access layers. Where/how do I join? (Is there a specific account area/URL on EDN or elsewhere that I need to visit/that I've missed?)

Thanks again,

Walter

Dmitry Arefiev wrote:
We fixed several issues related to DB2 AS400 compatibility.
All that will be included into upcoming RAD Studio version.
If you like, you can join to beta program and test DB2 AS400 support.

--
With best regards,
Dmitry
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 16, 2016 9:28 PM   in response to: Walter Prins in response to: Walter Prins
Where/how do I join? (Is there a specific account area/URL on EDN or elsewhere that I need to visit/that I've missed?)

I will handle that. Stay tuned ...

--
With best regards,
Dmitry
Walter Prins

Posts: 8
Registered: 6/21/01
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2016 2:58 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Where/how do I join? (Is there a specific account area/URL on EDN or elsewhere that I need to visit/that I've missed?)

I will handle that. Stay tuned ...

--
With best regards,
Dmitry

Thanks. I've had email. :)
Walter Prins

Posts: 8
Registered: 6/21/01
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 16, 2016 2:10 AM   in response to: Walter Prins in response to: Walter Prins
To add some detail to my original post (posted on G+ this morning):

"The basic problem is that basic dataset based update code simply does not work. That is, setting up a TFDConnection, TFDQuery and then attempting to update a table just simply does not work. The dataset is eiter read-only, or if I "force things" (for example by setting "UpdateNonBaseTables" or by unsetting "UpdateOptions.CheckReadOnly") then errors are generated.

Specifically, setting "UpdateNonBaseTables" to True allows the dataset to go into edit mode but then generates the following error:

EODBCNativeException with message '[FireDAC][Phys][ODBC][IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token . was not valid. Valid tokens: , FROM INTO.'"  


Using the FireDAC Monitor to trace the calls, I can see the origin of this error, the SQL generated is as follows:

TEST: TFDDAptTableAdapter($03776BF0).Lock: TFDPhysODBCCommand($036F86B0).Unprepare [Command="SELECT 
FROM TESTWP.TEST A
WHERE A.ID = :OLD_ID AND A.SOMETEXT = :OLD_SOMETEXT
FOR UPDATE"]


As you can see no field list is being generated for reasons I don't understand.

If instead I clear UpdateOptions.CheckReadOnly, then the following error is generated instead:

[FireDAC][Phys]-329. Cannot generate update query. WHERE condition is empty.

Again, this seems to be somehow related to the fields being not considered for inclusion for updating/locating records for reasons unknown. "

Edited by: Walter Prins on Feb 16, 2016 2:59 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 16, 2016 5:22 AM   in response to: Walter Prins in response to: Walter Prins
FROM TESTWP.TEST A

Ok, so does "TESTWP." makes the issue ?
Sorry, I does not know AS400 well.

--
With best regards,
Dmitry

Edited by: Dmitry Arefiev on Feb 16, 2016 5:22 PM
Walter Prins

Posts: 8
Registered: 6/21/01
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 17, 2016 5:12 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
FROM TESTWP.TEST A

Ok, so does "TESTWP." makes the issue ?
Sorry, I does not know AS400 well.

No -- if you'll notice, FireDAC is not generating any fields in the select. Consequently, the iSeries SQL parser is falling over when it reaches the "." between the TESTWP (schema/collection/library) and the TEST (Tablename).

If I run the same query in iSeries SQL tool I get the same error message.

If I fix it, by adding "*" or an explicit field list, the problem goes away.

The problem is FireDAC is not handling the SQL generation properly (e.g. it seems to think that all the fields are read only and/or not part of the base table) and no matter what I do I've not been able to get it to properly generate Insert/Update/Delete SQL etc.



--
With best regards,
Dmitry

Edited by: Dmitry Arefiev on Feb 16, 2016 5:22 PM
Guest
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 16, 2016 1:33 PM   in response to: Walter Prins in response to: Walter Prins
Hi Dmitry, I was wondering if this is the same reason I updated a project from XE7 to XE10 and now I get [FireDAC][Phys][ODBC][IBM][System i Access ODBC Driver]Driver not capable. When trying to INSERT or UPDATE with parameters.

Thanks
Jody
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 16, 2016 11:37 PM   in response to: Guest in response to: Guest
I was wondering if this is the same reason I updated a project from XE7 to XE10 and now I get [FireDAC][Phys][ODBC][IBM][System i Access ODBC Driver]Driver not capable. When trying to INSERT or UPDATE with parameters.

Most probably - yes.

--
With best regards,
Dmitry
Walter Prins

Posts: 8
Registered: 6/21/01
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 17, 2016 5:14 AM   in response to: Guest in response to: Guest
Jody Stone wrote:
Hi Dmitry, I was wondering if this is the same reason I updated a project from XE7 to XE10 and now I get [FireDAC][Phys][ODBC][IBM][System i Access ODBC Driver]Driver not capable. When trying to INSERT or UPDATE with parameters.

Thanks
Jody

For what it's worth, I've run into this error as well, in my attempts to make this work. (When I turned on Extended Metadata.) Might not be quite the same reason for this message as in your case of course.
Sergey Smagin

Posts: 3
Registered: 11/5/05
Re: FireDAC with iSeries v6r1 (drivers v7r1) - unable to update tables.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 18, 2017 6:53 AM   in response to: Walter Prins in response to: Walter Prins
Hello Dmitry,

our customers reported the same issue (queries are read-only for DB2 for i).
We are use RAD Studio XE8 currently.
I just wonder - is this issue solved in the latest RAD Studio versions?
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02