Connecting to Teradata DSN

Kurto2021 · January 23, 2015

I have been able to connect to our teradata sever in MS Access and dump results into a text file. I wanted to be able to pull this off from an exe instead of access so I fired up AutoIt. I am pretty sure I am not connecting to the server because I replaced the Connection String with random garbage and got the same results.

Question 1 how do I verify I have connected to the database

Question 2 how do I actually connect to the database because I am evidently wrong

Here is the code I have in autoit which is pretty much a straight rip from the wiki page

#include <array.au3>
#include <file.au3>


Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 2 ; adCmdTable
Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sConnectionString = "DSN=Production Teradata;uid=Axxxxx;pwd=Axxxxxx"
$oConnection.Open($sConnectionString) ; Open the connection
Global $sSQL = "select top 10  job_cde as job, count(job_cde) as job_total, job_ttl_nm as job_count from PRODBBYMEADHOCVWS.ACTIVE_EMPLOYEE_DETAIL_CURR where rtl_loc_flg = 'Y' group by job_cde,  job_ttl_nm" ; Select all records and all fields
$oRecordset.Open($sSQL, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
With $oRecordset
    While Not .EOF ; repeat until End-Of-File (EOF) is reached
        ; Write the content of all fields to the console separated by | by processing the fields collection
        ConsoleWrite("Process the fields collection:     ")
        For $oField In .Fields
            ConsoleWrite($oField.Value & "|")
        Next
        ConsoleWrite(@CR)
        ; Write a second line by accessing all fields of the collection by item number
        ConsoleWrite("Process the fields by item number: " & .Fields(0).Value & "|" & .Fields(1).Value & "|" & .Fields(2).Value & "|" & @CR)
        .MoveNext ; Move To the Next record
    WEnd
EndWith
$oRecordset.Close ; Close the recordset
$oRecordset = 0 ; Release the connection object
$oConnection.Close ; Close the connection
$oConnection = 0 ; Release the connection object

For comparison sake here is the working vba code from access

Private Sub Command0_Click()


Dim cnt As Object
Dim rst As Object
Dim stSQL As String


Const stCon As String = "DSN=Production Teradata;Uid=axxxxxx; Pwd=axxxxxx;"
Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
cnt.ConnectionString = stCon
cnt.Open


Set fso = CreateObject("Scripting.FileSystemObject")
Set txtFile = fso.CreateTextFile("C:\temp\Temptext.txt", True)


stSQL = "select top 10 job_cde as job, count(job_cde) as job_total, job_ttl_nm as job_count from PRODBBYMEADHOCVWS.ACTIVE_EMPLOYEE_DETAIL_CURR where rtl_loc_flg = 'Y' group by job_cde,  job_ttl_nm"
rst.Open stSQL, cnt, adOpenDynamic


If rst.RecordCount > 0 Or Not rst.EOF Then
    rst.MoveFirst
    Do Until rst.EOF = True
       txtFile.Write (rst![job] & "," & rst![job_total] & "," & rst![job_count] & vbCrLf)
       rst.MoveNext
    Loop
    rst.Close
    cnt.Close
End If
End Sub

Edited January 23, 2015 by Kurto2021

Xenobiologist · January 23, 2015

Haven't looked at your code. But for the connection string have a look here : https://www.connectionstrings.com/teradata/

And for the access have a look at this DB2 example

#include <Date.au3>
; Global Variables
Global $adUseServer = 2
Global $adUseClient = 3

; Initialize COM error handler
Global $oMyError = ObjEvent('AutoIt.Error', 'MyErrFunc')
Global $provider = 'IBMDADB2', $IP, $port, $userID, $password, $connection_Obj

;~ Global $DSN = 'DSPZ4988'
;~ Global $DSN = 'DSPP1102'
;~ Global $DSN = 'DSPP6011'
;~ Global $DSN = 'DSPI4962'
;~ Global $DSN = 'DSPI4845'
;~ Global $DSN = 'DSPS4964'
;~ Global $DSN = 'DSPW4937'
;~ Global $DSN = 'DSPP4924'
Global $DSN = 'DSPP6011'

If StringInStr($DSN, 'DSPP') <> 0 Then
    $userID = 'db2read'
    $password = 'xxx'
Else
    $userID = 'xgcoge1'
    $password = 'xxx'
EndIf

$connection_Obj = _connectDB($provider, $IP, $port, $DSN, $userID, $password)
If $connection_Obj = -1 Then Exit (1)

;~ Local $sqlRs = ObjCreate('ADODB.Recordset')
;~ _displayTable($connection_Obj, 'SELECT * FROM "DSPTSPT"."DIM_ZEIT"')

While 1
    Local $sqlRs = ObjCreate('ADODB.Recordset')

    If Not @error Then
        $sqlRs.open('SELECT TXT_SCHLUESSEL_ERLAEUTERUNG FROM "DSPTMCP"."WERTEBEREICHE_DEFINITION" WHERE "BEZ_SCHLUESSEL" = ''TSP_ETL3_RUN''', $connection_Obj)
;~      $sqlRs.open('SELECT max(ID_ZEIT) FROM "DSPTSPT"."DIM_ZEIT"', $connection_Obj)
        If Not @error Then
            $re_A = $sqlRs.GetRows()
            If $re_A[0][0] <> 'Transformer läuft...' Then
                MsgBox(64, 'Transformer', 'Transformer beendet um : ' & $re_A[0][0])
                $connection_Obj.close
                Exit (0)
            EndIf
            ConsoleWrite($re_A[0][0] & ': ' & _Now() & @CRLF)
            $sqlRs.close
        EndIf
    EndIf

    Sleep(10000 * 3)
WEnd
;~ _displayTable($connection_Obj, 'SELECT TXT_SCHLUESSEL_ERLAEUTERUNG FROM "DSPTMCP"."WERTEBEREICHE_DEFINITION" WHERE "BEZ_SCHLUESSEL" = ''TSP_ETL3_RUN''')


Func _connectDB($provider, $IP, $port, $DSN, $userID, $password)
    Local $sqlCon = ObjCreate('ADODB.Connection')
;~  $sqlCon.Mode = 16 ; Erlaubt im MultiUser-Bereich das öffnen anderer Verbindungen ohne Beschränkungen [Lesen/Schreiben/Beides]
    $sqlCon.Mode = 0 ; Erlaubt im MultiUser-Bereich das öffnen anderer Verbindungen ohne Beschränkungen [Lesen/Schreiben/Beides]
    $sqlCon.CursorLocation = 2;$adUseClient ; client side cursor Schreiben beim Clienten

;~  $sqlCon.Open('Provider=' & $provider & ';IP=' & $IP & ';Port=' & $port & ';DSN=' & $DSN & ';User ID=' & $userID & ';Password=' & $password) ; XP
    $sqlCon.Open('Provider=' & $provider & ';Server=' & $IP & ':' & $port & ';DSN=' & $DSN & ';UID=' & $userID & ';PWD=' & $password) ; win 7
    If @error Then Return -1
    Return $sqlCon
EndFunc   ;==>_connectDB

Func _getColumns($sqlCon, $SQL)
    Local $sqlRs = ObjCreate('ADODB.Recordset')
    If Not @error Then
        $sqlRs.open($SQL, $sqlCon)
        If Not @error Then
            For $i = 0 To $sqlRs.Fields.Count - 1
                ConsoleWrite($sqlRs.Fields($i).Name & @CRLF)
            Next
            $sqlRs.close
        EndIf
    EndIf
EndFunc   ;==>_getColumns

Func _displayTable($sqlCon, $SQL)
    Local $sqlRs = ObjCreate('ADODB.Recordset')
    If Not @error Then
        $sqlRs.open($SQL, $sqlCon)
        If Not @error Then
            Local $header[$sqlRs.Fields.Count], $rows
            For $i = 0 To $sqlRs.Fields.Count - 1
                $header[$i] = $sqlRs.Fields($i).Name
            Next
            $rows = $sqlRs.GetRows()
            $sqlRs.close
        EndIf
    EndIf
    _ArrayDisplay_WithHeader($rows, $header)
EndFunc   ;==>_displayTable

Func MyErrFunc()
    $HexNumber = Hex($oMyError.number, 8)
    MsgBox(0, 'COM Test', 'We intercepted a COM Error !' & @CRLF & @CRLF & _
            'err.description is: ' & @TAB & $oMyError.description & @CRLF & _
            'err.windescription:' & @TAB & $oMyError.windescription & @CRLF & _
            'err.number is: ' & @TAB & $HexNumber & @CRLF & _
            'err.lastdllerror is: ' & @TAB & $oMyError.lastdllerror & @CRLF & _
            'err.scriptline is: ' & @TAB & $oMyError.scriptline & @CRLF & _
            'err.source is: ' & @TAB & $oMyError.source & @CRLF & _
            'err.helpfile is: ' & @TAB & $oMyError.helpfile & @CRLF & _
            'err.helpcontext is: ' & @TAB & $oMyError.helpcontext _
            )
    SetError(1) ; to check for after this function returns
EndFunc   ;==>MyErrFunc

;~ _executeSQLonDB2Recordset($Obj, "insert into " & "Autoit" & "values('Xenobiologist', 'User')")

Func _executeSQLonDB2Recordset($sqlCon, $SQL)
    Local $sqlRs = ObjCreate('ADODB.Recordset')
    $sqlRs.open('SELECT * FROM Autoit', $sqlCon)
    If Not @error Then
        $sqlRs.Source = "Select * From Autoit" ; ganze Tabelle
        $sqlRs.AddNew ;'Neuen Datensatz erzeugen
;~   'Beispiele für Zuweisung von Werten an Tabellenfelder
        $sqlRs.Fields("Name") = "MEGA"
        $sqlRs.Fields("Job") = "MEGAMAN"
        $sqlRs.Update
        $sqlRs.close
    EndIf
EndFunc   ;==>_executeSQLonDB2Recordset


;$OptionVal = $sqlRs.Fields ('LAST_NAME' ).Value
;~          MsgBox(0, 'Record Found', 'Name:  ' & $OptionName );& @CRLF & 'Value:  ' & $OptionVal)
;~          $sqlRs.FIELDS(''' & $OptionName & ''') = '.F.' ; ADDED THIS LINE
; $sqlRs.Update  ; ADDED THIS LINE
;~ Driver=IBMDADB2

;~ _getColumns($connection_Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"')

;~ _displayTable($connection_Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"')
;~ _getColumns($connection_Obj, 'update "BI1TSNR"."AGENTURMANDANT" set "SL_BATCH_STATUS"=39 where "NR_MANDANT"=CAST(10300201 AS INTEGER)')
;~ _displayTable($connection_Obj, 'SELECT * FROM "DSPTXMC"."DIM_KNZ_GRUPPE"')

;~  _SQL_Execute($connection_Obj, "insert into DSPTXMC.FAKT_KENNZAHLEN values ('501', '2002-12-31-12.00.00.000000', '2010-11-20-13.39.13.000000', '4983', '2500000', NULL, '22.0', NULL)")
;~ _displayTable($connection_Obj, 'SELECT * FROM "DSPTMCP"."WERTEBEREICHE_DEFINITION" WHERE "BEZ_SCHLUESSEL" = ''TSP_ETL3_RUN''')
;~ _getColumns($connection_Obj, 'SELECT * FROM "DSPTMCP"."WERTEBEREICHE_DEFINITION" WHERE "BEZ_SCHLUESSEL" = ''TSP_ETL3_RUN''')

Func _ArrayDisplay_WithHeader(Const ByRef $avArray, $header, $sTitle = "Array: ListView Display", $iItemLimit = -1, $iTranspose = 0, $sSeparator = "", $sReplace = "|")
    If Not IsArray($avArray) Then Return SetError(1, 0, 0)

    ; Dimension checking
    Local $iDimension = UBound($avArray, 0), $iUBound = UBound($avArray, 1) - 1, $iSubMax = UBound($avArray, 2) - 1
    If $iDimension > 2 Then Return SetError(2, 0, 0)

    ; Separator handling
;~  If $sSeparator = "" Then $sSeparator = Chr(1)
    If $sSeparator = "" Then $sSeparator = Chr(124)

    ; Declare variables
    Local $i, $j, $vTmp, $aItem, $avArrayText, $sHeader = "Row", $iBuffer = 64
    Local $iColLimit = 250, $iLVIAddUDFThreshold = 4000, $iWidth = 640, $iHeight = 480
    Local $iOnEventMode = Opt("GUIOnEventMode", 0), $sDataSeparatorChar = Opt("GUIDataSeparatorChar", $sSeparator)

    ; Swap dimensions if transposing
    If $iSubMax < 0 Then $iSubMax = 0
    If $iTranspose Then
        $vTmp = $iUBound
        $iUBound = $iSubMax
        $iSubMax = $vTmp
    EndIf

    ; Set limits for dimensions
    If $iSubMax > $iColLimit Then $iSubMax = $iColLimit
    If $iItemLimit = 1 Then $iItemLimit = $iLVIAddUDFThreshold
    If $iItemLimit < 1 Then $iItemLimit = $iUBound
    If $iUBound > $iItemLimit Then $iUBound = $iItemLimit
    If $iLVIAddUDFThreshold > $iUBound Then $iLVIAddUDFThreshold = $iUBound

    ; Set header up
    For $i = 0 To UBound($header) - 1
        $sHeader &= $sSeparator & $header[$i]
    Next

    ; Convert array into text for listview
    Local $avArrayText[$iUBound + 1]
    For $i = 0 To $iUBound
        $avArrayText[$i] = "[" & $i & "]"
        For $j = 0 To $iSubMax
            ; Get current item
            If $iDimension = 1 Then
                If $iTranspose Then
                    $vTmp = $avArray[$j]
                Else
                    $vTmp = $avArray[$i]
                EndIf
            Else
                If $iTranspose Then
                    $vTmp = $avArray[$j][$i]
                Else
                    $vTmp = $avArray[$i][$j]
                EndIf
            EndIf

            ; Add to text array
            $vTmp = StringReplace($vTmp, $sSeparator, $sReplace, 0, 1)
            $avArrayText[$i] &= $sSeparator & $vTmp

            ; Set max buffer size
            $vTmp = StringLen($vTmp)
            If $vTmp > $iBuffer Then $iBuffer = $vTmp
        Next
    Next
    $iBuffer += 1

    ; GUI Constants
    Local Const $_ARRAYCONSTANT_GUI_DOCKBORDERS = 0x66
    Local Const $_ARRAYCONSTANT_GUI_DOCKBOTTOM = 0x40
    Local Const $_ARRAYCONSTANT_GUI_DOCKHEIGHT = 0x0200
    Local Const $_ARRAYCONSTANT_GUI_DOCKLEFT = 0x2
    Local Const $_ARRAYCONSTANT_GUI_DOCKRIGHT = 0x4
    Local Const $_ARRAYCONSTANT_GUI_EVENT_CLOSE = -3
    Local Const $_ARRAYCONSTANT_LVIF_PARAM = 0x4
    Local Const $_ARRAYCONSTANT_LVIF_TEXT = 0x1
    Local Const $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH = (0x1000 + 29)
    Local Const $_ARRAYCONSTANT_LVM_GETITEMCOUNT = (0x1000 + 4)
    Local Const $_ARRAYCONSTANT_LVM_GETITEMSTATE = (0x1000 + 44)
    Local Const $_ARRAYCONSTANT_LVM_INSERTITEMA = (0x1000 + 7)
    Local Const $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE = (0x1000 + 54)
    Local Const $_ARRAYCONSTANT_LVM_SETITEMA = (0x1000 + 6)
    Local Const $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT = 0x20
    Local Const $_ARRAYCONSTANT_LVS_EX_GRIDLINES = 0x1
    Local Const $_ARRAYCONSTANT_LVS_SHOWSELALWAYS = 0x8
    Local Const $_ARRAYCONSTANT_WS_EX_CLIENTEDGE = 0x0200
    Local Const $_ARRAYCONSTANT_WS_MAXIMIZEBOX = 0x00010000
    Local Const $_ARRAYCONSTANT_WS_MINIMIZEBOX = 0x00020000
    Local Const $_ARRAYCONSTANT_WS_SIZEBOX = 0x00040000
    Local Const $_ARRAYCONSTANT_tagLVITEM = "int Mask;int Item;int SubItem;int State;int StateMask;ptr Text;int TextMax;int Image;int Param;int Indent;int GroupID;int Columns;ptr pColumns"

    Local $iAddMask = BitOR($_ARRAYCONSTANT_LVIF_TEXT, $_ARRAYCONSTANT_LVIF_PARAM)
    Local $tBuffer = DllStructCreate("char Text[" & $iBuffer & "]"), $pBuffer = DllStructGetPtr($tBuffer)
    Local $tItem = DllStructCreate($_ARRAYCONSTANT_tagLVITEM), $pItem = DllStructGetPtr($tItem)
    DllStructSetData($tItem, "Param", 0)
    DllStructSetData($tItem, "Text", $pBuffer)
    DllStructSetData($tItem, "TextMax", $iBuffer)

    ; Set interface up
    Local $hGUI = GUICreate($sTitle, $iWidth, $iHeight, 10, 10, BitOR($_ARRAYCONSTANT_WS_SIZEBOX, $_ARRAYCONSTANT_WS_MINIMIZEBOX, $_ARRAYCONSTANT_WS_MAXIMIZEBOX))
    Local $aiGUISize = WinGetClientSize($hGUI)
    Local $hListView = GUICtrlCreateListView($sHeader, 0, 0, $aiGUISize[0], $aiGUISize[1] - 26, $_ARRAYCONSTANT_LVS_SHOWSELALWAYS)
    Local $hCopy = GUICtrlCreateButton("Copy Selected", 3, $aiGUISize[1] - 23, $aiGUISize[0] - 6, 20)
    GUICtrlSetResizing($hListView, $_ARRAYCONSTANT_GUI_DOCKBORDERS)
    GUICtrlSetResizing($hCopy, $_ARRAYCONSTANT_GUI_DOCKLEFT + $_ARRAYCONSTANT_GUI_DOCKRIGHT + $_ARRAYCONSTANT_GUI_DOCKBOTTOM + $_ARRAYCONSTANT_GUI_DOCKHEIGHT)
    GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_GRIDLINES, $_ARRAYCONSTANT_LVS_EX_GRIDLINES)
    GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT)
    GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE)

    ; Fill listview
    For $i = 0 To $iLVIAddUDFThreshold
        GUICtrlCreateListViewItem($avArrayText[$i], $hListView)
    Next
    For $i = ($iLVIAddUDFThreshold + 1) To $iUBound
        $aItem = StringSplit($avArrayText[$i], $sSeparator)
        DllStructSetData($tBuffer, "Text", $aItem[1])

        ; Add listview item
        DllStructSetData($tItem, "Item", $i)
        DllStructSetData($tItem, "SubItem", 0)
        DllStructSetData($tItem, "Mask", $iAddMask)
        GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_INSERTITEMA, 0, $pItem)

        ; Set listview subitem text
        DllStructSetData($tItem, "Mask", $_ARRAYCONSTANT_LVIF_TEXT)
        For $j = 2 To $aItem[0]
            DllStructSetData($tBuffer, "Text", $aItem[$j])
            DllStructSetData($tItem, "SubItem", $j - 1)
            GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETITEMA, 0, $pItem)
        Next
    Next

    ; ajust window width
    $iWidth = 0
    For $i = 0 To $iSubMax + 1
        $iWidth += GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH, $i, 0)
    Next
    If $iWidth < 250 Then $iWidth = 230
    WinMove($hGUI, "", Default, Default, $iWidth + 20)

    ; Show dialog
    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $_ARRAYCONSTANT_GUI_EVENT_CLOSE
                ExitLoop

            Case $hCopy
                Local $sClip = ""

                ; Get selected indices [ _GUICtrlListView_GetSelectedIndices($hListView, True) ]
                Local $aiCurItems[1] = [0]
                For $i = 0 To GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMCOUNT, 0, 0)
                    If GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMSTATE, $i, 0x2) Then
                        $aiCurItems[0] += 1
                        ReDim $aiCurItems[$aiCurItems[0] + 1]
                        $aiCurItems[$aiCurItems[0]] = $i
                    EndIf
                Next

                ; Generate clipboard text
                If Not $aiCurItems[0] Then
                    For $sItem In $avArrayText
                        $sClip &= $sItem & @CRLF
                    Next
                Else
                    For $i = 1 To UBound($aiCurItems) - 1
                        $sClip &= $avArrayText[$aiCurItems[$i]] & @CRLF
                    Next
                EndIf
                ClipPut($sClip)
        EndSwitch
    WEnd
    GUIDelete($hGUI)

    Opt("GUIOnEventMode", $iOnEventMode)
    Opt("GUIDataSeparatorChar", $sDataSeparatorChar)

    Return 1
EndFunc   ;==>_ArrayDisplay_WithHeader

;~ _printDB2Recordset($Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"')

;~ Func _printDB2Recordset($sqlCon, $SQL)
;~  Local $sqlRs = ObjCreate('ADODB.Recordset')
;~  If Not @error Then
;~      $sqlRs.open($SQL, $sqlCon)
;~      If Not @error Then
;~          ;Loop until the end of file
;~          While Not $sqlRs.EOF
;~              ;Retrieve data from the following fields
;~              ConsoleWrite($sqlRs.Fields('Nr_Mandant' ).Value & @CRLF)
;~              ConsoleWrite($sqlRs.Fields(1).Value & @CRLF)
;~              ConsoleWrite($sqlRs.Fields('COUNTRY' ).Value & @CRLF)
;~              $sqlRs.MoveNext
;~          WEnd
;~          $sqlRs.close
;~      EndIf
;~  EndIf
;~ EndFunc   ;==>_printDB2Recordset

Kurto2021 · January 23, 2015

you realize I said I have it working in access so one would assume my connection string is fine

Kurto2021 · January 25, 2015

I solved it. For those looking for an answer if they ever encounter this I changed my method for opening the recordset

I changed this

$oRecordset.Open($sSQL, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

to

$oRecordset = $oConnection.Execute ($sSQL)
$oRecordset.cursortype = 3

This is the complete code below

#include <array.au3>
#include <file.au3>


Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 2 ; adCmdTable
Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sConnectionString = "DSN=Production Teradata;uid=a870631;pwd=a870631"
$oConnection.Open($sConnectionString) ; Open the connection
Global $sSQL = "select top 10  job_cde as job, count(job_cde) as job_total, job_ttl_nm as job_count from PRODBBYMEADHOCVWS.ACTIVE_EMPLOYEE_DETAIL_CURR where rtl_loc_flg = 'Y' group by job_cde,  job_ttl_nm" ; Select all records and all fields
$oRecordset = $oConnection.Execute ($sSQL)
$oRecordset.cursortype = 3
With $oRecordset
    While Not .EOF ; repeat until End-Of-File (EOF) is reached
        ; Write the content of all fields to the console separated by | by processing the fields collection
        ConsoleWrite("Process the fields collection:     ")
        For $oField In .Fields
            ConsoleWrite($oField.Value & "|")
        Next
        ConsoleWrite(@CR)
        ; Write a second line by accessing all fields of the collection by item number
        ConsoleWrite("Process the fields by item number: " & .Fields(0).Value & "|" & .Fields(1).Value & "|" & .Fields(2).Value & "|" & @CR)
        .MoveNext ; Move To the Next record
    WEnd
EndWith
$oRecordset.Close ; Close the recordset
$oRecordset = 0 ; Release the connection object
$oConnection.Close ; Close the connection
$oConnection = 0 ; Release the connection object

stim100986 · July 30, 2015

OK, I give.

I am trying to do something simpler than this, took the code that seems to have worked here and made my minor changes to it:

#include <array.au3>
#include <file.au3>

Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 2 ; adCmdTable
Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sConnectionString = "DSN=myDSN;uid=myuserid;pwd=mypassword"
$oConnection.Open($sConnectionString) ; Open the connection
Global $sSQL = "select stuff from table where things"

$oRecordset = $oConnection.Execute ($sSQL)
$oRecordset.cursortype = 3
With $oRecordset
    While Not .EOF ; repeat until End-Of-File (EOF) is reached
       ConsoleWrite("Process the fields collection:     ")
        For $oField In .Fields
            ConsoleWrite($oField.Value & "|")
        Next
        ConsoleWrite(@CR)
        ConsoleWrite("Process the fields by item number: " & .Fields(0).Value & "|" & .Fields(1).Value & "|" & .Fields(2).Value & "|" & @CR)
        .MoveNext ; Move To the Next record
    WEnd
EndWith
$oRecordset.Close ; Close the recordset
$oRecordset = 0 ; Release the connection object
$oConnection.Close ; Close the connection
$oConnection = 0 ; Release the connection object

and after what appears to be enough time for the select statement to complete, I get:

(My Directory) (13) : ==> The requested action with this object has failed.:
$oRecordset = $oConnection.Execute ($sSQL)
$oRecordset = $oConnection.Execute ($sSQL)^ ERROR

What am I missing???

Sign In

Connecting to Teradata DSN

Recommended Posts

Kurto2021

Xenobiologist

Kurto2021

Kurto2021

stim100986

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta