Autoit connect to DB2

[Xenobiologist]

Hi,

anybody who can try it? I tried some code from ptrex and other pieces from the forum. None worked.

Would be great if anybody can post a little self-explaining example of how to connect to DB2 via Autoit.

What Provider / Driver?

What information for connection string?

What system requirements? Like VB 6 Runtime or something for ADODB.Connection

Thanks in advance!

Mega

[LarryDalooza]

Just another VBSCRIPT link here...

http://www.visualbasicscript.com/m_57448/m...y_/tm.htm#57454

Lar.

[LarryDalooza]

http://www-1.ibm.com/support/docview.wss?uid=swg21168211

[Xenobiologist]

http://www-1.ibm.com/support/docview.wss?uid=swg21168211

Thanks!

I will install a local DB2 tomorrow to get it started.

Mega

[ptrex]

@all

is this helping you out.

expandcollapse popup

; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Mode = 16  ; shared
$sqlCon.CursorLocation = 3 ; client side cursor

$sqlCon.Open ("Driver={DB2}; IP=[ip_address]; Port=[port_number]; Database=[database_name]; UID=[username]; PWD=[password]")
If @error Then
    MsgBox(0, "ERROR", "Failed to connect to the database")
    Exit
EndIf

; See also Catalog "ADOX Catalog Example.au3"

$sqlRs = ObjCreate("ADODB.Recordset")
If Not @error Then
    $sqlRs.open ("select * from Table", $sqlCon)
    If Not @error Then
        ;Loop until the end of file
        While Not $sqlRs.EOF
            ;Retrieve data from the following fields
            $OptionName = $sqlRs.Fields ('name' ).Value
            $OptionVal = $sqlRs.Fields ('value' ).Value
            MsgBox(0, "Record Found", "Name:  " & $OptionName & @CRLF & "Value:  " & $OptionVal)
            $sqlRs.FIELDS('"' & $OptionName & '"') = ".F." ; ADDED THIS LINE
           ; $sqlRs.Update  ; ADDED THIS LINE
            $sqlRs.MoveNext  
        WEnd
        $sqlRs.close
    EndIf
EndIf

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

untested no DB2 around yet.

regards,

ptrex

Edited April 28, 2008 by ptrex

[Xenobiologist]

Hi,

thanks everybody!!! I got it working!

Mega

[ptrex]

@Xenobiologist

Can you share the working example for others ?

thanks

regards

ptrex

[Xenobiologist]

Hi,

it is nearly the same as you posted.

This works! But it is only a connection to DB2 (Version 9.1.3 FP 3) on localhost.

I also need to learn how to execute SQL statements.

I will try to connect to zOS and AIX soon.

expandcollapse popup

; Initialize COM error handler
Global $oMyError = ObjEvent('AutoIt.Error', 'MyErrFunc')

Global $provider = 'IBMDADB2'
Global $IP = @ComputerName
Global $port = 50000
Global $DSN = 'MEGA'
Global $userID = 'db2admin'
Global $password = 'db2admin'

Global $Obj = _connectDB2($provider, $IP, $port, $DSN, $userID, $password)
_printDB2Recordset($Obj, 'SELECT * FROM customer')

Func _connectDB2($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.CursorLocation = 3 ; client side cursor Schreiben beim Clienten

    $sqlCon.Open('Provider=' & $provider & ';IP=' & $IP & ';Port=' & $port & ';DSN=' & $DSN & ';User ID=' & $userID & ';Password=' & $password)
    If @error Then Return -1
    Return $sqlCon
EndFunc   ;==>_connectDB2

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('FIRST_NAME' ).Value & @CRLF)
                ConsoleWrite($sqlRs.Fields('COUNTRY' ).Value & @CRLF)
                $sqlRs.MoveNext
            WEnd
            $sqlRs.close
        EndIf
    EndIf
EndFunc   ;==>_printDB2Recordset

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

Mega

[Xenobiologist]

Hi,

just tested to connect to DB2 on AIX and zOS. Both worked too.

Mega

[PixelMaster]

Hi all

I've got a problem with this script, i get this error:

CLI0118E Invalid SQL syntax. SQLSTATE=37000

Has anyone a idea how to solve this? the query i use should work...

[Xenobiologist]

Have a look at this : IBM DB2

[ReFran]

"I also need to learn how to execute SQL statements."

Hi,

I just started to change some queries to AdoDB and Au3.

For getting SQL statements basicly correct I work with MSQRY32.exe (MS-Office)

Attached an example, also if it is not finaly organized.

HTH, Reinhard

expandcollapse popup

#include <GuiConstantsEx.au3>
#include <WindowsConstants.au3>
#include <ListViewConstants.au3>
#include <GuiListView.au3>

DIM $sqlDat_strg
DIM $oDB

$oDb = ObjCreate("ADODB.Connection")

;; Flexi Criterias for Queries
DIM $KsQKto="FCRS  M%"
DIM $KsQGes = "%"
DIM $KsQFact = "V6"          ;; Fact
DIM $KsQAbrMJ1="2010-02-01"  ;;AbrechnungsMonatJahr von
DIM $KsQAbrMJ2="2010-02-01"  ;;AbrechnungsMonatJahr bis


;; ist das query falsch erzeugt $rs.open ein Fehler

Sql_connect()
Sql_table() ;nur als Info
;sql_KsKtoSal()
;sql_KsKtoBuch()
;sql_KsIcKtoSal()
;sql_KsWk()
Sql_DbClose()

;;;;;;;;;;;; Task Defininition
;$strNew = Ksq_CleanSqlDat_strg($sqlDat_strg)
;$sqlDat_arr = Ksq_ShowInListview(stringsplit(stringreplace($strNew,";","|"),@cr))


;;;SQL
func Sql_connect()
    $oDb.Open("IDLDM") ;ODBC-DSN defined Database Connection
    If IsObj($oDb) = 0 Then
        MsgBox(0,"","Not connected")
    Exit
    endif
endfunc


func Sql_table()
    Const $adTables = 20  ;description at:http://www.w3schools.com/ADO/met_conn_openschema.asp#schemaenum
    local $list
    $oRec = $odb.OpenSchema($adTables)
    while not $oRec.eof
        If StringLen( $oRec("TABLE_TYPE").value) > 5 Then;; Skip the hidden internal tables
                $oRec.movenext
                ContinueLoop
        EndIf
        $list = $list & $oRec("TABLE_NAME").value & ";"
        $oRec.MoveNext
    wend
    MsgBox(0,"",$list)

EndFunc

func Sql_KsKtoSal()
    $qSel ="SELECT K00301.K003_GES, K00301.K003_K010_KTO, K00301.K003_K011_FAC, K00301.K003_SOLL_HAB_KNZ, K00301.K003_WERT_LW, K00301.K003_WERT_KW, K00301.K003_WK_BETRAG, K00301.K003_VERARB_KNZ, K00301.K003_ABR_MON_JAHR "
    $qFrom = "FROM IDLDM.IDLDB.K00301 K00301 "
    $qWhere = "WHERE (K00301.K003_K010_KTO Like '"&$KsQKto &"')" _
            &   "AND (K00301.K003_K011_FAC Like'" &$KsQFact &"')" _
            &   "AND (K00301.K003_GES Like '"&$KsQGes&"')" _
            &   "AND (K00301.K003_ABR_MON_JAHR Between {d '" &$KsQAbrMJ1 &"'} and {d '"&$KsQAbrMJ2 &"'} )"
    $sql = $qSel &@lf& $qFrom &@lf&$qWhere
    msgbox(0,"",$sql)
    $strg = Sql_GetData($sql)
    clipput($strg)
    msgbox(0,"",$strg)
endfunc

func Sql_GetData($sql)
    local $Fn, $Fv
    $rs = ObjCreate("ADODB.recordset")
    $rs.open($sql,$oDb) ;;ErrorCheck einbauen


    for $x in $rs.Fields
        $fn &= $x.name &";"
    next
    ;msgBox(0,"",$fn)

    while not $rs.EOF
        for $v in $rs.Fields
          $fv &=  $v.value &";"
          ;msgbox(0,"",$v.value)
        next
        ;msgbox(0,"",$fv)
        $rs.MoveNext
        $fv &= @Cr
    wend
    $rs.close
    $sqlDat_strg = $fn&@Cr&$fv
    ;clipput($sqlDat_strg)
    ;msgBox(0,"",$sqlDat_strg)
    return $sqlDat_strg
endfunc

func Sql_DbClose()
    $odb.close
endfunc
;;;; end sql

[PixelMaster]

Thanks for your quick response!

But i don't have access to the db server, so i cannot change something in the configs =s

How could i change my statment to get it work?

"SELECT * FROM [db- name] WHERE ([db- name].IDNR)=650510"

[Xenobiologist]

HI,

firstly try your luck without Autoit e.g. with DB2 Client to confirm that all works fine.

Then just change the settings in this example to your needs.

expandcollapse popup

; Initialize COM error handler
Global $oMyError = ObjEvent('AutoIt.Error', 'MyErrFunc')

Global $provider = 'IBMDADB2'
Global $IP = '10.64.80.4'
Global $port = 50000
Global $DSN = 'DSNE'
Global $userID = 'xf01145'
Global $password = 'test1234'

Global $Obj = _connectDB2($provider, $IP, $port, $DSN, $userID, $password)
_printDB2Recordset($Obj, "SELECT * FROM B10TMCP.KENNZAHL_GRUPPEN WHERE SL_GRUPPE_KENNZAHL='BTRK'")

Func _connectDB2($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.CursorLocation = 3 ; client side cursor Schreiben beim Clienten

    $sqlCon.Open('Provider=' & $provider & ';IP=' & $IP & ';Port=' & $port & ';DSN=' & $DSN & ';User ID=' & $userID & ';Password=' & $password)
    If @error Then Return -1
    Return $sqlCon
EndFunc ;==>_connectDB2

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('FIRST_NAME' ).Value & @CRLF)
                ConsoleWrite($sqlRs.Fields('SL_GRUPPE_KENNZAHL').Value & @CRLF)
                $sqlRs.MoveNext
            WEnd
            $sqlRs.close
        EndIf
    EndIf
EndFunc ;==>_printDB2Recordset

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

Mega

[shreeks]

HI,

firstly try your luck without Autoit e.g. with DB2 Client to confirm that all works fine.

Then just change the settings in this example to your needs.

expandcollapse popup

; Initialize COM error handler
Global $oMyError = ObjEvent('AutoIt.Error', 'MyErrFunc')

Global $provider = 'IBMDADB2'
Global $IP = '10.64.80.4'
Global $port = 50000
Global $DSN = 'DSNE'
Global $userID = 'xf01145'
Global $password = 'test1234'

Global $Obj = _connectDB2($provider, $IP, $port, $DSN, $userID, $password)
_printDB2Recordset($Obj, "SELECT * FROM B10TMCP.KENNZAHL_GRUPPEN WHERE SL_GRUPPE_KENNZAHL='BTRK'")

Func _connectDB2($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.CursorLocation = 3 ; client side cursor Schreiben beim Clienten

    $sqlCon.Open('Provider=' & $provider & ';IP=' & $IP & ';Port=' & $port & ';DSN=' & $DSN & ';User ID=' & $userID & ';Password=' & $password)
    If @error Then Return -1
    Return $sqlCon
EndFunc ;==>_connectDB2

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('FIRST_NAME' ).Value & @CRLF)
                ConsoleWrite($sqlRs.Fields('SL_GRUPPE_KENNZAHL').Value & @CRLF)
                $sqlRs.MoveNext
            WEnd
            $sqlRs.close
        EndIf
    EndIf
EndFunc ;==>_printDB2Recordset

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

Mega

Hi,

My aim is to connect to a DB2 database, execute a query and retrieve a result using AutoIt script.

Can I just copy your above code and change the DB credentials ? Is it alone fine to connect to a DB2 database and execute a query ?

If 'changing' the DB credentials is ALONE fine then PFA snap with this post. Could you please tell me where the contents(Alias, Database Type, Driver(JDBC), Database URL, Userid, Password) as stated in this snap has to be re-mapped into your code? Thanks and looking forward for your earliest reply.

[shreeks]

Hi,

it is nearly the same as you posted.

This works! But it is only a connection to DB2 (Version 9.1.3 FP 3) on localhost.

I also need to learn how to execute SQL statements.

I will try to connect to zOS and AIX soon.

expandcollapse popup

; Initialize COM error handler
Global $oMyError = ObjEvent('AutoIt.Error', 'MyErrFunc')

Global $provider = 'IBMDADB2'
Global $IP = @ComputerName
Global $port = 50000
Global $DSN = 'MEGA'
Global $userID = 'db2admin'
Global $password = 'db2admin'

Global $Obj = _connectDB2($provider, $IP, $port, $DSN, $userID, $password)
_printDB2Recordset($Obj, 'SELECT * FROM customer')

Func _connectDB2($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.CursorLocation = 3 ; client side cursor Schreiben beim Clienten

    $sqlCon.Open('Provider=' & $provider & ';IP=' & $IP & ';Port=' & $port & ';DSN=' & $DSN & ';User ID=' & $userID & ';Password=' & $password)
    If @error Then Return -1
    Return $sqlCon
EndFunc   ;==>_connectDB2

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('FIRST_NAME' ).Value & @CRLF)
                ConsoleWrite($sqlRs.Fields('COUNTRY' ).Value & @CRLF)
                $sqlRs.MoveNext
            WEnd
            $sqlRs.close
        EndIf
    EndIf
EndFunc   ;==>_printDB2Recordset

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

Mega

Hi,

My aim is to connect to a DB2 database, execute a query and retrieve a result using AutoIt script.

Can I just copy your above code and change the DB credentials ? Is it alone fine to connect to a DB2 database and execute a query ?

If 'changing' the DB credentials is ALONE fine then PFA snap with this post. Could you please tell me where the contents(Alias, Database Type, Driver(JDBC), Database URL, Userid, Password) as stated in this snap has to be re-mapped into your code? Thanks and looking forward for your earliest reply.