JDBC request with external driver DB2/AS400

Question

Hello,&nbsp;I have to connect Testcomplete project with Db2/AS400 database using JDBC request. It requires a driver so I copied the jt400 jar in the 'bin' folder of TestComplete. I'm using the following to establish the connection between the TestComplete project and DB2/AS400:&nbsp;&nbsp;var &nbsp;conn &nbsp;= ADO.CreateADOConnection();

conn.ConnectionString =
"Driver =jt400.jar;"+
"Data Source =ussys;" +
"CurrentSchema=USER;" +
"User ID=XXXXXXX;" + 
"Password=XXXXXXX;"conn.LoginPrompt = false; conn.Open();&nbsp;I see a runtime error: OLE 80040E4D&nbsp;I think it's related to the Connection string, but I'm not able to find out what's wrong.&nbsp;Could someone please&nbsp;help me understand what's missing in the connection string or anything else?&nbsp;Thank youAbhi&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

alexkaras · Accepted Answer

Hi Abhi,
&nbsp;
JDBC and .jar files is Java-related stuff. You need to find regular Windows 32-bit ODBC drivers (or OLE providers)&nbsp;(because TestComplete is 32-bit application) for DB2/AS400 and install them. Whether or not will these drivers require Java depends on their implementation and I hope that their respective installers will take care about this.
Than you will have to create DSN,&nbsp;referencing the database that you are going to use,&nbsp;using ODBC Administrator and use this DSN when specifying connection for .CreateADOConnection(). This is in case ODBC drivers are used. In case of OLE providers, no DSN is required and connection string must be provided for .CreateADOConnection().
&nbsp;
P.S. This thread (https://community.smartbear.com/t5/TestComplete-Functional-Web/Can-anyone-help-me-how-to-access-Mongo-database/td-p/86442) may help you with creation of connection string.

nandini9 · Answer

Hi Abhishek,
I didn't find&nbsp;IBMDA400 driver in my system with windows 64bit. Did you install it seperately or did you get it through OS directly?
If installed, what is the exact driver name you installed?
Kindly let me know&nbsp;
&nbsp;
Thanku you,
Nandini

alexkaras · Answer

Hi Nandini,
&nbsp;
This is a separate third-party driver that must be installed manually.
https://www.google.com/search?q=db2+as400+odbc+driver+download
&nbsp;

nandini9 · Answer

Hi Alex,
Thanks for the response. I've installed IBM DB2 driver from the below link provided. I created a new DSN with the IBM DB2 driver selected and tried to connect. I am facing an issue after clicking on connect. What is the data source name we should mention while creating the DSN?
Please refer the screenshot of the issue and suggest.
&nbsp;
Regards,
Nandini

alexkaras · Answer

Hi Nandini,
&nbsp;
Important note 1:
TestComplete and ODBC driver must be of the same bitness. 64-bit flavor of TestComplete is located in the &lt;TestComplete.\x64\bin\ folder, while 32-bit&nbsp;flavor is in the &lt;TestComplete&gt;\bin one. Pay attention to this or TestComplete (and ADO subsystem) will not be able to find your DSN and driver.
&nbsp;
Important note 2:
You must use the odbcad32.exe utility to create and configure DSN for the ODBC driver. Like TestComplete, the bitness of odbcad32 must match the bitness of the ODBC driver.&nbsp;odbcad32 must be started from the &lt;Windows&gt;\System32\ folder if you are working with 64-bit ODBC driver. If you are working with 32-bit ODBC driver, then start odbcad32 utility from the &lt;Windows&gt;\SysWOW64\ folder.
&nbsp;
Further refers the screenshot you've posted:
Use these steps to create a DSN:
-- start odbcad32 utility;
-- Switch to the User DSN tab if you like to create a DSN that is accessible to your account only. Switch to the System DSN tab if you like to create a DSN that is accessible to any user logged into the given computer. Note, that in the latter case odbcad32 must be started with the elevated privileges (Run As Administrator);
-- Click the Add... button, provide whatever name for the DSN and select the appropriate ODBC driver (DB2/AS400 in your case);
-- Select created DSN in the list, click the Configure... button and provide parameters required by the driver;
-- Click Test button to test the connection. Test must succeed.
&nbsp;
Succeeded connection test means that the DSN exists and is properly configured.
Now you must craft the OLE DB connection string that you will provide to the ADO subsystem&nbsp;which is used by TestComplete to access databases. https://www.connectionstrings.com/as-400/ provides examples of AS400 connection strings.
https://community.smartbear.com/t5/TestComplete-Functional-Web/Can-anyone-help-me-how-to-access-Mongo-database/m-p/86460#M20415 may be used as a possible way to create and verify ADO connection string.
Note: If you installed OLE DB provider instead of ODBC driver, there is no need to create and configure DSN then. Only OLE DB connection string must be created in this case.
&nbsp;

nandini9 · Answer

Hi Alex,
Please refer the following steps which I followed:
1. Downloaded and installed&nbsp;v9.5fp10_ntx64_odbc_cli driver
2. Testcomplete which I use is 64bit, so to create a DSN, I've navigated to
&lt;Windows&gt;\System32\&nbsp;
3. Started odbcad32 utility with&nbsp;the elevated privileges (Run As Administrator);
4. Opened System DSN --&gt; Add --&gt;Selected IBM Data Server Driver
5. Entered Datasourcename as 'GIASDTADEV' --&gt; Add--&gt; given the username and password
6. After creating the DSN, clicked on configure and connect.
7. I am seeing the error attached in the screenshot1
8. So, I've edited the confg file manually. Attached even this file for reference.
9. I am not knowing what went wrong that is producing this error. Kindly , provide me a soultion

Forum Discussion

JDBC request with external driver DB2/AS400

7 Replies

Related Content

db2/as400 load data in excel sheet

Saving request and response to an external file for "JDBC Request" and "Groovy Scripts"

Display requests sent from external client to mock service

API call in test | aqHttp | Request Library | Test Complete | External Python Libraries

Need help in writing content of request (selected attributes) to external XML file.

Recent Discussions

Object Browser does not display WPF DataGridCell Border

Issue with retrieving text from listbox items in desktop win32 application

Read data from Excel

"Problems with the Object Spy #Desktop C#."

Searching for the mapped object "btnYes" took a long time