JDBC datasources

[NATO]

Hi, has anybody had any luck in connecting to DB2 or Apache's Derby databases? I've seen the MySQL, MS SQL, and Access data sources covered, but I'm currently living in a Java world and would love to find a UDF for DB2 connectivity!

[ptrex]

@NATO

I don't have a DB2 (AS400 or simular around) but this should get you going.

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

; http://ns7.webmasters.com/caspdoc/html/syntax_for_dsn_less_connection_strings.htm
; where [ip_address] is the IP address of the database server, [port_number] is the port for the database server, 
; [database_name] is the name of the database, 
; and [username] and [password] are the username and password required for accessing the database.

$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

Regards,

ptrex

[NATO]

Thanks!

Unfortunately, it's only throwing a COM error and stating that the data source name not found and no driver specified. I think the ODBC/CLI driver from IBM is cryptic at best. I'll work on it awhile and see what I can contribute to the community should anybody else be forced into using DB2.

If you are *really* bored, you can download a free version of DB2 to play with from IBM--or use Derby from Apache since it is pretty similar, but with less overhead.

Cheers,

Nate