0

I'm fetching query results from a DataBase using this vbs-code:

strSQL = "select * from EMP"
Set rsDB = objDBConn.Execute(strSQL)

Now I want to get this result exported to a txt-file.

How can I achieve this with qtp?

M463
  • 2,003
  • 3
  • 23
  • 39
rookie
  • 401
  • 3
  • 13
  • 29

2 Answers2

1

Use .GetString(), somewhat like:

Set rsDB = objDBConn.Execute(strSQL)
tsOut.Write rsDB.GetString(adClipString, , ",", vbCrLf, "Null")

(Obviously you need an open Textstream and the Const definition of adClipString)

Update wrt comment:

Sample code

Community
  • 1
  • 1
Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
1
set database = createobject ("ADODB.connection")
connectionString = "Driver={SQL Server};Server=" + sql_server + ";Uid=" + sql_user + ";Pwd=" + sql_password + ";" 
database.ConnectionString = connectionString
database.Open

sql = "select * from [testdb].[dbo].[table1]"
set resultset = database.Execute(sql)

Set objFSO=CreateObject("Scripting.FileSystemObject")
outFile="c:\output.txt"
Set objFile = objFSO.CreateTextFile(outFile,True)

Dim row, first_field
while not resultset.EOF

    row = ""
    first_field = true

    For Each field In resultset.Fields
        if (first_field = true) then
            row = field
            first_field = false
        else
            row = row & ";" & field
        end if
    Next ' field

    objFile.Write row & vbCrLf

    resultset.MoveNext
wend

objFile.Close

If you have fixed number of columns:
Use CStr(resultset(0)) to get the separate columns from the resultset. the number is the number of the column, starting with Zero.
If you don't know how many columns use the For Each Loop as shown in the code above.

(A plain example for read/write txt File with vbscript: https://stackoverflow.com/a/2198973/4654008)

Community
  • 1
  • 1
Thomas
  • 126
  • 1
  • 8
  • 1
    HI thanks for your answer. If I don't know how many number of column is available how can I write all the values in the same line. In you code I can just add (resultset(2) (3) .. but I don't want to do it manually.. – rookie Aug 06 '15 at 08:12
  • 1
    @rookie: I edited the answer and added a For Each-Loop. This will dynamically go through the fields of the resultset. – Thomas Aug 06 '15 at 08:44