Moderators JLogan3o13 Posted June 12, 2013 Moderators Share Posted June 12, 2013 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 More sharing options...
jdelaney Posted June 12, 2013 Share Posted June 12, 2013 (edited) try $adoCn.Open($txt) works for sybase and sql server Edited June 12, 2013 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 More sharing options...
Moderators JLogan3o13 Posted June 12, 2013 Author Moderators Share Posted June 12, 2013 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 More sharing options...
iamtheky Posted June 12, 2013 Share Posted June 12, 2013 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 More sharing options...
Moderators JLogan3o13 Posted June 13, 2013 Author Moderators Share Posted June 13, 2013 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 More sharing options...
Moderators JLogan3o13 Posted June 13, 2013 Author Moderators Share Posted June 13, 2013 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 More sharing options...
jdelaney Posted June 13, 2013 Share Posted June 13, 2013 (edited) 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): expandcollapse popupFunc 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 June 13, 2013 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 More sharing options...
Moderators JLogan3o13 Posted June 13, 2013 Author Moderators Share Posted June 13, 2013 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 More sharing options...
jdelaney Posted June 13, 2013 Share Posted June 13, 2013 If it's a huge return, check out how to loop, and pull directly from the adors return...pushing it all to an array will be a waste of time. 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 More sharing options...
Moderators JLogan3o13 Posted June 13, 2013 Author Moderators Share Posted June 13, 2013 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 More sharing options...
Moderators JLogan3o13 Posted June 14, 2013 Author Moderators Share Posted June 14, 2013 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 More sharing options...
jdelaney Posted June 14, 2013 Share Posted June 14, 2013 (edited) 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 June 14, 2013 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 More sharing options...
Moderators JLogan3o13 Posted June 14, 2013 Author Moderators Share Posted June 14, 2013 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. expandcollapse popup#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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now