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

Link to comment

Share on other sites

Xenobiologist

Link to comment

Share on other sites

Kurto2021

Link to comment

Share on other sites

Kurto2021

Link to comment

Share on other sites

stim100986

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta