Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: DELPHI 10 Seatlle Firedac and ODBC IBM iseries / Unable to update data


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


Permlink Replies: 3 - Last Post: Jul 8, 2016 3:12 AM Last Post By: Dmitry Arefiev
Romuald Kler

Posts: 4
Registered: 7/17/09
DELPHI 10 Seatlle Firedac and ODBC IBM iseries / Unable to update data  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 6, 2015 7:49 AM
Hello,

I try to use Firedac Seattle 10 with Delphi, but I am having serious difficulties, I can not change the data in the database

My table does contain a primary key and it is well marked in the ProviderFlags. I have checked that the connection, the querry or fields are not read-only.

When I execute my code, I have a exeception : EODBCNativeException who says that the generated SQL is not correct.

'SELECT NEUTAG, NEUMM, NEUJJ, NEUUHR, NEUUSR, NEUDST, ANDTAG, ANDMM, ANDJJ, ANDUHR, ANDUSR, ANDDST, SDBNR, EXPTYPA, PBEZ1, PBEZ2, FABBEZ1, FABBEZ2, STATU, EXRESCODE, SDBSPLPAR, SDBPDFNAM, EXPDIRPDF, CASNR, EIGNR, EGZ1NR, EGZ2NR, ZUBANR, ZUBNR, ZUBZ1NR, ZUBZ2NR, GULTAG, GULMM, GULJJ, ZBEZ1, OUTQ, SPRNR, LANDNR, EPNR, AREAID FROM FINAL TABLE (UPDATE GISDATU.PPEFSDSP'#$D#$A'SET EXPTYPA = ?'#$D#$A'WHERE EPNR = ?)'

This request is not compatible with iSeries. Instruction From FINAL TABLE (UPDATE ...) does not exist on ISERIES.

How to change the data on a Iseries using the ODBC driver and Firedac ? (without using a TFDupdateSql)

ISERIES : V6R1M0 : ODBC 32 Bits Iseries Access ODBC Driver 13.00.01.00

My test application is relatively simple:


FDConnection1.Connected := true;
FDQuery1.Active := true;
FDQuery1.First;
FDQuery1.Edit;
FDQuery1.FieldByName('EXPTYPA').AsString := 'OOB';
FDQuery1.Post;
FDQuery1.ApplyUpdates(-1);
FDQuery1.CommitUpdates;
FDConnection1.Commit;
 
 
 
 
object FDConnection1: TFDConnection
    Params.Strings = (
      'MonitorBy=Remote'
      'DataSource=EUROSOFT'
      'User_Name=RK'
      'Database=GISDATU'
      
        'ODBCAdvanced=SYSTEM=EUROSOFT;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=Q' +
        'GPL/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;CMT=0;XDYNAMIC=0;BLOCK' +
        'SIZE=32;REMARKS=1;PREFETCH=0;COMPRESSION=0;ALLOWUNSCHAR=1;BLOCKF' +
        'ETCH=0;GRAPHIC=3;ALLOWPROCCALLS=1'
      'Password=i5rk'
      'ODBCDriver=iSeries Access ODBC Driver'
      'RDBMS=DB2'
      'DriverID=ODBC')
    FetchOptions.AssignedValues = [evItems, evRecordCountMode, evAutoFetchAll]
    FetchOptions.AutoFetchAll = afDisable
    FetchOptions.RecordCountMode = cmFetched
    UpdateOptions.AssignedValues = [uvEDelete, uvEInsert, uvEUpdate, uvUpdateChngFields, uvUpdateMode, uvLockMode, uvLockPoint, uvLockWait, uvRefreshMode, uvCountUpdatedRecords, uvFetchGeneratorsPoint, uvCheckRequired, uvCheckReadOnly, uvCheckUpdatable, uvUpdateNonBaseFields, uvAutoCommitUpdates]
    UpdateOptions.UpdateNonBaseFields = True
    UpdateOptions.RefreshMode = rmAll
    UpdateOptions.CountUpdatedRecords = False
    UpdateOptions.FetchGeneratorsPoint = gpNone
    UpdateOptions.CheckRequired = False
    UpdateOptions.CheckReadOnly = False
    UpdateOptions.CheckUpdatable = False
    UpdateOptions.AutoCommitUpdates = True
    TxOptions.Isolation = xiUnspecified
    Left = 644
    Top = 137
  end
 
 
 object FDQuery1: TFDQuery
    CachedUpdates = True
    OnUpdateRecord = FDQuery1UpdateRecord
    Connection = FDConnection1
    UpdateTransaction = FDTransaction1
    FetchOptions.AssignedValues = [evMode, evItems, evLiveWindowParanoic, evDetailOptimize]
    FetchOptions.Mode = fmAll
    FetchOptions.DetailOptimize = False
    UpdateOptions.AssignedValues = [uvEDelete, uvEInsert, uvEUpdate, uvRefreshDelete, uvCountUpdatedRecords, uvGeneratorName, uvUpdateNonBaseFields, uvAutoCommitUpdates]
    UpdateOptions.UpdateNonBaseFields = True
    UpdateOptions.RefreshDelete = False
    UpdateOptions.UpdateTableName = 'GISDATU.PPEFSDSP'
    UpdateOptions.KeyFields = 'EPNR'
    SQL.Strings = (
      'select * from gisdatu.ppefsdsp')
 
    object FDQuery1EPNR: TBCDField
      AutoGenerateValue = arDefault
      FieldName = 'EPNR'
      Origin = 'EPNR'
      ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
      Required = True
      Precision = 10
      Size = 0
 
 


Best Regards
Romuald Kler
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: DELPHI 10 Seatlle Firedac and ODBC IBM iseries / Unable to update data  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 8, 2015 6:46 AM   in response to: Romuald Kler in response to: Romuald Kler
I answered you in other post.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Markus Schadegg

Posts: 4
Registered: 11/10/12
Re: DELPHI 10 Seatlle Firedac and ODBC IBM iseries / Unable to update data  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 5, 2016 5:52 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
I answered you in other post.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Where can the other post be found? (As I have a similar problem with DB/2)

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: DELPHI 10 Seatlle Firedac and ODBC IBM iseries / Unable to update data  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 8, 2016 3:12 AM   in response to: Markus Schadegg in response to: Markus Schadegg
Where can the other post be found? (As I have a similar problem with DB/2)

https://forums.embarcadero.com/message.jspa?messageID=710657#710657

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02