db2/as400 load data in excel sheet

Hi Folks,

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

Thanks,

Manoj

Manoj8690
5 years ago
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

Wamboo
Community Hero
5 years ago
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-database-using-Progress/m-p/192007#M15541

https://community.smartbear.com/t5/TestComplete-Desktop-Testing/Connect-TestComplete-to-Oracle-database-using-Oracle-Provider/m-p/192782#M15627

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
Community Hero
5 years ago
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
  SmartBear Alumni (Retired)
  5 years ago
  Thanks for help Benoit, Wamboo!
  
  Manoj8690 please let us know how you solved this and mark the most helpful reply as a solution!
  - Manoj8690
    New Contributor
    5 years ago
    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