• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

Win process blocked when performing insert into DB2 database (ADOConnection)


I have developed a Delphi 7 application that writes information on a DB2 UDB AS400 database. I use an ADOConnection to be able to access the DB2 database, and an ADOQuery to write into the DB2 database.

The code is as follows:

  with ADOqrWrite_ERP_COM_SGA_ERP do
    Parameters.ParamByName('iID').Value             := iID ;
    Parameters.ParamByName('sMensID').Value         := sMensID ;
    Parameters.ParamByName('sMensPar1').Value       := sMensPar1 ;
    Parameters.ParamByName('sMensPar2').Value       := sMensPar2 ;
    Parameters.ParamByName('sMensPar3').Value       := sMensPar3 ;
    Parameters.ParamByName('sMensPar4').Value       := sMensPar4 ;
    Parameters.ParamByName('sMensPar5').Value       := sMensPar5 ;
    Parameters.ParamByName('sMensPar6').Value       := sMensPar6 ;
    Parameters.ParamByName('sMensPar7').Value       := sMensPar7 ;
    Parameters.ParamByName('sMensPar8').Value       := sMensPar8 ;
    Parameters.ParamByName('sMensPar9').Value       := sMensPar9 ;
    Parameters.ParamByName('sMensPar10').Value      := sMensPar10 ;
    Parameters.ParamByName('sEstado').Value         := sEstado ;
    Parameters.ParamByName('iEstProc').Value        := iEstProc ;
    Parameters.ParamByName('sTMPEmision').Value     := sTMPEmision;
    Parameters.ParamByName('sTMPTrato').Value       := sTMPTrato;

This code is inside a procedure, and previous to the call is ADOConnERP.BeginTrans (ADO connection) call, and after the procedure is ADOConnERP.CommitTrans call.

The SQL command to execute is as follows:

insert into COM_INBOX(secuencia,tipo,param1,param2,param3,
values (:iID,:sMensID,:sMensPar1,:sMensPar2,:sMensPar3,:sMensPar4,

(where COM_INBOX is a database table in the AS400 system)

The Connection String for the ADOConnection is:
Provider=IBMDA400.DataSource.1;Password="";Persist Security Info=True;User ID=sga;Data Source=;Protection Level=None;Extended Properties="";Initial Catalog=S655F80D;Transport Product=Client Access;SSL=DEFAULT;Force Translate=65535;Default Collection=SGA;Convert Date Time To Char=TRUE;Catalog Library List=QGPL;Cursor Sensitivity=3;Use SQL Packages=False;SQL Package Library Name="";SQL Package Name="";Add Statements To SQL Package=True;Unusable SQL Package Action=1;Block Fetch=True;Data Compression=True;Sort Sequence=0;Sort Table="";Sort Language ID="";Query Options File Library="";Trace=0;Hex Parser Option=0;Maximum Decimal Precision=31;Maximum Decimal Scale=31;Minimum Divide Scale=0

The problem is that the process gets blocked when performing the ExecSql operation (this can be seen when debbuging step by step the procedure). The process does not display any error message, it's just blocked, and it never goes to "Post" operation.

Also if you go to the DB2 database you can see the new row inserted by the process, but it seems to me that it's not completely OK, because there's a process in the AS400 system reading this rows and transfering them to another table, and with the rows inserted this way is not doing anything, they are not transfered.

When the process is forced to end an exception is returned (see error.jpg) picture. "It's not possible to make the operation while executing in asynchronous way)

It's my first time using ADOConnections and also I don't usually work with AS400 systems (I have no chance to change anything on that system). Any help will be very appreciated

Tanks to all

  • 4
  • 4
1 Solution
how do you know the record was inserted?
llorensLAuthor Commented:
I know it becasue I can see the record in the DB2 database (from my windows server with sqlexplorer software and an ODBC connection to the DB2 in the AS400 system).
Also mention that the drivers used to connect to the AS400 are IBM iSeries Client Access (used to generate both ODBC connnection and TADOConnection connection string)
what happens if you cancel the operation? does the record remain in the database or is it deleted?
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

llorensLAuthor Commented:
The record remains but no CommitTrans has been executed on my software. I think the problem is more: why the process gets blocked?.
if your insert is processed then it is a good question why you get blocked
i'd suggest you ask your dba to start a trace on the db2 part so he could tell you how long it takes to perform the insert statement and what happens after it
llorensLAuthor Commented:
The record remains but no CommitTrans has been executed on my software. I think the problem is more: why the process gets blocked?.
you first need to find out if the process is connected to the database when it is blocked or not
llorensLAuthor Commented:
Finally the problem was on the db2 side, I didn't get to know what was has been done, but they changed something about libraries and it worked out. Thanks and regars

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now