Oracle SQL db queries

[FrancescoDiMuro]

18 minutes ago, jaja714 said:

What about %appdata%\SQL Developer\system\connections.xml?

Did you find anything useful in it?

Maybe this couls help?

[mLipok]

6 hours ago, jaja714 said:

FYI: I tried many combinations, including:

$service = 'mydbname.domain.com'
    $user = 'myusername'
    $pass = 'mypassword'
    .ConnectionString = 'Provider=OraOLEDB.Oracle;Data Source=' & $service & ';User Id=' & $user & ';Password=' & $pass & ';'
    .ConnectionString = 'Provider=msdaora;Data Source=' & $service & ';User Id=' & $user & ';Password=' & $pass & ';'
    .ConnectionString = 'Driver={Microsoft ODBC Driver for Oracle};ConnectString=' & $service & ';Uid=' & $user & ';Pwd=' & $pass & ';'
    .ConnectionString = 'Driver={Microsoft ODBC for Oracle};Server=' & $service & ';Uid=' & $user & ';Pwd=' & $pass & ';'

I'm not sure why these are failing.  Is it syntax?
 

First try this:

 

[JockoDundee]

here:

[mLipok]

btw.
Could I do test, check with this:
https://www.oracle.com/database/technologies/xe-downloads.html

?

 

[mLipok]

7 hours ago, jaja714 said:

FYI: I tried many combinations, including:

$service = 'mydbname.domain.com'
    $user = 'myusername'
    $pass = 'mypassword'
    .ConnectionString = 'Provider=OraOLEDB.Oracle;Data Source=' & $service & ';User Id=' & $user & ';Password=' & $pass & ';'
    .ConnectionString = 'Provider=msdaora;Data Source=' & $service & ';User Id=' & $user & ';Password=' & $pass & ';'
    .ConnectionString = 'Driver={Microsoft ODBC Driver for Oracle};ConnectString=' & $service & ';Uid=' & $user & ';Pwd=' & $pass & ';'
    .ConnectionString = 'Driver={Microsoft ODBC for Oracle};Server=' & $service & ';Uid=' & $user & ';Pwd=' & $pass & ';'

I'm not sure why these are failing. 

use my ADO.au3 UDF  

with this example   ADO_TESTING__ jaja714_Oracle.au3  :
 

expandcollapse popup

;~ https://www.autoitscript.com/forum/topic/205404-oracle-sql-db-queries/
#AutoIt3Wrapper_UseX64=N
;~ #AutoIt3Wrapper_UseX64=Y

#include "ADO.au3"
;~ #include "..\..\ADO.au3"
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

; Uncomment one of the following examples
;
_Example_MySQL('DataSource', 'UserName', 'Password', 'Select * ........')


Func _Example_MySQL($service, $user, $pass, $sQUERY)
    ; https://www.connectionstrings.com/oracle/
    ; https://www.autoitscript.com/forum/topic/205404-oracle-sql-db-queries/?do=findComment&comment=1477692

    Local $sConnectionString = 'Provider=OraOLEDB.Oracle;Data Source=' & $service & ';User Id=' & $user & ';Password=' & $pass & ';'
;~  Local $sConnectionString = 'Provider=msdaora;Data Source=' & $service & ';User Id=' & $user & ';Password=' & $pass & ';'
;~  Local $sConnectionString = 'Driver={Microsoft ODBC Driver for Oracle};ConnectString=' & $service & ';Uid=' & $user & ';Pwd=' & $pass & ';'
;~  Local $sConnectionString = 'Driver={Microsoft ODBC for Oracle};Server=' & $service & ';Uid=' & $user & ';Pwd=' & $pass & ';'

    _Example_1_RecordsetToConsole($sConnectionString, $sQUERY)
    _Example_2_RecordsetDisplay($sConnectionString, $sQUERY)
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MySQL

#Region Common / internal
Func _Example_1_RecordsetToConsole($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    ; Get recordset to array of arrays (Conent and ColumnNames)
    Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)

    ; Get inner array - only conent of Recordset
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetArray)

    ; Go through the array variable (Recorset Conent)
    Local $iColumn_count = UBound($aRecordsetContent, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordsetContent) - 1
        ConsoleWrite('==================================================================' & @CRLF)
        For $iColumn_idx = 0 To $iColumn_count - 1
            ConsoleWrite($aRecordsetContent[$iRecord_idx][$iColumn_idx] & @CRLF)
        Next
    Next

    ; Clean Up
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc   ;==>_Example_1_RecordsetToConsole

Func _Example_2_RecordsetDisplay($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc   ;==>_Example_2_RecordsetDisplay

Func _Example_3_ConnectionProperties($sConnectionString)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Get all connection properties to Array
    Local $aProperties = _ADO_Connection_PropertiesToArray($oConnection)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Show connection properties
    _ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "Name|Type|Value|Attributes")

EndFunc   ;==>_Example_3_ConnectionProperties
#EndRegion Common / internal

 

and if something will be wrong send here console output (of course anonymize them before sending).

 

[Earthshine]

the connection string website has ALL the strings and how to use them. You don't just need to use ADO was my point. You have Oracle SQL Dev installed then you have ORACLE drivers you can direct connect with. MUCH better than ADO could dream of.

 

take those stings and modify them as you need. develop them on the admin sql command line ORACLE uses, sqlplus

when they work from CMDLINE, then you copy the EXACT string to your code, then you use programming to write out the EXACT string with quotes where necessary. it's not that hard.

 

read this

Connecting to Oracle Database

 

 

Edited March 17, 2021 by Earthshine

[mLipok]

@Earthshine you are pointing to Net techniques which may (or not) rely on ADO. But in fact this is Net techniques.

But there is Oracle COM Automation Feature which should be possible to use with AutoIt, as so far not tested/confirmed.

 

 

[Earthshine]

oh I'm sure your UDF is very nice.  OP has options available. using a basic string using their driver and automating sqlplus is an option too

i might just set up a fresh win10 test vm and install oracle to make some examples if I have time.

Edited March 17, 2021 by Earthshine