Need help for db connection

[vick]

Hi I run following vbscript, it runs  perfectly OK and delete record.

Set conn = CreateObject("ADODB.Connection")
conn.open "DWPCORP",test","dwpcorp1"
strSQLQuery = "Delete  branches  where branchid=97"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3

 

but i run autoit script like below it is not deleteing record even i am  getting error like "Data source name not found and no default driver specified"

Local $oConnection = ObjCreate("ADODB.Connection")
$sConnectionString="DRIVER= {Oracle in OraClient 12Home1};DBQ=oda-corp.priv;DATABASE= DWPCORP; UID=test; PWD=dwpcorp1; PORT=1521;"
$oConnection.open($sConnectionString)
$oConnection.Execute("Delete  branches  where branchid=97;")
 

 

can anyone tell me what i am making wrong here pls?

[mLipok]

Use ADO.au3 UDF 

Look in my signature.

 

[mLipok]

... or just, Use COM ERROR HANDLER 

[vick]

Hi,

i got this error when i use ADO UDF.

The specified DSN contains an architecture mismatch between the Driver and Application

My script is as follows:

 

Global $sDSN = 'dwpcorp'
Global $sDatabase = 'DWECORP'
Global $sServer = 'oda.corp.priv'
Global $sPort = '1521'
Global $sUser = 'test'
Global $sPassword = 'dwpcorp1'

; Internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

_Example1()


Func _Example1()
    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
       IF IsObj($oConnection) then

         $rs = ObjCreate( "ADODB.RecordSet" )
         $rs.open("Delete  branches b where b.branchid=97;")
      EndIf


EndFunc    ;==>_Example1

[mLipok]

Try to add:

#AutoIt3Wrapper_UseX64=N
or

#AutoIt3Wrapper_UseX64=Y
 

[vick]

sorry

same result 

[mLipok]

What do you see when you use: _ADO_GetProvidersList() 

and copy content of this REGTree: [HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI]

 

[vick]

i tried like below but do not get anything

 

$a=_ADO_GetProvidersList()
ConsoleWrite($a)

 

it shows processing but i do not see anything 

[vick]

i got this in odbc.ini. as attached file

[Jimwright]

I think a direct au3 translation of the VBScript would be

$oConn = ObjCreate("ADODB.Connection")
$oConn.Open("DWPCORP","test","dwpcorp1")
$strSQLQuery = "Delete  branches  where branchid=97"
$SQLObj = ObjCreate("ADODB.Recordset")
$SQLObj.Open ($strSQLQuery,$oConn,3,3)

But it's been a long time since i've used DSNs.  Im presuming you have a DSN of DWPCORP set up which holds the connection string ?

[vick]

I am getting this error:

 

The requested action with this object has failed.:

 

[vick]

i tried below code where i am not getting error but also query is not deleting record as well . can anyone tell me what is wrong pls?

i attached registry screenshot as well.

Global $sDSN = 'dwpcorp'
Global $sDatabase = 'DWPCORP'
Global $sServer = 'oda.corp.priv'
Global $sPort = '1521'
Global $sUser = 'test'
Global $sPassword = 'dwpcorp1'
sleep(2000)
$oConnectionObj=_MySQLConnect($sUser, $sPassword, $sDatabase, $sServer, "{Oracle in OraClient12Home1}", $sPort)
;$sQuery = "Delete  patelvi.bmcmlbranches  where branchid=417"
;_Query($oConnectionObj, $sQuery)

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{Oracle in OraClient12Home1}", $iPort)
    Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v)
    If @error or $val = "" Then
        SetError(2)
        Return 0
    EndIf
    $ObjConn = ObjCreate("ADODB.Connection")
    $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort)
    If @error Then
        SetError(1)
        Return 0
    Else
        Return $ObjConn
    EndIf
EndFunc   ;==>_MySQLConnect

 

[mLipok]

7 hours ago, vick said:

i tried like below but do not get anything

 

$a=_ADO_GetProvidersList()
ConsoleWrite($a)

 

it shows processing but i do not see anything 

This is realy very long process.

 

[mLipok]

You should wait until end.

But as I see you are using this function in a wrong way, as result is not textual so you can not just put them to ConsoleWrite.

Here you have example:

Func _Example_GetProviderList()
    Local $aResult = _ADO_GetProvidersList()
    _ArrayDisplay($aResult, 'Result for _ADO_GetProvidersList()')
EndFunc   ;==>_Example_GetProviderList

 

[mLipok]

4 hours ago, vick said:

can anyone tell me what is wrong pls?

Lack of COM Error reporting ?

And few hints:

Hint #1:
new example, which will still not work until you change few other things (look in next few hints):

Global $sDriver = '{Oracle in OraClient12Home1}'
Global $sServer = 'oda.corp.priv'
Global $sDatabase = 'DWECORP'
Global $sUsername = 'test'
Global $sPassword = 'dwpcorp1'
Global $iPort = '1521'

; Internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

_Example1()

Func _Example1()
    Local $sConnectionString = _
            "DRIVER=" & $sDriver & _
            ";SERVER=" & $sServer & _
            ";DATABASE=" & $sDatabase & _
            ";UID=" & $sUsername & _
            ";PWD=" & $sPassword & _
            ";PORT=" & $iPort

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
       IF IsObj($oConnection) then

        $rs = ObjCreate( "ADODB.RecordSet" )
        $rs.open("Delete  branches b where b.branchid=97;")
      EndIf
EndFunc    ;==>_Example1

Hint #2:
Why you are using this:
 

$rs = ObjCreate( "ADODB.RecordSet" )
        $rs.open("Delete  branches b where b.branchid=97;")

When you simply can use
$oConnection.execute(....some....sql......query)

Hint #3:
Are you sure your SQL Syntax is correct ?

Usually SQL syntax for delete should look like:

DELETE FROM table_name WHERE condition;

Hint #4
Usually in SQL syntax using not standard/ strange table name for example "barnches d" (I mean with white char in name)  remain using some special characters like quotes, or double quotes or square brackets.

 

 

Edited November 10, 2017 by mLipok

[vick]

i got the attached file in provider get list.

 

Now I am not getting error when i ran above code.

I tried execute query directly but same result so i tried recordset as well.

this is oracle database so syntax is correct as same syntax worked in my vbscript.

[mLipok]

so how you connect via vbscript ? Connection string is most interesting.

 

[vick]

Set conn = CreateObject("ADODB.Connection")
conn.open "DWECORP","test","dwpcorp1"
strSQLQuery = "Delete  branches b where b.branchid=97"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
 

[vick]

in vb script i do not have to define any driver detail also

 

[vick]

if i put Microsoft ODBC for Oracle as driver, i got error of oracle client and componenet not installed