db2/as400 load data in excel sheet

[Manoj8690]

Hi Folks,

Can anyone help me to set connection with DB2/AS400 data base and load data in excel sheet.

Thanks,

Manoj

[Wamboo]

Hello,

In my opinion, what you need is to download the ODBC driver -> configure it to your operating system env and use it inside TC.

I already did it for Oracle DB and wrote some tutorials about it:

check this out:
https://community.smartbear.com/t5/TestComplete-Desktop-Testing/Connect-TestComplete-to-Oracle-datab...

https://community.smartbear.com/t5/TestComplete-Desktop-Testing/Connect-TestComplete-to-Oracle-datab...

The installation of the driver will be different but ODBP options are the same.

and with excel... check the sample application in default TestComplete installation:

C:\Users\Public\Documents\TestComplete 14 Samples

there is a very good example.

[BenoitB]

On DB2/AS400 it's not standard connection.

Some prerequesites are mandatory on the AS400 depending the kind of connection.

And you need iseries odbc access driver  (need to be registered to IBM)

https://www.ibm.com/support/pages/odbc-driver-ibm-i-access-client-solutions

Some other infos here:

https://www.nicolas-housset.fr/installer-le-pilote-odbc-pour-db2/

You can also use a third party tool allowing you to make software (and not driver) gateway.

https://stackoverflow.com/questions/20634843/configure-squirrel-sql-client-to-work-for-db2-as400

[sonya_m]

Thanks for help Benoit, Wamboo!

@Manoj8690 please let us know how you solved this and mark the most helpful reply as a solution!

[Manoj8690 · Accepted solution]

Thanks Everyone for your suggestion!!

Now I am able to fetch details from Database with below code.

But, could not able to store in excel... Can anyone help me for copy/save data from recordset to excel.

Sub TestADO
Dim Conn, Rs , strSql
Const adOpenStatic = 0
Const adLockOptimistic = -1
Const adCmdText = "&H0001"

Set Conn = CreateObject("ADODB.Connection")
strSql = "SELECT * from TableName LIMIT 20"

Conn.ConnectionString = "Provider=MSDASQL.1;" & "Persist Security Info=False;" & "User ID=NA;" & "Extended Properties= " & "DSN=NA;SYSTEM=NA;UID=NA;DBQ=NA;DFTPKGLIB=NA;LANGUAGEID=ENU;PKG=NA/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;"

Conn.Open
' Create a recordset
Set Rs = CreateObject("ADODB.Recordset")
' Open the recordset and get the Orders table from the OrdersDB database

'Rs.Open strSql, Conn, 3, 1, 2 ' adOpenStatic, adLockReadOnly, adCmdTable

Rs.Open strSql, Conn, adOpenStatic, adLockReadOnly, adCmdText

Log.AppendFolder "Customer names"
Rs.MoveFirst
While Not Rs.EOF
Log.Message Rs.Fields.Item("NA").Value
Rs.MoveNext
WEnd
Rs.Close
Conn.Close
End Sub

[BenoitB]

Great !

I suggest you to create a new post about this subject on Excel .. but if you search a little into the forum i think solution is already written 😉