Create ODBC connection ?

[Jango]

Hello,

I don't know if it's possible but i'm looking for a way to create ODBC connection with AutoIt.

I can find something relevant ... if someone have information about that please help

[BrettF]

  Jango said:

Hello,

I don't know if it's possible but i'm looking for a way to create ODBC connection with AutoIt.

I can find something relevant ... if someone have information about that please help

Have you done a forum search of ODBC?

[Jango]

  Bert said:

Have you done a forum search of ODBC?

Yes but to be clear i'm looking for a way to automate the creation of an ODBC connection not to use ODBC connection in my script ... i don't know if i'm clear as english is not my native language.

[ptrex]

@Jango

Why do you need an ODBC setup at the client ?

Much better is to use the DNS less connections. Works without a ODBC setup.

Which DB are you trying to connect to ?

regards

ptrex

[Jango]

  ptrex said:

@Jango

Why do you need an ODBC setup at the client ?

Much better is to use the DNS less connections. Works without a ODBC setup.

Which DB are you trying to connect to ?

regards

ptrex

@ptrex

In fact i made a script to install SQL Server Package (on the server) and execute it, these package use ODBC connection to connect to another DB (IBM AS400 DB) and actually i log on the server and create the ODBC connection manually (Settings/Control Panel/Administrative Tools/ODBC) but i would like to automate this process as i have many server to install

Edit: it's Microsoft SQL Server 2005

Edited February 11, 2008 by Jango

[ptrex]

@Joango

This should get you connected to an DB2 / AS 400 without an ODBC.

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

Not tested it because I don't have an AS400 around.

Regards

ptrex

[ptrex]

@Jango

This one is for MS SQL.

; Only for SQL server
$objConn = ObjCreate("ADODB.Connection")
$objConn.Open("Provider='sqloledb';Data Source='mysqlserver';Initial Catalog='Northwind';User ID='sa';Password='password';")
$rsCustomers = $objConn.Execute("SELECT * FROM Customers")
With $rsCustomers
    While Not .EOF
        ConsoleWrite(.Fields("CustomerID").Value & " - " & .Fields("CompanyName").Value & @LF)
        .MoveNext
    WEnd
    .Close
EndWith
$objConn.Close

Regards

ptrex

[Jango]

thank you for these script ptrex, i saved it

But i can't change the way SQL package access the DB and it need an ODBC connection setup so i think i need to poke in the registry to do that...

[ptrex]

@Jango,

Don't worry, here's one in VBScript that can easily be translated in AU3.

ODBC DSN

regards

ptrex

[Jango]

  ptrex said:

@Jango,

Don't worry, here's one in VBScript that can easily be translated in AU3.

ODBC DSN

regards

ptrex

Great this is i was looking for !