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

Hello!

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
  begin
    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;
    ExecSQL;
    Post;
  end;

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,
param4,param5,param6,param7,param8,param9,param10,
estado,error,tmp_emision,tmp_trato)
values (:iID,:sMensID,:sMensPar1,:sMensPar2,:sMensPar3,:sMensPar4,
:sMensPar5,:sMensPar6,:sMensPar7,:sMensPar8,:sMensPar9,:sMensPar10,
:sEstado,:iEstProc,:sTmpEmision,:sTmpTrato)

(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=128.100.0.1;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


Error.JPG
llorensLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
how do you know the record was inserted?
0
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)
0
momi_sabagCommented:
well,
what happens if you cancel the operation? does the record remain in the database or is it deleted?
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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?.
0
momi_sabagCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?.
0
momi_sabagCommented:
you first need to find out if the process is connected to the database when it is blocked or not
0
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.