Jump to content

Trying to connect to Oracle DB


Recommended Posts

  • Moderators

I have encountered a need to connect to an Oracle db, and am not having much luck. I have read through a number of posts on the forum, with several different ways of doing it, but seem to be getting an error either way. I have a DSN set up, and can manually (successfully) test the connection, but something I am doing in the script does not seem to work. I would appreciate any suggestions, as this is not my forte.

Info:
Machine has Oracle 11g installed (Oracle in OraClient11g_home1)

A couple methods I have tried:

For this one, I have tried the full driver string, as well as just OraClient11g_home1. The error returned from my error handler is "Data source name not found and no default driver specified"

$conn = ObjCreate( "ADODB.Connection" )

$DSN = "Driver={Oracle in OraClient11g_home1}; " & _
 "CONNECTSTRING=(DESCRIPTION=" & _
 "(ADDRESS=(PROTOCOL=TCP)" & _
 "(HOST=10.10.204.87)(PORT=1521))" & _
 "(CONNECT_DATA=(SERVICE_NAME=123prod)));uid=admin;pwd=Admin123;"
$conn.Open($DSN)

For this one, I tried both the Oracle 11g above, and Microsoft ODBC for Oracle. I get a different error for Microsoft ODBC for Oracle: "Driver's SQLSetConnectAttr failed"

Global $adoCn = ObjCreate( "ADODB.Connection" )
$adoCn.Properties("Prompt") = 1; 1=PromptAlways, 2=PromptComplete
$Txt = "DRIVER={Oracle in OraClient11g_home1};SERVER=ARTHUR;DBQ=123PROD;UID=admin;PWD=Admin123;"
        $adoCn.ConnectionString = $Txt
        $adoCn.Open

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

  • Moderators

Thanks for the suggestion, but that fails with the same error. One of the other devs here shared a vbscript snippet he used in the past to connect to another of the databases (below), which also doesn't work for me, as it says the Provider could not be located". I'm thinking it is something in the local environment.

conn.open("Provider=MSDAORA.1;User ID=" & UID & ";Password=" & PW & ";Data Source=" & DBNAME & ";Persist Security Info=False")

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

Can you write your tnsnames file on the fly with all that data?  then just issue a simple connect string?

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Link to comment
Share on other sites

  • Moderators

Unfortunately, one of the customer's restraints is to use the currently in place TNSNames. Their XP image has an environment variable pointing out to the file on a network share. I'm thinking more and more it is something with this machine they gave me; it has the 11g client installed but the errors are like it can't access the driver. I am going to try installing the Oracle driver on a VM and see if I can duplicate the results.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

  • Moderators

It does appear that the machine I was on was the culprit; something with the driver not being installed correctly. On the VM I am able to make a connection just fine. Now, on to the monster SELECT statement :)

This is what I am trying at present for a simple test query, once the connection is made. If anyone has suggestions on bettering the syntax, I would greatly appreciate it:

Table name: EDWARD

Some of the columns: AGENCY_DIM_KEY, AGENCY_NAME, AGENCY_CODE

The below doesn't throw an error, but it doesn't return anything either.

$query = $conn.Execute("SELECT * FROM EDWARD.AGENCY_DIM WHERE AGENCY_NAME<>'JOE BORK AGCY'")
   If IsObj($query) Then
      For $element in $query
         MsgBox(0, "", $element.AGENCY_NAME)
      Next
   EndIf

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

Use a adodb.recordset for that...example of what I use (you will have to comment out all the log functions...or change them to consolewrites...and if doing mulitple queries, break out the connection from the function):

Func Var_SQLStatement($sCallersConnectionString, $sCallersSQLStatement) ; Run a SQL statement
    ; Return all data in array...if no records returned, the TRUE is passed back (statement ran), if failure to run, then FALSE
        If $gbPerformLog Then Var_SetLogAndActOnState ( 2, $gbLogLevel_Strt, "Var_SQLStatement()", "Func=[Var_SQLStatement]: Starting function with Params=[" & $sCallersConnectionString & "," & $sCallersSQLStatement & "].", False, False)

    $ado = ObjCreate("ADODB.Connection")
    If @error = 1 Then
        If $gbPerformLog Then Var_SetLogAndActOnState ( 0, $gbLogLevel_Rtrn, "Var_SQLStatement()", "Func=[Var_SQLStatement]: UNable to create ADODB.Connetion Object.", False, False)
        Return False
    EndIf
    $adors = ObjCreate("ADODB.RecordSet")
    If @error = 1 Then
        If $gbPerformLog Then Var_SetLogAndActOnState ( 0, $gbLogLevel_Rtrn, "Var_SQLStatement()", "Func=[Var_SQLStatement]: UNable to create ADODB.RecordSet Object.", False, False)
        Return False
    EndIf

    $ado.Open($sCallersConnectionString)
    $adors.Open($sCallersSQLStatement, $ado)

    ; Only get records if there are records to receive
    If $adors.Fields.Count > 0 Then
        Dim $aReturn[1][1]
        $iField = 0
        ; Create the First record of the array with the Field names
        For $Field In $adors.Fields
            ReDim $aReturn[1][$iField + 1]
            $aReturn[0][$iField] = $Field.name
            $iField += 1
        Next

        $iRow = 1

        While Not $adors.EOF
            $iField = 0
            For $Field In $adors.Fields
                $sValue = $adors.Fields($Field.name).value
                ReDim $aReturn[$iRow + 1][UBound($aReturn, 2)]
                $aReturn[$iRow][$iField] = $sValue
                $iField += 1
            Next
            $adors.MoveNext
            $iRow += 1
        WEnd

        ; Close the recordset
        $adors.Close
        $adors = 0
        If $gbPerformLog Then Var_SetLogAndActOnState ( 1, $gbLogLevel_Rtrn, "Var_SQLStatement()", "Func=[Var_SQLStatement]: Returning Record(s)=[UBound=(" & UBound($aReturn) & ")], each containing Fileds=[" & UBound($aReturn, 2) & "].", False, False)
    Else
        If $gbPerformLog Then Var_SetLogAndActOnState ( 1, $gbLogLevel_Rtrn, "Var_SQLStatement()", "Func=[Var_SQLStatement]: [Insert|Update] Statement ran successfully.", False, False)
        $aReturn = True
    EndIf

    ; Close ADODB connection
    $ado.Close
    $ado = 0
    If IsArray($aReturn) Then
        If UBound($aReturn) > 1 Then
            Return $aReturn
        EndIf
    EndIf
    Return True

EndFunc   ;==>Var_SQLStatement

and your query is a bit off (unless oracle is off standard):

select AGENCY_DIM, anothercol, anothercol2 from edward where AGENCY_NAME<>'JOE BORK AGCY'

to return all cols, then

select * from

...or is edward the server?  I've always connected to that via the connection string

Edited by jdelaney
IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.
Link to comment
Share on other sites

  • Moderators

jdelaney, that works very well for the test query. I am going to spend some time ensuring I understand the function completely, before trying to apply it to the monster query. Many thanks.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

  • Moderators

Yep, I am playing with that now, I just about crashed my VM trying to pull it into an array. The query is big - about 7000 rows in the table, and the SELECT statement has a number of sum calls, ANDs, etc. It's going to be a fun project. :) 

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

  • Moderators

I'm still working on it, but thought I would post the sql here as well. Any SQL experts that could help with the formatting, as well as pulling the information out, I would be very appreciative. The eventual goal is to connect, run this query, then drop the results into specific cells in a spreadsheet (the last part should not be any problem once I have the data in hand).

Payroll.txt

 

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

look into 'inner join' to simplify that...abbrievs on the tables help also

select ccd.class_code, ccd.class_code_state

from POLICY_TRANSACTION_DETAIL_FACT ptd
 
 inner join ACCTG_COV_EFF_DATE_DIM.DATE_DAY_DIM_KEY ace on ace.DATE_DAY_DIM_KEY = ptd.ACCOUNT_COV_EFF_DATE_DIM_KEY
 inner join CLASS_CODE_DESCRIPTION_DIM ccd on ccd.CLASS_CODE_DESCRIPTION_DIM_KEY = ptd.CLASS_CODE_DESCRIPTION_DIM_KEY
...
where
 POLICY_DIM.POLICY_EFFECTIVE_DATE >= 01/01/2007 and

Edited by jdelaney
IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.
Link to comment
Share on other sites

  • Moderators

In the end, rather than trying to get the syntax, with multiple line concantenation correct, correct with error checking, I simply read the sql file in as an array and then converted the array to a string. This is working well, and gives me all I need (see script below) Now all I need to figure out is, if the Value of CLASS_CODE = "0042" then ConsoleWrite that entire row. Will give me something to work on over the weekend. :) 

Thanks again for all the help.

#include <Array.au3>
#include <File.au3>

Local $aArray
    _FileReadToArray(@DesktopDir & "\Payroll.txt", $aArray)
    $sString = _ArrayToString($aArray, " ")

$sCallersConnectionString = "Provider=MSDAORA.1;User ID=jlogan3o13;Password=Change123;Data Source=123PROD;Persist Security Info=False"
$sCallersSQLStatement = StringTrimLeft($sString, 3)

Var_SQLStatement($sCallersConnectionString, $sCallersSQLStatement)

Func Var_SQLStatement($sCallersConnectionString, $sCallersSQLStatement) ; Run a SQL statement
    ; Return all data in array...if no records returned, the TRUE is passed back (statement ran), if failure to run, then FALSE

    $ado = ObjCreate("ADODB.Connection")
        If Not IsObj($ado) Then MsgBox(0, "", "Error creating Connection Object")
    $adors = ObjCreate("ADODB.RecordSet")
        If Not IsObj($adors) Then MsgBox(0, "", "Error creating RecordSet Object")
    $ado.Open($sCallersConnectionString)
        If @error Then MsgBox(0, "", "Error opening Connection")
    $adors.Open($sCallersSQLStatement, $ado)
        If @error Then MsgBox(0, "", "Error calling Statement")

    ; Only get records if there are records to receive
    If $adors.Fields.Count > 0 Then
        Dim $aReturn[1][1]
        $iField = 0
        ; Create the First record of the array with the Field names
        For $Field In $adors.Fields
            ReDim $aReturn[1][$iField + 1]
            $aReturn[0][$iField] = $Field.name
            $iField += 1
        Next

        $iRow = 1

        While Not $adors.EOF
            $iField = 0
            For $Field In $adors.Fields
                If $Field.name = "CLASS_CODE" Then ;<--Here is where I should be able to pull the entire row if CLASS_CODE = "0042"
                    If $adors.Fields($Field.name).value = "0042" Then ConsoleWrite($adors.Fields($Field.name).value & @CRLF)
                EndIf
                $sValue = $adors.Fields($Field.name).value
                ReDim $aReturn[$iRow + 1][UBound($aReturn, 2)]
                $aReturn[$iRow][$iField] = $sValue
                $iField += 1
            Next
            $adors.MoveNext
            $iRow += 1
        WEnd

        ; Close the recordset
        $adors.Close
        $adors = 0
    Else
        ConsoleWrite("No records returned" & @CRLF)
    EndIf

    ; Close ADODB connection
    $ado.Close
    $ado = 0
    If IsArray($aReturn) Then
        If UBound($aReturn) > 1 Then
            Return $aReturn
        EndIf
    EndIf
EndFunc

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...